sql - how to check column structure in ssis?

Question

I have a table customer in my sql server.

Columns

1. Distributer_Code
2. Cust_code
3. cust_name
4. cust_add
5. zip
6. tel
7. dl_number
8. gstin

we receive customer files from the distributor on a monthly basis. so sometimes they send files with the wrong structuer.. like maybe gstin is missing or dl_number is missing or gstin is in place of dl_number and dl_number is in place of tel...basically, columns could be split..

when we upload those flat files with SSIS it gives error..and data doesn't get uploaded on the server if the structure is wrong.

I want to upload those data with null data if columns are missing or columns are misplaced.

Answer 1

Based on your comment, you are handling with flat files. To solve this problem, you have to read all columns as one column and retrieve the structure on the go.

Details

1. First add a Flat file connection manager.
2. In the flat file connection manager, go to the Advanced Tab, remove all columns and keep only one column (Column0).
3. Change the column type to DT_WSTR and the length to 4000.
4. Add a Dataflow task
5. Inside the Dataflow task add a Flat File source, a script component and an OLEDB destination.
6. Open the script component, go to Input/Output Tab and and add 8 output columns (Distributer_Code,Cust_code,cust_name,cust_add,zip,tel,dl_number,gstin)
7. Change the script language to Visual Basic.

8. Inside the script write the following code.

   Dim Distributer_Code as integer = -1
   Dim Cust_code as integer = -1
   Dim cust_name as integer = -1
   Dim cust_add as integer = -1
   Dim zip as integer = -1
   Dim tel as integer = -1
   Dim dl_number as integer = -1
   Dim gstin as integer = -1
   Dim intRowIndex as integer = 0
   
   Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
   
       If intRowIndex = 0 then
   
           Dim strfields() as string = Row.Column0.split(CChar("|"))
           Dim idx as integer = 0
   
           For idx = 0 To strFields.length - 1
   
               Select case str
   
               Case "Distributer_Code"
                   Distributer_Code = idx  
               Case "Cust_code"
                   Cust_code = idx 
               Case "cust_name"
                   cust_name = idx 
               Case "cust_add"
                   cust_add = idx  
               Case "zip"
                   zip = idx   
               Case "tel"
                   tel = idx   
               Case "dl_number"
                   dl_number = idx 
               Case "gstin"
                   gstin = idx 
   
               End Select
   
           Next
   
       Else
   
           Dim strfields() as string = Row.Column0.split(CChar("|"))
   
           If Distributer_Code > -1 Then Row.DistributerCode = strfields(Distributer_Code)
           If Cust_code > -1 Then Row.Custcode = strfields(Cust_code)
           If cust_name > -1 Then Row.custname = strfields(cust_name)
           If cust_add > -1 Then Row.custadd = strfields(cust_add)
           If zip > -1 Then Row.zip = strfields(zip)
           If tel > -1 Then Row.tel = strfields(tel)
           If dl_number > -1 Then Row.dlnumber = strfields(dl_number)
           If gstin > -1 Then Row.gstin = strfields(gstin)
   
       End If
   
   
       intRowIndex += 1
   
   End Sub
   

9. Map the output columns to the OLEDB Destination