Câu 1:

Câu 2:
-- 1. Bảng lưu dữ liệu bán hàng hàng ngày (dữ liệu sạch)
CREATE TABLE output.sales_daily (
SaleID INT PRIMARY KEY IDENTITY(1,1),
BranchCode NVARCHAR(10) NOT NULL,
OrderDate DATE NOT NULL,
ProductID INT, -- Kiểu INT
Quantity INT,
UnitPrice MONEY, -- Kiểu MONEY
TotalAmount AS (ISNULL(Quantity, 0) * ISNULL(UnitPrice, 0)), -- Cột tự động tính
SourceFileName NVARCHAR(255)
);
GO

-- 2. Bảng lưu các dòng dữ liệu bị lỗi

CREATE TABLE output.sales_daily_errors (
ErrorID INT PRIMARY KEY IDENTITY(1,1),
SourceFileName NVARCHAR(255),
OriginalRowData VARCHAR(MAX),
ErrorCode INT,
ErrorColumn INT,
ErrorMessage NVARCHAR(MAX),
ProcessedAt DATETIME DEFAULT GETDATE()
);
GO
-- 3. Bảng lưu dữ liệu tổng hợp
CREATE TABLE output.sales_aggre_unpivoted (
ID INT PRIMARY KEY IDENTITY(1,1),
BranchCode NVARCHAR(10),
ProductID INT, -- Kiểu INT
OrderDate DATE,
TotalDailySales MONEY, -- Kiểu MONEY
UNIQUE (BranchCode, ProductID, OrderDate)
);
GO

Câu 3:

1.​ SSIS package "D:\BI_PROJECT\Integration Services Project5\Integration Services

Project5\GK.dtsx" starting.​
Information: 0x4004300A at Data Flow Task, SSIS.Pipeline: Validation phase is
beginning.​
Warning: 0x802092A7 at Data Flow Task, OLE DB Destination [94]: Truncation may
occur due to inserting data from data flow column "OrderDate" with a length of 50 to
database column "OrderDate" with a length of 10.​
Warning: 0x802092A7 at Data Flow Task, OLE DB Destination 1 [124]: Truncation may
occur due to inserting data from data flow column "OrderDate" with a length of 50 to
database column "OrderDate" with a length of 10.​
Information: 0x4004300A at Data Flow Task, SSIS.Pipeline: Validation phase is
beginning.​
Warning: 0x802092A7 at Data Flow Task, OLE DB Destination [94]: Truncation may
occur due to inserting data from data flow column "OrderDate" with a length of 50 to
database column "OrderDate" with a length of 10.​
Warning: 0x802092A7 at Data Flow Task, OLE DB Destination 1 [124]: Truncation may
occur due to inserting data from data flow column "OrderDate" with a length of 50 to
database column "OrderDate" with a length of 10.​
Warning: 0x80049304 at Data Flow Task, SSIS.Pipeline: Warning: Could not open global
shared memory to communicate with performance DLL; data flow performance counters
are not available. To resolve, run this package as an administrator, or on the system's
console.​
Information: 0x40043006 at Data Flow Task, SSIS.Pipeline: Prepare for Execute phase
is beginning.​
Information: 0x40043007 at Data Flow Task, SSIS.Pipeline: Pre-Execute phase is
beginning.​
Information: 0x402090DC at Data Flow Task, Flat File Source [68]: The processing of file
"D:\Data\K22406\DN_20250629.csv" has started.​
Information: 0x4004300C at Data Flow Task, SSIS.Pipeline: Execute phase is beginning.​
Information: 0x402090DE at Data Flow Task, Flat File Source [68]: The total number of
data rows processed for file "D:\Data\K22406\DN_20250629.csv" is 201.​
Error: 0xC0202009 at Data Flow Task, OLE DB Destination [94]: SSIS Error Code
DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.​
An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server"
Hresult: 0x80004005 Description: "Conversion failed when converting date and/or time
from character string.".​
Error: 0xC0209029 at Data Flow Task, OLE DB Destination [94]: SSIS Error Code
DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "OLE DB
Destination.Inputs[OLE DB Destination Input]" failed because error code 0xC020907B
occurred, and the error row disposition on "OLE DB Destination.Inputs[OLE DB
Destination Input]" specifies failure on error. An error occurred on the specified object of
the specified component. There may be error messages posted before this with more
information about the failure.​
Error: 0xC0047022 at Data Flow Task, SSIS.Pipeline: SSIS Error Code
DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "OLE DB
Destination" (94) failed with error code 0xC0209029 while processing input "OLE DB
Destination Input" (107). The identified component returned an error from the
ProcessInput method. The error is specific to the component, but the error is fatal and
will cause the Data Flow task to stop running. There may be error messages posted
before this with more information about the failure.​
Information: 0x40043008 at Data Flow Task, SSIS.Pipeline: Post Execute phase is
beginning.​
Information: 0x402090DD at Data Flow Task, Flat File Source [68]: The processing of file
"D:\Data\K22406\DN_20250629.csv" has ended.​
Information: 0x4004300B at Data Flow Task, SSIS.Pipeline: "OLE DB Destination" wrote
0 rows.​
Information: 0x4004300B at Data Flow Task, SSIS.Pipeline: "OLE DB Destination 1"
wrote 0 rows.​
Information: 0x4004300B at Data Flow Task, SSIS.Pipeline: "OLE DB Destination 2"
wrote 0 rows.​
Information: 0x40043009 at Data Flow Task, SSIS.Pipeline: Cleanup phase is beginning.​
Task failed: Data Flow Task​
Warning: 0x80019002 at Foreach Loop Container: SSIS Warning Code
DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but
the number of errors raised (3) reached the maximum allowed (1); resulting in failure.
This occurs when the number of errors reaches the number specified in
MaximumErrorCount. Change the MaximumErrorCount or fix the errors.​
Warning: 0x80019002 at Package1: SSIS Warning Code
DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but
the number of errors raised (3) reached the maximum allowed (1); resulting in failure.
This occurs when the number of errors reaches the number specified in
MaximumErrorCount. Change the MaximumErrorCount or fix the errors.​
SSIS package "D:\BI_PROJECT\Integration Services Project5\Integration Services
 Project5\GK.dtsx" finished: Failure.​
The program '[22068] DtsDebugHost.exe: DTS' has exited with code 0 (0x0).
2.​ SSIS package "D:\BI_PROJECT\Integration Services Project5\Integration Services
Project5\GK.dtsx" starting.​
Information: 0x4004300A at Data Flow Task, SSIS.Pipeline: Validation phase is
beginning.​
Warning: 0x802092A7 at Data Flow Task, OLE DB Destination [94]: Truncation may
occur due to inserting data from data flow column "OrderDate" with a length of 50 to
database column "OrderDate" with a length of 10.​
Warning: 0x802092A7 at Data Flow Task, OLE DB Destination 1 [124]: Truncation may
occur due to inserting data from data flow column "OrderDate" with a length of 50 to
database column "OrderDate" with a length of 10.​
Information: 0x4004300A at Data Flow Task, SSIS.Pipeline: Validation phase is
beginning.​
Warning: 0x802092A7 at Data Flow Task, OLE DB Destination [94]: Truncation may
occur due to inserting data from data flow column "OrderDate" with a length of 50 to
database column "OrderDate" with a length of 10.​
Warning: 0x802092A7 at Data Flow Task, OLE DB Destination 1 [124]: Truncation may
occur due to inserting data from data flow column "OrderDate" with a length of 50 to
database column "OrderDate" with a length of 10.​
Warning: 0x80049304 at Data Flow Task, SSIS.Pipeline: Warning: Could not open global
shared memory to communicate with performance DLL; data flow performance counters
are not available. To resolve, run this package as an administrator, or on the system's
console.​
Information: 0x40043006 at Data Flow Task, SSIS.Pipeline: Prepare for Execute phase
is beginning.​
Information: 0x40043007 at Data Flow Task, SSIS.Pipeline: Pre-Execute phase is
beginning.​
Information: 0x402090DC at Data Flow Task, Flat File Source [68]: The processing of file
"D:\Data\K22406\DN_20250629.csv" has started.​
Information: 0x4004300C at Data Flow Task, SSIS.Pipeline: Execute phase is beginning.​
Information: 0x402090DE at Data Flow Task, Flat File Source [68]: The total number of
data rows processed for file "D:\Data\K22406\DN_20250629.csv" is 201.​
Error: 0xC0202009 at Data Flow Task, OLE DB Destination [94]: SSIS Error Code
DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E21.​
An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server"
Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors.
Check each OLE DB status value, if available. No work was done.".​
Error: 0xC020901C at Data Flow Task, OLE DB Destination [94]: There was an error
with OLE DB Destination.Inputs[OLE DB Destination Input].Columns[BranchCode] on
OLE DB Destination.Inputs[OLE DB Destination Input]. The column status returned was:
"The value could not be converted because of a potential loss of data.".​
Error: 0xC0209029 at Data Flow Task, OLE DB Destination [94]: SSIS Error Code
DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "OLE DB
 Destination.Inputs[OLE DB Destination Input]" failed because error code 0xC0209077
occurred, and the error row disposition on "OLE DB Destination.Inputs[OLE DB
Destination Input]" specifies failure on error. An error occurred on the specified object of
the specified component. There may be error messages posted before this with more
information about the failure.​
Error: 0xC0047022 at Data Flow Task, SSIS.Pipeline: SSIS Error Code
DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "OLE DB
Destination" (94) failed with error code 0xC0209029 while processing input "OLE DB
Destination Input" (107). The identified component returned an error from the
ProcessInput method. The error is specific to the component, but the error is fatal and
will cause the Data Flow task to stop running. There may be error messages posted
before this with more information about the failure.​
Information: 0x40043008 at Data Flow Task, SSIS.Pipeline: Post Execute phase is
beginning.​
Information: 0x402090DD at Data Flow Task, Flat File Source [68]: The processing of file
"D:\Data\K22406\DN_20250629.csv" has ended.​
Information: 0x4004300B at Data Flow Task, SSIS.Pipeline: "OLE DB Destination" wrote
0 rows.​
Information: 0x4004300B at Data Flow Task, SSIS.Pipeline: "OLE DB Destination 1"
wrote 0 rows.​
Information: 0x4004300B at Data Flow Task, SSIS.Pipeline: "OLE DB Destination 2"
wrote 0 rows.​
Information: 0x40043009 at Data Flow Task, SSIS.Pipeline: Cleanup phase is beginning.​
Task failed: Data Flow Task​
Warning: 0x80019002 at Foreach Loop Container: SSIS Warning Code
DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but
the number of errors raised (4) reached the maximum allowed (1); resulting in failure.
This occurs when the number of errors reaches the number specified in
MaximumErrorCount. Change the MaximumErrorCount or fix the errors.​
Warning: 0x80019002 at Package1: SSIS Warning Code
DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but
the number of errors raised (4) reached the maximum allowed (1); resulting in failure.
This occurs when the number of errors reaches the number specified in
MaximumErrorCount. Change the MaximumErrorCount or fix the errors.​
SSIS package "D:\BI_PROJECT\Integration Services Project5\Integration Services
Project5\GK.dtsx" finished: Failure.​
The program '[3184] DtsDebugHost.exe: DTS' has exited with code 0 (0x0).
3.​ SSIS package "D:\BI_PROJECT\Integration Services Project5\Integration Services
Project5\K224060796_BuiXuanMai.dtsx" starting.​
Information: 0x4004300A at Data Flow Task, SSIS.Pipeline: Validation phase is
beginning.​
Warning: 0x802092A7 at Data Flow Task, OLE DB Destination [97]: Truncation may
occur due to inserting data from data flow column "BranchCode" with a length of 50 to
database column "BranchCode" with a length of 10.​
Warning: 0x802092A7 at Data Flow Task, OLE DB Destination 1 [127]: Truncation may
occur due to inserting data from data flow column "BranchCode" with a length of 50 to
database column "BranchCode" with a length of 10.​
Information: 0x4004300A at Data Flow Task, SSIS.Pipeline: Validation phase is
beginning.​
Warning: 0x802092A7 at Data Flow Task, OLE DB Destination [97]: Truncation may
occur due to inserting data from data flow column "BranchCode" with a length of 50 to
database column "BranchCode" with a length of 10.​
Warning: 0x802092A7 at Data Flow Task, OLE DB Destination 1 [127]: Truncation may
occur due to inserting data from data flow column "BranchCode" with a length of 50 to
database column "BranchCode" with a length of 10.​
Warning: 0x80049304 at Data Flow Task, SSIS.Pipeline: Warning: Could not open global
shared memory to communicate with performance DLL; data flow performance counters
are not available. To resolve, run this package as an administrator, or on the system's
console.​
Warning: 0x80047076 at Data Flow Task, SSIS.Pipeline: The output column "FileName"
(56) on output "Derived Column Output" (52) and component "Derived Column 1" (48) is
not subsequently used in the Data Flow task. Removing this unused output column can
increase Data Flow task performance.​
Information: 0x40043006 at Data Flow Task, SSIS.Pipeline: Prepare for Execute phase
is beginning.​
Information: 0x40043007 at Data Flow Task, SSIS.Pipeline: Pre-Execute phase is
beginning.​
Information: 0x402090DC at Data Flow Task, Flat File Source [71]: The processing of file
"D:\Data\K22406\DN_20250629.csv" has started.​
Information: 0x4004300C at Data Flow Task, SSIS.Pipeline: Execute phase is beginning.​
Information: 0x402090DE at Data Flow Task, Flat File Source [71]: The total number of
data rows processed for file "D:\Data\K22406\DN_20250629.csv" is 201.​
Error: 0xC020902A at Data Flow Task, Derived Column 1 [48]: The "Derived Column 1"
failed because truncation occurred, and the truncation row disposition on "Derived
Column 1.Outputs[Derived Column Output].Columns[FileName]" specifies failure on
truncation. A truncation error occurred on the specified object of the specified
component.​
Error: 0xC0047022 at Data Flow Task, SSIS.Pipeline: SSIS Error Code
DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Derived
Column 1" (48) failed with error code 0xC020902A while processing input "Derived
Column Input" (49). The identified component returned an error from the ProcessInput
method. The error is specific to the component, but the error is fatal and will cause the
Data Flow task to stop running. There may be error messages posted before this with
more information about the failure.​
Information: 0x40043008 at Data Flow Task, SSIS.Pipeline: Post Execute phase is
beginning.​
Information: 0x402090DD at Data Flow Task, Flat File Source [71]: The processing of file
"D:\Data\K22406\DN_20250629.csv" has ended.​
 Information: 0x4004300B at Data Flow Task, SSIS.Pipeline: "OLE DB Destination" wrote
0 rows.​
Information: 0x4004300B at Data Flow Task, SSIS.Pipeline: "OLE DB Destination 1"
wrote 0 rows.​
Information: 0x4004300B at Data Flow Task, SSIS.Pipeline: "OLE DB Destination 2"
wrote 0 rows.​
Information: 0x40043009 at Data Flow Task, SSIS.Pipeline: Cleanup phase is beginning.​
Task failed: Data Flow Task​
Warning: 0x80019002 at Foreach Loop Container: SSIS Warning Code
DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but
the number of errors raised (2) reached the maximum allowed (1); resulting in failure.
This occurs when the number of errors reaches the number specified in
MaximumErrorCount. Change the MaximumErrorCount or fix the errors.​
Warning: 0x80019002 at Package1: SSIS Warning Code
DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but
the number of errors raised (2) reached the maximum allowed (1); resulting in failure.
This occurs when the number of errors reaches the number specified in
MaximumErrorCount. Change the MaximumErrorCount or fix the errors.​
SSIS package "D:\BI_PROJECT\Integration Services Project5\Integration Services
Project5\K224060796_BuiXuanMai.dtsx" finished: Failure.​
The program '[15152] DtsDebugHost.exe: DTS' has exited with code 0 (0x0).

Câu 4:

Câu 5:
Câu 6:
Câu 7:
Để giải quyết bài toán ETL của công ty ABC, em đã xây dựng một quy trình tự động hoàn chỉnh
bằng SSIS.
Đầu tiên, em thiết kế một luồng điều khiển (Control Flow) sử dụng Foreach Loop Container để
tự động lặp qua tất cả các file báo cáo doanh thu hàng ngày. Một điểm quan trọng em đã xử lý
là cơ chế chống lặp, bằng cách kiểm tra tên file trong database trước mỗi lần xử lý để đảm bảo
dữ liệu không bị nạp lại.
Trong luồng dữ liệu (Data Flow), em đã thực hiện một chuỗi các bước biến đổi. Em không dùng
Script Task mà sử dụng các Derived Column để trích xuất và làm sạch thông tin BranchCode và
OrderDate từ tên file. Về phần xử lý lỗi, em đã thiết lập một luồng riêng để "bắt" tất cả các dòng
dữ liệu không hợp lệ. Các dòng này, thay vì làm dừng cả quy trình, sẽ được ghi lại vào một
bảng lỗi riêng, kèm theo thông điệp lỗi chi tiết được lấy từ Script Component để tiện cho việc
theo dõi sau này.
Sau khi dữ liệu đã được làm sạch và chuyển đổi đúng định dạng, nó sẽ được chia làm hai
nhánh. Một nhánh ghi dữ liệu chi tiết vào bảng sales_daily. Nhánh còn lại sẽ đi qua một bước
Aggregate để tính toán doanh thu tổng hợp theo ngày, chi nhánh và sản phẩm, sau đó nạp vào
bảng sales_aggre_unpivoted. Để đảm bảo bảng tổng hợp này luôn cập nhật, em đã thêm một
bước xóa toàn bộ dữ liệu cũ ngay khi package bắt đầu chạy.
Màn hình cấu hình Job