SSIS CSV Import error when a special character in the text column

If you encounter issues importing a CSV file using an SSIS package—particularly when strings are enclosed in double quotes—here’s a method you can try to fix the problem.

Sample CSV Data

"090","Grey",""
"091","Grey, Red",""
"092","White/Teal",""
"042","Blue ","(\"is not blue\" )"    --- TextQualified '"' with single quote inside value
"093","Light Grey",""

Error Message:
[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on Flat File Source returned error code 0xC0202092. This error is fatal and stops pipeline execution. Additional error details might be available earlier in the log.

To fix this, review the column settings in the Flat File Connection Manager:

Steps:
Navigate to Flat File Connection Manager → Advanced Tab and review the column properties.

Set TextQualified to false.

Now, attempt to import the CSV file again. The issue should be resolved.

SSIS catalog database read

Understanding the SSISDB Catalog in SQL Server Integration Services (SSIS)

The SSISDB catalog serves as the central repository for managing and interacting with SSIS projects deployed to an Integration Services server. In this catalog, users can configure environments, manage parameters, execute and troubleshoot packages, and oversee server operations. The catalog is essential for running and managing Integration Services (SSIS) projects.

Key Components of the SSISDB Catalog

The SSISDB catalog contains various objects necessary for integration services management:

• Projects: Contains all the SSIS projects that are deployed.
• Packages: Includes details of all SSIS packages stored in the catalog.
• Parameters: Defines project and package parameters.
• Environments: Specifies runtime values for packages via environment configurations.
• Operational History: Tracks the execution history of packages and projects.

Important SSISDB Catalog Views

1. Executions

The executions view displays details about the instances where packages have been executed within the catalog. When a package is executed using the Execute Package task, it runs within the same execution instance as the parent package.

2. Folders

The folders view lists all folders within the SSISDB catalog, which help organize projects and packages.

3. Packages

The packages view shows all the packages registered in the catalog.

4. Projects

The projects view contains details about each project in the catalog, including project names and metadata.

Common SQL Queries to Manage and Troubleshoot SSISDB

1. View Executions Details

This SQL query retrieves execution details for packages within the SSISDB catalog:

SELECT E.execution_id, E.folder_name, E.project_name, E.package_name, E.reference_id, 
       E.reference_type, E.environment_folder_name, E.environment_name, E.project_lsn, 
       E.executed_as_sid, E.executed_as_name, E.use32bitruntime, E.operation_type, 
       E.created_time, E.object_type, E.object_id, E.status, E.start_time, E.end_time, 
       E.caller_sid, E.caller_name, E.process_id, E.stopped_by_sid, E.stopped_by_name, 
       E.dump_id, E.server_name, E.machine_name, E.total_physical_memory_kb, 
       E.available_physical_memory_kb, E.total_page_file_kb, E.available_page_file_kb, 
       E.cpu_count
FROM SSISDB.catalog.executions AS E
INNER JOIN ssisdb.catalog.folders AS F ON F.name = E.folder_name
INNER JOIN SSISDB.catalog.projects AS P ON P.folder_id = F.folder_id AND P.name = E.project_name
INNER JOIN SSISDB.catalog.packages AS PKG ON PKG.project_id = P.project_id AND PKG.name = E.package_name;
        

2. Find Error Messages by Package

This query allows you to locate error messages associated with a specific package:

SELECT q.*
FROM (SELECT em.* 
      FROM SSISDB.catalog.event_messages em
      WHERE em.operation_id = (SELECT MAX(execution_id) FROM SSISDB.catalog.executions)
      AND event_name NOT LIKE '%Validate%') q
WHERE package_name = 'MainPackage.dtsx'
ORDER BY message_time DESC;
        

3. Find All Error Messages

This query fetches all error messages related to the operations:

SELECT OM.operation_message_id, OM.operation_id, OM.message_time, OM.message_type, 
       OM.message_source_type, OM.message, OM.extended_info_id
FROM catalog.operation_messages AS OM
WHERE OM.message_type = 120;
        

4. Find Messages Associated with Failing Operations

This query retrieves messages linked to failing operations:

SELECT OM.operation_message_id, OM.operation_id, OM.message_time, OM.message_type, 
       OM.message_source_type, OM.message, OM.extended_info_id
FROM catalog.operation_messages AS OM
INNER JOIN (
    SELECT DISTINCT OM.operation_id 
    FROM catalog.operation_messages AS OM
    WHERE OM.message_type = 120
) D ON D.operation_id = OM.operation_id;
        

5. Find Messages Associated with the Last Failing Run

This query retrieves messages related to the last failing operation:

SELECT OM.operation_message_id, OM.operation_id, OM.message_time, OM.message_type, 
       OM.message_source_type, OM.message, OM.extended_info_id
FROM catalog.operation_messages AS OM
WHERE OM.operation_id = 
    (SELECT MAX(OM.operation_id) FROM SSISDB.catalog.operation_messages AS OM
    WHERE OM.message_type = 120);
        

6. Send Email with Query Results

If you want to send the query result via email, you can use the following query:

DECLARE @profile_name sysname = 'SQLProfile',
        @recipients varchar(max) = 'recipient@example.com',
        @subject nvarchar(255) = 'Failed Package Alert',
        @body nvarchar(max) = 'An SSIS package has failed. Please check the logs.',
        @query nvarchar(max) = 'SELECT O.object_name AS FailingPackageName, ...'
EXECUTE msdb.dbo.sp_send_dbmail @profile_name, @recipients, @subject, @body, @query;
        

References

• SSIS Catalog Documentation
• SSISDB Catalog Executions View

How to get SSIS catalog information's

 

Understanding Where SQL Server SSIS Package Information Is Stored

In SQL Server, Integration Services (SSIS) package execution details and related metadata are stored within the SSISDB catalog database. This centralized storage makes managing and monitoring your SSIS packages easier and more efficient.

---

Querying SSIS Package Execution Details

The following T-SQL query retrieves detailed information about SSIS package executions along with related folder, project, and package metadata stored in the SSISDB catalog:

SELECT
    E.execution_id,
    E.folder_name,
    E.project_name,
    E.package_name,
    E.reference_id,
    E.reference_type,
    E.environment_folder_name,
    E.environment_name,
    E.project_lsn,
    E.executed_as_sid,
    E.executed_as_name,
    E.use32bitruntime,
    E.operation_type,
    E.created_time,
    E.object_type,
    E.object_id,
    E.status,
    E.start_time,
    E.end_time,
    E.caller_sid,
    E.caller_name,
    E.process_id,
    E.stopped_by_sid,
    E.stopped_by_name,
    E.dump_id,
    E.server_name,
    E.machine_name,
    E.total_physical_memory_kb,
    E.available_physical_memory_kb,
    E.total_page_file_kb,
    E.available_page_file_kb,
    E.cpu_count,
    F.folder_id,
    F.name,
    F.description,
    F.created_by_sid,
    F.created_by_name,
    F.created_time,
    P.project_id,
    P.folder_id,
    P.name,
    P.description,
    P.project_format_version,
    P.deployed_by_sid,
    P.deployed_by_name,
    P.last_deployed_time,
    P.created_time,
    P.object_version_lsn,
    P.validation_status,
    P.last_validation_time,
    PKG.package_id,
    PKG.name,
    PKG.package_guid,
    PKG.description,
    PKG.package_format_version,
    PKG.version_major,
    PKG.version_minor,
    PKG.version_build,
    PKG.version_comments,
    PKG.version_guid,
    PKG.project_id,
    PKG.entry_point,
    PKG.validation_status,
    PKG.last_validation_time
FROM SSISDB.catalog.executions AS E
INNER JOIN SSISDB.catalog.folders AS F ON F.name = E.folder_name
INNER JOIN SSISDB.catalog.projects AS P ON P.folder_id = F.folder_id AND P.name = E.project_name
INNER JOIN SSISDB.catalog.packages AS PKG ON PKG.project_id = P.project_id AND PKG.name = E.package_name;

This query joins executions with their respective folders, projects, and packages to provide a comprehensive overview of SSIS activity.

---

How to Create the SSISDB Catalog in SQL Server Management Studio

If the SSISDB catalog is not yet created on your server, follow these steps to set it up:

1. Open SQL Server Management Studio (SSMS).

2. Connect to your SQL Server Database Engine instance.

3. In Object Explorer, expand the server node.

4. Right-click on Integration Services Catalogs and select Create Catalog.

5. Enable CLR Integration as the catalog requires CLR stored procedures.

6. Enable automatic execution of Integration Services stored procedures at SQL Server startup. This option runs the catalog.startup stored procedure each time the SQL Server instance restarts, ensuring that the catalog’s operation state is properly maintained and fixing the status of any running packages if the server was previously shut down unexpectedly.

7. Set a password for the encryption key used by the catalog, then click OK to complete the creation.

---

Additional Resources

• Deploying Packages to SQL Server Integration Services Catalog (SSISDB)

• Official Microsoft Documentation: Create the SSIS Catalog

---

This setup and querying approach gives you control and insight into your SSIS environment, helping you manage package deployments and executions effectively.