AS400 to SQL via SSIS

• SQLHeap

  Hall of Fame

  Points: 3572

  More actions

  April 6, 2016 at 9:33 am

  #399988

  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...

• SQLHeap

  Hall of Fame

  Points: 3572

  More actions

  April 6, 2016 at 11:21 am

  #1870269

  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...