SSIS – ELT New Customers
I’ve created an SSIS ELT task in Visual Studio for a scenario where new customers must be inserted into a database. Instead of manually inserting each customer into the database, I’ve designed an Excel .xlsx file to facilitate this process. This file allows new customers to be added from various sources, such as different stores, and it can even be stored in the cloud. Then once a day or night file will be extracted and loaded into DB
Here are the steps I’ve followed:
Created a “Customers” table in SSMS (SQL Server Management Studio).
Created a “Daily Update” table that contains a daily sum of new customers. This table can be used for future analysis as the data volume grows.
Set up the Data Flow as follows:
Collected data from the Excel file.
Performed necessary data conversions.
Sorted the data.
Aggregated the data.
Loaded all customers into the SSMS database table [SSIS_practiceJacek79].[dbo].[Customers].
Loaded only the daily sum of customers, along with a timestamp, SSMS log, and unique ID.
Tasks within the Data Flow:
Reading data from the source, which could be located on a local drive, server, or cloud. In my project, I’m reading from the local C drive.
Converting and sorting the data.
Loading the data into the database.
Deleting the previously read data from the source folder.
Adding the prefix “Loaded” to the filename along with the data timestamp and moving the Excel file to a different location. In my case, I’m archiving it in the C drive folder named “NewCustomers_ARCHIVE.”
Additionally, I’m creating a flat file (.txt) as a backup and placing it in a third location. This location could also be a local drive, server, or cloud. However, for simplicity, in my project, I’m using the C drive folder “NewCustomers_SSIS_LOG.”
A log file will be generated with the name “NEWCustomers,” and the date and time will be added to the file name.
This setup should streamline the process of adding new customers to the database
To automate this task, it is possible to enable the SQL Server Agent and create daily schedules for the SSIS package
SSMS tables —> SSIS_tab
SSIS project —-> Integration Services Project_Jacek79