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 ««123»»

Creating instances of excel file Expand / Collapse
Author
Message
Posted Tuesday, April 2, 2013 8:34 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:39 PM
Points: 5,307, Visits: 12,334
rs80 (4/2/2013)
Phil:

Just to make sure I've understood your approach:

1) In the data flow, I would load the data from the oledb source to the template.
2) Then I would use a file task to copy the template to whatever file name and location I want.

In this case wouldn't the template file keep on growing everytime I run the package with records from the oledb source? Do I have to purge the records in the template somehow? For example, run 1 of the package I add 10 records from the oledb source to the template, copy the file and rename it as ExceptionReport_201304021023. Run 2 of the package I add another 10 records from the oledb source to the template. In this case the template has 20 records and the exception report also has 20 records. However, I just want the last 10 records in the exception report.

Hope my question makes sense.


You have understood my suggestion well - makes sense and the answer is yes.

Keep a blank copy of the template somewhere handy and use a FST to copy it over the top of the Excel destination file before each data flow runs, to avoid appending data.

If you do a search on these forums, you will also find a technique to remove existing rows from an Excel spreadsheet using a SQL-like DELETE command. I've never implemented this though (I always try to export to CSVs, because mixing SSIS and Excel gives me palpitations).



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.

It is better to keep your mouth shut and appear stupid than to open it and remove all doubt. (Mark Twain)
Post #1437935
Posted Tuesday, April 2, 2013 9:12 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, November 21, 2014 12:41 PM
Points: 100, Visits: 313
palpitations :)

Please confirm the control flow should have this process:

1) fst to overwrite the original template w/ the existing template
2) data flow task - oledb source to excel destination
3) fst to copy the existing template to a different location and file name
Post #1437956
Posted Tuesday, April 2, 2013 9:37 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:39 PM
Points: 5,307, Visits: 12,334
Confirmed - that should do it. Good luck.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.

It is better to keep your mouth shut and appear stupid than to open it and remove all doubt. (Mark Twain)
Post #1437971
Posted Tuesday, April 2, 2013 12:35 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, November 21, 2014 12:41 PM
Points: 100, Visits: 313
Thanks kl25 and Phil for the help. I was able to implement the package using Phil's approach.
Post #1438051
Posted Tuesday, April 2, 2013 12:59 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, September 18, 2014 1:52 PM
Points: 504, Visits: 1,870
rs80 (4/2/2013)
Thanks kl25 and Phil for the help. I was able to implement the package using Phil's approach.


Glad to hear that you got it working!
Post #1438063
Posted Wednesday, April 3, 2013 8:18 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, November 21, 2014 12:41 PM
Points: 100, Visits: 313
I spoke too soon :)

Phil's approach is working. But when I try to run the package thru a batch job passing variables for the template path (source) and report path (destination) the package is still referring to the values that I have set locally. I'm expecting the variables to pick up the values from the batch job. Do I have to set expressions for the source and destination properties in each of the fst?
Post #1438364
Posted Wednesday, April 3, 2013 8:22 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:39 PM
Points: 5,307, Visits: 12,334
rs80 (4/3/2013)
I spoke too soon :)

Phil's approach is working. But when I try to run the package thru a batch job passing variables for the template path (source) and report path (destination) the package is still referring to the values that I have set locally. I'm expecting the variables to pick up the values from the batch job. Do I have to set expressions for the source and destination properties in each of the fst?


Can you provide a bit more detail about this 'batch job'? How are you calling the package and how are you passing the values to it?



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.

It is better to keep your mouth shut and appear stupid than to open it and remove all doubt. (Mark Twain)
Post #1438372
Posted Wednesday, April 3, 2013 8:36 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, November 21, 2014 12:41 PM
Points: 100, Visits: 313
I'm using the DtsRunTime.package to call the package and package.Execute to run the package. In the same library, I'm using DtsRunTime.Variable to pass the variables to the package.

Does that answer your question?
Post #1438388
Posted Wednesday, April 3, 2013 8:44 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:39 PM
Points: 5,307, Visits: 12,334
rs80 (4/3/2013)
I'm using the DtsRunTime.package to call the package and package.Execute to run the package. In the same library, I'm using DtsRunTime.Variable to pass the variables to the package.

Does that answer your question?


Hmm, not the answer I was expecting. Is this a C# app that is calling the package?



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.

It is better to keep your mouth shut and appear stupid than to open it and remove all doubt. (Mark Twain)
Post #1438395
Posted Wednesday, April 3, 2013 8:47 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, November 21, 2014 12:41 PM
Points: 100, Visits: 313
Yes, it is.
Post #1438401
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse