August 22, 2008 at 2:20 pm
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
August 22, 2008 at 2:51 pm
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.
August 22, 2008 at 2:57 pm
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.
August 22, 2008 at 5:12 pm
If you are iterating through comma delimited string, get the full row set of ID's and Iterate over it using FOR LOOP. 😉
August 25, 2008 at 7:28 am
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.
August 25, 2008 at 9:23 am
You'll still need to change the data type to 'object'. Try that out and let us know if you get past your error.
August 25, 2008 at 9:32 am
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
August 25, 2008 at 9:46 am
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.
August 25, 2008 at 10:44 am
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
August 25, 2008 at 12:34 pm
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?
August 25, 2008 at 12:42 pm
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