Fixed-width flat file to DB transformation not working

[dmace]

As mentioned in the thread I created a while ago, I took a few rows out of the file I was having trouble with and discovered how to use the testing modes in the transform configuration. I am able to load the source fixed-file properly; all fields are mapped correctly. I have a Postgresql table's columns in the target pane. If I map a literal "test" to a database column (for instance, SSN) and test the transformation, the table field is populated correctly. However, if I drag the flat-file field "SSN" over from the flat-file to associate it with the database column, I get no results when test-transforming. A value is displayed as being mapped on the flat file pane. It just never gets transferred over. If I deploy and actually run the transformation with the one mapping defined, the error log simply says:

All the INSERT operations on the target database failed. [CODE:10672]

All the INSERT operations on the target database failed. [CODE:10672]

Unable to insert data into database. [CODE:10209]

If I run the transform with my FULL mapping, for some reason the log shows the engine generating an insert statement using only 1 of the 64 columns involed. The column is the 1st in the target layout. It tries to place the entire row of data from the fixed file into it according to the insert. For example:

Transformation failed.
Last Error - Insert SQL statement failed to execute (Insert mode). SqlState = S1000 NativeError = 7
ERROR: value too long for type character varying(64)
SQL - Param Data INSERT INTO "intermediate" ("reasonCode") VALUES (?)
Param #0 = '<entire 1st row of file here>'

As you can see, it didn't pick up any but the 1st target column... which isn't even the one I mapped.

Ideas? What other info can I provide which would be helpful?

EDIT: I forgot to mention: The columns in the flat file are all Strings, and are mapping to varchars in the database.

[Frank]

Could you export this project, and send us the Jitterpak so I can see the problem.
It would be helpful if you also send the sample input file and the sql for creating the target database.

my email address is frank.chu@jitterbit.com

Frank

[dmace]

Could you export this project, and send us the Jitterpak so I can see the problem.
It would be helpful if you also send the sample input file and the sql for creating the target database.

my email address is frank.chu@jitterbit.com

Frank

I sent you the things you requested. It's also interesting to note: I created a delimited version of the text structure (comma delimited, no qualifiers) in question, and it seems to work just fine. It's just the fixed width version which is improperly mapping.

[Frank]

After examing the project you sent, I discovered a bug in our TransformationEngine dealing with the flat fixed field text document as the source of transformation. The bug has been fixed. It will be in thenew release due soon.

Thank you for bringing it to our attention.

Frank