Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to modify filename SSIS FTP Expand / Collapse
Author
Message
Posted Tuesday, February 10, 2009 7:57 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 20, 2009 4:51 PM
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.

Post #654375
Posted Tuesday, February 10, 2009 8:21 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, April 07, 2014 10:43 AM
Points: 1,042, Visits: 2,708
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, SQL Server MVP
Independent Business Intelligence Consultant
www.TimMitchell.net
@Tim_Mitchell

Post #654380
Posted Tuesday, February 10, 2009 8:26 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 20, 2009 4:51 PM
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?

Post #654382
Posted Monday, February 23, 2009 11:07 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 5:58 PM
Points: 182, Visits: 398
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..



Post #662792
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse