SSIS and Excel
after my SSIS package populates a table, I want to open my Excel sheet let it update (refresh) the data connection (which is set to auto populate upon opening), then remove the data connection, then save it as a new workbook. I have the code to do this. Here are the issues. If I step thru it in debug and do nothing, the new workbook is saved after the refresh happens. If I do not debug the new workbook does not have the refreshed data in it. I cannot get it to remove the data connection. I use oWB.connections.item("my connection").delete How to solve these?
March 28th, 2011 6:45pm

Are trying to remove the data connection from the Excel workbook?Arthur My Blog
Free Windows Admin Tool Kit Click here and download it now
March 28th, 2011 6:51pm

That whole series of events may be difficult to orchestrate. You may be better off attempting to use another method to refresh-on-open-only-once. The problem comes when you try to automate that package using Agent or whatever other process you're going to use. It's not likely to work. Can you not remove the "data refresh" code from the workbook entirely, do this logic in SSIS, and populate the workbook? That way, no refresh is required, and no connection information is stored in the workbook. Talk to me now on
March 28th, 2011 7:12pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics