DTS Error problems driving me mad!

I have a DTS scheduled to run daily. It has run OK for the past few months, but now always fails, the following error appears:

dtsrun.exe - application error

the instruction at 'xxxxxxx' referenced memory at 'yyyyyyy'. The memory could not be 'read'. click on ok to terminate the program.

The dts itself runs fine if started manually, but always fails as a scheduled job, even if its started manually.

Help!!!!!Eventhough a serious violation like this shouldn't happen, it may be caused by insufficient permissions. The job is run with the permissions the user has who is configured to run sqlserver (EM, Server properties, Security-tab).|||I have never encountered this particular error myself; however if I may toss in a couple of thoughts:

1. Is there an increased load being placed on the server of late?
2. Are there a number of DTS tasks scheduled to run simultaneously?
3. Are there other OS or application tasks scheduled to run at or near the same time as this package?
4. Do you know precisely what step DTS is failing on?
5. Is it possible to schedule and run this task from another SQL Server?

Some additional thoughts (which you may already know and I apologize if you do):

1. DTS runs in a separate memory space from SQL Server; multiple DTS packages running simultaneously will degrade the amount of RAM available.
2. There is a windows registry setting for throttling the number of DTS (or was it command -- .exe -- objects that can run simultaneously). I think the default is 10. Search MSDN.
3. If you use DTS heavily on one server, consider throttling back the amount of memory available to SQL Server; I know that this is NOT recommended by MS, but I have done this in the past with a good deal of success.

I hope that you can trace the roots of your issue; good luck.

Regards,

hmscott

DTS and process task - doesnt wait my called exe application finish completely

Hi,

I need to execute a DTS that have a couple of steps and one of them is a process task that simply call an exe file i made that will send an email to warn the user.

What happens here is that the process task executes my exe file but it doesn't wait for it to compete and fires the next task after and finally closes.

There is anyway to make a "while statament" to wait until my exe application finishes?

Any idea?

Thanks in advance,
Tiago TeixeiraI Don't think there is a way to reply to SQL that an executable that it started has now stopped running.

Shame your not using the SQL email service but your prob doing other things as well.

Do you have any contriol over the .exe source code ?

Idea 1. it could possibly create a file in Dos then delete it as a last action before ending. TSQL would then loop (possibly with a while loop) to check if the file still exists notifying you that the .exe has done it's job.

GW|||Why not connect your steps with "On success" workflow/execution paths?
Also, why are you calling another application to send an e-mail when the DTS package designer can send e-mails for you? :D|||I Don't think there is a way to reply to SQL that an executable that it started has now stopped running.

Shame your not using the SQL email service but your prob doing other things as well.

Do you have any contriol over the .exe source code ?

Idea 1. it could possibly create a file in Dos then delete it as a last action before ending. TSQL would then loop (possibly with a while loop) to check if the file still exists notifying you that the .exe has done it's job.

GW

Hi,

Thanks for the help.

I don't use SQL email service because i don't have a MAPI server available to do it, and i don't want create it in IIS just for this purpose.
I found a free dll extension around that sends email without a MAPI account but, the problem is that the DTS aim is export data to excel file and after send this excel file by email, the problem is that when i send the email ( using that dll) an error occurs because the excel file still locked by the precedent task, so i created myself an exe file that frees the xls file and send the email using a .NET library instead of the dll i said, but the erro i reported appears.

About your second idea, can you please be more specific or paste a code snippet abou how to do that Loop. I would appreciate very much.

Thanks in adavance,
Tiago Teixeira|||Hi Teix

I was basically just thinking of something like this


DECLARE @.cnt int
WHILE(@.cnt <= 5)
BEGIN
DECLARE @.result int
EXEC @.result =xp_cmdshell'del Myfile.exe'
IF(@.result = 0)
PRINT'Success'
ELSE
PRINT'Failure'
WAITFORDELAY'00:15'
SET @.cnt = @.Cnt +1
END


Not sure how wise this approach is though

Good Luck

GW|||Hi GWilliy,

I found a way to manage it without code, i tried the code snippet you generously wrote but application behaved in the same way as before.

So, i created by hand a schedule job and inspite of make a single DTS package that would make all the stuff i needed i sliced the main job in 3 parts and i added each one sequentially to the Job and after that all the steps were accomplished correctly and i got the stuff working.

Thanks all for the precious help and ideas,
Best regards,
Tiago Teixeira|||Try windows handler dll, I think every exe program has unique windows handler id, use this id in a loop... Just an Idea...|||thanks i'll investigate that later, but from now i think i've the job done.

thanks
Tiago Teixeira|||Poison Ref Try windows handler dll, how would you access this from TSQL ?

may be handy for future ref

Teix - Glad U got sorted

GW

DTS "Execute Process Task" fails...

I have a DTS package which utilizes an "Execute Process
Task". This task calls an EXE local to my machine. When I
initially setup the DTS routine it worked. A few days
later I tried to run it again and the executable failed
on one of its steps.

I tested the executable thoroughly and decided that the
problem was with the DTS routine. I found that when I
perform the following steps the task runs properly:

1. Edit the DTS routine
2. Edit the properties of the Execute Process Task
3. Click on the "..." button to navigate to my EXE (even
though the path supplied is already correct)
4. Run it

However, after saving the package and then closing and re-
opening SEM, the same DTS routine fails when I execute it
manually without performing the steps above.

Any ideas?
Thanks.All I can think is that the pathing for the EXE is not the same for the server as it is on your local machine. Try running the job directly from the server in the same manner. If you get the same error it is to do with the pathing to the EXE file. I have something similar here and I have to run some DTS packages via terminal services to ensure correct pathing.|||I know what you are saying (I've run into that before). I don't think that's the case here though because I am running everything on my local machine. So, if I understand you right, I am running the job on the "server" (my machine).

Thanks for the input though. Any other ideas?|||Ok, cools, so the local machine is the server. What error message are you recieving?|||I'v been tinkering with DTS packages and found myself getting caught out with the server path vs client path. Some times it looks on the server i.e Browse folder will actually present a view of C:\Win... of the machine on which your instance resides and sometimes you're looking at your local machine. Confusing but makes sense sometimes.|||The error message:
Step Error Source: Microsoft Data Transformation Services (DTS)Package
Step Error Description:CreateProcessTask 'DTSTask_DTSCreateProcessTask_1': Process returned code -1, which does not match the specified SuccessReturnCode of 1.
Step Error code: 80040496
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:4900

I thought it was some kind of permissions issue, but I've tried several things relating to DTS package permissions to no avail.|||if you set the package as a job on the server then it is a problem with the path name. The automatied job will run on the server thinking that the server is "god" not the local machine. I would say to share the local drive, map it to the server and set the path that way. It will work if you are running a job.
Jim|||1. The server machine is my local machine so there is no issue with executable paths.

2. The DTS package is not running as a job. This issue occurs when I run it manually.

More information:
When I run the DTS package and it gets to the "Execute Process Task" step it does run the EXE every time. However, the EXE fails if I don't perform the steps listed in my initial post.

Thanks for the input.|||So it does not error out in the dts step it actualy dies when the exe is run? what is the EXE?
Jim|||The EXE is a C++ Console application which serves as a wrapper around a 3rd party DLL.

The DLL basically performs an import into SQL server while encapsulating necessary business rules.

It is a very simple application.

Step 1: Connect
Step 2: Import (it fails here)
Step 3: Disconnect|||The only thing that I can think of is that a paramiter is lost or not saved in the dts package. But why that would need a path for an exe im not sure. Does the exe have varialbles in it? Jim|||JDionne! You da man!! You got me thinking about the parameters and...
I just figured it out!!

I've been so busy looking at possible security resolutions that I missed an obvious one.

The problem was with the parameters to the EXE. One of the parameters is a filepath to an import file. Since the EXE is in the same directory with this file I had just been supplying the file name (with out specifying a path) to the parameter. This works when the current working directory is the path to my EXE. So that's why it would always work after performing the steps in my initial post. These steps changed the current working directory to be that of my EXE.

The solution was to add the path to my filepath parameter.

Thanks JDionne and everyone for the help!|||Im glad that I could help. A fresh mind is always a good thing.
If you ever have any other problems dont hesitate to ask. you can private msg me too if youd like.
Regards Jim

Dtexecui.exe

When I try to run a .dtsx file or even the Dtexecui.exe I get following error:System.IO.FileNotFoundException.

Thanks

Bill

How are you running to dtsx?

At what stage of using dtexecui do you get the error?

Does it say what file cannot be found?

Is SSIS installed correctly on that machine? An open question, but do you think it is?