November 11, 2011 at 2:50 am
Hello, I have a package that exports some data into a flat file, then encrypts and sends using an SFTP task. How and where in the process can I update the filename to the following format <supplier>_<ddmmyyyyhh24miss>_<sequencenum>.pgp because it looks like the components need specified filenames etc. I will also need to increment the sequence number every time i process... thanks!
November 11, 2011 at 6:18 am
Create a variable called FileName (or whatever you would like to call it) and set evaluate to expression as true.
Then you can define your dynamic filename in the expression, for example:
"c:\\folder1\\folder2\\filename"+ (DT_STR, 50, 1252) GETDATE()+".pgp"
Notice how I have used a double backslash - this is an escape for a backslah in a string.
You can then set this variable in the expressions of your file destination (I think the exoression is ConnectionString). This will create a file with a datestamp down to the millisecond.
You can use this method for other custom/dynamic names - this is just a simple example 🙂
November 14, 2011 at 12:17 am
Regarding the sequence number:
you'll need to store this somewhere outside the package, probably some sort of configuration file or inside the SQL Server database. You cannot store this inside the package: variables will always get their initial value at starttime.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 14, 2011 at 9:34 am
what i've ended up doing is storing the information that i need and then using a sql statement to concatenate the columns to create a filename, how can I output my filename from my query to pass into my components?
November 15, 2011 at 11:28 pm
jhowe3 (11/14/2011)
what i've ended up doing is storing the information that i need and then using a sql statement to concatenate the columns to create a filename, how can I output my filename from my query to pass into my components?
This article explains how to pass SSIS variables to and from an Execute SQL Task:
http://www.simple-talk.com/sql/ssis/passing-variables-to-and-from-an-ssis-task/
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 16, 2011 at 2:09 am
ok i'll have a look at that thanks... that article was a big help! 😉
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply