DTS package Global Var's - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

DTS package Global Var's

Author

Topic

frank.svs
Constraint Violating Yak Guru

368 Posts

Posted - 2007-07-16 : 07:11:02

Hi pals,

I need some help.

There are 2 DTS packages in my application.
Both the DTS packages are supplied some global params.

Basically, what is happening is that, if i right click on DTS pkg1 in DTS Designer and set the global params,
the Pkg is running fine. In my DTS pkg1, the last step to be executed is, i need to execute a SQL task which will call
a stored procedure with 5 global params. But what's happening is, the DTS pkg1 is successful in executing all the stuff inside pkg1
but failed to call the stored procedure which is responsible for calling 2nd DTS pkg. Why it so happened.

At the same time,what i have done is that i tried to execute the DTS pkg2 also independently by supplying static values inside DTS designer
to the global var's.Its working fine.

My question, why the DTS pkg1 failed to call DTS pkg2.

I even went to the Windows Event Viewer.
The error i got is :

Event Type: Warning
Event Source: SQLSERVERAGENT
Event Category: Job Engine
Event ID: 208
Date: 7/16/2007
Time: 4:06:46 PM
User: N/A
Computer: FRANKY
Description:

SQL Server Scheduled Job 'CF1E86D6-8B0B-4E06-B91A-1EE98AF9FE59' (0x3664D5FC4618A941B9C5A374AEEC5640) - Status: Failed - Invoked on: 2007-07-16 16:06:45 - Message: The job failed. The Job was invoked by User <DOMAIN>\FRANKY. The last step to run was step 1 (Execute DTS).

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

Now what i need is how to debug this,
Is there any in SQL Server once the stored procedure responsible for calling DTS pkg2 frm DTS pkg1 is called , is there any provision to print the values
received by each package on my the server.

How to debug such kind of issues.

Any suggestions are appreciated.

Thanks and Regards,
franky

nr
SQLTeam MVY

12543 Posts

Posted - 2007-07-16 : 08:16:42

I would use an activex task to call the SP or to alter the source command in the sql task.
In that way you can see what is being executed.

Try using the profiler to see what is being sent ot the server if anything.
Turn on logging in the package and see if that gives you any more info.

The sp will be running the package on the server whereas the designer will run it on your client m/c - don't know if that might be an issue.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

frank.svs
Constraint Violating Yak Guru

368 Posts

Posted - 2007-07-16 : 08:26:40

Thank You

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources