SQLTeam.com | Weblogs | Forums

Package runs fine on desktop, but not on server

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.
" Data Flow Task
OnError View Context 8/14/2019 3:34:18 PM "Data Flow Task:Error: There were errors during task validation.
" Data Flow Task
OnError View Context 8/14/2019 3:34:18 PM "Second:Error: The requested OLE DB provider Microsoft.Jet.OLEDB.4.0 is not registered. If the 64-bit driver is not installed, run the package in 32-bit mode. Error code: 0x00000000.
An OLE DB record is available. Source: ""Microsoft OLE DB Service Components"" Hresult: 0x80040154 Description: ""Class not registered"".
"
OnError View Context 8/14/2019 3:34:18 PM "Data Flow Task:Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager ""Excel Connection Manager 1"" failed with error code 0xC0209303. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
" Data Flow Task
OnError View Context 8/14/2019 3:34:18 PM "Data Flow Task:Error: Excel Source 1 failed validation and returned error code 0xC020801C.
" Data Flow Task

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.

1 Like

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

1 Like

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

2 Likes

I will try it and will update you Jeff. Thanks