DTS Question

I would use the DTSRUN command to execute the DTS package. On the DTSRUN
command you can use the /L option to create a log froma DTS package.
"shub" wrote:

> I am using SQL Server 2000 and I have a created a DTS package that
> copies Data from a Excel Spreasheet to a SQL Server table then executes
> a user Stored Proc
> What I am wanting to do is if for some reason the Stored Proc errors
> out because of bad data , I would like to see the errors reported in a
> file so that it can be reviewed and I was not sure if there is a way to
> output the errors in DTS. When I run the Stored Proc theough Query
> Analyzer I see the errors in Query Analyzer, I basically want to see
> the same information after running the DTS. Can this be done, if so
> how.
> Any help in this regard is greatly appreciated.
> Thanks
>Thanks Greg for your response. I will definitely look at that option.
Is there any way this option could be incorporated when executed
through enterprise manager.
Greg Larsen wrote:[vbcol=seagreen]
> I would use the DTSRUN command to execute the DTS package. On the DTSRUN
> command you can use the /L option to create a log froma DTS package.
> "shub" wrote:
>|||I don't know of any way, sorry.
"shub" wrote:

> Thanks Greg for your response. I will definitely look at that option.
> Is there any way this option could be incorporated when executed
> through enterprise manager.
> Greg Larsen wrote:
>|||I am using SQL Server 2000 and I have a created a DTS package that
copies Data from a Excel Spreasheet to a SQL Server table then executes
a user Stored Proc
What I am wanting to do is if for some reason the Stored Proc errors
out because of bad data , I would like to see the errors reported in a
file so that it can be reviewed and I was not sure if there is a way to
output the errors in DTS. When I run the Stored Proc theough Query
Analyzer I see the errors in Query Analyzer, I basically want to see
the same information after running the DTS. Can this be done, if so
how.
Any help in this regard is greatly appreciated.
Thanks|||I would use the DTSRUN command to execute the DTS package. On the DTSRUN
command you can use the /L option to create a log froma DTS package.
"shub" wrote:

> I am using SQL Server 2000 and I have a created a DTS package that
> copies Data from a Excel Spreasheet to a SQL Server table then executes
> a user Stored Proc
> What I am wanting to do is if for some reason the Stored Proc errors
> out because of bad data , I would like to see the errors reported in a
> file so that it can be reviewed and I was not sure if there is a way to
> output the errors in DTS. When I run the Stored Proc theough Query
> Analyzer I see the errors in Query Analyzer, I basically want to see
> the same information after running the DTS. Can this be done, if so
> how.
> Any help in this regard is greatly appreciated.
> Thanks
>|||Thanks Greg for your response. I will definitely look at that option.
Is there any way this option could be incorporated when executed
through enterprise manager.
Greg Larsen wrote:[vbcol=seagreen]
> I would use the DTSRUN command to execute the DTS package. On the DTSRUN
> command you can use the /L option to create a log froma DTS package.
> "shub" wrote:
>|||I don't know of any way, sorry.
"shub" wrote:

> Thanks Greg for your response. I will definitely look at that option.
> Is there any way this option could be incorporated when executed
> through enterprise manager.
> Greg Larsen wrote:
>|||Hi Greg,
Yes, if you open up your DTS package and go to Package => Properties you
will see a tab for 'Logging', in the 'Error Handling' section you can specif
y
a file to log to. Just remember that the file is always appended to and not
overwritten.
Ray
"shub" wrote:

> Thanks Greg for your response. I will definitely look at that option.
> Is there any way this option could be incorporated when executed
> through enterprise manager.
> Greg Larsen wrote:
>|||Hi Greg,
Yes, if you open up your DTS package and go to Package => Properties you
will see a tab for 'Logging', in the 'Error Handling' section you can specif
y
a file to log to. Just remember that the file is always appended to and not
overwritten.
Ray
"shub" wrote:

> Thanks Greg for your response. I will definitely look at that option.
> Is there any way this option could be incorporated when executed
> through enterprise manager.
> Greg Larsen wrote:
>|||Thanks Ray. I tried using that however when there are multiple errorrs
it is displaying only the first error. For example in my case the DTS
package executes a stored proc to add logins from the table but in some
cases because of typos the proc cannot grant access because it cannot
find the user account in the domain, but if there are multiple errors
it is displaying the very firts one but when I run the same stored proc
through Query analyzer I see all the errors and I need to see all the
errors so that it can be informed that there are wrong entries in the
table.
Any ideas? Here is the only error I am getting
Step 'DTSStep_DTSExecuteSQLTask_2' failed
Step Error Source: Microsoft Data Transformation Services (DTS) Package
Step Error Description:The task reported failure on execution.
(Microsoft OLE DB Provider for SQL Server (80040e14): Windows NT user
or group 'YYY\XXX' not found. Check the name again.)
Step Error code: 8004043B
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:1100
****************************************
************************************
************************
rb wrote:[vbcol=seagreen]
> Hi Greg,
> Yes, if you open up your DTS package and go to Package => Properties you
> will see a tab for 'Logging', in the 'Error Handling' section you can spec
ify
> a file to log to. Just remember that the file is always appended to and no
t
> overwritten.
> Ray
> "shub" wrote:
>

dts parameter

I am running the DTS as follows,

SET @.CMD = 'dtsrun /S '+@.server+' /U '+@.user+' /P '+@.pass+' /N '+@.dtsn+' /A MyBinaryID:19 = '+@.MyBinaryID

EXECUTE master..xp_cmdshell @.CMD

This does not return all the data correctly.MyBinaryIDis a Binary(8) field, any idea how should I pass MyBinaryID and define what type of parameter in the DTS?

In the DTS package, set the Global Variable MyBinary to Integer type, and map it to the parameter used in your query. For example I use such query in a Execute SQL Task in DTS:

UPDATE tbl_DTS
SET name='xxx'
WHERE CONVERT(BIGINT,MyBinaryID)= ?

And I map the MyBinary Global Variable to Parameter1. Declare @.MyBinaryID as BIGINT in T-SQL and assign a proper value to it (of cource the value of @.MyBinary need to be some value converted from the BINARY(8) data). Then set the @.CMD as following:

SET @.CMD = 'dtsrun /S'+@.server+' /U'+@.user+' /P'+@.pass+' /N'+@.dtsn+' /A MyBinaryID= '
+CONVERT(VARCHAR,@.MyBinaryID)

EXEC master..xp_cmdshell @.CMD

DTS Package Problem

I have a stored procedure that calls a DTS package using xp_cmdshell dtsrun. The problem is strange, I can execute the stored procedure from Query Analyzer and it works fine. I call the stored procedure from inside of an ASP.NET application and it seems to execute but doesn't. The user account for the ASP.NET application has rights to execute the stored procedure and I have run a trace and so see any login failures. I'm stumped, any clues??

neel

xp_cmdshell can run an enterprise but it is a SQL Server Agent dependent service, you have to install SQL Server Agent with a service account to run all the dependent services. Hope this helps|||

Does the SQL Server agent have to be on the client computer or just the computer with SQL Server on it. I would think since I'm just sending the stored procedure name of the stored procedure on SQL Server that that server would be the only place I need the SQL Server Agent?

Thanks

Neel

|||The SQL Server Agent in the computer executing xp_cmdshell needs a service account because you can schedule xp_cmdshell with a job to transfer data from main frame to another SQL Server in the same network and it will run it. SQL Server Agent needs a service account if your application will use any of its dependent services like DTS Automation, Replication and jobs. Microsoft keep this from developers because there is a lot to know in development. Hope this help|||

Thanks it is working now. I also had to go in and setup a proxy account in the SQL Server Agent that had sysadmin rights because the users of the program and the program user account do not have sysadmin rights.

Thanks Again

Neel

DTS Package execution

Is there a method to either:
A) Kick of a DTS package directly from a stored procedure without shelling out to the DTSRun facility.
B) Kick off a scheduled job containing a DTS task from a stored procedure?
What's the best method?Choose option as stated in this KBA http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q269074 to schedule DTS as a job.|||Thanks Satya. Good info.

DTS package does not exist?

I ran the DTS package using the DTSRun as following:
SET @.action = 'DTSRun /S'+@.server+' /U "sa" /P "correctpassword" /N
"DataMover" '
exec master.dbo.xp_cmdshell @.action
It ran fine in one server. And failed in another server. The error message
is:
Error: -2147217900 (80040E14); Provider Error: 14262 (37B6)
Error string: The specified DTS Package ('Name = '"DataMover"
'; ID.VersionID = {[not specified]}.{[not specified]}') d
oes not exist.
Error source: Microsoft OLE DB Provider for SQL Server
Help file:
Help context: 0
The verified the DTS package named as "DataMover" does exist on the problem
server. The DTS packages in two servers are exactly the same. I'm confused
why it worked on one and failed on the other one.
Thanks a lot,
FLXI changed the
/N "DataMover"
to
/NDataMover
It woked about the change. I still don't understand why it performs
differently on two servers. FLX
"FLX" <nospam@.hotmail.com> wrote in message
news:%23paHNQDDEHA.4080@.TK2MSFTNGP09.phx.gbl...
> I ran the DTS package using the DTSRun as following:
> SET @.action = 'DTSRun /S'+@.server+' /U "sa" /P "correctpassword" /N
> "DataMover" '
> exec master.dbo.xp_cmdshell @.action
>
> It ran fine in one server. And failed in another server. The error message
> is:
> Error: -2147217900 (80040E14); Provider Error: 14262 (37B6)
> Error string: The specified DTS Package ('Name = '"DataMover"
> '; ID.VersionID = {[not specified]}.{[not specified]}')
does not exist.
> Error source: Microsoft OLE DB Provider for SQL Server
> Help file:
> Help context: 0
>
> The verified the DTS package named as "DataMover" does exist on the
problem
> server. The DTS packages in two servers are exactly the same. I'm confused
> why it worked on one and failed on the other one.
> Thanks a lot,
> FLX
>
>|||Hello,
The behavior you described is mostly like a bug mentioned below:
308801 FIX: DTSRUN Fails with Errors If the Arguments Have Multiple Space
http://support.microsoft.com/?id=308801
To resolve this problem, obtain the latest service pack for Microsoft SQL
Server 2000. For additional information, click the following article number
to view the article in the Microsoft Knowledge Base:
INF: How to Obtain the Latest SQL Server 2000 Service Pack
http://support.microsoft.com/?id=290211
Please check the service pack on your 2 servers.
321185 HOW TO: Identify Your SQL Server Service Pack Version and Edition
http://support.microsoft.com/?id=321185
If the services packs on your 2 servers are different, we can explain why
it performs differently on two servers.
I am looking forward to hearing from you soon.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

DTS Log Difference

Hi All,
I use the DTSRUN utility with /L option to provide the log filename with
path for logging.
I think in Package property "Error File" option is same as the above but
there the NAME will be given in design time.
Also there is an option to log to SQL Server - MSDB and also to eventlog.
Which method is the best for ERROR Log? Can any one explain with detail or
any website link that explain all that?
Thanks
PrabhatIt depends on who require the data. if the data is being used my
Network/System Administrators then you can log it into Event Log. If Databas
e
Administrators are going to use the data then u log it on MSDB.
If the data is required by the Application Suport Team the u can log it into
a flat file or one of the tables in the Production Database.
Where ever you log the data its one and the same.
hope this answers the question.
thanks and regards
Chandra
"Prabhat" wrote:

> Hi All,
> I use the DTSRUN utility with /L option to provide the log filename with
> path for logging.
> I think in Package property "Error File" option is same as the above but
> there the NAME will be given in design time.
> Also there is an option to log to SQL Server - MSDB and also to eventlog.
> Which method is the best for ERROR Log? Can any one explain with detail or
> any website link that explain all that?
> Thanks
> Prabhat
>
>
>|||Hi Chandra! Thanks for your reply.
Prabhat
"Chandra" <Chandra@.discussions.microsoft.com> wrote in message
news:8EF7A0BC-4391-40C0-A9FB-26A46DF9E14F@.microsoft.com...
> It depends on who require the data. if the data is being used my
> Network/System Administrators then you can log it into Event Log. If
Database
> Administrators are going to use the data then u log it on MSDB.
> If the data is required by the Application Suport Team the u can log it
into
> a flat file or one of the tables in the Production Database.
> Where ever you log the data its one and the same.
> hope this answers the question.
> thanks and regards
> Chandra
>
> "Prabhat" wrote:
>
eventlog.
or|||Good to know that the solution addressed your needs. Really appreciate if yo
u
can rate the Post.
This can be done by answering "Was this post helpful to you?"
"Prabhat" wrote:

> Hi Chandra! Thanks for your reply.
> Prabhat
>
> "Chandra" <Chandra@.discussions.microsoft.com> wrote in message
> news:8EF7A0BC-4391-40C0-A9FB-26A46DF9E14F@.microsoft.com...
> Database
> into
> eventlog.
> or
>
>|||Hi Chandra,
Yes This Post was answered my question and Helpful to me. Thanks.
Can U please look into my Post "DTS - With Conditions" and Help me in that?
Thanks
Prabhat
"Chandra" <Chandra@.discussions.microsoft.com> wrote in message
news:B1BF847C-25D8-44FE-B082-77D8687FBE0F@.microsoft.com...
> Good to know that the solution addressed your needs. Really appreciate if
you
> can rate the Post.
> This can be done by answering "Was this post helpful to you?"
> "Prabhat" wrote:
>
it
with
but
detail|||Hi Prabhat,
I will try to answer your post "DTS - With Conditions".
for more information on rating the post, please refer to
http://www.microsoft.com/wn3/locale...s.htm#RateAPost
thanks and regards
Chandra
"Prabhat" wrote:

> Hi Chandra,
> Yes This Post was answered my question and Helpful to me. Thanks.
> Can U please look into my Post "DTS - With Conditions" and Help me in that
?
> Thanks
> Prabhat
> "Chandra" <Chandra@.discussions.microsoft.com> wrote in message
> news:B1BF847C-25D8-44FE-B082-77D8687FBE0F@.microsoft.com...
> you
> it
> with
> but
> detail
>
>

DTS in SP Error

I have a DTS package already created on the server. I want to call it
in a sp using the xp_cmdshell 'dtsrun ...'
I'm getting "xpsql.cpp: Error 87 from GetProxyAccount on line 604"
Any ideas how to fix this?
Thanks
MikeI found the fix for this error but I have a new problem
This DTS package is supposed to drop the table if it exists then create
a new one and load data from an excel file. This works fine if I run it
manually from enterprise manager.
If I try to run it from query analyzer i get an error stating that the
directory is not a valid path. And it doesn't drop the table first.
Any ideas?|||I suppose that when you run that package from QA you are using a different
user than EM or something like that. And that user doesn't have permissions
over that folder.
--
Current location: Alicante (ES)
"Mike" wrote:

> I found the fix for this error but I have a new problem
> This DTS package is supposed to drop the table if it exists then create
> a new one and load data from an excel file. This works fine if I run it
> manually from enterprise manager.
> If I try to run it from query analyzer i get an error stating that the
> directory is not a valid path. And it doesn't drop the table first.
> Any ideas?
>