Using and Deploying Ultimate SFTP and FTP components in SSIS package in Visual Studio 2012

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 and create a new "Integration Services Project." If you don't see that project templates in your VS2012, you will need to download and install "Microsoft SQL Server Data Tools - Business Intelligence for Visual Studio 2012".

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> where <assembly.dll> is the name of the DLL you want to register (e.g., ComponentPro.Sftp.dll).
• Add references to these assemblies in the newly opened script

Then you can 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 the deployment 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 to 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 to 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