Advertisement

06.26.2008 at 11:02AM PDT, ID: 23519204
[x]
Attachment Details

Running DTS package via stored procedure using xp_cmdshell does not work for some  users, MS SQL Server

Asked by dwoliver in MS SQL DTS, MS SQL Server

I have a DTS package that imports a text file into a table.  I have a stored procedure that uses xp_cmdshell to run the DTS package using a dtsrun command line.  My user is the creator of the DTS package and can run the stored procedure successfully.  Other users can run  the stored procedure with no error but the import does not succeed.  These same users can succuessfully run the import by running the same  command line that is passed to xp_cmdshell from a command prompt on their local machine.  I am attaching the code from the stored procedure.Start Free Trial 
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:

AS
	DECLARE @CmdText varchar(500)
 
	BEGIN
		DELETE FROM dbo.tblHPNRosterImport
 
	END
 
 
	BEGIN
		SET  @CmdText = 'DTSRun /S "(local)" /N "ImportStateRosterData" /G "{10F3789C-9F95-4C40-9F8A-C91ADB256313}" /W "0" /E' 
 
		EXEC master.dbo.xp_cmdshell  @CmdText
	END
 
	RETURN
 
Keywords: Running DTS package via stored proce…
Title
1 exec DTS within a SP:  xp_cmdshell …
2 DTS
3 Running SSIS package/XP_CMDSHELL
4 DTS task using DTSRUN
5 Running  a DTS Package
6 Saving DTS Packages
 
Loading Advertisement...
 
[+][-]06.26.2008 at 11:29AM PDT, ID: 21877619

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]06.26.2008 at 12:00PM PDT, ID: 21877943

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]06.26.2008 at 02:08PM PDT, ID: 21879043

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]06.27.2008 at 06:58AM PDT, ID: 21883757

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]06.27.2008 at 08:27AM PDT, ID: 21884760

View this solution now by starting your 7-day free trial. Setting up your free trial is quick, easy, and secure. We will return you to this solution, unlocked, when you're done.

 

About this solution

Zones: MS SQL DTS, MS SQL Server
Sign Up Now!
Solution Provided By: rickchild
Participating Experts: 2
Solution Grade: B
 
 
 
Loading Advertisement...
20080716-EE-VQP-32 / EE_QW_2_20070628