| Component | Setting |
|-----------|---------|
| Data Flow → Error Output | Set Redirect Row on errors (e.g., data conversion). |
| Error Destination | dbo.Err_Customer (same schema as staging, plus ErrorCode, ErrorMessage). |
| OnError Event Handler | Write to SSIS_ExecutionLog with EventMessage. Optionally send an email (SMTP task). |
Example:
dtexec /F "PackageName.dtsx" /CHECKPOINT FILE "C:\Temp\Checkpoint.xml"
It was another typical Monday morning at TechCorp, a company known for its vast data analytics and integration solutions. Alex, a senior data engineer, was sipping his coffee while staring at his computer screen. He was about to start working on the day's task, a data migration project using SSIS (SQL Server Integration Services).
As he began setting up a new package, his colleague, Jake, burst into his cubicle. "Alex, I need your help," Jake said, looking frustrated. "Our team lead just reported an issue with one of our data integration packages. It's throwing an error code: SSIS-668."
Alex's eyes lit up. "SSIS-668? That's a new one. I haven't seen that before."
The error, as it turned out, was related to a failure in the data flow task within one of the packages. Specifically, it was having trouble with a data conversion process. The error message wasn't very helpful, stating only that there was a problem with the conversion of a particular data type. SSIS-668
Determined to solve the issue, Alex and Jake dove into troubleshooting. They examined the data flow, checked the data types of the source and destination columns, and verified that the conversion was correctly set up.
As they worked, Alex explained to Jake the common causes of such errors: incorrect data type conversions, data truncation, or sometimes issues with the source or destination database connections.
After about an hour of investigation, they discovered the problem. A recent change in the database schema had introduced a new data type for one of the columns that the SSIS package was trying to process. The package, however, was still configured for the old data type.
Armed with this knowledge, Alex made the necessary adjustments to the package. He updated the data conversion to accommodate the new data type, ensuring that the package could handle the data correctly.
With the fix in place, they re-executed the package. This time, it completed successfully, and the data was migrated without any issues. Run package with source returning rows — observe success
The team lead was grateful for their quick work. "Great job, guys. I don't know what we would have done if you'd taken longer to figure that out. And by the way, let's document SSIS-668. It might help someone else in the future."
And so, "SSIS-668" became a known error code within the company, symbolizing not just a technical challenge but also a successful collaboration and problem-solving effort.
This story is quite generic and based on common issues that could arise in an SSIS environment. If "SSIS-668" refers to something specific not covered here, please provide more details for a more accurate narrative.
If you're encountering an error coded "SSIS-668," here are a few general steps you can take to troubleshoot the issue:
Event Log and Output Window: Check the Event Log and the Output window in Visual Studio (if you're developing there) for additional information. | Component | Setting | |-----------|---------| | Data
Debugging: Use the SSIS debugger to step through your package and see where it fails.
Some common issues that could potentially be related to an error like "SSIS-668" (though the exact error isn't specified here) include:
If you can provide more details about the error message or under what circumstances you're encountering "SSIS-668," I could offer more targeted advice.
Recommended: Use a set‑based MERGE wrapped in a single transaction.
Why not row‑by‑row? Because MERGE processes millions of rows in seconds vs. hours for OLE DB Command loops.
BEGIN TRANSACTION;
-- 1️⃣ Expire current rows that are being updated
UPDATE tgt
SET EffectiveTo = src.EffectiveFrom,
IsCurrent = 0
FROM dbo.DimCustomer tgt
JOIN dbo.stg_Customer src
ON tgt.CustomerKey = src.CustomerKey
WHERE tgt.IsCurrent = 1
AND (tgt.Name <> src.Name
OR tgt.Email <> src.Email
OR tgt.Address <> src.Address);
-- 2️⃣ Insert new version rows (both inserts & updated rows)
INSERT INTO dbo.DimCustomer
(CustomerKey, Name, Email, Address,
EffectiveFrom, EffectiveTo, IsCurrent, LoadDateTime)
SELECT src.CustomerKey,
src.Name,
src.Email,
src.Address,
src.EffectiveFrom,
NULL, -- open-ended
1,
SYSUTCDATETIME()
FROM dbo.stg_Customer src
LEFT JOIN dbo.DimCustomer tgt
ON tgt.CustomerKey = src.CustomerKey
AND tgt.IsCurrent = 1
WHERE tgt.CustomerKey IS NULL -- brand‑new rows
OR (tgt.Name <> src.Name
OR tgt.Email <> src.Email
OR tgt.Address <> src.Address); -- updated rows
COMMIT TRANSACTION;
OUTPUT inserted.SurrogateKey, inserted.CustomerKey, GETDATE()
INTO dbo.Audit_CustomerLoad (SurrogateKey, CustomerKey, LoadDateTime);
If none of the above steps work, you may need to repair or reinstall SSIS. You can do this by:
Conclusion
+----------------+ +--------------------+ +-------------------+
| Source System | ---> | SSIS‑668 Package | ---> | Data Warehouse |
| (OLTP DB) | | (Data Flow, CDC) | | (Dim/Fact Tables) |
+----------------+ +--------------------+ +-------------------+
Key Components:
1️⃣ CDC Extraction Sub‑Package
2️⃣ Staging Area (temp tables)
3️⃣ SCD‑2 Merge Logic
4️⃣ Auditing & Logging
5️⃣ Error‑Handling & Redirection
When a data flow task receives zero rows from an upstream source (e.g., filtered source or empty lookup result), downstream components intermittently throw a null-reference or unexpected-rows error causing the package to fail. Symptoms include: