Part I:video link you may need to use:1.
https://www.youtube.com/watch?time_continue=3&v=DV4IMjnL_IU&feature=emb_logo2.
https://www.youtube.com/watch?v=Qq4yhhAk9fc&feature=emb_logo
Learning Objective
SSIS
Adding annotation
Label Objects
Error Log
Transformation Objects
Conditional Split
Derived Column
Union All **
Data Conversion **
Task:
Create an SSIS package using the data provided - NJCustomers.txt
1.Extract the data
2. Transform the dat
- Records with a state = "NJ" are considered "Good"data
- Records with a state that has more than 2 characters are considered "Bad"data
- Make the first and last names uppercase
3. Load
- Write the good data to a file called "NJGoodRecords.txt"
- Write the bad data to a file called "NJBadRecords.txt"
Add appropriate annotation to the design
Label all objects appropriately
What to submit:
1. The Project Solution
2. A Word document with screenshot of
- The SSIS Data Flow screen
Naming convention:firstnameLastname_BCS425_Lab3.fileExt
Part II (ASSIGNMENT 1.ZIP):
Valid format: Word document with file name in format “yourlastname_yourfirstinitial_BCS425_Assignment1.sql”
**See the attached file for assignment requirements *
´You will need to do the following:
Using your database incsserver.farmingdale.edu
´Create the tables according to the given schemas
´Create primary and foreign key constraints
´Write queries
´Backup the database
BCS425_Assignment_1.docx BCS 425 - Assignment 1 Valid format: Word document with file name in format “yourlastname_yourfirstinitial_BCS425_Assignment1.docx” IMPORTANT: Late assignments will be accepted for ONLY 1 week after the due date (with valid reason) but will have 50% taken off of the grade. Any assignments turned in more than a week late will not be accepted. · You will need to do the following: Using your database in csserver.farmingdale.edu · Create the tables according to the given schemas · Create primary and foreign key constraints · Write queries · Backup the database 1. Create the following tables with the given schemas: a. Sailors(sid: int, sname: nvarchar(50), rating: int, age: decimal(3, 1)) Primary key: sid b. Boats(bid: int, bname: nvarchar(50), color: nvarchar(50)) Primary key: bid c. Reserves(sid: int, bid: int, day: date) Primary key: sid, bid, day Foreign key:sid Sailors bid Boats Make sure you create the primary and foreign key constraints!!! 2. Load data into the BoatReservations DB. There is an SQL file on Blackboard that you can use to populate your BoatReservations DB. File Name: LoadBoatReservationsData.sql 3. Write the following queries: Q1 – Find the names of all sailors. There should NOT be any duplicates. Q2 – Find the names of sailors who have reserved boat number 103. Q3 – Find the sids of sailors who reserved a red boat (No duplicates). Q4 – Find the names of sailors who reserved a red boat (No duplicates). Q5 – Find the colors of boats reserved by Lubber. *Q6 – Find the names of sailors that have reserved at least one boat. *Q7 – Find the most recent boat reserved by each sailor. Save all these queries in one .sql file, please. LoadBoatReservationsData.sql /*Load Sailors*/ /*Sailors(sid: int, sname: nvarchar(50), rating: int, age: decimal(3, 1)) Primary key: sid*/ Insert into Sailors Values (1,'Lubber', 5, 35.3) Insert into Sailors Values (2, 'Smith', 3, 55.1) Insert into Sailors Values (3, 'Nolan', 1, 20.0) Insert into Sailors Values (4, 'Garner', 4, 62.2) Insert into Sailors Values (5, 'Kovak', 5, 33.0) Insert into Sailors Values (6, 'Kindley', 2, 33.4) Insert into Sailors Values (7, 'Presley', 5, 42.0) Insert into Sailors Values (8, 'Pichardo', 5, 35.2) Insert into Sailors Values (9, 'Wint', 3, 48.2) Insert into Sailors Values (10, 'Aaron', 4, 23.8) Insert into Sailors Values (11, 'Gaul', 5, 32.0) Insert into Sailors Values (12, 'Ray', 4, 34.0) Insert into Sailors Values (13, 'Ewart', 5, 56.7) Insert into Sailors Values (14, 'Max', 3, 45.0) Insert into Sailors Values (15, 'Dan', 3, 45.0) Insert into Sailors Values (16, 'Rinald', 2, 33.0) Insert into Sailors Values (17, 'Gilbert', 5, 44.0) Insert into Sailors Values (18, 'Punam', 5, 23.0) Insert into Sailors Values (19, 'Ricardo', 4, 33.5) Insert into Sailors Values (20, 'Shafiq', 5, 33.0) /*Load Boats*/ /*Boats(bid: int, bname: nvarchar(50), color: nvarchar(50)) Primary key: bid */ Insert into Boats Values (101, 'Lady Liberty', 'red') Insert into Boats Values (102, 'Misty', 'blue') Insert into Boats Values (103, 'Queen', 'black') Insert into Boats Values (104, 'Midnight Run', 'white') Insert into Boats Values (105, 'Blessed One', 'green') Insert into Boats Values (106, 'Jupiter', 'pink') Insert into Boats Values (107, 'Galaxy', 'red') Insert into Boats Values (108, 'Pride of the Sea', 'black') Insert into Boats Values (109, 'Fortune', 'white') Insert into Boats Values (110, 'Mad Max', 'red') Insert into Boats Values (111, 'Czar', 'green') Insert into Boats Values (112, 'Destiny', 'yellow') Insert into Boats Values (113, 'First Run', 'green') Insert into Boats Values (114, 'Silky Smooth', 'black') Insert into Boats Values (115, 'Mistletoe', 'blue') /*Load Reserves*/ /*Reserves(sid: int, bid: int, day: date) Primary key: sid, bid, day Foreign key:sid : Sailors bid : Boats */ Insert into Reserves Values (1, 103, '01/20/18') Insert into Reserves Values (5, 110, '02/02/18') Insert into Reserves Values (3, 105, '01/02/18') Insert into Reserves Values (4, 115, '02/02/18') Insert into Reserves Values (5, 103, '02/02/18') Insert into Reserves Values (6, 101, '01/28/18') Insert into Reserves Values (7, 109, '02/01/18') Insert into Reserves Values (8, 115, '02/06/18') Insert into Reserves Values (9, 106, '12/02/18') Insert into Reserves Values (9, 103, '01/02/18') Insert into Reserves Values (9, 102, '02/02/18') Insert into Reserves Values (10, 115, '12/15/18') Insert into Reserves Values (10, 113, '01/15/18') Insert into Reserves Values (10, 103, '02/05/18') Insert into Reserves Values (2, 105, '01/02/18') Insert into Reserves Values (2, 101, '02/02/18') Insert into Reserves Values (12, 104, '12/09/18') Insert into Reserves Values (12, 107, '01/19/18') Insert into Reserves Values (12, 114, '02/01/18') Insert into Reserves Values (11, 111, '01/17/18') Insert into Reserves Values (11, 107, '02/08/18') Insert into Reserves Values (14, 109, '02/02/18') Insert into Reserves Values (14, 110, '02/08/18') Insert into Reserves Values (15, 103, '01/04/18') Insert into Reserves Values (15, 109, '02/04/18') Insert into Reserves Values (5, 104, '02/10/18') Insert into Reserves Values (1, 107, '01/20/18') Go