Use SFTP (WinSCP) within SSIS

Posted: June 13, 2014 | Author: Jingyang Li | Filed under: SQL SSIS | Leave a comment

Download WinSCP from: http://winscp.net/eng/download.php

You can add a Script Component in your SSIS package and use the sample code below:


public void Main()

        {

            // TODO: Add your code here

            try

            {
                const string logname = “E:\\myFolder\\ftp_log.xml”;

                //// Run hidden WinSCP process

                Process winscp = new Process();

                winscp.StartInfo.FileName = “C:\\Program Files (x86)\\WinSCP\\winscp.com”;
                winscp.StartInfo.Arguments = “/log=\”” + logname + “\””;

                winscp.StartInfo.UseShellExecute = false;

                winscp.StartInfo.RedirectStandardInput = true;

                winscp.StartInfo.RedirectStandardOutput = true;

                winscp.StartInfo.CreateNoWindow = true;

                winscp.Start();
                //// Feed in the scripting commands

                winscp.StandardInput.WriteLine(“option batch abort”);

                winscp.StandardInput.WriteLine(“option confirm off”);

                winscp.StandardInput.WriteLine(“open myUser@sftp.mysftpsite.com”);

                winscp.StandardInput.WriteLine(“put E:\\myFolder\\data\\myFiles.txt”);
                winscp.StandardInput.WriteLine(“mv myFiles.txt myNewFile_” + System.DateTime.Now.Month.ToString() + “_” + System.DateTime.Now.Day.ToString() + “_” + System.DateTime.Now.Year.ToString() + “.csv”);

                winscp.StandardInput.Close();
                // Collect all output (not used in this example)

                string output = winscp.StandardOutput.ReadToEnd();
                // Wait until WinSCP finishes

                winscp.WaitForExit();

                Dts.TaskResult = (int)ScriptResults.Success;

            }

            catch (Exception xxx) {

                Dts.TaskResult = (int)ScriptResults.Failure;

            }

} }

From the WinSCP login client UI, you use myUser/mypass to connect your site and save your connection information as this:
myUser@sftp.mysftpsite.com
And your package should be ready to go. You can go WInSCP website to find other configuration as well.
The last setup is to make sure the package can run as an agent job. What I did was to connect to my ftp site and saved the credentials (user/pass) on my local machine and export the settings from WinSCP:>>Tools>>Export/backup Configuration to WinSCP.ini file and I put this file to WinSCP root folder and gave the service account user access permission to access this folder. You can modify path information within this WinSCP.ini file.
Don’t forget to give the permission to the data folder for the account who is running the agent service!

After create the account, from Edit>>Advanced>>Directories check Remote directory and Local directory (working folder for the file).

SSIS Package to Archive File to a destination wiht a name appended with datetime stamp

Posted: June 11, 2014 | Author: Jingyang Li | Filed under: SQL SSIS | Leave a comment

When we have a package to copy file to a new location, we may want to keep different copy for each time. We can append a datetime stamp to the file name.
Here are a few steps to follow:

1. Create two variables for the package one for destination folder (Dest_Dir) and one for destination file name (Dest_File);
2. Create a new file connection from Connection Manager and use create file property and give it a name, any name;
3. From the property window of the connection just created: go to +Expression…:
Choose ConnectionString from Property Dropdownbox and use the following as expression:

@[Dest_Dir] + “\\” + @[Dest_File] + “_”
+ (DT_WSTR,4)DatePart(“yyyy”, GetDate()) + RIGHT(“0”
+ (DT_WSTR,2)DatePart(“mm”, GetDate()), 2) + RIGHT(“0”
+ (DT_WSTR,2)DatePart(“dd”, GetDate()), 2) + RIGHT(“0”
+ (DT_WSTR,2)DatePart(“hh”, GetDate()), 2) + RIGHT(“0”
+ (DT_WSTR,2)DatePart(“mi”, GetDate()), 2) + “.txt”

You may need to make it in one row:

@[Dest_Dir] + “\\” + @[Dest_File] + “_” + (DT_WSTR,4)DatePart(“yyyy”, GetDate()) + RIGHT(“0” + (DT_WSTR,2)DatePart(“mm”, GetDate()), 2) + RIGHT(“0” + (DT_WSTR,2)DatePart(“dd”, GetDate()), 2) + RIGHT(“0” + (DT_WSTR,2)DatePart(“hh”, GetDate()), 2) + RIGHT(“0” + (DT_WSTR,2)DatePart(“mi”, GetDate()), 2) + “.txt”

4. From a System Task Editor, you can copy file from your source to the destination we created above to archive
the file from source and rename it with datetime appended to it so we will not overwrite it next time to copy file to this folder.

Cannot Download WSDL file for Web Services task in SSIS

Posted: June 5, 2014 | Author: Jingyang Li | Filed under: SQL SSIS | Leave a comment

I still have this issue when I migrate SSIS packages from 2008 to 20014.
I have used the method mentioned from the below link and use the external WSDL file saved from an IE browser for Web Services Task as the source without overwrite.
Type your web service link to an IE browser:
https//thesite.com/myservice.asmx?WSDL
and save the xml file as your yourwebservicefilename.wsdl file.

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/8e76f6e0-c61a-469c-bcc6-588c561b1565/wsdl-file-for-web-services-task?forum=sqlintegrationservices

Edit:
When deploy the package to use webservice, the webservice component needs to be refreshed to point to the updated wsdl file and find the correct method for the component.

SSIS or T-SQL Stored Procedure

Posted: March 4, 2014 | Author: Jingyang Li | Filed under: SQL Server 2008, SQL SSIS | Leave a comment

I have seen a solution to use dynamic SQL to migrate data into SQL Server database. But I feel the solution is pushing pretty hard in using T-SQL. It needs quite good of mastering of T-SQL skills to handle the customized task and for working with file names and scheduling, it becomes harder.
SSIS is a low cost ETL tool in SQL Server stack. It comes with many versions of SQL Server included into the server licence. It is easy to handle many tasks which may pose some difficulties with pure T-SQL approach.
I came across a summary of the comparison of these two from Infosys, which is a good source to have a quick overview of this topic.
Here is the link to that document:

Click to access sql-server-data-migration-approaches.pdf

Web Service Task — SSIS

Posted: October 21, 2013 | Author: Jingyang Li | Filed under: SQL Server 2008, SQL SSIS | Leave a comment

You can find generate information about Web Service Task:

http://msdn.microsoft.com/en-us/library/ms140114(v=sql.105).aspx

http://msdn.microsoft.com/en-us/library/ms181267(v=sql.105).aspx

http://msdn.microsoft.com/en-us/library/ms187617(v=sql.105).aspx

You can check out a step by step walk-through from this link:

http://www.bidn.com/blogs/BillBrannen/bidn-blog/619/ssis-web-service-task-step-by-step

How to Add New Column to Existing Flat File in SSIS

Posted: September 9, 2013 | Author: Jingyang Li | Filed under: SQL SSIS | Leave a comment

When you have a need to add new column to your existing text file through your SSIS package, here is how:
Open the Flat File Connection Manager and click Advanced tab >> click New and give a name on the right panel
and it will refresh left list. You can make changes to data type property from the right panel.
If you want to add new column to the middle of the column, you need to click on the double-arrow next to New to choose insertion position.

Schedule a Job To Run SSIS Package to Delete Old Files with Robocopy

Posted: March 25, 2013 | Author: Jingyang Li | Filed under: My IT tools, SQL Server 2008, SQL SSIS | Leave a comment

Step 1: Create a bat file (saved as TheDeleteFile.bat from notepad in this example) in the following format (use your own file paths)

IF NOT EXIST \\myShare\SQLData\empty mkdir \\myShare\SQLBackups\empty
Robocopy \\myShare\SQLData\backups \\myShare\SQLData\empty /e /MOVE /create /MINAGE:15 /LOG+:C:\jobfiles\log\%date:~-4,4%%date:~-10,2%%date:~-7,2%_log.txt
rmdir \\myShare\SQLData\empty /s /q

Any files in the \\myShare\SQLData\backups folder that are 15 days old will be deleted. You can change the number 15 to the number you desire.

Step 2: Design an SSIS Package with Execute Process Task component:

The major Process Properties:
RequiredFullFileName: True
Executable: C:\myjobfile\TheDeleteFile.bat
Arguments: ..\bat
WorkingDirectory: C:\myjobfile

Step 3: Set up a job from SSMS to run the SQL Server Intergration Package designed from above step and schedule a time to run this job.

References:

http://texhex.blogspot.com/2009/09/misusing-robocopy-to-delete-old-files.html

http://www.codesingh.com/2009/08/using-robocopy-to-delete-old-files-from.html

http://blogs.msdn.com/b/jjameson/archive/2009/11/07/using-robocopy-to-move-files-and-folders.aspx

To run a PowerShell Script from SSIS package

Posted: September 28, 2011 | Author: Jingyang Li | Filed under: Powershell, SQL SSIS | 4 Comments

From this thread:

http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/216d2ee6-0f04-480f-808d-8241bc4a8d18/

To run a PowerShell Script from SSIS package. Add a “Execute Process Task” in SSIS and use the following command text.

This works just great, plus the “-ExecutionPolicy ByPass” switch will take care of any server script policies.

C:\Windows\System32\WindowsPowerShell\v1.0\PowerShell.exe -ExecutionPolicy ByPass -command “. ‘L:\Powershell_Script\Script1.ps1’ ‘param1’ ‘param2′”

A Simple (Good) Sample with For Loop Container

Posted: September 13, 2011 | Author: Jingyang Li | Filed under: SQL SSIS | Leave a comment

I have been to this site a few times before and I found a lot good stuff related to SSIS.

Here is an entry for For Loop Container to set up with InitExpression , EvalExpression and AssignExpression.

For example, you can implement this C# for loop: for(int i=0; i<10; i++) with this For Loop Container.

http://www.sqlis.com/sqlis/post/For-Loop-Container-Samples.aspx

Schedule a Job for an SSIS package

Posted: April 27, 2009 | Author: Jingyang Li | Filed under: SQL SSIS | Leave a comment

After designing and testing your SSIS package at your development machine, you want to deploy it to your SQL Server machine to run as a job. You may have issues with the security settings associated with the job. Here is the required steps for how to set up a job to run the SSIS package through File System deployment.

1. Create a login account for the window account of your server your SQL Server is running at;

2.Create a user account for the user you created for the window account in database msdb;

3.Assign the user in msdb to SQLAgentUserRole, SQLAgentReaderRole, SQLAgentOperatorRole ;

4.Create a new credential for the window account (identity);

5.Under SQL Server Agent node(Proxies): Create a proxy account with the credential we created early and assign the proxy to SSIS Package Subsystem;You may need to assign the proxy account to the login under Principals selection with Add;

6.When you as window login are creating job under SQL Server Agent, you need to use SSIS Package as Type and Run as the proxy we created during your Step creation; Assign a schedule as you would with other jobs.

7. you can test run your job manually and wait for your schedule job to run if there is no other issue(You had better test your package in BIDS comes with SQL Server 2005).

Refer: Charles Kangai http://www.mydatabasesupport.com/forums/sqlserver-dts/182974-ssis-schedule-jobs.html

Hongwei Li: http://www.codeproject.com/KB/aspnet/Schedule__Run__SSIS__DTS.aspx

Jingyang Li

SQL Server