*This is done using Microsoft Visual Studio and SQL Server 2014
Step 1: Preparation
Before you start, you need to download the DDL script called “WeekThreeScript” that is attached to this assignment (see attachment above). Download this DDL file to a local directory on your C: drive. Make sure you review the file and understand the DDL and DML that is included in the file before you create your SSIS solution.
Step 2: Execute SQL Task and Data Flow Task
The following provides the details on what your SSIS solution will perform:
Create an Execute SQL Task to execute the WeekThreeScript file you downloaded from above.
Next, create a Data Flow Task to accomplish the following:
Create an OLE DB Source to point to the CUSTOMER table.
Create an Aggregate Transformation that will SUM the sales by state from the CUSTOMER table.
Create an OLE DB Destination to insert the data from the Aggregate Transformation into the SALES_STATE table.
Important: Make sure you are using a precedent constraint between the Execute SQL Task and your Data Flow Task. Also, it is very important that you have your first name as part of each transformation name. For example instead of calling your Aggregator Transformation “Aggregate Customer Data,” you would call your transformation “Aggregate Customer Data – (your first name).” Your assignment will not be accepted and graded until you have correctly named all your transformations using this format.