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

#1

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

#2

On Nov 13, 4:58 pm, Tomas Novotny
<TomasNovo... (AT) discussions (DOT) microsoft.com> wrote:

Quote:

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

#3

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

"jhofmeyr (AT) googlemail (DOT) com" wrote:

Quote:

On Nov 13, 4:58 pm, Tomas Novotny
TomasNovo... (AT) discussions (DOT) microsoft.com> wrote:
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

#4

On Nov 14, 4:58 pm, Tomas Novotny
<TomasNovo... (AT) discussions (DOT) microsoft.com> wrote:

Quote:

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

"jhofm... (AT) googlemail (DOT) com" wrote:
On Nov 13, 4:58 pm, Tomas Novotny
TomasNovo... (AT) discussions (DOT) microsoft.com> wrote:
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

#5

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

"jhofmeyr (AT) googlemail (DOT) com" wrote:

Quote:

On Nov 14, 4:58 pm, Tomas Novotny
TomasNovo... (AT) discussions (DOT) microsoft.com> wrote:
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

"jhofm... (AT) googlemail (DOT) com" wrote:
On Nov 13, 4:58 pm, Tomas Novotny
TomasNovo... (AT) discussions (DOT) microsoft.com> wrote:
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

#6