1

I have used SQL Servers Import Data wizard to import data from an Excel spreadsheet.

The Import Data Wizard worked fine to import the data.

It also worked fine to save the SSIS package.

I can then log in to Integration Services on the server and run the package from within Integration Services.

However, when I try to run the package from SQL Server Agent, I get the error message:

The Excel Connection Manager is not supported in the 64-bit version of SSIS, as no OLE DB provider is available.

I've seen suggestions to set the "Use 32 bit runtime" option, but this had no effect. I've seen suggestions to set something similar in BIDS, but I didn't use BIDS to generate this package. I've seen options to use the 32-bit version of DTExec but I don't think the 32-bit version is installed on the server.

SQL Agent Job definition:

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'import data', 
        @step_id=1, 
        @cmdexec_success_code=0, 
        @on_success_action=1, 
        @on_success_step_id=0, 
        @on_fail_action=2, 
        @on_fail_step_id=0, 
        @retry_attempts=0, 
        @retry_interval=0, 
        @os_run_priority=0, @subsystem=N'SSIS', 
        @command=N'/SQL "\"\ExcelImportPackage\"" /SERVER MyDBServer /X86  /CHECKPOINTING OFF /REPORTING E', 
        @database_name=N'master', 
        @flags=0

Given that the package works both from the Wizard and from Integration Services I believe the server has everything needed to run the package from SQL Server Agent, but I cannot see how to do it.

11
  • You may need to install the Microsoft Access runtime on the server for the version of Excel you are using. So if the Excel file was for Excel 2016, you may need to install the 64-bit version of the Access 2016 Runtime. – Antoine Hernandez Aug 26 '16 at 14:47
  • I would consider that as an option, but it seems unnecessary given that both the Wizard and Integration Services can successfully execute the package already. I simply want to automate a process that already works... – paulH Aug 26 '16 at 15:39
  • 1
    Sometimes doing things manually uses resources on your machine instead of the server. – Antoine Hernandez Aug 26 '16 at 15:42
  • 1
    It looks like you're doing all the right things. If you want to verify, create a new SSIS package; add a Script Task and use the code in Main as I show in Is my package running in 32 or 64 bit mode; and then when you define the job step add /reporting EI to the mix to we log Information messages. I don't think the referenced Biml on that blog post will work since this is 2008/R2 as the Task model changed with 2012 – billinkc Aug 26 '16 at 16:19
  • 1
    It's running SQL Server 2008 R2 SP2. I added IEW to the reporting but it didn't show anything extra. I guess I'll have to come back to it after the weekend - maybe it will play ball after a rest! :-) – paulH Aug 26 '16 at 17:11
1

Just as a work around (since you said running the package manually works): you should be able to run the dtsx package using T-SQL via a job or make a stored proc to be called by a job, as this article explains.

For a package in the server:

DECLARE @SQLQuery AS VARCHAR(2000)

DECLARE @ServerName VARCHAR(200) = 'ARSHAD-LAPPY' 

SET @SQLQuery = 'DTExec /SQL ^"\DataTransfer^" '
SET @SQLQuery = @SQLQuery + ' /SET \Package.Variables[ServerName].Value;^"'+ @ServerName + '^"'

EXEC master..xp_cmdshell @SQLQuery
GO

For a package in the file system:

DECLARE @SQLQuery AS VARCHAR(2000)

DECLARE @ServerName VARCHAR(200) = 'ARSHAD-LAPPY' 

SET @SQLQuery = 'DTExec /FILE ^"E:\DataTransfer.dtsx^" '
SET @SQLQuery = @SQLQuery + ' /SET \Package.Variables[ServerName].Value;^"'+ @ServerName + '^"'

EXEC master..xp_cmdshell @SQLQuery
GO 
1
  • Ok, I thought this had worked as SQL Server Agent reported it as succeeding and there was data in the table, but I was actually just seeing the data that had been successfully imported from the Wizard. Once I investigated I found that the script was throwing an error: The package path referenced an object that cannot be found: "\Package.Variables[ServerName].Value". Removing the second 'SET @SQLQuery...' line changes the error to the familiar: The Excel Connection Manager is not supported in the 64-bit version of SSIS, as no OLE DB provider is available – paulH Aug 26 '16 at 15:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.