Cannot change SQL command text in Data Flow Task

Question

I have an SSIS package that extracts data from Teradata into a SQL Server DB.

I'm using SQL Server 2008 R2 EE x64, and Visual Studio 2008 PE (BIDS) supplied with it, accessing Teradata v13.

In the Integration Services project, I have a Data Flow Task, it has an ADO .net source which has Data access mode set to “SQL Command”.

This worked for a while when I initially entered a SQL statement to select data.

But, when I change the existing SQL Command text and save the package, the changes are lost.
It keeps going back to the original SQL Statement.
It is currently “select col1, col2, … col10 from view1”.
When I change it to anything else, like “select col5 from view1”, and save, and then double click the ADO NET source again, I find that the SQL command text is still the old one. It goes back to the previous statement.

I’ve tried other statements like “exec macro” for Teradata, etc. but the same thing keeps happening - changes are not saved.

Does anyone have any ideas on this, or have you seen this before?

Answer 1

This is odd, but seems to be a component metadata corruption; so why don't you:

1. delete the teradata connector and source component, then re add them back, see if it now accepts the new SQL statement, if this does not work
2. abandon this package and create a new one replicating the functionality incorporating the new SQL.

In case #1 and 2 both fail post here any errors observed and find out whether you are missing any updates to either Teradata provider or SQL Server

---

Arthur My Blog

Answer 2

Thanks... that worked.

I deleted the ADO NET source and added a new one, then it seems to work.

Not sure why that happened, but hopefully I won't have to do that the next time I want to make a change to the SQL statement...