July 10, 2008 at 3:41 am
Issue:I am looking to automate a data migration from MS Access to Sql Server 2005.
I have the following options to accomplish it:
•SQL Server Migration Assistant
•Creating SSIS package and using import-export
Brief: Just to give a broader picture of the task,
•Daily copies of MSAccess .mdb file will be placed on a share.
•These copies need to be migrated from .mdb to SQL server and updated in the current database DAILY.
•Also, a new database needs to be created at the start of each month.
Current situation:
I am totally new to SSIS. For now I am able to migrate data using SQL Server Migration Assistant.
Any help as how to schedule the same for daily migration.
Also to create a database every month with month name.
Regards,
Dove
July 11, 2008 at 6:10 am
Within SQL Server Business Intelligence Development Studio, under "Project", is a "SSIS Import and Export Wizard...". I would recommend you start there and see what it does (fairly easy to follow along). You can go through the tutorials and walk-throughs after to clean it up, should you decide to. You can then schedule the package in a SQL Server Agent Job. I am sure a lot of other people have great suggestions about best practices and what not, but hopefully this will at least give you some direction!
July 11, 2008 at 8:11 am
Daily copies of MSAccess .mdb file will be placed on a share.
• These copies need to be migrated from .mdb to SQL server and updated in the current database DAILY.
• Also, a new database needs to be created at the start of each month.
Whoa there, take a step back and look at your architecture before continuing, please. Suggestions: 1. Once initial data is migrated to SS, have Access point to a SS staging database instead of to its own mdb file. This will make daily loads a LOT easier and consolidate general maintenance. 2. Consider using a timestamp/datestamp and views or snapshots based on month. Use a date dimension table if needed. Making a new database is going to make any reporting outside of month to month a nightmare. Quarterly or annually, for example.
I've had to deal with the aftermath of projects like you're describing and it isn't pretty. Please, spring for professional help if you don't have someone in house who can help you design an integrated solution.
It can be easy to have business users enter their data in Access and produce big picture reports with SS but the way you're approaching it is the hard, hard, hard way.
July 11, 2008 at 7:08 pm
I was going to suggest something similar to magarity. That is, evaluate why it is you are going to be doing the monthly procedure. If you are strictly doing it for backup purposes, then go for it, but if this is standard operating procedure for the application, then you may be doing it because you were using Access, since you're upgrading to SQL Server (even Express) you are not going to have the same restrictions.
July 13, 2008 at 1:27 am
Thanks for the responses.
The clients requires the daily migration from access to SQL. The access files get updated daily and these updated files are placed at share location. From here the .mdb file is converted to SQL.
And each month a new databse is to be created and the info stored in the consequent db of the current month.
For now, the plan is:
Step 1 - check if the db is of current month. IF not - create a new db. - Script below. DONE
Step 2 - migrate the mdb file to SQL
here there are two ways again -
1) SQL server Migration assistant for conversion. - We would create a project for the current month and run the job would run this project.
Issues - to Schedule SSMA and to save and run the projet each month from job. Automate it bacically
2) IMPORT EXPORT wizard. We can save it monthly as a SSIS package and run it from the job. - DONE
Issues: The job is failing with the following error:
Also, trying to automate creating of SSIS package each month. and getting rid of the error.
Any help from here on , esp the SSMS automation 🙂
Regards,
Dove
July 13, 2008 at 5:14 am
Hi Samata,
I would really appreciate if you could post the exact error that you get while using SSIS sked.
Regards,
Rajesh
July 15, 2008 at 5:18 am
Isn't there a way to partition the db table based on monthyear instead of creating a new db every month?
July 15, 2008 at 5:25 am
Error in the job -
Description: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. End Error Error: 2008-07-14 00:39:07.72 Code: 0xC0202009 Source: DTSCSR_NOV_07 Connection manager "DestinationConnectionOLEDB" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D
THe resolution is to save the SSIS package without the option of encrypt with password, etc. but i didnt find any such option. Have to work on it.
Currently i am busy with migrating nearly 40 db's from access to SQL. The import export fared pathetically, as right before migration it has issues. SSMA is better over importing yet, it has its own issues.
FOR now, my prome concerns are automatically creating the SSIS package each month.
To create the SSIS for importing data from access to SQL .
Regards,
Dove
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply