handle dynamicaly missing source columns in ssis

• asrinu13

  Say Hey Kid

  Points: 709

  More actions

  September 16, 2014 at 2:38 am

  #310943

  Hi i have small doubt in ssis.while extraction data from mysql table to sql server table that time mysql server source side table columns missing how to handled

  dynamical to loading data into sql server table

  source table: Test(mysql server)

  id | name | sal | deptno | loc | referby

  1 | abc | 100 |10 | hyd | xyz

  2 | mnc | 200 |20 |chen | pqr

  in ssis package first i done mysql server table conifuration then i drap and drop oledbdestination for mysql server table configuration. i congigurae the target table.

  after that i ran package its working fine and data look like in the below.

  Target table : Test (sql server )

  id | name | sal |deptno | loc |referby

  1 | abc | 100 |10 | hyd | xyz

  2 | mnc | 200 |20 |chen | pqr

  when i ran second time same package that time mysql server side test table have missed name columns and package fail.that time i open mysql server testsource congiguration

  source then i edit query like

  select id,'null' as name ,sal,deptno,loc,referby from test .after that i ran package.now data look like below

  Target table : Test (sql server )

  id | name | sal |deptno | loc |referby

  1 | null | 100 |10 | hyd | xyz

  2 | null | 200 |20 |chen | pqr

  every time i truncate the target table and load data. target test table(sql server) this columns has fixed its never changed

  after few days i ran same package again package is failed reason is mysql source side again deptno,loc columns deleted.

  i donot want edit again mysql souce query and wirte like 'null' as deptno,'null' as loc.reason is we donot know which column is deleted or which columns have

  we know maximum target table columns onlye have in source table.some time deleted columns or some time added column .

  in this sistuvation how to handled this time type of issue in ssis package level.

  we need to check id column is exist or not if exist in mysql source side then return same value other wise create 'null' as id same way need to

  check upto remain columns .please tell me how to approach to solve this issue in ssis package level