none
Header in flat file destination (ssis)

    Question

  • Hi

    I am creating a header in the flat file destination which has some hard coded values and record count which will get its value from a variable assisgned to a row count transformation in the package.

    There is no problem in hard coded value but when i try to get the value of record count, i always get zero.

    This is how i am doing it.

    In the flat file destination, when we open flat file destination editor, i hard code the values which i want in the HEADER itself right there in the editor.

    For the record count, i go properties of the flat file and select expression. In the expression editor, in property, i am selecting 'HeaderRowDelimiter' and drag that variable in the expression box.

    Let me know if there is any suggestion.

    Thanks

    Friday, February 10, 2012 12:24 AM

All replies

  • Hi Harry,

    Instead of setting the Header from the Flat file Destination Editor, set the Header using expressions for the Data Flow Task from the Control flow. Come to the Control Flow page and select the properties for the Data Flow component. Go to expressions and you would be able to find the [Flat File Destination] . [Header]. Set the value for this property dynamically

    Murali

    Friday, February 10, 2012 6:15 AM
  • And the value from RowCount Transformation can be used only after the corresponding DFT. If you use the variable in the same DFT where it is calculated using the RowCount Transformation, it would be only 0. You can read about RowCount Transformation from the following URL.

    http://msdn.microsoft.com/en-us/library/ms141136.aspx

    So my suggestion would be to use 2 DFTs, one for calculating the count and the next DFT would create the flat file. And use the expressions in the second DFT to set the Header appened with the variable set by the RowCount.

    Murali

    Friday, February 10, 2012 6:37 AM
  • The value of the rowcount is only known AFTER the Data Flow Task is completely finished... and there header is writen before that.

    Here are some examples:
    http://agilebi.com/jwelch/2008/02/08/adding-headers-and-footers-to-flat-files/

    http://social.msdn.microsoft.com/Search/en-US/sqlserver?query=add%20header%20and%20footer%20ssis&rq=meta:Search.MSForums.ForumID(00e50af7-5f43-43ad-af05-d98b73c1f760)+site:microsoft.com&rn=SQL+Server+Integration+Services+Forum


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

    Friday, February 10, 2012 7:49 AM
    Moderator
  • Your suggestion helps a lot. But in the first dft, i am creating flat file and the value is assigned to the variable. Now in the second dft to create header, how can i append the header to the same file created in first dft.
    Friday, February 10, 2012 5:36 PM
  • You need not create the Flat file in the first DFT itself. Use the first DFT with only the Source & the RowCount Transformation. Create the flat file in the second DFT.

    Else you can also do the following.

    Have an Execute SQL Task before the DFT and find the count of the records using the Execute SQL Task and store it in a variable.  You can do that by setting the Result property in the Execute SQL Task to Single Row and assign this value to a variable. Then you can proceed with the idea that I mentioned in my first reply.

    Murali

    Monday, February 13, 2012 4:37 AM
  • what an interesting location for setting the flat file header.. saved me hours man - thanks!
    Wednesday, October 22, 2014 11:35 PM
  • You need not create the Flat file in the first DFT itself. Use the first DFT with only the Source & the RowCount Transformation. Create the flat file in the second DFT.

    Else you can also do the following.

    Have an Execute SQL Task before the DFT and find the count of the records using the Execute SQL Task and store it in a variable.  You can do that by setting the Result property in the Execute SQL Task to Single Row and assign this value to a variable. Then you can proceed with the idea that I mentioned in my first reply.

    Murali


    Great solution! And simple!
    Thursday, May 12, 2016 9:29 AM