DTS fails coes it refuses to fill the unique id colum

THe unique Id column is set to identity seed. Yet when transfering 1500 records it fails to stuff them all becuase it says there is a NULL in unique id column.

"Cannot insert the value NULL into column UID column does not allow"

Yet in the transformation screen I am setting the UID column to <ignore> meaning I do not have that value in my source and I am wanting the destination table to do it's thing and incrememnt by 1 the identity seed.

Is this common?

THe source and destination tables are in the same databes.

When you click on "Transform" uncheck where it says "Enable Identity Insert"

|||

Thank you very much.

It worked.

I am not sure I "logically" understand that though.

|||

When you have enable Identity Insert checked in your DTS wizard what it does is says "I want you to insert the identity field that is coming from the table I'm copying from". This is useful when you have a truncated table and you want to keep your identity field values for data integrity.

Now comes the however.

When you set to Ignore the Identity column in your transformation you were essentially telling it to insert null values, and Identity columns can't be null. Had you not selected that then you would have most likely received the error that says you can't insert duplicates into your identiy column as it violates the unique constraint.

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
>.
>

DTS "Can Not Insert Null Value"

Hi,
I'm using a DTS package to import data from csv files, the destiantion
table however has a unique primary key field that can not be null, this
data is not in the CSV file.
I'm using an ActiveX transformation, and I've used the following code
to add a number to the ID field:
Function Main()
if isEmpty(N) then
N = 0
end if
N = N+1
DTSDestination("CDR_ID") = N
Main = DTSTransformStat_OK
End Function
However the problem with this is that it needs to start at whatever the
LAST id field number was, IE instead of 1,2,3, it needs to be x+1, x+2,
x+3 where X is the previously highest ID.
I was hoping that SQL server would generate the ID field if I didnt put
one in, but alas it was not to be.
Thanks is advance for any help.
Matt.With the unique PK have you got auto identity set up ?
--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
<Matt.Mawdsley@.gmail.com> wrote in message
news:1146128307.625814.307880@.e56g2000cwe.googlegroups.com...
> Hi,
> I'm using a DTS package to import data from csv files, the destiantion
> table however has a unique primary key field that can not be null, this
> data is not in the CSV file.
> I'm using an ActiveX transformation, and I've used the following code
> to add a number to the ID field:
> Function Main()
> if isEmpty(N) then
> N = 0
> end if
> N = N+1
> DTSDestination("CDR_ID") = N
> Main = DTSTransformStat_OK
> End Function
> However the problem with this is that it needs to start at whatever the
> LAST id field number was, IE instead of 1,2,3, it needs to be x+1, x+2,
> x+3 where X is the previously highest ID.
> I was hoping that SQL server would generate the ID field if I didnt put
> one in, but alas it was not to be.
> Thanks is advance for any help.
> Matt.
>|||Jack,
I don't belive I do, where is this set? and how? =)
Thanks,
Matt.

DTS "Can Not Insert Null Value"

Hi,
I'm using a DTS package to import data from csv files, the destiantion
table however has a unique primary key field that can not be null, this
data is not in the CSV file.
I'm using an ActiveX transformation, and I've used the following code
to add a number to the ID field:
Function Main()
if isEmpty(N) then
N = 0
end if
N = N+1
DTSDestination("CDR_ID") = N
Main = DTSTransformStat_OK
End Function
However the problem with this is that it needs to start at whatever the
LAST id field number was, IE instead of 1,2,3, it needs to be x+1, x+2,
x+3 where X is the previously highest ID.
I was hoping that SQL server would generate the ID field if I didnt put
one in, but alas it was not to be.
Thanks is advance for any help.
Matt.With the unique PK have you got auto identity set up ?
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
<Matt.Mawdsley@.gmail.com> wrote in message
news:1146128307.625814.307880@.e56g2000cwe.googlegroups.com...
> Hi,
> I'm using a DTS package to import data from csv files, the destiantion
> table however has a unique primary key field that can not be null, this
> data is not in the CSV file.
> I'm using an ActiveX transformation, and I've used the following code
> to add a number to the ID field:
> Function Main()
> if isEmpty(N) then
> N = 0
> end if
> N = N+1
> DTSDestination("CDR_ID") = N
> Main = DTSTransformStat_OK
> End Function
> However the problem with this is that it needs to start at whatever the
> LAST id field number was, IE instead of 1,2,3, it needs to be x+1, x+2,
> x+3 where X is the previously highest ID.
> I was hoping that SQL server would generate the ID field if I didnt put
> one in, but alas it was not to be.
> Thanks is advance for any help.
> Matt.
>|||Jack,
I don't belive I do, where is this set? and how? =)
Thanks,
Matt.