Solved

Using SSIS script task to create a text file

Posted on 2011-03-21
4
5,338 Views
Last Modified: 2013-11-10
Hi,

I need to my SSIS package to create a dynamic text file on the local file system that will be referenced by a subsequent Execute Process task. Specifically I want my Execute Process task to run the WinSCP process to use a script file (stored as a plain text file) to upload a zip file to an sftp server. The problem is that the name of the zip file changes every day according to the current date so I need the WinSCP script file to change its contents every day to make sure the correct zip file is uploaded to the sftp server.

My WinSCP script file will contain something like this:
option batch on
option confirm off
open sftp://Me:Password@192.168.1.1:22 -hostkey="ssh-rsa 1024 68:87:fa:79:2f:70:2c:28:f0:3f:a7:3b:9a:16:b9:00"
put "D:\SSIS\DATA\ARCHIVE\MyReport_20110321.zip"
exit

It is the put statement that needs to be dynamic.

If we say that the WinSCP script file is "D:\SSIS\WinSCP\Scripts\MyReportScript.txt" can someone please give me detailed instructions on how to write a script task that will delete and recreate "D:\SSIS\WinSCP\Scripts\MyReportScript.txt" every day, using a pre-set package level variable (lets call it MyReportFileName) to construct the put statement? I think the return characters to terminate each line are important too.

I know how to delete the file if it exists using the following (VB) code:
If File.Exists("D:\SSIS\WinSCP\Scripts\MyReportScript.txt") Then
            File.Delete("D:\SSIS\WinSCP\Scripts\MyReportScript.txt")
End If

But when it comes to writing lines to a new text file, I'm not sure what to do. Your help will be much appreciated!

Thanks in advance.
0
Comment
Question by:irb56
  • 2
4 Comments
 
LVL 16

Accepted Solution

by:
carsRST earned 500 total points
ID: 35183699
You'll probably have to use scripting.  Few options.

One option is to create the text file and just replace the necessary data.
Add a script task and use function below (code window) - translate to VB.NET if needed.  You can use it to open your file, change the contents, save and close the file.  Want I would do is set up a dummy line in your text file that the function can easily find and just replace.

If you're passing in a variable, you'll need to set the property on your script task ReadOnlyVariables and select the one or ones you plan on using.  Below is the line you can use to read that variable in your script.
Dts.Variables["User::VariableName"].Value (where "VariableName" is the name of your variable)

After your process you can change your text file back to your dummy statement, so that the next time it runs it can look for that line to replace.  Really it's just a matter of copying out your script file and reversing the process in the code.

Other alternative is to create the file every time.  More work, in my opinion.  But you can use the "File System" task to delete a file.  Add that task and change the operation to "Delete File."

Then just write  the code, in a script task, to create the text file each time.  Below is basic .NET code to write out to a text file.
http://www.astahost.com/info.php/Text-File-Operations-Vbnet_t6372.html

//replace function
        static public void ReplaceInFile(string filePath, string searchText, string replaceText)

        {

            StreamReader reader = new StreamReader(filePath);

            string content = reader.ReadToEnd();

            reader.Close();

 

            content = Regex.Replace(content, searchText, replaceText);

 

            StreamWriter writer = new StreamWriter(filePath);

            writer.Write(content);

            writer.Close();

        }

Open in new window

0
 
LVL 16

Expert Comment

by:AlexPace
ID: 35184711
If you are able to use Robo-FTP instead of WinSCP as the SFTP client you could have the script logic calculate the filename based on the current date like this:
;; build expected filename
SETEXTRACT day = %date "-" 1
SETEXTRACT month = %date "-" 2
SETEXTRACT year = %date "-" 3
SET ReportName = "MyReport_20" + year + month + date + ".zip"

WORKINGDIR "D:\SSIS\DATA\ARCHIVE"
GETFILE ReportName  ;; Is file actually here?
IFERROR!= $ERROR_SUCCESS GOTO done
FTPLOGON "192.168.1.1" /servertype=SFTP /user="Me" /pw="Password" /trust=all
SENDFILE ReportName
FTPLOGOFF

:done
EXIT

Open in new window


... or if you are certain you want to figure the filename to be sent in an external process you could use a tiny script that reads the name of the file to send from a command line argument like this:
TPLOGON "192.168.1.1" /servertype=SFTP /user="Me" /pw="Password"
SENDFILE %1 ;; note: first command line arg is autosaved as %1
FTPLOGOFF
EXIT

Open in new window

0
 
LVL 16

Expert Comment

by:AlexPace
ID: 35184828
first command in that second script should have been FTPLOGON instead of TPLOGON
0
 

Author Closing Comment

by:irb56
ID: 35188518
Thanks for the responses. I'm quite happy with WinSCP as the SFTP client in use in my SSIS packages so I went with the script task to recreate the WinSCP script file. For reference, the code (VB) was as below. The package variable "FinalReportName" was preset outside of the script task. It all works just fine. Many thanks for your assistance!  :-)

If File.Exists("C:\SSIS\MyReport_SFTP_Upload_Script.txt") Then
            File.Delete("C:\SSIS\MyReport_SFTP_Upload_Script.txt")
        End If

        Dim putLine As New String("put " & Dts.Variables("FinalReportName").Value)
        Dim writer As New StreamWriter("C:\SSIS\MyReport_SFTP_Upload_Script.txt")
        writer.WriteLine("option batch on")
        writer.WriteLine("option confirm off")
        writer.WriteLine("open sftp://Me:Password!@192.168.1.1:22 -hostkey=""ssh-rsa 1024 68:87:fa:79:2f:70:2c:28:f0:3f:a7:3b:9a:16:b9:00""")
        writer.WriteLine(putLine)
        writer.WriteLine("exit")
        writer.Close()
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

815 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now