Create SSIS Package in Code Part 1 - Pure C#

A quick question: if you are an ETL specialist, how many times do you need to repeat your SSIS design task? Myself, 80% of my SSIS time is to move a set of data from place A to place B.

So if you haven't known these sort of things, I am going to introduce three ways to build package dynamically:

• by native C# code (this post)
• by EzAPI 
• by BIML

Today let's focus on pure C# code, it is a bit crazy, so I will never ever suggest anyone to create SSIS packages through this way. But knowing the way how the package is created can give you some understanding when you are looking at EzAPI and BIML. So let's start today's topic.

All I am going to do, is to create a SSIS package in C#, which will load a text file into a table. Below is the T-SQL script to create the dummy table:

use Staging
go

if exists(select 1 from sys.tables where name = 'Customer')
begin
 drop table Customer
end
go

Create table Customer
(
CustomerKey int identity(1,1),
FirstName varchar(255),
LastName varchar(255),
Suburb varchar(100),
State varchar(3),
Postcode varchar(4),
CreatedAt datetime default(getdate())
)
go

insert into Customer (FirstName, LastName, Suburb, State, Postcode)
values
('Chi', 'Qi', 'Richmond', 'VIC', '3121'),
('John', 'Smith', 'Melbourne', 'VIC', '3000')
go

select * from Customer

And the data flow task is as simple as screenshot shows below:

The flat file used in this example is a very simple text file:

To have the package created from C# code directly, I have a C# console project created, then add below references:

Usually you can find these dll files at C:\Program Files (x86)\Microsoft SQL Server\<your SQL Server version>\SDK\Assemblies. Just remember put correct version into the place holder (2016: 130; 2014: 120, 2012: 110, etc.).

Now following the standard way, I add a few using clauses

using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using RunTimeWrapper = Microsoft.SqlServer.Dts.Runtime.Wrapper; 

Now here is a general overview of the steps I am going to do:

1. create a flat file connection manager for the source; 
2. create flat file connection in the flat file connection manager;
3. create column metadata within the flat file connection;
4. create an OLE DB connection manager for the destination;
5. create a data flow task in the package;
6. create the flat file source within the data flow task;
7. create the OLE DB destination within the data flow task;
8. create a path between flat file source and OLE DB destination;
9. map the columns between input and output;
10. save the package;
11. execute it!

And after executing the code (package), the staging.dbo.customer table should have dummy customer records from the text file:

I am not going to explain my code line by line - you can find all of them at the end of this post. But there are a few things you might want to know:

• the connection manager is identified by its name. To have a full list of connection managers, you can all of them at MSDN 
• the data flow task is identified by its name as well. But when adding them, keep in mind you need to prefix them with "STOCK:". List of the SSIS tasks can be found at here
• The difficult part is finding correct ComponentClassID for the source and destination. To make it simple, just remember for SSIS 2016 the code is "5", SSIS 2014 the code is "4", SSIS 2012 the code is "3"... 

        static void Main(string[] args)
        {

            Application app = new Application();

            Package pkg = new Package();

            string pkgPath = @"d:\test\myFirstPackage.dtsx";
            string fltFilePath = @"d:\test\text.txt";
                        

            //file connection manager
            ConnectionManager fltFileConMgr = pkg.Connections.Add("FlatFile");
            fltFileConMgr.ConnectionString = @"d:\test\test.txt";
            fltFileConMgr.Name = "my File Connection Manager";

            fltFileConMgr.Properties["Format"].SetValue(fltFileConMgr, "Delimited");
            fltFileConMgr.Properties["ColumnNamesInFirstDataRow"].SetValue(fltFileConMgr, Convert.ToBoolean(true));

            //build the flat file connection in the connection manager
            RunTimeWrapper.IDTSConnectionManagerFlatFile100 fltFileCon = (RunTimeWrapper.IDTSConnectionManagerFlatFile100)fltFileConMgr.InnerObject;
            fltFileCon.CodePage = 1252;

            //for the native flat file connection manager, create import columns
            //in this example, we hard code metadata for all columns
            //FirstName, LastName, Suburb, State, Postcode
            #region columns

            RunTimeWrapper.IDTSConnectionManagerFlatFileColumn100 colFirstName= fltFileCon.Columns.Add();
            colFirstName.ColumnType = "Delimited";
            colFirstName.ColumnDelimiter = "|";
            colFirstName.DataType = RunTimeWrapper.DataType.DT_STR;
            colFirstName.ColumnWidth = 255;
            ((RunTimeWrapper.IDTSName100)colFirstName).Name = "FirstName";

            RunTimeWrapper.IDTSConnectionManagerFlatFileColumn100 colLastName = fltFileCon.Columns.Add();
            colLastName.ColumnType = "Delimited";
            colLastName.ColumnDelimiter = "|";
            colLastName.DataType = RunTimeWrapper.DataType.DT_STR;
            colLastName.ColumnWidth = 255;
            ((RunTimeWrapper.IDTSName100)colLastName).Name = "LastName";

            RunTimeWrapper.IDTSConnectionManagerFlatFileColumn100 colSuburb = fltFileCon.Columns.Add();
            colSuburb.ColumnType = "Delimited";
            colSuburb.ColumnDelimiter = "|";
            colSuburb.DataType = RunTimeWrapper.DataType.DT_STR;
            colSuburb.ColumnWidth = 100;
            ((RunTimeWrapper.IDTSName100)colSuburb).Name = "Suburb";

            RunTimeWrapper.IDTSConnectionManagerFlatFileColumn100 colState = fltFileCon.Columns.Add();
            colState.ColumnType = "Delimited";
            colState.ColumnDelimiter = "|";
            colState.DataType = RunTimeWrapper.DataType.DT_STR;
            colState.ColumnWidth = 3;
            ((RunTimeWrapper.IDTSName100)colState).Name = "State";


            RunTimeWrapper.IDTSConnectionManagerFlatFileColumn100 colPostcode = fltFileCon.Columns.Add();
            colPostcode.ColumnType = "Delimited";
            colPostcode.ColumnDelimiter = Environment.NewLine; //this is the last column, so the delimiter needs to be set as new line
            colPostcode.DataType = RunTimeWrapper.DataType.DT_STR;
            colPostcode.ColumnWidth = 4;
            ((RunTimeWrapper.IDTSName100)colPostcode).Name = "Postcode";

            #endregion



            //ole db connection manager
            ConnectionManager oleConMgr = pkg.Connections.Add("OLEDB");
            oleConMgr.ConnectionString = "Data Source = CHI\\SS2016;Provider=SQLNCLI11.1;Initial Catalog = Staging;Integrated Security = SSPI;";
            oleConMgr.Name = "my OLE Connection Manager";


            //data flow task
            Executable executable = pkg.Executables.Add("STOCK:PipelineTask");
            TaskHost host = (TaskHost)executable;
            host.Name = "DFT my data flow task";

            //now time to add inner object into the DFT
            MainPipe dft = (MainPipe)host.InnerObject;
            

            IDTSComponentMetaData100 src = dft.ComponentMetaDataCollection.New();
            src.Name = "FLT SRC";

            src.ComponentClassID = "DTSAdapter.FlatFileSource.5";
            
            CManagedComponentWrapper instanceSrc = src.Instantiate();
            instanceSrc.ProvideComponentProperties();

            
            if (src.RuntimeConnectionCollection.Count > 0)
            {
                //very important!! get the native object by the conversion
                src.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(fltFileConMgr);
                src.RuntimeConnectionCollection[0].ConnectionManagerID = fltFileConMgr.ID;
            }
                                    

            instanceSrc.AcquireConnections(null);
            instanceSrc.ReinitializeMetaData();
            instanceSrc.ReleaseConnections();


            //OLE Destination
            IDTSComponentMetaData100 dest = dft.ComponentMetaDataCollection.New();
            dest.Name = "OLE DEST";
            dest.ComponentClassID = "DTSAdapter.OLEDBDestination.5";

            CManagedComponentWrapper instanceDest = dest.Instantiate();
            instanceDest.ProvideComponentProperties();

            if (dest.RuntimeConnectionCollection.Count > 0)
            {
                dest.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(oleConMgr);
                dest.RuntimeConnectionCollection[0].ConnectionManagerID = oleConMgr.ID;
            }

            instanceDest.SetComponentProperty("OpenRowset", "Customer");



            //Path between src and dest
            IDTSPath100 path = dft.PathCollection.New();
            path.AttachPathAndPropagateNotifications(src.OutputCollection[0], dest.InputCollection[0]);

            IDTSInput100 destInput = dest.InputCollection[0];
            IDTSVirtualInput100 destVirtualInput = destInput.GetVirtualInput();
            IDTSVirtualInputColumnCollection100 destVirtualInputColumns = destVirtualInput.VirtualInputColumnCollection;

            instanceDest.AcquireConnections(null);
            instanceDest.ReinitializeMetaData();
            instanceDest.ReleaseConnections();



            //finally, map columns between input and output by name
            foreach (IDTSVirtualInputColumn100 virtualCol in destVirtualInputColumns)
            {
                IDTSInputColumn100 inputCol = instanceDest.SetUsageType(destInput.ID, destVirtualInput, virtualCol.LineageID, DTSUsageType.UT_READONLY);
                IDTSExternalMetadataColumn100 externalColumn = destInput.ExternalMetadataColumnCollection[inputCol.Name];
                instanceDest.MapInputColumn(destInput.ID, inputCol.ID, externalColumn.ID);
            }


            app.SaveToXml(pkgPath, pkg, null);

            pkg.Execute();

            Console.WriteLine("package created");
            Console.ReadLine();
        }