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 using multiple parameters in a SQL Statement

In an OLE DB Source in an SSIS package, we are having difficulties using multiple parameters in a SQL statement.

Using a single '?' works fine, but I've read that when you want to map more than 1 parameter you should use 'Parameter0, Parameter1, etc'.

The problem is that when we use Parameter0 and Parameter1 and then try to map it, it says that the query contains no parameters.

Can anyone help with the correct way to use multiple parameters in a SQL query that's part of an OLE DB Source task?

Thanks,

Mike

You still use multiple question marks (?) as parameter markers in the query.

For OLE DB, your parameter names should be "0", "1", "2", etc... Not "Parameter0", "Parameter1", etc...|||

I used to have a lot of problems with that when the query was to big or complex (Sub queries, etc). To solve it, and I use it as a standard, I place the query inside of a variable and then use an expression to make it parameterized.

Here is how:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=756434&SiteID=1

|||

Phil Brammer wrote:

You still use multiple question marks (?) as parameter markers in the query.

For OLE DB, your parameter names should be "0", "1", "2", etc... Not "Parameter0", "Parameter1", etc...

I disagree with that. Paramter0, Parameter1..etc is actually the default parameter name used by the OLE DB Source component; so yes you can use it.

|||

Rafael Salas wrote:

Phil Brammer wrote:

You still use multiple question marks (?) as parameter markers in the query.

For OLE DB, your parameter names should be "0", "1", "2", etc... Not "Parameter0", "Parameter1", etc...

I disagree with that. Paramter0, Parameter1..etc is actually the default parameter name used by the OLE DB Source component; so yes you can use it.

Kirk Haselden comments otherwise in his SSIS book... (Page 182) Granted, that's for Execute SQL Tasks.

Now I guess I'll have to go test it myself as that's what I've always used.|||Either will work. "Parameter0" or just "0".

The issue is likely as Rafael pointed out if you are using a complex query.|||

Neither Parameter0 or just 0 is working

This is the SQL in an OLE DB Source.

At one point, ?0 and ?1 caused the parameter mapping dialog to appear, but these others are not working.

Still scratching my head...

Mike

|||

Mike Batton wrote:

Neither Parameter0 or just 0 is working

This is the SQL in an OLE DB Source.

At one point, ?0 and ?1 caused the parameter mapping dialog to appear, but these others are not working.

Still scratching my head...

Mike

Can you post your query?|||

With the following options, here's what happens when I click on the parameters button:

Select * from TEW_D_DT where dt_key = Parameter0 and cal_yr_id = Parameter1

Message says that the query contains no parameters

Select * from TEW_D_DT where dt_key = ?0 and cal_yr_id = ?1

This one causes the parameters mapping dialog to open and show two parameters. The problem here is that I set them and then say "OK" to the OLE DB Source Editor and I get this:

Must delcare the scalar variable "@.P10"

Select * from TEW_D_DT where dt_key = ?

This works, but it's only one parameter.

|||

Mike Batton wrote:

With the following options, here's what happens when I click on the parameters button:

Select * from TEW_D_DT where dt_key = Parameter0 and cal_yr_id = Parameter1

Message says that the query contains no parameters

Select * from TEW_D_DT where dt_key = ?0 and cal_yr_id = ?1

This one causes the parameters mapping dialog to open and show two parameters. The problem here is that I set them and then say "OK" to the OLE DB Source Editor and I get this:

Must delcare the scalar variable "@.P10"

Select * from TEW_D_DT where dt_key = ?

This works, but it's only one parameter.

Use question marks - always, and don't number them.

select * from tew_d_dt where dt_key = ? and cal_yr_id = ?

Then, in the parameter mappings, you can use "Parameter0" or "0" for the parameter name to map to your variable.|||

Cool, that worked.

Shouldn't I also be able to use the ? in the select part of the statement?

Mike

|||

Mike Batton wrote:

Cool, that worked.

Shouldn't I also be able to use the ? in the select part of the statement?

Mike

You mean to dynamically select columns? That would not be a good idea as it will change metadata.|||

What about in the case part of a select statement... should that work or not?

Below is part of the actual query that we're trying to get to work.

Mike

select 'ent' AS level_desc,

sku.new_concept_cd AS code_1,

sku.color AS color_name,

case when sls.date_key = ?

then

sum(isnull(sls.total_quantity,0) -

(isnull(sls.store_return_total_quantity,0) +

isnull(sls.catalog_return_total_quantity,0) +

isnull(sls.concierge_return_total_quantity,0))) else 0 end as sales_lw,

sum(isnull(sls.total_quantity,0) -

(isnull(sls.store_return_total_quantity,0) +

isnull(sls.catalog_return_total_quantity,0) +

isnull(sls.concierge_return_total_quantity,0))) as sales_life,

from edw.jjwhse.tew_l_stock_status_sku sku

left outer join

edw_jj.sales.vew_r_sls_sku_loc_wk sls

on sls.sku_key = sku.sku_key

where sls.date_key <= ?

and sku.sku_type_desc = 'sales'

and sls.loc_key not in (6,9,10,11,12,13,14,106)

and sku.new_concept_cd is not null

group by sku.new_concept_cd, sku.color, sls.date_key

|||

Mike Batton wrote:

What about in the case part of a select statement... should that work or not?

Try it... I don't know. If it doesn't work, then as Rafael stated, you can build your query first as an expression in a variable. Build it there first, then use that variable in the OLE DB connector.|||

Anything that changes the structure of the dataflow pipeline will not work; this is in the select part of the query: adding/removing columns; changing data types; changing column names, etc.

Perhaps if you provide an example of what you need to accomplish; somebody around here could help you.