In the control flow I have an "Execute SQL Task" that executes a stored procedure. The stored procedure returns a result set of about 2000 rows of data into a package variable that has been typed as Object to contain the data.
What I have not been able to figure out is how to access the rows of data (in the package variable) from within a data flow task. There does not seem to be a data flow source task to perform that operation.
What am I missing that would make this easy?
...cordell...
if you need to have control on every row of the result set you may be on the need of using a data flow task(source, transforms and destination components). Would you give an example of what you are trying to accomplish after getting the 2000 rows?
|||In the data flow task...I need to parse through the data to generate 3 unique result sets of data. One of the result sets will go to a text file, the 2nd result set go back into a staging table, and the 3rd result set goes to a excel spreadsheet.
|||Cordell Swannack wrote:
In the control flow I have an "Execute SQL Task" that executes a stored procedure. The stored procedure returns a result set of about 2000 rows of data into a package variable that has been typed as Object to contain the data.
What I have not been able to figure out is how to access the rows of data (in the package variable) from within a data flow task. There does not seem to be a data flow source task to perform that operation.
What am I missing that would make this easy?
...cordell...
This shows you how to do it:
Recordsets instead of raw files
(http://blogs.conchango.com/jamiethomson/archive/2006/01/04/2540.aspx)
I'm not sure if this is relevant though. If you are using a SQL statement to get the data why not just put the SQL statement into an OLE DB Source adapter? Why bother with the rigmarole of a variable and then having to write code?
-Jamie
|||
In the data flow task...I need to parse through the data to generate 3 unique result sets of data. One of the result sets will go to a text file, the 2nd result set go back into a staging table, and the 3rd result set goes to a excel spreadsheet.
I would wager you can accomplish all of this with a combination of the conditional split, multicast and/or derived column components.
Even if you can't, you would be better off employing a script component to parse through the data - there is still no need to put it into a variable first.
-Jamie
|||
Cordell Swannack wrote: In the control flow I have an "Execute SQL Task" that executes a stored procedure. The stored procedure returns a result set of about 2000 rows of data into a package variable that has been typed as Object to contain the data.
What I have not been able to figure out is how to access the rows of data (in the package variable) from within a data flow task. There does not seem to be a data flow source task to perform that operation.
What am I missing that would make this easy?
...cordell...
This shows you how to do it:
Recordsets instead of raw files
(http://blogs.conchango.com/jamiethomson/archive/2006/01/04/2540.aspx)
I'm not sure if this is relevant though. If you are using a SQL statement to get the data why not just put the SQL statement into an OLE DB Source adapter? Why bother with the rigmarole of a variable and then having to write code?
-Jamie
I was going to suggest something similar. In general you would use data flows to perform row by row operations; like transformations, splits, sorts, etc. However the control flow may be better place to row set based operations; like an update, select into, etc.
In you particular case, use a dataflow task and then drop a OLE DB source component, any required transform and the detination component.
|||Helpful suggestions....
While you can use the OLE DB Source adapater inside of the Data Flow task...I am executing a stored procedure that returns back a large result set. When you click on the 'Preview' button...the data and all of the column names are returned. (Just proving that everything works.)
However I haven't figure out how to set the column names typically set by OLE DB Source component. Right now they are blank. Usually at design time when you reference a table or use simple select statement the OLE DB Source task can map the columns specified by the table name or select statement to create a list of columns that are used for the output and thus by other components in the data flow task.
But when executing a stored procedure...the column names are not returned until completion (i.e. runtime) of the stored procedure. This is the problem.
...cordell...
|||Helpful suggestions....
While you can use the OLE DB Source adapater inside of the Data Flow task...I am executing a stored procedure that returns back a large result set. When you click on the 'Preview' button...the data and all of the column names are returned. (Just proving that everything works.)
However I haven't figure out how to set the column names typically set by OLE DB Source component. Right now they are blank. Usually at design time when you reference a table or use simple select statement the OLE DB Source task can map the columns specified by the table name or select statement to create a list of columns that are used for the output and thus by other components in the data flow task.
But when executing a stored procedure...the column names are not returned until completion (i.e. runtime) of the stored procedure. This is the problem.
...cordell...
You're absolutely right. This IS a problem with sprocs. And I agree that this sounds like a good justification for using the Execute SQL Task route. However, I suggest another workaround here:
Using stored procedures inside an OLE DB Source component
(http://blogs.conchango.com/jamiethomson/archive/2006/12/20/SSIS_3A00_-Using-stored-procedures-inside-an-OLE-DB-Source-component.aspx)
that you may wish to employ that will enable you to use the OLE DB Source component.
-Jamie
|||
I found your blog entry on the subject just as I was receiving your email.
The reason why a UDF doesn't work for my solution is the amount of transformation processing that must be performed upon the data. Basically I have 10,000's of data records that are processed and then randomized into a laboratory trial groups which are then distributed out to various computer systems for analysis.
I ended up changing around the stored procedure to create a table in the database...and then once the SSIS work has completed...I drop the table. A less than perfect solution...but it gets the job done and it is time to move on.
Thank you Jamie for your time to answer my questions and your patience.
...cordell...
