locked
Mappingfiles for Flat file RRS feed

  • Question

  • Hi Experts ,

    We have a task for migrating DB2 on Mainframe to SQL Server 2008R2. I was testing with a comma separated flat file having a table with

    Varchar,Date,integer,timestamp and date as datatypes ...somehow i managed to get it imported to SQL Server ...However, all the columns are Varchar now ...So looks like the SSIS import by default converts everything to VARCHAR .....This is something we do not want ...

    Then I found that there are mapping files .On my SQL Server setup it located at C:\Program Files\Microsoft SQL Server\100\DTS\MappingFiles .I found DB2ToMSSql10.XML ,DB2ToMSSql.XML,DB2ToSSIS10.XML,IBMDB2ToMSSql10.XML,IBMDB2ToMSSql.XML and IBMDB2ToSSIS10.XML .There is no maping file for Flatfile source.

    Does that mean that in order to do correct mapping we should directly connect to DB2 instance using Either DB2Connect or OLEDB provider for DB2 and extract the data (assuming that these mapping files will be used then) ?? I did not see the source server and target server options in BCP command ....So BCP (and bulk insert ) will also use the Flatfile ...am I right ?

    Secondly , Is it possible to have a DAT file for every table in DB2 and also a format file associated with it .....

    OR

    How will BCP/Bulk Insert work in case we first get the table structure created and then do a BCP / Bulk Insert ...

    I am totally stuck ....we cant do a connect to DB2 and import the rows for huge tables having 75 million rows in each of them ..

    Any help is greatly apreciated ...

    Regards


    Abhay Chaudhary OCP 9i, MCTS/MCITP (SQL Server 2005, 2008, 2005 BI) ms-abhay.blogspot.com/
    Monday, November 15, 2010 12:20 PM

Answers

  • Hi,

    As far as I know, there is no mapping file for flat file source which sounds reasonable since the data of each column is character in a flat file and it is hard to detect what its original data type is.

    However, you can manually map each column in the flat file to a SQL Server data type. To do this, please follow the steps:

    1. Right-click Flat File Source control in the Data Flow page of SSIS project, and select Show Advanced Editor.
    2. In the Advanced Editor for Flat File Source dialog, switch to Input and Output Properties tab and you can change each column mapped database in its right  Common Properties page.

    Hope this helps. Please let me know if you need more help.

    Thanks,
    Chunsong


    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    • Marked as answer by Abhay_78 Wednesday, November 17, 2010 9:45 AM
    Wednesday, November 17, 2010 8:19 AM

All replies

  • You can specify in SSIS how you want attributes to be imported, it might be better to stick with SSIS as you can clean data and do all sorts of wonderful things with it above and beyond the capabilities of BCP.
    Richard Douglas
    Monday, November 15, 2010 4:54 PM
  • Hi,

    As far as I know, there is no mapping file for flat file source which sounds reasonable since the data of each column is character in a flat file and it is hard to detect what its original data type is.

    However, you can manually map each column in the flat file to a SQL Server data type. To do this, please follow the steps:

    1. Right-click Flat File Source control in the Data Flow page of SSIS project, and select Show Advanced Editor.
    2. In the Advanced Editor for Flat File Source dialog, switch to Input and Output Properties tab and you can change each column mapped database in its right  Common Properties page.

    Hope this helps. Please let me know if you need more help.

    Thanks,
    Chunsong


    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    • Marked as answer by Abhay_78 Wednesday, November 17, 2010 9:45 AM
    Wednesday, November 17, 2010 8:19 AM
  • thanks guys ...I was aware of manual mapping but wanted to avoid it for hundereds of tables .....

    Regards


    Abhay Chaudhary OCP 9i, MCTS/MCITP (SQL Server 2005, 2008, 2005 BI) ms-abhay.blogspot.com/
    Wednesday, November 17, 2010 9:45 AM