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

[Tomas Novotny]

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

[j***@googlemail.com]

On Nov 13, 4:58 pm, Tomas Novotny

Post by Tomas Novotny
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

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]

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

Post by j***@googlemail.com
On Nov 13, 4:58 pm, Tomas Novotny

Post by Tomas Novotny
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

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

[j***@googlemail.com]

On Nov 14, 4:58 pm, Tomas Novotny

Post by Tomas Novotny
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

Post by j***@googlemail.com
On Nov 13, 4:58 pm, Tomas Novotny

Post by Tomas Novotny
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

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- 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]

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

Post by j***@googlemail.com
On Nov 14, 4:58 pm, Tomas Novotny

Post by Tomas Novotny
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

Post by j***@googlemail.com
On Nov 13, 4:58 pm, Tomas Novotny

Post by Tomas Novotny
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

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- 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

[unknown]

Hi,
I am facing the same problem in Informix.

Can any one help me in this
Thanks
Dharmbir

[unknown]

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

Hi
I am facing the same problem in Informix

Can any one help me in thi
Thank
Dharmbir

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 Novotn
<***@discussions.microsoft.com> wrote

Hi Tomas

Have you tried putting a Data Viewer on your data stream just befor
the OLE DB Command task? This will allow you to inspect the data tha
is being used as parameters by the task. It may be that one of you
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
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 work
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
On Nov 14, 4:58 pm, Tomas Novotn
<***@discussions.microsoft.com> wrote

Hi Tomas

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

Good luck
J

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

SSIS, OLE DB Command usage when updating data on informix
Hi
I am facing the same problem in Informix

Can any one help me in thi
Thank
Dharmbir


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