January 14, 2013 at 7:18 am
anilchennoju 20102 (1/12/2013)
Thanks John, it worked perfectly for me 🙂
Your welcome.
January 28, 2013 at 12:49 am
Has anybody achieved the short cut to add 'HEADER' or 'FOOTER' to a flat file . Using rowcount i got the number of records written to file , and using 'HEADER' property of the file destination got to write 'Number of records in fiel are :' .
But couldn't get how to add the 'Rowcount' value to header
January 28, 2013 at 7:41 am
nishi.modgil (1/28/2013)
Has anybody achieved the short cut to add 'HEADER' or 'FOOTER' to a flat file . Using rowcount i got the number of records written to file , and using 'HEADER' property of the file destination got to write 'Number of records in fiel are :' .But couldn't get how to add the 'Rowcount' value to header
Refer to steps 3a - 3d on the first page of the forum under my post. This allows you to set an expression for the Header in you 1st Data Flow Task. Make sure you reference the RowCount variable you used. Though, I have never tried doing a row count in a header before. You may run into a problem that it writes the header first before you have the row count. If that is the case you may have to get a count of the rows going to be written to the file before you write them to the file.
January 28, 2013 at 9:00 pm
ROWCOUNT gets the values once the data flow task completes, so i added a sql execute task to get the number of records and then concatenated in expression of data flow task for file.header .
February 13, 2015 at 3:11 am
Hi John Dempsey,
I am trying to create an SSIS package to include Header & footer, following your steps:
1. Place 2 Data Flow Tasks on the Control Flow tab.
2. Use the first Data Flow Task to generate your Header and Detail records (rows)
a. Obtain the data from your data source
b. Transform your data as needed to satisfy the results for your Detail Rows
c. Set up your Flat File Destination in the layout for your Detail Rows. (Don't worry about Header yet)
d. Upon opening your Flat File Destination task you will notice a item for the "Header:". If you were to enter something in the box here it would be hard-coded as the header for you within your file. But, instead of it being hard-coded you can make it dynamic. You can create a SSIS variable to store your dynamically built header information, then assign it to the Header property of the Flat File Destination in the 1st Data Flow task.
3. Set dynamic Header SSIS variable to Header property of Flat File Destination in 1st Data Flow Task.
a. On the Control Flow Tab select the first Data Flow Task then go to the properties window for task
b. Expand [+] Expressions property and click the [...] button
c. In the Property Expressions Editor window click in the left column to bring up a list of properties available within the Data Flow Task for you Header and Detail Rows.
d. Choose the property [Flat File Destination].[Header] (if you haven't renamed it yet) setting the expression to the name of your SSIS variable name you created for your dynamic header.
4. Your dynamic header should now show up when you run the first data flow task with your detail rows.
5. Open up the second data flow and create a source for your footer, I used a script component as an input data source, mapping the source row to an SSIS variable that contained my Footer Row.
6. Create your Flat File Destination for you footer making sure that you set the Overwrite property = False. Your Flat File Destination will be the same file name as your header and detail file name from you first Data Flow Task, but the layout will be different in order to line up with the footer.
7. Upon running you SSIS, the package will run through executing your first Data Flow Task which writes the data from the Header property, then each detail row. Then, it will run the second Data Flow Task writing the footer row to the end of the same file used in your Header and Detail Data Flow Task.
I managed creating the package till step 5, got stuck at step 6. Can you please elaborate?
Thanks.
Yamini
February 13, 2015 at 3:13 am
Sorry John Dempsey, I got until step 4..NOT step 5.
I am unable to create the footer DFT.
Thanks.
February 16, 2015 at 1:25 pm
What do you mean you couldn't create the Data Flow Task? Did you not create 2 Data Flow Tasks as described in the earlier steps?
August 4, 2015 at 3:50 pm
I've followed the steps mentioned but I'm stuck on number 5. I can't find a straight forward article or how to on how to setup a Script Component as a source...that works for me. The part I get stuck on is when people get to writing out the C# or the VB. I can't get anyone's syntax to work.
I'm using SSIS 2012 and all I'm doing is trying to add this in the footer of my file...
EOF|1167
I setup a variable to get this information. Here's the code for that variable...
"EOF|" + TRIM((DT_WSTR, 10) @[User::row_count])
The name of the variable is footer. The name of the file connection I'm using to insert the header and detail is SLVEXT.txt
Is there a good how-to somewhere that explains how to insert one line into the footer of a file? I'd rather stay away from creating a footer file and then concatenating files because that would involve an extra step to remove a footer file after concatenation. I'll keep googling in the mean time.
TIA,
John
August 5, 2015 at 5:13 pm
After spending almost another entire day trying to force the idea to use a data flow task with a Script Component, I had to look for another method because I couldn't get anyone's C#/VB code to work. I'm not a complete code newbie. I played with and modified a lot of other people's code today. I worked out a lot of the errors but there was always something that I couldn't get past.
I don't know if this is the cleanest (best) way to do this but I ended up using a Script Task instead and inserted this code and now I have the footer I'm looking for in my flat file...
public void Main()
{
ConnectionManager cm = Dts.Connections["SLVEXT.txt"];
var path = cm.ConnectionString;
var textToWrite = (string)Dts.Variables["User::footer"].Value;
System.IO.File.AppendAllText(path, textToWrite);
Dts.TaskResult = (int)ScriptResults.Success;
}
March 1, 2016 at 10:41 am
I wish I could +1 you on this solution John. Just implemented it here - good stuff! Thanks!
March 22, 2016 at 7:07 am
John Dempsey (11/10/2009)
You can probably get away with just two Data Flow Tasks to generate the file. The first Data Flow Task would generate your Header and Detail records for the file. Then, the second Data Flow Task would be used to write your Footer record to the file. The following may be the steps you would take to develop this in your SSIS package:1. Place 2 Data Flow Tasks on the Control Flow tab.
2. Use the first Data Flow Task to generate your Header and Detail records (rows)
a. Obtain the data from your data source
b. Transform your data as needed to satisfy the results for your Detail Rows
c. Set up your Flat File Destination in the layout for your Detail Rows. (Don't worry about Header yet)
d. Upon opening your Flat File Destination task you will notice a item for the "Header:". If you were to enter something in the box here it would be hard-coded as the header for you within your file. But, instead of it being hard-coded you can make it dynamic. You can create a SSIS variable to store your dynamically built header information, then assign it to the Header property of the Flat File Destination in the 1st Data Flow task.
3. Set dynamic Header SSIS variable to Header property of Flat File Destination in 1st Data Flow Task.
a. On the Control Flow Tab select the first Data Flow Task then go to the properties window for task
b. Expand [+] Expressions property and click the [...] button
c. In the Property Expressions Editor window click in the left column to bring up a list of properties available within the Data Flow Task for you Header and Detail Rows.
d. Choose the property [Flat File Destination].[Header] (if you haven't renamed it yet) setting the expression to the name of your SSIS variable name you created for your dynamic header.
4. Your dynamic header should now show up when you run the first data flow task with your detail rows.
5. Open up the second data flow and create a source for your footer, I used a script component as an input data source, mapping the source row to an SSIS variable that contained my Footer Row.
6. Create your Flat File Destination for you footer making sure that you set the Overwrite property = False. Your Flat File Destination will be the same file name as your header and detail file name from you first Data Flow Task, but the layout will be different in order to line up with the footer.
7. Upon running you SSIS, the package will run through executing your first Data Flow Task which writes the data from the Header property, then each detail row. Then, it will run the second Data Flow Task writing the footer row to the end of the same file used in your Header and Detail Data Flow Task.
Hope they helps get you started.
Thanks,
John
thank you for this, you just saved me from two text files being merged. I knew there had to be a way inside SSIS! :w00t: I added this code below inside the expression to get the header and date in case anyone needs it.
(DT_WSTR,1)"H" + (DT_WSTR,4)DATEPART("yyyy",GetDate()) + RIGHT("0" + (DT_WSTR,2)DATEPART("mm",GetDate()) ,2) +
RIGHT("0" + (DT_WSTR,2)DATEPART("dd",GetDate()),2) + REPLICATE( " ", 508 )
the output is H20160322 the replicate adds the spaces up to 8000
MCSE SQL Server 2012\2014\2016
Viewing 11 posts - 46 through 55 (of 55 total)
You must be logged in to reply to this topic. Login to reply