In this post l will be detailing the steps l used to ETL – Extract Transform and Load into a Microsoft SQL Server 2012 database from Washington State Corporations Data download. This showcases SQL Server inbuilt SSIS [SQL Server Integration Services] Data import and export wizard. We will be working with the text (tab limited) and not the XML version first.
1)
First download the text form data file from this link, click here.
Unzip the file as shown below.
2)
Create a database bucket and re-size to accomodate a large data import and reasonable file growth by value instead of percentage which is less efficient.
create database CorporationsDataWA;
USE [master]
GO
ALTER DATABASE [CorporationsDataWA]
MODIFY FILE ( NAME = N’CorporationsDataWA’, SIZE = 512000KB , FILEGROWTH = 102400KB )
GO
ALTER DATABASE [CorporationsDataWA]
MODIFY FILE ( NAME = N’CorporationsDataWA_log’, SIZE = 102400KB , FILEGROWTH = 102400KB )
GO
3)
Below show step by step screenshots of the ETL process. I also chose to save the SQL server generated workflow process for this ETL as a separate SSIS package for later review, and execution.
Notice the ETL error message generated. The default column width of 50 characters for the business name and address fields was not enough to handle the input data without truncation, l tried 1100 and finally settled for 200 characters for each of these name, address fields. And that worked. With a clear data dictionary one might have known beforehand the maximum characters for those data fields in the text limited input data file.
Arrows indicate important entry fields or steps. Click on images for a larger version.
B)
Importing Governing People (same process as above)
Below are screenshots showing one of the SSIS packages, with content(s) settings.
