none
Problem importing data from files with differing number of columns

Answers

  • What I would do is import everything as one column. In the script component I would split the column using the Split fuction. The results go into a string array. You can simply count the number of items in the array. If there are 37, assign them to the corresponding output columns and assign null to the last two.

    If there are 39 items, you don't need to do anything except assigning the correct string to the corresponding output.


    MCSE SQL Server 2012 - Please mark posts as answered where appropriate.

    • Marked as answer by ghw123 Thursday, July 04, 2013 8:45 PM
    Thursday, July 04, 2013 8:22 PM

All replies

  • What you could do is import it all as one column and parse it in a script component.
    You could use the .NET function Split to achieve this.

    MCSA SQL Server 2012 - Please mark posts as answered where appropriate.

    Thursday, July 04, 2013 7:08 AM
  • An alternative is to use a Script Component as a source.

    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

    Thursday, July 04, 2013 11:43 AM
    Moderator
  • Thanks for the replies, SSISJoost is it possible to add 2 columns and assign null value to them using the script component before passing to import.


    Thursday, July 04, 2013 8:04 PM
  • Sure. You need to add all possible output columns in the configuration of your script component.
    In the script itself, you just need to assign null values to these two columns and add a row to the output.


    MCSE SQL Server 2012 - Please mark posts as answered where appropriate.

    Thursday, July 04, 2013 8:08 PM
  • The standard Data Flow Task supports only static metadata defined at design time. If you can use third-party solutions, check the commercial COZYROC Data Flow Task Plus. It supports dynamic metadata at runtime, which allow you to process files with varying number of columns. No programming skills are required.

    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

    Thursday, July 04, 2013 8:08 PM
  • Thanks Koen, so I would select all input columns from the input columns window and then on the inputs and outputs window add 2 extra columns and assign null value. Then how would I deal with files that have 39 columns?
    Thursday, July 04, 2013 8:19 PM
  • What I would do is import everything as one column. In the script component I would split the column using the Split fuction. The results go into a string array. You can simply count the number of items in the array. If there are 37, assign them to the corresponding output columns and assign null to the last two.

    If there are 39 items, you don't need to do anything except assigning the correct string to the corresponding output.


    MCSE SQL Server 2012 - Please mark posts as answered where appropriate.

    • Marked as answer by ghw123 Thursday, July 04, 2013 8:45 PM
    Thursday, July 04, 2013 8:22 PM
  • Thanks again Koen, I am very new to SSIS so the learning curve is steep, how would I import everything as 1 column?
    Thursday, July 04, 2013 8:32 PM
  • Use another delimiter in the flat file connection manager.

    MCSE SQL Server 2012 - Please mark posts as answered where appropriate.

    Thursday, July 04, 2013 8:40 PM
  • Thanks Koen.
    Thursday, July 04, 2013 8:45 PM