July 5, 2012 at 9:26 am
Hi,
I have created a simple SSIS package which imports data from .xlsm file to Sql Server DB.
When I build and execute the package through Solution its working.
Also when I tried to Execute through command dtexec /f "C:\SSIS\SSIS_FFIEC101\Package.dtsx" it is working.
But when I try to execute the same using
xp_cmdshell 'dtexec /f "C:\SSIS\SSIS_FFIEC101\Package.dtsx"' in a SP, its failing.
I have also tried executing the package through a job, it is failing.
My actual requirement is to cal the package from MSAccess form Button event. My idea is to call the package through a SP.
am stuck in calling the package through a SP, any suggestions or help is highly appreciated.
Thanks,
Srini
July 5, 2012 at 10:37 am
Do you get an error message? If so, what is it?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 5, 2012 at 10:51 am
Permissions...:-)
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 5, 2012 at 12:50 pm
Thanks for your on time response.
I have created a job using package source as file system and given the path to execute the package.
When I start the job, its failing, the error message shown below.
Date7/5/2012 2:45:00 PM
LogJob History (SSIS)
Message
Microsoft (R) SQL Server Execute Package Utility
Version 10.0.1600.22 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
Started: 2:45:00 PM
Error: 2012-07-05 14:45:00.38
Code: 0xC001404B
Source: Package Log provider "SSIS log provider for Text files"
Description: The SSIS logging provider has failed to open the log. Error code: 0x80070005.
Access is denied.
End Error
Error: 2012-07-05 14:45:00.49
Code: 0xC0202009
Source: Package Connection manager "Excel Connection Manager 1"
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
End Error
Error: 2012-07-05 14:45:00.49
Code: 0xC020801C
Source: Data Flow Task 1 Excel Source [1]
Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Excel Connection Manager 1" 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: 2012-07-05 14:45:00.49
Code: 0xC0047017
Source: Data Flow Task 1 SSIS.Pipeline
Description: component "Excel Source" (1) failed validation and returned error code 0xC020801C.
End Error
Error: 2012-07-05 14:45:00.49
Code: 0xC004700C
Source: Data Flow Task 1 SSIS.Pipeline
Description: One or more component failed validation.
End Error
Error: 2012-07-05 14:45:00.49
Code: 0xC0024107
Source: Data Flow Task 1
Description: There were errors during task validation.
End Error
DTExec: The package execution returned DTSER_FAILURE (1).
Started: 2:45:00 PM
Finished: 2:45:00 PM
Elapsed: 0.25 seconds[/b][/i]
July 5, 2012 at 3:15 pm
What type of an Account are you using for the SQL Server Agent Account?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 5, 2012 at 3:38 pm
Welsh Corgi (7/5/2012)
What type of an Account are you using for the SQL Server Agent Account?
It's not the agent, but right idea. XP_CmdShell has a proxy user on the server. If you're not the DBA, go ask the person who is and have them look up who the proxy cmdshell user is.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
July 5, 2012 at 4:29 pm
Good job addressing the XP_cmdshell. I did not have enough time to look it up I was trying to deal with several issues, my bad
The OP mentioned that he tried running it as a job and it failed. I realize that I did not provide a solution to his problem but if he ever needs to run an SSIS PAckage as a Job he may want to consider the following.
Depending upon what you are doing an SSIS Job will fail if you use a Local Account for SQL Server Agent. So for that particular failure the Agent Account is likely the issue. Check the Job History and please post.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 5, 2012 at 4:50 pm
Thank you....
I don't know how far this i relevant to my problem. But i s=did attached my services screenshot, it has the logon as "NT Authority\Network Service" for all the Sql Server Services.
Does this need to point to local system?
Please find the attachment below.
FYI, I have already posted my error log message above..
July 5, 2012 at 5:07 pm
If you want to run an SSIS Package as a Job you will need to change the Service Account to a Domain Account.
If you do not have a Domain Account designated to be used with the SQL Server Agent Service request one.
Try changing the Account to your Login and run the job and it should nolt fail.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 9, 2012 at 2:01 pm
Hi, thanks for all your help..
There is no issue in the package, a i rightly posted in my last reply, it is an issue with the Sql Server agent being pointed to some other login. I have changed it now to my local and the jobs are running fine.
Thanks Much!!!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply