August 6, 2009 at 3:39 pm
Hi Experts,
I am running a package with a 64bit version of sql server .The package runs fine when executed manually in management studio (imported the package).
However, when I try to schedule it, it fails. I have tried a few things to fix this but to no avail. The summary of the error it gives me is that Excel is not supported in the 64 bit. I have tried the following to work around this;
- in the package property, changed the run64bitruntime debug option to "false"
- set delayed validation property to "true" for all the tasks in the package
Do I need to change the ole db microsoft jet provider? If so, how do I go about that? Any ideas please?
August 7, 2009 at 12:15 pm
Because you are using the Excel/Jet driver you need to make sure you use the 32-bit version of dtexec to run the package. On 64-bit installs SQL Server Agent uses the 64-bit version when you create a job to run an SSIS package. Check out this article, the answer you are looking for is near the bottom of the article.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 10, 2009 at 1:48 pm
A method we use is to execute the pack using the cmd mode in 32 bit. Setup the job step as a CMD package, prefixed with the following:
"C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\dtexec.exe"
Extract the command line string for your DTS package and put it after the dtexec.exe command.
Steve Haines
January 20, 2011 at 7:46 am
For anyone looking for a working solution I am using the code below on a 64 bit SQL 2008 server. Thanks Steve for the push in the right direction.
My SSIS package adds @I_SIP_ID as a new column to the excel results then inserts into a staging table.
DECLARE @V_PACKAGE VARCHAR(1000)
DECLARE @I_SIP_ID INT, @V_FILEPATH VARCHAR(1000)
SELECT @I_SIP_ID = 80, @V_FILEPATH = 'G:\SIP\NEW1.xls'
/*CMD_SHELL HAS TO USE DOS 8.3 NOTATION SO CAN ONLY HAVE ONE SET OF DOUBLE QUOTES!
SELECT @V_PACKAGE = '"D:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\dtexec.exe" '
+ '/FILE G:\SIP\Import_SIP_excel.dtsx '
+ '/SET \Package.Variables[User::i_sip_id].Value;' + CONVERT(VARCHAR(10), @I_SIP_ID) + ' '
+ '/SET \Package.Variables[User::s_file_name].Value;' + @V_FILEPATH + ' '
+ '/MAXCONCURRENT -1 '
+ '/CHECKPOINTING OFF'
CREATE TABLE #T (V_OUTPUT VARCHAR(1000))
--Insert into temp table so we pick up the results as well as exec
INSERT INTO #T
EXEC master..xp_cmdshell @V_PACKAGE
SELECT *
FROM #T
--WHERE V_OUTPUT LIKE '%Error%'
DROP TABLE #T
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply