Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to modify filename SSIS FTP


How to modify filename SSIS FTP

Author
Message
danthorngren
danthorngren
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 24
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.
Tim Mitchell
Tim Mitchell
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1122 Visits: 2912
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
danthorngren
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 24
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
MarkG
SSC-Enthusiastic
SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)

Group: General Forum Members
Points: 182 Visits: 469
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..



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search