SSIS 2008 - send FTP file in MyData_<today> format

Question

Post reply

SSIS 2008 - send FTP file in MyData_<today> format

Veronka

Default port

Points: 1425
More actions
June 20, 2011 at 1:42 pm

#242419

Hello,
I need to create a daily feed to push data to a FTP server. The file format needs to be dynamic. MyData_followed by today's date...
So far I have
1) Data Flow task to extract data from the DB and dump it into a flat file (MyData_TEST) (this file name should be dynamic)
2) FTP task --> push MyData_TEST to the ftp server
I am REALLY NEW to SSIS. Kindly ask for detailed instructions.
Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Veronka Default port Points: 1425 More actions · Answer 1

Ok. I solved the first part: Write into a dynamic file name.

http://www.mssqltips.com/tip.asp?tip=1084

Still having a problem sending dynamic file to FTP server.

Advice?

Thanks.

Veronka Default port Points: 1425 More actions · Answer 2

okay, I solved the problem. Step by ste intructions:

1) Drag Drop a Data Flow Task on Control Task

2) Double Click on it and drag a ADO NET Source container

3) Connect to your server and write the query to get the date from the DB

4) Drag a Flat File Destination Container (specify a dummy .txt file name for now)

6) Create a new variable of type string and specify the folder name where the file will be dumped

(Variables window is adjacent to Toolbox window. If you don't see it go to Menu --> SSIS --> Variables. Then expand the Window, Create New Variable DataSourcePath, change the Type to String and enter C:\Temp as a value).

7)To dynamicaly create the text file: Select Flat File Connection Manager --> Properties --> Expressions --> Click on ellipsis button. Select Connection String from the dropdown in the Properties column. Click Ellipsis to open Expression Builder. Set the expression to @[User::DataSourcePath] + "TEST_" + (DT_WSTR, 2) DATEPART( "MM", getdate() ) + (DT_WSTR, 2) DATEPART( "DD", getdate() ) + (DT_WSTR, 4) DATEPART( "YYYY", getdate() ) + ".txt" Click Evaluate Expression to check it.

8) On Control Flow tab, drag a FTP task

9) Setup a connection to FTP server

10) To grab the dynamically created file: In FTP Task Editor Window --> File trasfer tab --> Set IsLocalPathVariable to true. Local Variable --> Create a new variable FTPSourceFile. Leave the value Empty.

10) In the Variables Window, select FTPSourceFile --> F4 to see Properties Winow --> Set EvaluateAsExpression to True. Expression property --> @[User::DataSourcePath] + "TEST_" + (DT_WSTR, 2) DATEPART( "MM", getdate() ) + (DT_WSTR, 2) DATEPART( "DD", getdate() ) + (DT_WSTR, 4) DATEPART( "YYYY", getdate() ) + ".txt"

This worked for me. I am not sure this is the most efficient way, but it works.

PRADEEP KUMAR HT Grasshopper Points: 21 More actions · Answer 3

HI,

Is it possible to implement encryption of data present in file in SSIS 2008? Requirement is to extract data from database, encrypt the data before writing into file and send file over mail/ftp. Also, need to know how to decrypt the data present in the file using SSIS 2008? Is it possible?

Thanks in advance