none
Extraction from files

    Question

  • Hello All,

    I'm looking for the extraction of flat files ,error handling and archiving in ssis.  ,what are things do i need to consider for this extraction  .i have to consolidate several different files  .Is it possible for the extraction from these files if each day we get different columns .and different data types,what should we do with file names before extraction if they are date stamped , do we need to remove dates  for the flat file source,Also  the should be  the error handling procoess for the extraction and  validations .

    Many Thanks

    Kumar


    • Edited by Vision2040 Sunday, February 05, 2012 10:53 AM typo error
    Saturday, February 04, 2012 11:41 PM

Answers

All replies

  • Integration Service's work mainly bases on metadata. If your files have different columns and data types, you likely have to create different connection managers. That is annoying. I think you should organize your files first, before doing IS. Error handing, extraction, validation .... are not a big deal, IS is designed to deal with those things.
    Sunday, February 05, 2012 1:09 AM
  • Vision,

    Loading data from flat files will be challenging if their structure and data types are unpredictable, specially if you need to load them via data flow task. So here are few things you can evaluate:

    regarding logging, I would recommend you start by reading about that topic in the help file: Implementing Logging in Packages:  http://msdn.microsoft.com/en-us/library/ms138020.aspx

    For the file names part you can use an expression in the connection manager to pass correct file name. http://consultingblogs.emc.com/jamiethomson/archive/2005/05/30/SSIS_3A00_-Enumerating-files-in-a-Foreach-loop.aspx

     

    I would also recommend you to start simple and to ask more specific questions as you progress if you want to get ore out of this forum.

     


    Help Others! Don't forget to mark your thread as answered

    Rafael Salas - SSIS and more
    • Marked as answer by Vision2040 Wednesday, February 08, 2012 7:02 PM
    Sunday, February 05, 2012 4:34 AM
    Moderator
  • Thank you Rafael,

    I will take your sujjestion into consideration for future posts.

    I  Was looking specifically the extraction of files ,and the things to consider if some thing goes wrong ,may be the missing columns  which can be handled  as you pointed to third party component.

     But for others If the   data types  changes  or contains some errors  in column names Is it still Possible to handle  these anomalies  .

     

    Many Thanks

    Kumar

    Sunday, February 05, 2012 11:40 AM
  • Hello Kumar,

    The columns will definately miss, and the package will fail with error, since the meta data(different columns and differnt data types) is not what the downstream tasks expected.

    What is that you intend to design?


    Please vote as helpful or mark as answer, if it helps
    Cheers, Raunak | t: @raunakjhawar | My Blog
    • Marked as answer by Vision2040 Wednesday, February 08, 2012 7:02 PM
    Monday, February 06, 2012 6:24 AM
  • Rauna,

    What can we do if meta data changes on each file i.e for missing columns,is there any way to make the all the column headers  after a file has been imported.

     

    Many Thanks

    Kumar

    Monday, February 06, 2012 9:21 AM
  • Hi Kumar,

    If the file structure  changes(meta data - columns), design a new connection manager


    Please vote as helpful or mark as answer, if it helps
    Cheers, Raunak | t: @raunakjhawar | My Blog
    Monday, February 06, 2012 10:10 AM
  • Rauna,

    I have defined a separate connection managers  for each file,But   the importrd file some times contains missing columns than the expected columns.

    Many Thanks

    Kumar

     

    Monday, February 06, 2012 10:13 AM
  • Hi Kumar,

    Missing columns or missing data?


    Please vote as helpful or mark as answer, if it helps
    Cheers, Raunak | t: @raunakjhawar | My Blog
    Monday, February 06, 2012 10:15 AM
  • missing columns,Suppose i need 8 columns to map to my target table and i expect only 6 on one day what can we do as flat file source will fail .

     

    Many Thanks

    Kumar

    Monday, February 06, 2012 10:22 AM
  • missing columns,Suppose i need 8 columns to map to my target table and i expect only 6 on one day what can we do as flat file source will fail .
    The flat file will fail itself. No extra efforts needed. But as a designer, you must seek that the dtsx does not fails.
    Please vote as helpful or mark as answer, if it helps
    Cheers, Raunak | t: @raunakjhawar | My Blog
    Monday, February 06, 2012 10:26 AM
  • I dont want it to be failed ,still want to load  with missing columns as null.

     

    Many Thanks

    Kumar

    Monday, February 06, 2012 10:41 AM
  • As long as meta data remains the same, this is the case of missing data.
    Please vote as helpful or mark as answer, if it helps
    Cheers, Raunak | t: @raunakjhawar | My Blog
    Monday, February 06, 2012 11:25 AM
  • Rauna,

    The Meta Data Doesnt remain same,some files will not contain column names  and there data completly

     


    • Edited by Vision2040 Monday, February 06, 2012 11:53 AM
    Monday, February 06, 2012 11:53 AM
  • Hi Kumar,

    Before you plan to implement any transformation to data, you need to know the data.

    SSIS, will not auto determine the meta data and auto adjust itself.


    Please vote as helpful or mark as answer, if it helps
    Cheers, Raunak | t: @raunakjhawar | My Blog
    • Proposed as answer by Eileen Zhao Tuesday, February 07, 2012 8:51 AM
    Monday, February 06, 2012 11:56 AM