SSIS adding datetime to outbound file name

[JCCDevel]

I'm struggling with something that is probably pretty simple. I have
an SSIS package that creates a flat file from a SQL Server table. I
then take that file and FTP to a vendor. They have requested that I
append date and time to the file name so that they will know when it
is a new file. So, instead of "VendorFile.txt", it should look like
"VendorFile20081025.txt"

I think I need to create the file name as a variable but Im not sure
how I then use that variable in the FTP task. I have tried this a few
times and I get an error that that says the variable is missing the
file path - though when I look at it in the variable name it looks
right.

Can somebody tell me the steps to get this done

Thanks in advance for your help!

JCC

[Todd C]

Are you using SSIS to do the FTP process as well?



Click on the Connection Manager for the Flat File Destination. In the
Properties page, expand Expressions and create a new Expression for
ConnectionString. Set the Expression to be like the following:

"<path and filename>" + (DT_STR, 8, 1252)(YEAR(GETDATE()) * 10000 +
MONTH(GETDATE()) * 100 + DAY(GETDATE())) + ".txt"

A couple of notes: YEAR() * 10000 + Month() * 100 + DAY() will get you
today's date in the format YYYYMMDD. But you can use any format you want.

The (DT_STR, <length>, <code page>) (N) will convert N to a string that can
be concatenated with the file name.

HTH
--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]

Post by JCCDevel
I'm struggling with something that is probably pretty simple. I have
an SSIS package that creates a flat file from a SQL Server table. I
then take that file and FTP to a vendor. They have requested that I
append date and time to the file name so that they will know when it
is a new file. So, instead of "VendorFile.txt", it should look like
"VendorFile20081025.txt"
I think I need to create the file name as a variable but Im not sure
how I then use that variable in the FTP task. I have tried this a few
times and I get an error that that says the variable is missing the
file path - though when I look at it in the variable name it looks
right.
Can somebody tell me the steps to get this done
Thanks in advance for your help!
JCC

[JCCDevel]

Post by Todd C
Are you using SSIS to do the FTP process as well?
Click on the Connection Manager for the Flat File Destination. In the
Properties page, expand Expressions and create a new Expression for
"<path and filename>" + (DT_STR, 8, 1252)(YEAR(GETDATE()) * 10000 +
MONTH(GETDATE()) * 100 + DAY(GETDATE())) + ".txt"
A couple of notes: YEAR() * 10000 + Month() * 100 + DAY() will get you
today's date in the format YYYYMMDD. But you can use any format you want.
The (DT_STR, <length>, <code page>) (N) will convert N to a string that can
be concatenated with the file name.
HTH
--
Todd C
[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]

I'm struggling with something that is probably pretty simple.  I have
an SSIS package that creates a flat file from a SQL Server table.  I
then take that file and FTP to a vendor.  They have requested that I
append date and time to the file name so that they will know when it
is a new file.  So, instead of "VendorFile.txt", it should look like
"VendorFile20081025.txt"
I think I need to create the file name as a variable but Im not sure
how I then use that variable in the FTP task.  I have tried this a few
times and I get an error that that says the variable is missing the
file path - though when I look at it in the variable name it looks
right.
Can somebody tell me the steps to get this done
Thanks in advance for your help!
JCC- Hide quoted text -

- Show quoted text -

Hi Todd,

Thanks for your response. Yes, I am using SSIS for the FTP. I did add
a new expression for the flat file connection string that looks
similar to your code. I think maybe the issue is how I then set the
FTP task. I went into the FTP task, set "IsLocalPathVariable" to
true - but then what should I put in the local path? I suspect that
is my issue. I know the expression piece is correct becuase I can see
the renamed file in my outbouvd directory

Thanks for your time with this!

Julie

[Todd C]

Try this work-around:
Create a new String variable called FileName. Add a Script Task prior to the
data flow and in it, set the value of the FileName variable to what you had
in the Expression. You will need to edit the code because you are now working
in VB.Net, not SSIS Expression language, but it is a fairly straight forward
string to build.

Now, for the ConnectionManager ConnectionString property, set it equal to
the FileName Variable.

And lastly, in your FTP Task, set the IsLocalPathVariable property to True,
and you should have a property where you can select the Variable to use.

Keep us posted.
--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]

Post by JCCDevel

Post by Todd C
Are you using SSIS to do the FTP process as well?
Click on the Connection Manager for the Flat File Destination. In the
Properties page, expand Expressions and create a new Expression for
"<path and filename>" + (DT_STR, 8, 1252)(YEAR(GETDATE()) * 10000 +
MONTH(GETDATE()) * 100 + DAY(GETDATE())) + ".txt"
A couple of notes: YEAR() * 10000 + Month() * 100 + DAY() will get you
today's date in the format YYYYMMDD. But you can use any format you want.
The (DT_STR, <length>, <code page>) (N) will convert N to a string that can
be concatenated with the file name.
HTH
--
Todd C
[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]

Post by JCCDevel
I'm struggling with something that is probably pretty simple. I have
an SSIS package that creates a flat file from a SQL Server table. I
then take that file and FTP to a vendor. They have requested that I
append date and time to the file name so that they will know when it
is a new file. So, instead of "VendorFile.txt", it should look like
"VendorFile20081025.txt"
I think I need to create the file name as a variable but Im not sure
how I then use that variable in the FTP task. I have tried this a few
times and I get an error that that says the variable is missing the
file path - though when I look at it in the variable name it looks
right.
Can somebody tell me the steps to get this done
Thanks in advance for your help!
JCC- Hide quoted text -

- Show quoted text -

Hi Todd,
Thanks for your response. Yes, I am using SSIS for the FTP. I did add
a new expression for the flat file connection string that looks
similar to your code. I think maybe the issue is how I then set the
FTP task. I went into the FTP task, set "IsLocalPathVariable" to
true - but then what should I put in the local path? I suspect that
is my issue. I know the expression piece is correct becuase I can see
the renamed file in my outbouvd directory
Thanks for your time with this!
Julie

[JCCDevel]

Post by Todd C
Create a new String variable called FileName. Add a Script Task prior to the
data flow and in it, set the value of the FileName variable to what you had
in the Expression. You will need to edit the code because you are now working
in VB.Net, not SSIS Expression language, but it is a fairly straight forward
string to build.
Now, for the ConnectionManager ConnectionString property, set it equal to
the FileName Variable.
And lastly, in your FTP Task, set the IsLocalPathVariable property to True,
and you should have a property where you can select the Variable to use.
Keep us posted.
--
Todd C
[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]

Post by JCCDevel

Post by Todd C
Are you using SSIS to do the FTP process as well?
Click on the Connection Manager for the Flat File Destination. In the
Properties page, expand Expressions and create a new Expression for
"<path and filename>" + (DT_STR, 8, 1252)(YEAR(GETDATE()) * 10000 +
MONTH(GETDATE()) * 100 + DAY(GETDATE())) + ".txt"
A couple of notes: YEAR() * 10000 + Month() * 100 + DAY() will get you
today's date in the format YYYYMMDD. But you can use any format you want.
The (DT_STR, <length>, <code page>) (N) will convert N to a string that can
be concatenated with the file name.
HTH
--
Todd C
[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]

I'm struggling with something that is probably pretty simple.  I have
an SSIS package that creates a flat file from a SQL Server table.  I
then take that file and FTP to a vendor.  They have requested that I
append date and time to the file name so that they will know when it
is a new file.  So, instead of "VendorFile.txt", it should look like
"VendorFile20081025.txt"
I think I need to create the file name as a variable but Im not sure
how I then use that variable in the FTP task.  I have tried this a few
times and I get an error that that says the variable is missing the
file path - though when I look at it in the variable name it looks
right.
Can somebody tell me the steps to get this done
Thanks in advance for your help!
JCC- Hide quoted text -

- Show quoted text -

Hi Todd,
Thanks for your response. Yes, I am using SSIS for the FTP.  I did add
a new expression for the flat file connection string that looks
similar to your code.  I think maybe the issue is how I then set the
FTP task.  I went into the FTP task, set "IsLocalPathVariable" to
true  - but then what should I put in the local path?  I suspect that
is my issue.  I know the expression piece is correct becuase I can see
the renamed file in my outbouvd directory
Thanks for your time with this!
Julie- Hide quoted text -

- Show quoted text -

Hi Todd,

Thanks for your help! I think I am all set but the process is no
longer putting a copy of the outbound file in the directory - so I'm
not sure if the file was even created (though the SSIS packge results
looked good). would like to find a way to also keep a copy of the
outbound file

I really appreciate the time you have taken to help me!

Julie