Main Components – SSDT,SSIS,SSMS
Main Components
A. SSDT (SQL Server Data Tools) in Visual Studio
• Role: Development
• What it does:
o You use Visual Studio + SSDT to create SSIS packages (.dtsx files).
o Packages contain ETL logic: Extract data from sources, Transform it, and
Load it into destinations.
• Example: You extract sales data from Excel, clean it, and load it into SQL Server.
Arrow: “Develop Packages” → sends your package design to SSIS for execution.
1
B. SSIS (SQL Server Integration Services)
• Role: Execution / Integration
• What it does:
o It runs the packages you developed in SSDT.
o Can execute packages locally, on a server, or in the SSISDB Catalog.
• Key Services:
1. SSIS Service → runs the packages.
2. SSISDB Catalog → stores deployed packages and allows scheduling via SQL Server
Agent.
Arrow: “Deploy & Execute” → sends packages from development to execution on SSIS.
C. SSMS (SQL Server Management Studio)
• Role: Monitoring / Management
• What it does:
o You connect to Integration Services or Database Engine to:
▪ Deploy packages to SSISDB Catalog
▪ Monitor running packages
▪ Schedule packages with SQL Server Agent
• Example: You can check if a data load succeeded or failed using SSMS.
2
Workflow Explained
1. Develop:
o In Visual Studio + SSDT, create SSIS packages with your ETL logic.
2. Deploy:
o Use SSMS or Visual Studio to deploy the package to the SSISDB Catalog on
SQL Server.
3. Execute / Schedule:
o SSIS service runs the package manually or via a schedule (SQL Server Agent).
o Data moves from source to destination according to package logic.
4. Monitor:
o In SSMS, check execution status, logs, and troubleshoot errors if needed.
Why We Use This
• Automate ETL workflows → no manual data cleaning or transfers.
• Centralize and schedule packages → SSISDB Catalog + SQL Server Agent.
• Error handling & logging → easier to maintain data pipelines.
• Scalable & enterprise-ready → can handle large volumes of data efficiently.
In simple words:
• SSDT = Build your ETL
• SSIS = Run your ETL
• SSMS = Manage & monitor your ETL
3
Scenario: Sales Data ETL Process
Business Case:
A retail company wants to load daily sales data from a CSV file into their SQL Server
database, clean it, and calculate total sales per product.
Step 1: SSDT (SQL Server Data Tools)
• What it is: SSDT is where you design your SSIS packages. Think of it as your
development environment.
• In SSDT:
1. Create a new Integration Services Project.
2. Add a Data Flow Task:
▪ Source: CSV file containing daily sales data.
▪ Transformations:
▪ Remove duplicates.
▪ Convert text to proper data types (e.g., string → date, string →
decimal).
▪ Calculate TotalSales = Quantity * UnitPrice.
▪ Destination: SQL Server table ([Link]).
3. Save and build the package (.dtsx file).
SSDT is like your “workshop” where you design the ETL pipeline. You don’t actually move
data here; you define how the data will move and transform.
Step 2: SSIS (SQL Server Integration Services)
• What it is: SSIS is the engine that executes the ETL package you built in SSDT.
4
• How it works:
1. Deploy the .dtsx package to the SSIS Catalog in SQL Server.
2. Execute the package manually or schedule it via SQL Server Agent.
3. The package reads the CSV, applies transformations, and loads data into the
SQL Server table.
SSIS is like the “delivery truck.” You designed the route in SSDT, and SSIS drives the truck to
move the data from source → destination.
Step 3: SSMS (SQL Server Management Studio)
• What it is: SSMS is where you monitor, query, and manage the database.
• In SSMS:
1. Check the loaded data:
2. SELECT * FROM [Link];
3. Run aggregation queries:
4. SELECT ProductID, SUM(TotalSales) AS DailyTotal
5. FROM [Link]
6. GROUP BY ProductID;
7. Troubleshoot or maintain the database as needed.
SSMS is like the “control center” or “cash register.” The data has arrived, and now we can
analyze, report, or manage it.
Summary Table
Tool Role in ETL Example Activity
SSDT Design Create Data Flow Task, Transform Data
SSIS Execute Run the package, move and clean data
SSMS Monitor/Query Check data, run SQL queries, generate reports