- OLE provider is now "Provider=SQLNCLI11.1"
- LogProvider object in SQL 2016 is no longer 2, it's 5: "DTS.LogProviderSQLServer.5"
- NOTE: As of when this blog post was written, this information could not be found on MSDN Online.
- And more...
Benefits of having objects in the package created programmatically
- The package framework allows for a standardized approach.
- Achieve more consistency in your SSIS packages created through package framework.
- Save time and be more accurate in designing the required changes.
- Understand the inner layer of SSIS package design.
- Add a Connection Object
Dim conMgrDest As ConnectionManager = Pkg.Connections.Add("OLEDB")
- Add Event Handlers
- 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
- Download the Sample Solution
- I have created 4 empty SSIS packages that are in the "SampleEmptySSISPackages" folder, in the case of my customer I had 300 packages.
- Copy all packages into the "ToBeProcessed" folder.
- Open the solution, "DotNetCodeOnSSISPkgs.sln".
- Open "pkgFwDotNetCodeOnSSISPkgs.dtsx" and go to Parameters.
- Make sure that the "uPara_FolderPath" is pointing to the location of your "ToBeProcessed" folder.
- To create the Connection Manager, you will need to set the "SSISLogServer" and the "SSISLogDB".
- Run the SSIS package
- New Connection Object "conn_OLEDB_SSISLoggingConnection" is added
- The Event Handlers: OnError, OnPostExecute and OnPreExecute events have been added at the package level.
- In the menu SSIS --> Logging, you will see the following items:
- Containers: Package is checked
- Providers and logs: new name has been added, "My SSIS Log Provider for SQL Server", and the same connection object has been used.
- Details: OnError, OnExecuteStatusChanged, OnPostExecute, OnPreExecute are all checked