Big file with lots of columns doesn't output content of many of them

Question

Big file with lots of columns doesn't output content of many of them

Naomi Nosonovsky 8,771

Good day.

I have a SSIS package that selects the data using the following statement:

EXECUTE im_automation.accent_changes
WITH RESULT SETS
(
    (
        [Source] VARCHAR(30),
        Category VARCHAR(30),
        ProvType VARCHAR(30),
        [Social Group] VARCHAR(3),
        [Social Group Partner] VARCHAR(30),
        [Tracer ID] VARCHAR(30),
        [Request Type] VARCHAR(20),
        [Change Type] VARCHAR(100),
        [Status] VARCHAR(10),
        [OHCS Eff Date] DATE,
        [Earliest Eff Date] DATE,
        [OHCS Change Date] DATE,
        [OHCS Term Date] DATE,
        [Latest Term Date] DATE,
        [Social Grouping Term Date] DATE,
        ExclusionRreason VARCHAR(MAX),
        TinNumber VARCHAR(20),
        NPI VARCHAR(10),
        ProviderID VARCHAR(30),
        ProvLastName VARCHAR(40),
        ProvFirstName VARCHAR(40),
        ProvMI VARCHAR(10),
        ClinicName VARCHAR(40),
        Generation VARCHAR(100),
		[T50 Specialty] VARCHAR(100),
		
        [T50 ProvType] VARCHAR(100),
        [T15 Taxonomy] VARCHAR(100),
        LicenseNumber VARCHAR(40),
        LicenseSate VARCHAR(10),
        LicenseIssueDate DATE,
        LocAddress1 VARCHAR(100),
        LocAddress2 VARCHAR(100),
        LoCcity VARCHAR(100),
        LocState VARCHAR(10),
        LocZip VARCHAR(10),
        RemitAddress1 VARCHAR(100),
        RemitAddress2 VARCHAR(100),
        RemitCity VARCHAR(100),
        RemitState VARCHAR(10),
        RemitZip VARCHAR(10),
        ProvGroup VARCHAR(30),
        Specialty VARCHAR(100),
		Title VARCHAR(100),
        MainStatus VARCHAR(20),
        SubStatus VARCHAR(20),
        StatusDate DATE,
        [Client Eff Date] DATE,
        SubSpecialty VARCHAR(100),
        LocCounty VARCHAR(30),
        AcnProvID VARCHAR(40),
        OfficeLocationID VARCHAR(40),
        LocEffDate DATE,
        LocTermDate DATE,
        TinEffDate DATE,
        TinTermDate DATE,
        ProvPhone VARCHAR(20),
        ProvFax VARCHAR(20),
        Gender VARCHAR(30),
        ClienTermDate DATE,
        CredStatusEffDate DATE,
        [Social Grouping Effective Date] DATE,
        [Dually Contracted] VARCHAR(3)
    )
);

As you can see, there are lots of columns. It is supposed to generate a | delimited text file. However, many of the columns don't output values and instead output just the bars.

What can I do to fix the package? Is it a bug or limitation is SSIS?

P.S. It helps to print the code - I noticed typo in the ExlusionReason column. Update. Looks like this problem is related to script task attempting to add a footer to the file. It is either fails with the out of memory expression or produces bad data.

Naomi Nosonovsky 8,771 Reputation points

2025-10-21T00:37:50.2+00:00

Now there are less columns and the procedure outputs the columns correctly and when I run it in VS and add Data Viewer I see the results perfectly fine. However, when I preview the file in the editor I don't see the columns. Here is the output:

I cannot understand it. Here is execution in run time in Data Viewer:

So in run-time where are columns populated, but when I preview OutPut file from connection manager the preview show just 2 columns populated. What may be the reason and how can I correctly get the columns with all data?
Michael Le (WICLOUD CORPORATION) 3,495 Reputation points Microsoft External Staff

2025-10-21T02:51:10.57+00:00
Your flat file connection manager likely still has the old column definitions from when you had more columns. When you reduced the column count, the connection manager didn't automatically update its metadata.

You could try the following steps to resolve the issue:

Delete and recreate your flat file connection manager to refresh the metadata

Verify column mappings in your Flat File Destination component (Mappings tab)

Check delimiter settings - ensure the pipe (|) delimiter is correctly configured

Remove the problematic script task temporarily to isolate the issue
Naomi Nosonovsky 8,771 Reputation points

2025-10-21T12:56:25.2566667+00:00

Thank you. Somehow it started to work.

Answer recommended by moderator

0 additional answers

Your answer

Naomi Nosonovsky 8,771 Reputation points

2025-10-21T00:37:50.2+00:00

Now there are less columns and the procedure outputs the columns correctly and when I run it in VS and add Data Viewer I see the results perfectly fine. However, when I preview the file in the editor I don't see the columns. Here is the output:

I cannot understand it. Here is execution in run time in Data Viewer:

So in run-time where are columns populated, but when I preview OutPut file from connection manager the preview show just 2 columns populated. What may be the reason and how can I correctly get the columns with all data?
Michael Le (WICLOUD CORPORATION) 3,495 Reputation points Microsoft External Staff

2025-10-21T02:51:10.57+00:00

Your flat file connection manager likely still has the old column definitions from when you had more columns. When you reduced the column count, the connection manager didn't automatically update its metadata.

You could try the following steps to resolve the issue:

Delete and recreate your flat file connection manager to refresh the metadata

Verify column mappings in your Flat File Destination component (Mappings tab)

Check delimiter settings - ensure the pipe (|) delimiter is correctly configured

Remove the problematic script task temporarily to isolate the issue
Naomi Nosonovsky 8,771 Reputation points

2025-10-21T12:56:25.2566667+00:00

Thank you. Somehow it started to work.

Answer 1

Hello @Naomi Nosonovsky ,

Out of Memory Exception

A reliable method is to avoid reading the large data file into memory. Instead, you can create the footer in a separate, small file and then append it.

Adjust your package to first generate the main data file
Then use a separate, simple Data Flow Task to create a second file containing only the footer text.
Finally, add an Execute Process Task at the end of your control flow to run a copy command.

If you still encounter issues after implementing the fix above, please check these within your main Data Flow Task:

Data Type Validation: Double-check that the data types and lengths defined in your Flat File Connection Manager exactly match the WITH RESULT SETS definition in your EXECUTE statement.
Column Mappings: Open your Flat File Destination, go to the Mappings tab, and ensure every column from the source is correctly mapped to its destination column.
Use a Data Viewer: Right-click on the data path arrow between your source and destination and select Enable Data Viewer. When you run the package in debug mode, a window will pop up showing you the data as it flows through that point.

Please try the recommended solution for the footer first, as it is the most likely cause.

I hope this helps.

Shruti Dhruv 100 Reputation points Microsoft External Staff Moderator

2025-10-10T13:18:36.3566667+00:00

Hi @Naomi Nosonovsky ,

I hope the explanation provided has helped you resolve the issue. If you have any questions feel free to reach out.

Thank You

Share via

Big file with lots of columns doesn't output content of many of them

0 additional answers

Your answer