Error when returning results of Execute SQL Task component to a string variable

  • Hello,

    In my Execute SQL Task, I run the following query:

    DECLARE @IDList varchar(max)

    SELECT @IDList = COALESCE(@IDList + ', ', '') + ID

    FROM dbo.IDs

    WHERE RowNumber >= 1 and RowNumber <= 5

    SELECT @IDList as IDList

    It returns something like this: 014023, 418738, 42108, 420109, 041592

    The result set is set to single row, and I have the result name in the result set set to IDList.

    The variable I am trying to put the result into is a string and i get the following error:

    Error: 0xC002F309 at Execute SQL Task 1, Execute SQL Task: An error occurred while assigning a value to variable "IDList": "The type of the value being assigned to variable "User::IDList" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.

    I'm guessing it doesn't like that the return value is a varchar and the variable is a string. But I have tried to change the variable to char and had no luck. Not sure what to do next.

    Thanks in advance for any help.

    D

  • Try changing the variable from Varchar(max) to varchar(4000) in your sql query. I tested it and it worked for me when i made the change.

    SSIS is not able to map varchar(max) to a string type.

  • Any time you are storing a recordset (even if its a single row) into a package variable, you must define it as an 'object' data type, not string. Change User::IDList from string to object and it will work.

    Just curious, but do you plan on iterating through the comma delimited list once it is in the variable? If so, skip the single row variable assignment, use the full result set option and a for each loop container to iterate through the results.

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • If you are iterating through comma delimited string, get the full row set of ID's and Iterate over it using FOR LOOP. 😉

  • My goal is to put the comma delimited single row result into a variable and then use that variable in an sql statement in a subsequent component in my package. the list of IDs I am selecting is the only dynamic part of the sql statement.

    Is this the best way to do this or am I missing something?

    Thanks for all the replies.

  • You'll still need to change the data type to 'object'. Try that out and let us know if you get past your error.

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • It worked both when i changed to to varchar(4000) and when I left it as varchar(max) and changed the result to object.

    Now I am trying to determin what the optimal way is to plug that variable into another string variable that is an sql statement.

    Example:

    the new variable I am trying to construct is a string i want to look like this:

    select * from table where ID in (@IDList)

    the variable IDList is the comma delimited list of IDs i created previously. would I be able to use a variable that is type object in this way? obviously i can test to make sure which is what I am trying to do now.

    Thanks again

  • Is that the only other place you'll be using the IDList? Maybe it would help if you could give a brief description of the package's purpose and the logic flow through the packge as to where this IDList fits in.

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • Here is the lowdown:

    I am trying to connect to an outside datasource (via data reader source - only way I can go) that only allows me to pull 2000 records at a time. So I run an initial unrestricted query to get a list of ID numbers, then I need to query the source again multiple times, pulling 2000 records at a time.

    A for loop seems to be the way to go, so I am in the process of trying to figure out the logic to populate the string variables that contains the select statements for the data reader source. Right now I have this flow:

    1. Retrieve IDs using initial unrestricted query and put in a table

    2. Add a column to the table with row numbers

    3. Put the number of rows to be imported into a variable called @NumberOfRecords

    Then comes the For Loop:

    I put the following values in my For Loop:

    InitExpression - @ForLoopCounterBegin = 1

    Eval Expression - @ForLoopCounterEnd < @NumberOfRecords + 2000

    AssignExpression - @ForLoopCounterBegin = @ForLoopCounterBegin + 2000

    Inside the for loop:

    4. Script task to increment the variable @ForLoopCounterEnd to @ForLoopCounterEnd + 2000

    5. Script Task to create sql statement to get next 2000 IDs and put in the variable @SQLCmd_GetIDs

    6. Execute SQL Task using variable @SQLCmd_GetIDs to run the query that gets the comma seperated list of LNs and puts the result into a variable called @IDList

    DECLARE @IDList varchar(4000)

    SELECT @IDList = COALESCE(@IDList + ', ', '') + ID

    FROM dbo.IDs

    WHERE RowNumber >= @ForLoopCounterBegin and RowNumber <= @ForLoopCounterEnd

    SELECT @IDList as IDList

    It returns something like this: 014023, 418738, 42108, 420109, 041592

    7. Script task to use the variable containing the comma delimited list of IDs into a SQL select statement that will be put into the variable @GetData that I will incorporate into step 8

    It should look something like this:

    select * from table where ID in (014023, 418738, 42108, 420109, 041592)

    8. Data flow that uses @GetData as the sqlcommand in a Data Reader Source component and puts 2000 rows of data in the result table

    Then rinse, lather and repeat steps 4-8 until I process all of the records.

    Using the script task in steps 5 and 7 to create the sql select statements and stick them into string variables is slowing me down quite a bit.

    Feel free to pick apart this process and suggest alternative methods, as this is my first pass at using SSIS for something like this.

    Dave

  • A couple of quick querions:

    1. Are you using the for loop container or coding your own loop in a script task?

    2. What is the reason for the 2000 row batches, performance, business rule, etc?

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • 1. Loop container

    2. No idea, this is from a vendor. I'm guessing they want to keep us from bogging down their server (data is available to many people)

    Thanks

Viewing 11 posts - 1 through 10 (of 10 total)

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