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
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\BinnMicrosoft (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
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