July 8, 2011 at 11:27 am
Hi,
I need to import Access db to SQL server using SSIS packages.Could some one tell me how to do these in the best possible way.I am having like 3 access databases.I know we can do it using import and export wizard but I need to do it using SSIS.
Thanks in advance....
July 8, 2011 at 11:43 am
ravi01031986 (7/8/2011)
I need to import Access db to SQL server using SSIS packages.Could some one tell me how to do these in the best possible way.I am having like 3 access databases.I know we can do it using import and export wizard but I need to do it using SSIS.
This may be the best place to start: http://msdn.microsoft.com/en-us/library/cc280478.aspx
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.July 8, 2011 at 11:48 am
Thanks Paul...Will go through it...
July 8, 2011 at 12:55 pm
ravi01031986 (7/8/2011)
Hi,I need to import Access db to SQL server using SSIS packages.Could some one tell me how to do these in the best possible way.I am having like 3 access databases.I know we can do it using import and export wizard but I need to do it using SSIS.
Thanks in advance....
I have migrated a lot of Databases from Access to SQL using DTS and SSIS.
Please consider that you Access Databases tables structures and queries may not be that good.
You may want to change the names of objects that have spaces or use reserved words. If you continue to use a Microsoft Access GUI after migrating to a SQL Server Backbend then you would need to create Views so that the GUI still works until you convert it to .NET or whatever.
Usually the Primary Key and non-clustered Indexes are wrong.
Quite often the Access Database is not in 3rd Normal Form.
The Access queries are often poorly written. There is a lot of clean up involved but if yo know some shortcuts you can come up with a more clearly defined query that performs better.
I have just touched on the surface. It is not difficult but if you know some tricks it will simplify the process.
In many cases there is some code in the VBA modules that should be moved to the back end, SSISS, Jobs, etc.
If you would like help let me know.
I would start with a table and then move to converting from a local Access Query (SELECT, INSERT, UPDATE, DELETE and MAKE Table Action Query) All are considered Action queries (DML) with the exception of a SELECT Query).
Best of luck.
Regards,
WC
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply