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.

error: the publication does not exist

I had a publication that I removed from the Local Publications folder but it still shows up in Replication Monitor (with a red 'X'). No jobs exist related to this publication either.

Everything is working ok, but I can't get rid of this from the Replication monitor.

Any help would be appreciated.

Thanks.

SQL 2000 ?

Get into Enterprise Manager.

Right Click on the server connection.

Select Properties , in the window select Replication.

Select Disable, it will remove all your references as to the Replication.

Let me know if it works.

|||if this is SQL 2005, look in distribution database for table MSmerge_publications and see if your deleted publication still exists. if so, delete it.|||

Yes, I'm using SQL Server 2005.

I tried opening the Distribution database via Management Studio to do what you said but when I click on a table the option to open it is greyed out. This is only the case for tables in the Distribution db and for the temp_db. I can open all tables everywhere else.

I tried looking into permissions but don't see any differences between settings on dbs that allow me to open tables and those that don't.

I'm kind of a noob to SQL Server 2005 so I may just be missing something simple.

Thanks.

Scott

|||

You'll have to try TSQL queries to access the table. Open a new query window:

use distribution

go

select * from MSmerge_publications

go

|||

Yes, that did it!

However, the problematic data was in another set of tables...

There was a record in the MSReplication_monitordata table and an associated record in the MSSnapshot_agents and MSSnapshot_history that were associated with the publication that no longer existed.

Not sure why this data was still in these tables but once I deleted these records, the error in Replication Monitor dissapeared.

Thanks!

|||

Hi, there,

I have the same problem. I could not find the distribution database which mentioned in the posts. When I tried to delete the publication, I got the publication " " does not exist.[SQL server error: 20026]. I tried to use sp_droppublication, it gave me error "the database is not enabled for publication". Nevertheless, I can see the publication in MS SQL Management Studio and Publication monitor with OK status.

Could you anyone has ideas to delete this publication? Thanks.

error: the publication does not exist

I had a publication that I removed from the Local Publications folder but it still shows up in Replication Monitor (with a red 'X'). No jobs exist related to this publication either.

Everything is working ok, but I can't get rid of this from the Replication monitor.

Any help would be appreciated.

Thanks.

SQL 2000 ?

Get into Enterprise Manager.

Right Click on the server connection.

Select Properties , in the window select Replication.

Select Disable, it will remove all your references as to the Replication.

Let me know if it works.

|||if this is SQL 2005, look in distribution database for table MSmerge_publications and see if your deleted publication still exists. if so, delete it.|||

Yes, I'm using SQL Server 2005.

I tried opening the Distribution database via Management Studio to do what you said but when I click on a table the option to open it is greyed out. This is only the case for tables in the Distribution db and for the temp_db. I can open all tables everywhere else.

I tried looking into permissions but don't see any differences between settings on dbs that allow me to open tables and those that don't.

I'm kind of a noob to SQL Server 2005 so I may just be missing something simple.

Thanks.

Scott

|||

You'll have to try TSQL queries to access the table. Open a new query window:

use distribution

go

select * from MSmerge_publications

go

|||

Yes, that did it!

However, the problematic data was in another set of tables...

There was a record in the MSReplication_monitordata table and an associated record in the MSSnapshot_agents and MSSnapshot_history that were associated with the publication that no longer existed.

Not sure why this data was still in these tables but once I deleted these records, the error in Replication Monitor dissapeared.

Thanks!