1

I want to create an SSIS package with source as azure sql and destination as DB2 table. I have to check if Id (int) in sql matches with Id (X12 basically varchar) in DB2, if it matches, I have to update name in db2 with the name in SQL else insert new record in DB2.

Since, datatype of id does not match, I have to do a data type conversion- that is if id in sql is 1 .. I have to convert it to varchar with leading zeros - 00000000001 Can some one please provide a step by step approach? and where exactly can I make this conversion.. in which task?

Hadi
  • 36,233
  • 13
  • 65
  • 124
Ritu
  • 31
  • 1
  • Hi, did you try using derived column transformation to add leading zeros (**ex: RIGHT("0000000000"+[Id],10)**)? please check this [link](https://stackoverflow.com/questions/22303528/ssis-derived-column-padding-of-leading-zeroes) if this is of any help. – NiharikaMoola-MT Jan 25 '22 at 11:31
  • `SELECT FORMAT (1, REPLICATE ('0', 11))`? – Mark Barinstein Jan 25 '22 at 13:10
  • If DB2 is on Azure you can use Azure Data Factory. It's quite straight forward: https://learn.microsoft.com/en-us/azure/data-factory/connector-sql-server?tabs=data-factory – Francesco Mantovani Jan 26 '22 at 09:53

0 Answers0