The packages are running fine in the 32 bit environment, by the way. I am only experiencing this issue on the 64 bit server.
Thnx in advance, for any suggestions or answers.
ScottyeOK... never mind... I figured it out. You have to set the RetainSameConnection value from False to True in the properties of the connection.
The packages are running fine in the 32 bit environment, by the way. I am only experiencing this issue on the 64 bit server.
Thnx in advance, for any suggestions or answers.
ScottyeOK... never mind... I figured it out. You have to set the RetainSameConnection value from False to True in the properties of the connection.
Can you post a specific error message? It might also be a good idea to
check in a DB2 or IBM forum to see if there are known issues with that
specific version of MDAC. MDAC 2.8 is available, so you could also try
upgrading to see if that resolves your issue, although there's no
specific reference to DB2 in the release notes:
http://support.microsoft.com/default.aspx?kbid=820761
Simon
Can you post a specific error message? It might also be a good idea to
check in a DB2 or IBM forum to see if there are known issues with that
specific version of MDAC. MDAC 2.8 is available, so you could also try
upgrading to see if that resolves your issue, although there's no
specific reference to DB2 in the release notes:
http://support.microsoft.com/default.aspx?kbid=820761
Simon|||Hi ,
I also need to access DB2 table in my SQL Server DTS package. but can you give me from where you downloaded DB2 ODBC drivers where I can install and start using DB2 in DTS package.
Regards
Sridhar.B
Quote:
Originally Posted by Simon Hayes
gastineau5@.comcast.net (Zach Gastineau) wrote in message news:<a3883fcb.0404280717.36c3f09f@.posting.google.com>...
> I'm having problems using the DB2 Connect ODBC Driver in my SQL Server
> 2000 DTS packages. It started occuring after installing MDAC 2.7
> (which is necessary for the .NET framework). This error occurs when I
> try to open the transformation that uses an ODBC Connection object
> that is pointing to the DB2 ODBC. Has anybody else seen this or has a
> solution other than re-installing everything and not loading the .net
> framework mdac 2.7. I would like to get the DB2 ODBC driver working
> with MDAC 2.7.
Can you post a specific error message? It might also be a good idea to
check in a DB2 or IBM forum to see if there are known issues with that
specific version of MDAC. MDAC 2.8 is available, so you could also try
upgrading to see if that resolves your issue, although there's no
specific reference to DB2 in the release notes:
http://support.microsoft.com/default.aspx?kbid=820761
Simon
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 DaveYes, 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