February 11, 2009 at 5:02 am
Hi,
I need to transfer data from one DB to another and I am hoping that SSIS will do what I need. I would really appreciate it if anyone can point me in the right direction.
DB1(source) has a table called STUDENTS with all student data eg. ID, Forename, Surname, Class
DB2(destination) has tables for PEOPLE with PersonID (PK auto gen), Forename, Surname and a table for LEARNERS with LearnerID (PK auto gen), PersonID (FK), Class
What would be the best way of doing this in SSIS so that the id's are correctly generated and matched?
I am half thinking of using a staging database if it is possible to overwrite the PK's in the destination tables (ideally in SSIS) and force it. But there must be a neater solution in SSIS??
Anybody got any suggestions?
February 17, 2009 at 7:37 am
OK so I've spent a couple of hours playing around with this.
I'm going to use a staging db for the time being (the example given above is much simplified) and its working ok.
BTW along the way I've found some useful SQL scripts on the web to help me with setting this up...
To get clean blank copy of the test DB, this will uncheck constraints and clean db:
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
GO
EXEC sp_MSForEachTable '
IF OBJECTPROPERTY(object_id(''?''), ''TableHasForeignRef'') = 1
DELETE FROM ?
else
TRUNCATE TABLE ?
'
GO
This query will drop all constraints
USE [DBNAME]
GO
-- Drop all constraints
declare @sql varchar(1024)
declare curs cursor for
select 'ALTER TABLE '+tab.name+' DROP CONSTRAINT '+cons.name
from sys.objects cons,sys.objects tab
where cons.type in ('C', 'F', 'PK', 'UQ', 'D')
and cons.parent_object_id=tab.object_id and tab.type='U'
order by cons.type
open curs
fetch next from curs into @sql
while (@@fetch_status = 0)
begin
exec(@sql)
fetch next from curs into @sql
end
close curs
deallocate curs
Constraints can be turned off per table:
ALTER TABLE tablename NOCHECK CONSTRAINT ALL
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
GO
Constraints can be turned off for all tables:
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
GO
This script will turn constraints on again
ALTER TABLE tablename CHECK CONSTRAINT ALL
and optinoally we can return seed values to their default if we changed this before.
EXEC sp_MSForEachTable ' IF OBJECTPROPERTY(object_id(''?''), ''TableHasIdentity'') = 1 DBCC CHECKIDENT (''?'', RESEED, 1000) '
GO
February 17, 2009 at 8:54 am
The simplest way to do this is create a backup of the db and restore it to the staging db. You can set this up as a script and schedule it using ssis if need be or just save it and run as needed.
February 17, 2009 at 12:05 pm
The other, almost as simple way is...
If you have Modify rights on the new database and SQL Management Studio (or you feel like scripting the mods, see frankieryan's post) or if you know a DBA that you can work with, is to have the Auto-Number feature turned off long enough for you to populate your tables. After you pupulate your tables, either you or the DBA can turn it back on.
February 18, 2009 at 4:35 am
Thanks anyway guys, have sorted it.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply