ETL from multiple Access databases -SSIS?

Howdy Folks,

I have an ETL project that I want to ask a question about. I want to loop through a folder containing several Access databases, and extract and load each one into a single SQL Server 2005 database. The table layout is the same for all the Access dbs, as well as the SQL db. The number of Access dbs in the folder, their records, and their names will change. Is there a way i can use the SSIS Foreach tool to move through the folder and set the connection string inside the loop?

Thanks for your reply,

Chris

Yes, you can do this.

Use the ForEach File Enumerator to enumerate your list of files. you can use wildcards (e.g. *.mdb)

Store the file path of each file in a variable. You can then use the contents of that variable to dynamically build your connection string using an expression.

Lots of information via Google about this stuff if you want to go hunting or ask here with any pertinent questions.

Regards

-Jamie

|||

Thanks Jamie (alot ) for your suggestion. I tried using the Foreach File Enumerator with the root path for the Access files and a *.mdb wildcard. Then I created a mapped string variable varFileNameto hold the file path. Inside the loop i have my Data Flow Task. In the data source for the task, I was using the variable in an expression as the ConnectionSting property which didn't work. So I added an OLE DB connection string in the expression like "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @.[User::varFileName] but i don't think that's the answer.

I've been googling on this but can't find solutions for multiple .mdb's, most of waht i find is dealing with flat files. Great blogs btw.

|||

Should be able to find all you need here: http://www.connectionstrings.com/

-Jamie

|||

Yep, i went there. Thanks Jamie. My string was ok. Actually what got me running was to set the Foreach Loop Container>Properties>Execution>DelayValidation = True. Works great running the SSIS package on the box with SQL server (as opposed to OLE DB) destinations.

Errors restoring Transaction Logs

Howdy,
I just accidently wiped out a table that had some pretty important data in
it. Can I restore it from the transaction log? Here's what I've tried so far
and I get the errors as stated below. I did do "net pause mssqlserver"
before running it.
USE master
RESTORE DATABASE cpts
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\RPCS.mdf'
WITH RECOVERY
RESTORE LOG cpts
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\Copy of
RPCS.ldf'
WITH RECOVERY, STOPAT = 'Apr 24, 2006 5:20 PM'
Erros
Server: Msg 3101, Level 16, State 2, Line 3
Exclusive access could not be obtained because the database is in use.
Server: Msg 3013, Level 16, State 1, Line 3
RESTORE DATABASE is terminating abnormally.
Server: Msg 3101, Level 16, State 2, Line 7
Exclusive access could not be obtained because the database is in use.
Server: Msg 3013, Level 16, State 1, Line 7
RESTORE LOG is terminating abnormally.
Thanks!!
David LozziYour database cpts will need to be changed to single user mode.
EXEC master.dbo.sp_dboption 'cpts', 'single user', true
(Be reminded to set it to false afterwards.)
Martin C K Poon
Senior Analyst Programmer
====================================
"David Lozzi" <dlozzi@.nospam.nospam> bl
news:u6smwZ$ZGHA.1196@.TK2MSFTNGP03.phx.gbl g...
> Howdy,
> I just accidently wiped out a table that had some pretty important data in
> it. Can I restore it from the transaction log? Here's what I've tried so
far
> and I get the errors as stated below. I did do "net pause mssqlserver"
> before running it.
> USE master
> RESTORE DATABASE cpts
> FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\RPCS.mdf'
> WITH RECOVERY
> RESTORE LOG cpts
> FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\Copy of
> RPCS.ldf'
> WITH RECOVERY, STOPAT = 'Apr 24, 2006 5:20 PM'
>
> Erros
> Server: Msg 3101, Level 16, State 2, Line 3
> Exclusive access could not be obtained because the database is in use.
> Server: Msg 3013, Level 16, State 1, Line 3
> RESTORE DATABASE is terminating abnormally.
> Server: Msg 3101, Level 16, State 2, Line 7
> Exclusive access could not be obtained because the database is in use.
> Server: Msg 3013, Level 16, State 1, Line 7
> RESTORE LOG is terminating abnormally.
> Thanks!!
> David Lozzi
>|||Pausing the service only prevents new server connections; existing
connections to the database are not affected.
You'll need to kill connections to the database before you can restore.
With SQL 2000 and above, you can do this easily with ALTER DATABASE ...
ROLLBACK IMMEDIATE:
ALTER DATABASE MyDatabase
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE
Note that you don't necessarily need to overwrite the existing database to
recovery the lost data. An alternative is to restore to a different
database name (and different file names) so you can extract the lost data.
However, if related tables have been modified since the problem, you might
find it easier to overwrite the existing database,
Hope this helps.
Dan Guzman
SQL Server MVP
"David Lozzi" <dlozzi@.nospam.nospam> wrote in message
news:u6smwZ$ZGHA.1196@.TK2MSFTNGP03.phx.gbl...
> Howdy,
> I just accidently wiped out a table that had some pretty important data in
> it. Can I restore it from the transaction log? Here's what I've tried so
> far
> and I get the errors as stated below. I did do "net pause mssqlserver"
> before running it.
> USE master
> RESTORE DATABASE cpts
> FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\RPCS.mdf'
> WITH RECOVERY
> RESTORE LOG cpts
> FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\Copy of
> RPCS.ldf'
> WITH RECOVERY, STOPAT = 'Apr 24, 2006 5:20 PM'
>
> Erros
> Server: Msg 3101, Level 16, State 2, Line 3
> Exclusive access could not be obtained because the database is in use.
> Server: Msg 3013, Level 16, State 1, Line 3
> RESTORE DATABASE is terminating abnormally.
> Server: Msg 3101, Level 16, State 2, Line 7
> Exclusive access could not be obtained because the database is in use.
> Server: Msg 3013, Level 16, State 1, Line 7
> RESTORE LOG is terminating abnormally.
> Thanks!!
> David Lozzi
>|||Seems you are trying to restore from the database files (mdf, ldf). That is
not how RESTORE work,
You restore from backup files (taken by the TSQL BACKUP command).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"David Lozzi" <dlozzi@.nospam.nospam> wrote in message news:u6smwZ$ZGHA.1196@.TK2MSFTNGP03.ph
x.gbl...
> Howdy,
> I just accidently wiped out a table that had some pretty important data in
> it. Can I restore it from the transaction log? Here's what I've tried so f
ar
> and I get the errors as stated below. I did do "net pause mssqlserver"
> before running it.
> USE master
> RESTORE DATABASE cpts
> FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\RPCS.mdf'
> WITH RECOVERY
> RESTORE LOG cpts
> FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\Copy of
> RPCS.ldf'
> WITH RECOVERY, STOPAT = 'Apr 24, 2006 5:20 PM'
>
> Erros
> Server: Msg 3101, Level 16, State 2, Line 3
> Exclusive access could not be obtained because the database is in use.
> Server: Msg 3013, Level 16, State 1, Line 3
> RESTORE DATABASE is terminating abnormally.
> Server: Msg 3101, Level 16, State 2, Line 7
> Exclusive access could not be obtained because the database is in use.
> Server: Msg 3013, Level 16, State 1, Line 7
> RESTORE LOG is terminating abnormally.
> Thanks!!
> David Lozzi
>|||Good catch, Tibor.
Hope this helps.
Dan Guzman
SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%235lJqxFaGHA.4292@.TK2MSFTNGP04.phx.gbl...
> Seems you are trying to restore from the database files (mdf, ldf). That
> is not how RESTORE work, You restore from backup files (taken by the TSQL
> BACKUP command).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "David Lozzi" <dlozzi@.nospam.nospam> wrote in message
> news:u6smwZ$ZGHA.1196@.TK2MSFTNGP03.phx.gbl...
>