SQL Server Integration Services C# Script Task Example

By:   |   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

  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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Daniel Calbimonte 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



Comments For This Article




Monday, April 30, 2018 - 7:04:46 PM - Daniel Back To Top (75827)

Sí se puede. Tienes que usar expresiones SSIS.


Wednesday, March 28, 2018 - 6:18:38 PM - Brian Back To Top (75548)

 Buenas Tardes, una consulta serìa posible copiar de un ftp a una carpeta local, el ftp  contiene archivos en formato csv , con nombre Lecturas_20180227072401,Lecturas_20180228072404,Lecturas_20180229072401, solo quiero copiar ejm: de fecha 27, depues al otro dia de fecha28. el otro dia de fecha 29 y asi sucesivamente, despues mes de marzo,abril, y asi sucesivamente seria posible? _20180227_ ahi esta la fecha y todos los archivos tendran ese formato ( no se puede eliminar archivos del ftp, solo copiar) 















get free sql tips
agree to terms