Developing ETL pipelines with SSIS (SQL Server Integration Services)

TASK:

• I developed two SSIS packages: STG_FCT_PRODUCT_SALES for staging data and FCT_PRODUCT_SALES for transferring data to the final table.

ACTION:

• In the STG_FCT_PRODUCT_SALES package, I set up a Connection Manager for the source files, created a variable to make the source dynamic, and used an Execute SQL Task to truncate the staging table. I implemented a Foreach Loop Container to dynamically iterate over the source files and added a Data Flow Task to transfer data to the SQL Server database. After processing, I moved the files to a loaded folder to confirm successful execution.
• In the FCT_PRODUCT_SALES package, I added an Execute SQL Task to delete existing data to avoid duplication using the query: DELETE FROM [dbo].[FCT_PRODUCT_SALES] WHERE ORDER_ID IN (SELECT DISTINCT ORDER_ID FROM [dbo].[STG_FCT_PRODUCT_SALES]); I also added a Data Flow Task using OLE DB connections to move data from the staging table to the FCT_PRODUCT_SALES table. Additionally, I configured SQL Server Agent to schedule the packages for regular updates.

RESULT:

• This streamlined workflow ensured data quality and availability, significantly enhancing the efficiency of reporting and analysis.