November 27, 2012 at 1:14 pm
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.
November 27, 2012 at 2:17 pm
sharon.poirier (11/27/2012)
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.
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 Dempsey
November 28, 2012 at 9:08 am
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?
November 28, 2012 at 12:08 pm
sharon.poirier (11/28/2012)
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?
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 Dempsey
November 29, 2012 at 6:35 am
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
November 29, 2012 at 7:32 am
sharon.poirier (11/29/2012)
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
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 Dempsey
November 29, 2012 at 7:49 am
I 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 - //@HDRJOURNALDISBURSEMENT
I so appreciate your help
November 29, 2012 at 7:58 am
Sharon,
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.
November 29, 2012 at 8:14 am
Yes, 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?
November 29, 2012 at 2:48 pm
I would recommend trying to create a separate Script Task to display the variable to see if it stays changed outside the assigning task.
January 10, 2013 at 7:31 am
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!
January 10, 2013 at 7:52 am
rs80 (1/10/2013)
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!
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.
January 10, 2013 at 12:56 pm
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/?
January 10, 2013 at 1:25 pm
rs80 (1/10/2013)
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.
January 12, 2013 at 10:35 pm
Thanks John, it worked perfectly for me 🙂
Viewing 15 posts - 31 through 45 (of 55 total)
You must be logged in to reply to this topic. Login to reply