 |
Find an Access Database (from anywhere on the file system)
|
| Total Hit (638) |
It may be that we have built a DTS package to synchronise data held in an Access database and our SQL Server. The Access databases all have a consistent name but due to the fact that they are used by travelling salesmen on their laptops we cannot guarantee where the database will be.
For this we
....Read More |
Rating
 |
|
|
|
Deleting an Excel WorkSheet from within a package
|
| Total Hit (1891) |
Here is a simple and quick way of deleting a worksheet in an Excel spreadsheet. It uses two global variables, one for the Excel file name and one for the sheet to delete. As this uses OLE automation of the Excel application object, you will need Excel installed on any machine that this package execu
....Read More |
| Rating
|
|
|
|
Importing a Named Range From Microsoft Excel
|
| Total Hit (1723) |
On the newsgroups recently there has been a great deal of interest in importing data from Microsoft Excel into SQL Server using DTS. One of the questions that caught my eye was "How do I import values from cells from anywhere on the page?". You can do this using Named Ranges in the Excel spreadsheet
....Read More |
| Rating
|
|
|
|
How can I dynamically set the Access System Database
|
| Total Hit (669) |
You can easily change the filename for an Access database connection using the Dynamic Properties Task in SQL Server 2000. Unfortunately the property selection dialog does not allow access to the system database property (Jet OLEDB:System database) which you need to specify correctly when accessing
....Read More |
| Rating
|
|
|
|
How to check the file date
|
| Total Hit (603) |
This sample script was written to check the age of a file. If you import files according to a schedule, you may wish to check that the file is recent to prevent the potential re-import of an old file. The script reads the filename from an existing package connection and uses the scripting FileSystem
....Read More |
| Rating
|
|
|
|
Connecting to an AS400 with DTS
|
| Total Hit (563) |
DTS does not support AS400 connectivity natively, but by obtaining a third party ODBC or OLE-DB provider you can connect quite easily. In no particular order, some links for suppliers of such providers:
|
| Rating
|
|
|
|
How can I change the filename for a text file connection?
|
| Total Hit (619) |
It is a common scenario to have a scheduled package that imports a new file each day. Conversely you may wish to produce a uniquely named file each day. You can change the filename (DataSource) property of a connection at run-time from within an ActiveX Script Task.
....Read More |
| Rating
|
|
|
|
How can I change the filename for an Access Connection?
|
| Total Hit (696) |
You can change the Access MDB file of a connection at run-time using an ActiveX Script Task. The following example reads the new filename from a global variable and updates the connection called "Accounts".
|
| Rating
|
|
|
|
|
|
Getting Syntax Help for DTSRun
|
| Total Hit (579) |
If you are having problems with the syntax for the dtsrun command line, or just want an easy way of generating a valid command line first time, try using the dtsrunui utility. As the name indicates it is very similar to dtsrun, but with a user interface. This allows you to easily select your package
....Read More |
| Rating
|
|
|
|
Execute a package from a package
|
| Total Hit (598) |
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 (704) |
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 (695) |
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 (600) |
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 (675) |
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
|
|
|
|
Make the Execute Package Task name dependent only
|
| Total Hit (754) |
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 (805) |
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 (724) |
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 (608) |
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 (776) |
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 (1082) |
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 (764) |
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 (840) |
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 (757) |
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 (635) |
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 (667) |
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 (968) |
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 (672) |
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
|
|