October 22, 2009 at 8:07 am
Hey Guys,
I have to load few files everyday... I need help on onething.. filename will be Ex. individual_20091022.txt What I need to do is assign the filename in some variable and then use that variable to update a Log-Table and then load that file and update the Log-Table once again after the file is loaded. This is my first package and has never done the scripting task... Thanks guys for the help.
October 22, 2009 at 9:19 pm
You've got a couple of options. The simplest, to be used if you only need to use the date-embedded filename once, is to create an expression on the ConnectionString property of the output file connection. To do this, open your file connection, open the Expressions window, and create your custom expression. Assuming that you have a variable named FileName in your package which contains the base filename, you could use something similar to the expression below:
REPLACE( @[User::Filename] , ".txt", RIGHT("00" + (DT_STR, 2, 1252)DATEPART("MONTH", GETDATE()), 2)
+ RIGHT("00" + (DT_STR, 2, 1252)DATEPART("DAY", GETDATE()), 2)
+ (DT_STR, 4, 1252)DATEPART("YEAR", GETDATE())
+ ".txt")
This will add the date formatted MMDDYYYY just before the extension (assuming it's a ".txt" file). The file "MyFile.txt" would become "MyFile10222009.txt" after the expression.
hth,
Tim
Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices
October 23, 2009 at 1:09 am
Tim, I think you may have misunderstood the requirement.
Sounds to me like the OP needs to read up on the use of FOREACH containers in iterating round folders of 0 or more files.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply