CODEWITHSUNDEEP
c#sql-serverssisado.net
Corez
Corez

Reputation: 354

SSIS: How to fix warning "the output column is not subsequently used" from ADO NET Source

I'm writing an SSIS package to take some text data, join it with a field from a table in SQL Server, and then insert the data into a table. I'm using the SQL Server Data Tools 2015 and SQL Server 2014.

I have configured a connection manager with an ADO NET connection to the database. In this particular Data Flow component I have an ADO NET Source that highlights a table in that database. I take the table data, sort it and then do a Merge Join. I only need Id and Field1 from that ADO NET Source. Let's say that it has Field2 and Field3 as well for the sake of discussion.

I get the following warnings for Field2 and Field3 when I run the job:

[SSIS.Pipeline] Warning: The output column "Field2" (45) on output "ADO >NET Source Output" (42) and component "ADO NET Source" (34) is not subsequently >used in the Data Flow task. Removing this unused output column can increase >Data Flow task performance.

I have a lot of data that I'm working through so I'd prefer not to bring this data into memory if I can avoid it. I assumed the that way to go is to delete it from the ADO NET Source by removing an output column. However, when I double click on the ADO NET Source and select Columns I see the table with External Column on the left and Output Column on the right. When I right click one of these column mappings none of the options in the context menu are enabled (Cut, Copy, Paste, Delete)

When I go into the Advanced Editor View (by right clicking on the ADO NET Source and selecting Show Advanced Editor) I then navigate to the Column Mappings tab. I delete the mappings for the columns that I don't need and select OK. When I open the Advanced Editor again the mappings show up again as if they were not deleted. Also when I go to Input and Output Properties and select "Remove Column" I get the following error:

The column cannot be deleted. The component does not allow columns to be >deleted from this input or output. Pipeline component has returned HRESULT error code 0xC0208018

Does anyone know how I can trim down the data coming in from the ADO NET Source?

Upvotes: 2

Views: 7861

Answers (2)

Dariusz Bielak
Dariusz Bielak

Reputation: 415

So you have few options here:

  • In the Data Source task you can select SQL Command as the data access mode and craft the SQL command which will only select the columns you are after. You can also apply sort here.

    • Create a view in the source database with only the columns which you require in your SSIS process and reference this view in the data source task

Second option allows you to pass only the columns you are after but you still need to apply sort.

First option will allow you to sort the records in the database, which is the more preferable way, also you can set the sort options in the advanced editor, which means that you do not need to use the sort task. However you are ending up with the embedded SQL which you have to maintain. I think that you can use stored procedures in this step, which will allow you to move maintenance to the database and reuse a code if needed.

Upvotes: 1

Amit Sukralia
Amit Sukralia

Reputation: 950

You will have to remove the columns which you don't need by writing the SQL rather than selecting the table itself in the Source. When you select the table, it brings up all the columns in the table as the source/input. Therefore, write a SQL query to fetch only those columns which you need.

Upvotes: 0

Related Questions