Importing New Data Without Emptying Existing Tables
The case study I am about to describe has evolved over the past two years
and is still changing as I learn new techniques.
Problem
Soon after I was given the position of SQL Programmer at bigdough.com I was
put in charge of all the DTS packages on our SQL Servers.
Over time I discovered the following problems with our main DTS package
which imports stock ownership data into our databases.
First, it seemed to be designed to be run manually with no fail safes nor
checks to verify that everything ran correctly.
Second, it emptied the tables before pumping the new data into them every
night. These were problems as
our customer base outside the United States was growing and we would get
complaints from those customers that they couldn’t see any data.
I remember distinctly getting a complaint from a customer in Japan.
Also during this time period we got a new DBA who wanted all our DTS
packages to be as dynamic as possible to take into account changes in the
existing databases without having to change the DTS packages every time.
When we switched data providers we had to change our import process to
handle a full update or a daily update depending on what we received on a
given day; The previous
provider simply provided a full update every night.
Business Challenge
The challenge for me was to devise a way to keep the data available at all
times while importing the new data, detect if a full or daily update was
received and run appropriate data pumps, put in sufficient fail safes to
ensure bad data would not get imported, and to make the process automatic
including notification to pagers upon failure.
Solution
Keeping The Data Available
The first thing I tackled was to keep the data available at all times.
I adjusted the DTS package to create tables identical to the existing tables
and added “_New” to the end of the table name.
This piece had to be dynamic in the sense that if new indexes were added to
the original table the DTS package needed to be able to handle this without
changing the DTS package. I
went to the system tables and found where info about tables, primary keys,
and indexes were stored and created a stored procedure that would script
tables with or without primary key and indexes.
The SP can also script the primary key and/or the indexes without the table.
The SP is available in the script section and can be viewed by clicking on
its name: http://www.sqlservercentral.com/scripts/contributions/543.asp
sp_CreateAndExecTableScript.
With this SP I create most of the tables with all their indexes except for
the ones that have nearly a million rows.
The data pump tasks go faster without the indexes and the placement of the
indexes after the data is in place takes about 2.5 minutes.
After the data is imported into the new tables I run the stored procedure
sp_rename multiple times to rename the existing tables with the extension
“_old”. Then I rename all the
tables with the extension
“_New” to the original table name.
All these rename commands are encased by a BEGIN/COMMIT TRANSACTION with a
ROLLBACK being issued if one of the rename commands fails.
This is an all or nothing transaction to avoid having old data in one table
live at the same time as new data in a different table.
If this step fails a failure e-mail is immediately sent to my cell phone
which is always beside my bed.
The phone beeps until I look at the e-mail.
After testing this we have found that data is always available.
Whoever is logged on during the import and/or swapping of tables will only
experience a slight slowness on the site.
This slowness was reduced when we upgraded from SQL Server 7.0 to SQL Server
2000.
Importing Full or Daily Update
When we changed data providers we had to change our DTS package so it could
detect if we received a full or daily update and make it run the appropriate
data pumps and other SQL tasks.
All files associated with the daily update have the word daily in front of
their name. Those for a full
update don’t. The DTS package
has a data pump for each file in a full update and each file in a daily
update.
To control which data pumps get executed I created two SQL Execute Tasks.
One is called 1st Gateway Daily Update and the other is called 1st Gateway
Full Update. The only code in
the task is a remark stating the purpose of the task.
All data pumps for the daily update can’t execute until the SQL task called
1st Gateway Daily Update succeeds.
All data pumps for the full update can’t execute until the SQL task called
1st Gateway Full Update succeeds.
I then created an ActiveX Script Task with a VBScript to detect which files
were received. If the files for
a daily update are received then the VBScript marks the SQL Execute Task
called 1st Gateway Full Update as completed.
This is interpreted by the DTS package as a failure and so none of the data
pumps for the full update are executed.
The task called 1st Gateway Daily Update is marked as completed when a full
update is received thus preventing those data pumps from executing.
There are other tasks after the appropriate data pumps execute that must
run, however they can’t run until the needed data pumps succeed.
To make sure they would run after the data pumps I created two more SQL
Execute Tasks similar to those described above and simply changed “1st”
to “2nd”. The 2nd
Gateway Daily Update can’t execute until all the daily update data pumps
succeed and the 2nd full update task can’t execute until the full
update data pumps succeed. All
tasks that follow can’t execute until both the 2nd gateway tasks
have completed.
So, if a daily update is received the ActiveX Script Task will mark both
full update gateways as completed.
This keeps all the full update data pumps from executing and makes sure the
tasks that must run after the daily data pumps succeed wait for the data
pumps. Now the DTS Package can
handle a full or daily update.
This method was used to avoid a failure entry for each data pump that didn’t
find a file. Half of the data
pumps would fail and be logged as failed in the error log.
This method reduces the number of known failures from about 10 to 2.
One for each task marked as completed.
Here is a shortened version of the VBScript that does what I described in
this section:
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main()
' Specify the OLE DB provider.
set DATABASENAME = CreateObject("ADODB.Connection")
DATABASENAME.Provider = "sqloledb"
' Specify connection string on Open method.
ProvStr = "Server=SERVERNAME;Database=DATABASENAME;UID=SQLSERVERLOGIN;PWD=PASSWORD"
DATABASENAME.Open provStr
'Create temporary table
query = "CREATE TABLE #fileexists (doesexist smallint, fileisdir smallint, direxist smallint, txtfilename varchar(45) NULL default 'unknown')"
DATABASENAME.Execute(query)
query = "INSERT INTO #fileexists (doesexist, fileisdir, direxist) EXEC master..xp_fileexist 'D:\TextFilesForImport\dailyupdate.txt' UPDATE #fileexists SET txtfilename = 'dailyupdate.txt' WHERE txtfilename = 'unknown'"
DATABASENAME.Execute(query)
query = "INSERT INTO #fileexists (doesexist, fileisdir, direxist) EXEC master..xp_fileexist 'D:\TextFilesForImport\fullupdate.txt' UPDATE #fileexists SET txtfilename = 'fullupdate.txt' WHERE txtfilename = 'unknown'"
DATABASENAME.Execute(query)
'Start finding UpdateType
'1 indicates Full Update. 0 or 2 indicates at least one file missing. 3 indicates Daily Update.
UpdateTypeQuery = "SELECT doesexist AS UpdateType FROM #fileexists WHERE txtfilename = 'fullupdate.txt'"
set LocalImportType = DATABASENAME.execute(UpdateTypeQuery)
DTSGlobalvariables("UpdateType").value = LocalImportType("UpdateType")
'enters this if when file name institution.txt is not found
If DTSGlobalvariables("UpdateType").Value = 0 Then
UpdateTypeQuery = "SELECT doesexist + 2 AS UpdateType FROM #fileexists WHERE txtfilename = 'dailyupdate.txt'"
set LocalImportType = DATABASENAME.execute(UpdateTypeQuery)
DTSGlobalvariables("UpdateType").value = LocalImportType("UpdateType")
End If
'End finding UpdateType
query = "DROP TABLE #fileexists"
DATABASENAME.Execute(query)
Dim oPackage
Set oPackage = DTSGlobalVariables.Parent
'This if handles daily updates. It marks the Gateway Full Update steps as completed.
If DTSGlobalvariables("UpdateType").Value = 3 Then
'Mark 1st Gateway Full Update step as completed
oPackage.Steps("DTSStep_DTSExecuteSQLTask_4").ExecutionStatus = DTSStepExecStat_Completed
'Mark 2nd Gateway Full Update step as completed
oPackage.Steps("DTSStep_DTSActiveScriptTask_7").ExecutionStatus = DTSStepExecStat_Completed
End If
'This if handles full updates. It marks the Gateway Daily Update steps as completed.
If DTSGlobalvariables("UpdateType").Value = 1 Then
'Mark 1st Gateway Daily Update step as completed.
oPackage.Steps("DTSStep_DTSExecuteSQLTask_1").ExecutionStatus = DTSStepExecStat_Completed
'Mark 2nd Gateway Daily Update step as completed.
oPackage.Steps("DTSStep_DTSActiveScriptTask_6").ExecutionStatus = DTSStepExecStat_Completed
End If
Main = DTSTaskExecResult_Success
End FunctionAvoiding False Failure Alerts
The above solution created a new problem.
The job that executes the DTS package will always complete reporting failure
because of the tasks that get marked as completed.
To avoid this I modified the job to always report success even upon failure.
That avoids the false failure alert.
Unfortunately, the job will no longer alert me when there really is a
failure.
For that I created a table where the DTS package will log when it started
and when it finished. Then I
created a separate job that will count the number of times the job logged a
start for the day and how many times it logged a finish for the same day.
If the numbers aren’t the same then an e-mail gets sent to my cell phone to
wake me up and I groggily power up my computer to investigate the problem.
Fail Safes
We discovered a problem one day when all the expected data was not on our
production servers and wondered why we didn’t receive an e-mail to alert us
of a problem. Upon
investigation we discovered that we had received all the required files in
the proper format. The problem
was that the vendor had sent one or two of the files with less than half the
normal number of rows. Nothing
in the DTS package was designed to handle this problem since the few rows
received were correct and so the data pumps moved them to their respective
tables.
The fail safe I designed for this eventuality was to create an SQL Execute
Task that would count the number of rows in each table.
If the row count fell below a minimum level then the task would send an
e-mail to my cell phone and stop the job so that no more tasks were
executed. I placed this task
after the data pump tasks to the new tables and before the task that renames
all the tables to make the new data available on our web site.
Thus if there are not enough rows in any of the tables the new data will not
be made live.
Another problem we faced was when no new files were available the import DTS
package would still run and send an e-mail to tell us it had succeeded.
It had succeeded in importing yesterday’s data.
This was not acceptable. I
developed the following VBScript and placed it in an ActiveX Script task:
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
'This task checks the file creation date for all the text
'files in the zip file and if not today then it creates a directory
'and moves the file to that directory and then stops the
'DTS Package.
Function Main()
Dim TodayDate
Dim FullUpdateDate
Dim DailyUpdateDate
TodayDate = Date
Dim objFSO, objFolder, objFile, colFiles, objFSO2
Set objFSO2 = CreateObject("Scripting.FileSystemObject")
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(DTSGlobalVariables("gvFilePath").Value)
Set colFiles = objFolder.Files
'delete folder WrongDate if exists
If objFSO.FolderExists(ObjFolder&"\WrongDate") Then
objFSO.DeleteFolder(ObjFolder&"\WrongDate")
End If
'begin checking dates on LionShares text files and move old files to WrongDate folder
If colFiles.Count > 0 Then
For Each objFile in colFiles
'checks dates for all files
IF UCase(objFile.Name) = "DAILYUPDATE.TXT" or UCase(objFile.Name) = "FULLUPDATE.TXT" _
or UCase(objFile.Name) = "FUNDS.TXT" or UCase(objFile.Name) = "HOLDINGS.TXT" _
or UCase(objFile.Name) = "STOCKS.TXT" or UCase(objFile.Name) = "ANALYTICS.TXT" _
or UCase(objFile.Name) = "FUNDANALYTICS.TXT" Then
DailyUpdateDate = TRIM(LEFT(objFile.DateLastModified, instr(objFile.DateLastModified, " ")))
'the below line is for debugging purposes.
'MsgBox "!" & DailyUpdateDate & "!" & TodayDate & "!"
IF TRIM(TodayDate) <> DailyUpdateDate Then
If objFSO.FolderExists(ObjFolder&"\WrongDate") Then
objFile.Move(objFolder&"\WrongDate\")
Else
objFSO.CreateFolder(objFolder&"\WrongDate")
objFile.Move(objFolder&"\WrongDate\")
End If
End If
End If
Next
End If
'end checking dates on LionShares zip files and move old files to WrongDate folder
Set objFSO = nothing
Set objFolder = nothing
Set colFiles = nothing
Main = DTSTaskExecResult_Success
End FunctionThis script will compare the date of each file with today’s date and if the
date is not the same then the text file will be moved to a different
directory. Now we can just let
the DTS package run. If no
files are left where the data pump tasks can find them then the entire
package will halt and the process described in the section called
Avoiding False Failure Alerts will send out an e-mail to my cell phone
since the package will have logged a start time but no finish time.
The reason we will let the package continue at this point is because we
don’t know if we are receiving a daily or a full update.
Since we’ll only receive one or the other then the one that was not received
today will have the wrong date and thus get moved to the WrongDate folder
before the task executes that determines if we have a full or daily update.
Benefits
We now have a DTS package that will not adversely affect our customers and
that will let everyone who monitors them sleep knowing that if something
really goes wrong a wakeup e-mail will be sent to our cell phones and pagers
to alert us of the problem. We
now have fewer problems with the import and keep incomplete or bad data off
our web site. We also have
eliminated complaints that say there is no data to be seen in the areas of
our web site effected by this DTS package.
Summary
In this case study I have explained how to keep data available while new
data is being imported and some of the fail safes that help automate the
import process. I have also
described one method for handling full and daily updates in the same DTS
package while minimizing the number of failures recorded in error logs.
