Solved

Looking for solution to dynamically map output from Stored Procedure

Posted on 2011-03-07
11
790 Views
Last Modified: 2012-05-11
I have a SSIS package that calls a Stored Procedure.  The package gets parameters passed in which are parameters for the SP.  Based on those parameters it will select a different query with a different results set.  So the returned columns will be different.

All I want to do is take the dynamic result set of the query and export it to a text file without doing the mapping.  BCP will take the results set and export it to a text file without mapping.

Do I need to do a script?  I found a simple script that reads one line at a time and writes it to a text file but it errors out and I can't figure out the error.   Attached is the VB Script.  Is there a way to debug the VB?  The only error I get is cannot open...

The work-around would be to have different output destinations that it would go to depending on the parameter.  The problem with this is that every time I add a column I would have to change the SSIS package instead of it being dynamically inserted into a text file...

Any help would be greatly appreciated...

Thank you!
ScriptMain.vb
0
Comment
Question by:Bodhi108
  • 6
  • 5
11 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35065755
>>BCP will take the results set and export it to a text file without mapping.<<
Then call BCP from your SSIS package.

>>The problem with this is that every time I add a column I would have to change the SSIS package instead of it being dynamically inserted into a text file...<<
Which begs the question:  Why do you keep on having to add columns?  That does not sound like a very well designed system.

0
 

Author Comment

by:Bodhi108
ID: 35076986
Why does it keep changing?  We collect data for outside parties.  These outside parties tell us what they want.  There are always upgrades to what they want which requires updates to our software (front-end, database and reporting system).  I am trying to make it as flexible as possible by only adding new columns to the table, the stored procedure without having to change the SSIS package.

Two questions:
1)  How do I execute BCP within SSIS?  Is it using the Execute SQL Task?

2)  I have it working with BCP outside of SSIS and it hangs with Preemptive_os_pipeops.  I might need to ask that within a different question since this is not SSIS related...
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35077227
>>How do I execute BCP within SSIS? <<
You should be able to execute it using the Execute Process Task.
0
 

Author Comment

by:Bodhi108
ID: 35080113
BCP does not solve the problem.  I believe it does some internal mapping of columns.  If I call a BCP from a Stored Procedure, the columns in the select statement from the SP have to be identical.  It works fine when I run it with the first two parameters which have identical column output.  If I run it with the other results set (with a different number of columns) it gets the Preemptive_os_pipeops which locks the process whenever the parameter is used for the different results set.

I am new to SSIS but have over 20 years with SQL programming.  Unfortunately, it doesn't help much with SSIS.

Is there a way to have a SSIS package do the following?
Have two different Execute SQL Tasks that call the same SP but with two different parameters.

I would need a conditional statement that I can use on the Control Flow to direct the flow to either one of the Execute SQL Tasks based on the parameter passed into SSIS?  I see a Conditional Split but that is used on the Data Flow.

Thanks!
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35082843
>>BCP will take the results set and export it to a text file without mapping.
...
BCP does not solve the problem. <<
Ok

>>Have two different Execute SQL Tasks that call the same SP but with two different parameters.<<
Sure.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:Bodhi108
ID: 35084860
Thanks, that sounds good but what task do I use to have a conditional statement to run on or the other based on a the parameter passed into the package?.  

For example, if Parameter =1 run SP1 else if Parameter =2 run SP2...  I see a Conditional Split on the Data Flow but I don't see a Conditional Split on the Control Flow.  Where would this if statement be locate?.  I need two separate flows since they are going to to two different outputs.

Thanks!
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35089359
Here is a good tutorial on the subject of controlling the workflow:
Introduction to Expressions on Workflow
http://www.sqlis.com/sqlis/post/Introduction-to-Expressions-on-Workflow.aspx
0
 

Accepted Solution

by:
Bodhi108 earned 0 total points
ID: 35380034
Not really solved.

Went with a solution that I didn't like since it involves creating two Stored Procedures and two SSIS Packages instead of dynamically controlling the output depending upon the parameters entered in.

Thanks anyway for the help!
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35380109
>>In the end, none of the solutions worked<<
I realize you are new here, however if you had taken the time to post some feedback we could have helped more, as it is you abandoned the question, which is to say the least a discourtesy to the volunteers here and indicates a lack of interest in getting this resolved.
0
 

Author Comment

by:Bodhi108
ID: 35380178
I apologize.  I did not at all mean to be discourteous.  When I received the link for tutorial I thought that was as far as I could go with my issue.  So, yes, I did abandon the question when I received the link...  Sorry.  In the future I will be more careful in giving more feedback.

Thanks for your explanation.  Yes, I am new here so I am learning the ropes.
0
 

Author Closing Comment

by:Bodhi108
ID: 35410416
In the end, none of the solutions worked so I had to go with a solution that I came up with which I didn't like but works.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

I'd like to talk about something that is near and dear to my heart: build systems. Without them, building software is all about compiling locally, with software versions everywhere. It can be a mess. Today we are going to discuss building a small di…
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.
This tutorial will introduce the viewer to VisualVM for the Java platform application. This video explains an example program and covers the Overview, Monitor, and Heap Dump tabs.
This tutorial explains how to use the VisualVM tool for the Java platform application. This video goes into detail on the Threads, Sampler, and Profiler tabs.

705 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now