Поделиться через

Query Designer in SSIS 2008 builds bad query when column name is same as table name with SQLNCLI10 Provider

  • Статья

I was working on a case yesterday and an interesting issue came up. A customer was migrating an SSIS 2005 package to 2008. While doing so, he was trying to modify the query from the Query Builder in the OLEDB Source. The Query Designer was surprisingly modifying the query into a bad syntax and that was resulting into a Syntax error. But if we don’t use the Query Designer the query was being parsed without any error. I was able to repro the issue with this query:

 SELECT CustomerName.CustID, CustomerName.City, CustID.PhoneNo
FROM CustomerName INNER JOIN

CustID ON CustomerName.CustID = CustID.CustID

Notice that the table CustID has a column with the same name, i.e. CustID. As a result the query was being modified inside Query Designer:

 SELECT CustomerName.CustID, CustomerName.City, dbo.CustomerName.CustID.PhoneNo
AS Expr1
FROM dbo.CustomerName INNER JOIN
dbo.CustID ON CustomerName.CustID = dbo.CustomerName.CustID.CustID

However if we open up a Query Designer and try to build the query inside it from scratch, it was able to create it. But if we provide it with a pre-written query which has a column name same as its table name, it was not able to reverse engineer the query. I tested this with the Management Studio Query Designer, and that does not have this issue. I also tested it with BIDS 2005 Query Designer and that does not have this issue either.

It was odd because the Query Designer code base was not really changed. So the only difference I can see is the provider it was using to build the Query. BIDS 2005 OLEDB Source uses a connection manager with the SQLNCLI OLEDB Provider. When we migrate the package to SSIS 2008, the provider automatically changes to SQLNCLI10. Whereas when you use the same Query Designer from SQL Management Studio, it will use .NET SqlClient Provider.

An easy workaround to avoid this would be to use the OLEDB Source in SSIS 2008 with a connection manager using the SQLNCLI Provider instead of SQLNCLI10.