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
// Upload a log text to the server
byte data = System.Text.Encoding.Default.GetBytes(logContent);
System.IO.MemoryStream ms = new System.IO.MemoryStream(data);
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
Tuesday, May 01, 2012
Step by step show you how to use the Ultimate FTP component in PowerShell to connect and transfer a file.
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:
To have our components listed in the VS References dialog, copy Ultimate assemblies into C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies\.
Add Ultimate assemblies into Global Assembly Cache (GAC) or copy the assemblies to C:\Program Files\Microsoft SQL Server\90\DTS\Binn.
To use the component in SSIS Script Task, add reference to the needed components in the Project Explorer window.