SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SSIS and Import/Export (2008)
 Appending to a filename from a table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Sqlraider
Yak Posting Veteran

USA
65 Posts

Posted - 08/23/2013 :  13:49:55  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
1635 Posts

Posted - 08/27/2013 :  23:14:21  Show Profile  Click to see yosiasz's MSN Messenger address  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 08/28/2013 :  03:31:33  Show Profile  Reply with Quote
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
Go to Top of Page

Sqlraider
Yak Posting Veteran

USA
65 Posts

Posted - 08/28/2013 :  09:35:09  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 08/28/2013 :  12:02:58  Show Profile  Reply with Quote
you're welcome
Let me know how you got on

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Sqlraider
Yak Posting Veteran

USA
65 Posts

Posted - 08/28/2013 :  16:04:43  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 08/29/2013 :  03:40:48  Show Profile  Reply with Quote
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
Go to Top of Page

Sqlraider
Yak Posting Veteran

USA
65 Posts

Posted - 08/29/2013 :  13:51:38  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000