April 30, 2009 at 12:54 pm
Hello,
I need to accomplish the following task:
On a daily basis, delete the data out of an excel spreadsheet and repopulate it with data from a SQL database query.
After some searching and testing, I found that because the spreadsheet contains linked data, I cannot simply run a query after setting up an excel data connection.
What I've tried to do to get around it is create a package that is set up as follows:
First Step - delete the excel spreadsheet using a File System Task with a flat file connection to the spreadsheet.
Second Step - In a data flow task:
First, use my OLE DB connection and create a OLE DB Source with the appropriate view/query selected.
Then, using an Excel Destination (pointing to the same spreadsheet that was deleted in the first step), create the destination connection.
The "Excel Destination" part is where I am getting stuck. I need to name the Excel sheet and create the column headings each time this process is run. However, it only works the first time I do it. The settings don't seem to save. (when this is run daily, each time, I need the sheet "tblSmallwares" to be created with 12 columns w/ specified data types)
Any thoughts/ideas?
Thank you so much in advance and please let me know if further clarification is needed.
Christine
April 30, 2009 at 6:21 pm
One way of making this work is to have a template spreadsheet somewhere, which contains no data, but only the column headings/sheet names. Then your process would be:
a) Delete orig spreadsheet
b) Copy from template
c) Continue with processing ...
Phil
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 1, 2009 at 9:02 am
Great idea!! It worked like a charm.
Now, one more small problem. For some reason, the records are being inserted into the spreadsheet out of order. For this insert, there are 17179 rows total. While the records are sorted like the should be per the query, the last 6235 records are being inserted at the beginning of the spreadsheet - so they are effectively out of order.
Any ideas?
Thanks!
May 1, 2009 at 8:36 pm
So the records coming out of the OLEDB source are in the correct order, yet they are written in some other order?
Can you confirm that by adding a Data Viewer to view the source data?
I don't know the answer, but I imagine that the Sort transformation will fix it for you, if needed.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 4, 2009 at 7:43 am
Ah, the sort worked. Thanks! That was very weird. I appreciate your help with these issues! Have a wonderful week!
November 3, 2009 at 2:02 pm
absolutely brilliant! thank you so much
forget about DROP TABLE `Sheet1` and all that this is the way to go!!!!!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply