ComponentPro Blog

Posts Tagged ‘ssis’

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

Wednesday, August 07, 2013

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 dont 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’.
  • 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 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 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 dont 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

Tags //  ftp  sftp  ssis  

Ultimate FTP in Microsoft Windows PowerShell

Tuesday, May 01, 2012

Step by step show you how to use the Ultimate FTP component in PowerShell to connect and transfer a file.

Tags //  ftp  powershell  sql  ssis  

How to use the Ultimate components in SSIS package

Wednesday, March 16, 2011

In order to use the Ultimate components in SSIS script task, you may need to register the components to SSIS and add reference to the components in the Script Task. The following steps illustrate how to register and use the Ultimate DLLs in SSIS package:

  1. To have our components listed in the VS References dialog, copy Ultimate assemblies into C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies\.
  2. Add Ultimate assemblies into Global Assembly Cache (GAC) or copy the assemblies to C:\Program Files\Microsoft SQL Server\90\DTS\Binn.
  3. To use the component in SSIS Script Task, add reference to the needed components in the Project Explorer window.

Tags //  excel  ftp  mail  pdf  sftp  sql  ssis  zip