Needing help with ssis transfer

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SSIS and Import/Export (2008)

Needing help with ssis transfer

Author

Topic

shacky
Starting Member

16 Posts

Posted - 2010-11-07 : 23:36:53

Hi,

i am trying to transfer all the database objects from one database to another/ one server to another. I'm using Transfer SQL Server Objects Task to do the job for me. The database that I use is sql server 2000 database and i have set the compatibility to 100 to meet the ssis requirement.

The settings that i use are the following:
Both servers connect using sa account(Source/Destination) and uses the same database name
DropObjectFirst = true
Copy Data = true
CopySchema = true
UseCollation = true
IncludeDependentObjects = true
CopyAllObjects = false
I've selected all the tables/stored procedures/views to transfer
CopyObjectLevelPermissions = true
CopyIndexes = true
CoppyTriggers = true
CopyPrimaryKeys = true
CopyForeignKeys = true

However, when i ran the package, i got the following error message: "Cannot update identity column 'Id'"

Can someone assist me? Thanks

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-11-08 : 14:54:47

if you're copying everything, just restore a backup to the target server

shacky
Starting Member

16 Posts

Posted - 2010-11-09 : 04:57:27

Hi

I am supposed to replicate the database to a server overseas. Currently, I am trying out server to server locally (same subnet) but i always get "cannot update identity column" and "discover dependencies failed" error.

Regards

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-11-09 : 10:38:34

i dont use Transfer SQL Server Objects Task, but there must be a setting somewhere in it to enable identity insert, as there is in pump tasks and such

shacky
Starting Member

16 Posts

Posted - 2010-11-09 : 20:13:51

Any recommendations on what methods/ways i can use in order to make the transfer work? I've been stuck on this for weeks.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-11-09 : 21:45:48

is this a one time shot or does it need to recur on a scheduled basis?

shacky
Starting Member

16 Posts

Posted - 2010-11-09 : 21:55:27

it is on recur basis

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-11-09 : 22:04:57

ok, i set one up and tested it with identity columns. works fine for me.

try setting option
- Existing data = replace

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-11-09 : 22:06:53

if it's recurring, then this is probably the worst way you can do this.

had a look at trasnactional replication or snapshot replication?

shacky
Starting Member

16 Posts

Posted - 2010-11-09 : 22:31:11

Hi,

Do you have any links relating to replication setup? Thanks

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-11-09 : 23:10:00

start here

Post back with any questions. Replication is pretty easy to set up and mantain.

shacky
Starting Member

16 Posts

Posted - 2010-11-10 : 22:24:50

Hi,

I've found out that transaction replication is really useful when i test it out using a database with 1 table and synchronizing the same database within 2 servers.

I wanna ask that if let's say the table in server 1 has a new column or the database has a new table. Do i need to reset the publications settings? and will it affect the synchronization greatly?

shacky
Starting Member

16 Posts

Posted - 2010-11-11 : 00:39:07

Sorry for double posting. Currently i have "Cannot update identity column" error. All the data are not being replicated over to the other server. Do you know how to solve this problem? The database being use is from sqlserver 2000 ported over to sql server 2008.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-11-11 : 09:58:40

did u set existing data - replace option?

shacky
Starting Member

16 Posts

Posted - 2010-11-11 : 19:56:27

Hi,

I am trying out transactional replication/snapshot replication as you suggested.

I tried using transactional replication but it only copy tables with primary keys so i switched over to snapshot replication.

The "Cannot update identity column" comes from transaction replication. As for snapshot replication, right now everything is working fine, if i encounter any problem, i will post again.

Thanks

shacky
Starting Member

16 Posts

Posted - 2010-11-22 : 21:40:53

Hi, i am having this problem with snapshot replication. My database has grown too huge while running the replication. it has grown too huge and has hit the hard disk limit(40gb). Is there any way i can do to reduce them?

X002548
Not Just a Number

15586 Posts

Posted - 2010-11-22 : 22:07:17

How big is the log? How often do you do backups? Hoe often do you transaction log backups....when and how often do you drink tequila?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

shacky
Starting Member

16 Posts

Posted - 2010-11-23 : 01:39:31

The log file is 24.3 gb currently. The replication was scheduled hourly from 9am-6pm daily.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources