1

In our DTS package we copy data from Oracle from column VARCHAR2(50 CHAR) (Unicode) to Sybase column VARCHAR(50) (Nonunicode). Source is Oracle DB where we select data from sql query.

Next one is Data conversion where we transform data to STRING[DT_STR] 50 (CODE PAGE 1250). Finally are data inserted to Sybase table column Varchar(50). But after running we see only empty columns, no original values.

Where can be the error?

Update 1

If i execute SELECT @@VERSION on the Sybase server i got the following information:

Adaptive Server Enterprise/15.7/EBF 21520 SMP SP102 /P/ia64/HP-UX B.11.31/ase157sp101/3445/64-bit/FBO/Sat Jul 13 05:47:31 2013

And i am using OLEDB Provider to connect to Sybase destination

Hadi
  • 36,233
  • 13
  • 65
  • 124
David Novy
  • 89
  • 1
  • 9
  • The problem is only in String columns. In Integer columns is not problem. – David Novy Feb 12 '19 at 06:58
  • 1
    Please check that the column mapping on the destination is correct (the data conversion adds new columns), you can also add a Watcher on the flow exiting the data conversion to corroborate the cast. Also check the amount of rows before each task, you might be losing rows because of failing casts if they are set to ignore on failure. – EzLo Feb 12 '19 at 07:25
  • We get all rows. But String columns miss. – David Novy Feb 12 '19 at 07:41
  • Source column: c_poj_sml --------- value1 value2 etc. Destination column: c_poj_sml --------- NULL NULL – David Novy Feb 12 '19 at 07:42
  • Add data viewers to check if there is a data loss in the data flow – Hadi Feb 12 '19 at 08:34
  • You're copying from a double-byte column (unicode) to a single byte column (non-unicode) - if you need to handle unicode data you should use nvarchar() for the column in Sybase or you can pretty much guarantee you will run into issues with character conversion at some point, especially if you use Chinese or similar characters which require both bytes. – Rich Campbell Feb 12 '19 at 08:40
  • I enabled data Viewer. Select is OK. Transformation is OK as well. Problem is in insert. In DEBUG I can not see problem, ale data miss. I will recommend to development section in other to change columns to NVARCHAR. – David Novy Feb 12 '19 at 08:57
  • @DavidNovy what is the destination used for Sybase? and what is the provider? – Hadi Feb 12 '19 at 20:19
  • SELECT @@VERSION Adaptive Server Enterprise/15.7/EBF 21520 SMP SP102 /P/ia64/HP-UX B.11.31/ase157sp101/3445/64-bit/FBO/Sat Jul 13 05:47:31 2013 Provider is OleDB – David Novy Feb 13 '19 at 08:44

1 Answers1

0

Use ADO.Net instead of OLEDB

Based on your comments, it looks like you are using OLEDB Provider to connect to Sybase. The prefered way to connect to Sybase is using ADO.Net providers not OLEDB.

Sybase OLEDB Provider issues

With a small search on the web you can realize that Sybase OLEDB provider may cause several issue when used in SSIS, as example:

Sybase ADO.NET

ADO.Net is the proper way to connect to sybase from SSIS.

In the following official documentation they mentioned that:

Adaptive Server ADO.NET Data Provider can be integrated into SQL Server Integration Services (SSIS), allowing for native access to ADO.NET Data Provider functions.
With the integration, you can use Adaptive Server as an:

  • ADO.NET Connection Manager
  • ADO.NET Source data flow component
  • ADO.NET Destination data flow component

Also in the official download page, they didn't mentioned OLEDB provider but they mentioned ADO.NET

Step by step guide to use Sybase ADO.Net data provider

You can check the following link:

Workaround

If ADO.Net didn't work, check that the issue is not caused by some unicode characters that are not supported in Sybase destination.

Try adding a script component, add an Output column of type DT_STR, and use the following code:

Row.outColumn = Encoding.GetEncoding("Windows-1250").GetString(Encoding.GetEncoding("Windows-1250").GetBytes(Row.inColumn))

And map outColumn to the destination.

Community
  • 1
  • 1
Hadi
  • 36,233
  • 13
  • 65
  • 124