Check File size before importing

I have a DTS procedure that runs 4 times a day. It has worked well until
now. It is reading infromation from a text file. Today the text file was
empty and DTS failed giving me this error; "The volume for a file has been
externally altered so that the opened file is no longer valid.". I believe
this is happening because the file size is 0 K bytes (This file is
constantly overwriitten). Is there a way before importing a text file to
check and verify that the file is not empty?
Use FSO (File System Object) to check the size first in an ActiveX script,
you can find an exampl on sqldts.com
"deheinz1" <deheinz1@.discussions.microsoft.com> wrote in message
news:5292E0B2-B1B5-4B3F-A51C-45E66D8F0C53@.microsoft.com...
>I have a DTS procedure that runs 4 times a day. It has worked well until
> now. It is reading infromation from a text file. Today the text file was
> empty and DTS failed giving me this error; "The volume for a file has been
> externally altered so that the opened file is no longer valid.". I
> believe
> this is happening because the file size is 0 K bytes (This file is
> constantly overwriitten). Is there a way before importing a text file to
> check and verify that the file is not empty?
|||Even confirming that the file size is > 0 may not indicate reliably that the
source data transfer has completed. I have a similar situation where files
are being FTP'd from the mainframe and can take 1/2 or more to complete
(assuming they do complete). My solution was to ask the data processing
staff to download the file with an extention of .TMP and then rename it to
..DAT after the download is complete.
"deheinz1" <deheinz1@.discussions.microsoft.com> wrote in message
news:5292E0B2-B1B5-4B3F-A51C-45E66D8F0C53@.microsoft.com...
> I have a DTS procedure that runs 4 times a day. It has worked well until
> now. It is reading infromation from a text file. Today the text file was
> empty and DTS failed giving me this error; "The volume for a file has been
> externally altered so that the opened file is no longer valid.". I
believe
> this is happening because the file size is 0 K bytes (This file is
> constantly overwriitten). Is there a way before importing a text file to
> check and verify that the file is not empty?

Check File size before importing

I have a DTS procedure that runs 4 times a day. It has worked well until
now. It is reading infromation from a text file. Today the text file was
empty and DTS failed giving me this error; "The volume for a file has been
externally altered so that the opened file is no longer valid.". I believe
this is happening because the file size is 0 K bytes (This file is
constantly overwriitten). Is there a way before importing a text file to
check and verify that the file is not empty?Use FSO (File System Object) to check the size first in an ActiveX script,
you can find an exampl on sqldts.com
"deheinz1" <deheinz1@.discussions.microsoft.com> wrote in message
news:5292E0B2-B1B5-4B3F-A51C-45E66D8F0C53@.microsoft.com...
>I have a DTS procedure that runs 4 times a day. It has worked well until
> now. It is reading infromation from a text file. Today the text file was
> empty and DTS failed giving me this error; "The volume for a file has been
> externally altered so that the opened file is no longer valid.". I
> believe
> this is happening because the file size is 0 K bytes (This file is
> constantly overwriitten). Is there a way before importing a text file to
> check and verify that the file is not empty?|||Even confirming that the file size is > 0 may not indicate reliably that the
source data transfer has completed. I have a similar situation where files
are being FTP'd from the mainframe and can take 1/2 or more to complete
(assuming they do complete). My solution was to ask the data processing
staff to download the file with an extention of .TMP and then rename it to
.DAT after the download is complete.
"deheinz1" <deheinz1@.discussions.microsoft.com> wrote in message
news:5292E0B2-B1B5-4B3F-A51C-45E66D8F0C53@.microsoft.com...
> I have a DTS procedure that runs 4 times a day. It has worked well until
> now. It is reading infromation from a text file. Today the text file was
> empty and DTS failed giving me this error; "The volume for a file has been
> externally altered so that the opened file is no longer valid.". I
believe
> this is happening because the file size is 0 K bytes (This file is
> constantly overwriitten). Is there a way before importing a text file to
> check and verify that the file is not empty?

Char to Bit

I am importing a table where I need to convert a char(1) with the
values of 't' or 'f' into a bit field with valies of 1 or 0. Is there
a built-in function that does that? I've been searching, but I can't
find an answer.On 15 Apr 2005 14:25:24 -0700, imani_technology_spam@.yahoo.com wrote:

>I am importing a table where I need to convert a char(1) with the
>values of 't' or 'f' into a bit field with valies of 1 or 0. Is there
>a built-in function that does that? I've been searching, but I can't
>find an answer.

Hi imani,

The best answer is to store it as a CHAR(1) column with values 't' and
'f' and to forget aboout converting to BIT - what do you expect to gain
from it?

The second best answer is to use a CASE expression.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Do not use bits in SQL. You cannot use them as Booleans in other
Microsoft host languages. They are proprietary. And a good SQL
porgrammer does not write with flags anyway. Do some searching about
bits for the details.|||I don't have a choice in the matter. I have been told to convert a
char(1) to a bit. So what is the best way to do it within a UDF?

--CELKO-- wrote:
> Do not use bits in SQL. You cannot use them as Booleans in other
> Microsoft host languages. They are proprietary. And a good SQL
> porgrammer does not write with flags anyway. Do some searching about
> bits for the details.|||The #2 answer is a CASE expression with CAST() functions to be safe.
You will need to document that for the next guy because this is suicide
and you do not want to be blamed for it.

Did you know that +1, +0, -0 and -1 are all used for BOOLEANs, but not
consistently within Microsoft's own proprietary languages? You have to
know what the host language will be to do the mapping from Boolean to
bits. Once that decision is made you cannot use another incompatible
host language. And watch out for CLR later.|||Thanks for the info. I am aware that MS isn't consistent with their
own Booleans. However, I'm the new guy on a very large team, so I have
to deter to them on that issue. Also, I didn't know you could CAST
from char(1) to a bit. I thought the two data types were incompatible.|||On 17 Apr 2005 09:33:44 -0700, imani_technology_spam@.yahoo.com wrote:

>Thanks for the info. I am aware that MS isn't consistent with their
>own Booleans. However, I'm the new guy on a very large team, so I have
>to deter to them on that issue. Also, I didn't know you could CAST
>from char(1) to a bit. I thought the two data types were incompatible.

Hi imani,

You're right, you can't just CAST a char(1) to bit, unless the char(1)
holds only '0' and '1' - and even for that case, I'd run a test before
betting any money on it :-)

That's why both Joe (Celko) and I (in my previous reply in this thread)
suggest using a CASE. Joe's suggestion to *ALSO* use a CAST is actually
quite good - not really needed in SQL Server, but it better documents
what you're doing:

CASE WHEN CharColumn = 't' THEN CAST(1 AS bit) ELSE CAST(0 AS bit) END

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)