November 11, 2008 at 2:41 pm
Hello,
I'm rather new to SSIS so any help would be appreciated.
I've setup a Foreach loop (ADO Enumerator) to loop through a full result set from a query. The first thing I do in my Foreach loop is Execute a Script Task which just prints a variable value in a MsgBox. The value of my variable being printed is exactly what it should be. Next, I execute an SQL Task which just selects a count(*) from a table where id = ? (this is an odbc connection, thus the ?). This should plug in the variable value that I just printed successfully. In the Parameter Mapping of my SQL Task, I use the same variable name that I printed in the previous Script Task. The Direction is Input and the DataType is SQL_VARCHAR (this is actually a string in my list of variables but from what I read, the datatype here is dependant on the connection which is ODBC to MSSQL 2005). The Parameter Name is 1. This runs ok but the result of my select count (*) was incorrect so I ran a trace and found that the value of my variable is being truncated down to one character (the first character of my variable value). Has anyone else ran into this before? What am I doing wrong?
Thanks in advance for your help.
FR
November 12, 2008 at 8:24 am
Just a little more info: If I do this using an OLE DB connection, it works fine. The DataType is then a VARCHAR. Why is the SQL_VARCHAR from my ODBC connection being truncating to the first char? Anyone?
October 13, 2011 at 3:07 am
Hi,
I was having the same problem and was pulling my hair out. I tried testing all of the string-esque variable types to make sure that SQL_VARCHAR was the right one to be using.
It wasn't. SQL_WVARCHAR is the one you want. Seems that SQL_VARCHAR defaults the length to 1, regardless of the length you specify. I'm using SQL_WVARCHAR(4000) and it's doing the job.
Hopefully that works for you.
Thanks
Phil
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply