none
Flat Files Column formatting RRS feed

  • Question

  • I am trying to load data from flat files into SQL Server I have around 150 columns and when I import flat files in my SSIS package all the columns are by default string with length 50 and I have to manually change all the 150 columns into proper data type so that I can transfer my data in SQL Server else it throws data mismatch error and process stops.

    So is there any way where I don't have to manually change column types and it is done automatically by SSIS?



    • Edited by Chirag27 Wednesday, September 11, 2019 8:43 PM
    Wednesday, September 11, 2019 8:42 PM

All replies

  • Hi Chirag27,

    Instead of using a SSIS package, you could use Import Flat File Wizard

    You could edit the fields if they are incorrect during the process.

    Please refer to Import Flat File to SQL Wizard.

    Hope it helps.

    Regards,

    Zoe


    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 12, 2019 6:11 AM
  • So is there any way where I don't have to manually change column types and it is done automatically by SSIS

    Sure, in flat file Connection Manager tab "Advanced pages" there is a button "Suggest column types", but that's really only a Suggestion.

    See Flat File Connection Manager Editor (Advanced Page)


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Thursday, September 12, 2019 6:14 AM
  • Hi Chirag27,

    The answer is NO on your question. There is no such functionality in SSIS out-of-the-box.

    It is  better to switch to data feeds in XML format. You will be able to use XML Schemas where all data elements are specified with their data types, lengths, precisions, optional vs. mandatory, unique identifiers, etc.

    Thursday, September 12, 2019 3:42 PM
  • Hello Olaf,

    I tried suggest column types but it does not correctly change all the types and hence I need to again manually check all the columns and change accordingly. Thanks a lot for your suggestion 

    Thursday, September 12, 2019 6:52 PM
  • Hello Zoe,

    This import wizard take more time to ingest data(I have more than 50 gb of data).

    Thanks a lot for your suggestion

    Thursday, September 12, 2019 6:53 PM
  • Hi Chirag27,

    As Yitzhak said before, using a XML file is better.

    Regards,

    Zoe


    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

    Friday, September 13, 2019 3:14 AM