How to produce an OLE DB output with no data input, only variables
-
Can anyone tell me how to write results held in variables to a different table?
I have a sql task that extracts a list of 3000 items, and a For Each Loop that works through the list, using a data flow task to write rows to a table from each matching item.
The data flow task maintains a row count variable, and as a second step within the For Each loop I want to write the count to a different table, with the relevant item from the list.
I've tried using a data flow task, but this fails because it doesn't have a source (it runs ok but doesn't write a row). I've also tried using a SQL task but this gave differing errors depending on what I fed to it. I've just read that Insert Into is not a suitable candidate for this task.
What can I use at the control flow level, inside the for each container, that will allow me to add a row to a table from the values of 3 variables?
-
I would have thought a SQL task would have been the best candidate for what you are looking to do. What were the errors you were receiving?
Could you not use the SQL task to call a stored proc? Is the problem the number of variables or the data types? If this is the case, one option would be to pass the variables through as an XML segment, and then use the stored proc to parse it, or if your destination is SQL2005 insert them straight into an XML column.
Tom
Life: it twists and turns like a twisty turny thing
-
Thanks for getting back to me.
I managed to get it working by putting my sql in a variable and setting SQLSourceType to Variable. When I first posted I was trying to do it with direct input.
I've tried a number of things in between and can't remember exactly what was in the original code. I'm not sure if using a variable was the whole answer or if I just resolved my problem accidentally along the way :ermm:
Leastways it's working now. Thanks again
-
No problem, glad it's up and running.
Like most things with SSIS, there are usually two or three ways of getting it done.
Cheers,
Tom
Life: it twists and turns like a twisty turny thing
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply