none
SSIS: Count number of columns RRS feed

  • Question

  • Hi,

    I am processing flat files and I want to make sure that my files will contain the number of columns I expect, because a user might some day input a file with a new column format. If the file does not contain the correct number of columns, process must stop. I am planning to input the expected of columns in a User Variable.

    But how do I test for the number of columns in the file, and do I stop the execution?

    Be precise please (do I work in the control flow? in the Data flow?).

    Thanks in advance.

    Paul




    • Edited by pauldube Friday, September 2, 2011 3:17 PM
    Friday, September 2, 2011 2:51 PM

Answers

All replies

  • IMHO the best is to employ a Precedence Constraint.

    This is because the error handling becomes more or less standardized and the setup re-usable. E.g. you can report on the event of an error by the means of email.

    So you just branch out off the "normal" execution flow on an even of an error and deal with it. The whole thing takes 10 min of dev time.

    Your package though is going to fail if the input flat file metadata has changed, so to gracefully handle this nuance you need to disable the validation phase.

    The rest of the design you can find here: http://lanestechblog.blogspot.com/2010/11/using-error-and-other-multiple-paths-in.html


    Arthur My Blog
    By: TwitterButtons.com
    Friday, September 2, 2011 3:41 PM
    Moderator
  • It's nice, but that's not what I wanted to do in the first place. I have 4 sources and when I get an error because a file has an incorrect format it's not clear, from the error message, where the error is coming from (what source file).

    That would be very nice to be able to scan in advance each file to find out if they're OK.

    Paul


    • Edited by pauldube Friday, September 2, 2011 7:58 PM
    Friday, September 2, 2011 7:57 PM
  • You can add a pre-execute event handler in form of a Script Task in which you can try to parse the file structure using .net code.
    Arthur My Blog
    By: TwitterButtons.com
    Friday, September 2, 2011 8:06 PM
    Moderator
  • How do I do that while using the same Source Connection? I do not want to make the package clumsier by adding another Connection to the same file...

    Paul

    Friday, September 2, 2011 8:12 PM
  • The script task is not going to utilize it. It will skim through the top record, and report back to you in form of a true/false value that you can place into a Package variable and then consume at the beginning of the package.
    Arthur My Blog
    By: TwitterButtons.com
    Friday, September 2, 2011 8:15 PM
    Moderator
  • It does not help.

    From what I understand anyway it cannot work: I cannot use the defined connection trying to count the number of columns in the file, since the connection already defines the column mapping.

    Still can't see how to do that, if ever it's possible.

     

    Paul

    Tuesday, September 6, 2011 12:12 PM
  • Hi Paul,

    I agreed with you that we cannot use the defined connection to count the number of columns, here is simlar thread, please refer to SSISJoost's reply as below:
    http://social.msdn.microsoft.com/Forums/en/sqlintegrationservices/thread/afeac64e-115b-42c5-9860-ff4d4d70ceb1

    This is just a workaround for you, if you have concern about it, I suggest you can submit a feedback at https://connect.microsoft.com/sqlserver/feedback, and hope Microsoft improve this feature in the next service pack or product release.

    Thanks for your understanding.

    Thanks,
    Eileen
    • Marked as answer by Eileen Zhao Monday, September 26, 2011 6:39 AM
    Wednesday, September 14, 2011 11:49 AM
  • Dear Pauldube

    I think Arthurz is right

     (1) First  take  one package level variable for storing length of Header row of each and every file

    Eg.          Variable Name = FileRowLen

                    Variable Type = Integer

    (2) Take one script task and Add FileRowLen  variable to readwritevariables then edit script

    Add below code :

    Dim FileRowStack as string()

    FileRowStack = System.IO.File.ReadAllLines(FileSource)

    ‘–File source is string variable contain filepath that you want to import

    Dts.variables(“FileRowLen”).value = FileRowStack(0).length

     

    (3) Now open precedence constraint editor then

                    Eveluation operation :  Expression

                    Expression  :  @FileRowLen =  -- Header length of file which would you like to process

                    Multiple constraints : Logical or….

    Press OK

    Connector will looks  like ----------------


    Ramesh Rathod
    Wednesday, September 14, 2011 12:45 PM
  • I am using VS2010 with C# 2010, how can i write this to work for my environment?
    Monday, September 8, 2014 6:20 PM
  • I am using VS2010 with C# 2010, how can i write this to work for my environment?

    1) Read first line of file with C#: http://msdn.microsoft.com/en-us/library/system.io.streamreader.readline(v=vs.90).aspx

    2) String split: http://msdn.microsoft.com/en-us/library/b873y76a(v=vs.90).aspx

    3) Use length to count columns: http://msdn.microsoft.com/en-us/library/system.array.length(v=vs.90).aspx


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

    Monday, September 8, 2014 8:02 PM
    Moderator