Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
qman
Constraint Violating Yak Guru
442 Posts |
 Posted - 2013-04-09 : 12:45:06
|
| I have a stored procedure being called from a Data Flow task.The OLEDB data source previews OK, but does not return any columns to map to a destination.Can someone please instruct me on how to get this to work?Thank you! |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2013-04-09 : 13:35:06
|
| in the source editor type in the command (EXEC storedProcedureName;) then tick the columns tab, make sure the columns are selected. |
 |
|
|
qman
Constraint Violating Yak Guru
442 Posts |
Posted - 2013-04-09 : 13:57:55
|
| Thanks Russell, I don't see any columns to select, it's empty. The SP does return a result set when executing outside of SSIS. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-09 : 14:52:37
|
| for returning columns like that you need to use a UDF as the command inside OLEDB source ( at least in SSIS 2005)------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
qman
Constraint Violating Yak Guru
442 Posts |
Posted - 2013-04-09 : 15:03:47
|
| Ugh, might be better to have the SP write to a "live" table, and then have SSIS reference the table.I can always create the table prior to calling, and drop after... What do you think? |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2013-04-09 : 15:34:08
|
| But you don't have to do that in SSIS 2008.How many tables is the stored proc referencing, and are they all in the database that the SSIS connection is to?Your workaround is exactly what I'd do if it's 2005. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-10 : 00:44:30
|
quote:
Originally posted by qman
Ugh, might be better to have the SP write to a "live" table, and then have SSIS reference the table.I can always create the table prior to calling, and drop after... What do you think?
yep..that should work finethe SP call can be made prior to data flow task using execute sql taskand then at the end of package as last task you can add a execute sql task to clear /drop the created table------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
Return Stored Proc Results Within DataFlow Task