Dtexec - Can Run In A Job (CmdExec) But Not From Xp_cmshell

Hi all,

I've working a while, not at full time, but seeking the solution...Here what I want to do and what I've done till now:

I want to build a sql job, so I can run a package (witch loads 2 excel sheets into 2 tables) passing "dynamic" parameters, like convert(varchar,getdate(),112) in the format YYYYMMDD. From what I've found, I can do this with an Stored Procedure, which first set this variable, building a statement so it can be run by xp_cmdshell. For example:

EXEC master.dbo.xp_cmdshell 'DTEXEC /SQ PACKAGE /SET "Package.Variables[DAY].Value";20070101'

And this statement runs with no problem in the SP until it reaches the step of loading the Excel Sheets into tables. Here it gives an error. It's about the JET driver. From what I've read in the forum and from the output error the problem seems to be that this statement executes the dtexec.exe 64bits, even the suggestion to change the property Run64BitRuntime set to False, it stills running from the dtexec.exe 64bits.


So, I changed the statement to point to dtexec.exe 32bits:

EXEC master.dbo.xp_cmdshell 'C:Program Files (x86)Microsoft SQL Server90DTSBinnDTExec.exe /SQ PACKAGE /SET "Package.Variables[DAY].Value";20070101'

But executing this statement it does not even run. The errors are:
'C:Program' is not recognized as an internal or external command
operable program or batch file

This was very wird to me, because this was very mentioned by the moderators.

So, I copy this same statement and created a job with a step type of "Operating System (CmdExec)" and it runs great...With no problem with the extraction from excel source.

Now my questions are:
1. Why the step job CmdExec recognizes the path of dtexec.exe 32bits ('C:Program Files (x86)Microsoft SQL Server90DTSBinnDTExec.exe ), but trying to run with xp_cmdshell it gives the error mencioned above. And if there is another way to set dtexec.exe 32bits besides this way?

2. If I cannot run it throught xp_cmdshell, how could I pass a parameter like convert(varchar,getdate(),112) in the format YYYYMMDD instead of the static parameter 20070101.


Thanks in advance.
Marco Francisco (Portugal)