DTS data turns into null

hi,
I have a sql server 2000 DTS package, that selecting
yesterday's data from one of our vendors database
(Advantage platform) into our sql server 2000 db, daily
process.
my select query is like this:
SELECT *
FROM vendor_table
WHERE (DATE = { fn CURDATE() } - 1)
Advantage don't work with 'getdate()' for some reason.
recently, we are experiencing a problem with this task:
On certain day, over 10% of the records that got pulled
over by the above filter turns into null in the date field
once got imported into our tables. some of the records'
date field will become '11/12/1926', any significants of
this date? This has happened several times now.
When I log on advantage platform and view the data, there
is no null date in the whole table, and all the date are
in the same format, so why some changed into null once it
imported over to our db?
many thanks!
JJ
I personally would identify the rows that come out "wrong" from the source
system and I would create a source of only these rows. I would then watch
them come through and try to figure out why they are coming through as they
do. Profiler might be useful to you here.
Is it always the same rows?
Allan
"JJ Wang" <anonymous@.discussions.microsoft.com> wrote in message
news:22ba601c45daf$7c144660$a401280a@.phx.gbl...
> hi,
> I have a sql server 2000 DTS package, that selecting
> yesterday's data from one of our vendors database
> (Advantage platform) into our sql server 2000 db, daily
> process.
> my select query is like this:
> SELECT *
> FROM vendor_table
> WHERE (DATE = { fn CURDATE() } - 1)
> Advantage don't work with 'getdate()' for some reason.
> recently, we are experiencing a problem with this task:
> On certain day, over 10% of the records that got pulled
> over by the above filter turns into null in the date field
> once got imported into our tables. some of the records'
> date field will become '11/12/1926', any significants of
> this date? This has happened several times now.
> When I log on advantage platform and view the data, there
> is no null date in the whole table, and all the date are
> in the same format, so why some changed into null once it
> imported over to our db?
> many thanks!
> JJ
|||I personally would identify the rows that come out "wrong" from the source
system and I would create a source of only these rows. I would then watch
them come through and try to figure out why they are coming through as they
do. Profiler might be useful to you here.
Is it always the same rows?
Allan
"JJ Wang" <anonymous@.discussions.microsoft.com> wrote in message
news:22ba601c45daf$7c144660$a401280a@.phx.gbl...
> hi,
> I have a sql server 2000 DTS package, that selecting
> yesterday's data from one of our vendors database
> (Advantage platform) into our sql server 2000 db, daily
> process.
> my select query is like this:
> SELECT *
> FROM vendor_table
> WHERE (DATE = { fn CURDATE() } - 1)
> Advantage don't work with 'getdate()' for some reason.
> recently, we are experiencing a problem with this task:
> On certain day, over 10% of the records that got pulled
> over by the above filter turns into null in the date field
> once got imported into our tables. some of the records'
> date field will become '11/12/1926', any significants of
> this date? This has happened several times now.
> When I log on advantage platform and view the data, there
> is no null date in the whole table, and all the date are
> in the same format, so why some changed into null once it
> imported over to our db?
> many thanks!
> JJ
|||Hi
I am not sure why the date is significant, it looks rather near the windows
cut off date, but that should not come into play! If you change the date so
it is formatted as a safe string i.e YYYYMMDD format then I think you should
avoid this problem.
John
"JJ Wang" <anonymous@.discussions.microsoft.com> wrote in message
news:22ba601c45daf$7c144660$a401280a@.phx.gbl...
> hi,
> I have a sql server 2000 DTS package, that selecting
> yesterday's data from one of our vendors database
> (Advantage platform) into our sql server 2000 db, daily
> process.
> my select query is like this:
> SELECT *
> FROM vendor_table
> WHERE (DATE = { fn CURDATE() } - 1)
> Advantage don't work with 'getdate()' for some reason.
> recently, we are experiencing a problem with this task:
> On certain day, over 10% of the records that got pulled
> over by the above filter turns into null in the date field
> once got imported into our tables. some of the records'
> date field will become '11/12/1926', any significants of
> this date? This has happened several times now.
> When I log on advantage platform and view the data, there
> is no null date in the whole table, and all the date are
> in the same format, so why some changed into null once it
> imported over to our db?
> many thanks!
> JJ
|||Hi
I am not sure why the date is significant, it looks rather near the windows
cut off date, but that should not come into play! If you change the date so
it is formatted as a safe string i.e YYYYMMDD format then I think you should
avoid this problem.
John
"JJ Wang" <anonymous@.discussions.microsoft.com> wrote in message
news:22ba601c45daf$7c144660$a401280a@.phx.gbl...
> hi,
> I have a sql server 2000 DTS package, that selecting
> yesterday's data from one of our vendors database
> (Advantage platform) into our sql server 2000 db, daily
> process.
> my select query is like this:
> SELECT *
> FROM vendor_table
> WHERE (DATE = { fn CURDATE() } - 1)
> Advantage don't work with 'getdate()' for some reason.
> recently, we are experiencing a problem with this task:
> On certain day, over 10% of the records that got pulled
> over by the above filter turns into null in the date field
> once got imported into our tables. some of the records'
> date field will become '11/12/1926', any significants of
> this date? This has happened several times now.
> When I log on advantage platform and view the data, there
> is no null date in the whole table, and all the date are
> in the same format, so why some changed into null once it
> imported over to our db?
> many thanks!
> JJ
|||thanks Allan. it's hard to tell whether they are always
the same rows since there is no primary keys in this
table! I will take your good advise and watch it through
the profiler. what kind of filter should I do in
profiler? (I don't want to kill the system).
JJ
>--Original Message--
>I personally would identify the rows that come
out "wrong" from the source
>system and I would create a source of only these rows. I
would then watch
>them come through and try to figure out why they are
coming through as they
>do. Profiler might be useful to you here.
>Is it always the same rows?
>Allan
>
>"JJ Wang" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:22ba601c45daf$7c144660$a401280a@.phx.gbl...
field[vbcol=seagreen]
there[vbcol=seagreen]
it
>
>.
>
|||thanks for the good tip, I will keep it in mind.
JJ
>--Original Message--
>Hi
>I am not sure why the date is significant, it looks
rather near the windows
>cut off date, but that should not come into play! If you
change the date so
>it is formatted as a safe string i.e YYYYMMDD format then
I think you should
>avoid this problem.
>John
>"JJ Wang" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:22ba601c45daf$7c144660$a401280a@.phx.gbl...
field[vbcol=seagreen]
there[vbcol=seagreen]
it
>
>.
>
|||Once you have your "Bad" rowset you can simply watch the TextData
column to see how it is interpreted.
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
|||thanks Allan!
JJ
>--Original Message--
>Once you have your "Bad" rowset you can simply watch the
TextData
>column to see how it is interpreted.
>
>Allan Mitchell (Microsoft SQL Server MVP)
>MCSE,MCDBA
>www.SQLDTS.com
>I support PASS - the definitive, global community
>for SQL Server professionals - http://www.sqlpass.org
>.
>

DTA package handling multiple file formats

Hi,

Currently we get data from more then 200 different sources and all of
our vendors provide data in different file formats. The problem is we
have more then 100 DTS packages now and the maintainance is very
diffucult.
Every time our vendor changes the format we have to change in multiple
DTS packages.
Is anybody know what would be the right way of reducing the no. of DTS
packages.
The type of file formats we get are .xls .txt .dat .csv etc. and .txt
& .dat files comes with different delimitters. The # of columns also
varies from file to file. Is it possible to have a DTS package which
can handle diff file formats and loads data into a staging table and
from there based of the source of the file we can move data into
respective tables & columns.

We are using SQL SERVER 2000

Thanks in advance.

Subodh"Subodh" <sgoyal@.agline.on.ca> wrote in message
news:90104bf0.0501240846.58b2b293@.posting.google.c om...
> Hi,
> Currently we get data from more then 200 different sources and all of
> our vendors provide data in different file formats. The problem is we
> have more then 100 DTS packages now and the maintainance is very
> diffucult.
> Every time our vendor changes the format we have to change in multiple
> DTS packages.
> Is anybody know what would be the right way of reducing the no. of DTS
> packages.
> The type of file formats we get are .xls .txt .dat .csv etc. and .txt
> & .dat files comes with different delimitters. The # of columns also
> varies from file to file. Is it possible to have a DTS package which
> can handle diff file formats and loads data into a staging table and
> from there based of the source of the file we can move data into
> respective tables & columns.
> We are using SQL SERVER 2000
> Thanks in advance.
> Subodh

Personally, I would look at writing an external script or program in C#,
Python, Perl or whatever to manipulate the files and load the staging table.
The script could load the data directly to the staging table by dynamically
generating INSERTs, or it might transform the source files to your own
standard file format to be used with bcp.exe, BULK INSERT or the DTS Bulk
Insert task.

Your maintenance efforts would be then directed at the program, not at the
packages, which is probably a good thing - it's likely easier to modify one
module/class/object than 10 packages, and most languages have good library
support for parsing, tokenizing, regexes and so on. Or perhaps a hybrid
solution might work - an external program for proprietary file formats, and
standard DTS connections/tasks for the rest. You might also want to ask in
microsoft.public.sqlserver.dts to see if someone else has experienced a
similar situation.

Finally, since your basic issue (as I understood it) is that you have too
many file formats, you should consider agreeing a standard file format - at
least with your larger clients/vendors - rather than looking at it just as a
technical problem. I have no idea how easy that would be in your company's
situation, of course.

Simon|||
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!