Call Stored Procedure in SSIS
Hello,
I've a SSIS package which export data in flat files from database.
I need to export around 15 flat files from different tables based on query.
So, I created a stored procedure with input parametrized; based on that I can distinguish which query will be executed and based on that record set.
But, when I tried to call that SP in SSIS then it always return first query record set only in old db source when click on column. That is, if I pass a flag value which should return 3rd query result and columns then it still shows first one.
In ole db source, I am using, for e.g., EXEC SP_Name 'xyz'.
Do you have any clue on that?
Best Regards,
MohitPandit
I've a SSIS package which export data in flat files from database.
I need to export around 15 flat files from different tables based on query.
So, I created a stored procedure with input parametrized; based on that I can distinguish which query will be executed and based on that record set.
But, when I tried to call that SP in SSIS then it always return first query record set only in old db source when click on column. That is, if I pass a flag value which should return 3rd query result and columns then it still shows first one.
In ole db source, I am using, for e.g., EXEC SP_Name 'xyz'.
Do you have any clue on that?
Best Regards,
MohitPandit
Last Comment
Jason Yousef
I don't get it, can you post a screen shot of the OLEDB and paste your SP here., or even explain more please.
Huslayer is rigth. We can't help you only with that introduction.
>>But, when I tried to call that SP in SSIS then it always return first query record set only in old db source when click on column. That is, if I pass a flag value which should return 3rd query result and columns then it still shows first one.<<
In SSIS the meta data will always use the first resultset available.
In SSIS the meta data will always use the first resultset available.
Unless your different queries in that SP return a result set with the same schema layout, I don't think you'll be able to get that working using the regular OLE DB Source. You may want to investigate into using the Script Component as source.
Here's an example that may get you started: http://bobp1339.blogspot.com/2008/09/how-to-use-ssis-script-component-as.html
(not exactly the same scenario though, but should be interesting nonetheless)
Here's an example that may get you started: http://bobp1339.blogspot.com/2008/09/how-to-use-ssis-script-component-as.html
(not exactly the same scenario though, but should be interesting nonetheless)
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Thanks
Would you mind sharing how you've finally implemented it? Can be interesting for readers of this solution.
Microsoft SQL Server 2005
Microsoft SQL Server 2005 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. It includes support for managing XML data and allows a database server to be exposed over web services using Tabular Data Stream (TDS) packets encapsulated within SOAP (protocol) requests.
72K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
