Loop through excel spreadsheet, write to text file
-
I am trying to create a package that loops through each record of an excel spreadsheet and outputs that information to a text file.
So far, I have created an Execute SQL task object that connects to my Excel file and then a Foreach Loop container that houses a script task object. I am running into problems figuring out how to reference the variables that are holding my resultset from the excel file into my VBA code inside the script task. I also need to figure out how to actually write a loop in VBA that will access each excel record and then finally create the text file with this information.
Thanks in advance for the help!
-
at a glance, I would say perhaps an easier solution would be to use a data flow task in your foreach loop.
use the loop to iterate over your directory and grab the excel filename into a variable. make your excel connection dynamic by using this variable. map the excel columns to a text file destination. job done.
tom
Life: it twists and turns like a twisty turny thing
-
I'd go with the dataflow task as well, but if you are only using 1 spreadsheet you don't need to use the For Each loop. You just need a Source(Your excel sheet) and a Flat File Destination. If you are doing some manipulation in the script task you can use a script component in the DataFlow to do that. Or, depending on the manipulation you are doing there may be a pre-defined task that will do it for you.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply