Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Feb 27, 2004

SQL Server 2000 DTS Part 10 - DTS Designer Tasks - The Data Driven Query Task - Page 2

By Marcin Policht

Now it is time to present functionality of the described above components in a very simple example. Our data source will comprise of a text file containing entries to be used in order to modify content of the Shippers table in the Northwind database, in the following, semicolon-separated format:

Update;1;Super Speedy Express;(503) 555-9831
Update;2;Offshored Package;91 (123) 555-3199
Insert;4;Road Runner;(503) 555-1234
Insert;5;Speedy Gonzalez;(503) 555-4321
Delete;4;Road Runner;(503) 555-1234

Copy the five rows listed above to a text file and save it, (location and name are not relevant). As you can easily guess, the first field indicates the type of change to be applied, the second is the record (row) identifier, and the last two (Company Name and Company Phone) are used to create a new entry or update an existing one (they are obviously irrelevant in case of deletion). The row number 4 will be inserted and then shortly after deleted (purely for demonstration purposes). Note that we cannot simply delete already existing three Shippers rows because of the referential integrity rules. After the DTS task is completed, the first two rows should be modified and the fourth row should be added (with ShipperID identity column value set to 5).

We will use Shippers as our binding (as well as target) table. Since source and binding tables require separate connections, we will start by defining them. The first one will be of Text File (Source) type (you can locate it in the Connection menu or toolbar of the DTS Designer window). Specify the name of the file you just saved, make sure that you select Delimited file format, and choose semicolon as the column delimiter. Our target connection will be based on standard Microsoft OLE DB Provider for SQL Server pointing to the Northwind database on your test SQL Server.

Once connections are created, select the Data Driven Query task from the Task menu of the DTS Designer (or the toolbar area). Make sure that the Text File (Source) connection is listed on the Source tab (you can use the Preview button to verify that our sample data is properly interpreted). On the Bindings tab, choose the Northwind database connection and Shippers table. Delete default transformation on the Transformation tab. Click on the New button to define a new one and choose the ActiveX Script entry from the Create New Transformation dialog box. This will trigger display of the Transformation Options dialog box. On the Source Columns tab, select all columns (Col001 to Col004) and verify that the Binding Columns tab contains all entries from the binding table. On the General tab, click on the Properties button, which will display the following, automatically generated transformation script :

Function Main()
	DTSDestination("ShipperID") = DTSSource("Col001")
	DTSDestination("CompanyName") = DTSSource("Col002")
	DTSDestination("Phone") = DTSSource("Col003")
	Main = DTSTransformstat_InsertQuery
End Function

Replace it with the custom one, which will take into consideration the first column to determine the type of query to be invoked:

Function Main()
	Select Case UCase(Trim(DTSSource("Col001")))
		Case "UPDATE"
			DTSDestination("ShipperID") = DTSSource("Col002")
			DTSDestination("CompanyName") = DTSSource("Col003")
			DTSDestination("Phone") = DTSSource("Col004")
			Main = DTSTransformstat_UpdateQuery
		Case "INSERT"
			DTSDestination("ShipperID") = DTSSource("Col002")
			DTSDestination("CompanyName") = DTSSource("Col003")
			DTSDestination("Phone") = DTSSource("Col004")
			Main = DTSTransformstat_InsertQuery
		Case "DELETE"
			DTSDestination("ShipperID") = DTSSource("Col002")
			Main = DTSTransformstat_DeleteQuery
		Case ELSE
			Main = DTSTransformstat_UserQuery			
	End Select       
End Function

Confirm your changes by clicking on the OK buttons and return to the Data Driven Query Task Properties dialog box. On the queries tab, fill out the content of Insert, Update, and Delete query types with the following TSQL statements (one per type):

INSERT Shippers ([CompanyName],[Phone])
VALUES (?, ?)

UPDATE Shippers
SET CompanyName = ?, Phone = ?
WHERE (ShipperID = ?)

WHERE ShipperID = ?

For each statement, click on the Parse/Show parameters and verify that each parameter corresponds to an appropriate column from the target table (Shippers). Default mapping between parameters and destination columns can be modified by clicking on individual destination columns in the lower portion of the dialog box (this will be necessary for the Insert and Update statements). Finally, click on OK to close the Data Driven Query Task Properties dialog box, save your package, and execute it. You can verify that the content of the Shippers table has changed as expected using standard methods (Query Analyzer or Enterprise Manager):

1	Super Speedy Express		(503) 555-9831	
2	Offshored Package		91 (123) 555-3199	
3	Federal Shipping		(503) 555-9931	
5	Speedy Gonzalez		(503) 555-4321	

This simple exercise should give you the basis for creating your own packages including Data Driven Query task. For other examples, refer to the Books Online. We will complete our coverage of DTS tasks in the next article.

» See All Articles by Columnist Marcin Policht

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM