DaveWentzel.com            All Things Data


ETL Series

Principles for Designing Good ETL Packages
ETL Best Practices

Various SSIS Coding Tricks


dtsrunui information
dtsrunui.exe is located in the Tools directory of your SQL installation.  This tool allows you to quickly write a DTSRUN statement, which can execute a package from the command line.  It can also automate the process of passing parameters to the package.  

DTS Security
Any user, by default, can create and view DTS packages on your server.  If you don't want your users to be able to see the packages installed on your server, simply deny them access to sp_enum_dtspackages stored procedure. Users will not see an error if they try to see a package in Enterprise Manager, but the list will simply be empty now.
You can lock down who can create packages in Enterprise Manager by denying access to the sp_add_dtspackage stored procedure in the MSDB database. If a user is especially creative, they could get around that stored procedure by using the DTS object model, but that will lock down 99% of possible problems.
You may also want to place unwanted users into the db_denydatawriter and db_denydatareader role in the MSDB database for added security. 
Comparing Files Before Processing
There are lots of solutions for this if you do a google search.  I like using a combination of DIR and XML processing to handle this. 
DECLARE @tbl TABLE (ID INT IDENTITY(1,1),FullPath varchar(max), ModifiedDate datetime, FileName varchar(max))
INSERT INTO @tbl EXECUTE xp_cmdshell 'DIR ...'
UPDATE @tbl SET ModifiedDate = SUBSTRING(fullpath,1,PATINDEX('%[AP]M%',fullpath)+1)
UPDATE @tbl SET FileName = SUBSTRING(fullpath,40,LEN(fullpath))
SELECT ModifiedDate,FileName FROM @tbl FOR XML PATH('FILE'),ROOT('ROOT'))

Add new comment