January 27, 2011 at 6:38 am
Hi ,
i have deployed the SSIS packages in MSDB Server. I like to run the deployed packages through jobs by using Operating System(Exec cmd) options in SQl Server Agent. but i tried running by using the below command but it's not running . it says cannot read my source file .
/DTS "\MSDB\MIS\Billing ETL\CNS_SRC_OUTPUT_FILES" /SERVER GLS1DESQL01 /CHECKPOINTING OFF /REPORTING E
OR
/DTS "\\GLS1DESQL01\MSDB\MIS\Billing ETL\CNS_SRC_OUTPUT_FILES" /SERVER GLS1DESQL01 /CHECKPOINTING OFF /REPORTING E
NOTE: Source for my package is Excel File it's located in some other server.
Error:
Message
Executed as user: CORP\s-ssis_mis. The process could not be created for step 1 of job 0xA503DC6ED1BD504EB9601FA4F405CDF1 (reason: The system cannot find the file specified). The step failed.
can anybody help or correct me . If i'm doing any wrong in my above command .
Thanks in Advance!
Sabarinathan.C
January 27, 2011 at 8:52 am
Within the Job Properties you can change the Step Type to SQL Server Integration Services Package and you can select your package that is stored in MSDB that way. Is there any reason you can not run your package in this way?
January 27, 2011 at 10:41 am
Go to the cmd prompt --> type dtexecui and enter
In the GUI that open, select the package stored in the MSDB and then go to the Command Line tab (The last option on the left)
This gives you the command line that you need to put in the SQL Server Agent Job.
Now coming to the excel source file, the path to the excel file specified in the SSIS package should be
1. Valid
2. Should contain the file
3. SQL Server Agent should have access to the File
4. If the Job is tunning under a proxy account, then that proxy should have access to that file.
Thank You,
Best Regards,
SQLBuddy
January 27, 2011 at 1:06 pm
You'll have to use the dtexec utility in the exec cmd step.
Since you are uploading an Excel file, the package should probably run in 32-bit mode, so make sure you select the right dtexec.exe.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 27, 2011 at 10:52 pm
Hi, Initially i have tried connecting "SQl integration Services" and connected the MSDB server to schedule the package . but while triggering the package through job i got below error message.
Error:
Executed as user: CORP\s-ssis_mis. Microsoft (R) SQL Server Execute Package Utility Version 10.50.1600.1 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 12:00:20 AM Error: 2011-01-28 00:00:23.53 Code: 0xC00F9304 Source: CNS_SRC_OUTPUT_FILES Connection manager "SRC_CNS" Description: SSIS Error Code DTS_E_OLEDB_EXCEL_NOT_SUPPORTED: The Excel Connection Manager is not supported in the 64-bit version of SSIS, as no OLE DB provider is available. End Error Error: 2011-01-28 00:00:23.53 Code: 0xC020801C Source: DF_CNS_SRC_STG SRC_CNS [642] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "SRC_CNS" failed with error code 0xC00F9304. There may be error messages posted before this with more information on why the AcquireConnection method call failed. End Error Error: 2011-01-28 00:00:23.53 Code: 0xC0047017 Source: DF_CNS_SRC_STG SSIS.Pipeline Description: component "SRC_CNS" (642) failed validation and returned error code 0xC020801C. End Error Error: 2011-01-28 00:00:23.54 Code: 0xC004700C Source: DF_CNS_SRC_STG SSIS.Pipeline Description: One or more component failed validation. End Error Error: 2011-01-28 00:00:23.54 Code: 0xC0024107 Source: DF_CNS_SRC_STG Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 12:00:20 AM Finished: 12:00:23 AM Elapsed: 3.39 seconds. The package execution failed. The step failed.
So i tried connecting the package through Operating System CMD option. But even though i'm getting the same error message.
guide me how to choose the 32 bit dtexec.exe .
Thanks in Advance!
Sabarinathan.C
January 27, 2011 at 10:55 pm
Hi Koen,
Excatly i'm gotting 64 bit error while i'm executing the package through jobs . can you guide me how to choose 32 bit dtexec.exe from which location . i never used this CMD prompt option to execute the job .
Error :
Executed as user: CORP\s-ssis_mis. Microsoft (R) SQL Server Execute Package Utility Version 10.50.1600.1 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 12:00:20 AM Error: 2011-01-28 00:00:23.53 Code: 0xC00F9304 Source: CNS_SRC_OUTPUT_FILES Connection manager "SRC_CNS" Description: SSIS Error Code DTS_E_OLEDB_EXCEL_NOT_SUPPORTED: The Excel Connection Manager is not supported in the 64-bit version of SSIS, as no OLE DB provider is available. End Error Error: 2011-01-28 00:00:23.53 Code: 0xC020801C Source: DF_CNS_SRC_STG SRC_CNS [642] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "SRC_CNS" failed with error code 0xC00F9304. There may be error messages posted before this with more information on why the AcquireConnection method call failed. End Error Error: 2011-01-28 00:00:23.53 Code: 0xC0047017 Source: DF_CNS_SRC_STG SSIS.Pipeline Description: component "SRC_CNS" (642) failed validation and returned error code 0xC020801C. End Error Error: 2011-01-28 00:00:23.54 Code: 0xC004700C Source: DF_CNS_SRC_STG SSIS.Pipeline Description: One or more component failed validation. End Error Error: 2011-01-28 00:00:23.54 Code: 0xC0024107 Source: DF_CNS_SRC_STG Description: There were errors during task validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 12:00:20 AM Finished: 12:00:23 AM Elapsed: 3.39 seconds. The package execution failed. The step failed.
Thanks in Advance!
Sabarinathan
January 27, 2011 at 11:37 pm
If you have a 64-bit machine, you'll have two program folders:
one regular, for 64-bit programs and one called C:\Program Files (x86)\. That last one is for 32-bit programs.
Look for dtexec there.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 28, 2011 at 10:00 am
The Excel Connection Manager is not supported in the 64-bit version of SSIS, as no OLE DB provider is available.
First Question is
Are you able to run the pakage fine in BIDS
If Yes, Then
2. Find the path of your 32 bit DTexec and then paste it before the command in the Job. It will be something like this
"C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\dtexec.exe" /f "F:\SSIS Packages\Reporting\ImportClientXLS.dtsx" /X86
Thank you,
Best Regards,
SQLBuddy
January 28, 2011 at 4:26 pm
try this
C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\DTExec.exe /DTS "\MSDB\MIS\Billing ETL\CNS_SRC_OUTPUT_FILES" /SERVER GLS1DESQL01 /CHECKPOINTING OFF /REPORTING E
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply