DTS error on GUID column

I am doing a DTS from table "source" to table "destination". However
"destination" has a primary key of data type uniqueidentifier and the source
doesn't have a corresponding map field. So I thought setting the
"destination" primary key to have a default value of newId() will
automatically insert a guid for every record inserted (just like a regular
identity key) but unfortunately this doesn't work. I get a message that says
"cannot insert null into the field fieldName" which makes sense because it i
s
the PK but why doesn't the newId() generate an automatic GUID? Or is it even
possible? or could I change the transformation script to make it work and if
so, how? Thanks for any help.On guid primary key column undid primary key constraint, set allow null, set
default value to newId() and DTS worked fine and new guids got inserted.
After DTS set all constraints back as original. So everything's good.
"Naveen" wrote:

> I am doing a DTS from table "source" to table "destination". However
> "destination" has a primary key of data type uniqueidentifier and the sour
ce
> doesn't have a corresponding map field. So I thought setting the
> "destination" primary key to have a default value of newId() will
> automatically insert a guid for every record inserted (just like a regular
> identity key) but unfortunately this doesn't work. I get a message that sa
ys
> "cannot insert null into the field fieldName" which makes sense because it
is
> the PK but why doesn't the newId() generate an automatic GUID? Or is it ev
en
> possible? or could I change the transformation script to make it work and
if
> so, how? Thanks for any help.

DTS + PK

Hello,
I have a table with a primary key.
I also have a CSV. The table has all the same fields as the CSV plus the
PKID.
I want to create a DTS that will import it. It seems to be putting in NULLS
for the PKID though.
I created the table and checked it. It is a PK and Identity is set to 1 and
increment by 1.
How to fix this?When you are in the DTS wizard, click the transform tab and see if all
columns are properly matched.
"Won Lee" <noemail> wrote in message
news:#3r#BOIUDHA.612@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I have a table with a primary key.
> I also have a CSV. The table has all the same fields as the CSV plus the
> PKID.
> I want to create a DTS that will import it. It seems to be putting in
NULLS
> for the PKID though.
> I created the table and checked it. It is a PK and Identity is set to 1
and
> increment by 1.
> How to fix this?
>|||All the columns are matched. I remember not having a problem with this at
all before. I have several other DTS packages that look similar. All the
cloumns are matched except for the pkID.
SO the table in SQL server has 7 fields. The CSV has 6 fields seperated by
commas.
"ilovesql" <ilovesql@.hotmail.com> wrote in message
news:e6K$lMJUDHA.3192@.tk2msftngp13.phx.gbl...
> When you are in the DTS wizard, click the transform tab and see if all
> columns are properly matched.
>
> "Won Lee" <noemail> wrote in message
> news:#3r#BOIUDHA.612@.TK2MSFTNGP12.phx.gbl...
> > Hello,
> >
> > I have a table with a primary key.
> > I also have a CSV. The table has all the same fields as the CSV plus
the
> > PKID.
> > I want to create a DTS that will import it. It seems to be putting in
> NULLS
> > for the PKID though.
> > I created the table and checked it. It is a PK and Identity is set to 1
> and
> > increment by 1.
> >
> > How to fix this?
> >
> >
>|||Very strange. However, when you uncheck Allow Identity Insert, it looks
working -- though I thought it should be the other way around.
"Won Lee" <noemail> wrote in message
news:#lfqVYRUDHA.2036@.TK2MSFTNGP10.phx.gbl...
> All the columns are matched. I remember not having a problem with this at
> all before. I have several other DTS packages that look similar. All the
> cloumns are matched except for the pkID.
> SO the table in SQL server has 7 fields. The CSV has 6 fields seperated
by
> commas.
> "ilovesql" <ilovesql@.hotmail.com> wrote in message
> news:e6K$lMJUDHA.3192@.tk2msftngp13.phx.gbl...
> > When you are in the DTS wizard, click the transform tab and see if all
> > columns are properly matched.
> >
> >
> > "Won Lee" <noemail> wrote in message
> > news:#3r#BOIUDHA.612@.TK2MSFTNGP12.phx.gbl...
> > > Hello,
> > >
> > > I have a table with a primary key.
> > > I also have a CSV. The table has all the same fields as the CSV plus
> the
> > > PKID.
> > > I want to create a DTS that will import it. It seems to be putting in
> > NULLS
> > > for the PKID though.
> > > I created the table and checked it. It is a PK and Identity is set to
1
> > and
> > > increment by 1.
> > >
> > > How to fix this?
> > >
> > >
> >
> >
>|||Yes. That was the case. I delselctged enable indentity insert and it
worked fine.
"Quentin Ran" <quentinran@.yahoo.com> wrote in message
news:eGaoByWUDHA.2316@.TK2MSFTNGP09.phx.gbl...
> Very strange. However, when you uncheck Allow Identity Insert, it looks
> working -- though I thought it should be the other way around.
>
> "Won Lee" <noemail> wrote in message
> news:#lfqVYRUDHA.2036@.TK2MSFTNGP10.phx.gbl...
> > All the columns are matched. I remember not having a problem with this
at
> > all before. I have several other DTS packages that look similar. All
the
> > cloumns are matched except for the pkID.
> >
> > SO the table in SQL server has 7 fields. The CSV has 6 fields seperated
> by
> > commas.
> > "ilovesql" <ilovesql@.hotmail.com> wrote in message
> > news:e6K$lMJUDHA.3192@.tk2msftngp13.phx.gbl...
> > > When you are in the DTS wizard, click the transform tab and see if all
> > > columns are properly matched.
> > >
> > >
> > > "Won Lee" <noemail> wrote in message
> > > news:#3r#BOIUDHA.612@.TK2MSFTNGP12.phx.gbl...
> > > > Hello,
> > > >
> > > > I have a table with a primary key.
> > > > I also have a CSV. The table has all the same fields as the CSV
plus
> > the
> > > > PKID.
> > > > I want to create a DTS that will import it. It seems to be putting
in
> > > NULLS
> > > > for the PKID though.
> > > > I created the table and checked it. It is a PK and Identity is set
to
> 1
> > > and
> > > > increment by 1.
> > > >
> > > > How to fix this?
> > > >
> > > >
> > >
> > >
> >
> >
>

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.

DTS

Hi friends,
In my project I have redesigned my database structure. In the existing
structure there is no Primary key and no relationship b/w data.
In the new structure Primary key and the relationship is added.
Now I want to migrate the existing data into the new structure.
There is a chance for duplicate records and also records that does not
satisfy referential integrity.
How to migrate the data? I want to have a copy of the duplicate records and
also the records which does not satisfy referential integrity.
Its a huge database, so i can't query table by table to find the mismatch
records.
How to proceed?
thanks
vanitha
thanks a lot
vanithaHi
I think a multi-phase data pump may be what you are looking for check out
http://www.sqldts.com/default.aspx?282
John
"Vanitha" wrote:

> Hi friends,
> In my project I have redesigned my database structure. In the existing
> structure there is no Primary key and no relationship b/w data.
> In the new structure Primary key and the relationship is added.
> Now I want to migrate the existing data into the new structure.
> There is a chance for duplicate records and also records that does not
> satisfy referential integrity.
> How to migrate the data? I want to have a copy of the duplicate records an
d
> also the records which does not satisfy referential integrity.
> Its a huge database, so i can't query table by table to find the mismatch
> records.
> How to proceed?
> thanks
> vanitha
> thanks a lot
> vanitha|||Thanks John.
I am not getting the Phases Tab. How we will get "Visual Basic
Transformation Script"
Thanks
vanitha
"John Bell" wrote:
> Hi
> I think a multi-phase data pump may be what you are looking for check out
> http://www.sqldts.com/default.aspx?282
> John
> "Vanitha" wrote:
>|||Hi
If you have enabled viewing multiphase transaformations (see figure 1.1)
then to get you will need to delete any existing transformations created and
then create new activeX transformation. You can add phases on the phases tab
on the transformation options, or the phases tab on the Active X
transformation properties (properties button on the general tab of the
transformation options dialogue)
John
"Vanitha" wrote:
> Thanks John.
> I am not getting the Phases Tab. How we will get "Visual Basic
> Transformation Script"
> Thanks
> vanitha
> "John Bell" wrote:
>

DSV and Cube do not match - generates Key Errors

Hi All,

This is strange behaviour, hopefully I can resolve it without rebuilding the cube and all the dimensions from scratch.

I have a cube with a fact table, and a number of dimensions, including an EventType and Event Date. (Event Type is "Sale", "Return", etc.etc, Event Date is the date it occurred)

When I created the DSV for this I accidently joined the fact table EventTypeID field to the EventTimeID on the Event Date dimensions. Not suprisingly this gave me a key error, as my EventTypeID on the fact table has values from 1-12, and the EventTimeID records start at 10000 and go upwards.

Having seen the error I went into the DSV and changed the relationship so that the Event Date dimension table was joined to the fact table on the correct fields. I then checked using SQL that there were no missing keys or other oddities on the base tables. I then manually did FULL process on all the dimensions, then tried to process the cube.

No dice. The error still occurs, it still claims that there is a missing key on the Event Date dimension, and a little further investigation shows that it is still using EventTypeID as the joining key. I have manually re-processed all dimensions etc, but to no avail.

How do I get the cube to pick up the changes in the DSV? What I don't want to have to do is throw it all away, as there are a fair number of hierarchies etc I would have to re-create.

Any help appreciated.

Richard R.

The Error reported back is:

Errors in the OLAP storage engine:
The attribute key cannot be found:
Table: dbo_tbl_Sales_FACT_LOAD, Column: EventTypeID, Value: 1.
Errors in the OLAP storage engine:
The attribute key was converted to an unknown member because the attribute key was not found.
Attribute Tbl Time DIM of Dimension:
Event Date from Database: ProtoType Cubes,
Cube: Sales And Mailings, Measure Group: Tbl Sales And Mailing FACT LOAD,
Partition: Tbl Sales And Mailing FACT LOAD, Record: 1.

Unfortunately, the metadata is pulled from the DSV and embedded in the higher level objects (dimensions, cubes, measure groups, etc.) when those objects are created. You need to recreate the objects for it to pickup the revised metadata in the DSV. Just updating the DSV doesn't do it alone.

Sometimes if it is a simple change, you can script out the objects into an XMLA script and then recreate it from the script rather than taking the time to drag & drop new objects around the system. However, you have to be careful and be knowledgeable. It is straightforward to do -- you are just editing a flat file, but if there are lots of references and changes I wouldn't go down that path.

Sorry to give you the bad news.

_-_-_ Dave

|||

As the late, great, Kenny Everett would have said

"Oh Bum!"

Thanks Dave,

Richard

|||

I hope somebody releases an XML refresh & validation tool for this issue. I have been troubleshooting this one and other datatype & DSV errors for awhile, and it affects multiple dimensions.

I have found that by correcting the relationship, then going into the cube's dimensions and clicking on the ... next to the dimension, you can reselect the key field (the same field, just click it again) and it seems to do the trick.

Otherwise, it's 'view code' and manually changing the DSV & the objects affected.

|||Wow, I just sank a few hours of time because of this issue...has anyone seen an refresh/validation tool like Andrew indicated? I could easily see how it could save tons of time!

DSV and Cube do not match - generates Key Errors

Hi All,

This is strange behaviour, hopefully I can resolve it without rebuilding the cube and all the dimensions from scratch.

I have a cube with a fact table, and a number of dimensions, including an EventType and Event Date. (Event Type is "Sale", "Return", etc.etc, Event Date is the date it occurred)

When I created the DSV for this I accidently joined the fact table EventTypeID field to the EventTimeID on the Event Date dimensions. Not suprisingly this gave me a key error, as my EventTypeID on the fact table has values from 1-12, and the EventTimeID records start at 10000 and go upwards.

Having seen the error I went into the DSV and changed the relationship so that the Event Date dimension table was joined to the fact table on the correct fields. I then checked using SQL that there were no missing keys or other oddities on the base tables. I then manually did FULL process on all the dimensions, then tried to process the cube.

No dice. The error still occurs, it still claims that there is a missing key on the Event Date dimension, and a little further investigation shows that it is still using EventTypeID as the joining key. I have manually re-processed all dimensions etc, but to no avail.

How do I get the cube to pick up the changes in the DSV? What I don't want to have to do is throw it all away, as there are a fair number of hierarchies etc I would have to re-create.

Any help appreciated.

Richard R.

The Error reported back is:

Errors in the OLAP storage engine:
The attribute key cannot be found:
Table: dbo_tbl_Sales_FACT_LOAD, Column: EventTypeID, Value: 1.
Errors in the OLAP storage engine:
The attribute key was converted to an unknown member because the attribute key was not found.
Attribute Tbl Time DIM of Dimension:
Event Date from Database: ProtoType Cubes,
Cube: Sales And Mailings, Measure Group: Tbl Sales And Mailing FACT LOAD,
Partition: Tbl Sales And Mailing FACT LOAD, Record: 1.

Unfortunately, the metadata is pulled from the DSV and embedded in the higher level objects (dimensions, cubes, measure groups, etc.) when those objects are created. You need to recreate the objects for it to pickup the revised metadata in the DSV. Just updating the DSV doesn't do it alone.

Sometimes if it is a simple change, you can script out the objects into an XMLA script and then recreate it from the script rather than taking the time to drag & drop new objects around the system. However, you have to be careful and be knowledgeable. It is straightforward to do -- you are just editing a flat file, but if there are lots of references and changes I wouldn't go down that path.

Sorry to give you the bad news.

_-_-_ Dave

|||

As the late, great, Kenny Everett would have said

"Oh Bum!"

Thanks Dave,

Richard

|||

I hope somebody releases an XML refresh & validation tool for this issue. I have been troubleshooting this one and other datatype & DSV errors for awhile, and it affects multiple dimensions.

I have found that by correcting the relationship, then going into the cube's dimensions and clicking on the ... next to the dimension, you can reselect the key field (the same field, just click it again) and it seems to do the trick.

Otherwise, it's 'view code' and manually changing the DSV & the objects affected.

|||Wow, I just sank a few hours of time because of this issue...has anyone seen an refresh/validation tool like Andrew indicated? I could easily see how it could save tons of time!

DSV and Cube do not match - generates Key Errors

Hi All,

This is strange behaviour, hopefully I can resolve it without rebuilding the cube and all the dimensions from scratch.

I have a cube with a fact table, and a number of dimensions, including an EventType and Event Date. (Event Type is "Sale", "Return", etc.etc, Event Date is the date it occurred)

When I created the DSV for this I accidently joined the fact table EventTypeID field to the EventTimeID on the Event Date dimensions. Not suprisingly this gave me a key error, as my EventTypeID on the fact table has values from 1-12, and the EventTimeID records start at 10000 and go upwards.

Having seen the error I went into the DSV and changed the relationship so that the Event Date dimension table was joined to the fact table on the correct fields. I then checked using SQL that there were no missing keys or other oddities on the base tables. I then manually did FULL process on all the dimensions, then tried to process the cube.

No dice. The error still occurs, it still claims that there is a missing key on the Event Date dimension, and a little further investigation shows that it is still using EventTypeID as the joining key. I have manually re-processed all dimensions etc, but to no avail.

How do I get the cube to pick up the changes in the DSV? What I don't want to have to do is throw it all away, as there are a fair number of hierarchies etc I would have to re-create.

Any help appreciated.

Richard R.

The Error reported back is:

Errors in the OLAP storage engine:
The attribute key cannot be found:
Table: dbo_tbl_Sales_FACT_LOAD, Column: EventTypeID, Value: 1.
Errors in the OLAP storage engine:
The attribute key was converted to an unknown member because the attribute key was not found.
Attribute Tbl Time DIM of Dimension:
Event Date from Database: ProtoType Cubes,
Cube: Sales And Mailings, Measure Group: Tbl Sales And Mailing FACT LOAD,
Partition: Tbl Sales And Mailing FACT LOAD, Record: 1.

Unfortunately, the metadata is pulled from the DSV and embedded in the higher level objects (dimensions, cubes, measure groups, etc.) when those objects are created. You need to recreate the objects for it to pickup the revised metadata in the DSV. Just updating the DSV doesn't do it alone.

Sometimes if it is a simple change, you can script out the objects into an XMLA script and then recreate it from the script rather than taking the time to drag & drop new objects around the system. However, you have to be careful and be knowledgeable. It is straightforward to do -- you are just editing a flat file, but if there are lots of references and changes I wouldn't go down that path.

Sorry to give you the bad news.

_-_-_ Dave

|||

As the late, great, Kenny Everett would have said

"Oh Bum!"

Thanks Dave,

Richard

|||

I hope somebody releases an XML refresh & validation tool for this issue. I have been troubleshooting this one and other datatype & DSV errors for awhile, and it affects multiple dimensions.

I have found that by correcting the relationship, then going into the cube's dimensions and clicking on the ... next to the dimension, you can reselect the key field (the same field, just click it again) and it seems to do the trick.

Otherwise, it's 'view code' and manually changing the DSV & the objects affected.

|||Wow, I just sank a few hours of time because of this issue...has anyone seen an refresh/validation tool like Andrew indicated? I could easily see how it could save tons of time!