DTS SQL Task Trauma

Question

Post reply

DTS SQL Task Trauma

Rick Mortensen-169991

Grasshopper

Points: 17
More actions
September 8, 2004 at 4:09 pm

#60211

Greetings SQL Wizards,

I am using DTS to extract a specific value from a csv file. That value I am then setting to a DTSGlobalVariable (activeX task 1). The value of this global variable is set in task 1 as DTSGlobalVariables("export_id"). I would now like to execute a sql task based on the value of that variable.

This seems like it should be a piece of cake, but when I open the SQL task and try to access that globalvariable as a parameter, I am unable to see the variable. I am trying to execute the following statement:

INSERT into tbl1 select * from tbl2 where export_id = ?

This doesn't work either -

INSERT into tbl1 select * from tbl2 where export_id = DTSGlobalVariables("export_id")

When I try to set the parameter (by clicking on the "Parameters" button), that global variable doesn't show up. Any ideas, or feedback if I should perform this action a different way. Thanks in advance.

Viewing 7 posts - 1 through 6 (of 6 total)

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

bobsterboy SSCommitted Points: 1539 More actions · Answer 1

bobsterboy

SSCommitted

Points: 1539

September 8, 2004 at 10:00 pm

#522227

Change your statement to

INSERT into tbl1 select * from tbl2 where export_id = @GlobalParameterVariable

Then click on parameters button. If that doesn't work, (it works on my machine), you might need to get the latest service pack.

sontracteur SSC Veteran Points: 290 More actions · Answer 2

Hi !

I think your problem come from your request ! It's too COMPLEX !

For using the "?" syntax, you must use the most simple syntax.

In your case, I define a procedure and I call it. A simple "exec MyProc ?" will work fine I'm sure.

The parser simply does not recognise the syntax when you do more than very very basic syntax queries.

Rick Mortensen-169991 Grasshopper Points: 17 More actions · Answer 3

Thanks for your feedback. That may be the route I will have to take as I am unable to see that global variable from within the SQL task. I am thinking that maybe it has something to do with the fact that the variable is being created and assigned assigned as a piece of an ActiveX task being executed in a prior step in this package, and not just defined as a constant for the package.

Sheesh, I hope I'm able to properly state what may be happening. Any ideas on why you think one wouldn't be able to use a DTSGlobalVariable value in a SQL task??? I believe, that it doesn't appear in the global variables section of the package properties because it is extracted from a file and then used throughout the package. ARGH!?!?!?!?!! I could be way off here, but I figured there's got to be a reason I'm not seeing it. hmmmmmm

I really appreciate your feedback and you taking time to take a peek at this "feature".

sontracteur SSC Veteran Points: 290 More actions · Answer 4

Hum !

I didn't understand you have created the variable in the ActiveX task. To be honest it has never occured to me doing that way ... and believe me, I've done a lot of DTS packages. I have always created my variables in the package properties and it has always worked out fine !

So, create the variable in the package propertie, assign it the value from your file in your activeX task, and I'm pretty sure you will see it in your SQL task.

Also be aware of the complexity problem I've talked about first.

Bye.

Tiago Rente Default port Points: 1403 More actions · Answer 5

Tiago Rente

Default port

Points: 1403

September 10, 2004 at 8:35 am

#522560

How can you use the Global Variable in the SQL Task?

I created the Global Variable in the Package using the Explicit Global Variables option.

Thanks in advance.

Rick Mortensen-169991 Grasshopper Points: 17 More actions · Answer 6

Thanks so much sontracteur as your suggestion gave me what I needed to get my package working. I created the global variable in the package properties with no value. I assigned a value to it through the ActiveX task spoken of earlier with the following statement - DTSGlobalVariables("export_id").value = MYVALUE. I then modified the SQL task to:

INSERT INTO tbl1 SELECT * FROM tbl2 where export_id = ?

I then clicked on the Parameters button in the SQL task and chose my global variable (export_id) and associated it with Parameter 1. Everything seems to work fine.

I hope this explanation helps Tiago Rente and thanks again sontracteur for your insight that made me look at my processes a little differently and arrive at the solution.