Click here to Skip to main content
15,941,293 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi,
I have an SSIS project for processing multiple excel file and load data to SQL server DB. I have a connection manager for excel and added the expression ExcelFilePath to my variable @User::File_Path. Which is populated from For each loop container (In variable mapping User::File_Path is set to 0 Index).

The package is running fine with multiple Excel files but the problem is when the file name is changed or a new file comes in the folder (with same structure) it gets error. If the excel file Debit "QR Feb 2018.xlsx" (which is used while creating the excel source connection) is present in that folder there is no problem. Only if the exact file name is not present then the problem occurs.

Error shows :-
Quote:
TITLE: Package Validation Error
------------------------------

Package Validation Error

------------------------------
ADDITIONAL INFORMATION:

Error at Copy Data to TempCard [Excel Source [140]]: A destination table name has not been provided.

Error at Copy Data to TempCard [SSIS.Pipeline]: "Excel Source" failed validation and returned validation status "VS_ISBROKEN".

Error at Copy Data to TempCard [SSIS.Pipeline]: One or more component failed validation.

Error at Copy Data to TempCard: There were errors during task validation.

(Microsoft.DataTransformationServices.VsIntegration)

------------------------------
BUTTONS:

OK
------------------------------


What I have tried:

I changed the DelayValidation to True. Also tried by changing the connection.
Posted
Updated 14-May-18 20:18pm

It sounds like your connection has been set up to only connect to that spreadsheet. You can parameterize your connection following the steps here ... Parameterizing connections and values at runtime using SSIS environment variables[^]
 
Share this answer
 
This was not the solution. Not even the scenario. The problem was with the delay validation of the foreach loop container. Must be set true for variable excel file.I had changed the delay validation of my excel source connection but not to the foreach loop container.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900