DB2 and Service Pack 2

Hi

We have recently upgraded to SP2, and some of the packages that used to run under SP1, simply don't work anymore. The thing is everytime we try to access DB2 using a SQL Task with at least 1 parameter, the task fails with the following error.

CLI0109E String data right truncation. SQLSTATE=22001 Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

The SQL Task works as long as I don't use parameters, but if I try something like this

SELECT CHAR(current timestamp) FROM sysibm.sysdummy1 where 'SAMPLE' = ?

and I try to map a String variable, that contais the value SAMPLE, to its only parameter,

User::Var1 INPUT VARCHAR 0 -1

the task fails.

Has anyone faced the same problem using IBM's OLE DB provider for DB2 after installing SP2?
I would really appreciatte any suggestions/comments on this matter.

Thanks.

While I don't know what might be causing the issue, a workaround would be to use a package variable to contain the SQL command and update the variable with a Script task before the Execute SQL task runs. In the Script task, you could update the variable and concatenate in the value of your parameter, producing a complete string that represents the parameterized SQL you want to run. This would eliminate the need for a parameterized query.

HTH,

Dave Fackler

|||Hello Dave

Yes, I suppose I could use a Script Task to build the query, and if I only had 1 sql task I would probably do it, but the truth is I've got several packages, each one having several SQL Tasks accesing DB2. Besides, in the near future, more people from my department will be using SSIS to access DB2, so sooner or later we will have to address this problem.

Due to the nature of the error, my guess is the new 'Parameter Size' field has something to do with it, but I cannot be certain.

Thanks|||Just a follow up

I've just noticed that some of my SSISs actually do work. The difference is that in these packages the SQL Tasks use the SUBSTR function to process the parameter. As far as I know SUBSTR processes a character expression and returns a VARCHAR, so I guess I'm experiencing a data-type conversion problem.

I would really appreciatte it if someone could tell me if they are able to use a SQL Task with parameters to access DB2 using IBM's OLE DB Provider for DB2 and SQL Server 2005 SP2.

Thanks in advance.|||

A number of changes were made to the SQL Task in SP2 to improve our handling of variable length parameters (mainly string types). I didn't think they affected input parameters, but if you're only seeing the problems after installing SP2, they could be related.

The last field on the parameter mapping page sets the parameter size. Since your error message is relating to string truncation, could you try setting this value to something large enough to hold your string? (1024, for example). The default value (-1) tell SSIS to guess at the correct size to use, and we may be having trouble retrieving the size from the DB2 provider.

|||Hello everybody

I had the same problem with data truncation. Then I changed the connection from OLE DB to ODBC. Now it works fine.

However you to adapt the parameter names according to the yellow table in this entry: http://www.msdner.com/dev-archive/5/19-80-57637.shtm

For string values I took the datatype "SQL_WVARCHAR".

Hope this helps...

Cheers

sql