How to execute a sp from SSIS

[ptheriault]

I'm really struggling with this task here. I have a stored procedure that I need to execute nightly, save the contents to a csv file and e-mail the file to 3 users.

I've created my project in BIDS
I've added a connection to the database in connection Managers.
I created an OLE DB Data Flow source that will execute the procedure.
This is were I get stuck. How do I send the output to a csv file? I've added a flat file destination but I'm getting a message about no available input columns.

Does anyone know what I'm doing wrong.

Thanks for the help.


- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[ptheriault]

Update:
I've made some progress. I changed my control flow to exute SQL task. Then I added a task to send mail. But I can't figure out how to have the execute sql task to output to a csv file.


- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[katbear]

I had the same problem. I ended up having to use a Script Task to accomplish do it.

Although this is not to say you *can't* write the output to a csv file using a flat file destination, I never figured it out myself because in my case, I was writing the output of *multiple* stored procs to files, and you definitely CANNOT do this using a single flat file destination.

I was under the impression that you *can* write to a flat file if it's just a single stored procedure.

You will get better help at the MSDN SSIS forums.

 

[ptheriault]

Thanks Kat,
I couldn't get it to work using a stored procedure but I was able to get it to work with a select statement from a static table.



- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[MDXer]

The Short answer is that you can't. The execute SQL task is a Control Flow task where as your destination objects are Data Flow tasks. THe 2 are seperate Flows and only the Data flow is meant to consume a recordset and work with it.

As Kat stated you can write a custom script to do this. You would probably use the Script as a Source transformation which will give you an output. You will have to create an output Column for each column you need to introduce to your data flow, a real pain if you have a large number of columns. This you can then use as the input source for your destination.

What does the SP do? You may be able to build a DF that accomplishes what the SP does.

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[ptheriault]

Thanks Paul.

The execute SQL task is a Control Flow task where as your destination objects are Data Flow tasks

I did read that after the fact. That's when I decided to have my procedure write to a static table instead of a #tmp table that had the final select. I then ran from start -> run -> DTSWizard an export data task that I saved as a DTS package. Then I just added the send mail step.

I liked the old DTS better when it came to creating quick export packages. It was much eassier to use.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[MDXer]

Some things do take more time to build in SSIS but in my experience the gain in performance is well worth it.

For example I had a process this weekend that as a stored procedure to 10 - 15 minutes to process a single days woth of data. This same process takes less than 1 minute as a self contained SSIS package where all aggregations and joins are handled in the SSIS DF.

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[ptheriault]

I just need to find a class. I'm looking right now for a class at learningtree.com

Do you know of any?

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[katbear]

Hi Paul,

I just wanted to update this post. Turns out that today I had to do exactly what you were trying to do. Execute a stored procedure and write output to a file.

This is how I did it:

DATA FLOW TASK --> OLE DB SOURCE --> FLAT FILE DESTINATION

In the OLE DB SOURCE, I entered my stored proc. Then, I previewed it just to make sure it's working, by clicking the Preview button.

Then, under columns I selected the columns I wanted to go to my file.

Then, in the FLAT FILE DESTINATION, I defined my connection manager, then under mappings, mapped my columns.

It was that simple, and works great.

So I was correct in that you CAN write the output of a single stored proc to a file.

HTH

 

[ptheriault]

Thanks Kat.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[katbear]

Sure. Did it work for you too?

 

[ptheriault]

I haven't tried it yet. I was able to rewrite my proc to insert data into a static table. Then I wrote my package to select * from table.



- Paul
- If at first you don't succeed, find out if the loser gets anything.