Sqlserver manage DTS Packages

Managing DTS Packages-editing, scheduling, and viewing package logs

Extends SQL servers, especially data warehouse servers, have tons of DTS Packages. therefore, managing DTS Packages is a task that a lot of DBAs have to do. in the second of a Series articles, I'll talk about some of the DTs management issues I faced and how I resolved them. the issues I will address in this article are: DTS package ownership and editing, scheduling DTS Packages, viewing package logs, and stopping a running package in DTS designer. again, I hope this information will be helpful to you. for my article on SQL trace and database access auditing, click here.

Background

There are 6 developers on our OLAP/data warehouse team. Before I started working on our data warehouse server, everybody isSysAdminOn the servers, which is truly, truly bad. they 've been creating databases, granting access, and basically doing any kind of activities on SQL Server. as a result, we have tons of DTS Packages. values of them are just for testing or just been used once and never got cleaned up. all the developers access the server using Windows authentication.

To centralize the control of this database, I decided to removeSysAdminRole from all the developers and only grant themDb_ownerRole on the databases that they actually work on. This has eliminated extends confusions and miscommunications we had and increased the stability of our servers.

However, when all the developers belong toSysAdminRole, there are no problems for them to edit each other's packages, scheduling packages, viewing the package logs, etc. i'll give you a brief explanation of all those issues and provide solutions I used to resolve those issues

DTS package ownership and editing

In SQL Server, all DTS Packages are managed and stored inMSDBDatabase. Although developers cocould still open and review each DTS Packages and use the Save As function, onlyOwnerOr a member ofSysAdminRole cocould edit it. in my case, because the developers was assigned Windows authentication, each packages created has the developer's Windows login ID as its owner. therefore, although the 6 developers work on the same project, they couldn't edit each other's package, because they all have different owners. yet another reason why SQL authentication is better than Windows authentication (see Andy Warren's article on SQL authentication ).

Looking under the hood, I found out that the DTs designer CILSSp_add_dtspackageStored Procedure inMSDBDatabase when saving a local package. If you open this procedure and look at the Code toward the end of the procedure, you will see the following code

-- // Only the owner of DTS package ''% s' or a member of the SysAdmin role may create new versions of it.
If (@ owner_sid <> suser_sid () and (isnull (is_srvrolemember (N 'sysadmin'), 0) <> 1 ))
Begin
Raiserror (14586,-1,-1, @ name)
Return (1) -- Failure
End

Similarly, when deleting a package, a similar stored procedure,Sp_drop_dtspackage, Is called and it contains the same code that prevent non-owner orNon-SysAdminFrom deleting this packages.

There are 2 ways to resolve this. one unorthodox approach is to remove the checks from the stored procedure. this is not recommended because both of the stored procedures are considered system stored procedures. when you modify them, you might nullify all the support agreement with Microsoft, if there are any ;-).

Another approach requires a little more work but is worth the effort. first, you can create a SQL login that has the same rights and authority as the Windows login. then, using the uninitialized ented stored procedure,Sp_reassign_dtspackageowner, You can change the owner of the DTS package from the Windows login ID to this newly created SQL Login ID. The following is the syntax for this procedure:

Sp_reassign_dtspackageowner [@ name =] 'name ',
[@ ID =] 'id ',
[@ Newloginname =] 'newloginname'

After this is done, you need to ask your developers to change their connection setting from using Windows authentication to using SQL authentication. afterwards, you can choose to delete the Windows Authentication account in SQL Server. since all developers now use the same SQL login and this login is the owner of the DTS package, they shocould be able to review each other's packages.

What happens to the connections within a DTS package that uses the developer's Windows authentication, when the package was first created? Nothing. The package will work as usual, even that Windows authentication is removed. I 've done extensive testing on this and this didn't cause any problem at all.

Scheduling DTS Packages

Developers developers might schedule a DTS package by Right clicking on it and choose "schedule package...". However, if the person who schedules the package does not haveSysAdminAuthority, the scheduled package might not run at all. most failed scheduled DTS Packages are due to the fact the security context of SQL Server Agent is not set up properly. that is, SQL Server Agent Proxy account is not set up or is not set up properly. if you view the job history, you will see the following error message:Non-sysadmins have been denied permission to run cmdexec job steps. The step failed.

When a DTS package is scheduled, a SQL Server Agent job is created. It is a regularSQLServerAgent cmdexecJob that uses the dtsrun utility supplied by SQL Server. The owner of the job is the login ID used by the DTs schedd. If the job owner isSysAdmin, The package is run under the SQLServerAgent Service Startup account. otherwise, the context used is that of the sqlagentcmdexec Proxy account, if one is enabled. I think this account is disabled by default in SQL 2000.

To enable SQL Agent Proxy account, you need to open Enterprise Manager, expand the management folder, right click "SQL Server Agent", and then choose Properties.

Once you get the SQL Server Agent property window open, clickJob SystemTab. In the last frame of the tab, you will see non-SysAdmin job step Proxy account. Uncheck the check box. Then clickReset Proxy accountButton. fill in appropriate information. apply the changes and you are good to go. when a developer schedules a package now, the owner of the job is still the developer. when the job/package actually runs, it is going to be run under whatever proxy account you just defined.

To test this, create a test job against a test database and change the job owner to a login that does not belongSysAdmin. You will notice if you run the job it will fail before you enable the Proxy account. After the Proxy account is enabled, the job shocould run as expected.

One word of caution here. enabling the SQL Server Agent Proxy account is potentially a security risk. therefore, you need to choose the account properly. also, for SQL 7, a local Windows Account will be created during SQL 7 installation. this account ID isSqlagentcmdexecAnd this account will be used as the Proxy account. you really don't have a choice of which account you want to use. in SQL 2000, you can use any account (local or domain) you want.

Viewing the logs of DTS Packages

For debugging purposes, you can log the execution of DTS package. logging a DTS package can tell a developer the status of each step within the package: the start time, end time, whether it is successful, if not, for what reason, etc. you might notice that developers will not be able to view the package logs once they are taken outSysAdmin.

The reason is simple. As we mentioned earlier, DTS Packages are stored inMSDBDatabase. Likewise, its logging data is stored inSysdtssteplogAndSysdtstasklogTables within that database. To allow developers view the logging information, just addDb_datareaderRole of MSDB to his/her login ID. That shoshould take care of this issue.

Stopping a DTS package when it is running

Occasionally, for whatever reasons, some developers might want to kill a running DTS package during execution. if they try to stop the DTs execution within DTS designer, they will not get any feedback from SQL Server and the package wowould still be running.

When a developer tries to stop a running DTS package, s/he essential is trying to kill a process. OnlySysAdminAndProcess AdminCan do this. To allow a developer to stop a running DTS package, his/her login ID has to belong toProcess AdministratorFixed server role. Alternatively, they cocould issue the kill command to kill the DTs process.

I actually don't recommend you doing this on production server. Hopefully you have a testing environment and you cocould grant developersProcessadminRight on the test server. developers shoshould do their testing and debugging on the test server and move the packages to production after a thorough testing is done. they shocould not be able to stop a process on the production at will.

Summary

In this article, I talked about some issues related to DTS package management, provided the solutions and the rational behind them. feel free to post questions in this article's forum. I am planning to contribute more articles to this site. so stay tuned.