I have started on a project work. One of the preliminary tasks is to extract data from excel tables and populate them into SQL Server Tables. One method was to do a copy and paste but the data size that I’m dealing with is in several millions of rows.
Therefore the alternative and easier solution is to use SQL Server Integration Services (SSIS) is a component of the Microsoft SQL Server database software that can be used to perform a broad range of data migration tasks. SSIS is a platform for data integration and workflow applications.
So I installed MS SQL Server 2014 Enterprise edition with Business Intelligence package in it. In another post I will detail the steps to create a simple ETL package but for now I will stick to the solution of this problem.
The solution is instead of using SQL Server object use the OLEDB Destination instead. Otherwise you may need to edit your local policy to grant the “Create global objects” permission in order for the SQL Server Destination to work properly i.e.
“Users who execute packages that include the SQL Server destination require the “Create global objects” permission. You can grant this permission to users by using the Local Security Policy tool opened from the Administrative Tools menu. If you receive an error message when executing a package that uses the SQL Server destination, make sure that the account running the package has the “Create global objects” permission.”
SQL Server Destination Reference http://msdn.microsoft.com/en-us/library/ms141095.aspx.