SSIS - Default values not being copied

#1

I have a package which copies a number of tables from SQL 2000 to SQL 2005.

The tables are not being created with default values on their columns.

Any ideas please?

--
Michael Tissington
http://www.oaklodge.com
http://www.tabtag.com

#2

Hi Michael,

Welcome to use MSDN Managed Newsgroup.

Yes, tables will not be created with default value if you let Integration
Services make the new table. You should generate the SQL scripts for your
tablese and execute the scripts in SQL Server 2005 first to make the table
and then use Integration Services to transfer the data.

Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!

Sincerely yours,

Michael Cheng
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.

#3

Is this a BUG ?

It must be a BUG !

Why does SSIS copy everything (PK, FK etc) but not default values for
columns?

In SQL 2000 and dts packages this was not a problem, defaults were copied ?

What am I missing here ?

--
Michael Tissington
http://www.oaklodge.com
http://www.tabtag.com

"Michael Cheng [MSFT]" <v-mingqc (AT) online (DOT) microsoft.com> wrote

Quote:

Hi Michael,

Welcome to use MSDN Managed Newsgroup.

Yes, tables will not be created with default value if you let Integration
Services make the new table. You should generate the SQL scripts for your
tablese and execute the scripts in SQL Server 2005 first to make the table
and then use Integration Services to transfer the data.

Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!

Sincerely yours,

Michael Cheng
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no
rights.

#4

Hi Michael,

Would you please help me clarify "In SQL 2000 and dts packages this was not
a problem, defaults were copied"?

I have made the test with SQL Server 2000 below

1. Create a new table in the SQL 2000
------
create table test12
(
pid int,
idd int default 5
)
go
insert into test12(pid) values (1)
insert into test12 values (2,2)
insert into test12 values (3,4)
insert into test12(pid) values (5)
------

2. Create a new Package in DTS, When generating a new Transform Data Task,
it will be a prompt Dialog with the schema below for Destination database
------
CREATE TABLE [test12] (
[pid] int NULL,
[idd] int NULL )
------

You could see that in SQL 2000, it will also not generating the database
with default value.

3. Execute the DTS package and all data will be tranformed swimmingly to
the destination server.

Sincerely yours,

Michael Cheng
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.

#5

Michael

Wrong task. You should be using the Copy SQL Server Objects task.

Allan

"Michael Cheng [MSFT]" <v-mingqc (AT) online (DOT) microsoft.com> wrote

Quote:

Hi Michael,

Would you please help me clarify "In SQL 2000 and dts packages this was
not
a problem, defaults were copied"?

I have made the test with SQL Server 2000 below

1. Create a new table in the SQL 2000
------
create table test12
(
pid int,
idd int default 5
)
go
insert into test12(pid) values (1)
insert into test12 values (2,2)
insert into test12 values (3,4)
insert into test12(pid) values (5)
------

2. Create a new Package in DTS, When generating a new Transform Data
Task,
it will be a prompt Dialog with the schema below for Destination
database
------
CREATE TABLE [test12] (
[pid] int NULL,
[idd] int NULL )
------

You could see that in SQL 2000, it will also not generating the database
with default value.

3. Execute the DTS package and all data will be tranformed swimmingly to
the destination server.

Sincerely yours,

Michael Cheng
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no
rights.

#6

Allan,

Not the wrond task - I can only use Copy objects if I have sysadmin.

And besides in SQL 2000 this worked correctly!

--
Michael Tissington
http://www.oaklodge.com
http://www.tabtag.com

"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

Quote:

Michael

Wrong task. You should be using the Copy SQL Server Objects task.

Allan

"Michael Cheng [MSFT]" <v-mingqc (AT) online (DOT) microsoft.com> wrote in message
news:N$Klvm07FHA.4000 (AT) TK2MSFTNGXA02 (DOT) phx.gbl:

Hi Michael,

Would you please help me clarify "In SQL 2000 and dts packages this was
not
a problem, defaults were copied"?

I have made the test with SQL Server 2000 below

1. Create a new table in the SQL 2000
------
create table test12
(
pid int,
idd int default 5
)
go
insert into test12(pid) values (1)
insert into test12 values (2,2)
insert into test12 values (3,4)
insert into test12(pid) values (5)
------

2. Create a new Package in DTS, When generating a new Transform Data
Task,
it will be a prompt Dialog with the schema below for Destination
database
------
CREATE TABLE [test12] (
[pid] int NULL,
[idd] int NULL )
------

You could see that in SQL 2000, it will also not generating the database
with default value.

3. Execute the DTS package and all data will be tranformed swimmingly to
the destination server.

Sincerely yours,

Michael Cheng
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no
rights.

#7

Michael,

Here is some script to create a table

Then create an SSIS package to transfer the table to SQL 2005 ... the
default for Col1 (newid()) does not get created

---------------------
CREATE TABLE [dbo].[Table1] (

[Col1] [uniqueidentifier] NOT NULL ,

[Col2] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Table1] WITH NOCHECK ADD

CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED

(

[Col1]

) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Table1] ADD

CONSTRAINT [DF_Table1_Col1] DEFAULT (newid()) FOR [Col1]

GO

---------------------

--
Michael Tissington
http://www.oaklodge.com
http://www.tabtag.com

"Michael Cheng [MSFT]" <v-mingqc (AT) online (DOT) microsoft.com> wrote

Quote:

Hi Michael,

Would you please help me clarify "In SQL 2000 and dts packages this was
not
a problem, defaults were copied"?

I have made the test with SQL Server 2000 below

1. Create a new table in the SQL 2000
------
create table test12
(
pid int,
idd int default 5
)
go
insert into test12(pid) values (1)
insert into test12 values (2,2)
insert into test12 values (3,4)
insert into test12(pid) values (5)
------

2. Create a new Package in DTS, When generating a new Transform Data Task,
it will be a prompt Dialog with the schema below for Destination database
------
CREATE TABLE [test12] (
[pid] int NULL,
[idd] int NULL )
------

You could see that in SQL 2000, it will also not generating the database
with default value.

3. Execute the DTS package and all data will be tranformed swimmingly to
the destination server.

Sincerely yours,

Michael Cheng
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no
rights.

#8

Michael.

Bit confused now then

So you are not using the Copy Objects task? If that is the case and you
are only using the Data Flow task then?

If this is the case then in DTS you would have had to specify the
DEFAULT on the column when you created the destination table right?

Allan

"Michael Tissington" <mtissington (AT) newsgroups (DOT) nospam> wrote

Quote:

Allan,

Not the wrond task - I can only use Copy objects if I have sysadmin.

And besides in SQL 2000 this worked correctly!

--
Michael Tissington
http://www.oaklodge.com
http://www.tabtag.com

"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote in message
news:enTdI567FHA.1148 (AT) tk2msftngp13 (DOT) phx.gbl...

Michael

Wrong task. You should be using the Copy SQL Server Objects task.

Allan

"Michael Cheng [MSFT]" <v-mingqc (AT) online (DOT) microsoft.com> wrote in
message
news:N$Klvm07FHA.4000 (AT) TK2MSFTNGXA02 (DOT) phx.gbl:

Hi Michael,

Would you please help me clarify "In SQL 2000 and dts packages this
was
not
a problem, defaults were copied"?

I have made the test with SQL Server 2000 below

1. Create a new table in the SQL 2000
------
create table test12
(
pid int,
idd int default 5
)
go
insert into test12(pid) values (1)
insert into test12 values (2,2)
insert into test12 values (3,4)
insert into test12(pid) values (5)
------

2. Create a new Package in DTS, When generating a new Transform Data
Task,
it will be a prompt Dialog with the schema below for Destination
database
------
CREATE TABLE [test12] (
[pid] int NULL,
[idd] int NULL )
------

You could see that in SQL 2000, it will also not generating the
database
with default value.

3. Execute the DTS package and all data will be tranformed swimmingly
to
the destination server.

Sincerely yours,

Michael Cheng
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader
so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no
rights.

#9

I'm using Transfer SQL Server Objects task in an SSIS package.
And the ONLY problem I'm having with it, is that it does NOT copy the
DEFAULT column constraint. This worked correctly in SQL 2000 ... se my
previous post for a sample sql script.

--
Michael Tissington
http://www.oaklodge.com
http://www.tabtag.com
"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

Quote:

Michael.

Bit confused now then

So you are not using the Copy Objects task? If that is the case and you
are only using the Data Flow task then?

If this is the case then in DTS you would have had to specify the DEFAULT
on the column when you created the destination table right?

Allan

"Michael Tissington" <mtissington (AT) newsgroups (DOT) nospam> wrote in message
news:OKek$X#7FHA.1600 (AT) TK2MSFTNGP10 (DOT) phx.gbl:

Allan,

Not the wrond task - I can only use Copy objects if I have sysadmin.

And besides in SQL 2000 this worked correctly!

--
Michael Tissington
http://www.oaklodge.com
http://www.tabtag.com

"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote in message
news:enTdI567FHA.1148 (AT) tk2msftngp13 (DOT) phx.gbl...

Michael

Wrong task. You should be using the Copy SQL Server Objects task.

Allan

"Michael Cheng [MSFT]" <v-mingqc (AT) online (DOT) microsoft.com> wrote in
message
news:N$Klvm07FHA.4000 (AT) TK2MSFTNGXA02 (DOT) phx.gbl:

Hi Michael,

Would you please help me clarify "In SQL 2000 and dts packages this
was
not
a problem, defaults were copied"?

I have made the test with SQL Server 2000 below

1. Create a new table in the SQL 2000
------
create table test12
(
pid int,
idd int default 5
)
go
insert into test12(pid) values (1)
insert into test12 values (2,2)
insert into test12 values (3,4)
insert into test12(pid) values (5)
------

2. Create a new Package in DTS, When generating a new Transform Data
Task,
it will be a prompt Dialog with the schema below for Destination
database
------
CREATE TABLE [test12] (
[pid] int NULL,
[idd] int NULL )
------

You could see that in SQL 2000, it will also not generating the
database
with default value.

3. Execute the DTS package and all data will be tranformed swimmingly
to
the destination server.

Sincerely yours,

Michael Cheng
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader
so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no
rights.

#10

Hi Allan and Michael,

Thanks for the update.

Allan is right and I used wrong task at first.

Michael, I have reproduce it on my side as your have described. It looks
like an issue in the SQL Server 2005 Integration Services. I have submit
this to development team via internal resouces. You are also encouraged to
submit this via the FeedBack link below

http://lab.msdn.microsoft.com/produc...k/default.aspx

If there is any update for this issue, I will post here.

Sincerely yours,

Michael Cheng
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.