SSIS, OLE DB Command usage when updating data on AS400/DB2

Tomas Novotny

unread,

Nov 13, 2007, 11:58:05 AM11/13/07

to

Hello,
I have installed Microsoft OLE DB Provider for DB2.
I have a problem with string parameters when updating records on AS400/DB2
when using OLE DB Command component in SSIS.
I have some records stored in MSSQL server datatable, for each row in this
table exist adequate row in AS400/DB2. After some changes (processing) on
MSSQL I would like to update corresponding records in AS400/DB2.
I have parametrized SQL Update query. But when updating some string columns,
there is set just empty string in table in DB2. No errors occured during SSIS
package run. It works when I hardcode some string value in a query directly.
But I need to pass it as parameters to execute UPDATE command for each row
in a source dataset. I use DT_STR data types in package and in db table
varchar type.
Just string parameters are problematic. It works for numeric parameters.
I tried also change CodePage from 1252 to 37 but it had no effect.

Execute SQL Task in For Each Container works but it seems to be slow.

Can you help me? Or suggest some alternative solution?

Thank You.
Tomas Novotny

jhof...@googlemail.com

unread,

Nov 14, 2007, 11:48:24 AM11/14/07

to

On Nov 13, 4:58 pm, Tomas Novotny

Hi Tomas,

Have you tried putting a Data Viewer on your data stream just before
the OLE DB Command task? This will allow you to inspect the data that
is being used as parameters by the task. It may be that one of your
up-stream steps is not setting the columns correctly.

Good luck!
J

Tomas Novotny

unread,

Nov 14, 2007, 11:58:03 AM11/14/07

to

Hi,
yes, I did it.
Value is correct. It seems to be a problem to bound it correctly to a
parameter.
But it is just in case of string values.

Tomas

jhof...@googlemail.com

unread,

Nov 15, 2007, 12:36:02 PM11/15/07

to

On Nov 14, 4:58 pm, Tomas Novotny

> > J- Hide quoted text -
>
> - Show quoted text -

Hi Tomas,

If it was a SQL Server destination, my next step would be to run SQL
Profiler to catch the exact SQL statements that are being executed
against the server. Is there a similar tool you can use with DB2 to
capture the SQL being issued against the database by SSIS?

Good luck!
J

Tomas Novotny

unread,

Nov 15, 2007, 12:56:00 PM11/15/07

to

Hi,
in case of MSSQL OLE DB is used (destination is MSSQL server) then it works
correctly. I don't know about possibility to use some profiling on DB2.

Tomas

dharmbirkumar

unread,

Aug 7, 2008, 5:24:00 AM8/7/08

to

Hi,
I am facing the same problem in Informix.

Can any one help me in this
Thanks
Dharmbir

alanlevy

unread,

Dec 3, 2009, 9:21:11 AM12/3/09

to

I've had the same problems when using ssis to AS400 with HIT driver software. Hit wrote back saying this is a know Microsoft bug and the work around is to create individual insert/update strings and execute them on the AS400.
I am still investigating a more viable solution. Appreciate all the help.

Dharmbir Kumar wrote:

SSIS, OLE DB Command usage when updating data on informix
07-Aug-08

Previous Posts In This Thread:

On Tuesday, November 13, 2007 11:58 AM
TomasNovotn wrote:

SSIS, OLE DB Command usage when updating data on AS400/DB2

Hello,
I have installed Microsoft OLE DB Provider for DB2.
I have a problem with string parameters when updating records on AS400/DB2
when using OLE DB Command component in SSIS.
I have some records stored in MSSQL server datatable, for each row in this
table exist adequate row in AS400/DB2. After some changes (processing) on
MSSQL I would like to update corresponding records in AS400/DB2.
I have parametrized SQL Update query. But when updating some string columns,
there is set just empty string in table in DB2. No errors occured during SSIS
package run. It works when I hardcode some string value in a query directly.
But I need to pass it as parameters to execute UPDATE command for each row
in a source dataset. I use DT_STR data types in package and in db table
varchar type.
Just string parameters are problematic. It works for numeric parameters.
I tried also change CodePage from 1252 to 37 but it had no effect.

Execute SQL Task in For Each Container works but it seems to be slow.

Can you help me? Or suggest some alternative solution?

Thank You.
Tomas Novotny

On Wednesday, November 14, 2007 11:48 AM
jhofmey wrote:

Re: SSIS, OLE DB Command usage when updating data on AS400/DB2

On Nov 13, 4:58 pm, Tomas Novotny
<TomasNovo...@discussions.microsoft.com> wrote:

Hi Tomas,

Have you tried putting a Data Viewer on your data stream just before
the OLE DB Command task? This will allow you to inspect the data that
is being used as parameters by the task. It may be that one of your
up-stream steps is not setting the columns correctly.

Good luck!
J

On Wednesday, November 14, 2007 11:58 AM
TomasNovotn wrote:

Hi,yes, I did it. Value is correct.

Hi,
yes, I did it.
Value is correct. It seems to be a problem to bound it correctly to a
parameter.
But it is just in case of string values.

Tomas

On Thursday, November 15, 2007 12:56 PM
TomasNovotn wrote:

Hi,in case of MSSQL OLE DB is used (destination is MSSQL server) then it works

Hi,
in case of MSSQL OLE DB is used (destination is MSSQL server) then it works

correctly. I do not know about possibility to use some profiling on DB2.

Tomas

On Saturday, November 17, 2007 8:14 AM
jhofmey wrote:

Re: SSIS, OLE DB Command usage when updating data on AS400/DB2

Hi Tomas,

Good luck!
J

On Thursday, August 07, 2008 5:24 AM
Dharmbir Kumar wrote:

SSIS, OLE DB Command usage when updating data on informix

Submitted via EggHeadCafe - Software Developer Portal of Choice
SQL Server: UDF IsValidNumber
http://www.eggheadcafe.com/tutorials/aspnet/62b3708e-d782-4bb1-ba9a-54e941c6b023/sql-server-udf-isvalidnu.aspx