How to modify filename SSIS FTP

Question

How to modify filename SSIS FTP

danthorngren

SSC Veteran

Points: 280
More actions
February 10, 2009 at 7:57 pm

#126367

Working on my first SSIS project, I have the package working except for one process. I assume it is simple, but I do not see how its done.
This package will be a job that runs everyday, it simply gets a file via FTP, then processes that file with a series of stored procedures, I'm sure quite common.
Like I said I have it all working except the file name needs to change every day. This is how I have been doing it, I get the file name in SQL Server 2005.
SELECT 'inv.open.datafile.' + CONVERT(VARCHAR(6), GETDATE() -1, 12) AS [YYMMDD]
For today that sets the file name = inv.open.datafile.090209
I have been trying to run an "Execute SQL Task" before the FTP task and somehow set a variable to that SQL statement but I am missing something. OR maybe I am screwing up the properties on the FTP task to use the variable??
Does anyone know or know of a tutorial to do this?
Thanks for any help.

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

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

Tim Mitchell SSCoach Points: 15681 More actions · Answer 1

There are several ways you can do this. The way I usually do this is to FTP the file down to a working directory, and then use a ForEach Loop to process the file (or files, if more than one) from that working directory. Once the file is processed, you can use a File System Task to move the file from the working directory to an archive directory.

Feel free to post more info if this won't solve your problem.

hth,

Tim

Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices

danthorngren SSC Veteran Points: 280 More actions · Answer 2

Thanks Tim,

My real issue is just the one step in this process. How do I use the SQL Select statement as the file name for the ftp process? So just before the ftp happens the file name is generated using that statment?

MarkG Ten Centuries Points: 1237 More actions · Answer 3

Create a variable, Fname (or whatever), that is scoped to the Package. Set the property, EvaluateAsExpression to True.

Set the Expression to be (and I may need help here but there are plenty of examples of Date Expressions in the forum): "My.Filename." + (DT_STR, 4, 1252) YEAR(@[System::StartTime])

+ RIGHT("0" + (DT_STR, 2, 1252) MONTH(@[System::StartTime]), 2)

+ RIGHT("0" + (DT_STR, 2, 1252) DAY(@[System::StartTime]), 2)

You can tweek that to use GetDate() instead of the System::StartTime, again look for examples here.. I believe you need to use DateAdd("Day", -1, GetDate()) instead of GetDate() -1, and you may need the "" around "Day" for use in SSIS.

Once you have the variable correctly defined:

Create an FTP Task and under the File Transfer option set the IsRemotePathVariable to True.

Set the RemoteVariable to the variable just defined above. It should appear as User::Fname, using the name above.

Fill out the rest of the appropriate options for the Ftp Task.

To follow up on a previous recommendation, I would download the file to a local folder, import that file into a staging table - stgDailyImport (or whatever), truncating the table at the beginning of the operation and once the data is imported into that table, archive the txt file then continue with your normal processing, referencing the stgDailyImport.

Hope it helps..