SSIS and SSMS Error Documentation

Often, we encounter challenges when trying to import data, especially when dealing with large datasets and complex data types. In this documentation, I'll share my experience, the errors I encountered, and the eventual solutions I found along the way.

Outline

• Bulk Insert Challenges in SSMS

• Data Loading and Cleaning Using SSIS

• Conclusion

Bulk Insert Challenges in SSMS

Initially, the bulk insert operation in SQL Server Management Studio (SSMS) was employed to load data into the database. Each column was set to its respective data type, intending to ensure data integrity. However, issues arose as data corruption occurred along the way. The process was further complicated when attempts to set explicit data types for columns resulted in persistent error messages.

image 1: data conversion error

Faced with these challenges, a temporary fix was implemented by loading all columns as strings. The rationale behind this approach was to tackle the issue of incorrect data type conversions. However, loading all data as strings can be challenging, particularly when dealing with datasets that contain multiple columns with varying data types.

image 2: corrupt data

Data Loading and Cleaning Using SSIS

• STEP 1:

In an effort to address data integrity issues, SQL Server Integration Services (SSIS), that provides a more structured and controlled environment for data loading, was employed to create a data pipeline that would allow problematic rows to be filtered out before being loaded into SQL database.

However, after successfully processing the data through the pipeline, the three new columns resulting from data type conversions on the original data were all populated with NULL values in the database.

Recognizing that the pipeline worked correctly up to the point of database insertion, I decided to pivot my approach. Instead of sending the data directly to the database using the OLE DB Destination, I opted for a Flat File Destination. This entailed storing the processed data as a CSV file on my local computer, enabling me to access it manually. This approach indeed resolved the NULL value problem, as the files retained their intended data.

image 3: Data pipeline using flat file destination

Nevertheless, I am now faced with the challenge of automating the process entirely for a professional setting. While the workaround of saving data locally and then manually importing it works, it falls short of meeting the automation standards expected in a workplace environment. Additionally, the three newly generated columns in the flat file had been correctly converted to the required data types but then got reversed to strings when stored in the flat file. This is because flat files do not retain their data types when stored; they convert all columns to strings.

Therefore, I seek to understand the underlying reasons for the NULL values in the database to devise a fully automated solution that ensures data integrity throughout the entire process.

• STEP 2:

The issue was identified in the Data Conversion Transformation Editor. After pinpointing the error source, I proceeded to rectify it by ensuring that the columns were appropriately converted to their respective data types. Additionally, I switched the destination from a flat file to OLE DB Destination. Upon executing the code in SSMS, it ran successfully, confirming the successful data upload. A subsequent verification of the converted columns revealed that they had been correctly inserted into the database.

image 4: Data pipeline using OLE DB Destination

• STEP 3:

When looking at the data in SQL, I noticed some missing values in certain columns. However, these missing values are only in rows where the 'CustomerID' is absent. So, to address this, I added a third Conditional Split to filter out these empty rows. I can review and analyze them separately at a later time.

image 5: Data pipeline with three conditional splits

Conclusion

In the world of data management, challenges are inevitable, and adaptability is key. The journey from attempting bulk inserts in SSMS to using SSIS showcased the importance of selecting the right tools and methodologies for data loading. While SSMS serves as a valuable environment for database management and scripting, SSIS excels in complex data integration scenarios, providing the structure and control needed to ensure data integrity and mitigate data corruption risks.

In retrospect, the transition to SSIS not only resolved data corruption issues but also offered enhanced data transformation capabilities and robust error handling. This experience underscores the significance of error documentation, thorough troubleshooting, and leveraging specialized tools like SSIS when dealing with complex data loading challenges.

*You can access the analysis on this GitHub repository.