 |
Make the Execute Package Task name dependent only
|
| Total Hit (756) |
By default the Execute Package Task will use the PackageID (GUID) to load and execute a package. The PackageID is assigned when you save a new package. A simple illustration of this is to open a package and then use Save As, giving the package a new name. If you where to replace the target or child
....Read More |
Rating
 |
|
|
|
How to manipulate the Execute Process task.
|
| Total Hit (807) |
Here is some code showing how we can dynamically manipulate the Execute Process task. Say we have an executable "c:\PassFile.exe" and it accepts one argument /F which is for a file. We want to be able to pass in whatever file we choose and we also want to be able to set some of the properties of the
....Read More |
| Rating
|
|
|
|
|
|
Changing the DTS Send Mail Task
|
| Total Hit (726) |
This is a simple example of using an ActiveX Script Task to dynamically change the main properties of a DTS Send Mail task. First set the message subject to the same name as the package. The body of the message is a combination of a static string and a global variable value. Finally we read the file
....Read More |
| Rating
|
|
|
|
How can I change the source data file for a Bulk Insert Task
|
| Total Hit (610) |
To change the name of the source data file at runtime you can use an ActiveX Script Task. In the following example the new filename is read from a global variable called ImportFilename. Please ensure you change the bulk insert task's name value ("DTSTask_DTSBulkInsertTask_1") to match that in your p
....Read More |
| Rating
|
|
|
|
Global Variables and SQL statements in DTS
|
| Total Hit (778) |
For those fortunate enough to have SQL 2000, using global variables with an Execute SQL Task, or as part of the 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. Even if you ar
....Read More |
| Rating
|
|
|
|
Changing the DataPump Source and Destination Tables
|
| Total Hit (1086) |
You can use an ActiveX Script to change both the source and destination of the DataPump task.
The first thing you need is the Name of the task. Right-click the DataPump task and select Workflow Properties. On the options tab it shows you the step name. The task name is almost guaranteed to be th
....Read More |
| Rating
|
|
|
|
How to export all tables in a database
|
| Total Hit (765) |
This is a very short article as it is really a demonstration of concepts explained elsewhere, although quite a useful implementation in it's own right. The export itself is done with the Bulk Export Task, and this is wrapped in a recordset driven loop as covered in the article How to loop through a
....Read More |
| Rating
|
|
|
|
How to loop through a global variable Rowset
|
| Total Hit (843) |
The Execute SQL Task for SQL Server 2000 allows both input and parameters to be specified. In this article we will be focusing on the Rowset output parameter functionality, and what we can then do with the Recordset it returns. The Rowset parameter takes the result set of a SELECT statement and stor
....Read More |
| Rating
|
|
|
|
Processing The Same Row More Than Once
|
| Total Hit (759) |
In his SQL Server Magazine article of August 2002 Itzik Ben-Gan shows us how to use Transact SQL to normalise lab test results from a fictional wine maker using either functions in SQL Server 2000 or Transact SQL gymnastics in Version 7. In this article we're going to show you how to do the exact sa
....Read More |
| Rating
|
|
|
|
Formatting Character Data into Datetime fields
|
| Total Hit (637) |
When building tables people quite often use character fields to hold dates. A style that is quite often used to format the date is this yyyymmdd. When we come to take this data into a field of say smalldatetime then we can encounter errors.
|
| Rating
|
|
|
|
Looping, Importing and Archiving
|
| Total Hit (668) |
One of the most common things asked for by people using DTS is the ability to loop through a directory and process files along the way. This is how I do it. Although it may look a little complicated for what is quite an easy task it isn't trust me.
|
| Rating
|
|
|
|
Multiple Paths in Workflow
|
| Total Hit (971) |
Branching workflow is a common requirement, and is quite easy to achieve using the basic On Success or On Failure constraints available through the designer. However branches go in different directions and do not meet up again.
|
| Rating
|
|
|
|
Skip the execution of an individual Task
|
| Total Hit (674) |
It is easy to build a nice linear workflow path using the DTS designer, but unfortunately our processes are not always that simple. In certain circumstances you may wish to skip a task. This is where the power of workflow can be used, but unfortunately it is not available graphically through the des
....Read More |
| Rating
|
|
|
|
How can I check if a file exists?
|
| Total Hit (663) |
Checking for the existence of a file before trying to import it can be very useful in ensuring your systems run smoothly. You can use the Scripting FileSystemObject to check for the presence of a file using ActiveX Script. If the file exists you can obviously import it or initiate an alternative pro
....Read More |
| Rating
|
|
|
|
Tutorial : Problems With IDENTITY() and the DataPump task.
|
| Total Hit (722) |
Just recently we have seen a number of posts in the newsgroups concerning problems with IDENTITY columns when inserting data into a table using DTS. This walkthrough should hopefully explain most of the problems we can encounter and give you some useful information about behind the scenes stuff as w
....Read More |
| Rating
|
|
|
|
Tutorial : Write File Transformation
|
| Total Hit (729) |
In this article we will show you how to export a file stored in your SQL Server table out to disk. For this we will need two tables. One will be the table that holds the file and its name and the other is a dummy table that we need when using the WriteFile transformation.
....Read More |
| Rating
|
|
|
|
Tutorial : Introduction to Workflow
|
| Total Hit (707) |
This article will give you a quick overview of the three types of workflow precedence constraints available and also highlight a couple of common problems associated with workflow. The most basic feature workflow gives you is the ability to control in which order your tasks will be executed. If all
....Read More |
| Rating
|
|
|
|
|
|
Tutorial : Introduction To Global Variables
|
| Total Hit (736) |
Global Variables allow you to make your DTS packages more flexible. Some of their uses include.
1. Storing Values passed around within a package.
2. Accepting values from outside the package to be used inside it.
3. Holding not only single values but multiple values in a recordset.
....Read More |
| Rating
|
|
|
|
Tutorial : A Tour Of ActiveX Script
|
| Total Hit (644) |
One of the ways to gain a lot of Flexibility in DTS is to use an ActiveX script. They can be found in the transform Data Task, the Data Driven Query Task and as a standalone task. When we look at the actual screens there is a lot to see and some of the things can be daunting especially if you have n
....Read More |
| Rating
|
|
|
|
Tutorial : Building a Package in the DTS Designer
|
| Total Hit (711) |
The point of this tutorial is to give us an introduction to the DTS designer. If you're serious about DTS or want to explore more of its many features then it won't be long before you end up here. This tutorial was created using SQL Server 2000 but users of SQL Server 7 will still find it useful. Wh
....Read More |
| Rating
|
|
|
|
Tutorial : Importing A Text File Using The DTS Wizard
|
| Total Hit (687) |
This tutorial came about from watching the Newsgroups and realising that although people had heard of DTS they were unsure as to what it is and where to find it. We wanted to explain DTS to people and give them an understanding of what it does so they can go away and use it themselves.
....Read More |
| Rating
|
|
|
|
Tutorial : How to Use Lookups in DTS
|
| Total Hit (613) |
When you see people in the newsgroups answering a question with a query and the query includes a question mark then you may be forgiven for thinking that the person who is replying doesn't know what value goes in here. Here we present a different answer and yes you guessed it the solution we present
....Read More |
| Rating
|
|
|
|
Execute a package from a package
|
| Total Hit (599) |
You can use a parent or master package to control the execution of one or more child packages. This is often used in a looping scenario, or when there are a number of individual packages that make up a process.
This can be achieved very simply in SQL Server 2000 by using the Execute Package Task
....Read More |
| Rating
|
|
|
|
|
|
DTS Global Variables and Visual Basic .Net
|
| Total Hit (706) |
1. In order to change the value of a DTS Global Variable of type String from VB.Net code, the variable must be programmatically removed from the collection and added back with its new value.
2. In order to change the value of a DTS Global Variable of type Date, the new value must be explictly cas
....Read More |
| Rating
|
|
|
|
Execute a package from Visual Basic (VB)
|
| Total Hit (697) |
To execute a package from Visual Basic is a relatively simple task using the DTS object model. Before you start using the object model you must add a the appropriate reference to your project. From the Project menu select References and check the "Microsoft DTSPackage Object Library".
....Read More |
| Rating
|
|
|
|
Global Variables and Stored Procedure Parameters
|
| Total Hit (603) |
One of the benefits of the SQL Server 2000 is that the SQL tasks support mapping of global variables to parameter placeholders within the SQL. This means you can use the global variable as an input parameter for your stored procedure.
|
| Rating
|
|
|
|
Bulk Export Task
|
| Total Hit (677) |
The DTS Bulk Export Task was originally conceived in response to a problem with the OLE-DB provider for text files. For more details see DataPump truncates delimited fields to 255 characters. Whilst this problem can be fixed quite easily the Bulk Export Task still offers some benefits over the DataP
....Read More |
| Rating
|
|