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

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Feb 18, 2001

Data Transformation Services (DTS)

By Darren Green

Overview

Lookups are a very powerful weapon in your DTS arsenal, with more uses than simply looking a single value. In this article I will try and cover the different uses I have come up with. I will avoid covering too much of the basics of what a lookup is since you already have Books Online to do this.

I have created a sample process loosely based on loading data between hospital systems. Your raw data is episodes of care for a patient. The download contains the raw data files (EpisodeData1.csv, EpisodeData2.csv), a script to create the database objects (DTSLookup_EpisodeSampleObjects.sql), and a DTS package that demonstrates the methods shown below. You will notice that I have used a separate connection for the lookups, as this improves performance.

ActiveX Script Transformation:        Lookup T-SQL:    

Raw Data

EpisodeData1.csv
EpisodeIDPatientIDLocalSpecialtyStartDateEndDateLengthOfStay
123456785636251220000112200001143
5687894557345612200002012000011515
245987565636252220000405200001062
1254789925875622200006042000061114
1254786625875622 2000061114
365478952587562220002406200024070
969887892546881220000701 0

EpisodeData2.csv
EpisodeIDPatientIDLocalSpecialtyStartDateEndDateLengthOfStay
969887892546881220000701200007022

Simple Lookup

Just for completeness we will start with a simple lookup, which returns one value. In our sample we use this to transform our treatment specialty from a local code into a nationally recognised code.

' TransformSpecialty - Simple Lookup 
' (Assign to variable for later use)
iNationalSpecialty = DTSLookups("TransformSpecialty").Execute(DTSSource("LocalSpecialty").Value)
DTSDestination("NationalSpecialty") = iNationalSpecialty
SELECT [National]
FROM LocalSpecialty
WHERE (Local = ?)

Multiple Arguments & Multiple Values

This example is slightly more advanced in that it requires two arguments to return the result, which itself consists of two values. The multiple arguments is quite obvious, however the multiple result values are slightly more complicated as it they are returned in an array. Another feature we have used in this lookup is the ability to call stored procedures as well a the usual full SQL statement. In our sample we use this derive a resource code and high level banding for an episode of care. The resource is obviously related to the length of stay in hospital, but is also affected by the specialty.

' ResourceAllocation - Multi-Argument, Multi-Value
aResourceValues = DTSLookups("ResourceAllocation").Execute(iNationalSpecialty, _
	DTSSource("LengthOfStay").Value)
DTSDestination("ResourceCode").Value = CStr(aResourceValues(0))
DTSDestination("ResourceLevel").Value = aResourceValues(1)
spDTSLookup_MultiDemo ?, ?

Writing Data

Lookups aren't restricted to SELECT statements, and in this example we write episodes that have invalid dates to a log table for further investigation. The date validation requires that a valid episode start date is present and either a valid end date or no end date, which denotes an unfinished episode. In addition the the source data has dates in yyyymmdd format, which is not a valid VBScript date format, so we use our own function to convert them into yyyy-mm-dd.

' Check Dates for valid StartDate, and valid EndDate, or no EndDate
If (IsDate(DBDate(DTSSource("StartDate"))) And IsDate(DBDate(DTSSource("EndDate")))) _
  Or (IsDate(DBDate(DTSSource("StartDate"))) And IsNull(DTSSource("EndDate"))) Then

...

Main = DTSTransformStat_OK
Else
	DTSLookUps("LogError").Execute DTSSource("EpisodeID"), DTSSource("PatientID")
	Main = DTSTransformStat_SkipInsert
End If

...
Function DBDate(sDate)
	If Len(sDate) = 8 And IsNumeric(sDate) Then
		DBDate = Left(sDate, 4) & "-" & Mid(sDate, 5, 2) & "-" & Right(sDate, 2)
	ElseIf IsNull(sDate) Then
		DBDate = Null
	Else
		DBDate = "InvalidFormat"
	End If
End Function	
INSERT INTO EpisodeLoadLog (EpisodeID, Error)
VALUES (?, 'Date validation error (PatientID=' + ? + ')')

Deleting Data

The final example deletes data. Taking the concept of unfinished episodes outlined above, we would expect to get those previously unfinished episodes as completed ones in the next load. Remember in the initial load we did not have enough information to allocate our resource information correctly as the length of stay was 0. In this scenario we will assume that we should only get "duplicate rows" for episodes that have now closed, so we use the lookup to delete any existing rows for the specified episode, if they have a NULL end date.

' DeleteUnfinishedEpisode - Deletes an existing episode if it has a no EndDate
DTSLookups("DeleteUnfinishedEpisode").Execute DTSSource("EpisodeID")
DELETE FROM PatientEpisodes
WHERE (EpisodeID = ?) AND (EndDate IS NULL)

The Transformation

If we now take all of the code and put it together in a logical manner, we get the following ActiveX Transformation Script:

Function Main()
	Dim iNationalSpecialty, aResourceValues

	' Check Dates for valid StartDate, and valid EndDate, or no EndDate
	If (IsDate(DBDate(DTSSource("StartDate"))) And IsDate(DBDate(DTSSource("EndDate")))) _
	  Or (IsDate(DBDate(DTSSource("StartDate"))) And IsNull(DTSSource("EndDate"))) Then

		' DeleteUnfinishedEpisode - Deletes an existing episode if it has a no EndDate
		DTSLookups("DeleteUnfinishedEpisode").Execute DTSSource("EpisodeID")

		DTSDestination("StartDate") = DBDate(DTSSource("StartDate"))
		DTSDestination("EndDate") = DBDate(DTSSource("EndDate"))

		' Direct Transfer
		DTSDestination("EpisodeID") = DTSSource("EpisodeID")
		DTSDestination("PatientID") = DTSSource("PatientID")
		DTSDestination("LengthOfStay") = DTSSource("LengthOfStay")

		' TransformSpecialty - Simple Lookup 
		' (Assign to variable for later use)
		iNationalSpecialty = DTSLookups("TransformSpecialty").Execute(_
			DTSSource("LocalSpecialty").Value)
		DTSDestination("NationalSpecialty") = iNationalSpecialty

		' ResourceAllocation - Multi-Argument, Multi-Value
		aResourceValues = DTSLookups("ResourceAllocation").Execute(iNationalSpecialty, _
			DTSSource("LengthOfStay").Value)
		DTSDestination("ResourceCode").Value = aResourceValues(0)
		DTSDestination("ResourceLevel").Value = aResourceValues(1)

		Main = DTSTransformStat_OK
	Else
		DTSLookUps("LogError").Execute DTSSource("EpisodeID"), DTSSource("PatientID")
		Main = DTSTransformStat_SkipInsert
	End If
End Function

Function DBDate(sDate)
	If Len(sDate) = 8 And IsNumeric(sDate) Then
		DBDate = Left(sDate, 4) & "-" & Mid(sDate, 5, 2) & "-" & Right(sDate, 2)
	ElseIf IsNull(sDate) Then
		DBDate = Null
	Else
		DBDate = "InvalidFormat"
	End If
End Function

Running the sample package

To run the sample package on your own server, follow the steps below.

  • Place EpisodeData1.csv in C:\Temp\
  • Run DTSLookup_EpisodeSampleObjects.sql. The package connections point to the master DB of the (local) server, so ensure this where you create the objects, or amend the package as appropriate.
  • Open and execute the package. The package has an owner/operator password of swynk, which ensures it is compatible with SQL 7.0 (SP1/SP2/SP3) and SQL 2000.
  • Now check that you have 5 episodes in PatientEpisodes, and two errors in EpisodeLoadLog. Note that we also have one unfinished episode.
  • To demonstrate the delete in action, place EpisodeData2.csv in C:\Temp\, and amend the Text File (Source) connection to point to it. Re-execute the package.
  • Now check that the unfinished episode has been updated, well actually it was deleted then re-inserted but you get the idea.

    Download the sample - DTSLookup_EpisodeSample.zip



  • MS SQL Archives

    Comment and Contribute

     


    (Maximum characters: 1200). You have characters left.

     

     




    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


















    Thanks for your registration, follow us on our social networks to keep up-to-date