How to change the name of a table within Access so I can use foreach loop to import data to SQL Server table

Question

Post reply

How to change the name of a table within Access so I can use foreach loop to import data to SQL Server table

pharmkittie

SSCertifiable

Points: 5775
More actions
February 12, 2016 at 1:24 pm

#399719

I have an SSIS package that imports data from various Access file sources (.mdb files in a directory that are looped through to import to a SQL Server table). Works fine if I open all the .mdb files and change the table name (one table only in each .mdb file) to a standard name. Is there a way to programmatically change the table names from within SSIS so that I can add that (script?) before the foreach loop? I've tried googling but the one example I found had a snippet of code in VB that I'm not experienced enough to employ. I am open to anything that would work from within SSIS (ideally) or even a shell script outside of SSIS. Thanks.
P.S. I cannot ask for the source of the Access files to change the table name.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Spiff SSCertifiable Points: 7766 More actions · Answer 1

Do these databases get generated new or overwritten or something?

If they don't, could you not create a standard named view in each database that your package can read?

Lowell SSC Guru Points: 323504 More actions · Answer 2

also, does the table inside follow some sort of naming convention, but is dynamic?

for example a table named [Invoices_2016-02_14] from yesterday, but it's [Invoices_2016-02_15] from today?

if you can determine a pattern, you can change the connection string dynamically, without changing the table name itself

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Phil Parkin SSC Guru Points: 246695 More actions · Answer 3

An alternative approach is to loop round the tables, whatever they are called, using a foreach loop. Here is an example.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

pharmkittie SSCertifiable Points: 5775 More actions · Answer 4

Thanks Phil, but where is the example? I do have a foreach loop that dynamically changes to connect in turn to each Access db's filename. The table name inside each of these files has the same name as the file. Thanks!

pharmkittie SSCertifiable Points: 5775 More actions · Answer 5

Thanks "Get me?" but how would I do that? Would I have to open each file to create the view? Thanks.

pharmkittie SSCertifiable Points: 5775 More actions · Answer 6

Thanks Lowell. The table is named the same as the file name. How would I use this dynamically ?

Phil Parkin SSC Guru Points: 246695 More actions · Answer 7

pharmkittie (2/15/2016)
Thanks Phil, but where is the example? I do have a foreach loop that dynamically changes to connect in turn to each Access db's filename. The table name inside each of these files has the same name as the file. Thanks!

Follow the hyperlink in my post.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

pharmkittie SSCertifiable Points: 5775 More actions · Answer 8

Thanks Phil! Sorry I didn't see the link. That's what I get for working in a dark room with the laptop on battery (dim screen). I will use this example whenever the power comes back on here. I'm sorry r=that I didn't find this example when I googled. It looks just right. Thanks again! I'll report back when I have it working.

pharmkittie SSCertifiable Points: 5775 More actions · Answer 9

I've been struggling with the example in the link you provided. The author leaves out steps (and refers to things in figure 3 that show something different than what the connection strings should be) that I need to understand it. There are some details skipped between hard coding the table names and changing to a variable for the table names.

I have a directory of Access mdb files with the same stem in the name but different dates. I managed to get a foreach loop to work to process through the files and put each file's one table's data into one SQL Server table but that was after I went in and changed all the individual table names (1 for each mdb file) to one standard name. The table name in each mdb file matches the name of the file so it varies with the date.

I am looking for an example that either renames the table in each file without opening the Access mdb file (a script at the o/s level would be fine) or something that I can do within SSIS. I've searched with variations of the string "change table name in Access without opening it) but I haven't found anything that does what I want to do. Thanks.

Phil Parkin SSC Guru Points: 246695 More actions · Answer 10

pharmkittie (2/19/2016)
I've been struggling with the example in the link you provided. The author leaves out steps (and refers to things in figure 3 that show something different than what the connection strings should be) that I need to understand it. There are some details skipped between hard coding the table names and changing to a variable for the table names.
I have a directory of Access mdb files with the same stem in the name but different dates. I managed to get a foreach loop to work to process through the files and put each file's one table's data into one SQL Server table but that was after I went in and changed all the individual table names (1 for each mdb file) to one standard name. The table name in each mdb file matches the name of the file so it varies with the date.
I am looking for an example that either renames the table in each file without opening the Access mdb file (a script at the o/s level would be fine) or something that I can do within SSIS. I've searched with variations of the string "change table name in Access without opening it) but I haven't found anything that does what I want to do. Thanks.

If it were me, I would not do it that way.

The link I provided seems to cover the requirement pretty well, so I do not understand why you would be having problems.

For each Access database, retrieve a list of table names contained and put those table names into an object variable called User::AccessTables.

Use that variable as a Foreach enumerator (so ... loop round the table names).

Map each table name to a variable User::TableName.

Use that variable in the source for your data flow ('SQL Command from variable' using User::SelectQuery)

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.