DEPLOYING SSIS IN TASK SCHEDULER WITH BAT FILE FOR ORACLE CONNECTIVITY

Hi,

Few of the below Points have to be Considered while deploying a SSIS Package as a File Sytem in a Windows Scheduler using Batch File Command.

Pre-Requiste : Windows 7 , SQL SERVER 2008 R2 Setup File, Tested DTSX Package File With Configuration  Within the DTSX Project Properties, select the Debugging page, and change the 64BITRUNTIME to FALSE, default value is TRUE.

Assume Oracle - 11/10g runs in/as a 32-bit Runtime.

Possible cause of Errors Listed out here ! since when i Choose Oracle is my Destination

I have to Use the OLEDB as my Destination Object ! Note: I have used Default Provider

http://blogs.msdn.com/b/dataaccesstechnologies/archive/2009/11/10/ssis-error-code-dts-e-oledberror-an-ole-db-error-has-occurred-reasons-and-troubleshooting.aspx

  

I need the Command Tool DTEXEC Utility for my Deployement
So i Started running the SQL server 2008 R2 Setup

When i tried to Install the Integration Services alone as show in the Figure ,

After Completing the Installtion when i try to deploy it
I GOT THE BELOW ERROR , because i have tried executing dtexec util using the Below path in my batch File

C:\Program Files\Microsoft SQL Server\100\DTS\Binn

Microsoft (R) SQL Server Execute Package UtilityVersion 10.50.1600.1 for 64-bit
Copyright (C) Microsoft Corporation 2010. All rights reserved.

Started: 5:50:44 AM

Progress: 2013-04-15 05:50:45.15
Source: Data Flow Task
Validating: 0% complete
End Progress
Error: 2013-04-15 05:50:45.15
Code: 0xC0209303
Source: Package Connection manager "Oracle_Source"

Description: SSIS Error Code DTS_E_OLEDB_NOPROVIDER_64BIT_ERROR. The requested OLE DB provider MSDAORA.1 is not registered -- perhaps no 64-bit provider is available. Error code: 0x00000000.
An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered".
End Error
DTExec: The package execution returned DTSER_FAILURE (1).

Started: 5:50:44 AM
Finished: 5:50:45 AM
Elapsed: 0.25 seconds


Now I understood the Error is Because of 32 bit installation   Again Now i Tried Installing the Mangement Tools - ALL using the R2 setup 

After the installation got over, i found the  C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\ (32 Bit folder) has the DTEXEC utility copied on it

Now tried using the  below path in my batch file to execute it

C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\

It didnt stopped throughing the Erros, find the below LOG ERROR


This time  Error Log is different !!!


Microsoft (R) SQL Server Execute Package Utility


Version 10.50.1600.1 for 32-bit

Copyright (C) Microsoft Corporation 2010. All rights reserved.

Started: 3:37:15 AM

Progress: 2013-04-12 03:37:16.18

Source: Data Flow Task

Validating: 0% complete

End Progress

Error: 2013-04-12 03:37:16.20

Code: 0xC0202009

Source: Package Connection manager "Oracle_Source"

Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.

An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80004005 Description: "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.".

End Error

Error: 2013-04-12 03:37:16.20

Code: 0xC020801C

Source: Data Flow Task OLE DB Source [668]

Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Oracle_Source" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

End Error

Error: 2013-04-12 03:37:16.20

Code: 0xC0047017

Source: Data Flow Task SSIS.Pipeline

Description: component "OLE DB Source" (668) failed validation and returned error code 0xC020801C.

End Error
 
Clearly Indicates from the Error that the OLEDB Provider is not instllaed for the Oracle Connectivity in the Server

DownLoad the 32-bit Oracle Data Access Components (ODAC) from Oracle.com Downloads
ODAC1120320Xcopy_32bit.zip

Follow the installation Instructions Thats it ..........

you are now ready to Deploy DTSX Package in the Live Server with the minimal Pre-requiste installed on it

Find below the Create Task Scheduler Steps
http://geekepisodes.com/sqlbi/2010/schedule-ssis-package-execution-with-windows-task-scheduler/
Batch file command for windows Scheduler follows

@echo off

FOR /F "tokens=2-4 delims=/ " %%i IN ('date /t') DO SET DATE=%%i-%%j-%%k

C:

CD C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\

dtexec.exe /FILE C:\DashBoard\SSIS_Setup\Package.dtsx /DECRYPT password /ConfigFile C:\DashBoard\SSIS_Setup\DashBoardConfig.dtsConfig /CHECKPOINTING OFF > C:\DashBoard\SSIS_Setup\Logs\%DATE%_PSStageLog.txt

if ERRORLEVEL 0 SET ERRORLEV=0

if ERRORLEVEL 1 SET ERRORLEV=1

echo ERRORLEVEL = %ERRORLEV%

exit /B %ERRORLEV%

echo PS Stage Complete


 
Thanks for this blogger

http://blogs.lessthandot.com/index.php/DataMgmt/ssis-1/when-is-dtexec-installed