Part 14 – S1 C1 P13 – ETL

CAUTION! This will be pretty dry fodder, but if you have any interest in databases, data and how to get the latter into the former this might be useful to you.

Extract, Transform & Load – You may see ETL in a lot of job descriptions in the data space, but at the end of the day it is just get some data, transform it into a format that you can work with & load it into a target, in this case SQL Server Database.

Our priority one is setting up a database. By now you will hopefully have downloaded SQL Server 2017/2019/20xx and are in a position to create a database – If you haven’t go back to the last post. This bit is pretty simple. Log in to the default server you’ve created on your machine, then left click on Databases and Click New Database.

Just give the Database a logical name – the one I’m using is called FM_Soccermetrics – and click OK – You will now be the owner of a pristine, but blank, database

So we have a database to put our data into, but how will we do that. That’s where our Visual Studio and SQL Server Data Tools installations will come in handy. Open Visual Studio (in my case 2017) and click the Create new project link in the middle(ish) of the screen.

In the window that opens you should have Business Intelligence as a clickable on the left hand Menu – click that and select the Integration Services Project (If you see SSIS anywhere that’s what this is) at the top – give it a logical name and click OK. If you don’t see Business Intelligence as an option on the left hand menu (or Analysis Services or Reporting Services) you need to make sure SSDT (SQL Server Data Tools) is installed for VS 2017/whatever version of Visual Studio you are using.

You should now have a blank project open, with an SSIS Package just called Package.dtsx – Rename that using right click – Rename – to something more logical for you (I’ve renamed mine PPFM_JM for PearceyPlays Football Manager – Journeyman). Next step is understanding the interface. the SSIS Toolbox should be open on the left hand side, and everything you need for this task is in there. First up click on data Flow Task and drag it into your main blank window. This is the task where you can move the extracted data we created in the last post. First up we will look at moving the csv data I created.

Click on the Data Flow Task – This will give you another blank window. First up you need to make a connection for the csv file we created. to do that right click in the connection managers window 20% up the screen and click New Flat File Connection.

Give it a descriptive name, Browse where you saved your csv file (You will need to change your file type from text files to csv files when you browse), make sure you set a Text Qualifier (Just under Format – Delimited) to a Double Quote (Stops issues parsing the data out when you have commas in the data itself), and then click Columns on the left hand side.

All the columns and data from your csv file you produced from the squad view should be in the preview window (Note I have 23 players in my First Team Squad, so there are 24 rows including the header row). Next Click Advanced under Columns.

Select all the columns in the column name window by clicking one one and pressing Ctrl + A (they turn blue as per the screenshot) – Change Data Type to Unicode String (This will allow for any non-latin text characters to come through with no issues) and change the width to 255 from 50 (50 characters is not long enough for some of the fields, I use 255 as that is the maximum you can get out of Excel, so I always use that as a default, you can use whatever you think is a big enough number for the data). Then Click OK – Your csv connection is now set up.

You will see your csv connection in your Connection Managers. Next you need to create a connection to your SQL Server Instance. Right Click again in the Connection Managers Window and click on New OLEDB Connection ( https://en.wikipedia.org/wiki/OLE_DB ) – Then click New. You will need to get your SQL Server Name you have created your new blank Database on, and you will need to paste it into the Server Name selector.

Select your Database, test the connection (Should be successfully, if not follow the error message) and click OK – Your OLEDB Connection (Connecting to your SQL Database) should now be in your Configure OLE DB Connection Manager Window. Select it, Click OK and it should now be in your main Connection Managers Window ready to use in the Data Flow Task. You can rename it (It defaults to Server.Database) if you wish by right clicking on it – rename.

You should be back in the Data Flow task blank window. We firstly need the Flat File Source (All text files like this are considered to be ‘Flat’ Files – https://en.wikipedia.org/wiki/Flat-file_database ) from the Other Sources Menu at the bottom of the SSIS Toolbox on the left of the screen. Click and drag that into you data flow task blank window, then click in the task itself (It can be good to rename these generic tasks so you can see what is happening in the task – I’m calling this csv Source). The Flat File Source Editor will come up.

Make sure Retain Null Values is ticked, then click columns on the left. You will get an error.

The problem with the headers coming from the file (So from Football Manager itself) is that there is the same header twice in the extract, and SSIS doesn’t like that as you can’t have the same header name twice in the same database table (Where this data will reside at some point) – As such I take you back to the original csv file I created in Excel. I have created unique headers for all the columns (All 291 of them) with better naming conventions so it will make our lives easier when the data is in the database. These headers are in the below code block – You can take these and replace the original headers in Notepad by Copy and Paste.

[training_medical_recommendation_dsc],[stats_youth_goals_cnt],[stats_youth_appearences_cnt],[disp_yellow_cnt],[ctrct_yearly_wage_rise_pct],[training_ind_workload_dsc],[attrib_work_rate],[ctrct_work_permit_req_ind],[ctrct_leave_at_end_of_ctrct_ind],[player_weight_amt],[player_scout_cons_dsc],[ctrct_waive_comp_for_mgr_role_ind],[ctrct_offered_new_wage],[ctrct_loan_wage_contrib_amt],[ctrct_net_wage_amt],[ctrct_wage_after_apps_num],[ctrct_wage_after_apps_amt],[ctrct_wage_after_league_games_num],[ctrct_wage_after_league_games_amt],[ctrct_gross_wage_amt],[attrib_vision],[player_value_amt],[ctrct_unused_sub_fee_amt],[player_unique_ID],[unknown_type],[ctrct_transfer_status_dsc],[player_transfer_options_dsc],[player_transfer_fees_rec_amt],[training_rating_num],[training_load_dsc],[training_intensity_dsc],[training_happiness_dtl],[training_happiness_dsc],[ctrct_top_scorer_division_bonus_amt],[ctrct_top_division_relegation_wage_drop_pct],[ctrct_top_division_promotion_wage_rise_pct],[injury_time_left_dsc],[attrib_throwing],[attrib_punching],[attrib_technique],[attrib_teamwork],[ctrct_division_team_year_bonus_amt],[stats_team_goals_scored_90_mins_num],[stats_team_goals_conceded_cnt],[stats_team_goals_scored_cnt],[stats_team_goals_conceded_90_mins_num],[attrib_tackling],[stats_tackles_game_num],[stats_tackles_won_cnt],[stats_tackles_comp_pct],[stats_tackles_attempted_cnt],[player_scout_pros_dsc],[attrib_strength],[ctrct_offer_status_dsc],[stats_player_starts_cnt],[attrib_stamina],[ctrct_squad_status_dsc],[player_squad_num],[stats_shots_attempted_90_min_num],[stats_shots_on_target_pct],[stats_shots_on_target_90_min_num],[stats_shots_on_target_cnt],[stats_shots_attempted_cnt],[player_short_term_plans_dsc],[ctrct_set_for_release_ind],[ctrct_sell_on_fee_profit_pct],[ctrct_sell_on_fee_pct],[player_secondary_position_cd],[player_second_nationality_cd],[ctrct_season_goal_bonus_num],[ctrct_season_goal_bonus_amt],[player_scout_style_dsc],[player_scout_min_wage_demand_amt],[player_scout_min_asking_price_amt],[player_scout_max_wage_demand_amt],[player_scout_max_asking_price_amt],[player_scout_best_role_cd],[player_scout_best_role_dsc],[player_scout_best_duty_dsc],[player_scout_best_position_cd],[stats_saves_tipped_cnt],[stats_saves_parried_cnt],[stats_saves_held_cnt],[attrib_rushing_out],[player_right_foot_dsc],[ctrct_relegation_wage_drop_pct],[ctrct_relegation_release_clause_ind],[player_birth_region_dsc],[attrib_reflexes],[disp_red_cnt],[injury_recurring_dsc],[ctrct_promotion_wage_rise_pct],[player_promises_cnt],[player_pref_squad_num],[player_pref_foot_dsc],[attrib_positioning],[training_pos_role_duty_dsc],[player_position_cd],[stats_points_game_num],[training_new_player_trait_dsc],[stats_player_of_match_cnt],[player_birth_city_dsc],[player_personality_dsc],[ctrct_wages_paid_by_sponsor_pct],[ctrct_gate_reciepts_to_player_pct],[ctrct_comp_for_mgr_role_pct],[attrib_penalty_taking],[stats_penalties_scored_pct],[stats_penalties_scored_cnt],[stats_penalties_taken_cnt],[attrib_passing],[stats_passes_comp_90_mins_num],[stats_passes_comp_num],[stats_passes_comp_pct],[stats_passes_att_90_mins_num],[stats_passes_att_num],[attrib_pace],[injury_overall_risk_dsc],[player_overall_happiness_dsc],[ctrct_overall_fee_amt],[ctrct_opt_ext_by_club_ind],[ctrct_ext_prom_final_league_games_num],[ctrct_ext_final_league_games_num],[ctrct_ext_avoid_relg_league_games_num],[attrib_one_on_ones],[loan_from_club_dsc],[stats_offsides_against_cnt],[attrib_off_the_ball],[stats_mistakes_made_cnt],[scout_shortlist_dyas_left_num],[ctrct_non_playing_job_offer_rel_clause_ind],[ctrct_non_prom_rel_clause_ind],[attrib_natural_fitness],[player_nation_cd],[player_national_team_cd],[player_nation_birth_cd],[player_morale_dsc],[stats_mistakes_leading_to_goals_cnt],[stats_mins_since_last_goal_num],[stats_mins_since_last_conceded_num],[stats_mins_game_num],[stats_minutes_num],[ctrct_fgn_min_fee_rel_clause_exp_date],[ctrct_fgn_min_fee_rel_clause_amt],[ctrct_dom_min_fee_rel_clause_exp_date],[ctrct_dom_min_fee_rel_clause_amt],[ctrct_dom_hgh_div_min_fee_rel_clause_exp_date],[ctrct_dom_hgh_div_min_fee_rel_clause_amt],[ctrct_mjr_cont_cup_min_fee_rel_clause_exp_date],[ctrct_mjr_cont_cup_min_fee_rel_clause_amt],[ctrct_cont_cup_min_fee_rel_clause_exp_date],[ctrct_cont_cup_min_fee_rel_clause_amt],[ctrct_min_fee_rel_clause_exp_date],[ctrct_min_fee_rel_clause_amt],[player_media_handling_dsc],[player_media_dsc],[training_match_sharpness_dsc],[training_match_sharpness_num],[training_match_load_dsc],[ctrct_match_hghst_earner_clause_ind],[attrib_marking],[player_long_term_plans_dsc],[attrib_long_throws],[attrib_long_shots],[loan_status_dsc],[loan_duration_date_range],[loan_exp_date],[player_position_level_dsc],[player_left_foot_dsc],[attrib_leadership],[ctrct_last_transfer_fee_amt],[stats_last_match_rating_num],[player_last_club_dsc],[stats_last_five_games_av_rating_num],[player_knowledge_level_pct],[attrib_kicking],[stats_key_tackles_cnt],[stats_key_passes_90_mins_num],[stats_key_passes_cnt],[stats_key_headers_cnt],[attrib_jumping_reach],[player_international_happiness_dsc],[stats_international_goals_conceded_cnt],[stats_international_goals_scored_cnt],[stats_international_caps_num],[ctrct_international_cap_bonus_amt],[stats_international_av_rating_num],[stats_international_assists_cnt],[stats_international_app_cnt],[stats_interceptions_90_mins],[stats_interceptions_cnt],[injury_susc_dsc],[injury_risk_dsc],[ctrct_injury_release_clause_ind],[injury_preventing_training_dsc],[injury_dsc],[injury_dt],[player_home_grown_status_dsc],[player_hierarchy_dsc],[player_height_amt],[attrib_heading],[stats_headers_won_pct],[stats_headers_won_90_mins_num],[stats_headers_won_cnt],[attrib_handling],[stats_goals_scored_90_mins],[stats_goals_conc_cnt],[stats_goals_scored_cnt],[ctrct_goal_bonus_amt],[player_general_happiness_dsc],[stats_games_won_cnt],[stats_games_missed_concurrent_cnt],[stats_games_lost_cnt],[stats_games_drawn_cnt],[stats_games_win_pct],[ctrct_type_cd],[attrib_free_kicks],[stats_fouls_conceeded_cnt],[stats_fouls_drawn_cnt],[attrib_flair],[attrib_first_touch],[attrib_finishing],[ctrct_FFP_contrib_dsc],[ctrct_fee_amt],[ctrct_offered_exp_dt],[player_EU_national_ind],[attrib_eccentricity],[player_club_home_grown_due_dt],[attrib_dribbling],[stats_dribbles_game_num],[stats_dribbles_cnt],[player_division],[stats_distance_90_mins],[stats_distance_num],[attrib_determination],[attrib_decisions],[player_scout_days_since_rpt_num],[player_birth_dt],[attrib_crossing],[stats_crosses_completed],[stats_cross_comp_pct],[stats_crosses_att_cnt],[attrib_corners],[ctrct_typ_dsc],[ctrct_start_dt],[ctrct_ext_after_prom_ind],[ctrct_expiry_date],[attrib_concentration],[stats_goals_conceded_90_mins],[attrib_composure],[attrib_communication],[attrib_command_of_area],[player_current_club_dsc],[player_clean_sheet_cnt],[ctrct_clean_sheet_bonus_amt],[stats_chances_created_90_mins],[stats_chances_created_cnt],[attrib_bravery],[player_based_in_nation_cd],[player_based_in_nation_division_dsc],[attrib_balance],[stats_season_av_rating_num],[stats_mins_goal_num],[stats_assists_90_mins_num],[stats_assists_cnt],[player_AM_style_dsc],[player_AM_best_role_cd],[player_AM_best_role_dsc],[player_AM_best_duty_dsc],[player_AM_best_pos_cd],[ctrct_asking_price_amt],[stats_app_cnt],[ctrct_app_fee_amt],[attrib_anticipation],[stats_league_goals_all_time_cnt],[stats_league_apps_all_time_cnt],[stats_goals_all_time_cnt],[stats_apps_all_time_cnt],[attrib_agility],[attrib_aggression],[ctrct_agent_ownership_pct],[ctrct_agent_nm],[player_age_num],[attrib_aerial_reach],[stats_aerial_challenges_90_mins],[stats_header_attempts_cnt],[training_additional_focus_dsc],[ctrct_active_rel_rls_clause_ind],[ctrct_active_non_prom_clause_ind],[attrib_acceleration],[player_nm]

Once you replaced the headers in the original file in Notepad there are a few little bits to do – go back to your csv Connection in the Connection Managers Window – remove it, and redo the flat file connection setup we went through earlier. Done that? Go back to the Data Flow Source task, click in it and now click columns – as your column headers are now unique you get no errors:

This is now ready for the next step – click OK

We now need to drag a OLE DB Destination task onto the pane – If you don’t see Other Destinations in the SSIS Toolbox scroll down until you do, click on the OLE DB Destination and drag it over – rename it (I usually rename this to the table I intend to load – we haven’t got one yet but I’ll call it dbo_stg_all_data – dbo means Database Owner and is a schema ( https://en.wikipedia.org/wiki/Database_schema ) and is used for permissions to certain tables normally – stg_ means Stage – its usual to call a table that is taking extract data in without any transforms a Stage table, but _raw is also used. Take the Blue line from the bottom of the csv Source task and pull it until it joins the new destination task you have – this is the data flow from the source to the destination. Click in the Destination task.

We don’t have a table yet, so click New next to the blank Name of the Table window. A Table Creation script with all the columns from the source is created.

Change the underscore between the dbo and stg in the CREATE TABLE top line to a full stop/period – Table names in SQL are in <server>.<database>.<schema>.<table>, but we know what server and database this is on from the OLE DB Connection so we only need the Schema (dbo) and the name – also add an _t to the end of the table name – this denotes it as a table and is best practice. Click OK, then click Mappings in the left hand window.

All the columns should be mapped from the Input (csv source) to the Destination (database table we just made) automatically as they all have the same names. We are good to go. Click OK. neither of the two boxes should have a Red X in them (If you do you need move your cursor over them to see what is wrong and debug it – outside the scope of this already very long post). Click in Control Flow at the top left of this pane and you should just see the Data Flow task in your window now. Right Click in that and Click Execute Task. Your computer should now be loading the data from the file to the database. the background should get some white horizontal lines on it and the Data Flow Task should have a Yellow timer on it, quickly changing to a green tick. This means you have been successful 🙂

Click in the Data Flow task and you should see the 2 windows, source and Destination again, both with green ticks plus the number of rows of data moved between both (In my case 23 rows of data, the header isn’t counted). To exit out of this execute state in Visual Studio go to the top of the screen – Debug – Stop Debugging. Time to go and have a look at the Database.

Press the + sign against the Database you’ve created earlier, then the + against tables, and lo-and-behold your table you just created in Visual Studio should be there. We need to check the data in the table. Right Click on your database Name and select new Query, then type this in:

SELECT * FROM [dbo].[dbo.stg_all_data_t]

SELECT is the way to look at data in SQL – * means everything – so this means Show me everything that is in the table I’ve just created and loaded for the first time. Press F5 or click on Execute near the top of the screen and you should see the data in teh Results window in the bottom half of the screen.

SUCCESS!!!!!!! We have our precious player data in the database ready for all the analysis we can think of. However this data isn’t structured. Next time I will take you through how we can structure this data (The Transform in ETL) to make our lives a lot easier down the road. Well done for getting to this point in the post, its to your credit 🙂