SSIS C# Script Task Example

By: Daniel Calbimonte | Comments (2) | Related: More > Integration Services Development

Problem

I would like to learn how to create a SQL Server Integration Services (SSIS) Package that will run tasks using code. Can you help me with an example of how to complete a task with native SSIS features and with C# code?

Solution

Yes, we can. In this new tip (third in the series - see Part 1 and Part 2), we will learn how to create an SSIS package using C# code, how to add tasks and execute them. If you do not know how to use the SSIS script tasks, we recommend you to read the first and second tips about the script task. This tip requires some knowledge taught in previous tips.

In this tip, we will create a package and add the file system task to copy a file from a source to a specified folder using C#.

Requirements

Read the two previous tips - see Part 1 and Part 2.
SQL Server Integration Services is installed.
SQL Server Data Tools Installed (SSDT) or BIDS (Business Intelligence Development Studio) is installed.
You can use SQL Server 2005 or later versions of the relational database engine. In this example, we are using SQL Server 2014.

Getting started

Step 1 - In this example, we are going to create a package programmatically and copy a file from a source to a destination folder. To understand the program, we are going to do the process visually and then programmatically.

Step 2 - In order to start, drag and drop the File System Task.

Step 3 - Double click on the task and specify the following properties. The File System Task will copy the db1_test.bak backup to the scriptdestination folder.

Step 4 - In order to test it, execute the task.

Step 5 - You can now see that the destination file was copied successfully.

Step 6 - The next step is to do the same thing using code in the Script Task. Drag and drop the script task.

Step 7 - In the script reference, add the Microsoft.SqlServer.FileSystemTask.

Step 8 - In the namespaces region, add this line of code:

using Microsoft.SqlServer.Dts.Tasks.FileSystemTask;

Step 9 - Write the following code.

try 
{
// TODO: Add your code here

// Source file
String sourcePath = @"C:\scripts\db1_test.bak"; 

// Destination folder
String destinationPath = @"C:\scriptsdestination";

//Create a package
Package myPackage = new Package();

//Add the filesystemtask to the package
Executable myExecutable = myPackage.Executables.Add("STOCK:FileSystemTask");

//Adding the Taskhost and associating with the FileSystemTask 
TaskHost myTaskHost = myExecutable as TaskHost;

//Setting to false the option to use variables for the source and destination paths
myTaskHost.Properties["IsSourcePathVariable"].SetValue(myTaskHost, false);
myTaskHost.Properties["IsDestinationPathVariable"].SetValue(myTaskHost, false);

//Create a connection file
ConnectionManager myConnection = myPackage.Connections.Add("FILE");

//Specification of the Name and the connection 
myConnection.Name = "db1_test.bak;
myConnection.ConnectionString = sourcePath;

//Specification of the use of the source file and setting the values
myConnection.Properties["FileUsageType"].SetValue(myConnection, DTSFileConnectionUsageType.FileExists);
myTaskHost.Properties["Source"].SetValue(myTaskHost, myConnection.Name);

//Specification of the Name and the destination connection 
ConnectionManager myDestConnection = myPackage.Connections.Add("FILE");
myDestConnection.Name = "scriptsdestination";
myDestConnection.ConnectionString = destinationPath;

//Specification of the use of the folder path and setting the values
myDestConnection.Properties["FileUsageType"].SetValue(myDestConnection, DTSFileConnectionUsageType.FolderExists);
myTaskHost.Properties["Destination"].SetValue(myTaskHost, myDestConnection.Name);

//Specify the copy file operation
myTaskHost.Properties["Operation"].SetValue(myTaskHost, DTSFileSystemOperation.CopyFile);
myTaskHost.Properties["OperationName"].SetValue(myTaskHost, "Copy File");

//Overwrite the file if it already exists
myTaskHost.Properties["OverwriteDestinationFile"].SetValue(myTaskHost, true);
DTSExecResult result = myPackage.Execute();
Dts.TaskResult = (int)ScriptResults.Success;
}

// Showing the error message
catch (Exception ex)
{
Dts.Events.FireError(18, "The process failed", ex.ToString(), "", 0);
Dts.TaskResult = (int)ScriptResults.Failure;
}

Step 10 - To understand this code, we are going to explain it comparing with the File System Task created visually. The sourcePath specifies the source connection to be used.

String sourcePath = @"C:\scripts\db1_test.bak";

When we specify the source connection, we need to specify the path of the file to copy.

When you create a connection, it is necessary to specify the path.

Step 11 - The next line of code is used to define the destination.

String destinationPath = @"C:\scriptsdestination

This is the path used to define the destination folder of the file to be copied.

Step 12 - In the example, we will use the scriptdestination folder.

Step 13 - The following line of code creates a package. The package is the container of all the tasks, connections, flows and configuration.

//Create a package
Package myPackage = new Package();

Step 14 - The next lines add the FileSystem Task to the package. The TaskHost is used to set the properties of a task.

//Add the filesystemtask to the package
Executable myExecutable = myPackage.Executables.Add("STOCK:FileSystemTask");
//Adding the Taskhost and associating with the FileSystemTask
TaskHost myTaskHost = myExecutable as TaskHost;

Step 15 - You can specify the connections creating a path or using variables. In this example, we are not using variables, so the value is false.

//Setting to false the option to use variables for the source and destination paths
myTaskHost.Properties["IsSourcePathVariable"].SetValue(myTaskHost, false);
myTaskHost.Properties["IsDestinationPathVariable"].SetValue(myTaskHost, false);

Step 16 - The following lines of code create a file connection. This is done by default when you select a path visually in steps 10 and 11. In code, you need to specify the connection name and specify the source path. If you go to the Connection Manager, you can see that the connection are created automatically.

//Create a connection file
ConnectionManager myConnection = myPackage.Connections.Add("FILE");
//Specification of the Name and the connection
myConnection.Name = "db1_test.bak";
myConnection.ConnectionString = sourcePath;

Step 17 - The FileUsageType is used to verify if the File Exists or it needs to be created. In this case, the File exists. The other line of code related to myTaskHost is to set the values of step 16.

//Specification of the use of the source file and setting the values
myConnection.Properties["FileUsageType"].SetValue(myConnection,
DTSFileConnectionUsageType.FileExists);
myTaskHost.Properties["Source"].SetValue(myTaskHost, myConnection.Name);

Step 18 - These lines of code are similar to the ones on step 16, but for the destination file. We are creating a connection in the connection manager with the destination folder.

//Specification of the Name and the destination connection
ConnectionManager myDestConnection = myPackage.Connections.Add("FILE");
myDestConnection.Name = "scriptsdestination";
myDestConnection.ConnectionString = destinationPath;

Step 19 - The next option is to define if the folder exists or needs to be created and finally, set the connection values of step 18.

//Specification of the use of the folder path and setting the values
myDestConnection.Properties["FileUsageType"].SetValue(myDestConnection,
DTSFileConnectionUsageType.FolderExists);
myTaskHost.Properties["Destination"].SetValue(myTaskHost, myDestConnection.Name);

Step 20 - The Operation specifies if the files will be copied. You can move, delete, change the attributes or rename the file.

//Specify the copy file operation
myTaskHost.Properties["Operation"].SetValue(myTaskHost,
DTSFileSystemOperation.CopyFile);
myTaskHost.Properties["OperationName"].SetValue(myTaskHost, "Copy File");

Step 21 - The overwritedestination file property is used to overwrite the file if it already exists.

//Overwrite the file if it already exists
myTaskHost.Properties["OverwriteDestinationFile"].SetValue(myTaskHost, true);

Step 22 - Finally, we execute the package and if everything is all right, a successful message is displayed.

DTSExecResult result = myPackage.Execute();
Dts.TaskResult = (int)ScriptResults.Success;

Conclusion

Writing a SSIS package using code is not so easy, but with this tip I think it will be easier. I hope you understand the tip. If not, feel free to write your questions and comments below.

Next Steps

There are several links that will be useful to you:
Check out the previous tips in the series:
- Getting started with the SSIS Script Task
- Fire Events, Execute T-SQL Commands and Run SMO with the SSIS Script Task

About the author

Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and 6-time Microsoft Certified IT Professional. Daniel started his career in 2001 and has worked with SQL Server 6.0 to 2022. Daniel is a DBA as well as specializes in Business Intelligence (SSIS, SSAS, SSRS) technologies.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

SQL Server Integration Services C# Script Task Example