sadie519590


Hi,

I tried to find some information about this, but surprisingly can't seem to find anything. Seems like it would be a very common scenario.

I need to send the output of a stored procedure to a flat file. First, I created an Execute SQL Task that calls the stored procedure. I selected "full result set."

My first question is, how do I capture the individual column values For example, under "Result Set", should I create an Object variable, or should I use individual column variables I've tried both ways, but can't seem to get to the next item, below...

Now, how do I map the variables to the flat file If I use a data flow task, the flat file has "no available inputs". If I add an OLE DB Source before the flat file destination, there's no place to capture the result set.

Clearly, I am missing something here.

Thanks




Re: stored procedure output to flat file

Anthony Martin


-create a data flow task

-put the stored procedure as your oledb source and connect it to a flat file destination.







Re: stored procedure output to flat file

Phil Brammer

Anthony Martin wrote:

-create a data flow task

-put the stored procedure as your oledb source and connect it to a flat file destination.



Correct. Just make sure that the stored procedure returns column names for each column.






Re: stored procedure output to flat file

sadie

Hi,

This is exactly what I tried at first (before my original post).

When I connect the OLE DB source to the flat file destination, it says that there's no available input columns.

So are you saying that I need to change the stored proc so that it actually returns named columns

Because when I look under the OLE db source, "columns" table, there are no available external columns.

Thanks





Re: stored procedure output to flat file

Phil Brammer

If the stored proc is returning data, isn't it in a row/column format If so, then yes, have it return named columns (column alias) and then it should work in the OLE DB source component.





Re: stored procedure output to flat file

sadie

Hi,

I just examined the stored procedure. Some of the columns were named. However, I changed it so that all the columns returned are named, ie

SELECT

col1 AS col1,

col2 AS col2,

etc

However, I still am not seeing any available external columns, nor can I successfully connect the flat file. It's still saying "no available input columns".





Re: stored procedure output to flat file

Phil Brammer

sadie519590 wrote:

Hi,

I just examined the stored procedure. Some of the columns were named. However, I changed it so that all the columns returned are named, ie

SELECT

col1 AS col1,

col2 AS col2,

etc

However, I still am not seeing any available external columns, nor can I successfully connect the flat file. It's still saying "no available input columns".



Delete the OLE DB Source and re-add it. Then, be sure you are using a SQL Command and type in "exec your_Sproc" From there, click on the columns tab and you should be presented with the output columns of the stored procedure.





Re: stored procedure output to flat file

sadie

Ok, mystery solved. Execute permission was missing. My fault.

Thanks for the help





Re: stored procedure output to flat file

Vikramg

In my case i have a cursor and a temp table inside the storeprocedure. I am selecting from the temp table in the stored procedure as the final step. I am not seeing any columns as output. If i preview then i can see the data returned from the stored procedure in the preview tab but the columns are empty in the column mapping section. I am not sure how to get those column names displayed there for mapping.

The conneciton has full access including execute permissions not sure what could be the cause any Ideas.

Thanks

Vikram





Re: stored procedure output to flat file

jwelch

You need to put a SELECT statement that returns the same set of columns as your final SELECT as the first statement in the procedure. It shouldn't return any rows, but it needs to be there so SSIS can read the metadata.






Re: stored procedure output to flat file

Vikramg

Hi Thanks for your response

I figured it lastnight based on the help from other post in the same forum

My stored procedure has select statement at the end but in the middle i was doiong lot temorary tables and cursors. So the stored procedure is returning a count insted of column names from the final select. I had to SET NOCOUNT ON

SET FMT ONLY in the stored proceudure to return the columns and it all works fine

Thanks

Vikram