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
LVL 5
MohitPanditAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jason Yousef, MSSr. BI  DeveloperCommented:
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
Vitor MontalvãoMSSQL Senior EngineerCommented:
Huslayer is rigth. We can't help you only with that introduction.
0
Anthony PerkinsCommented:
>>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
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

ValentinoVBI ConsultantCommented:
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
rmm2001Commented:
@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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MohitPanditAuthor Commented:
Thanks
0
ValentinoVBI ConsultantCommented:
Would you mind sharing how you've finally implemented it?  Can be interesting for readers of this solution.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.