HELP DTS problems !

I had some DTS packages that are scheduled to run as a job every week. They have been running fine for about 4 months.

One of the jobs failed in the weekend. i was normally able to run the job by logging into the server. The job failed instantenously.

I can't even run the DTS package. I am registered on my enterprise manager as an administrator as well. I tried registering as sa but it comes with the same result. i do not have any aliases. and the DTS packages are only working within the indivdual server. The errror messsage is :-

[DBNETLIB][Connection Open (Connect()).]SQL Server does not exist or access is denied

Any help would be greatly appreciated.mmmm...have you checked that all your connection inside the DTS point to a valid server? Can you connect to the server that you use inside the DTS?|||i actually created another new package and it works.
so

1. the old package does not run
2. The new package runs fine.

Do you think that my msdb database is corrupted ? or is it the individual databases ?|||Right-mouse click on the package and select "Versions". See what that shows you (busy fingures, man, busy fingures ;))|||Did that, the packages haven't been modified for the last 6 months.
I think i might restore the msdb database and go from there.|||Check the schedule relationed with your DTS package in SQL Server agent manager.See whether the owner of job had been changed or not.|||The SQL Server agent is being run as a domain user who is an administrator on the SQL Server.

Currently merging our backup tape to get the msdb database.
There was actually some patching of the servers in the weekend and one of the domain controllers was rebooted.

Does anyone think that there is some relation with the domain account, sql server agent and my dts packages are not working|||Things don't happen on their own. Some had to have been changed for the "anomaly" to happen.|||I restored the msdb database still the same error

[DBNETLIB][Connection Open (Connect()).]SQL Server does not exist or access is denied

the UDLs i believe i not working properly. Does anyone know about UDL corruption?

Would restoring the master database help ?|||Is there anything of note in your server logs? What shows up in the job history (especially if you show details)?

Have you tried to execute the package one step at a time, to see what fails? If you have multiple connections, it might not be failing where you'd expect.

Once you know which connection is failing, try to make a connection using the same credentials via Query Analyzer or OSQL.EXE to see if you get a more useful diagnostic message.

If push comes to shove, export your DTS package to a structured storage file and post it... Maybe someone here can see something in there that might help.

I'd exhaust every other avenue before even considering restoring master. That would be an absolute last ditch option for me.

-PatP|||Restoring MASTER would have no affect on success or failure of a DTS package. There NOTHING in MASTER that relates to DTS, NOTHING!..Having said that, - actually there is everything, - databases, logins, linked servers...I'd save the package as a BAS (not structured, Pat) and see what's going on there.|||When you restore the msdb database from a different server, u restore the jobs, dtspackages etc. Does any part of the system tables point to previous server. ? like sysjobs.

Help DTS Job programming

Hello:
I'm scheduling DTS packages to run as a job but when I do that, the job siply does not works, someone tolds me that it's a problem with the SQL and Windows NT authentication and security and he gaves me this URL http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q269074.
but it not so clear to me, i'm working with SQL Server 200 and Windows Server 2000.

What can I do?The job simply does not work ...
This means what are you getting any error?
If so review information from DTS package log and SQL error log or use PROFILER to monitor the activity.

That is the only KBA information available if you want to schedule a dts package.

Come with complete details about issue and setup.|||Always make sure you have the same drivers on the server as you do on your local machine if applicable. Don't know if that helps or not|||If you schedule the DTS package it will run in the context of sqlagent privileges and drivers on that server. And also the same fashion even though if you execute the package manually.|||I've seen this as a common problem when people are connecting to the server but running the package from a machine that does have appropriate drivers.

For poster - this may help, these guys are the bet in the biz with DTS - http://www.sqldts.com/default.aspx?222|||someone tolds me that it's a problem with the SQL and Windows NT authentication and security
BTW what is the current authentication set on SQL Server?

Help calling DTS package.

I'm new to using DTS packages and I'm running into a problem. Hopefully someone can help me out. I have an ASP.NET page that needs to call a DTS package. Everything seems to be working right up until I call opkg.Execute. The package seems to run but nothing happens. After further investigation I have found what looks like a permissions issue. this is the message I'm getting"
Error Number:
-2147467259 {Integer}

Error Description:
"The Microsoft Jet database engine cannot open the file ''. It is already opened exclusively by another user, or you need permission to view its data." {String}

This is obviously a permissions issue. I'm just not sure who the DTS package is running as and where to set the permissions.

Any ideas??

ThanksAfter a few modifications I'm getting a different error now. But it still is obviously a permissions problem. Everything I've read says set Impersonation equal to true. Unfortunately it already is. Any suggestions from you SQL Server gurus?

"Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection."

Thankssql

help about sql server job

I created a stored procedure.When i execute the sp in Query Analyzer or in
dts packages, it worked well.But when i scheduled it as job,the job
failed.There are 51 jobs at the server totally.
I also scheduled the sp as job on another server,this time it worked well
too.
Could anyone tell me the reason?Should I set any configurations at the
server?
Below is the sp script,I used linked server in this sp:
CREATE PROCEDURE sp1 AS
declare @.last_upd_tm datetime
set @.last_upd_tm=(select top 1 rspn_upd_tm from TBL_eCardGEMSRpt_load_tm
order by rspn_upd_tm desc)
delete trspn from TBL_eCardGEMS_Response trspn join
linkedserver.eCardGEMsRpt.dbo.Response erspn
on trspn.ResponseID=erspn.ResponseID
where erspn.LastUpdateDate>@.last_upd_tm
insert into TBL_eCardGEMS_Response
select * from linkedserver.eCardGEMsRpt.dbo.Response erspn
where erspn.LastUpdateDate>@.last_upd_tm
delete tkt from TBL_eCardGEMS_Ticket tkt join
linkedserver.eCardGEMsRpt.dbo.Response erspn
on tkt.TicketID=erspn.TicketID
where erspn.LastUpdateDate>@.last_upd_tm
insert into TBL_eCardGEMS_Ticket
select distinct tkt.* from linkedserver.eCardGEMsRpt.dbo.Response erspn
join
FMSQLPRD002.eCardGEMsRpt.dbo.Ticket tkt on
erspn.TicketID=tkt.TicketID
where erspn.LastUpdateDate>@.last_upd_tm
insert into TBL_eCardGEMSRpt_load_tm
select max(lastupdatedate) from linkedserver.eCardGEMsRpt.dbo.Response
GOAaron,
Verify if the database that the job is calling is the correct Database. By
default the database selected is master.
[ ]'s
PEDRO HENRIQUE NUNES - Brasil
"Aaron Huang" <Aapursueron@.hotmail.com> wrote in message
news:bgsh1m$lgj$1@.news01.intel.com...
> I created a stored procedure.When i execute the sp in Query Analyzer or in
> dts packages, it worked well.But when i scheduled it as job,the job
> failed.There are 51 jobs at the server totally.
> I also scheduled the sp as job on another server,this time it worked well
> too.
> Could anyone tell me the reason?Should I set any configurations at the
> server?
> Below is the sp script,I used linked server in this sp:
> CREATE PROCEDURE sp1 AS
> declare @.last_upd_tm datetime
> set @.last_upd_tm=(select top 1 rspn_upd_tm from TBL_eCardGEMSRpt_load_tm
> order by rspn_upd_tm desc)
> delete trspn from TBL_eCardGEMS_Response trspn join
> linkedserver.eCardGEMsRpt.dbo.Response erspn
> on trspn.ResponseID=erspn.ResponseID
> where erspn.LastUpdateDate>@.last_upd_tm
> insert into TBL_eCardGEMS_Response
> select * from linkedserver.eCardGEMsRpt.dbo.Response erspn
> where erspn.LastUpdateDate>@.last_upd_tm
> delete tkt from TBL_eCardGEMS_Ticket tkt join
> linkedserver.eCardGEMsRpt.dbo.Response erspn
> on tkt.TicketID=erspn.TicketID
> where erspn.LastUpdateDate>@.last_upd_tm
> insert into TBL_eCardGEMS_Ticket
> select distinct tkt.* from linkedserver.eCardGEMsRpt.dbo.Response erspn
> join
> FMSQLPRD002.eCardGEMsRpt.dbo.Ticket tkt on
> erspn.TicketID=tkt.TicketID
> where erspn.LastUpdateDate>@.last_upd_tm
> insert into TBL_eCardGEMSRpt_load_tm
> select max(lastupdatedate) from linkedserver.eCardGEMsRpt.dbo.Response
> GO

Help - DTS Custom Task to handle Job Failuer

I need to find a better way to handle DTS Job Failure issue. Currently, we have about 50 jobs which executed through DTS packages. Everytime when sources were not there or came in late, the DTS sent out an email to my page which I carried every day. Some those came in during the holidays even though I know the source party wont generate the source files at that day.

Trying to avoid to get beep everytime when job was failed. Someone suggested that it is possible to add kind of executable file within the Custom task and let it trigger the DTS packages. If, for example, a holiday then dont run the package so I wont get the page.

Any hints suggestions would be greatly appreciated

J827I get unhappy when I see 0 replies, but I have not done this before but this is how I would handle it.

I would write a dll in VB. It would check todays date against a list of holidays I might keep in a database table. If it did'nt return a result I would would use the VB syntax for firing off stored procedures (still in my dll).

Of course before you do this you need to already have your 50 DTS packages in place.

I would then build a new DTS package whose only step was to fire off this custom DLL. Register your cutom task and assign it icon and then build your DTS package using the DTS Designer (not the wizard).

Good luck.|||create a table or array with the dates of all non action days (holidays weekends down days).
create a master package that runs all of your sub packages(use the execute package task).
create a Execute SQL (or activex)Task to evaluate the NonActiveDays table against the execution of the package if getdate() = @.nonactiveday, return 0
else 1
then pass that value as a global variable to the task
when the 1 hits, run the package
this is a fairly simple solution.

and just for giggles if your code returns a 0, you can have the task email you a reminder that the task didnt run becuase of a holiday.

this opens you up to many possibllilties, for instance you could have this code run in a job the day before to remind you that the next day is a holiday and dts wont run...|||thanks thanks!|||Are you executing this from the web like in ASP.Net or something?

If you are, the problem is that the DTS package needs to run in the same security context that you created it in. The IUSR_etc... account in IIS does not have the ability to execute the package. The workarounds are sloppy and I have discussed them on this forum before. Do a search for ASP and DTS.|||I created a C# Console Application to trigger the DTS package directly and it worked (see code below). But it was an EXE (not DLL) file type.

I would like to plug this app as part of control in an existing DTS package by adding it into a Custom Task. I just noticed the DTS Custom Task only allows adding DLL format file to be registered. Then I recreated the app with Class library Type and tried to register / run Regsvr32 appName. I got Could not find entry point for DLLRegisterServer in the DLL / appName.DLL was load but the DLLRegisterServer entry point was not found, the file cant be registered.

Any helps would be highly appreciated!

---------------------
try
{
DTS.Package2Class package = new DTS.Package2Class();
object pVarPersistStgOfHost = null;

string ServerName = "DEV";
string ServerUserName = null;
string ServerPassword = null;
string PackagePassword = null;
string PackageGUID = null;
string PackageVersionGUID = null;
string PackagName = "DTSTestPackage";


package.LoadFromSQLServer(
ServerName,
ServerUserName,
ServerPassword,
DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_UseTrus tedConnection,
PackagePassword,
PackageGUID,
PackageVersionGUID,
PackagName,
ref pVarPersistStgOfHost);

package.Execute();
package.UnInitialize();

// force Release() on COM object
System.Runtime.InteropServices.Marshal.ReleaseComO bject(package);

package = null;
}
catch(System.Runtime.InteropServices.COMException e)
{
Console.WriteLine("COMException {0}", e.ErrorCode.ToString() );
Console.WriteLine("{0}", e.Message);
Console.WriteLine("{0}", e.Source);
Console.WriteLine("Stack dump\n{0}\n", e.StackTrace);
}
catch(System.Exception e)
{
Console.WriteLine("Exception");
Console.WriteLine("{0}", e.Message);
Console.WriteLine("{0}", e.Source);
Console.WriteLine("Stack dump\n{0}\n", e.StackTrace);
}