Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Aug 2004
    Location
    Calgary, Alberta
    Posts
    106

    Unanswered: 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,
    -Kilka

  2. #2
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    Hmmm, 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.)

  3. #3
    Join Date
    Aug 2004
    Location
    Calgary, Alberta
    Posts
    106
    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

  4. #4
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    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...

  5. #5
    Join Date
    Aug 2004
    Location
    Calgary, Alberta
    Posts
    106
    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

  6. #6
    Join Date
    Aug 2004
    Location
    Calgary, Alberta
    Posts
    106
    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 ?

  7. #7
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    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....

  8. #8
    Join Date
    Aug 2004
    Location
    Calgary, Alberta
    Posts
    106
    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.

  9. #9
    Join Date
    Aug 2004
    Location
    Calgary, Alberta
    Posts
    106
    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

  10. #10
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    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?
    Last edited by rokslide; 08-09-04 at 20:17.

  11. #11
    Join Date
    Aug 2004
    Location
    Calgary, Alberta
    Posts
    106
    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

  12. #12
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    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....

  13. #13
    Join Date
    Aug 2004
    Location
    Calgary, Alberta
    Posts
    106
    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

  14. #14
    Join Date
    Aug 2004
    Location
    Calgary, Alberta
    Posts
    106

    Transform data task IN clause with string.

    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

  15. #15
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    Can you post the entire sql statement??

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •