DTS - Source file with 0 rows - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

DTS - Source file with 0 rows

Author

Topic

jsiedliski
Yak Posting Veteran

61 Posts

Posted - 2003-09-23 : 17:48:59

I created a DTS package which imports data from a delimited text file to a SQL Server table (SQL Server 2000) NO HEADER ROW.

My source file structure is set by an outside party (3rd party data file). So, I have no control over how it looks.

The good news is that the structure is always the same. The bad news (what I need help with) is that sometimes there are no changes sent over, so the file has nothing in it (0 bytes). I guess that if there was a header, the package would not fail. But the file structure does not include the header.

So, my issue is that on the days that there are no changes in the file , the DTS package fails becuase there is no data. Is there a way for me to use some sort of command to skip running the DTS if the file size = 0? Maybe something with xp_cmdshell?

Anyone have a solution / suggestion?

Thanks,

Jack

It is what it is.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-09-23 : 18:20:04

Why don't you create the table prior to transferring the data? That way the transfer part doesn't have to do it. This is the recommended approach anyway.

Tara

jsiedliski
Yak Posting Veteran

61 Posts

Posted - 2003-09-23 : 18:35:25

The table is already there (prior to the DTS data transfer run). It is a permanent container.

The error is with the source file being 0 bytes (no rows). In other words, it errors out when attepting to connect to the text file during the Data Transformation process.

Hope this helps

It is what it is.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-09-23 : 19:14:53

Oh ok, I misunderstood your question.

To solve your problem, you could run the below VBScript (checks if the file is 0 bytes, if it is add a space to the file) prior to the transfer of the data.



dim filesys, readfile, filetxt

Const ForReading = 1, ForWriting = 2, ForAppending = 8 

set filesys = CreateObject("Scripting.FileSystemObject") 
set readfile = filesys.GetFile("c:\temp\somefile.txt")
Set filetxt = filesys.OpenTextFile("c:\temp\somefile.txt", ForWriting, True) 

If readfile.Size = 0 Then 
   filetxt.WriteLine(" ")
   filetxt.Close 
End If

Tara

jsiedliski
Yak Posting Veteran

61 Posts

Posted - 2003-09-23 : 20:16:45

That works great - Thanks.

To be exact, I am inserting 1 record (from a previous file which had data), rather than a space. Thanks again for the code.

--Jack

It is what it is.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources