DTS global variable problems...

Hey all,

I've recently been attempting a transform data task with a custom query for the source. Using the query, i've attempted to use global params, but it only ever seems to work if there is only one item in the global var. If I return an entire resultset, I get a "EXCEPTION_ACCESS_VIOLATION" instead. I'm trying to use it like "SELECT * FROM whatever WHERE ID IN(?)"

I've pondered this problem for quite some time now and I am wondering if there is a workaround for it. I know it would take much too long to do the same thing in activeX with a transform, so I would rather do it this way if I could.

Thanks in advance,
-KilkaHmmm, I'm not sure why that shouldn't work.... how are you setting your global variable??

An alternative way would be to use an ActiveX task to set the query value (eg. instead of setting a global variable set the query for the transform data task.)|||I've created them when I specify the IN (?) parameter under the parameters button. I then fill them with some ID's from several xls spreadsheets. I know that this procedure is working correctly because I wrote some activeX to spit out the contents of the var, and the size of the var. Everything there is right on the money. Also, when I specified the global variables, I set them as type <empty>, which is what I think im supposed to do according to msdn. I'm thinking that maybe this has something to do with the IN and it's param, as I'm doing this for 1500 items or so.

Thanks rokslide,
-Kilka|||errghhh, you might be running into a length problem... I suspect that there is a maximum length on the sql query for the transform data task...

Could you put the id's into a temp table and then do a join or something?

Can you test it without using so may items? perhaps only 10 or something...
that way you can try and eliminate possible problems...|||No, I've tried that already too. I've reduced the resultset so it only has two elements and I still get the same problem. I've verified that there were only two elements with the activeX script.

-Kilka|||Also,

Just thought that I should mention my global variables both appear as type "Dispatch" under the package properties. I was also thinking that perhaps I should try using another statement other than IN(?) in my data transformation. Is there anything like IN that would give me the same results ?|||apart from using multiple or statements, no, not really unless you create a temp table and join on it...

if you don't try and set the sql using a global variable what happens. does it still fail or does it work, I'm beginning to wonder if the problem isn't somewhere else...|||Ok,

So i've done some more playing around with this, still to no avail. Deleting the global var should and does give me "Global Variable 'chid2' not found'". If I create the variable but don't set it, I get "Invalid character value for cast specification". Finally if I set the var, even if the resultset has just one element, I still get the EXCEPTION_ACCESS_VIOLATION.

This is the error from the log

Step 'Copy Data from Results to Results Step' failed

Step Error Source: Microsoft Data Transformation Services (DTS) Package
Step Error Description:Provider generated code execution exception: EXCEPTION_ACCESS_VIOLATION
Step Error code: 80040005
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:700

I've looked up this error and made sure that the Transform Data Task is executing on the main thread. I think I've done this right. When I put the param in this task, and it's already been filled, I get "No value given for one or more parameters" when attempting to do a preview.

Also, i've noticed that specifying one param, such as id=? in my source query works fine, it's just a rowset that does not work.|||also, it's just come to my attention that I get the same error when running test under the Tranformations tab of the Transform Data Task properties. The type of tranformation is a copy column.

Thanks in advance,
-Kilka|||So it may not be the querying at all but sme problem with the transform itself. Perhaps columns of the wrong data types or something affecting things?

If you completely replace the whole global variable bit and replace it (temporarily) with a static value and then try and execute it what happens?|||I've already tried that with an IN(1,2,3,4,5,5) in my source query. That seems to have worked fine.

I'd rather be doing as little processing in activeX as possible, but it's starting to look like I'll have to. I can just the global vars fine with activeX, but the problem is going to be performance. If anyone can think of anything else, I would be glad to hear it.

I was also thinking about constructing my string in a activeX object before this transformation, but I'm not sure if I'm allowed to use exec in a source like that because DTS won't know what columns I'm selecting much less moving.

Come to think of it, do you think the problem might be that there are no "," between elements of the global var. I think I'm going to try that next.

Thanks again rokslide,
-Kilka|||here's a thought that may or may not work....

what happens if you add an execute sql stask to your dts package and get it to try and execute the sql statement that is in your data transformation task?

does it still have a problem?? if not you could slip the execute sql task n before your transformation and get it to populate a temp table, then you can have your transformation execute a query that joins to the temp table to determine it's records...

just a thought...|||Yeah, it turns out I'm not in a position to do that. I can't create a temp table. I think my solution will be to cycle through the records using an activeX transformation and only copy the ones I need. I know this is a slow and stupid way to do it...single threaded too :( However, thanks for the help.

Cheers,
-kilka|||So now, I've taken to using a string to use my IN clause. however, there is some wierdness.

Assuming 445 is legit:
If I have 0,0,0,0,445 in the IN clause, I get the correct resultset
If I have 445,0, I get nothing
If I have 0,1,0,0,445 in the IN clause, I get nothing.
If I have 445 in the IN clause, it works fine..

Can anyone shed any light on this problem ?

-Kilka|||Can you post the entire sql statement??|||So I figured it out, got things to work dynamically.

So upon further investigation i've discovered that every data transformation object has a datapump task associated with it. The datapump task can have the source sql statement altered under the disconnected edit tool. I also found out that this variable can be edited using the following VB code:

' 205 (Change SourceSQLStatement)
Option Explicit

Function Main()
Dim oPkg, oDataPump, sSQLStatement

' Build new SQL Statement

sSQLStatement = "select * from <table> where ID IN(" & DTSGlobalVariables("instring2").value & ") OR ID IN (" & DTSGlobalVariables("instring3").value & ")"

' Get reference to the DataPump Task
Set oPkg = DTSGlobalVariables.Parent
Set oDataPump = oPkg.Tasks("DTSTask_DTSDataPumpTask_1").CustomTask

' Assign SQL Statement to Source of DataPump
oDataPump.SourceSQLStatement = sSQLStatement

' Clean Up
Set oDataPump = Nothing
Set oPkg = Nothing

Main = DTSTaskExecResult_Success
End Function

I would run this after I built the instring global variables. I then run the activeX task and finally the transformation task. I'm going to post a big howto at some point when I get time as well.

At present however, I'm now stuck with the fact that I can't go:
DTSDestination("CellPhone") = Left(DTSSource("CellPhone"),Len(DTSSource("CellPhone"))-4)

in the transformation. When I hit parse, it works, however when I hit test/execute the package, I get "Invalid procedure call or argument:'Left'" I know for a fact is the second argument that's causing the problem, but can't figure out why this wouldn't be allowed.

Thanks,
-Kilka|||Hi there,

Actually that was kinda what I was meaning, or something similar,... with your lastest problem you need to check for cellphone values that are less then 4 long. if a value is less then 4 (and possibly 4 as well) your call will fail.

Cheers,
Roko