Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Integration Services C# Script Task Example


By:   |   Read Comments   |   Related Tips: 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

  1. Read the two previous tips - see Part 1 and Part 2.
  2. SQL Server Integration Services is installed.
  3. SQL Server Data Tools Installed (SSDT) or BIDS (Business Intelligence Development Studio) is installed.
  4. 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.

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.

File system task

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

execute the File System Task

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

Files copied

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

The script task

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

Adding references

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.

File system source connection

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

File Connection Manager

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.

destination connection

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

File Connecion Manager

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(); 
new ssis package created

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; 

Adding the file system task

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);

Use of variables set to 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;

Connection Managers

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);

Connection created

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);

destination path

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");

File System operation

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);

Overwrite destination file property

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;

Executing the package

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


Last Update:






About the author
MSSQLTips author Daniel Calbimonte Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and Microsoft Certified IT Professional.

View all my tips





More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools