contains specific metrics from our accounting system from the the prior day.
The data gets dumped out to the csv file sometime at night, and a DTS packag
e
imports it into SQL at 7:30 am.
I need to verify that the data populated into SQL every morning. I've been
manually querying the table, E1_Data, on one of three date columns, ENDED,
with the following script,
SELECT ENDED
FROM E12SQL.E1_DATA
WHERE (MONTH(ENDED) = '08') AND (DAY(ENDED) = '29') AND (YEAR(ENDED) =
'2005')
If I get data back, I know yesterdays csv file imported into SQL.
Surely there is a better way to do this.
AlwaysLearningYou could create a stored proc and add it as a second step in your job to ru
n
the DTS package.
create proc as
declare @.myDate varchar(20)
set @.myDate = convert(varchar,dateadd(day,-1,getDate()),1) --yesterday's
date
if not exists(select Ended from E1_DATA where Ended > @.myDate) begin
raiserror('No imported records',16,1)
return 1
end
return 0
"AlwaysLearning" wrote:
> We have a SQL 2000 database that we import a csv file into. The csv file
> contains specific metrics from our accounting system from the the prior da
y.
> The data gets dumped out to the csv file sometime at night, and a DTS pack
age
> imports it into SQL at 7:30 am.
> I need to verify that the data populated into SQL every morning. I've bee
n
> manually querying the table, E1_Data, on one of three date columns, ENDED,
> with the following script,
> SELECT ENDED
> FROM E12SQL.E1_DATA
> WHERE (MONTH(ENDED) = '08') AND (DAY(ENDED) = '29') AND (YEAR(ENDED) =
> '2005')
> If I get data back, I know yesterdays csv file imported into SQL.
> Surely there is a better way to do this.
>
> --
> AlwaysLearning|||The "yesterday's date" was all supposed to be a comment, it didnt' wrap well
.
Also, you may need to have the where clause be >= instead of >.
"Kathi Kellenberger" wrote:
> You could create a stored proc and add it as a second step in your job to
run
> the DTS package.
> create proc as
> declare @.myDate varchar(20)
> set @.myDate = convert(varchar,dateadd(day,-1,getDate()),1) --yesterday
's
> date
> if not exists(select Ended from E1_DATA where Ended > @.myDate) begin
> raiserror('No imported records',16,1)
> return 1
> end
> return 0
>
>
> "AlwaysLearning" wrote:
>|||So how do you want to verify the that the data was inserted. And why don't
you trust that the DTS package worked? Have there been cases where it
didn't but didn't raise any errors?
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"AlwaysLearning" <AlwaysLearning@.discussions.microsoft.com> wrote in message
news:D3420366-7668-49EB-B245-6A668FC6B552@.microsoft.com...
> We have a SQL 2000 database that we import a csv file into. The csv file
> contains specific metrics from our accounting system from the the prior
> day.
> The data gets dumped out to the csv file sometime at night, and a DTS
> package
> imports it into SQL at 7:30 am.
> I need to verify that the data populated into SQL every morning. I've
> been
> manually querying the table, E1_Data, on one of three date columns, ENDED,
> with the following script,
> SELECT ENDED
> FROM E12SQL.E1_DATA
> WHERE (MONTH(ENDED) = '08') AND (DAY(ENDED) = '29') AND (YEAR(ENDED) =
> '2005')
> If I get data back, I know yesterdays csv file imported into SQL.
> Surely there is a better way to do this.
>
> --
> AlwaysLearning|||Louis, you ask, "...why don't you trust that the DTS package worked?"
Actually, the DTS package works just fine, it is that sometimes the
accounting system doesn't export its data to the csv file which results in
DTS pulling in the same data twice.
Regarding your first question, "...how do you want to verify the that the
data was inserted". If the data imported successfully, then the table,
E1_Data, will contain the priors day date in the ENDED column. It seems
logical to me that if I count the number of records in the table that have
yesterdays date and store that value somewhere, Excel or another SQL table,
then I can verify the data came over.
AlwaysLearning
"Louis Davidson" wrote:
> So how do you want to verify the that the data was inserted. And why don'
t
> you trust that the DTS package worked? Have there been cases where it
> didn't but didn't raise any errors?
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
> "Arguments are to be avoided: they are always vulgar and often convincing.
"
> (Oscar Wilde)
> "AlwaysLearning" <AlwaysLearning@.discussions.microsoft.com> wrote in messa
ge
> news:D3420366-7668-49EB-B245-6A668FC6B552@.microsoft.com...
>
>|||That's
. I am guessing that the other suggestion of having a step thatchecks the table and raises an error if no data is in the table would
probably be a good idea.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"AlwaysLearning" <AlwaysLearning@.discussions.microsoft.com> wrote in message
news:88D357A1-27B8-44C2-AE6B-E7CEF4767DA3@.microsoft.com...
> Louis, you ask, "...why don't you trust that the DTS package worked?"
> Actually, the DTS package works just fine, it is that sometimes the
> accounting system doesn't export its data to the csv file which results in
> DTS pulling in the same data twice.
> Regarding your first question, "...how do you want to verify the that the
> data was inserted". If the data imported successfully, then the table,
> E1_Data, will contain the priors day date in the ENDED column. It seems
> logical to me that if I count the number of records in the table that have
> yesterdays date and store that value somewhere, Excel or another SQL
> table,
> then I can verify the data came over.
> --
> AlwaysLearning
>
> "Louis Davidson" wrote:
>
