SSIS Excel Connection Manager Expression

1
votes

I've set up an Excel Connection with expression for Properties ConnectionString and ExcelFilePath using SSIS variables for the Folder Path and Filename.

ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @[User::parmSourcePathUNC] + @[User::varSourceFileName] + ";Extended Properties=" + "\"" + "EXCEL 8.0;HDR=YES" + "\"" + ";"

ExcelFilePath = @[User::parmSourcePathUNC] + @[User::varSourceFileName]

This is OK if the file exists but I use a Stored Proc to determine the latest name of the filename which has a date suffix. If I change the name of the file by a single character (to simulate the next day's arrival) the package fails validation with the following error:

Error: "component 'Excel Source" (14)" failed validation and returned validation status "VS_ISBROKEN".

The new filename is set by a call to the Sproc before the Load from Excel so this should be possible but I don't see anyway to (for example) defer Validation. Thanks in advance for any help.

1
exceldynamicssisconnectionexpression

1 Answers

0
votes

Sounds like you are looking for the DelayValidation parameter. You should it to true for the Excel Connection Manager object.

https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.connectionmanager.delayvalidation.aspx