When completed zip your project folder and name the zipped folder with your last and first name. Then upload that zipped folder on Canvas. Create al
When completed zip your project folder and name the zipped folder with your last and first name. Then upload that zipped folder on Canvas. Create all the packages under one project.
Please include truncate SQL commands in every package to delete any destination table and prevent duplication before running any task in your package.
Please name every data flow task, data source task, destination source task, and any other task or transformation module meaningfully so that their names are self-documented.
Please create the Movies database in SQLServer if you have not done yet.
Create an SSIS package that will export the data to the Movies database from the source tables and columns listed below found in the AdventureWorks database:
Source Tables: Person.Person, Person.EmailAddress, Person.StateProvince, Person.PersonPhone, Person.BusinessEntityAddress, and Person.Address.
Source fields: FirstName, LastName, AddressLine1, AddressLine2, City, StateProvince.Name, EmailAddress, and PhoneNumber.
The new table will contain every employees information from the tables above regardless of they have a phone number or email address.
Name the new table PersonBio in your movies database.
Name the Source Assistant PersonBioSource, and the Destination Assistant PersonBioDestinationName the package PersonBio.
Create an SSIS package and name is SplitByStateName. Use the query from question 1 as the source of your data and then use Conditional Splits to split your data. The condition to split the data into different tables would be the first letter of the StateProvince name. So, if a State or Province name begins with the letter A then all the data about that province or state will go to a destination table called StatesWithA etc. You will create 5 new destination tables (StatesWith A, B, C, Null, and Others).
You may have to place the condition for the states with NULL value first, before specifying other conditions.
The following screenshot shows a sample of the new destination or output table:
Create an SSIS package and name it ProductSalesInfo.
As for the data source, create a query from these tables: Production.Product, Production.ProductSubcategory, ProductCategory, Sales.SalesOrderHeader, Sales.SalesOrderDetail.
Show the following fields in the query: Production.Product.Name, Production.ProductCategory.Name AS [CategoryName], Production.Product.ListPrice, Sales.SalesOrderHeader.OrderDate (only the orders after 2004), Sales.SalesOrderDetail.OrderQty.
Create two Derived Columns in the destination table. Name the first derived column SalesAmount. You can calculate the sales amount by multiplying ListPrice and OrderQty.
Name the second derived column SalesQtr. The data for this column should be extracted from the OrderDate field using a month function. You will need to build an IF statement around the month function that will check and assign the quarter value. The conditions for the IF statements can be, IF month of the date is > 9 then the value of the SalesQtr is 4th qtr, IF the month of the date is > 6 then then SalesQtr is 3rd Qtr, IF the month is > 3 then SalesQtr is 2nd Qtr, and for all the other months SalesQtr is 1st Qtr.
Name the output table ProductSalesInfo.
The following screenshot shows a sample of the output table:
Create an SSIS package and name it SalesAggregate.
Use the tables from the previous question as your data source.
Select these fields for the source query: Production.Product.Name, Production.ProductCategory.Name AS CategoryName, Sales.SalesOrderDetail.OrderQty, Sales.SalesOrderDetail.UnitPrice.
Create a derived column called SalesAmount by multiplying Price with Qty. After adding the derived column task, add an Aggregate task in the package. Aggregate the fields in such a way that for each product name total quantity and total sales amount are shown.
Name the output table.
Using a Multicast task, export the data in a flat file and into a SQLServer table. Name the flat file SalesAggregate.txt and the SQLServer table SalesAggregate.
The following screenshot shows a sample of the output table:
