SSIS Tasks: Module 1: The AdventureWorks Inc.

Sales department would like for you to produce an extrac t for them to import into a proprietary system. Theyd like the extract to take th e Product_Transaction_History.csv and Product_Transaction_History_Archive.csv fi les (found inside the D:\SSIS Tasks\Module 1 Files folder) and pull out the prod ucts by quantity and grouped by ProductID. The extract layout should contain the following fields: ProductID Unique product identifier QuantityOrdered Total of this product that has been ordered LastTransactionDate The date of the last purchase of this product TotalCost The total cost of this product (Hint: Quantity * ActualCost) Expected Result The extract should be a comma separated value file (CSV) and the name of the fil e should have the current date appended to it. The extract should also be ordere d by ProductID in ascending order and TotalCost in ascending order. Module 2: The Adventure Works Inc. Marketing department has purchased a subscription to a zip code database for their direct mailers. They wish to import this data on a m onthly basis into their SQL Server automatically and completely refresh the tabl e. In this practice, you are going to take a flat file extract that contains eve ry zip code in the United States and load it into the AdventureWorks database. Y oure going to ensure that the table will be dropped and reloaded every time the p ackage is run. We will use this table in later practices as a lookup table, so p lease do not skip this practice! Module Requirements a. Create a new SSIS project and a package called Module2LabA.dtsx b. Create a package that accomplishes the following goals: i. Package executes the D:\SSIS Tasks\ Module 2 Files\Module2LabA-1.sql SQL script. Note: Append the table name in the script file with your name i.e. <yourname>Zip CodeLookup ii. Package bulk loads the D:\SSIS Tasks\ Module 2 Files\ZipCodeExtract.csv file into the newly created AdventureWorks.dbo.<yourname>ZipCodeLookup table. Expected Result Module 3: The AdventureWorks Inc. Marketing department would like to send catalogs to newl y formed corporations in the state of Florida. They only care about the companie s that are owned by Florida based residents. Their IT department has purchased a series of extracts from the state and marketing would like the data cleansed be fore it is loaded. The data is keyed in by users who do not necessarily have to follow data integrity standards. For example, a field like the City field is an optional field. In this practice, you will use a few of the key transformations in SSIS to cleanse and standardize the data. Rows that dont match the companies t hat marketing care about will be written to a queue for manual inspection. Module Requirements a. Create a new SSIS project and a package called Module3LabA.dtsx b. Create a package that accomplishes the following goals: i. Reads the D:\SSIS Tasks\ Module 3 Files\010305c.dat comma delimited file The file is comma delimited with a text qualifier of a double-quote (). The first row in the file is a header that contains the column names. ii. Write the data in the file to a new table (create by clicking the New Ta ble button in the destinations) called <yourname>MarketingMailing. iii. Ensure the zip code is only 5 digits always before writing to the table. iv. City and state is a required field for the mailing campaign. If the row does not contain a city or a state, look it up against the <yourname> ZipCodeLookup (using the zip code) table that you created in Module 2. If you still cant find it, move that one record to a <yourname>MailingErrorQueue

table to be manually inspected by the QA team. c. The AddressLine1 and AddressLine2 columns should be set to a string that is 150 characters in length (in the Connection Manager). Expected Result in Data flow task (Name this as Load Marketing Leads.) Module 4: The AdventureWorks Inc. Marketing department would like to ensure that the same file is never loaded twice into its MarketingMailing table. To do this, you prop ose that after you load a file, you archive the file into a directory and dynami cally rename the file. In this practice, you will use expressions and variables to start to make the package dynamic and to archive the file you will use the Fi le System task. Module requirements a. Clone the Module3LabA.dtsx package into another packages called Module4L abA.dtsx by copying and pasting. b. Modify the package so that accomplishes the following goals: i. Create a set of string variables: strFileName holds the full path and file name. set the value to D:\SSIS Tasks\ Module 4 Files\010305c.dat strArchiveFileName holds the full path and file name of the archive file. Set value to blank strSourceFolder holds the path to where the files originate from set the value to D:\SSIS Tasks\ Module 4 Files\ (its important that you keep the same case sensitive characters you see in these values) strArchiveFolder holds the path to where the file will be archived to set the value to D:\SSIS Tasks\ Module 4 Files\Archive (its important that you ke ep the same case sensitive characters you see in these values) strDBName holds the database youre loading set the value to AdventureWorks strServerName holds the server name youre loading set the value to <Servername>or your computer name. set the value for strArchiveFileName from properties window Set the value to @[User::strArchiveFolder] + "\\Processed_" + RIGHT(@[User::strFi leName], 11) this will be a dynamic value. ii. Setup the flat file connection manager to read its file name from the st rFileName variable iii. Archive the file to the archive path in the strArchiveFolder variable an d rename the file to Processed_FileName.dat like Processed_010305c.dat. iv. Setup the OLE DB connection manager to use the necessary variables to ma ke the connection dynamic. Expected Result in Control flow task Note: To confirm the package worked end-to-end, open File Explorer and browse to the D :\SSIS Tasks\ Module 4 Files\Archive directory. You should see one file in the d irectory called Processed_010305c.dat. In the D:\SSIS Tasks\ Module 4 Files\ dir ectory, the 010305.dat file should now be gone. If you were to execute the package again, the first step would fail since the fi le is no longer in the directory. You can however change the variable to a new f ile name in the Variables window and re-execute. Module 5: You are a DBA for the AdventureWorks Inc. and have grown weary of constantly man ually reconfiguring the package to load the corporation data for your Marketing department. Youd like to modify the package to read a directory and load every fi le that ends with a .DAT extension and then archive the file automatically. In t his practice, you will use the ForEach Loop container to create a loop in the co

ntrol flow. Youd like to also audit how many records are written each day into yo ur marketing table and when the load happened. Module Requirements a. Run the script D:\SSIS Tasks\ Module 5 Files\ Module5LabA-1.sql in Manag ement Studio to create the <yourname>MarketingMailingAudit table. b. Clone the Module4LabA.dtsx package into another packages called Module5L abA.dtsx by copying and pasting. c. Modify the package so that accomplishes the following goals: i. Create a set of integer variables: intAuditRowsInserted holds the amount of rows that were sent into the data flow. set the value to 0. ii. Loop through all *.DAT files in the path stored in the strSourceFolder v ariable. Change the value to D:\SSIS Tasks\ Module 5 Files\ iii. Add the necessary transform to count the rows in the data flow being wri tten to the destination. iv. Create an audit record in the <yourname>MarketingMailingAudit table with the following information: The current date The file name that was loaded The number of rows loaded Expected Result: Data Flow task Control flow tas k Module 6: While the AdventureWorks Inc Marketing department appreciates the data youve been providing them, they wish it were in a cleaner format to increase their custome r response rate. Currently the data is all upper case and they would prefer to s ee first letter in each word be upper case and the rest lower case. While youre c oding, you wish to also reset the auditing variable each time the Foreach Contai ner is looped to prevent tampering. Note: DELETE FROM MarketingMailing a. Clone the Module5LabA.dtsx package into another packages called Module6L abA.dtsx by copying and pasting. b. Add a script component and write a script to change the proper case Expected result