?
Solved

Call Stored Procedure in SSIS

Posted on 2011-10-05
7
Medium Priority
?
414 Views
Last Modified: 2012-05-12
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
0
Comment
Question by:MohitPandit
7 Comments
 
LVL 21

Expert Comment

by:Jason Yousef, MS
ID: 36924042
I don't get it, can you post a screen shot of the OLEDB and paste your SP here., or even explain more please.
0
 
LVL 53

Expert Comment

by:Vitor Montalvão
ID: 36924108
Huslayer is rigth. We can't help you only with that introduction.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36924204
>>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.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 37

Expert Comment

by:ValentinoV
ID: 36924512
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)
0
 
LVL 7

Accepted Solution

by:
rmm2001 earned 1500 total points
ID: 36936486
@acperkins is right. The metadata for the source will be read off of the first dataset in your query.

To do this you'll have to either use a script component or add a few more steps. In your stored proc, create a "temp" table for your records. So you'll have 15 of those. Then the next step in your SSIS package would be to write all 15 files (each need their own file destination as metadata isn't dynamic). Then the next step would be to delete the "temp" tables you added after the package ends execution.

You may want to consider doing this a sql bcp statement or something where you can export to a file. Have the file path as a parameter that you pass in to your proc and then you could output it like that. If you have changing metadata on your query, bcp may be the way you want to go.
0
 
LVL 5

Author Closing Comment

by:MohitPandit
ID: 36954566
Thanks
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 36954670
Would you mind sharing how you've finally implemented it?  Can be interesting for readers of this solution.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Loops Section Overview

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question