Create a new ssis project

Assignment Help Computer Engineering

Reference no: EM133540436

Assignment: Business Intelligence and Data Mining

Title: Extraction, Transformation, Loading (ETL) with SQL Server Integration Services (SSIS)

Instructions:

I. Create a new SSIS project.

II. Create a new database called ETL_Data in SQL Server.

III. Create an SSIS package for each of the following tasks:

1. PersonBio - This package will export the data to the ETL_Data 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 ETL_Data database.

Name the Source Assistant PersonBioSource, and the Destination Assistant PersonBioDestination

Name the package PersonBio.

2. SplitByStateName - This package will split the data from the PersonBio table into different tables within ETL_Data database based on the first letter of the StateProvince name.

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.

3. ProductSalesInfo - This package will calculate the sales amount and sales quarter for each product.

The data source for this package will be a query from the following tables: Production.Product, Production.ProductSubcategory, ProductCategory, Sales.SalesOrderHeader, Sales.SalesOrderDetail.

The query should show the following fields: 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.

4. SalesAggregate - This package will aggregate the data from the ProductSalesInfo table to show the total quantity and total sales amount for each product.

The data source for this package will be the ProductSalesInfo table.

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 SalesAggregate.

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.

IV. Save all of the packages in the same project.

V. Truncate the destination tables in each package before running the package.

VI. Name the data flow tasks, data source tasks, destination source tasks, and any other task or transformation module meaningfully.

Reference no: EM133540436

Questions Cloud

Create separate scripts within powershell that will perform : Create two separate scripts within PowerShell that will perform two types of backups of a given source directory and store the backed up files in a given target

What costs are associated for each task : What costs are associated for each task? Identify the estimated costs associated with each task. This may include personnel costs, materials, software licenses.

Discuss each component and specific information to included : Discuss each component and the specific information to be included within each section of the policy.

Discuss the implications of your research findings : Develop a 3 to 4 pages paper detailing problem addressed in the research paper and the proposed solution. Discuss the implications of your research findings.

Create a new ssis project : Create a new SSIS project. Create a new database called ETL_Data in SQL Server. Create an SSIS package for each of the tasks.

Prepare a report by interpreting the visualisations : Finish designing your visualisations, then prepare a report by interpreting the visualisations and integrating with theory from this subject

Create a new process and execute the command in new process : CSC 35500- With the exception of the cd and exit commands, each command should create a new process and execute the command in the new process.

Make recommendations on customer segmentation and targeting : Make recommendations on customer segmentation, targeting and product positioning; using the marketing mix to engage with the target segment; pricing tactics

What bundaberg can do to acquire new customers : What Bundaberg should do with customer relationship management and What Bundaberg can do to acquire new customers

User Account

All Pages