So I have a package with a few control flow tasks manipulating SQL server data and then I have a script task that builds a string that defines where I want to output the data from SQL server to a new excel file. My excel file name is to be dynamic so for April I have a file called MarchData.xls - the previous month's data.
So my script task handles this as the code below shows:
Dim strFileName As String
Dim strFolderName As String
Dim strMonthNameShort As String
Dim strFullPath As String
Dim lastMonth As New Date(DateTime.Today.Year, DateTime.Today.Month - 1, 1)
strFileName = Dts.Variables("FileName").Value.ToString
strFolderName = Dts.Variables("FolderName").Value.ToString
strMonthNameShort = MonthName(lastMonth.Month, True)
strFullPath = strFolderName & strFileName.Substring(0, strFileName.IndexOf(".xls")) & strMonthNameShort & ".xls"
Now I thought that when I created a data flow task to take my data from SQL server to excel that I would set my excel connection manager connectionstring to use my @FullPath variable but I understand that initially the excel connection manager needs a default connection.
I tried this but it forces you to pick a sheet in the manager and do the mappings so if you don't set them it cancels the initial connection when you close the editor window (OK is grayed out).
If I pick a sheet and close excel connection editor it soon fails afterwards with:
TITLE: Package Validation Error
Package Validation Error
Error at Data Flow Task [Excel Destination ]: The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009.
Error at Data Flow Task [DTS.Pipeline]: component "Excel Destination" (16) failed validation and returned error code 0xC020801C.
Error at Data Flow Task [DTS.Pipeline]: One or more component failed validation.
Error at Data Flow Task: There were errors during task validation.
Error at Package [Connection manager "Excel Connection Manager"]: An OLE DB error has occurred. Error code: 0x80040E4D.