July 8, 2008 at 6:25 pm
Hi all.
I need a good resource to figure out how to use For...Loop or ForEach...Loop from SSIS in order to do the following:
1) Call up filenames from a SQL Table, looping through them one by one
2) Create a recordset for each filename called up in #1 above and export that to the filename
It sounds easy, but the only example of using the for loops in SSIS seem to be demonstrations of looping through files in directories, not fields in a table. I'm also a little unclear on how to use enumerators... do I need to get a count of the rows in the table prior to doing the loop, and use a number as an enumerator until I hit the count of records in the table, or can I rely on just the table recordset to enumerate for me?
Anyone have any good examples of how to do this, please list them for me!
July 8, 2008 at 10:01 pm
Not saying it's a good example but it's a quick one (btw, you may also want to check out ssis.com, the sister site of dts.com for examples, i would be guessing they have some).
1. Create a new pkg
2. create two package level variables
2.1 - First variable (name it whatever you please, just remember it), i named adoVar, set it's type to Object
2.2 - second variable i named vfilename, set it's type to string
3. Add a dataflow task to the control flow workspace
3.1 In this first dataflow task, add an OLEDB Source. Point this source at your table that contains the file names (see bottom of post for a quick sample table of filenames 🙂 ).
3.2 Add a Recordset Destination to the dataflow workspace.
3.3 Connect the output of the OLEDB source to the Recordset destination
3.4 On the Component Properties of the Recordset Destination, under Custom Properties, set the VariableName property to be the name of the variable created in 2.1 above
4. Go back to the control flow workspace, add a foreach looping container
4.1 Edit the properties of the foreach container, set the enumerator to be a Foreach ADO Enumerator
4.2 Set the (drop down) ADO Object Source variable to be the variable created in 2.1
4.3 Set the radio button options to be Rows in the first table
4.4 on the Variable Mappings tab, select the variable created in 2.2 above (assuming using sample table) use the index field value of 0 (zero), alternatively use the value as per your own table output.
5. Add a dataflow task to the inside of the foreach container
5.1 (edit the dataflow task) Add an OLE DB source, set it to an arbitrary table
5.2 Add a Flat File Destination to the dataflow task. For the filename, i used a dummy named (e.g. c:\temp\dummy.txt)
5.3 Hook the output of the OLE DB source to the flat file destination
6. (the magic part 😉 ) Single click the Flat File Connection manager object you created (as part of adding the flat file destination). You'll see it listed at the bottom of the work area under the tab named COnnection Managers.
6.1 After selecting (not editing/opening) the connection, using the Properties pane, edit the Expressions for the Flat File Connection manager(ie click the ellipses [...] button).
6.2 Using the drop down inthe first column, select COnnectionString.
6.3 Click th Ellipses button [...] and in the Expression Builder dialog, expand the Variables folder and click and drag the 'vfilename' variable to the Expression textbox. This should result in an expression like '@[User::vfilename]' (without the quotes).
6.4 Click OK and then OK again.
now run and sit back and enjoy 😉
Basic overview is that the first dataflow task loads up the contents of your table (the one containing the target filenames) to an ADO variable. After completing this, execution moves to the foreach task. It iterates thru the rows in that table (held in the variable) and executes the inner dataflow for each one. The expression on the flat file connection changes the target output filename (the connectionstring) on each iteration of the foreach.
To make this really real world, you're likely to also use an expression on the inner ADO Data source, to modify the source table that you're extracting rows from (for export to the flat files).
Here's the create script for the dummy filenames table.
CREATE TABLE filenames (
[filename] VARCHAR(255)
)
INSERT INTO filenames ([filename]) VALUES ('c:\temp\firstfile.txt')
INSERT INTO filenames ([filename]) VALUES ('c:\temp\secondfile.txt')
INSERT INTO filenames ([filename]) VALUES ('c:\temp\thirdfile.txt')
HTH,
Steve.
July 8, 2008 at 10:14 pm
Thanks a million Steve!
I found one example on another forum which I've managed to work most of the way through, but the end part actually doing the final query of data to output is a bit tricky... so between that example and yours I should be able to get it sorted!
I've worked with SQL for years, but only recently started using the SSIS interface big-time with DTS for data transfers between us and our parent company. I'm still surprised at the simplicity and ease with which one can use these tools, but equally disappointed with the amount of documentation available on how to use it, so it's all been sort of hit and miss!
Microsoft tends to give one example for each task, and they're all so highly configurable that you'd need a lot more than that to cover the vast capability available. So thanks again, I'm sure your answer will be of help!
July 8, 2008 at 10:18 pm
Ah, and I wasn't aware of SSIS.com. I'll DEFINITELY have to check that out!
I wonder why nothing has come up in any Google searches from that site....
July 8, 2008 at 10:19 pm
No prob Sharon, hope it all works out 🙂
I haven't read his SSIS one (sorry Brian) but Brian Knight (one of the guys that started SQL Server Central and also a trainer etc) has co-written large tomes on DTS and SSIS, could be worth checking out also. I think it's one of the red cover 'Professional' series. Should be able to find it eaisly on Amazon (search by author) or most good bookstores.
Steve.
July 16, 2008 at 7:57 pm
Hi Steve.
Thanks again for your help on this. I had time to test it out today, and it worked a treat!
Now, I just have one more issue with this... on the loop inside the ForEach Container, I need to change the datasource from a single table as I have now, to actually query only a subset of data based on the batch I'm currently working on.
The batch/filename is currently in my vFilename variable, so I need to say "Select [field] from
where
.[batchname] = User::vFileName.
But when I set the data source to SQLCommand and try to parse the query with the variable in it, it doesn't work....
So... how can I refer to a variable from within this SQL Statement to get the current data I need out of the table, as I loop through?
I'm hoping you know the answer to this!
July 16, 2008 at 10:40 pm
If you only need to parameterize the where clause item, then change your statement from
Select [field] from
where
.[batchname] = User::vFileName
to
Select [field] from
where
.[batchname] = ?
If you've got the datasource set to SQLCOmmand, doing this, and then clicking the Variables button will let you specify which variable should be used for the '?' (variable placeholder). Note, I believe you can use multiple variables (e.g. where a = ? and b = ?) and then the order of the vars you specify as the source is important.
If you need to param up more of the statement, like the actual table name etc, then your bes bet id to add another step in front of this that creates the statement in a (SSIS string) variable and then change the mode from SQLCommand to SQLCommand From Variable and then select your 'command' variable from the DDL.
HTH,
Steve.
July 16, 2008 at 10:51 pm
Thanks again Steve!
Had I not been so lazy, I could've looked back at the example I got from someone prior to yours and noticed that it used the '?' as a placeholder for the parameter!
That should do the job, anyway, so thanks for the reminder.
By the way, I tried to go to both ssis.com and dts.com, and neither seems to be an active site at the moment!
Anyway, I'll continue to post here, as I seem to get great answers (don't worry, I'll try not to bug you too much!).
Well, that's one more little project sorted. Thanks again!
July 16, 2008 at 11:25 pm
Glad it worked. BTW, the sites are sqldts.com and sqlis.com.
Steve.
September 9, 2009 at 6:51 am
Hello Steve,
I'm trying to use your sample and got the following error:
Error: 0x3 at Foreach Loop Container: Variable "User::adoVar" does not contain a valid data object
Warning: 0x80019002 at Foreach Loop Container: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
I'm a newbie in SSIS... What it could be?
September 9, 2009 at 7:37 am
Hel (9/9/2009)
Hello Steve,I'm trying to use your sample and got the following error:
Error: 0x3 at Foreach Loop Container: Variable "User::adoVar" does not contain a valid data object
Warning: 0x80019002 at Foreach Loop Container: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
I'm a newbie in SSIS... What it could be?
Ok. After I posted a message I found a solution.
Now I have a small variation from original task. Let imagine that our dummy table filenames has values firstfile, secondfile, thirdfile instead of full path. So, I need to add to our variable vfilename a dir and extension. I realize it's a stupid question but I'm working with SSIS the first day......
September 9, 2009 at 10:31 am
I would probably use an Expression on the variable and pre-pend the full path to it. Alternatively, you could use a second variable to hold the path and perhaps even a third to hold the combined value of the two.
Steve.
September 9, 2009 at 11:22 am
stevefromOZ (9/9/2009)
I would probably use an Expression on the variable and pre-pend the full path to it. Alternatively, you could use a second variable to hold the path and perhaps even a third to hold the combined value of the two.
I'm sorry again for stupid questions but what exactly should I add in Expression? And how contincate directory variable with other variable? I do not program neither in C# nor VB...
September 9, 2009 at 12:41 pm
Thanks a lot! Will check links shortly!
Viewing 15 posts - 1 through 15 (of 31 total)
You must be logged in to reply to this topic. Login to reply