SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SSIS and Import/Export (2008)
 Return Stored Proc Results Within DataFlow Task
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

qman
Constraint Violating Yak Guru

USA
439 Posts

Posted - 04/09/2013 :  12:45:06  Show Profile  Reply with Quote
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

USA
5072 Posts

Posted - 04/09/2013 :  13:35:06  Show Profile  Visit russell's Homepage  Reply with Quote
in the source editor type in the command (EXEC storedProcedureName;) then tick the columns tab, make sure the columns are selected.
Go to Top of Page

qman
Constraint Violating Yak Guru

USA
439 Posts

Posted - 04/09/2013 :  13:57:55  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 04/09/2013 :  14:52:37  Show Profile  Reply with Quote
for returning columns like that you need to use a UDF as the command inside OLEDB source ( at least in SSIS 2005)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

qman
Constraint Violating Yak Guru

USA
439 Posts

Posted - 04/09/2013 :  15:03:47  Show Profile  Reply with Quote
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?
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 04/09/2013 :  15:34:08  Show Profile  Visit russell's Homepage  Reply with Quote
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.

Edited by - russell on 04/09/2013 15:34:48
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 04/10/2013 :  00:44:30  Show Profile  Reply with Quote
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 fine
the SP call can be made prior to data flow task using execute sql task
and then at the end of package as last task you can add a execute sql task to clear /drop the created table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000