asked on

Integrating multiple data access database into one SQL database using SSIS package.

Hi,

We've got a multiple access databases each having 28 tables.
There are at least a total of 12 access database coming from different places.
The only common to these 12 database access are that they have the same table schema.
Each table has their own primary key that sets each record unique in its own containing access database.

The SQL server database will have the same schema as the access database.

I want to load and integrate all data table from 12 different access database into one sql database using one SSIS package.

Since each has their own data with primary key and each database access doesn't know the data from one another I am certain that some of the data might collide or overwritten by another since some of the table will have similar primary key.

I don't want them to override each other when moving the data to the sql server.

Anything I need to add/modify in the SQL to prevent colliding or overwriting one another?
How do I approach the SSIS package?

I probably need to assign new primary for each table.

Does anyone know how I can approach loading and integrating my different data access to the sql server using ssis package?

Thanks,

Roland

Russell Fox

I don't use SSIS, but here's a template of a SQL script you can use in SQL Server to pull the data in. This assumes that you have an identity field set up in SQL Server, but by tracking the [OriginalDB] name and and its original ID number, you can always trace it back to where it came from. If you truncate the SQL table each time it will pull in all information when you run it, or you can change the queries to only pull in new data (WHERE NOT EXISTS...):

--	Allow ad hoc distributed queries
--	CHECK CURRENT SETTINGS FIRST so you'll know whether or not you need this and how to change it back.
sp_configure 'show advanced options',1   
RECONFIGURE WITH OVERRIDE
GO
sp_configure 'Ad Hoc Distributed Queries',1   
RECONFIGURE WITH OVERRIDE
GO

--	First Access database:
--First table
INSERT INTO dbo.Table1
        ( [OriginalDB] ,
		  [OriginalDBID] ,
          [Field1] ,
          [Field2]
		  )
SELECT 'DB1' AS OriginalDB,
	[OriginalDBID] ,
	[Field1],
	[Field2]
FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0','Data Source="E:\DB1.mdb"')...Table1;
GO
	--Second table
	INSERT INTO dbo.Table2
			( [OriginalDB] ,
			  [OriginalDBID] ,
			  [Field1] ,
			  [Field2]
			  )
	SELECT 'DB1' AS OriginalDB,
		[OriginalDBID] ,
		[Field1],
		[Field2]
	FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0','Data Source="E:\DB1.mdb"')...Table2;
	GO

--	Second Access database:
--First table
INSERT INTO dbo.Table1
        ( [OriginalDB] ,
		  [OriginalDBID] ,
          [Field1] ,
          [Field2]
		  )
SELECT 'DB2' AS OriginalDB,
	[OriginalDBID] ,
	[Field1],
	[Fielt2]
FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0','Data Source="E:\DB2.mdb"')...Table1;
GO

--	Etcetera

--	Disallow ad hoc distributed queries (IF DESIRED - CHECK CURRENT SETTINGS)
sp_configure 'show advanced options', 1 
RECONFIGURE WITH OVERRIDE
GO
sp_configure 'Ad Hoc Distributed Queries', 0
RECONFIGURE WITH OVERRIDE
GO

Open in new window

ASKER CERTIFIED SOLUTION

PatHartman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rainsee65

ASKER

Thanks for both responses.

For Pat question about one time merge, it is a one time merge in a way. My plan is to create an a web app with a dropdown list that the user will select the datasource to import one at a time. The dropdown list will have the 12 datasources but those datasources will not change.

Once the datasource is selected I will run the SSIS package for each datasources.
My other question is I probably need to pass parameters to the SSIS package like the name of the datasources and the current table to be imported then retrieve those inside the SSIS package and use that to insert to the source data field in the sql table.

I am just a SSIS newbie and don't know yet how to programatically run the SSIS.

PatHartman

One-time means - convert all files ONCE. That is different from having to import replacement copies or add/change data. There would be no point in creating a web page if you only had to do this once.

So, is this a one-time effort or will you have to do this over and over again either on demand or on a schedule? If it is a repeat exercise, will you be replacing complete sets of data or will you be keeping the master data and adding to it or updating it? What about deletions?

Rainsee65

ASKER

This is a one time effort. All the different regions will upload their own data access database and will be in one folder. I probably will run the SSIS package each per database changing the connection per run.

PatHartman

OK. Do you still have questions about how to proceed. Both Russel and I have suggested using Access but you could use SSIS assuming you know how. If all it is doing is a straight copy/append, you will have conflicts and so will be better off doing what I suggested from Access. Or you can hang on and hope that some SSIS expert offers a solution.

Rainsee65

ASKER

I have a requirement to use SSIS.

Rainsee65

ASKER

This would be a one time merge.
I am going to try your suggestion on having a PK , loading the old source and old PK.
I will stick with SSIS package sine it is a requirement to what I am working on.

Thanks,

Roland