Passing a variable to a DTS Package from SPROC

[Guest]

Hello all,

I am trying to pass a variable from a stored procedure into a dts package. In the dtsrun command (within the stored procedure), I have added the following:

/A batchID:varchar(50)=@messageBatchKey

In the package I added batchID into the global variables tab and set it as a sting. What I need to do is perform a data transfer based on the batchid that I pass in.

Here is what I am doing in the Transform Data Task query section:

SELECT t_sqlbox_outbox_sms.*
FROM t_sqlbox_outbox_sms
WHERE t_sqlbox_outbox_sms.message_log_id = '" & _DTSGlobalVariables("@batchID").Value & "'

Nothing seems to be working though. Can someone help??

Thanks!!!

[The_ScareCrow]

See attached code for an example.
Ken

[Guest]

Ken,

Thank you very much for replying. Do you think the problem is with my dtsrun statement in my stored procedure or in the actual package that is trying to query the table?

Thanks again!!

[The_ScareCrow]

As I don't know what is in your dtsrun or the package, what can I say about them ?

You should be able to use the stored procedure code I supplied, just changing the values to suit your package.

Ken