How to use stored procedures within a DTS data transformation task?

Question

I have a DTS package with a data transformation task (data pump). I’d like to source the data with the results of a stored procedure that takes parameters, but DTS won’t preview the result set and can’t define the columns in the data transformation task.

Has anyone gotten this to work?

Caveat: The stored procedure uses two temp tables (and cleans them up, of course)

Answer 1

You would need to actually load them into a table, then you can use a SQL task to move it from that table into the perm location if you must make a translation.

however, I have found that if working with a stored procedure to source the data, it is almost just as fast and easy to move it to its destination at the same time!

Answer 2

Nope, I could only stored procedures with DTS by having them save the state in scrap tables.

Answer 3

Enter some valid values for the stored procedure parameters so it runs and returns some data (or even no data, you just need the columns). Then you should be able to do the mapping/etc.. Then do a disconnected edit and change to the actual parameter values (I assume you are getting them from a global variable).

DECLARE @param1 DataType1
DECLARE @param2 DataType2 SET @param1 = global variable
SET @param2 = global variable (I forget exact syntax)

--EXEC procedure @param1, @param2
EXEC dbo.proc value1, value2

Basically you run it like this so the procedure returns results. Do the mapping, then in disconnected edit comment out the second EXEC and uncomment the first EXEC and it should work.

Basically you just need to make the procedure run and spit out results. Even if you get no rows back, it will still map the columns correctly. I don't have access to our production system (or even database) to create dts packages. So I create them in a dummy database and replace the stored procedure with something that returns the same columns that the production app would run, but no rows of data. Then after the mapping is done I move it to the production box with the real procedure and it works. This works great if you keep track of the database via scripts. You can just run the script to build an empty shell procedure and when done run the script to put back the true procedure.