Post reply

ssis flat file header issue

  • January 11, 2012 at 3:15 pm

    #266418

    I am using an expression to create a dynamic header row in the flat file. I am able to create dynamic date and time whenever the file is created and i am struggling to get the record count (total number of records loaded into flat file). I am getting the header as below: zero shows number of records which infact is not a right number. i am using the expression in the flat file connection manager and in the property i am using header row delimiters.

    ^ (Carrot) is the column delimiter

    20120111^140232^0

    My expression is :

    DT_STR,4,1252)DATEPART( "yyyy" , @[System::StartTime] ) +

    RIGHT("0" + (DT_STR,4,1252)DATEPART( "mm" , @[System::StartTime] ), 2) +

    RIGHT("0" + (DT_STR,4,1252)DATEPART( "dd" , @[System::StartTime] ), 2) + "^"+

    RIGHT("0" + (DT_STR,4,1252)DATEPART( "hh" , @[System::StartTime] ), 2) +

    RIGHT("0" + (DT_STR,4,1252)DATEPART( "mi" , @[System::StartTime] ), 2) +

    RIGHT("0" + (DT_STR,4,1252)DATEPART( "ss" , @[System::StartTime] ), 2)+"^"+(DT_WSTR, 1)@[User::Case_RecordCount]+"\r"

    Please let me know how to get a value of record count in header.

    Thanks

  • January 11, 2012 at 4:27 pm

    #1432203

    h.singh10 (1/11/2012)

    Please let me know how to get a value of record count in header.

    The header is what makes this more difficult because you can't pre-pend a text file target. You'll have to run the same query twice. Once to get a count for your rowcount and then again with the exact same parameters to feed the data into the file. If you do this, you can store the resulting count into your package variable.

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply