databasejournal.com databasejournal.com

Data Transformation Services (DTS)

Overview The most common method used to execute DTS packages from within T-SQL scripts or stored procedures, is via xp_cmdshell and dtsrun. Whilst this works, and is very simple the implement, it lacks flexibility. The SQL Server 2000 version of dtsrun allows you to pass GlobalVariables on the command line, which improves it's appeal, but the security restrictions on xp_cmdshell can still limit it's appeal. This article...

databasejournal.com databasejournal.com

DTS How to...use and return global variables from the Execute SQL or DataPump Task

8 How to use and return global variables from the Execute SQL or DataPump Task For those fortunate enough to have SQL 2000, using and returning global variables with an Execute SQL Task, or using them as part of source statement in the DataPump task is now a standard feature. For those still using SQL Server 7.0 this tedious process, but here are some examples of how it can be done. The only way to integrate a global variable...

databasejournal.com databasejournal.com

DTS How to...Skip Rows during Import

7 How to Skip Rows during Import A common request is how to skip certain rows during an import or data transformation process. DTS can accomplish this, but perhaps the simplest solution is to import data into a staging table, then use standard SQL statements to delete what is not required, before inserting the remaining rows into the final table. Now back to DTS... Firstly you can skip header rows by using the First Row...

databasejournal.com databasejournal.com

SQL Server Links

This is not an extensive list of SQL Server links, just some specific pages that I find useful and are not always easy to find. For more links see the DatabaseJournal Links page amongst others. Microsoft Resources Microsoft TechNet - SQL Server Technology Centre: http://www.microsoft.com/technet/sql/ MSDN Online SQL Server Developer Centre: http://msdn.microsoft.com/sqlserver/ MSDN Library: See Technical Articles > Database...

databasejournal.com databasejournal.com

Build your own DTS CustomTask

We all use the standard DTS Tasks within our packages, but sometimes we need that little bit more. The first choice is to write an ActiveX Script Task to do the job, and this usually suffices. Unfortunately there are some limitations as to what you can do, the most obvious being the inability to define variables as types. The other problem is one of maintenance, as the script must be maintained in every package that requires this...

databasejournal.com databasejournal.com

Import all files in a directory (Multi Pkg)

This example shows how you can use two DTS packages to control the import of all files in a given directory. For this, it is assumed that all files have the same format, and that all files are imported into the same table. Feel free to expand on this to fit your own solution. (This example uses the Microsoft FileSystemObject) Package One (DTSDirDemo-Loop) has just an Active Script Task, with the following code...

databasejournal.com databasejournal.com

DTSBackup Utility (VB)

This small application allows you backup DTS Packages by saving them to COM-structured storage files. This can be done for both server based packages and those stored in the repository. COM-structured storage files can also be imported or restored, back into the server or repository. Backing up the msdb DB will backup your DTS Packages, but this makes it very difficult to restore just a single package. The msdb also controls...

databasejournal.com databasejournal.com

GVCustomTask Step by Step Guide

A step by step guide for the GVCustomTask written in response to a newsgroup posting: Here is a step by step guide. Download and extract GVCustomTask.dll to a suitable location Open Enterprise Manager and create a New Package From the Task menu select Register Custom Task Supply a suitable description, and browse for the DLL & ICO files to where you placed them in step 1 Create a New global variable from the Package Properties...

databasejournal.com databasejournal.com

DTS How to...dynamically change...Tasks

4 How to dynamically change...Tasks I would recommend familiarising yourself with ScriptPkg before reading this article: Dynamically change package properties utilising ScriptPkg This article will focus on the more complex process of changing Task properties, but for an explanation and examples of changing connection properties please see: How to dynamically change...Connections On with the article.. Each package contains a...

databasejournal.com databasejournal.com

File Information

As part of the system documentation I maintain about my main SQL Server I wanted to a report detailing the locations of all the database files. It also includes all of the options you can set when creating the DB, including log files. I also use it to help monitor growth over time. This is an Excel Workbook (97) designed to provide a formatted report, detailing information about all of the database files on your server. It uses...

  • Darren Green
    D
  • John Percival
    J
1 min read
databasejournal.com databasejournal.com

Text File Connection file name

One problem with a source or destination Text File connection, is that your file name can change. Editing the package every time you run it can be tedious and slow. The scripts below demonstrate how to change the file name using an ActiveX script. Example 1: Changing the file name to a date derived value Dim oPKG Dim cn Set oPKG = DTSGlobalVariables.Parent Set cn = oPKG.Connections("Text File (Source)") ...

databasejournal.com databasejournal.com

DTSBrowse Utility (VB)

This small application allows you to list the packages available on your server. You can add/edit/delete Global variables within a package as well as executing it. I have created several processes in response to user requests that utilise DTS. I wrote this utility to allow users to execute packages themselves. I use a Global Variable to supply the filename of the user file, so it makes life easier if they can change this...