When OLEDB Data Source table get updated the ssis package lose functionality.
When I export data from SQL Server data source to Excel file destination I experience
2 of the following problems:
1.
The sql table date column names get dynamically updated every month e.g.
1st col - 02/01/2007 will be replaced with 03/01/2007
2nd col -03/01/2007 will be replaced with 04/01/2007
&&.
14 col -03/01/2008 will be replaced with 04/01/2008
15 col - Misc
For some reason when I reconnect to my newly updated table in BI SSIS package my dynamic column names in sql data source table get displayed in the wrong order:
1st col - 03/01/2007
2nd col - 04/01/2007
&&&..
14 col - Misc
15 col - 04/01/2008
The only solution that I found is to delete OLE DB Source and then re-create it.
I have a lot of similar tables in this package and would like to update them dynamically- not manually every time then my data source table change.
Could someone please suggest another solution?
2.
My destination Excel file is a template with headers and some column formatting.
In order to get the updated column names I had to delete old column names. The problem is it makes the old worksheets with headers and formatting dysfunctional, only the header would display in the preview, and you cannot map the column names and populate the worksheets.
I would like to keep the header in the 1st row of each workbook, and column formatting, and display the column names in the 3rd row.
Is there a way to re-use formatted template without erasing everything every time then my data source table changes?
Start Free Trial
