Use and deploy Ultimate SFTP components in SSIS package in Visual Studio

This topic illustrates how to add a "Script task" to an SSIS project in Visual Studio 2012 and deploy the SSIS Package to SQL Server 2012 and later. Older Visual Studio versions including VS 2008 and 2010 are also supported (see another topic for those versions).

Step 1: Create a new "Integration Services Project"

Open Visual Studio 2012 or later and create a new "Integration Services Project". If you don't see that project templates in your Visual Studio, you will need to download and install "Microsoft SQL Server Data Tools - Business Intelligence for Visual Studio 2012" or a later version.

Step 2: Add a Script Task

Drag-and-drop a Script Task from the SSIS toolbox to the Control Flow window

Step 3: Edit the newly added Script Task

• Right-click on the newly added Script Task and choose "Edit Script...".
• Add needed DLLs (e.g. ComponentPro.Ftp.dll, ComponentPro.Network.dll, ComponentPro.FileSystem.dll) to GAC. It should be done on any computer the SSIS package is intended to run! Hint: use ‘gacutil –i assemly.dll’.
• Add references to these assemblies in the newly opened script
• Start writing your script. An example of the script is as follows:

using (var client = new ComponentPro.Net.Ftp())    
{
	// Connect to an FTP server
	client.Connect("demo.componentpro.com");          

	client.Login("test", "test");

	// Upload a log text to the server
	byte[] data = System.Text.Encoding.Default.GetBytes(logContent);

	System.IO.MemoryStream ms = new System.IO.MemoryStream(data);

	client.UploadFile(ms, remotePath);
}

The screenshot below shows how the code is written in Script Task:

Step 4: Build Integration Services Project and prepare for deploying to the SQL Server

Now switch back to the Integration Services Project and build it, you should then see:

Step 5: Deploy the package

• Right-click on the Integration Services Project in VS2012 and choose Deploy
• Follow the Wizard steps to deploy your package to the SQL server

Step 6: Ensure that SSISDB is created under Integration Services Catalogs in SQL Server 2012

Right-click on Integration Services Catalogs in SQL Server 2012 and choose Create Catalog if you don't see any nodes under "Integration Services Catalogs"

Step 7: Add a new job to SQL Server Agent\Jobs

• Add a new job to SQL Server Agent\Jobs
• Create a new step and choose "SQL Server Integration Services Package" from the Type dropdown list
• Choose SSIS Catalog from the Package source dropdown list
• Select your SQL server from the Server list below the Package source dropdown
• In the Package textbox, click on "..." to browse to your newly installed SSIS Package