AS400 to SQL via SSIS

  • I've inherited a process that imports AS400 to SQL. The process pulls it in and dumps it to text files, then moves it to a database (I know...:crazy:). Anyway, when it dumps to text file it's failing due to AS400 converting a single quote to Hex. I've tried transformations like derived column and data conversion but nothing is working and I can't find anything on the internet. Surely someone else has had this issue. Any ideas will be welcomed.

    There is an exception to every rule, except this one...

  • Had to change up the process. Added a script task to select the data cleanly then insert.

    TRUNCATE TABLE TableA

    GO

    INSERT INTO TableA (Column)

    SELECT REPLACE(Column, '?', '') ColumnFixed, Column NotFixed

    FROM OPENROWSET('IBMDA400', 'xxx.xx.xxx.xxx'; 'username'; 'passsword', '

    SELECT

    FROM TESTDATA.Table M

    WHERE M.CRDT >= 0

    and m.POLN11 = ''1234567''

    ')

    There is an exception to every rule, except this one...

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply