ETL Magic with SSIS – Part I  Ron Johnson 

Introduction  This paper will demonstrate a process for loading a comma‐delimited data file into a staging table for further processing  then validating the data and moving specific data into host tables.  There are several excellent papers and tutorials  individually describing simple data load processes, error‐handling, logging, etc. using SSIS but I haven’t found a source  demonstrating a process start‐to‐finish which motivated me to write this paper.  The intended audience includes  beginning SSIS developers, DBAs, and anyone with a desire to experience the magic of data management with this  remarkable tool.  A significant challenge in producing this paper was the identification of a data set that was non‐trivial, not proprietary,  yet allowed the demonstration of important techniques available in SSIS.  I settled on using a data set I downloaded  from a U.S. government WWW site containing data produced using taxpayer dollars thus publically available.  The data  set is the Worldwide M1+ Earthquakes, Past 7 Days csv file available at http://explore.data.gov/catalog/raw/.    I’ve included ample screen shots where appropriate (please note that the following screen shots are of my local system’s  directory structure, etc.) as well as attempted to describe the process as clearly and concisely as possible without  insulting anyone’s intelligence; however, be forewarned that finding the balance between too much or not enough  detail is a challenge unto itself.    In developing this paper it quickly become apparent that a complete treatment of the sample ETL project would be too  involved for a single paper; therefore, I divided the paper into two parts where I believe the division makes sense.  Taken  together the two parts of the paper provide, in my opinion (biased as it is), a thorough example of a non‐trivial ETL  project using “real” data.  For the record, I use the terms Task and Transformation synonymously SSIS purest would say  that this is incorrect I accept their judgment (although, I would argue that the definition of a “task” may generically  include a transformation.  For the novice, a Task acts upon the Control Flow while a Transformation acts upon the Data  Flow). 

Preliminaries: Setting up SQL Server Configuration  Depending on the environment some values used by the package may change when the package is moved from  development to production.  Personally, I store my packages in SQL Server after development and have no desire to  revisit the package in BIDS to change connections or other values.  Again, there are choices mine is to use SQL Server  configuration rather than environment variables, XML, or registry settings, etc. it’s just my choice it doesn’t have to be  yours.  To setup SQL Server configuration we first want to create an Environment Variable to define the connection string to the  package’s configuration database.  Call the Environment Variable SSISConfigurationDatabase (or, whatever strikes your  fancy) and set its value to the connection string for the server / instance / database where you will store the other  configuration values.   What does this do?  It allows us to control where the package will look for its configuration data. 

ETL Magic with SSIS   Ron Johnson   

Page 1 

With the Environment Variable defined we now create a new Integration Services Project in BIDS.  Create a connection  manager for the database that either contains or will contain the configuration table.  In my case the table already exists  because I created it for other projects.  If you don’t have a table we’ll create one in a few minutes.    With the connection manager defined right‐click on the design surface and select Package Configurations…, ensure that  the Enable package configurations checkbox is checked.  Select the Add… button then configure the selections as shown  below: 

  When you click the Next > button the workflow will ask you to “Select the property which will have its value set by the  configuration.” as shown below: 

  ETL Magic with SSIS   Ron Johnson   

Page 2 

Select the ConnectionString property belonging to the connection manager we just created.   That’s it!  We now have an externally configurable connection to the database that stores the other configuration  properties for the package.   

Priming the Process  Although configuring a data source in SSIS to retrieve the data file from a WWW site is a straightforward process the  more realistic scenario, in my experience, is that a client delivers a data file via ftp, web service, etc.; therefore, our  example will assume that the data file is delivered to a specific directory designated to receive ftp transmissions.  So, we want to look in a directory for a file that will be processed by our package.  Do we know when the file will be  delivered? Do we know when the transmission of the file will be complete?  If so, then the problem is somewhat  simplified because we just pick the file up out of the directory; however, again, in my experience, the file may be there  or it may be getting there giving us the option to either fail the package if the file is not available executing the package  again at a later time or we can wait for the file to be ready.  As with any programming endeavor there is always more than one approach to solving a problem.  In this case I’ve tried  a few approaches before I found a very reliable component called the File Watcher Task contributed to the SSIS  community by Konesans and available here (please note the component’s accompanying license).  This clever little  component greatly simplified my packages allowing execution at time t while the data source file may not be available  until sometime after t.   First, we need to create a Flat File connection manager to our data.  Enter double quotes (“) in the Text qualifier  property’s textbox and check the Column names in the first data row checkbox as shown below: 

 

ETL Magic with SSIS   Ron Johnson   

Page 3 

Take a peek at the columns by selecting the Columns view: 

  Not bad, with very little effort SSIS has parsed the data file for us.  If we wanted to change the names of the columns or  the data types assigned to them we select the Advanced view: 

  Personally, I prefer to give SSIS the first shot at defining the data types.  To accomplish this I select the Suggest Types  button: 

ETL Magic with SSIS   Ron Johnson   

Page 4 

  I change the Number of rows value to 1000 then click OK.  Examine the individual column definitions; it appears that everything is acceptable except for the Datatime column  which is defined as a string(42).  Before we leave the Advanced view note that this is where the names of the columns  can be modified and columns can be added or deleted.  Click on the Preview view to examine the expected output: 

  From this we can see why the Datetime field is string(42). 

Storing the Flat File ConnectionString in the Configuration Database  To access the Package Configurations Organizer right‐click on the Control Flow design surface then select the Add  button.  ETL Magic with SSIS   Ron Johnson   

Page 5 

  Earlier we defined the Environment Variable that stores the location of the configuration database now we will create a  new table to store the package configurations like the FlatFile Connection Manager.    If you have previously created a Configuration table select it; otherwise select the New… button.  The Configuration  table is defined as:  CREATE TABLE [dbo].[SSIS Configurations] 

(    ConfigurationFilter NVARCHAR(255) NOT NULL,    ConfiguredValue NVARCHAR(255) NULL,    PackagePath NVARCHAR(255) NOT NULL,    ConfiguredValueType NVARCHAR(20) NOT NULL  )    The Configuration filter is an identifier allowing the segregation of package configurations within the table; type in a  useful identifier for this value.  The storage system for package configuration values is complete.    Select the values from the FlatFile Connection Manager to store in the configuration table as shown below: 

ETL Magic with SSIS   Ron Johnson   

Page 6 

  The Package Configuration Organizer should now look like this: 

  Execute the following query to examine the configuration values stored in the configuration table for our package:  SELECT [ConfigurationFilter] ,[ConfiguredValue] ,[PackagePath] ,[ConfiguredValueType] FROM [test].[dbo].[SSISConfigurations] WHERE Configurationfilter like 'Earth%' ConfigurationFilter  EarthQuakeData 

ConfiguredValue  C:\SSIS Paper\worldwide M1+ Earthquakes  ‐ 7 Days.txt 

PackagePath \Package.Connections[EarthquakeData].Properties[ConnectionString] 

ConfiguredValueType String

Begin When Ready  Follow the directions for installing the Konesans File Watcher Task component and adding it to the toolbox.  Then, drag a  File Watcher Task onto the Control Flow design surface.  My policy is to rename a component as soon as I drop it onto  ETL Magic with SSIS   Ron Johnson   

Page 7 

the design surface because I find this approach helps me stay organized so rename the File Watcher Task.  While we’re  at it, let’s set the file name Filter, the Path, and change the value of the Find Existing Files property as shown below: 

  Once the File Watcher Task is configured you can run the package; the File Watcher Task will find the EarthQuakeData  file in the specified directory and turn green – Success!  If the data file is not found, then the File Watcher Task will turn  yellow waiting for the file to become available making this component a very welcome addition to the SSIS toolbox. 

Staging  Now that our package will control its own execution based on when a file is available from a client our focus shifts to the  processing of the data into a staging table.  First, drag a Data Flow Task onto the design surface; connect the  EarthQuakeData File Watcher Task by dragging its green output arrow onto the Data Flow Task.  The Control Flow  design surface should look like this: 

ETL Magic with SSIS   Ron Johnson   

Page 8 

  Double click on the Data Flow Task to switch to the Data Flow design surface.  Our destination is a SQL Server instance  and the source is a flat file so drag both from the toolbox onto the design surface.   Connect the Flat File Source to the  SQL Server Destination by dragging its green output arrow onto the SQL Server Destination.  The Data Flow design  surface should look like this: 

  Notice the red circle with the ‘X’; this is telling us that the component is not properly configured.  Because SSIS uses the metadata from upstream components to seed the configuration values of the downstream  components we start configuration with the Flat File Source component.  Double click on the component to popup this  dialogue pre‐populated with the EarthQuakeData connection manager we previously created: 

ETL Magic with SSIS   Ron Johnson   

Page 9 

  Press the Preview button to view the data from the flat file.    Click on the Columns view to select / unselect and rename columns for output.  Click on the Error Output view to define the action the component should take if an error occurs during the processing of  a row of data from the flat file input.  For our example we will accept the defaults.  Double click the SQL Server Destination component.  As mentioned above SSIS uses the metadata created during the  configuration of the Flat File Source to seed the configuration values of the SQL Server Destination.  Create a connection  manager for the database where the Earthquake data will be loaded.  If the data is to be loaded into an existing table,  then select that table from the Use a table or view drop down; otherwise, press the New button which will generate the  Create Table script as shown below: 

ETL Magic with SSIS   Ron Johnson   

Page 10 

  Add an ID column of type int IDENTITY(1,1) to the CREATE TABLE command.  The SQL Destination Editor should look like this: 

  Click on the Mappings view to see how the output columns from the Flat File Source will map to the columns in the  database table.   Take a look at the other available configuration settings in the Advanced view, then click the OK button.  (Note: this connection manager may also be included in the package configurations as simply as the Flat File Connection  Manager; I leave doing so to the reader.)  Run the package.  The two tasks on the Data Flow design surface should have turned green – Success!  Examine the  database; the flat file data is loaded in the table SQL Server EarthQuake Data Table.    ETL Magic with SSIS   Ron Johnson   

Page 11 

  With just a few mouse clicks we have created a process to read the source file into the database.  Work remains to be  done so drop the SQL Server EarthQuake Data Table containing the imported data. 

Summary  Pretty, pretty, pretty straightforward.  Again, in practice I do not like to modify a tested package which is why I use the  package configurations as I do.  I would argue that externalizing the package configurations is an “SSIS Best Practice”.   The ETL tasks included in Part I of this paper are an introduction to the power and simplicity of SSIS; Part II takes this  example to the next level introducing validations and transformations, error handling, and distribution of the data.                 

ETL Magic with SSIS   Ron Johnson   

Page 12