Flaw in my DTS design -- sql server dts

Jim Abel
3/25/2005 9:14:55 AM

I need some help with the way I make dts packages that
bring in data from outside data sources. A typical DTS
Package that I've made Deletes the records of a table
then on success connects and pulls down a new set of
records and then on success does and update to another
table from the newly collected records. On occasion I
have an issue where the datasource can not be contacted
and the package stops at that point.

I'm interested to know if anyone hase a better design
that I model so that the first step (Truncate table)
doesn't occur if I can't make the Datasource connection?

NickName
3/25/2005 1:50:07 PM

Three stage approach would be one:
1) preparation for data import;
2) import source data into STAGING area (tables);
3) data cleansing/parsing
and proper logging such as flagging/recording job start and upon
completion flagging/recording job finished
(finished != data import completed 100% correctly, could be due to
inconsistency in source data format or ...), here detailed log comes in
handy).

[quoted text, click to view]

outlander
3/28/2005 2:01:07 PM

I may not understand the problem. If detecting the files existence would
help then I know a way to do that. In some dts I need to be sure the file is
there and that is larger than a certain size. Because it's AIX, in my case,
the file can exist in a directory but with a zero length. sFormatDate is the
string with a file name. I hope it helps....

set opackage = dtsglobalvariables.parent

set objfso = createobject("Scripting.FileSystemObject")

if objfso.fileexists(sFormatDate) then
set objfile = objfso.getfile(sFormatDate)
if objfile.size < 80 then
Main = DTSTaskExecResult_Failure

else
Main = DTSTaskExecResult_Success
end if
else
Main = DTSTaskExecResult_Failure
end if

set objfso = nothing
set objfolder = nothing
set colfiles = nothing

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server dts

sql server dts : Flaw in my DTS design