February 28, 2008 at 9:39 am
hi,
So here's the problem. I need a table from a access DB that our clients send us every month. I extract the table and load it into SQL Server 2005 DB. Problem is that Access table name changes every month, for example, for november, it's ARTB November 2007, for Decembar it's ARTB December 2007... you get the picture.
How can I solve this problem. I was thiking about two ways, but I don't know how to accomplish any..
1. Rename the table name in Access DB. But this would require use of a wildcard to access the table. I don't know how to use a wildcard in a rename table statement.
2. Change name of the table in SSIS somehow to it matches the table name in Access. I don't know how to do this either...
These SSIS packages have to be fully automated and run from a SQL box.
Please help...
------------
🙂
February 28, 2008 at 2:42 pm
Wow! This is a unique one. Usually I have seen people having problem when they have different filename according to date (today, tomorrow and so on) I have advise many people on using GetDate for that.
But for your case, I think we can use the same logic, but just more script to get the month as in your case here, it is the month that is changing. If you know soem coding, why don't you add the code to point to the source. All you have to do is use the same GetDate function, but then customize your code to meet the month.
For example today's file will say ARTPFebruary, so you can use get date to do that but match the February with if month is = 2, then February.
Hope you get the logic. I am not a good programmer but I think we got the logic here and this can be done if you can match the month by using getDate.:)
[font="Verdana"]Imagination is more important than knowledge-Albert Einstein[/font]
February 29, 2008 at 8:07 am
SQL King (2/28/2008)
But for your case, I think we can use the same logic, but just more script to get the month as in your case here, it is the month that is changing. If you know soem coding, why don't you add the code to point to the source. All you have to do is use the same GetDate function, but then customize your code to meet the month.For example today's file will say ARTPFebruary, so you can use get date to do that but match the February with if month is = 2, then February.
I understand your logic, but "getdate" funtion will not work, I thought about it too. The reason is that today is end of feburary, and we might be running December or January Data.
------------
🙂
February 29, 2008 at 8:12 am
Accessing VBA or VS automation for Office tools would give you the ability to cycle through the "tabledefs" collection in the Access file, or the table names as defined in an Access file. You would need to leverage something like that to find out the table name.
You can then use that info to push that into the right places in SSIS.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply