InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 18 Jun 2013 18:01:46 GMT20http://www.sqlservercentral.com/Forums/Topic815666-148-1.aspxROWCOUNT 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 .Mon, 28 Jan 2013 21:00:13 GMTnishi.modgilhttp://www.sqlservercentral.com/Forums/Topic815666-148-1.aspx[quote][b]nishi.modgil (1/28/2013)[/b][hr]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[/quote]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.Mon, 28 Jan 2013 07:41:36 GMTJohn Dempseyhttp://www.sqlservercentral.com/Forums/Topic815666-148-1.aspxHas 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 headerMon, 28 Jan 2013 00:49:33 GMTnishi.modgilhttp://www.sqlservercentral.com/Forums/Topic815666-148-1.aspx[quote][b]anilchennoju 20102 (1/12/2013)[/b][hr]Thanks John, it worked perfectly for me :-)[/quote]Your welcome.Mon, 14 Jan 2013 07:18:46 GMTJohn Dempseyhttp://www.sqlservercentral.com/Forums/Topic815666-148-1.aspxThanks John, it worked perfectly for me :-)Sat, 12 Jan 2013 22:35:02 GMTanilchennoju 20102http://www.sqlservercentral.com/Forums/Topic815666-148-1.aspx[quote][b]rs80 (1/10/2013)[/b][hr]Thanks John. In the process of trying it out. I haven't done step #5 before. Can I follow the steps from this article: http://www.ssistalk.com/2007/04/04/ssis-using-a-script-component-as-a-source/?[/quote]I didn't thoroughly read the article. But, a brief perusal looks like that should help you get started. Remember that if you create the footer using a variable that your footer variable needs to call the RowCount variable for the value.Thu, 10 Jan 2013 13:25:38 GMTJohn Dempseyhttp://www.sqlservercentral.com/Forums/Topic815666-148-1.aspxThanks John. In the process of trying it out. I haven't done step #5 before. Can I follow the steps from this article: http://www.ssistalk.com/2007/04/04/ssis-using-a-script-component-as-a-source/?Thu, 10 Jan 2013 12:56:25 GMTrs80http://www.sqlservercentral.com/Forums/Topic815666-148-1.aspx[quote][b]rs80 (1/10/2013)[/b][hr]This post has been active for more then 3 years :) How would I go about adding a record count in the footer? The record count would capture the number of records in the detail section. Would it be to add a derived column? So for example, if I have an OLEDB source I would capture the number of records coming out of the OLEDB source. Thanks for the help![/quote]You would add the Row Count task for a Data Flow to the Data Flow Task you have for the "Details" rows which will assign the count to a Package variable. You will then use the Package Variable in the Footer Data Flow Task where you define the footer.Thu, 10 Jan 2013 07:52:11 GMTJohn Dempseyhttp://www.sqlservercentral.com/Forums/Topic815666-148-1.aspxThis post has been active for more then 3 years :) How would I go about adding a record count in the footer? The record count would capture the number of records in the detail section. Would it be to add a derived column? So for example, if I have an OLEDB source I would capture the number of records coming out of the OLEDB source. Thanks for the help!Thu, 10 Jan 2013 07:31:13 GMTrs80http://www.sqlservercentral.com/Forums/Topic815666-148-1.aspxI would recommend trying to create a separate Script Task to display the variable to see if it stays changed outside the assigning task.Thu, 29 Nov 2012 14:48:20 GMTJohn Dempseyhttp://www.sqlservercentral.com/Forums/Topic815666-148-1.aspxYes, the evaluate as expression on variable is set to true. What seems a little odd is that the value property on the variable shows the unresolved header(same as what's being written to file)any other thoughts?Thu, 29 Nov 2012 08:14:14 GMTsharon.poirierhttp://www.sqlservercentral.com/Forums/Topic815666-148-1.aspxSharon,For you variable you are using for the Header, do you have it set to evaluate as expression? You are at the point where it is hard for me to picture the problem because I can't see the whole package.Thu, 29 Nov 2012 07:58:25 GMTJohn Dempseyhttp://www.sqlservercentral.com/Forums/Topic815666-148-1.aspxI am very close thanks to you.I use "" for my cr/lf... I'll look into that further. Yes, the variables do contain data as a result of my script component that sets them.here is how I set them: Public Overrides Sub PostExecute() 'Copy value of script variable to SSIS variable Me.Variables.strYear = Year Me.Variables.strMonth = Month Me.Variables.strDay = Day Me.Variables.strDivision = Division End Sub I set a postexecute breakpoint and watch window shows data in my headerRecord variable which uses the above variables.watch window value of variable:(User::strHeaderRecord {@HDRBATCH21631108PR - 11/08/2163@HDRJOURNAL001DISBURSEMENT})When my file is written this is what I get:@HDRBATCHPR - //@HDRJOURNALDISBURSEMENTI so appreciate your helpThu, 29 Nov 2012 07:49:18 GMTsharon.poirierhttp://www.sqlservercentral.com/Forums/Topic815666-148-1.aspx[quote][b]sharon.poirier (11/29/2012)[/b][hr]Hi John,Thanks for keeping with me on this. I did as instructed and I am getting my header record in my file, however, two things are not correct:1. my carraige return line feed is not happening... all on one line (i need two) it does work while in the expression editor.2. the expression uses variables that are set in a script task (post execute) sub. these variables are not being resolved in my header.??Sharon[/quote]Hi Sharon,1. If you look on page 2 of this thread their is a post related to the Carriage Returns. It references using escape characters "\r". I don't recall at the moment how it works, so you may need to take info from that post and do a little more research.2. I'm not exactly sure I understand what you mean by "script task (post execute) sub". What is the order of your steps? It seems this is more of a problem with resolving your variables than with the header step exactly. If you did another script task with a Message Box after the script task setting up your variables do they values display then? They probably are but I will ask anyway, are the variables being passed in as ReadWrite to the script task setting them?It seems like you are very close now. John DempseyThu, 29 Nov 2012 07:32:28 GMTJohn Dempseyhttp://www.sqlservercentral.com/Forums/Topic815666-148-1.aspxHi John,Thanks for keeping with me on this. I did as instructed and I am getting my header record in my file, however, two things are not correct:1. my carraige return line feed is not happening... all on one line (i need two) it does work while in the expression editor.2. the expression uses variables that are set in a script task (post execute) sub. these variables are not being resolved in my header.??SharonThu, 29 Nov 2012 06:35:07 GMTsharon.poirierhttp://www.sqlservercentral.com/Forums/Topic815666-148-1.aspx[quote][b]sharon.poirier (11/28/2012)[/b][hr]Thanks John,Yes it helped tremendously and have successfully appended my footer to end of my detail file. I have another question with the header. In item #3 you refer to the header property of the flat file destination having an expression editor. I'm using ssis 2005 and don't have the espressions editor for the header property. If I put my variable there my file contains my variable name for the header.Can I access this property in my script task and assign it the variable? If so, how?[/quote]Hi Sharon,You need to follow the lettered steps under step 3 to get this to work for you. Based upon you explanation, it seems like you are just putting the Variable name in the Header property of the flat file destination itself. In order to get to the expression editor for the header, you must be in the properties window for the Details Data Flow Task not the properties window of the flat file destination. Review the step by step again for steps 3a - 3d.Let me know if you are still having problems. You will not need to do this in a Script Task.John DempseyWed, 28 Nov 2012 12:08:00 GMTJohn Dempseyhttp://www.sqlservercentral.com/Forums/Topic815666-148-1.aspxThanks John,Yes it helped tremendously and have successfully appended my footer to end of my detail file. I have another question with the header. In item #3 you refer to the header property of the flat file destination having an expression editor. I'm using ssis 2005 and don't have the espressions editor for the header property. If I put my variable there my file contains my variable name for the header.Can I access this property in my script task and assign it the variable? If so, how?Wed, 28 Nov 2012 09:08:55 GMTsharon.poirierhttp://www.sqlservercentral.com/Forums/Topic815666-148-1.aspx[quote][b]sharon.poirier (11/27/2012)[/b][hr]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.I really like your approach to the header/detail/footer issue. I'm new to SSIS so please forgive me I know this is an old post, however, how do I map the source row to a variable. If I make my flat file destination the same file as my detail my mapping is that of my detail.My assumptions are that it is a source script component with one output and output column. How do I map to variable?Thank you for any assistance with this.[/quote]Hi Sharon,It appears you have to questions, the different mappings for your connection manager and creating a source row from a variable. The answer follow starting with the mapping.You will have two Data Flow Tasks and two Connection Managers. The first Data Flow Task will be for the Header & Detail rows using the Connection Manager with the Details layout. The other Data Flow Task will be for your Footer with a different Connection Manager object to the same file name, but with a differenct layout. You also need to make sure that the Footer's Data Flow Task Flat File Destination component property "Overwrite" is set to false. The reason for this is that we are pointing to the same file as the Details connection, but with a different layout and want to append to the file. If we didn't do this we would always overwrite the file with just the footer.In order to create a source row from a variable following my steps, you would need to place a Script Component in the Footer Data Flow Task and select the "Source" radio button to indicate how it will be used. You will need to make sure you include your SSIS variable name in the Custom Properties section of the component in either "ReadOnlyVariables" or "ReadWriteVariables" depending on how you will use it. You will then script your row to equal the SSIS Variable you created for your Footer row. Please see Books Online for further info on Script Component tasks in the Data Flow and coding examples. Hope that helps, John DempseyTue, 27 Nov 2012 14:17:44 GMTJohn Dempseyhttp://www.sqlservercentral.com/Forums/Topic815666-148-1.aspx5. 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.I really like your approach to the header/detail/footer issue. I'm new to SSIS so please forgive me I know this is an old post, however, how do I map the source row to a variable. If I make my flat file destination the same file as my detail my mapping is that of my detail.My assumptions are that it is a source script component with one output and output column. How do I map to variable?Thank you for any assistance with this.Tue, 27 Nov 2012 13:14:40 GMTsharon.poirierhttp://www.sqlservercentral.com/Forums/Topic815666-148-1.aspxHiI am creating a header in flat file which is a destination as suggested by you in your post. i want to pass the record count in the header.I am creating an expression in the flat file connection manager properties. I am selecting 'HeaderRowDelimiter' in property box and experssion i am dragging my count variable (r1) as (DT_WSTR, 12)@[User::r1]. But when i am executing the package, i am getting file as:(DT_WSTR, 12)@[User::r1]1,store manager2,customer representative3,regional manager4,shift leader5,district managerSomehow it does not giving me the value of record count.Any suggestions as how to solve this problemThanks Mon, 02 Jan 2012 22:26:52 GMTh.singh10http://www.sqlservercentral.com/Forums/Topic815666-148-1.aspxHi I was reading your posting regarding creating headers and footer in a flat file. It helps me a lot. I am concern about the footer that i want to generate and then later append to the final file.I am not too much familiar with VB. I am using script component as a source in my second data flow.i have three variables whose values i want to append in the footer. Please advice me as how we can do this.ThanksHarryWed, 28 Dec 2011 17:09:08 GMTh.singh10http://www.sqlservercentral.com/Forums/Topic815666-148-1.aspxThanks for your information.Mon, 25 Jul 2011 22:41:13 GMTmdkmohanhttp://www.sqlservercentral.com/Forums/Topic815666-148-1.aspxIf you follow the original steps, they walk you through mapping the variable to the header. As for your question on the different sql connections and file paths, if the file layout is the same you can use Package Configurations to point to different connections and/or file paths. Please refer to Books Online for more info on SSIS package configurations.Mon, 25 Jul 2011 09:48:11 GMTJohn Dempseyhttp://www.sqlservercentral.com/Forums/Topic815666-148-1.aspxThanks for your prompt response....Yes, how to map the SSIS variable to the header property? and I have another one too.How do I use the same SSIS package for different sql connections and different file destination paths ?Thanks in AdvanceMon, 25 Jul 2011 09:21:25 GMTmdkmohanhttp://www.sqlservercentral.com/Forums/Topic815666-148-1.aspx[quote][b]mdkmohan (7/25/2011)[/b][hr]Hi John,Your post was very helpful, but I have a query,"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"How do I implement this ?[/quote]I'm not sure I understand your question. Which part are you trying to implement? The following step after that statement, step #3, in the original post explains how to map the SSIS variable to the header property. Is this what you meant?Mon, 25 Jul 2011 08:52:49 GMTJohn Dempseyhttp://www.sqlservercentral.com/Forums/Topic815666-148-1.aspx[quote][b]monali154 (7/20/2011)[/b][hr]This is really helpful...Thanks! But I have question,For Footer I used the script task to get the value, but how will be mapping of the footer be done to flat file dest? I dont know if I am missing something.Thanks in advance![/quote]Do you mean script task or script component in the data flow? If you mean you have the footer value in a variable and then added it to the data flow pipeline through the script component, your next step is to just wire the script component task in the data flow task to the Flat File Destination Connection with the footer layout. Please refer back to steps 5 & 6 in the original post.Mon, 25 Jul 2011 08:48:32 GMTJohn Dempseyhttp://www.sqlservercentral.com/Forums/Topic815666-148-1.aspxHi John,Your post was very helpful, but I have a query,"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"How do I implement this ?Mon, 25 Jul 2011 00:45:50 GMTmdkmohanhttp://www.sqlservercentral.com/Forums/Topic815666-148-1.aspxThis is really helpful...Thanks! But I have question,For Footer I used the script task to get the value, but how will be mapping of the footer be done to flat file dest? I dont know if I am missing something.Thanks in advance!Wed, 20 Jul 2011 16:56:05 GMTmonali154http://www.sqlservercentral.com/Forums/Topic815666-148-1.aspx[quote][b]paterjean (3/25/2011)[/b][hr]Hi John Dempsey,All i want to say is AWESOME !!!AWESOME post saved lots of my time like a day or two.Thanks a LOT again..[/quote]Your welcome, I'm glad I could help and thank you for the feedback it is appreciated. You demonstrate the reason SQL Server Central's community is so great.Mon, 28 Mar 2011 10:08:13 GMTJohn Dempseyhttp://www.sqlservercentral.com/Forums/Topic815666-148-1.aspxHi John Dempsey,All i want to say is AWESOME !!!AWESOME post saved lots of my time like a day or two.Thanks a LOT again..Fri, 25 Mar 2011 14:23:50 GMTpaterjeanhttp://www.sqlservercentral.com/Forums/Topic815666-148-1.aspxi have a job that is reverse of this topic but involves parsing out a ragged flat file details from the header and footer. there are two headers, two details, two footers.header1=Header000detail datafooter1=Trlr 1111 recordsheader2=Header000detail datafooter2=Trlr 2222 records.How can I get the details into a Sql Table without the header and footer. The row delimited is crlf with no text qualifyer. I am having difficulty getting this to work. Any help appreciated.Tue, 15 Feb 2011 18:46:43 GMTbarb0822http://www.sqlservercentral.com/Forums/Topic815666-148-1.aspxThere was some problem yesterday and I was not able to view all your replies and was going on posting my problems..Sry about thatThu, 16 Sep 2010 11:36:53 GMTpreensheenhttp://www.sqlservercentral.com/Forums/Topic815666-148-1.aspxHey, Is there a way that we can bold the headers for the flat file.Thu, 16 Sep 2010 11:33:51 GMTpreensheenhttp://www.sqlservercentral.com/Forums/Topic815666-148-1.aspxHey jhon,Thanks, so much :-):-DIt worked out. It solved my problem.Thu, 16 Sep 2010 07:54:28 GMTpreensheenhttp://www.sqlservercentral.com/Forums/Topic815666-148-1.aspx[quote][b]preensheen (9/15/2010)[/b][hr]Hitting enter doen't really works[/quote]Is that your expression?Wed, 15 Sep 2010 09:26:42 GMTPhil Parkinhttp://www.sqlservercentral.com/Forums/Topic815666-148-1.aspxHitting enter doen't really worksWed, 15 Sep 2010 09:21:09 GMTpreensheenhttp://www.sqlservercentral.com/Forums/Topic815666-148-1.aspxPlease post your expression.Wed, 15 Sep 2010 09:09:38 GMTJohn Rowanhttp://www.sqlservercentral.com/Forums/Topic815666-148-1.aspxwhen select [flatfile].[HEADER] for the data flow task.Then in the expression, I am trying to give CR\LF but the expression doesn't get validated can you please show me an examplE with a screen shot.ThanksWed, 15 Sep 2010 08:56:26 GMTpreensheenhttp://www.sqlservercentral.com/Forums/Topic815666-148-1.aspxSet your variable up to Evaluate as Expression and use the expression builder to add the carriage returns into your variable.[code]"This " + "\r is " + "\r a " + "\r test"[/code]Tue, 14 Sep 2010 09:53:55 GMTJohn Rowanhttp://www.sqlservercentral.com/Forums/Topic815666-148-1.aspx[quote][b]preensheen (9/14/2010)[/b][hr]What does it mean by carriage returns...[/quote]It means a new line. (Hitting the enter key)For example:MyHeaderInfoThere would be an unprintable character representing a CR\LF (Carriage Return/Line Feed) {Windows/DOS} after each word.Tue, 14 Sep 2010 09:42:26 GMTJohn Dempsey