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.
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.
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 ?
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.
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.
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....
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.
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..