Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345»»

Creating Header AND Footer to a flat file destination file Expand / Collapse
Author
Message
Posted Tuesday, November 27, 2012 1:14 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 5:58 AM
Points: 5, Visits: 29
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.
Post #1389391
Posted Tuesday, November 27, 2012 2:17 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 3:44 PM
Points: 940, Visits: 1,738
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
Post #1389412
Posted Wednesday, November 28, 2012 9:08 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 5:58 AM
Points: 5, Visits: 29
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?

Post #1389885
Posted Wednesday, November 28, 2012 12:08 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 3:44 PM
Points: 940, Visits: 1,738
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
Post #1390042
Posted Thursday, November 29, 2012 6:35 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 5:58 AM
Points: 5, Visits: 29
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
Post #1390502
Posted Thursday, November 29, 2012 7:32 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 3:44 PM
Points: 940, Visits: 1,738
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
Post #1390542
Posted Thursday, November 29, 2012 7:49 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 5:58 AM
Points: 5, Visits: 29
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
Post #1390555
Posted Thursday, November 29, 2012 7:58 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 3:44 PM
Points: 940, Visits: 1,738
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.
Post #1390565
Posted Thursday, November 29, 2012 8:14 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 5:58 AM
Points: 5, Visits: 29
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?
Post #1390589
Posted Thursday, November 29, 2012 2:48 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 3:44 PM
Points: 940, Visits: 1,738
I would recommend trying to create a separate Script Task to display the variable to see if it stays changed outside the assigning task.
Post #1390871
« Prev Topic | Next Topic »

Add to briefcase «««12345»»

Permissions Expand / Collapse