Exec Stored Procedure

Hi,

Hope someone can help me out here - I'm trying to design a data flow in SSIS which relies in part on a stored procedure as a data source - I'm running it through OLE DB.

Sure Exec MystoredProc works fine on preview and on parsing, but it refuses to acknowledge any of the columns, when I go to Edit-->Columns everything is blank.

Just out of interest - the reason I am using a stored procedure is because I dump the data into a temp table and then amend a couple of the columns to make it the same as my other database (for example where len(field) = 6 then field = '0000' + field).

Possibly I'm better off taking the raw data through the OLE connection and then transforming it through SSIS, but my gut feeling is I should minimise what I'm dumping into SSIS and offload the processing onto the local DB. Any thoughts?

Thanks

Rich

To do exactly what you need - use OLE DB Command Transformation - part of the data flow. Use ? for column parameters.

IMHO: There are two schools that define how to do this: ETL and ELT. What you are trying to do is ETL - the disadvantage of this is if you have a lot of rows - this method will execute the SP as many times - this is performance impact.

In ELT approach you load the data with Bulk Insert and then apply a SQL statement or another set of trasnformations to load the target.

|||

This is helpful Dima, particularly your comments on ETL & ELT, but it doesn't quite hit the spot in terms of what I want to achieve.

Maybe because I'm being thick. My fundamental problem is that my OLE DB connector will not return any column names. When I add the OLE DB transform it just whinges that I don't have any column names.

As I see it your solution will allow me to connect two tables through OLE DB connectors, drag them into OLEDB transform, do my changes, and then output columns - are you saying that there is no way for SSIS to derive column names from a stored procedure without iterating through the recordset one record at a time? This sounds totally useless, but completely feasible.....

As I have already done the work I really just want to get the stored procedure results into SSIS via my OLE DB source, if you are saying I have to start again then I will, but in truth if using this technology means I have to rewrite all my existing SP code as data flows then it is about as much use to me as a chocolate teapot, I have years of work wrapped up in stored procedures and also I would prefer to distribute the load, I want as few transformations as possible in SSIS, the primary use for the server is reporting, not data transforms, we have plenty of spare DB capacity across the planet and bandwidth is at a premium so thinking about what you've said I think we are better off restricting the recordset at source.

|||

I feel your pain :)

What is your OLE DB source? a DB or file? If you have weird source - you may want to see if there is newer ODBC or OLE DB driver out there.

For the existing SP's:

If you already have SP's that accept fields and insert them into a table, row by row - you should be able to reuse them. You can map source fields to the OLE DB command parameters.

|||

Hi Dima,

Hmm... Just to let you know I worked round the problem after reading your answers - my stored procedures now delete and create tables, which I then query, not very efficient but hey, it works, and I can schedule them monthly just before my reports need doing. Anyway, it is only MS SQL I have a problem with, all my Oracle stuff is based on views anyway and it works fine (?)

I tried using the ADO.NET source instead but this is even more messed up.

Path of least resistance until Microsoft sort this mess out, I think.

Thanks for your help

Rich

|||I guess you are doing something wrong... You can call a SP in an OLEDB datasource (described here: http://msdn2.microsoft.com/en-us/library/ms141696.aspx). I tried that with Adventureworks using the SP they suggested for testing with parameters (just entered a one as value for each ?). It returned the metadata correctly...|||I've had similar problems where SSIS doesn't correctly identify columns if you have anything more than a vanilla SELECT statement in the s/p. The solution is to do a SET NOCOUNT ON at the start of the s/p. In addition, there seems to be a problem if the s/p uses temporary tables (i.e. #TableName). It works fine if using table variables though.

Greg.