DTS in Sproc help....

26 May 2005 4:00 PM

j c via SQLMonster.com

I have been trying to schedule a DTS package that executes perfectly fine
when fired with Enterprise Manager. However, I need this package to run
during a run-time process inside my application. So, I have added the exec
params inside a StoredProc -- everything is working as expected. But, the
affected table is not getting populated with the data from the .xls file.

So - I am pretty sure that it's a connection/filepath/user issue. But I am
not sure how to handle this problem. The process goes like this:

1. Drop existing dbo.SR_Traps
2. Create dbo.SR_Traps
3. Connect to H:\Shared\Monitoring 2005 Data\S.R. Data Prep. 2005.xls
4. Data Pump from excel table into dbo.SR_Traps table

Looking at the DTS Package Logs, it seems to fail on number 4. above. Here
is the actual error message contained within the log:

Step Error Source: Microsoft JET Database Engine
Step Error Description:'H:\Shared\Monitoring 2005 Data\S.R. Data Prep.
2005.xls' is not a valid path. Make sure that the path name is spelled
correctly and that you are connected to the server on which the file
resides.
Step Error code: 80004005
Step Error Help File:
Step Error Help Context ID:5003044

Any input/suggestions are greatly appreciated.

j

--
Message posted via http://www.sqlmonster.com

26 May 2005 4:16 PM

ChrisR

Can you send us the string you think is blowing up?

Show quoteHide quote

"j c via SQLMonster.com" <forum@nospam.SQLMonster.com> wrote in message
news:70ef433752634d96a99ea2a3916fa27a@SQLMonster.com...
>I have been trying to schedule a DTS package that executes perfectly fine
> when fired with Enterprise Manager. However, I need this package to run
> during a run-time process inside my application. So, I have added the
> exec
> params inside a StoredProc -- everything is working as expected. But, the
> affected table is not getting populated with the data from the .xls file.
>
> So - I am pretty sure that it's a connection/filepath/user issue. But I
> am
> not sure how to handle this problem. The process goes like this:
>
> 1. Drop existing dbo.SR_Traps
> 2. Create dbo.SR_Traps
> 3. Connect to H:\Shared\Monitoring 2005 Data\S.R. Data Prep. 2005.xls
> 4. Data Pump from excel table into dbo.SR_Traps table
>
> Looking at the DTS Package Logs, it seems to fail on number 4. above.
> Here
> is the actual error message contained within the log:
>
> Step Error Source: Microsoft JET Database Engine
> Step Error Description:'H:\Shared\Monitoring 2005 Data\S.R. Data Prep.
> 2005.xls' is not a valid path. Make sure that the path name is spelled
> correctly and that you are connected to the server on which the file
> resides.
> Step Error code: 80004005
> Step Error Help File:
> Step Error Help Context ID:5003044
>
>
> Any input/suggestions are greatly appreciated.
>
> j
>
> --
> Message posted via http://www.sqlmonster.com

27 May 2005 1:41 PM

j c via SQLMonster.com

Ok - here is the StoredProc:

****
CREATE PROCEDURE AO_EXESRTrapLocsDTS AS

EXEC master..xp_cmdshell 'DTSRun /S "server" /U "user" /P "password" /N
"SR_Trapping_Data"'
GO
****

Here is the VB code I am using to fire the SP (which it is firing! But,
the resulting table gets dropped then recreated, and the last step of data
pump is not populating the table. It's empty after the DTS runs).

VB Code:
****
Dim pConn As New ADODB.Connection
pConn = "Provider=SQLOLEDB.1; Data Source=;" & _
"Initial Catalog=; User Id=; Password="
pConn.Open

Dim pDTSCommand As New ADODB.Command
pDTSCommand.ActiveConnection = pConn
pDTSCommand.CommandType = adCmdStoredProc
pDTSCommand.CommandText = "AO_EXESRTrapLocsDTS"
pDTSCommand.Execute
*****

Thanks for your input!

James

--
Message posted via http://www.sqlmonster.com

27 May 2005 6:50 PM

ChrisR

If you run the sp in query anylyzer, do you have the same outcome?

Show quoteHide quote

"j c via SQLMonster.com" <fo***@SQLMonster.com> wrote in message
news:c7fcc5e00f7243d4a2774c44dc1c37d0@SQLMonster.com...
> Ok - here is the StoredProc:
>
> ****
> CREATE PROCEDURE AO_EXESRTrapLocsDTS AS
>
> EXEC master..xp_cmdshell 'DTSRun /S "server" /U "user" /P "password" /N
> "SR_Trapping_Data"'
> GO
> ****
>
> Here is the VB code I am using to fire the SP (which it is firing! But,
> the resulting table gets dropped then recreated, and the last step of data
> pump is not populating the table. It's empty after the DTS runs).
>
> VB Code:
> ****
> Dim pConn As New ADODB.Connection
> pConn = "Provider=SQLOLEDB.1; Data Source=;" & _
> "Initial Catalog=; User Id=; Password="
> pConn.Open
>
> Dim pDTSCommand As New ADODB.Command
> pDTSCommand.ActiveConnection = pConn
> pDTSCommand.CommandType = adCmdStoredProc
> pDTSCommand.CommandText = "AO_EXESRTrapLocsDTS"
> pDTSCommand.Execute
> *****
>
>
> Thanks for your input!
>
> James
>
> --
> Message posted via http://www.sqlmonster.com

2 Jun 2005 3:03 PM

j c via SQLMonster.com

Hi Chris,

I ran the following in QueryAnalyzer...

****
EXEC master..xp_cmdshell 'DTSRun /S "MMSQL1" /U "mms" /P "mmspass" /N
"SR_Trapping_DataNoNulls"'
****

This is the actual string used in the StoredProc. The error in QA is:

" Error string: The specified DTS Package ('Name =
'SR_Trapping_DataNoNulls'; ID.VersionID = {[not specified]}.{[not
specified]}') does not exist. "

After looking in the Data Transformation Services/ Local Packages area, I
can see that it is there. If I "Execute" the DTS package from Enterprise
Manager, there is no problem.

James

--
Message posted via http://www.sqlmonster.com