can't insert dt_str to char type

Question

Hi,

Having problem with OLE-DB connection to informix.

Created a flat file has two columns and 2 row:
1|a
2|b

tried to load it to a informix table:
create table t
( c1  int,
  c2 char
);

2 rows inserted, but only column c1 got data.

Changed c2 from char to varchar then ok.

thanks for any help.

gg

Answer 1

 

Check the size of c2 in your create table statement as Default size is 1 byte.Make sure whether it can hold dt_str

 

http://msdn2.microsoft.com/en-us/library/ms141036.aspx

Answer 2

Also, can you please post the complete error message you're receiving?

 

Answer 3

Adding few more inputs.

http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.ddi.doc/ddi65.htm

Whenever the database server retrieves or stores a CHAR(n) value, it transfers exactly n bytes. If an inserted value is shorter than n, the database server extends the value with single-byte ASCII space characters to make up n bytes. If an inserted value exceeds n bytes, the database server truncates the extra characters without returning an error message. Thus the semantic integrity of data for a CHAR(n) column or variable is not enforced when the value that is inserted or updated exceeds n bytes.

Answer 4

PK_SSIS wrote:

Adding few more inputs. http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.ddi.doc/ddi65.htm Whenever the database server retrieves or stores a CHAR(n) value, it transfers exactly n bytes. If an inserted value is shorter than n, the database server extends the value with single-byte ASCII space characters to make up n bytes. If an inserted value exceeds n bytes, the database server truncates the extra characters without returning an error message. Thus the semantic integrity of data for a CHAR(n) column or variable is not enforced when the value that is inserted or updated exceeds n bytes.

But none of this information applies, unless Gary is changing the length of the column when switching to varchar.  If the lengths are the same, then your information doesn't apply here.  We really need the error message to continue troubleshooting.

Answer 5

If I am not wrong (as per link )  there might not be any error message atleast  from informix and for integer column  data is getting inserted.If there are error message from ssis that would be great.Correct me if i am wrong.

 

Whenever the database server retrieves or stores a CHAR(n) value, it transfers exactly n bytes. If an inserted value is shorter than n, the database server extends the value with single-byte ASCII space characters to make up n bytes. If an inserted value exceeds n bytes, the database server truncates the extra characters without returning an error message. Thus the semantic integrity of data for a CHAR(n) column or variable is not enforced when the value that is inserted or updated exceeds n bytes.

Answer 6

You are right, there is no error message from both site.  2 rows were inserted, but c2 is blank. Also tried to change to char(2), still not good.

thanks.

Answer 7

garygu wrote:

You are right, there is no error message from both site.  2 rows were inserted, but c2 is blank. Also tried to change to char(2), still not good. thanks.

Add a data viewer right before your destination in the data flow and ensure that you actually have data in that c2 column.

Answer 8

also tried this, the viewer displays correctly.

Answer 9

I'm having the same problem. Has anyone found a solution to this?

I'm trying to insert data into informix table. numeric and date types go well, char fields get "blank" string values. if I load data into the same table in sql server using the same dataflow, all is ok.

Answer 10

What is the code page set to on your DT_STR columns?

Answer 11

In order to avoid other issues, I'm loading data from Table X from Informix with ole db source and then I unloding the same date in the same Table X in the same database on Informix by using ole db destination. Ole db source and ole db destination have AlwaysUseDefaultCodePage set to True and the code page for db source, db destination and all string columns is the same 1250

?

Thanks!

Answer 12

By working with IBM support, we found that SSIS inserts became null values when inserted into Informix through OLEDB, or when using an OLEDB Command against the Infomix OLEDB provider in the SSIS Dataflow.

This is a known defect in the IBM Informix OLEDB Provider.
The APAR is:
   IC61845 CHAR COLUMN IS INSERTED AS BLANK FROM A SSIS TRANSFORMATION PACKAGE WHEN USING AN OLEDB COMMAND
Future link: http://www-01.ibm.com/support/docview.wss?uid=swg1IC61845

The fix for this APAR would be included in the next version of the CSDK / ICONNECT package (3.50.TC6)
Customers with a support contract can contact IBM directly to request a special build containing this fix (3.50.TC4X4)

Thanks, Jason

---

Didn't get enough help here? Submit a case with the Microsoft Customer Support team for deeper investigation - http://support.microsoft.com/select/default.aspx?target=assistance

Answer 13

Although this is a very old debate, do you have information if this defect is fixed?

Because, currenty I have IBM Informix ClientSDK 3.70 TC5DE and I still have this problem that CHAR COLUMN IS INSERTED AS BLANK FROM A SSIS TRANSFORMATION PACKAGE WHEN USING AN OLEDB COMMAND.

Thanks, Kruno

Answer 14

I personally don't have an IBM account. Can you get to the link? It gives me a 410 error.

---

Didn't get enough help here? Submit a case with the Microsoft Customer Support team for deeper investigation - http://support.microsoft.com/select/default.aspx?target=assistance

Answer 15

This page lists the same title as the above fix in the items that are fixed in the Informix client SDK release: 3.50.xC8

http://publib.boulder.ibm.com/infocenter/idshelp/v115/index.jsp?topic=%2Fcom.ibm.relnotes.doc%2Fcsdk_350xc8%2FDEFECTS.HTML

---

Didn't get enough help here? Submit a case with the Microsoft Customer Support team for deeper investigation - http://support.microsoft.com/select/default.aspx?target=assistance

Answer 16

Yes, I saw that this is fixed in v.3.50.xC8.

But as I mentioned, I have IBM Informix ClientSDK version 3.70 TC5DE installed on Windows Server 2003 and Windows Vista and I still have this problem that CHAR COLUMN IS INSERTED AS BLANK FROM A SSIS TRANSFORMATION PACKAGE WHEN USING AN OLEDB COMMAND.

Does anyone have similar problems?

---

Kruno

Answer 17

You'll probably need contact IBM with that question since their support team made the prior fix mentioned in this thread.

The page used to list the bug description as "CHAR COLUMN IS INSERTED AS BLANK FROM A SSIS TRANSFORMATION PACKAGE WHEN USING AN OLEDB COMMAND"

But now I can't find that title. Maybe they reworded it... not sure if its the same or different as this one:

IC67106 ESQL/C-GENERIC NULL VALUE INSERTED INTO A CHAR COLUMN IS
ALWAYS STORE AS A BLANK IF 'BLANK_STRINGS_NOT_NULL VARIABLE' IS SET

I have no idea what fixes get ported forward to the Informix ClientSDK 3.70 builds. Do they normally forward port fixes from 3.50 to 3.70?  Can you find the matching docs and see if the bug is mentioned in the "Defect Fixed Addendum" article? You may have to contact them to ask them to port the fix to the 3.70 version.

This URL seems to list the fixes in 3.70 from xC1 to xC5 (not sure what the naming convention means) but I don't see a matching fix description.

http://publib.boulder.ibm.com/infocenter/idshelp/v117/topic/com.ibm.relnotes.doc/notes/csdk_370xc5/DEFECTS.HTML

Thx, Jason

---

Didn't get enough help here? Submit a case with the Microsoft Customer Support team for deeper investigation - http://support.microsoft.com/select/default.aspx?target=assistance