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