PACKAGE ENCODING

Hi, a new (little..) problem with xml source.

I have to import large xml file in sql server and I use data transfer task and xml source.

The xml file are generated without specifing any encoding, and so I obtain many character error if i don't change the encoding.

When i put "by hand" (with a xml editor) in the xml file this encoding <?xml version="1.0" encoding="ISO8859-1"?>, the SSIS task works perfectly without any error.

So, i'm looking for a way to use this encoding without editing xml file (more than 500 mb...). the way that i can imagine are:

1) change the package encoding (but I haven't find this kind of settings)

2) change the xml source encoding (but I haven't find this kind of settings)

3) change the console chcp (normally i have 850, i have tested 1252 but without any success)

4) make a xml trasformation (but i don't know the best way); I've tried with XML task without any success...

could anybody help me?

thank you in advance

alessandro

Hi, an update.

I've found a little "workaround" using XML task - patch. In this way I can patch the original xml file with a diff xml file that contain only the encoding settings. The ouput is a sort of "merge" of the xml file and the new enconding.

the real problem is that this package fail because it parse the xml file before create the new one...and it find the illegal character (that are the reasons for wich i want to change the xml encoding...!!!).

I've seen that it works making a test (replacing the illegal caracther "by hand").

So i have founded another way to obtain the same problem....

Please..:! someone help me!

thank you in advance

Alessandro

|||

....Nobody help me?

Now i'm working on .net xml source with scripting...

lavez

Package cancelled

Hi,
I'm trying to transfer data from a DB2 database to a MS SQL Server database (approx. 150 000 rows).

When I execute the package, the process is always cancelled (In output window: SSIS package "DB2_test_TranAddl.dtsx" finished: Canceled.)

I use a view on DB2 to provide the data and I can modify it to return only a few rows. To achieve this, I use a WHERE clause: WHERE OUTLET_NO Between X And Y.

When I try OUTLET_NO Between 1 And 5 (255 rows) the package executes successfully.
When I try OUTLET_NO Between 5 And 10 (388 rows) it works.

When I try OUTLET_NO Between 1 And 10 (643 rows) it fails!!
Any idea?

How fast does each of the queries run (1-5 and 5-10)? Suppose the connector is timing out?|||

Phil Brammer wrote:

How fast does each of the queries run (1-5 and 5-10)? Suppose the connector is timing out?

A few seconds (less than 5)

The timeouts are set to 0.

I tried changing them to 60 seconds but it doesn't work...

|||

OK here's an update:

I was using the IBM driver, it is not functionnal.

I installed the Microsoft OLE DB provider for DB2 and (after spending like half an hour trying to configure the connection) it works. 150 000 rows transferred successfully in a little more than 8 minutes. Takes about 1min30sec. with the DTS version so I guess I have to do some tweaking...

EDIT: I use the FastLoad on the destination. Takes approx 1min30sec.

|||

Fleo,

I have the same performance issue you mentioned. 300,000 rows takes only 3 minutes with the ODBC connection in DTS, but more than 5 minutes with the microsoft OLD DB provider for DB2 in SSIS. Have you figured out any tweaking that may help speed up the transfer?

Thanks.

|||

Any reason you can't use an ODBC connection in SSIS?

-Jamie

|||I need to load data from SQL2005 to AS400 and there is no ADO.NET/ODBC destination. Also, the unicode issue.|||

Jane2006 wrote:

I need to load data from SQL2005 to AS400 and there is no ADO.NET/ODBC destination. Also, the unicode issue.

Right, well, let's keep your issue to the thread you started: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1723508&SiteID=1

You can use the OLE DB Destination to write to AS400.

Package cancelled

Hi,
I'm trying to transfer data from a DB2 database to a MS SQL Server database (approx. 150 000 rows).

When I execute the package, the process is always cancelled (In output window: SSIS package "DB2_test_TranAddl.dtsx" finished: Canceled.)

I use a view on DB2 to provide the data and I can modify it to return only a few rows. To achieve this, I use a WHERE clause: WHERE OUTLET_NO Between X And Y.

When I try OUTLET_NO Between 1 And 5 (255 rows) the package executes successfully.
When I try OUTLET_NO Between 5 And 10 (388 rows) it works.

When I try OUTLET_NO Between 1 And 10 (643 rows) it fails!!
Any idea?

How fast does each of the queries run (1-5 and 5-10)? Suppose the connector is timing out?|||

Phil Brammer wrote:

How fast does each of the queries run (1-5 and 5-10)? Suppose the connector is timing out?

A few seconds (less than 5)

The timeouts are set to 0.

I tried changing them to 60 seconds but it doesn't work...

|||

OK here's an update:

I was using the IBM driver, it is not functionnal.

I installed the Microsoft OLE DB provider for DB2 and (after spending like half an hour trying to configure the connection) it works. 150 000 rows transferred successfully in a little more than 8 minutes. Takes about 1min30sec. with the DTS version so I guess I have to do some tweaking...

EDIT: I use the FastLoad on the destination. Takes approx 1min30sec.

|||

Fleo,

I have the same performance issue you mentioned. 300,000 rows takes only 3 minutes with the ODBC connection in DTS, but more than 5 minutes with the microsoft OLD DB provider for DB2 in SSIS. Have you figured out any tweaking that may help speed up the transfer?

Thanks.

|||

Any reason you can't use an ODBC connection in SSIS?

-Jamie

|||I need to load data from SQL2005 to AS400 and there is no ADO.NET/ODBC destination. Also, the unicode issue.|||

Jane2006 wrote:

I need to load data from SQL2005 to AS400 and there is no ADO.NET/ODBC destination. Also, the unicode issue.

Right, well, let's keep your issue to the thread you started: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1723508&SiteID=1

You can use the OLE DB Destination to write to AS400.