August 24, 2011 at 6:43 am
Hey all,
We've imported a DTS package from an old SQL2000 instance (running on Windows Server 2003 32bit) to an SQL2005 instance (Windows Server 2003 R2 64bit).
The package will copy a table from an Oracle db (SAP) to MSSQL db (where the package is started).
It was difficult to edit the package to connect correctly to the Oracle db (ODBC provider problems with 32&64bit).
But the package works now, I can execute it and it will work (in DTS 2000 Package Designer) !
Now I want to shedule this task, using SQL Server Agent job.
But here I encounter the biggest problem.
I already tried so much action in the job step:
Package (found on Management - Legacy - Data Transformation Services) = Copy Klant table FROM SAP
- An DTSrun:
CmdExec: C:\Progra~2\Microsoft SQL Server\80\Tools\Binn\DTSRun.exe /Sservername /E /N"Copy Klant table FROM SAP"
Error:
Error string: Oracle client and networking components were not found. These components are supplied by Oracle Corporation and are part of the Oracle Version 7.3.3 or later client software installation. Provider is unable to function until these components are installed. Error source: Microsoft OLE DB Provider for Oracle
- Migrate DTS to SSIS using SSMS
Migration wizard failed:
TITLE: DTS Migration Wizard Error
------------------------------
Could not load file or assembly 'Microsoft.SqlServer.Exec80PackageTask, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified.
Click Abort to stop the migration of the current package.
Click Retry to retry the operation.
Click Skip to skip the migration of the current task and continue with the next task.
- Save DTS file & run:
Doesn't worked too
- Migrate DTS to SSIS using Free DTSxChange & dtexec:
CmdExec: dtexec /F "S:\SSISpackages\Copy Klant table FROM SAP.dtsx"
Doesn't worked too
- Migrate DTS to SSIS using Free DTSxChange & SSISpackage type:
Package: S:\SSISpackages\Copy Klant table FROM SAP.dtsx
Doesn't worked too
- Created an bat file and start in job (T-SQL)
Batfile:
cd C:\Progra~2\Microsoft SQL Server\80\Tools\Binn\
DTSRun.exe /Sservername /E /N"Copy Klant table FROM SAP"[/i]
T-SQL: EXEC xp_cmdshell 'C:\scripts\Copy_Klant_table_FROM_SAP.bat'
Job was succesfull but didn't gave results
- Created an bat file and start in job (CmdExec)
Batfile:
cd C:\Progra~2\Microsoft SQL Server\80\Tools\Binn\
DTSRun.exe /Sservername /E /N"Copy Klant table FROM SAP"[/i]
CmdExec: C:\scripts\Copy_Klant_table_FROM_SAP.bat
Executed as user: CORP\sql2k5service. The process could not be created for step 1 of job 0x9DCDD5B12C4A9348B5497094015142DD (reason: 5). The step failed.
And starting the bat file (logged in as Administrator) worked well and did the job!
But changing the execute as variable in the job step didn't worked well 🙁
I still think it's a problem about the 32bit/64bit difference.
But I cann't get me out of this... 🙁
August 29, 2011 at 6:32 am
Temporary solution: add a scheduled task in Windows to start the bat file... this works
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply