Running Stor Proc from DTS package

Question

Post reply

Running Stor Proc from DTS package

lritchie

SSC Enthusiast

Points: 178
More actions
October 15, 2003 at 1:54 pm

#106736

Can anyone give a hint as to how I can run/execute a series of stored procedures from within a DTS package.
I've tried using ActiveX with no luck.
Thank you,
Lance

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

rs SSCrazy Points: 2819 More actions · Answer 1

Hi,

Yes you can execute the stored procedures from DTS

1. make a connection to your server

2. create new task called Execute SQL Task and provide the connection which you created in first step then

EXEC [Your Procedure Name]

Thanks.

kokyan SSCommitted Points: 1694 More actions · Answer 2

You can pass the parameters into the store procedure by using global variables.

Click on the properties at Execute SQL Task.

Regards,
kokyan

jan van der Goes SSC Rookie Points: 41 More actions · Answer 3

The way I do it:

Create a Transform Data Task.

Define you stored procedures as lookup query.

In de query designer use the following syntax: spname ?,?,?

For the package it does't matter if the lookup is a Select, Insert or whatever.

Then in your ActiveXscript tranformation call the Lookup SP example:

GroepID = DTSLookups("InsertGroep").Execute(Left( DTSSource("groepnaam") , 30 ),null,DTSSource("GT"))

This way the execution of the stored procedures becomes a side effect of the transformation.

I use it for conversions to new systems. In the tranformation I write the new and old Primary keys to a Access table for later processing.

lritchie SSC Enthusiast Points: 178 More actions · Answer 4

I tried the solution from srgangu and it worked very well.

Thank you,

Lance

icata Hall of Fame Points: 3176 More actions · Answer 5

I was tring jan van der Goes's way but I get "Connection is busy with results from another command".

Ionel

roncash Grasshopper Points: 14 More actions · Answer 6

Well I think the emphasis of your question is bundling a number of stored procedures.

If you know how to execute one stored procedure from DTS, i.e. set up a connection object, then a sql execute object, then you can use that one stored procedure to bundle/nest other sp's in.

I would set up one sp called monthlybundle, then within the "monthlybundle" sp, I would include other stored procedures, such as Execute procDailyUpdates

Execute procDailyDeletes

Execute procDailInserts

Of course all of the other stored procedures have to be set up, but the emphasis is that you can Execute many other sp's from within one single sp.

Hope that helps.

Ron

Eoin The Forge SSCrazy Points: 2932 More actions · Answer 7

Orse, if you were not bundling, you could just set then up from a job/task.

rstone SSCertifiable Points: 6209 More actions · Answer 8

On advantage of using a job step is that the step history will contain information from prints or raiserrors. Comes in handy. Good luck in finding this output in a dts package execution.

RandyHelpdesk: Perhaps Im not the only one that does not know what you are doing. 😉