SSIS package failing in SSMS

Question

Thank you in advance for your help on this issue. Here is the situation: I have a SSIS package that essentially take data from a excel file ( using a Excel Connection) and upload it to a SQL table through a OLE DB Connection. This package runs completely fine in Visual Studio but I keep having this error once it is deployed on SSMS:

Failed to configure a connection property that has the following path: \Package.Connections[Excel Connection Manager].Properties[ConnectByProxy]. Element "ConnectByProxy" does not exist in collection "Properties". ; at Microsoft.SqlServer.IntegrationServices.Server.ISServerExec.ConnectionParametersManager.ConfigureProperty(String parameterName, Object parameterValue) at Microsoft.SqlServer.IntegrationServices.Server.ISServerExec.ConnectionParametersManager.ConfigureProperties() at Microsoft.SqlServer.IntegrationServices.Server.ISServerExec.ProjectOperator.ValidatePackageWithReference(Int64 validationId, Int64 infoId, Int64 projectId, String packageName, Int64 versionId, Nullable`1 referenceId, Project isserverProject, Boolean offlineMode)>

And I have the same error when it comes to the OLE DB ( SQL Server Native Client 11.0) connection. Both Connections are at the project level and I deploy the hole project in SSMS. I also have the Microsoft.ACE.OLEDB.16.0 provider installed in SSMS.

To fix that error I have made some changes in the project configuration in SSMS like setting the ConnectByPoxy value to TRUE or adding my UserName but still failed. The package is pretty simple, has no parameters, no variables. Also tried checking the 32 bit runtime..

Answer 1

Sometimes SSIS can throw strange errors when working with deployments to remote servers that you never encounter when executing the package locally. This implies there is likely something wrong with the compatibility of the server's version of SQL Server and your local copy (of SQL Server or Visual Studio). Perhaps verifying the deployed version of SQL Server your remote server uses and comparing it to your local copy would be worthwhile.

If they do match, next I would check the project's target SQL Server version by following the instructions at this site, and ensure it is correct. I would also confirm that the server has access to the directory in which the Excel files are being received, just to be safe.

This article specifically addresses the "ConnectByProxy" element issue you are encountering, and may prove helpful in resolving the issue or at least in giving more insight into possible issues.