Use SFTP (WinSCP) within SSIS

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).

Advertisements

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

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

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

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:
http://www.infosys.com/microsoft/resource-center/Documents/sql-server-data-migration-approaches.pdf


Web Service Task — SSIS

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

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

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