Vishal Monpara’s Blog

There is no straight way in DTS to stop the package on a certain condition without having it fail. Suppose in DTS package is suppose to be executed everyday at 8:00AM. This package is looking for a file c:\abc.txt. If the file has not arrived, it should gracefully stop without reporting any error. If the file has arrived then process the file and report success. This situation is handled by a bit of programming. Write down the following code in ActiveX Script Task.

Dim fso, file1Dim pkgSET pkg = DTSGlobalVariables.Parent

Set fso = CreateObject("Scripting.FileSystemObject")

IF (fso.FileExists("c:\abc.txt")) THEN	Msgbox "File exist. Continue"	pkg.Steps("DTSStep_DTSActiveScriptTask_2").DisableStep=False

	Set fso= Nothing

ELSE	Msgbox "Stop"	pkg.Steps("DTSStep_DTSActiveScriptTask_2").DisableStep=True END IFset pkg=nothingMain = DTSTaskExecResult_Success

What this does is disables the second step and hence the whole sequence of task is automatically disabled. so the Task1 will report success and DTS package execution will be completed without executing task 2 and onwards. You can download the dts package example and try to run it on your computer. It looks for file c:\abc.txt. If this file exists, it will run total 3 task with result SUCCESS. If the file is not found, it will run only Task1 with result SUCCESS.

CREATE FUNCTION [dbo].[fn_FileExists] (@FileName nvarchar(255))
RETURNS bit AS
BEGIN
	DECLARE @FS int
	DECLARE @FileID int
	DECLARE @OLEResult int
	declare @Exists bit

	EXECUTE @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FS OUT
	IF @OLEResult <> 0
	BEGIN
		RETURN 0
	END

	EXECUTE @OLEResult = sp_OAMethod @FS, 'FileExists', @Exists OUT, @FileName

	IF @OLEResult <> 0
	BEGIN
		return 0
	END
	EXECUTE @OLEResult = sp_OADestroy @FS

	return @Exists
END

Important things to remember.

1. Existence of the file will be checked on the server in which it is created. If you are running this stored procedure on remote machine, it will check for file on remote machine and not on local machine.
2. To call a function, you should write

   if dbo.fn_FileExists ('Filename.txt') = 1
   	print 'exist'

   Here “dbo.” must be appended to call the function, otherwise it will give error.

There are many times a situation arises when we have to upload data from excel datasheet to SQL Server and the data in the sheet is not normalized. At that time, we need to make more than one record from a single record. Suppose you have a sheet in which each row in spreasheet shows particular order and items in it. Now at the time of uploading data you want to split each order record into a separate record and have each item from that order belonging to separate row in the new transformation. In this example, for simplicity, I have source and destination as excel spreadsheet. Here is the ActiveX Script code.

Function Main()
   
   do while 1=1
	DTSGlobalVariables("gCounter") = CLng(DTSGlobalVariables("gCounter")) + 1
	select case CLng(DTSGlobalVariables("gCounter"))
	case 1
		DTSDestination("ID") = DTSSource("OrderID")
		DTSDestination("Item") = DTSSource("Item1")
	case 2
		DTSDestination("ID") = DTSSource("OrderID")
		DTSDestination("Item") = DTSSource("Item2")
	case 3
		DTSDestination("ID") = DTSSource("OrderID")
		DTSDestination("Item") = DTSSource("Item3")
	end select

	if DTSGlobalVariables("gCounter") < 3 then
		Main = DTSTransformStat_SkipFetch
		Exit do
	else
		DTSGlobalVariables("gCounter") = 0
		Main = DTSTransformStat_OK
		Exit do
	end if
loop
End Function

I expect that you know simple transformation of data. Here what I did is created one ActiveX transformation which has 4 source columns and transformed into 2 destination columns. This program already knows that 1 row of spreadsheet needs to be split in 3 rows. One global variable “gCounter” is used which keeps track of how many records are splitted from a single record and if all the records are generated, fetch next record. Here above function goes in infinite loop as you can see in the while loop condition. Initially value of global variable is 0. When the first record is fetched, gCounter becomes 1. Now destination fields are populated with source record. Now function is exited with value “DTSTransformStat_SkipFetch”. This value prohibits the function from fetching next record while inserting the new record in destination with the values entered in destination fields. So our first record is inserted in the destination. Now source record pointer still points to the first record, but when the above function is called, it will get the second value from the source and inserts it in destination. Again the return value of function is “DTSTransformStat_SkipFetch”. This process repeats until we generated enough number of records to end the loop. When the loop ends, the return value would be “DTSTransformStat_OK”. This value will cause the program to fetch new record from the source. You may download source code by clicking on DTS Split Record Example. Please unzip the file in C:\ and open the DTS package DTS_Split_Record.dts from SQL Server Enterprise Manager.

Lets say you have data in one format(It can be a database/file/excel spreadsheet also). You want to convert it into another format. You will have a thought of writing a VB program and convert the source file into the format of destination file. But wait a minute. If you have lots of files in the different format and you want to convert it into different formats, then would you write VB programs for each conversion? You can write, but it is very time consuming and error prone.

Here comes into picture the DTS. As name suggests, it is a service for transforming data from one format to another. We can draw a workflow diagram of the data and SQL server Enterprise Manager will automatically create code to execute it. Here we need not have knowledge of how to open a file in VB, how to manipulate data, and how to store in the destination. DTS package handles all these intricacies automatically. Suppose you have excel file in which user data is stored. Now you want to put only certain data (for ex. all persons whose email address ends with yahoo.com) to SQL Server. In DTS, you have to drag excel sheet object, SQL Server object and make them point to appropriate source and destination. Put an arrow pointing from excel sheet to SQL Server. An arrow represents transformation of data from one form to another. By default, it transforms all data. We can create a custom tranformation and depending on the condition, we can insert data into SQL Server.

So the ease with DTS is, if we have multiple transformation, it would be very easy for a programmer to transform data from one form to another without lots of coding.

So lets start using DTS with a simple example. You have excel file and you want to upload it in SQL Server. So here are the steps.

1. Enterprise Manager > Microsoft SQL Servers > [SQL Server Group] > [SQL Server] > DAta Transformation Services > Local Packages.
2. Right click on it and choose “New Package”
3. On the left hand side, you can see “Connection” and “Task”. Connection represents source and destination of data. Task represents different tasks to convert the data from one form to another
4. In “Connection” 3rd button is Excel. Click on it. It will open a dialog box. Provide the name of the new connection and browse the source excel file and put the path in “File Name”.
5. In “Connection” 1st icon represents SQL Server. Click on it and provide valid credentials and select appropriate database.
6. In “Task” menu 3rd button is “Transform Data Task”. Now click on Excel icon which will become source. Click on SQL Server icon. It will become destination.
7. Double click on the arrow. It will open up property which is important to transform Excel data into SQL data.
8. Select appropriate sheet in Source tab. In Destination tab, select appropriate table name. For our case it is “Item”.
9. You can see one to one mapping from source field to destination fields. Now press “OK”.
10. In the main menu, go to “Package” > Execute. This command starts executing the package. Now if you open the table, you will see the data.

You can download the whole dts package by clicking on DTS Package Example. Please save the package in c:\. From enterprise manager, right click on “Data Transformation Services” and choose “Open Package”, browset c:\DTS\DTSExample.dts. Once you open it, double click on SQL Server icon and change the credentials. Run the script
DTS.sql to create a table “Item”.