I am having issues creating a good data flow from Excel Source to SQL DB in BIDS 2010. I'm using 32-bit runtime, i have windows authentication on the SQL Server. I'm trying to send the data to a table that has no relationships or constraints at all. My excel file is .xls and I've tried doing this to SS2012 and SS2008R2 databases, getting the same errors back.
Here's my Package Validation Error:
Error at Data Flow Component [SSIS.Pipeline]: "component "Source for Excel Connection Manager" (1)" failed validation and returned validation status "VS_ISBROKEN".
Error at Data Flow Component [SSIS.Pipeline]: One or more component failed validation.
Error at Data Flow Component: There were errors during task validation.
(Microsoft.DataTransformationServices.VsIntegration)
Please check at this URL, I have commented earlier today.
BIDS Package Error's on Truncate while EXPORTING to flat file
Some suggestion as well:
1) Check the Data type in excel source. If the Source data type is different Convert or cast them as it is in Database.
2) Map the column correctly. Check if there are not null field in the destination.
3) Make sure you change the Project properties to RUN64bits to FALSE
This may not relate, but I was getting a very similar error while trying to write data to Excel from global tables. Turned out to be an issue with the Connection Manager for the database. I had to set "RetainSameConnection" to True and it started working right away. Again, this may be unrelated to your issue, and this is several months later, but it could be a solution for someone else that runs into the problem.
Again this may not be the case always but for me the excel was corrupted.
I followed these steps after wasting 3 hours:
- tried to open the source excel file, it didn't open and prompted the file is corrupted.
- recreated the excel file from the source, saved as excel (.xlsx) file
- recreated the connection manager and edited excel source
and everything works fine.
