Software Architecture – Sync Data between offline/Disconnected databases – SSIS – BIDS

September 18, 2010

Problem definition:
ERP Product is installed in head office and 5 branch offices such that each location installation has its on database. Locations are not connected via Internet that is they are acting as diconnected databases.

We want to sync databases of different branches.

Solution:
Some possible solutions to problem are as:
• Replication
• ETL
• Sync Providors
• Sync Framework
• Change Data Capture
We will compare and analyze pros and corns of these method in separate post. But here we will use ETL approach to solve this problem.
At closing of each working day, data is fetched from each branch database and new data is posted to other branches and head office database Server via email. So each database read email and executes the database differences from other databases.
So we can break whole process in 4 programming Steps.
1. Extract data from source database and convert them into a text file with data/time stamp attached.
[Implemented as SSIS Package and run via C# Code]

For details see post here

2. Read files from a folder in step 1, and send them as attachment in email via Outlook.
[Implemented by interop of MS Outlook API]

For details see post here

3. On other side, emails are pulled automatically via outlook and attachments are downloaded and saved in a local folder in hard disk.
[Implemented by interop of MS Outlook API]
For details see post here

4. Text files s are read from folder and loaded in to database.
[Implemented as SSIS Package and run via C# Code]

For details see post here

Future consideration:

1. How to identify the differential of data between the daily move of data.
2. This solution is working for one table how to handle more tables for data sync process.
3. There are many hardcoded parameters that needs to be fetched from configuration files.

Source: download from here Rename the file to ssis-solution-toupload.zip to extract.
Feedback is welocme


Software Architecture – To Load the data from files of a folder to table of SQL Server via SSIS in SQL Server Business Intelligence Development Studio

September 18, 2010

Objective:
Objective of this post is to load the data from all the files in a folder to a database of SQL Server via SSIS in SQL Server Business Intelligence Development Studio 2008 .

Environment:
SSIS + SQL Server Business Intelligence Development Studio 2008 + C# 4.0

Steps:

1. Create a new Package named “LoadData.dtsx” in BIDS 2008.
2. Create a new variable with name “FileName”.
3. Drag and drop a “Foreach loop container” to SSIS designer.
Set the Enumerator type and configuration settings as shown below.

Set “variable mapping” as shown below.

4. Drag a data flow task on SSIS Designer.
5. On the Data Flow tab, drag and drop the “Flat File Source” from the Data Flow Source in the Data Flow Tool Box. Click on New to create the New Flat File Source Connection Manager. Uses a property expression on the ConnectionString property of a Flat File Connection Manager to point to the file indicated by User::FileName

6. On the Data Flow tab, drag and drop the “OLE DB Designation” from the Data Flow Designation in the Data Flow Tool Box. Create the new OLE DB Designation connection manager by clicking on “New” button on the OLE DB Designation Editor.
Configuration will look like as:

7. So at the end of these changes, Control Flow will look like as:

And Data Flow will look like as:

8. Run the Package from Solution explorer to see the output.
9. Package can be run by following c# code.

string pkgLocation = @"F:\\e-pros\\testbest\\SSISFirst\\SSISFirst\\SSISFirst\\ExtractData.dtsx"; 
 
        Package pkg; 
        Microsoft.SqlServer.Dts.Runtime.Application app; 
        DTSExecResult pkgResults; 
        Variables vars; 
 
        app = new Microsoft.SqlServer.Dts.Runtime.Application(); 
        pkg = app.LoadPackage(pkgLocation, null); 
 
      //  vars = pkg.Variables; 
      //  vars["A_Variable"].Value = "Some value";                
 
        pkgResults = pkg.Execute(null, null, null, null, null);
 
        if (pkgResults == DTSExecResult.Success)
        {
            Console.WriteLine("Package ran successfully");
 
        }
        else
            Console.WriteLine("Package failed"); 

Related Post: https://softarchitect.wordpress.com/2010/09/18/software-architecture-sync-data-between-offlinedisconnected-databases-ssis-bids/


Software Architecture – To extract the data from a table of SQL Server and convert it to text file via SSIS in SQL Server Business Intelligence Development Studio

September 18, 2010

Objective:
Objective of this post is to extract the data from a table of SQL Server and convert it to text file having name as datetime appended via SSIS in SQL Server Business Intelligence Development Studio 2008 .

Environment:
SSIS + SQL Server Business Intelligence Development Studio 2008 + C# 4.0
Steps:

1. Create a new Package named “ExtractData.dtsx” in BIDS 2008.
2. Create a new variable with name “FileLocation” and set its value to folder name, lets say “F:\FileInbox”
3. Append variable “FileLocation” value with datetime stamp by adding a “Script Task” as:
Dts.Variables[“User::FileLocation”].Value = Dts.Variables[“User::FileLocation”].Value.ToString() + “\\data_” + DateTime.Now.ToString(“MMddyyyy_hhmmss”) ;
This command will define the name of data file to be generated.
4. Drag a data flow task on SSIS Designer.
5. On the Data Flow tab, drag and drop the “OLE DB Source” from the Data Flow Sources in the Data Flow Tool Box. Create the new OLE DB source connection manager by clicking on “New” button on the OLE DB Source Editor. Specify the command that will fetch the data from the table as:

SELECT TOP 1000 [AreaId]
,[AreaCode]
,[Description]
,[Active]
,[CreatedDate]
,[ModifiedDate]
,[BranchId]
FROM [GeneralAreaMaster]

6. On the Data Flow tab, drag and drop the “Flat File Destination” from the Data Flow Destinations in the Data Flow Tool Box. Click on New to create the New Flat File Destination Connection Manager. Configure properties accordingly.
7. So at the end of these changes, Control Flow will look like as:

And Data Flow will look like as:

8. Run the Package from Solution explorer to see the output.
9. Package can be run by following c# code.

string pkgLocation = @"F:\\e-pros\\testbest\\SSISFirst\\SSISFirst\\SSISFirst\\ExtractData.dtsx"; 
 
        Package pkg; 
        Microsoft.SqlServer.Dts.Runtime.Application app; 
        DTSExecResult pkgResults; 
        Variables vars; 
 
        app = new Microsoft.SqlServer.Dts.Runtime.Application(); 
        pkg = app.LoadPackage(pkgLocation, null); 
 
      //  vars = pkg.Variables; 
      //  vars["A_Variable"].Value = "Some value";                
 
        pkgResults = pkg.Execute(null, null, null, null, null);
 
        if (pkgResults == DTSExecResult.Success)
        {
            Console.WriteLine("Package ran successfully");
 
        }
        else
            Console.WriteLine("Package failed"); 

Related Post: https://softarchitect.wordpress.com/2010/09/18/software-architecture-sync-data-between-offlinedisconnected-databases-ssis-bids/


Software Architecture – To Analyze Licensing needs for PCMS- Pegasus Cargo Management System

July 19, 2010

Topic: To Analyze Licensing needs for PCMS- Pegasus Cargo Management System
By: Shahzad Sarwar
Dated: 19th July 2010

Pegasus Cargo Management System-PCMS:
Pegasus Cargo Management System is one of the FIRST software in the cargo industry, which is totally integrated with accounts. Designed by experts from cargo and software industry it provides one stop solution for data entry and decision support. The implementation of detailed function level checks ensures reliability & date integrity of system. The embedded security through appropriate Access Controls allows complete control of user access and data manipulation. PCMS provides detailed and extensive MIS reports helping the management to take informed decisions.

Conclusions:
• PCMS-Pegasus Cargo Management System needs .Net framework 3.5 runtime and SQL server on a Windows Machine.
• Microsoft .Net 3.5 runtime is shipped free on all latest windows version. If not available , it can be installed by downloading from Microsoft site for free.
• PCMS-Pegasus Cargo Management System is targeted for small to medium size organizations where database needs are not very high tech. Advance features of SQL Server are not required for application to run.
• With reference to section 2.1, most close versions for consideration should be SQL Server Express edition and SQL Server Workgroup Edition.
• SQL Server Express edition with advance features will be launching pad for PCMS ERP. As it is free and have all the features required to run PCMS.
• For Details about SQL Server Express edition with Advance features, see tables (2.2, 2.3).
• After starting along SQL Server Express edition with Advance features, down the roads, when database size exceeds 10 GB limit, we can propose clients to switch to SQL Server Workgroup edition.
• For detailed analysis of SQL Server Express edition with advance features, see section (3.1, 3.2, 3.3, 3.4, 3.5, 3.6) in Red Color.
• For detailed analysis of SQL Server Workgroup edition, see sections (3.1, 3.2, 3.3, 3.4, 3.5, 3.6) in Pink Color.
• Pricing for licenses shown in section 4.