Creating SSIS Package Components and Features Programmatically using .NET

Problem Statement

This might sound strange, but I am writing about a subject that has been around since 2005. However, since then, the objects within SSIS 2016 have changed and I recently had a customer request that prompted me to document my findings.

I needed to add and configure some features in an SSIS package and I had old .NET code that did the magic for me in 2007. I used the same code for my SQL 2016 SSIS package but it didn't work. WHAT?!?!

Solution

Some of the changes that were required included the following:

1. OLE provider is now "Provider=SQLNCLI11.1"
2. LogProvider object in SQL 2016 is no longer 2, it's 5: "DTS.LogProviderSQLServer.5"
1. NOTE: As of when this blog post was written, this information could not be found on MSDN Online.
3. And more...

Benefits of having objects in the package created programmatically

1. The package framework allows for a standardized approach.
2. Achieve more consistency in your SSIS packages created through package framework.
3. Save time and be more accurate in designing the required changes.
4. Understand the inner layer of SSIS package design.

Solution Breakdown

I am going to create or configure the below objects in a sample SSIS package

1. Add a Connection Object
     Dim conMgrDest As ConnectionManager = Pkg.Connections.Add("OLEDB")
2. Add Event Handlers
     Pkg.EventHandlers.Add("OnError")
3. Set the SSIS Logging
     Dim logProv As LogProvider = Pkg.LogProviders.Add("DTS.LogProviderSQLServer.5")

       Pkg.LoggingOptions.SelectedLogProviders.Add(logProv)  

       Pkg.LoggingMode = DTSLoggingMode.Enabled 

       Pkg.LoggingOptions.EventFilter = arrayEventFilter

Steps

1. Download the Sample Solution
2. I have created 4 empty SSIS packages that are in the "SampleEmptySSISPackages" folder, in the case of my customer I had 300 packages.
3. Copy all packages into the "ToBeProcessed" folder.
4. Open the solution, "DotNetCodeOnSSISPkgs.sln".
5. Open "pkgFwDotNetCodeOnSSISPkgs.dtsx" and go to Parameters.
6. Make sure that the "uPara_FolderPath" is pointing to the location of your "ToBeProcessed" folder.
7. To create the Connection Manager, you will need to set the "SSISLogServer" and the "SSISLogDB".
8. Run the SSIS package

Validating Results

To check the results, open the packages in the folder "ToBeProcessed" in Visual studio. You will see the changes mentioned below:

1. New Connection Object "conn_OLEDB_SSISLoggingConnection" is added
2. The Event Handlers: OnError, OnPostExecute and OnPreExecute events have been added at the package level.
3. In the menu SSIS --> Logging, you will see the following items:
1. Containers: Package is checked
2. Providers and logs: new name has been added, "My SSIS Log Provider for SQL Server", and the same connection object has been used.
3. Details: OnError, OnExecuteStatusChanged, OnPostExecute, OnPreExecute are all checked

NOTE: To do the test again, you must copy the sample files from the "SampleEmptySSISPackages" folder into the "ToBeProcessed" folder and rerun the "pkgFwDotNetCodeOnSSISPkgs.dtsx" package.

References

MSDN LogProviders.Add Method
Adding Connections Programmatically