locked
Reading data from fixed length file RRS feed

  • Question

  • Hi Experts, 

     I have a flat file with fixed length, and this file contain two types of data master records and and  details  records in same file. We have different length for master data and different for details records . I want to load master data in different table and details records in different table, we have a column rectype to identify the record type. I am looking for solution , I rely appreciate for help. 

      


    Regards Vikas Pathak

    Monday, July 25, 2016 4:24 AM

Answers

  •  I got answer, I have redefined the format file once gain, and its worked, line terminate should be on perfect place.

     My Solution : I have created two separate format separate file  one for master and another for detail, and call one by one in OPENROWSET function, finally I am successful to read all the data properly.


    Regards Vikas Pathak


    Thursday, July 28, 2016 2:52 AM

All replies

  • Hi Vikas you need to use Conditional Split task in your BI project.

    http://stackoverflow.com/questions/27649580/how-to-import-1-flat-file-to-multiple-tables-in-ssis


    Monday, July 25, 2016 9:33 AM
  • Thank you enric for response and your time. I have seen the Oracle developer did this in a quick, without using any tools as they have the External table. I tried to do same using OPENROWSET but no chance. We have 1200000 records and my query was just showing 600000 records. and query is executing successfully. I observe that my last column length of fixed length file is 3, but when I am specifying 3 its giving error as data truncation. when I am specifying more than 100 its working but returning only 600000 of records.

     I have tried so many ways "\n\r" , adding new column in format file for 0 etc but no luck. 


    Regards Vikas Pathak




    Monday, July 25, 2016 4:08 PM
  •  I got answer, I have redefined the format file once gain, and its worked, line terminate should be on perfect place.

     My Solution : I have created two separate format separate file  one for master and another for detail, and call one by one in OPENROWSET function, finally I am successful to read all the data properly.


    Regards Vikas Pathak


    Thursday, July 28, 2016 2:52 AM