Putting the row count in the header of a text file.
Hello, I'm pretty new to SSIS but so far what I have is a package that exports a SQL Server table to a text file. I needed to add a dynamic header that had the date and time of creation. Now I need to know how many records are being exported and put that number into the header.For the header I am using a script task in the control flow which works well to put the creation date in the header. The script runs and writes the header and then the data flow exports and appends the records to the same text file. It seems to me since the script runs before the data flow I won't know the amount of records until after the data flow is done.Maybe I could write the header after the data is gathered but before it is exported. Can anyone make some suggestions?Basically the text file would be:2/6/2008154DataDataData...the 154 would be the total number of records to follow.While I'm at it can someone tell me how to access the destination file path in the flat file connection? Right now I'm just hardcoding the path into my script.Thanks,Gunner
February 7th, 2008 5:39am
Why not determine the row count in advance? You could use an Execute SQL Task and run SELECT COUNT(*) FROM style query, and store the count in a variable. This could run immediately prior to your Data Flow task. Another method is to export as normal, grabbing the row count, but consider that a staging file. The write the header information to a file. Now append the files together. I have yet to find a better method than the plain old DOS COPY command, e.g. copy file1.txt+file2.txt file3.txt To get the file path from a flat file or file connection manager - Code Snippet Public Sub Main() Dim filePath As String = CType(Dts.Connections("Flat File").AcquireConnection(Nothing), String) System.Windows.Forms.MessageBox.Show(filePath) Dts.TaskResult = Dts.Results.Success End Sub
February 7th, 2008 11:54am
A full discussion of using connections in script tasks is provided in Books Online - Connecting to Data Sources in the Script Task http://msdn2.microsoft.com/en-us/library/ms136018.aspx
February 7th, 2008 12:24pm
Thanks for the help. It looks pretty straightforward. I'll give it a shot on Monday.
February 10th, 2008 5:22am
Gunner, I too am pretty new to SSIS. Your scenario matches exactly the one I am facing, adding a dynamic header to a flat file. Were you able to get this operational? If so, would you be willing to share the code used to accomplish this?
February 16th, 2011 1:54pm