SSIS - Renaming a file using recordset value
-
I've got a SQL task that sends values into a Foreach Loop container. I've got a Data Flow task that pulls information from a db source and I've got a Flat File destination setup to put the information into a file called Chapter.csv. I've pulled over a File System task to rename that file. My goal is to rename that file...
Chapter1.csv
The number 1 is one of the values that the SQL task sent into the Foreach Loop container. How do I grab that value to assign it to a variable so that I can rename the file? I tried creating an expression that used the recordset variable name but since it's an Object type, it wouldn't let me. The next file created will be Chapter.csv and after it is populated with data, I'd like to rename it to Chapter2.csv...and so on.
I'd really prefer not to have to use a Script Task since my knowledge of VB or C# isn't up to snuff to use that type of task.
TIA,
John -
Can you expand on what you mean by this, please?
one of the values that the SQL task sent into the Foreach Loop container
In particular, is there a non-Object package variable which contains the value?
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 -
Rather than trying to explain what I meant by that, let me try and reword my original question...
How do I take the first value of the my variable named Recordset (that the SQL task created)...
...and use that value to rename the file within the Foreach Loop container?...without using a Script task?
I know that I can hard code changes but that will only work for the first file I create and rename. When the Foreach loop executes the first time, it's going to use the number 1 to pull the data for all records in chapter 1. I'd like to be able to capture that number in a variable to rename the file from Chapter.csv to Chapter1.csv.
Hopefully I didn't confuse things a second time. Sorry if I did.
TIA,
John - J M-314995 - Wednesday, March 14, 2018 12:28 PMRather than trying to explain what I meant by that, let me try and reword my original question...
How do I take the first value of the my variable named Recordset (that the SQL task created)...
...and use that value to rename the file within the Foreach Loop container?...without using a Script task?
I know that I can hard code changes but that will only work for the first file I create and rename. When the Foreach loop executes the first time, it's going to use the number 1 to pull the data for all records in chapter 1. I'd like to be able to capture that number in a variable to rename the file from Chapter.csv to Chapter1.csv.
Hopefully I didn't confuse things a second time. Sorry if I did.
TIA,
JohnThe first thing you need to learn about is how to shred that recordset. Take a read here, for example.
Once you work this out, you will see that you will have a standard string variable available within the FEL container which will contain your chapter number.
Building an expression which uses this to set your 'target file name' is straightforward, so you'll be able to use this and a File System Task to perform your rename.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 -
Ok....After some more verifying of settings, it looks like I was trying to use the object variable in the ole db source container as a variable. Instead I need to use the non-object variable that I previously setup. I'm able to name the files how I need to name them at this point. Thanks for your assistance and patience with a newb.
Thanks,
John
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply