In this post I’m going to show you lightly the way that can be used to load massive data, from a flat file (CSV format) with customers and loading all the customer data to a SQL Server table, using Integration Services of the SQL Server 2008 or SQL Server 2008 R2.
Initial hardware & software requirements:
- Server with SQL Server 2008 or SQL Server 2008 R2 must be installed and must be reachable by the developer computer.
- The developer computer machine must have Visual Studio 2008 Shell for BIDS and must have access to the SQL Server machine server.
In the installation of SQL Server 2008 (R2) don’t forget to check Integration Services option checkbox (by default not checked):
In our sample let’s consider that we have Customers in a flat source file with format CSV and we want to load it fast in a SQL Server table in order to be able to do some queries.
So we have a CSV file (origin source) with the next data with 8.000 different customers:
And in the SQL Server we have a database TEMPTEST with an empty MyCustomers table (destiny source) that has the next structure:
CREATE TABLE MyCustomers
[First Name] nvarchar(50),
[Middle Name] nvarchar(50),
[Last Name] nvarchar(50)
–SELECT [ID], [First Name], [Middle Name], [Last Name] FROM [MyCustomers]
To do the data migration, we must load the data from the origin flat source to the destiny SQL server doing the next steps:
From Visual Studio 2008 (shell bids version):
File -> New -> Project
On the package we have selected Control Flow tab selected by default. We need to use a Data Flow Task, so we drag and drop a Data Flow Task control from the toolbox bar and we’ll have it like the screenshot:
If we create a Data Flow Task does make sense to access to the Data Flow tab that resides close to Control Flow tab. In the Data Flow tab the controls of the toolbox are different of the controls that are available in the Control Flow tab. We drag and drop two controls (origin and destiny source controls) and we linked them with the flow green arrow. The origin control is going to be the Flat File Source control. The destiny control is going to be an Ole DB Destination control in order to connect with the SQL Server database and specify the destiny table on it. After adding the controls the screenshot it has the next look and feel:
We must specify on first place, in the Connection Manager Area the connections that are need it on the controls. 2 Connections are defined: one for the origin flat file source control, and the other one for OLE DB destination control.
Setting Origin Flat File Connection:
Setting Origin Flat File Source Control:
Setting Destiny OLE DB Connection:
Setting Destiny OLE DB Connection Control:
Mappings are filled automatically if origin fields have the same name as destiny fields. If not the same, then the Mapping must be specified.
Testing the application in debug mode loads all data from file to SQL 8.000 records in less than one second:
Then we can go to the table and the 8.000 records will be returned by the query:
Written by Alejandro Royo