Urgeeeeent ...DTS question

[tlaksh]

I have a dts question. I have a dts that converting data from a txt file into the SQL 7.0 database. The text file gets data by running a batch file.

Now before the data gets converted I want to check if the txt file has any data or if size of txt file is 0. If not i donn't want to go ahead with the dts. Else dts failes with an error that data was not found.

Any suggestions ..Thanks a ton

 

[rajeevnandanmishra]

Hi there,

IF you add the following lines in the begining of your code, it may solve your problem

-----------------------------------------------
CREATE TABLE #temp1 (filedesc varchar(255) null)
INSERT #temp1
EXEC master..xp_cmdshell 'dir c:\myDirectory\myFile /n /-c '
IF (select convert(int,substring(des,len(rtrim(des))-9,2)) from #temp1
where des like '%myFile')=0
BEGIN
Print 'File Size is ZERO Bytes'
drop table #temp1
END
drop table #temp1
... Here your normal Code
-----------------------------------------------

In the IF statement you should convert 9 to the length of your filename + 2.
i.e. if your file is mybcp.txt, convert 9 with 11

Hope this will help you!

 

[tlaksh]

With the above code how can i go to the next step on success of this code..

Eg: If i write this code in a sql task how can i go to the next task of Text file (source) transformation.

I am kinda confused now..can u please explain in detail...

Thanks

Lakshmi.

 

[tlaksh]

Retry....any more suggestions...Thanks Folks...

Laksh

 

[rajeevnandanmishra]

Hi there,

In the following if program reaches the second "drop table" statement, it means that your file have some data. And so you can write your existing sql task after that.

--------------------------
CREATE TABLE #temp1 (filedesc varchar(255) null)
INSERT #temp1
EXEC master..xp_cmdshell 'dir c:\myDirectory\myFile /n /-c '
IF (select convert(int,substring(des,len(rtrim(des))-9,2)) from #temp1
where des like '%myFile')=0
BEGIN
Print 'File Size is ZERO Bytes'
drop table #temp1
RETURN
END
drop table #temp1

... Here your normal Code

--------------------------

 

[ruupy]

Say tlaksh,

You're importing data from a flatfile, do you happen to know how to write the result (being a compleet recordset or just the valua of a variable) of a query, directly to a TXT file???,....

ThenX alot in Advance.

RuupY