none
how to skip dynamic header rows in ssis RRS feed

  • Question

  • Hi I have one doubt in ssis ,
    I want load multiple csv files into  sql server table using ssis package.
    while loading time we need consider data from headers on wards.

    Source path have 3 csv files with fixed header columns with data
    but each file have description and dates creation information before headers and
    one file description comes 2row and headers row start from 4th row with data.
    another file descriptions comes from 1 row and 9 row on wards have headers with data
    and another file will come file description from 5 row and  headers row start from 7throw
    columns headers are fixed in the all  csv files



    Files location : 1)C:\test\a.csv 2)C:\test\b.csv 3)C:\test\c.csv
    a.csv file data like below :here description and dates comes 2and 3 row.actual data start from 4th row onwards
    descritiion:empinfromationforhydlocation
    creadeddate:2018-04-20

    id |name|loc
    1  |a   |hyd

    b.csv file data like below :here description and dates comes 1and 2 row.actual data start from 9th row onwards
    description:empinfromation for hyd location
    createddate:2018-04-21

    id |name|loc
    10  |b   |chen

    c.csv file data like below :here descritpion and comes 5 and 6 row.actual data start from 9th row onwards
    descritiion:empinfromationforhydlocation
    creadeddate:2018-04-21

    id |name|loc
    20  |c   |bang

    Based on above 3 file I want load data into target sql server table : 
    emp :
    id  | Name |Sal
    1   |a     |hyd
    2   |b     |chen
    3   |c     |bang

    here I tried like below in the package side: 
    create variable : 1)filelocationpath: C:\test\  2)filename : C:\test\a.csv
    drag and drop the foreachloop contaier :choose the type of enumerator foreachfile enumeratior
    directory: c:test\
    variable mappting :filename configue it.
    type of file: *.csv 
    reterivefilename: filename and extension
    inside foreachloop container I drag and drop the dataflow task
    and create flat file connection here used one of file is configure and header row skiped is 1
    and used data converion required column and configure to oledb destionation  table
    and create dynamic connection expression for flatfileconnection to pass filename dynamically.

    after execute the package 2nd file is failed due to descrition and dates inforamtion
    description and dates is not constantly comes fixed rows next day files description and dates will comes  with different rows

    is there possible to find dynamicaly how many row will skip and that count will pass in header row  skip.is it possible in ssis.
    please tell me how to achieve this task in ssis 
    Saturday, September 21, 2019 5:53 AM

All replies

  • Hi

    For the above requirement In a high level You can create 3 different Flat File Data sources and union them all before inserting into the destination table.

    And specifically for the issue you are facing the below property setting in the Flat File Connection Manager would hopefully sought out your issue.

    Hope this is helpful !!

    Thank you


    If this post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.

    Saturday, September 21, 2019 2:33 PM

  • is there possible to find dynamicaly how many row will skip and that count will pass in header row  skip.is it possible in ssis.
    please tell me how to achieve this task in ssis 

    You might need to write a .Net code in a script task which deletes the rows (description, date details etc.) before Columns header and save CSV. 

    Then, iterate and load CSVs. 

    Or better would be to check with the team who provides CSV files if they can just eliminate adding descriptions and simply provide columns header in first row itself.


    Cheers
    Vaibhav
    MCSA (SQL Server 2014)

    Saturday, September 21, 2019 2:57 PM
  • Hi I have one doubt in ssis ,
    I want load multiple csv files into  sql server table using ssis package.
    while loading time we need consider data from headers on wards.

    Source path have 3 csv files with fixed header columns with data
    but each file have description and dates creation information before headers and
    one file description comes 2row and headers row start from 4th row with data.
    another file descriptions comes from 1 row and 9 row on wards have headers with data
    and another file will come file description from 5 row and  headers row start from 7throw
    columns headers are fixed in the all  csv files



    Files location : 1)C:\test\a.csv 2)C:\test\b.csv 3)C:\test\c.csv
    a.csv file data like below :here description and dates comes 2and 3 row.actual data start from 4th row onwards
    descritiion:empinfromationforhydlocation
    creadeddate:2018-04-20

    id |name|loc
    1  |a   |hyd

    b.csv file data like below :here description and dates comes 1and 2 row.actual data start from 9th row onwards
    description:empinfromation for hyd location
    createddate:2018-04-21

    id |name|loc
    10  |b   |chen

    c.csv file data like below :here descritpion and comes 5 and 6 row.actual data start from 9th row onwards
    descritiion:empinfromationforhydlocation
    creadeddate:2018-04-21

    id |name|loc
    20  |c   |bang

    Based on above 3 file I want load data into target sql server table : 
    emp :
    id  | Name |Sal
    1   |a     |hyd
    2   |b     |chen
    3   |c     |bang

    here I tried like below in the package side: 
    create variable : 1)filelocationpath: C:\test\  2)filename : C:\test\a.csv
    drag and drop the foreachloop contaier :choose the type of enumerator foreachfile enumeratior
    directory: c:test\
    variable mappting :filename configue it.
    type of file: *.csv 
    reterivefilename: filename and extension
    inside foreachloop container I drag and drop the dataflow task
    and create flat file connection here used one of file is configure and header row skiped is 1
    and used data converion required column and configure to oledb destionation  table
    and create dynamic connection expression for flatfileconnection to pass filename dynamically.

    after execute the package 2nd file is failed due to descrition and dates inforamtion
    description and dates is not constantly comes fixed rows next day files description and dates will comes  with different rows

    is there possible to find dynamicaly how many row will skip and that count will pass in header row  skip.is it possible in ssis.
    please tell me how to achieve this task in ssis 

    I would suggest an approach like below for this scenario

    1. dump the entire file content into single row table with an additional Identity (staging table you create)

    2. write  a sql procedure to parse the rows of the table and get the first row with delimiter character (| in your case) and store its id in a variable

    3. get all rows which are greater than the identity value stored in the variable and use string parsing logic (using LEFT,RIGHT etc functions or PARSENAME if number of columns is maximum 4)

    4. Transfer the parsed out values to your actual table

    5. Truncate the staging once you've done transferring the data to keep it ready for processing the next file data


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Sunday, September 22, 2019 5:06 AM
  • Hi jairama,

    We could use Union All, Derived Column, Sort in Data Flow Task and Execute SQL Task to achieve  the result.

    Please follow the steps as the pictures shown below. 

    Source:

    Transformations:

    Output in Data Flow Task:

    (We can just create new destination table with columns : name and id. Column0 is just for showing.)

    CREATE TABLE [table923] (
        [name] nvarchar(50),
        [loc] nvarchar(50)
    )

    Execute SQL Task:

      delete from table923 where [name]=''or [name]='name'; --Delete unuseful data.
      go
      
      ALTER TABLE table923 ADD id int identity; --Add new column id (with identity).
      go

    Result:

    Best Regards,

    Mona


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com


    Monday, September 23, 2019 5:28 AM
  • Hi jairama,

    Please remember to click "Mark as Answer" the responses that resolved your issue.

    This can be beneficial to other community members reading this thread. 

    Thank you.

    Regards,

    Mona


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, September 26, 2019 9:44 AM