Sql-server – SQL Server 64-bit – Import from Excel

sql-server-2008-r2ssis

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.

Best Answer

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
Related Question