I have an SSIS package created from a SQL 2000 DTS using the Migration Wizard. The package imports data from a MySQL database to a SQL 2005 64-bit database running on 64-bit windows server 2003. The package runs fine when executed from SQL Server Management Studio but when I schedule it as a job it fails with: Executed as user: [the domain admin account]. The package execution failed. The step failed. I've tried a lot of different ways to make this work including creating a new SSIS package. Again, the package ran fine except when it was scheduled as a job. Any ideas would be appreciated. thanks!

Are you logging the execution of the package, is there any more information available in the log.

I have logging mode set to enabled for the DataFlow task --is that what you mean?Where can I see the output? When I run the package manually I see the detailed output, but in this case it runs successfully. I actually was finally able to get the DTS package scheduled by scripting the job on the SQL 2000 db and running the script on the SQL 2005 db.

LOTS of people have had problems executing from SQL Agent. Search this forum for some help. -Jamie

Yeah, that's what I spent all of yesterday and the day before doing. I think our situation may be a bit unique where we are using 64-bit sql on windows server 2003 64-bit connecting to MySQL. The main problems I ran into with SSIS are that 1) the MySQL drivers are 32-bit and only work in the WOW64 environment -- it's not clear to me how to make the SSIS package run on WOW64, that may be why I got a lot of "Class not registered" errors, 2) the BI Development Studio tools don't save the password in the connection string for MySQL connections (sometimes?) soI got a lot of authentication errors saying that I was using the "NO PASSWORD" option or something like that, and 3) once I got past those problems the package would run, but not as a scheduled job. At least for what I was doing, the BI Development Studio was extremely flaky. I'd import the package from SQL 2000 and the MySQL connection would work. I'd test, compile and deploy the package and at some point in the process it would stop working with errors like "class not registered", "driver not supported", etc. etc. etc.I went through that over and over again, so I'm pretty sure the problems weren't caused by careless mistakes on my part. I guess I'll have to wait for the service pack.... Rich Wood

My understanding is that if running 64 bit edition of SSIS EVERYTHING has to be 64 bit, including all the drivers. Therefore if you only have 32 bit drives you need to install the 32 bit edition of SSIS, (if not installed by default) I understand that there are 2 program files folders one for 32 bit and one for 64 bit. So look for dtexec in the 32 bit folder

I was facing exactly same problem, that my SSIS package was running fine in dev env but same package was not running in 64bit env, I have checked the check box of data source and include password=abc; in connection string, then it was started. The second issue i was facing, when I have run the package as a job it failed, then I have changed the option of precompiled = true of script task of package then again deployed and then created a job with checked data source and password inclusion then it start working. Imran

The precompile would make sense as a 32bit compiled script won't work on a 64bit machine ( I dont think)

but i am saying that i have done this and my problem resolved

I was tried to explain why it resolved your problem.

Great suggestion but my package is currently using a Data Conversion, not a script task. Unfortunately there's no "Precompiled" property. I suppose I could try to create the package on a 32-bit machine and use a script -- at this point it's not worth it to me. Thanks for the input.

I have a similar problem. I am not able to run the SSIS package as a job, worst still the SSIS logger does not work when executed as a job. The package works fine when executed in dev as well as from Integration services (logging also works in this condition.)

You are right. to run the 32bit from the command line (I have the same issue with an Oracle source to 64bit SQL2005 target). I use, D:\SQLSer~1\90\DTS\Binn\dtexec /F "E:\Projects\OracleFactExtract.dtsx" as a command string in the scheduler. Ensure that you have all the parameters set-up correctly for your execution. This is due to an Oracle bug not recognizing directory paths with '(' in the path, in our case, "D:\SQLServer2005 (x86)\90\DTS\Binn". Try to avoid mixing 32 bit and 64 bit drivers in the same package, otherwise you might be forced to run that package as 32 bit only.

I have a similar issue to one stated. My SSIS development environment is 32-Bit. Script tasks are set to Precompile=Y.When packages are promoted to a new 64-bit server, all script tasks fail and have to be recompiled. Since you can't run Precomile=N on 64-bit, I'm faced with having to manually edit each package every time it is promoted. Since only DBAs have rights to be on the Prod box, it becomes a major time consuming process to involve DBAs. (I'm not knocking DBAs. I'm one myself, just not on the Prod boxes I speak of). Is there a way around promoting scripts developed under 32 bit to 64 bit in SSIS? Rob

We had the exact same problem this link solved the issue, good luck http://support.microsoft.com/default.aspx?scid=kb;en-us;904800

I have had the same problem and gone through alot to get it running. I say, the dont is, since you dont know the future of your BI projects and its dynamics (business focus changes) DONT install SQL Server 2005 on 64x machine unless you are "FOR SURE" that all your connection in a heterogenous (Oracel, sql server 2000, mysql ...) will be in 64. I say dont. Now, untill most of the software and driver vendors have pushed out 64bit versions, you are safe with 32bit. Now to how i solved my problem. When you run in SSIS, the reason why it run is because the 64bitruntime property of the project has be set to false so SSIS runs in 32bit, thus can find 32bit connnection. This is not the same with Server Agent, its blind, it can only execute from 64bit unless you use the commandline to point it to 32bit dtsexe. What i did was that i run a virtual machine with Sql server 05 32bit installed and used its Server Agent to run the packages in Integeration Services in my 64bit environment. That said, you will have to import you packages into integration services and run it with Server Agent in 32bit environment or pointing to 32bit dtsexe. Good luck.

THIS IS HOW YOU FIX THE ISSUE. Step 1. If you are developing SSIS packages with Excel on a 64 bit machine you need to make sure the package is configured to not use 64bit runtime. There are no 64bit Excel drivers (Jet) and so whilst you code works in the desiger, i.e. getting fields and doing previews etc it won't work at runtime. Thats because the designer is 32bit and the runtime defaults to 64bit on a 64bit machine. So how do you achieve this. You need to go into the Project Properties and select the Debugging node on the left and then change the Run64bitRuntime to false. Step2. Create the job step "operating system command" and enter the command line parameters as below. Note that you need to run using the 32 bit dtexec. Code Snippet C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\dtexec /FILE "C:\SSIS\Package.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E

Hi,I am facing same issue.it will be gr8 I get help form experts like you peoples

Bimal, How to do your step 2? Thanks.

Very useful sharing! Thank you!