DTS run from .NET

My dts package has to pick up a file which is in a mapped drive of the
sql server... (system1)...
when I run it from code the execution ends up in an error... but if I
have the drive mapped (drive where I have the source file) onto the
web server (system2) the execution goes through fine.. I donot want to
have the drive mapped onto the web server.. how can I do this..Try and use the UNC path.

Joel Scavone|||I did use a full netwok path and even did add identity impersonate in
the web.config... but does not help..

The package runs fine when I register the sql server my machine and run
the package..

Sigh! Don't know what wrong

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

DTS Parametirized ExecuteSQlTask

Hi,

I have a DTS whit several SQl tasks that executes a stored procedure. The result of this execution is stored in an output parameter as global variable.

The problem is if i manually launch the DTS package, it works with no problems but after i schedule the job, i received an error in the SQl tasks i said before. What can i do to fix it?

The Error is:
-----
Executed as user: SCCCOL1\sqlservices. ...t: DTSStep_DTSActiveScriptTask_4 DTSRun OnFinish:
DTSStep_DTSActiveScriptTask_4 DTSRun OnStart: DTSStep_DTSExecuteSQLTask_26 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_26 DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart: DTSStep_DTSActiveScriptTask_1 DTSRun OnFinish: DTSStep_DTSActiveScriptTask_1 DTSRun OnStart: DTSStep_DTSExecuteSQLTask_2 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_2 DTSRun OnStart: DTSStep_DTSExecuteSQLTask_4 DTSRun OnError: DTSStep_DTSExecuteSQLTask_4, Error = -2147220421 (8004043B) Error string: The task reported failure on execution. Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 700 Error Detail Records: Error: -2147220421 (8004043B); Provider Error: 0 (0) Error string: The task reported failure on execution. Er... Process Exit Code 1. The step failed.When you schedule a dts task as a job, the permissions used for this job varies. Who is the owner of the job, what login is being used for the sql server agent service and what is the step doing when it fails ?|||Originally posted by rnealejr
When you schedule a dts task as a job, the permissions used for this job varies. Who is the owner of the job, what login is being used for the sql server agent service and what is the step doing when it fails ?

Hi,

Thanks for ur answer. The owner of my job is an windows authenticated user how has administrator permissions over the server, but user used to run sqlserver agent is default user: SqlServices. However in the properties, i've configured the connections like windows authentication ( i supuse that it's using my administrative account to run jobs).

In a previous post, i found that i have to doing bigger the login time-out for SQL Agent. I did it and i rebuild the package into one new with a connection with clear specifications to the server. i mean that before i've reference to [local] server and after i changed it to [NAMESERVER] SQL on my network. I scheduled this package and it works.

is it a bug of SQl Server? why Agent SQl works with a form and not with another?

Thanks,
Maritzita

DTS packages and transaction

Hello everyone,

My question:
Is it possible to gate the complete execution of a DTS package?

Experimentation:
-I have to databases, dbSource and dbArchive.
-Each database has 1 table of the same format, tableData

1- I put 50 000 rows in dbSource
2- Once the insertion completed, I start my DTS package. The package does this:
2.1 - Move all rows of dbSource in dbArchive
2.2 - Truncate rows from dbSource
3- Immediatly after the DTS had start, I start the insertion of another 1000 rows inside dbSource.

Results:
Once everything is terminated (DTS and 1000-rows insertion), here is the count inside each database:

dbSource:881
dbArchive:50079

So I "lost" 40 rows in the process.

My expectations:
By setting the package property Use Transactions with the Serialization isolation level, I thought that the reading part of the bulk move would be gated, so any insertion in dbSource would be officially inside dbSource after the DTS completion only.

Feel free to ask any other information you might need. I can also explain my problem from a higher point a view and what I try to achive.

Thanks !I think that this is one of the major enhancements that is available to you in SQL 2005 (SQL Server Integration Services).

Be prepared for a steep learning curve.

Regards,

hmscott|||Thanks for the info.

Unfortunately, I cannot move to 2005 on this project I run...sqlsql

DTS package runs slow when run from SQLAgent

So I've seen some posts about slow DTS performance. Many of them seem
to be related to the package execution times increasing over time.
That's not my situation.
I have this one DTS package that I had been running from SERVERA. It
normally runs in under 5 minutes. I had the package scheduled in a SQL
Server Agent 2000 job. The package was responsible for truncating a
table in a database on SERVERB and then copying data from a table in
an Oracle daabase on SERVERC.
I'm working on consolidating some SQL Server instances, so I figured
I'd add the Oracle client to SERVERA, move the DTS package to SERVERA
and recreate the job on SERVERA as well. So the DTS package runs
perfectly fine when I'm terninal serviced into the server and
executing the package from either the DTS designer or from the command
line. However, the package seems to hang when running the job from a
SQL Server Agent job.
I'm using the same user account when executing the job from terminal
session and job. The only difference I've seen so far is that SERVERB
is setup to run SQL Server and SQL Agent as DOMAIN\sqlservices, but
SERVERA is setup to run the services as sqlservices@.domain.com. I
can't imagine that would be the problem, though.
Do you have any suggestions as to what the problem could be. I can't
put the job on SERVERA into production until I figure why it doesn't
run like it does on SERVERB.
Thanks in advance,
OsolageHi
"Osolage" wrote:

> So I've seen some posts about slow DTS performance. Many of them seem
> to be related to the package execution times increasing over time.
> That's not my situation.
> I have this one DTS package that I had been running from SERVERA. It
> normally runs in under 5 minutes. I had the package scheduled in a SQL
> Server Agent 2000 job. The package was responsible for truncating a
> table in a database on SERVERB and then copying data from a table in
> an Oracle daabase on SERVERC.
> I'm working on consolidating some SQL Server instances, so I figured
> I'd add the Oracle client to SERVERA, move the DTS package to SERVERA
> and recreate the job on SERVERA as well. So the DTS package runs
> perfectly fine when I'm terninal serviced into the server and
> executing the package from either the DTS designer or from the command
> line. However, the package seems to hang when running the job from a
> SQL Server Agent job.
> I'm using the same user account when executing the job from terminal
> session and job. The only difference I've seen so far is that SERVERB
> is setup to run SQL Server and SQL Agent as DOMAIN\sqlservices, but
> SERVERA is setup to run the services as sqlservices@.domain.com. I
> can't imagine that would be the problem, though.
> Do you have any suggestions as to what the problem could be. I can't
> put the job on SERVERA into production until I figure why it doesn't
> run like it does on SERVERB.
> Thanks in advance,
> Osolage
It is not clear if you have specified SERVERB as part of the DTSRun command
or whether you have modified the job to run with distributed transactions. I
f
you have done the former then I would expect similar times and any
degredation would be network related.
John

DTS package runs slow when run from SQLAgent

So I've seen some posts about slow DTS performance. Many of them seem
to be related to the package execution times increasing over time.
That's not my situation.
I have this one DTS package that I had been running from SERVERA. It
normally runs in under 5 minutes. I had the package scheduled in a SQL
Server Agent 2000 job. The package was responsible for truncating a
table in a database on SERVERB and then copying data from a table in
an Oracle daabase on SERVERC.
I'm working on consolidating some SQL Server instances, so I figured
I'd add the Oracle client to SERVERA, move the DTS package to SERVERA
and recreate the job on SERVERA as well. So the DTS package runs
perfectly fine when I'm terninal serviced into the server and
executing the package from either the DTS designer or from the command
line. However, the package seems to hang when running the job from a
SQL Server Agent job.
I'm using the same user account when executing the job from terminal
session and job. The only difference I've seen so far is that SERVERB
is setup to run SQL Server and SQL Agent as DOMAIN\sqlservices, but
SERVERA is setup to run the services as sqlservices@.domain.com. I
can't imagine that would be the problem, though.
Do you have any suggestions as to what the problem could be. I can't
put the job on SERVERA into production until I figure why it doesn't
run like it does on SERVERB.
Thanks in advance,
OsolageHi
"Osolage" wrote:
> So I've seen some posts about slow DTS performance. Many of them seem
> to be related to the package execution times increasing over time.
> That's not my situation.
> I have this one DTS package that I had been running from SERVERA. It
> normally runs in under 5 minutes. I had the package scheduled in a SQL
> Server Agent 2000 job. The package was responsible for truncating a
> table in a database on SERVERB and then copying data from a table in
> an Oracle daabase on SERVERC.
> I'm working on consolidating some SQL Server instances, so I figured
> I'd add the Oracle client to SERVERA, move the DTS package to SERVERA
> and recreate the job on SERVERA as well. So the DTS package runs
> perfectly fine when I'm terninal serviced into the server and
> executing the package from either the DTS designer or from the command
> line. However, the package seems to hang when running the job from a
> SQL Server Agent job.
> I'm using the same user account when executing the job from terminal
> session and job. The only difference I've seen so far is that SERVERB
> is setup to run SQL Server and SQL Agent as DOMAIN\sqlservices, but
> SERVERA is setup to run the services as sqlservices@.domain.com. I
> can't imagine that would be the problem, though.
> Do you have any suggestions as to what the problem could be. I can't
> put the job on SERVERA into production until I figure why it doesn't
> run like it does on SERVERB.
> Thanks in advance,
> Osolage
It is not clear if you have specified SERVERB as part of the DTSRun command
or whether you have modified the job to run with distributed transactions. If
you have done the former then I would expect similar times and any
degredation would be network related.
John

DTS package runs slow when run from SQLAgent

So I've seen some posts about slow DTS performance. Many of them seem
to be related to the package execution times increasing over time.
That's not my situation.
I have this one DTS package that I had been running from SERVERA. It
normally runs in under 5 minutes. I had the package scheduled in a SQL
Server Agent 2000 job. The package was responsible for truncating a
table in a database on SERVERB and then copying data from a table in
an Oracle daabase on SERVERC.
I'm working on consolidating some SQL Server instances, so I figured
I'd add the Oracle client to SERVERA, move the DTS package to SERVERA
and recreate the job on SERVERA as well. So the DTS package runs
perfectly fine when I'm terninal serviced into the server and
executing the package from either the DTS designer or from the command
line. However, the package seems to hang when running the job from a
SQL Server Agent job.
I'm using the same user account when executing the job from terminal
session and job. The only difference I've seen so far is that SERVERB
is setup to run SQL Server and SQL Agent as DOMAIN\sqlservices, but
SERVERA is setup to run the services as sqlservices@.domain.com. I
can't imagine that would be the problem, though.
Do you have any suggestions as to what the problem could be. I can't
put the job on SERVERA into production until I figure why it doesn't
run like it does on SERVERB.
Thanks in advance,
Osolage
Hi
"Osolage" wrote:

> So I've seen some posts about slow DTS performance. Many of them seem
> to be related to the package execution times increasing over time.
> That's not my situation.
> I have this one DTS package that I had been running from SERVERA. It
> normally runs in under 5 minutes. I had the package scheduled in a SQL
> Server Agent 2000 job. The package was responsible for truncating a
> table in a database on SERVERB and then copying data from a table in
> an Oracle daabase on SERVERC.
> I'm working on consolidating some SQL Server instances, so I figured
> I'd add the Oracle client to SERVERA, move the DTS package to SERVERA
> and recreate the job on SERVERA as well. So the DTS package runs
> perfectly fine when I'm terninal serviced into the server and
> executing the package from either the DTS designer or from the command
> line. However, the package seems to hang when running the job from a
> SQL Server Agent job.
> I'm using the same user account when executing the job from terminal
> session and job. The only difference I've seen so far is that SERVERB
> is setup to run SQL Server and SQL Agent as DOMAIN\sqlservices, but
> SERVERA is setup to run the services as sqlservices@.domain.com. I
> can't imagine that would be the problem, though.
> Do you have any suggestions as to what the problem could be. I can't
> put the job on SERVERA into production until I figure why it doesn't
> run like it does on SERVERB.
> Thanks in advance,
> Osolage
It is not clear if you have specified SERVERB as part of the DTSRun command
or whether you have modified the job to run with distributed transactions. If
you have done the former then I would expect similar times and any
degredation would be network related.
John

DTS package execution time

Hi - I currently have a DTS package that takes raw data from a SQL
table and inserts records into several tables via a custom ActiveX
transformation. The package uses logic to determine which table to
insert into and then calls DTSlookups to perform the inserts. The #
of records I'm working with is small (3,000-10,000). If I perform a
"test, the rows are actually inserted, and rather quickly. If I
execute the package through normal methods, the execution takes several
hours and often times out?
Any ideas'Running a DTS package within Enterprise Manager executes it locally on
the machine where EM is running. Running it using a schedule runs it
on the server where the agent service is running. Different machines
have different resources and are under different loads, and have
different distances from the data. Look for data traveling over the
network vs remaining local.
As you describe it, both the source data and the final destination of
the data are in SQL Server. Are they on the same SQL Server? If so,
did you consider using stored procedures? Keeping all the work
withing SQL Server itself has some performance advantages.
Roy Harvey
Beacon Falls, CT
On 27 Jul 2006 10:37:26 -0700, clawdaddy@.gmail.com wrote:

>Hi - I currently have a DTS package that takes raw data from a SQL
>table and inserts records into several tables via a custom ActiveX
>transformation. The package uses logic to determine which table to
>insert into and then calls DTSlookups to perform the inserts. The #
>of records I'm working with is small (3,000-10,000). If I perform a
>"test, the rows are actually inserted, and rather quickly. If I
>execute the package through normal methods, the execution takes several
>hours and often times out?
>Any ideas'|||clawdaddy@.gmail.com wrote:
> Hi - I currently have a DTS package that takes raw data from a SQL
> table and inserts records into several tables via a custom ActiveX
> transformation. The package uses logic to determine which table to
> insert into and then calls DTSlookups to perform the inserts. The #
> of records I'm working with is small (3,000-10,000). If I perform a
> "test, the rows are actually inserted, and rather quickly. If I
> execute the package through normal methods, the execution takes several
> hours and often times out?
> Any ideas'
>
There's really not enough info to come up with a cause, but if this is a
SQL-to-SQL process (reading from SQL/writing to SQL), I'd question why
you used DTS to do this. I think you'd get better performance, not to
mention easier debugging, by doing this a series of INSERT INTO/SELECT
statements.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

DTS package execution time

Hi - I currently have a DTS package that takes raw data from a SQL
table and inserts records into several tables via a custom ActiveX
transformation. The package uses logic to determine which table to
insert into and then calls DTSlookups to perform the inserts. The #
of records I'm working with is small (3,000-10,000). If I perform a
"test, the rows are actually inserted, and rather quickly. If I
execute the package through normal methods, the execution takes several
hours and often times out?
Any ideas'Running a DTS package within Enterprise Manager executes it locally on
the machine where EM is running. Running it using a schedule runs it
on the server where the agent service is running. Different machines
have different resources and are under different loads, and have
different distances from the data. Look for data traveling over the
network vs remaining local.
As you describe it, both the source data and the final destination of
the data are in SQL Server. Are they on the same SQL Server? If so,
did you consider using stored procedures? Keeping all the work
withing SQL Server itself has some performance advantages.
Roy Harvey
Beacon Falls, CT
On 27 Jul 2006 10:37:26 -0700, clawdaddy@.gmail.com wrote:
>Hi - I currently have a DTS package that takes raw data from a SQL
>table and inserts records into several tables via a custom ActiveX
>transformation. The package uses logic to determine which table to
>insert into and then calls DTSlookups to perform the inserts. The #
>of records I'm working with is small (3,000-10,000). If I perform a
>"test, the rows are actually inserted, and rather quickly. If I
>execute the package through normal methods, the execution takes several
>hours and often times out?
>Any ideas'|||clawdaddy@.gmail.com wrote:
> Hi - I currently have a DTS package that takes raw data from a SQL
> table and inserts records into several tables via a custom ActiveX
> transformation. The package uses logic to determine which table to
> insert into and then calls DTSlookups to perform the inserts. The #
> of records I'm working with is small (3,000-10,000). If I perform a
> "test, the rows are actually inserted, and rather quickly. If I
> execute the package through normal methods, the execution takes several
> hours and often times out?
> Any ideas'
>
There's really not enough info to come up with a cause, but if this is a
SQL-to-SQL process (reading from SQL/writing to SQL), I'd question why
you used DTS to do this. I think you'd get better performance, not to
mention easier debugging, by doing this a series of INSERT INTO/SELECT
statements.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

DTS Package Execution Problem

Friends,
I have very strange problem.
I have a DTS Package, which migrate data from one database to another
database after some validation.
Configuration :
-Sql Server 2000
-Working on Test Database Server
-2 GB RAM
The issue is, lastly the whole migration takes only 6 - 7 hours, but
now its not compelete after 24+ hours.
--Database is same.
--DTS package is same.
If any body has any idea, please reply me.
--Points to find the exact problem.Hi
What does the packaege do? Have you checked whether your db is growing
during the execution or not? What is recovery model of the database?
"Rahul" <verma.career@.gmail.com> wrote in message
news:1189486332.650922.89980@.o80g2000hse.googlegroups.com...
> Friends,
> I have very strange problem.
> I have a DTS Package, which migrate data from one database to another
> database after some validation.
> Configuration :
> -Sql Server 2000
> -Working on Test Database Server
> -2 GB RAM
> The issue is, lastly the whole migration takes only 6 - 7 hours, but
> now its not compelete after 24+ hours.
> --Database is same.
> --DTS package is same.
> If any body has any idea, please reply me.
> --Points to find the exact problem.
>

DTS Package Execution Order

I have a package that has 12 data pump tasks all executing in parallel.

It is transferring raw data from an AS400 DW to a MSSQLSvr Staging area.

Each pump task on completion assigns values to a set of global variables, then having done this passes these as parameters to a sproc which inserts them into a table.

This seems to work for 4 or 5 of the pump tasks but, the rest of the rows in the table are all the same because the remaining pump tasks are all executing before the sprocs.

Is there a way to make sure that the entire set of job steps completes, before starting another job set of steps while still keeping them running in parallel.

I had wondered if there was a way to use the PumpComplete phase of each pump step to fire off the sproc, but can't see how you execute the step.

Any ideas would be much appreciated.Darren Green suggested
Why not take a simper approach and only populate your progress list as
tasks start executing. You could drive this quite happily off events.

To determine order of execution you would need to enumerate all steps as
constraints are held by the task they go to, not from.

For each step, enumerate the PrecedenceConstraints collection, to get
the PrecedenceConstraint objects. The StepName is the preceding step, So
if a step as no PrecedenceConstraints it is the start step. Not sure
that this guaranteed to 100 accurate either as in theory you can change
the basis and result to in effect be a "On Preceeding Step Not Run", and
have a circular reference, but I suspect DTS itself may have the same
problem as you in this case, so probably not worth worrying about for
the start step, but perfectly valid elsewhere.

DTS Package Execution Context

I have some DTS Packages that are no longer executing successfully. They were yesterday they are not today.

They are reporting an error when this line is called...

Set objConn = CreateObject("ADODB.Connection")

The error is...

The remote server machine does not exist or is unavailable.

Now this error occurs when I execute the package, but if I execute the individual task that makes this call no error is raised. There is only one task in the package and all previous lines in the task are just setting up variables and the like.

I suspect that the problem has occured due to the installation of MS Office on the machine running SQL Server. I think that perhaps a dll was overwritten that should not have been.

Has anyone else had this problem and found a solution? What is the difference in the execution context when you execute task instead of execute package?

Anyone able to help me out?

Cheers,
RokslideHowdy

Seach for this phrase within Books On Line :

package execution permissions

I think it will point you in the right direction...

Cheers,

SG.|||I have had a look through the online books. They tell you about the permissions that the package executes with but not the task,..

I would have thought they would be the same, but from what I am seeing it would appear that they are not...|||A couple of questions:

Is this a vb dts package or a sql dts package ? If it is a sql dts package, is this occuring in an activex scripting task ? Have you tried recreating a package from scratch to see if the error appears ? Did the office installation update your mdac ?|||The package is a sql dts package and the code is in an activex scripting task.

If I recreate the package the problem still appears.

I think it is possible that the installation of office did update mdac, but I don't know for sure as I didn't do the install. The MDAC version tool tells me that we are using MDAC 2.7 at the moment...|||Have you restarted sql server since the installation ? Have you installed the service pack for mdac ? Will this error appear in any attempt to CreateObject() such as Recordset or Scripting.FileSystemObject ? If you have visual basic - save this package as a visual basic module and try it from the vb ide.|||I can create any object except for ADODB objects from the looks of things.

I can restart the server but I believe it has been rebooted after the office install...|||I would apply the service pack for mdac. When you are attempting to execute the package what login are you using (and with what permissions) ? When you execute the task (and it succeeds) and you execute the package (and it fails) are you using the same login ? What is the login used for the sql server and agent services ? Also, whoever installed office, under what login was it installed ?|||Also, try to execute the vbscript code in office and see if the same error occurs. And if you have vb installed, try creating a connection object using early binding first (setting the reference for the ado object) and if that succeeds try the late-binding method (createobject). Also, try logging in as an Administrator (or Domain Admin) and as a last resort temporarily change the login for sql service to the Administrator account (to eliminate any permissions issues).|||If I open the Package and click the execute package button it fail, if I then right click on the task and go "Execute Task" it succeeds...

I will try out some of the other things you have suggested and get back to you. :)|||If the mdac sp does not help, then I would start to test permissions issues (also turn on logging for the package) - modifying the sqlagent/sql server services to run as administrator and creating the package as administrator. Lastly reinstalling the mdac.|||It looks like it was the MDAC installation that was corrupted.

INstalled MDAC 2.8 and all my troubles have gone away. Of course I had to prove to support that this was the problem before they would let me install it... ;)

Thanks for all the help. :)

DTS Package Execution (From Job)

Hi,
We have DTS package which imports data from an Excel file into an SQL Server 2000 table. The DTS package runs fine when exectued from SQL Server Enterprise Manager, but when we run the Package from a Job it executes when the excel file is in the local drive. The execution of the package fails when the excel file resides on a different Computer shared drive.
We get the following error message.

\\CompName\SharedDrive\ExcelFile.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.
Error source: Microsoft JET Database Engine
Help file:
Help context: 5003044

Note that we have access to the shared drive.
Please reply if have faced the same problem!Is it possible that the job owner doesn't have rights to remote computer and when package runs within its context it can't access a file?

It is only a guess.

Good Luck.|||Do u mean the "file access" ?
We have the file access! The file was created and modifed by us in the remote system. Do u think of any IIS problem? As of now there is no IIS installed in these systems.

Regards,
Srinidhi S. Rao

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 - stored procedure

I created a dts package and I can execute it.

I want to include the dts package execution in a stored procedure, but I can't get the stored procedure to execute it from the cmdshell.

I have sql integration services and mssql 2005 services running under a domain account.

I have saved the package as a FILE System stored package.

I just can't find a reason why it won't execute from stored procedure.....

Ron

Do you get any errors?

Thanks

|||

Hi,

An alternative would be to use a JOB to fire the package from a stored procedure or a web page.

Something like the example bellow:

Regards,

Philippe

ALTER PROCEDURE [Users].[up_Backlog2Days]

@.Day1 varchar(15)

, @.Day2 varchar(15)

as

begin

-- call the procedure like that from within an excel pivot

-- Exec sm.users.up_Backlog2Days @.Day1 = '14-Dec-2006', @.Day2 = '15-Dec-2006'

set nocount on

Declare @.Cmd as Varchar(500)

Declare @.ReturnCode as int

set @.Cmd = '/DTS "\Deployed Packages\Backlog to compare 2 days" /SERVER "." /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /SET "\package.variables[Day1].Value";"' + @.Day1 + '" /SET "\package.variables[Day2].Value";"'+ @.Day2 + '"'

EXEC msdb.dbo.sp_update_jobstep @.job_name=N'Backlog 2 days compare', @.step_id=1 ,

@.command= @.Cmd

exec msdb.dbo.sp_start_job @.job_name = N'Backlog 2 days compare'

While (SELECT Count(Status) AS Status

FROM OnGlobals.dbo.tb_Isready

WHERE (Name = 'Backlog_2_Days') and Status = 'Ready') != 1

begin

WaitFor Delay '00:01:00'

-- nothing

end

Select * from staging.dbo.tb_backlog_2_Days

End

|||

no, no errors

none that I can find

no errors in the event viewer

no errors in the sql log

it's like it just passes over the part of the procedure with the dts package

Ron

|||Can you post the code you're using to execute the DTS? Does the SQL Account running the SP have permission to remotely/locally run the package?

DTS Package - "Provider generated code execution exception: "EXCEPTION_ACCESS_VIOLATION"

When running a step within my DTS package I'm receiving the following
error - "Provider generated code execution exception:
"EXCEPTION_ACCESS_VIOLATION".
I think it may be something to do with my global variable, but I'm not
sure as I'm pretty certain I've set it all up correctly.
Below are screenprints showing my settings.
http://img153.imageshack.us/my.php?image=19tv1.jpg
http://img153.imageshack.us/my.php?image=25wz1.jpg
http://img153.imageshack.us/my.php?image=39pf.jpg
http://img164.imageshack.us/my.php?image=43nx.jpg
http://img164.imageshack.us/my.php?image=51ao.jpg
http://img164.imageshack.us/my.php?image=64lo.jpg
http://img164.imageshack.us/my.php?image=71yn.jpg
Any advice of fixing this would be greatly appreciated.Can anyone help? The below screenshots show my specific settings (note
I'm still getting the same error with these options)
http://www.files2net.com/files/46053431/1.JPG
http://www.files2net.com/files/266513949/2.JPG
http://www.files2net.com/files/266955779/3.JPG

DTS Package - "Provider generated code execution exception: "EXCEPTION_ACCES

When running a step within my DTS package I'm receiving the following error
- "Provider generated code execution exception: "EXCEPTION_ACCESS_VIOLATION"
.
I think it may be something to do with my global variable, but I'm not sure
as I'm pretty certain I've set it all up correctly.
Below are screenprints showing my settings.
http://img153.imageshack.us/my.php?image=19tv1.jpg
http://img153.imageshack.us/my.php?image=25wz1.jpg
http://img153.imageshack.us/my.php?image=39pf.jpg
http://img164.imageshack.us/my.php?image=43nx.jpg
http://img164.imageshack.us/my.php?image=51ao.jpg
http://img164.imageshack.us/my.php?image=64lo.jpg
http://img164.imageshack.us/my.php?image=71yn.jpg
Any advice of fixing this would be greatly appreciated.Can anyone help? The below screenshots show my specific settings (note I'm s
till getting the same error with these options)
http://www.files2net.com/files/46053431/1.JPG
http://www.files2net.com/files/266513949/2.JPG
http://www.files2net.com/files/266955779/3.JPG|||Managed to solve it by changing the settings to the following:
http://www.files2net.com/files/154842170/settings.jpg

DTS Package

Hi there

I've use an example I've found to build the following dts.execution from VB6:

Dim opackage As DTS.Package

Set opackage = New DTS.Package
opackage.LoadFromSQLServer PARS, , , DTSSQLStgFlag_UseTrustedConnection, , , , copyTxt, 0
opackage.Execute
opackage.UnInitialize
Set opackage = Nothing

The problem is when I execute it gives an error saying:
The specified DTS Package ('name='[notspecified]'; ID.Version ={'[notspecified]'},{'[notspecified]'}') does not exist.

Can anyone see what is wrong please? :eek:
ThanksWe had a similar problem here if the DTS package had more than one version. On the DTS packages, right click and go to versions. I would save the existing package under a new name, then delete all versions but one on the package referenced by the application.

DTS job failing execution when scheduled, works fine manually.

My DTS Package work fine if I Execute it manually, but I need to do it automatically just after midnight. I defined my schedule and made sure the job was present in the SQL Server Agent>Jobs, but it fails and the Job History shows the following error:

DTSRun: Loading... DTSRun: Executing... DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005) Error string: [Microsoft][ODBC Microsoft Access Driver] Cannot start your application. The workgroup information file is missing or opened exclusively by another user. Error source: Microsoft OLE DB Provider for ODBC Drivers Help file: Help context: 0 Error Detail Records: Error: -2147467259 (80004005); Provider Error: 1901 (76D) Error string: [Microsoft][ODBC Microsoft Access Driver] Cannot start your application. The workgroup information file is missing or opened exclusively by another user. Error source: Microsoft OLE DB Provider for ODBC Drivers Help file: Help context: 0 DTSRun OnFinish: DTSStep_DTSDataPumpTask_1 DTSRun: Package execution complete. Process Exit Code 1. The step failed.

Help!!!Whenever I've had these types of issues, they had to do with the sqlserveragent not having permissions to a particular resource. You might want to start looking in that direction.|||I guess you are tired of getting up at midnight :-)

Does this help at all?PRB: Need to Map to Default Admin Account and Use NULL for Password In Order to Query Linked Server to Access Database.

Terri|||I figured it out, I was using a mapped drive and apparently NT Services do not recognize them. I used the UNC path in my DSN connection and all is well.

Thanks for the replies.

DTS Job ActiveX Script Help

I have a DTS Package Job that needs to pre-check a txt file (see below) with a 'Date' in it. TO compare it with the current Date (execution Date -> today). If they match, move on to the next step and fail otherwise. I don't know how to create an ActiveX script to do this kind of comparison.

----------------------
Volume Unit Referred SBR Used Recfm SSNE BlkSz Dsorg Dsname
5GSL4B 6760 2005/03/09 1065535 FB 3000 27000 PS 'AAS3P.QT.SECMRK.ZXWSDB.FULL.UNPACKED'
----------------------

Thank you for any suggestion!

J827I would create the text file as another data source. You can then grab the first line, and put the date into the global variable or something, then run an activeX script to compare the global variable to the current date.

Does that kinda achieve what you are after?