Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 809
  • Last Modified:

Looking for solution to dynamically map output from Stored Procedure

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
Bodhi108
Asked:
Bodhi108
  • 6
  • 5
1 Solution
 
Anthony PerkinsCommented:
>>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
 
Bodhi108Author Commented:
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
 
Anthony PerkinsCommented:
>>How do I execute BCP within SSIS? <<
You should be able to execute it using the Execute Process Task.
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
Bodhi108Author Commented:
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
 
Anthony PerkinsCommented:
>>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
 
Bodhi108Author Commented:
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
 
Anthony PerkinsCommented:
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
 
Bodhi108Author Commented:
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
 
Anthony PerkinsCommented:
>>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
 
Bodhi108Author Commented:
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
 
Bodhi108Author Commented:
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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now