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

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/

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: