locked
Importing Flat files using SSIS package RRS feed

  • Question

  • Hi Experts ,

    We have a requirement where we need to migrate from DB2 on mainframe to SQL Server 2008 R2 .One of the ways is to move the data to Flat files in ASCII format or ANSI format and then importing it in to SQL Server using SSIS (Bulk insert) .But we have 400+ tables to import ....

    I was looking for any assistance where we can read the flat files and move the data in to appropriate tables ...

    Any help is greately appreciated ..

    Regards


    Abhay Chaudhary OCP 9i, MCTS/MCITP (SQL Server 2005, 2008, 2005 BI) ms-abhay.blogspot.com/
    Sunday, January 16, 2011 5:03 PM

Answers

  • Use the import/export wizard in SSMS.
    Todd McDermid's Blog Talk to me now on
    • Proposed as answer by Reza RaadMVP Sunday, January 16, 2011 6:01 PM
    • Marked as answer by Jerry Nee Wednesday, February 9, 2011 4:51 AM
    Sunday, January 16, 2011 5:54 PM

All replies

  •  are  those flat files have same structure?

    If yes, you can use simple data flow in a foreach loop and loop through all files and import them.

    If no, you can not use data flow task for dynamic metadata. but you can use Execute sql task within a foreach loop and make BCP command dynamically for each file and import them. a sample of bcp command to import flat file to sql server is here:

    http://www.cryer.co.uk/brian/sqlserver/howtoimportcsv.htm


    http://www.rad.pasfu.com
    Sunday, January 16, 2011 5:28 PM
  • Hi Abhay,

    I'm assuming you're trying to avoid declaring the metadata for 400+ tables in SSIS; have you considered using SSIS to call BCP?  Here is a link on the BCP utility, perhaps it'd be an acceptable alternative for you.

    http://msdn.microsoft.com/en-us/library/ms162802.aspx

    Jon

    • Proposed as answer by Jon Kleinhans Sunday, January 16, 2011 7:15 PM
    Sunday, January 16, 2011 5:32 PM
  • Thanks Reza ...I could not understand your question regarding "SAME STRUCTURE"....There are 300+ tables having allkind of datatypes .The destination table structure will be created in advance ..The flat files will be either ASCII or ANSI format with columns Pipe or tilda separated ...Going through your link now....


    Abhay Chaudhary OCP 9i, MCTS/MCITP (SQL Server 2005, 2008, 2005 BI) ms-abhay.blogspot.com/
    Sunday, January 16, 2011 5:35 PM
  • I have used BCP and BulkInsert already ...I am looking for some example of using it in SSIS and that too for 300+ tables ...So basically I am looking for some automation here.
    Abhay Chaudhary OCP 9i, MCTS/MCITP (SQL Server 2005, 2008, 2005 BI) ms-abhay.blogspot.com/
    Sunday, January 16, 2011 5:36 PM
  • by "same structure" I mean same number of columns , same columns names, and same data types in all tables.

    But I think you haven't same structure on your 300+ tables.

    Note that you can not use dynamic metadata in ssis data flow task, this means that you should have 300+ source/destination for all your tables. and this is awful job.

    but you can use BCP command, all you should to do is to create bcp command dynamically based on flat file names and destination table, then run the BCP command inside an execute sql task.

    then you can put this execute sql task in a foreach loop structure which loops through all 300+ files and import them one by one.

     

    Does it make sense to you?


    http://www.rad.pasfu.com
    Sunday, January 16, 2011 5:44 PM
  • Here's an example video of BCP usage.

    http://www.youtube.com/watch?v=dyXfgPcjupQ

    As Reza indicated, you'd have to go through the files and dynamically assemble the BCP commands and execute them (via Execute SQL Task) to import the data without declaring the metadata in SSIS.

    Jon

    Sunday, January 16, 2011 5:49 PM
  • Use the import/export wizard in SSMS.
    Todd McDermid's Blog Talk to me now on
    • Proposed as answer by Reza RaadMVP Sunday, January 16, 2011 6:01 PM
    • Marked as answer by Jerry Nee Wednesday, February 9, 2011 4:51 AM
    Sunday, January 16, 2011 5:54 PM