I have a SSIS package that creates a file and move the source file if the package completes successfully. When I run it from the desktop, all is well. When I deploy and EXECUTE it from server, it says that it is successful but I know for sure it did not EXECUTE because a file has not been created and the source file has not been moved.
I ran a report on the SSISDB but that too shows success.
How are you referencing the location of the file in your package? If you are referencing a drive letter - you need to check that location on the server where the package has been deployed.
I am using UNC Jeff but I found something new.
When I run the package on desktop through Visual Studio, it is a success.
When I run it the package on desktop through SSMS, it said that it is a success but it really isn't activate.
When I run it the package on Server (remote in) through SSMS, it said that all tasks failed.
Here are some of the errors. Do I need to install OLEDB?
OnError View Context 8/14/2019 3:34:18 PM "Data Flow Task:Error: One or more component failed validation.I never run any packages through SSMS - I deploy the project to the catalog and then setup an agent job to execute the package(s). This way I can insure the packages are run using a proxy account that has the necessary permissions for both database and network resources.
There are just too many issues with trying to run packages through SSMS - one of which is version related.
As for your specific issue - you appear to be importing or exporting to or from Excel. This is a separate issue with Office...and how Office has been installed. If you install the x86 version of Office on your desktop - you can use SSDT to build the package, but it won't run on the server or from SQL Server because it is expecting the x64 version.
If you install the x64 version of Office - then you cannot build the package unless you also install the x86 version of the drivers. To install the x86 or x64 version (when you have the x86 version of Office) of the drivers you have to install them using the appropriate command line.
It looks like you either don't have the specified drivers - or the method you are using to execute the package is expecting a different version.
Thanks Jeff. I didn't know that Office is required to be installed on the server to run the SSIS for MS Excel properly. I will update you.
Not office, just the ace drivers
To clarify - on your workstation you can install either the x86 or x64 version of Office. If you installed the x64 version of Office then you would get an error in SSDT when attempting to work with Excel or Access because SSDT is x86 and the drivers installed are x64.
To work with Excel you would have to install the x86 version of the ACE drivers.
On the server - you need the x64 version of the drivers to match SQL Server and the x86 version of the drivers to match SSMS and/or SSDT.
When you install Office the ACE driver for that version is installed - to install the opposing version you need to use the command line with either the /passive or /quiet (2016 and higher) after downloading the drivers: https://www.microsoft.com/en-us/download/details.aspx?id=54920
I will try it and will update you Jeff. Thanks
