Appending to a filename from a table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SSIS and Import/Export (2008)

Appending to a filename from a table

Author

Topic

Sqlraider
Yak Posting Veteran

65 Posts

Posted - 2013-08-23 : 13:49:55

I'm new to SSIS so I'm not sure how to do this.

I'm going to run the SSIS package from a SQL Agent job.

A csv file is placed in for example: C:\Stage\filename.csv.

After Importing the file into a SQL Table I need to append to the filename the year and month it was processed for. The problem is the file shows up 1 - 2 months later. So I run an SQL query to pull the processed month I need to append to the filename.

Heres the query I run to retrive the processed month from the table, the column name is Date_ccyymm (char (6)):


select Top 1   Date_ccyymm
from calendar
where Process_Flg = 'N'
order by DateKey

The result of the query is Date_ccyymm = '201306'

Now I want to rename filename.csv to filename201306.csv.

How can I pass the result of an SQL Task to a variable to use in a File System Task?

Or is there an easier way?

Thanks,
SQLRAIDER

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2013-08-27 : 23:14:21

use File System Task to do a rename and use the expressions builder to rename the file name

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-28 : 03:31:33

put your query in execute sql task and add a variable to store result. set ResultSet as single row and in Results tab map resultset to variable created

select Top 1 Date_ccyymm
from calendar
where Process_Flg = 'N'
order by DateKey

Create a variable for generating new file name (FileName). Set EvaluateAs Expression true for it and then set expression like

@[User::ActualFileName] + variable created above + ".csv"
(I'm assuming you're already capturing the FileName in a variable called ActualFileName)

Now use this new variable @[User::FileName] in File System Task for getting it renamed. Set IsDestinationPathVariable property to true for making it dynamic based on variable passed.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Sqlraider
Yak Posting Veteran

65 Posts

Posted - 2013-08-28 : 09:35:09

quote:
Originally posted by visakh16

put your query in execute sql task and add a variable to store result. set ResultSet as single row and in Results tab map resultset to variable created

select Top 1 Date_ccyymm
from calendar
where Process_Flg = 'N'
order by DateKey

Create a variable for generating new file name (FileName). Set EvaluateAs Expression true for it and then set expression like

@[User::ActualFileName] + variable created above + ".csv"
(I'm assuming you're already capturing the FileName in a variable called ActualFileName)

Now use this new variable @[User::FileName] in File System Task for getting it renamed. Set IsDestinationPathVariable property to true for making it dynamic based on variable passed.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Thanks for the detailed explanation. I'm going to give this a try and will reply with the results.

Sqlraider

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-28 : 12:02:58

you're welcome
Let me know how you got on

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Sqlraider
Yak Posting Veteran

65 Posts

Posted - 2013-08-28 : 16:04:43

Getting the following Error:

Error at File System Task: Failed to lock variable "Filename.csv" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.".

Error at File System Task[File System Task]: An error occurred with the following error message: "Failed to lock variable "Filename.csv" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.".
".

Error at File System Task: There were errors during task validation.

The variable should be "Filename201306.csv" but is "Filename.csv". Its as though it is not accepting the result of my query.

Any ideas?

Thanks,
Sqlraider

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-29 : 03:40:48

variable is not Filename201306.csv. I think its taking variable value instead of name

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Sqlraider
Yak Posting Veteran

65 Posts

Posted - 2013-08-29 : 13:51:38

Found the problem. I forgot to put the directory path in the Destination Connection in my Rename Task. I thought it would rename to the same path as specified in the Source.

Thanks for all your help,
Sqlraider

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources