Wednesday, 9 July 2014

Create SSIS Package programatically using SSIS API in c#

Today I am going to show you, how you can create a SSIS package programmatically in C#.
Before that, let's discuss first problem statement or scenario where we might need this kind of solution.

Problem Statement: I need to extract data from multiple tables in a database and save that extracted data to a separate database for the purpose of read-only use (for example Reporting purpose). We need to do this because we want to avoid load on OLTP system. Requirement is to automate this process, so any time I should be able to create a new table (based on new requirement coming) and load/update data in that table without writing code each time.

Solution: Here I created a table(as per below mentioned table schema) programmatically in database now next step to load data on this table.

Now let's create SSIS package to load data to this table programmatically using C#. Requirement to insert new rows (if it is new) or update the existing row (if it is already exists) based on unique key value.
To do this we need below assemble reference to our project.

Step 2: Creating connection manager to connect to source and destination database. To make example simple I used same database as a source as well as destination. Written within "Add connection manager" region.

Step 3: Created Oledb Source Data Flow control which will extract the data from the source location. Written within "Add OLE DB Source Data Flow" region.

Step 4: Created a lookup data flow control which will help to identify the new row and updated row by looking to destination database. Written within "lookup transform" region. Here we need to mention which column you want to look in destination to identify rows. in my case I am using 'Name' column (the column which will be used here must be unique). As a result lookup will give result as 'Match Column'(rows already exists in destination and need update) and 'No Match Column'(new rows and need insert).

Step 5: Next I added a Oledb Destination Data flow control for No Match lookup output, to insert rows in destination. Written within " Add OLE DB Destination Data Flow for No Match Output" region.

Step 6: Than I added a Oledb Command Data flow control which will update the existing rows using update sql command. Written within " Add OLE DB Command Data Flow for Matching Output" region. Written within "Add OLE DB Command Data Flow for No Match Output" region.

Now we need to do mapping of columns for Oledb Destination and Oledb Command Data Flow control.

Step 7: Doing mapping of columns for Oledb Destination data flow control. written within "Connect source and destination for No Match Outputs" region.

Here the destination external column for Oledb Command will be auto generated with the name like Param_0, Param_1 ... etc, based on params required by sql command defined with '?' symbol in oledb command data flow control.

You have to create some logic to do this mapping, as column names are not same (refer below pic)

Step 9: Than at last saving the package to some location to use it later, using some job to update data periodically.

Finally if you try to open this package using SQL Server BI Dev Studio, it will look like below image here:

4 comments:

Thanks for sharing and nice article, SSIS is much limited to oledb connector. hopefully when MS moves to odbc as unversal driver and make enough support across all dev tools then SSIS can be considered an generic ETL tool perhaps.

Hi Binod - Thanks for this nice article. I've got an issue while creating the SSIS package - when adding connections to the package as in your code: ConnectionManager connection = package.Connections.Add("OLEDB"); . The exception is - Microsoft.SqlServer.Dts.Runtime.DtsRuntimeException: The connection type "OLEDB" specified for connection manager "{89241BBE-E5AD-4BEA-9309-7BA55E2DA871}" is not recognized as a valid connection manager type. This error is returned when an attempt is made to create a connection manager for an unknown connection type. Check the spelling in the connection type name. Can you please look at this issue?