Need to import multiple tables from Access MDB

[sparkbyte]

I have SEVERAL Access Databases (100+) that I need to migrate the data to an MS SQL 2005 server.

There are about 20 -25 primary Access Applications, the reset of the files contain archived data from the primaries. I need to be able to have a script open each of the archive MDBs and import the data from a list of predifigned tables. Although it would be nice if the Script could open the MDB and and import the tables and columns that match on the SQL server side.

Here is what I have been able to piece togother so far.

What it is missing is the ability to open the mdb files and match the tables and columns against the SQL server DBs

/*
----------------------------------------------------------------------------
-- Object Name: Script
-- Project: Misc
-- Business Process: Supports SQL Server 2000 and 2005
-- Purpose: Capture the files in a specific directory with xp_cmdshell
-- Database: N\A
-- Dependent Objects: 
-- Called By: N\A
-- Upstream Systems: N\A
-- Downstream Systems: N\A
-- 
--------------------------------------------------------------------------------------
-- Rev | CMR | Date Modified | Developer | Change Summary
--------------------------------------------------------------------------------------
--
*/ 
SET NOCOUNT ON

-- 1 - Variable declarations
DECLARE @CMD1 varchar(5000) 
DECLARE @CMD2 varchar(5000)
DECLARE @FilePath varchar(200)
DECLARE @FilePath1 varchar(200)
DECLARE @SQL varchar(MAX)

-- 2 - Create the #OriginalFileList temporary table to support the un-cleansed file list
CREATE TABLE #OriginalFileList (
Col1 varchar(1000) NULL
)

-- 3 - Create the #ParsedFileList temporary table to suppor the cleansed file list
CREATE TABLE #ParsedFileList (
PFLID INT PRIMARY KEY IDENTITY (1,1) NOT NULL,
DateTimeStamp datetime NOT NULL,
FileSize varchar(50) NOT NULL,
FileName1 varchar (255) NOT NULL
)

-- 4 - Initialize the variables
SELECT @CMD1 = ''
SELECT @CMD2 = '' 
SELECT @FilePath = 'D:\AccessDBs\CentOps\Archived Databases\'

-- 5 - Build the string to capture the file names in the restore location
SELECT @CMD1 = 'master.dbo.xp_cmdshell ' + char(39) + 'dir ' + char(34) + @FilePath + char(34) + '\*.*' + char(39)

-- 6 - Build the string to populate the #OriginalFileList temporary table
SELECT @CMD2 = 'INSERT INTO #OriginalFileList(Col1)' + char(13) +
'EXEC ' + @CMD1

-- 7 - Execute the string to populate the #OriginalFileList table
EXEC (@CMD2)

-- 8 - Delete unneeded data from the #OriginalFileList
DELETE FROM #OriginalFileList
WHERE COL1 IS NULL

DELETE FROM #OriginalFileList
WHERE COL1 LIKE '%Volume%'

DELETE FROM #OriginalFileList
WHERE COL1 LIKE '%Directory%'

DELETE FROM #OriginalFileList
WHERE COL1 LIKE '%<DIR>%'

DELETE FROM #OriginalFileList
WHERE COL1 LIKE '%bytes%'

-- 9 - Populate the #ParsedFileList table with the final data
INSERT INTO #ParsedFileList (DateTimeStamp, FileSize, FileName1)
SELECT LTRIM(SUBSTRING (Col1, 1, 20)) AS 'DateTimeStamp',
LTRIM(SUBSTRING (Col1, 21, 18)) AS 'FileSize',
LTRIM(SUBSTRING (Col1, 40, 1000)) AS 'FileName1'
FROM #OriginalFileList

-- ********************************************************************************
-- INSERT code here to process the data from the #ParsedFileList table 
-- ********************************************************************************

	declare @StartRecord int
	declare @RowNum int
	declare @MaxRowNum int
	declare @FileName varchar(200)
	declare @SQLprintCount varchar(200)
	set @RowNum = 0 --initially index is set to 0
	Select @MaxRowNum = count(*) from #ParsedFileList
	Select @StartRecord = count(*) from dbo.Monthly
		WHILE @RowNum <= @MaxRowNum
			Begin
				set @RowNum = @RowNum + 1
					Select @FilePath1 = @FilePath
					select @Filename = FileName1 from #ParsedFileList
					where @RowNum = PFLID and FileName1 is not null
					Select @Filepath1 = @FilePath1 + @Filename
		--			print @FilePath1
						Set @SQL= '
						INSERT INTO [CentOps].[dbo].[Monthly]
								   ([OFFICE_ID]
								   ,[PREFIX]
								   ,[SUFFIX]
								   ,[SEQUENCE_NUM]
								   ,[TRANSACTION_DATE]
								   ,[GOVE_ID]
								   ,[SEC_OPT_ID]
								   ,[REMARK]
								   ,[OFFICE_DESC]
								   ,[SECTION_DESC]
								   ,[RESP_DESC]
								   ,[RESP_CODE]
								   ,[SECTION_CODE]
								   ,[TRANSFER_IN_OUT_FCO_ID]
								   ,[GOVE_FULL_NAME]
								   ,[TRANSACTION_CODE]
								   ,[FCO])
						Select
									[OFFICE_ID]
								   ,[PREFIX]
								   ,[SUFFIX]
								   ,[SEQUENCE_NUM]
								   ,cast([TRANSACTION_DATE] as datetime) as [TRANSACTION_DATE]
								   ,[GOVE_ID]
								   ,[SEC_OPT_ID]
								   ,[REMARK]
								   ,[OFFICE_DESC]
								   ,[SECTION_DESC]
								   ,[RESP_DESC]
								   ,[RESP_CODE]
								   ,[SECTION_CODE]
								   ,[TRANSFER_IN_OUT_FCO_ID]
								   ,[GOVE_FULL_NAME]
								   ,[TRANSACTION_CODE]
								   ,[FCO]
						FROM OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',''$FilePath$'';''admin'';'''',Monthly)
						'
						Set @SQL = Replace(@SQL,'$FilePath$',@FilePath1)
						--print @SQL
						Exec (@SQL)
			End


-- 10 - Drop the temporary tables
DROP TABLE #OriginalFileList
DROP TABLE #ParsedFileList

SET NOCOUNT OFF
--GO

--select count(*) from dbo.Monthly
--truncate table dbo.Monthly
--use master;
--exec sp_configure 'xp_cmdshell','1'
--reconfigure

Thanks

John Fuhrman
faq329-6766

 

[timfoster]

You'd be better off doing this either from Access or from an SSIS package rather than t-sql.

Personally, I'd go with Access as it's easier to control and debug than SSIS.

 

[sparkbyte]

Thanks, I have finally gotten it working with SSIS. There were date and time formatted fields in the Access DB that were causing problems with the import. I had to use SSIS's field conversion tools to get the data to import properly.

Thanks!!

Thanks

John Fuhrman
faq329-6766
thread329-1334328
thread329-1424438