How to create SSIS Package Programmatically (Add / Execute Tasks)


In this post, we will see how to create SSIS Package Programmatically to use ZappySys SSIS PowerPack. This blog post assumes you have basic knowledge of Programming language like C# and you have installed Visual Studio Express or a similar product (VS Code not supported).


Before we perform the steps listed in this article, you will need to make sure the following prerequisites are met:
  1. SSIS designer installed. Sometimes it is referred to as BIDS or SSDT (download it from the Microsoft site).
  2. Basic knowledge of SSIS package development using Microsoft SQL Server Integration Services.
  3. Make sure ZappySys SSIS PowerPack is installed (download it, if you haven't already).
  4. (Optional step). Read this article, if you are planning to deploy packages to a server and schedule their execution later.

Add SSIS Dll References

The very first thing is to create a C# Project (i.e. Console or WinForm). Add SSIS dll reference.

You will need the following references in your project. The below example is for SSIS 2017 (Version 14.x.x)

  • For SSIS 2012 change to Version=
  • For SSIS 2014 change to Version=
  • For SSIS 2016 change to Version=
  • For SSIS 2017 change to Version=
  • For SSIS 2019 change to Version=
  • And may be future versions same way ….

Step-By-Step Example (Create SSIS Package Programmatically – C#)

Now let’s see each piece to create SSIS Package in code and later we will show the full code.

Import Namespaces

First step is at the top you need to import some namespaces.

Create SSIS Package

Here how you can create SSIS Package instance in code

Add SSIS Connection

Now lets add connection manager to our package instance. To add connection you will need two pieces. Creation Name (i.e. ZS-SFTP) and ConnectionString. You can find creation name from exsing Dtsx file by opening in text editor.

How to find CreationName of SSIS Connection manager

To find the creation name of your Connection Type open an existing DTSX file where you had similar connection type in the package. Locate node like below and check CrewationName. It can be a friendly name or can be fully qualified assembly name. Both ways is fine but a friendly name is easy to remember.

How to find ConnectionString

Now another thing you need to know is the exact connectionstring. You can go to SSIS Designer and right-click on Connection Icon and check Properties Grid. It will show you ConnectionString or you can use the above technique > Open DTSX file in notepad and find connectinstring there.

ConnectionString doesnt include any passwords / secrets. So manually append those properties in your connectionstring when you set in code.

Add SSIS Task

Now let’s add task in our SSIS Package instance. To create SSIS Task programmatically in code, you need to know the exact type name (i.e. creation name). As we discussed earlier check your dtsx file to know the creation name for the task you like to create. In the below code we used a friendly name which is not supported in SSIS 2012 so for that you must use full name. For SSIS 2014 or higher you can use friendly names.

You can also find it in file like this (example of SSIS 2017 and 2019 folders)
C:\Program Files (x86)\Microsoft SQL Server\140\DTS\UpgradeMappings\ZappySys.PowerPack140.Extensions.xml
C:\Program Files (x86)\Microsoft SQL Server\150\DTS\UpgradeMappings\ZappySys.PowerPack150.Extensions.xml


Execute SSIS Package

Now let’s execute the package using the below line

Check Execution Errors

At the end you need to check execution errors if any.


Full Code

Here is the full code



Posted in SSIS PowerPack, SSIS SFTP Task, SSIS Tips & How-Tos and tagged , , .