Question

Create an SSIS (ETL) package with the following instructions:

1. Load customer data from a text file to a data mart
2. Transform the customer's name to UPPER case prior to loading to the db.
3. Bad data - write to a log file
4. Good data - load into the db
Be sure to add labels to the ETL package design
Customer.Txt
CustomerCode,CustomerName,CustomerAmount,SalesDate
1001,Samuel,100.23,1/1/2017
1002,Joseph,300,2/2/2012
1003,Radhika,400,4/1/2018
1004,Tom,500,3/3/2014
1005,Mary,700,5/10/2014
1006,Randy,900,4/5/2016
1007,Shiv,1100,3/2/2018
1008,Lisa,1240,5/20/2017
1009,,1325,

Answer 1

To create an SSIS package for handling customer data, start a new SSIS project, use a Flat File Source, transform names to uppercase with Data Conversion, split the data into good and bad with a Conditional Split, and then load good data into the database and log bad data to a file. Don't forget to label each step for clarity.

Step-by-step explanation:

To create an SSIS package that processes customer data from a text file to a data mart while handling data transformation and logging, follow these steps:

• Start by creating a new SSIS project in SQL Server Data Tools (SSDT).
• Create a new data flow task in the package to handle the data loading.
• Use a Flat File Source to read the customer data from the text file.
• Insert a Data Conversion transformation to convert the 'CustomerName' column to uppercase.
• Add a Conditional Split to separate good data from bad data based on predefined conditions such as null fields or incorrect formats.
• For good data, connect the output to an OLE DB Destination to insert the data into the database.
• For bad data, connect the output to a Flat File Destination to write the log file.
• Add annotations to the data flow tasks as labels indicating their purpose for easy understanding and maintenance.

Be sure to configure each component with the correct connection managers, field mappings, and error logging options. Once the package is configured and tested, deploy it to the SQL Server Integration Services catalog, and schedule it to run as needed.