Input source

[GShen]

I have migrated a DTS to SSIS and it did not migrate properly. Getting all sorts of messages. The source is an the execution of a stored procedure. The source says SQL COMMAND. And the command is not a SELECT it is EXEC XXXXXX where xxxx is the stored procedure. SSIS seems to be having a problem with this. I tried adding the columns manually and then I get metadata errors. I tried started from scratch and using the wizard but it will not allow for the execution of a stored procedure. I tried building from scratch and again I get metadata errors.

Does SSIS support the execution of a stored procudere ? It is very complex stored procedure.

The only other option I can think of is to insert the results of the stored procedure into a table and then drive the connection with the table where SSIS will not have any problems.

A have a lot more like this I was trying to avoid doing that.

The other funny thing is I have a few which are done the same way (EXEC XXX) and they migrated perfectly.

Any suggestions ?

Remember when... everything worked and there was a reason for it?

 

[MDXer]

yes SSIS supports stored procedure results as the source of a data flow.

when you say it is a complex SP in what way?

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

 

[GShen]

There are 3 selects in the SP. The 1st inserts item pricing data from a few tables into a temp table and the 2nd inserts item pricing data from different tables into a 2nd temp table. Then the 3rd does a full outer join from these 2 temp tables, outputing the results. If there is an entry in each table, it decides which one to select as the output cannot have duplicate items. I know when I use this information on a report I have to comment out the temp tables and just do a normal select of dummy fields. Then after the report is finished, I uncomment the temp stuff and comment out the dummy fields. It then all works. Maybe I should try the same with this ?

Remember when... everything worked and there was a reason for it?

 

[MDXer]

When you create a Data source in SSIS it tries to do a Select Top 1 * From to get the columns names and datatypes. Since the design of your SP uses 2 temp tables then this may infact be the source of your problem.

On a side note why are yuo doing this in the DB rather than in a SSIS Dataflow?

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

 

[GShen]

MDXER,
Do you mean to take the entire SP and cut and paste it in the dataflow input source as a query ? Would that work with the temp tables?
By the way. I got it to work going to a temp table in the meantime. I mean, an actual table called TEMP. I then populate the DTS from the TEMP table. However, if you are telling me I can take my query and stick it in the dataflow and it will work, then I will give that a shot. I really didn't want to create a dummy table so to speak. At least I know I am not going crazy.

Thanks,

Remember when... everything worked and there was a reason for it?

 

[MDXer]

No what I am saying is to utilize dataflows to produce the same results as your sp. if you are bring data together into a temp table you could possibly merge data flows together to produce the same results as your sp.

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