Problems using SSIS w/ non-SQL Server data sources

I've encountered a few problems using SSIS against non-SQL Server data
sources and was hoping that others might have some experience. Google
searches and browsing MSDN hasn't led me to a solution, so any advice
on the following is appreciated:

1) When using the "Data Source Views" wizard to add a data source
view from an ODBC data source, only tables appear in the object
listing. Views do not. (I've also observed this with SQL Server 2005
databases as well, but it's a bigger issue when you can't use the native SqlClient, as is the case for many ODBC-only databases.) ODBC traces show that both table and
view metadata is being returned to SSIS correctly, so it appears as
though SSIS is filtering out views from the object listing in the
wizard.

2) When creating named queries against (non-SQL Server) ODBC data
sources, SSIS appears to use the SQL Server SQL syntax for referencing
schemas/objects (e.g. "SELECT * FROM [schema].[table]", rather than
"SELECT * FROM schema.table"). This isn't valid SQL in many databases.

Am I missing something? Is there a way to change this through some
configuration setting?

Hello Christopher,

I think you may be burning a lot of time unproductively on "Data Source Views". From my understanding, for SSIS they are only a way to assist in generating a SQL statement for use in SSIS. Once generated, SSIS stores the resulting SQL.

So to my mind, why bother? I prefer to ignore "Data Source Views" and just work directly in the package.

SSIS is so fussy and pedantic that I don't think you will get any "metadata management" benefit from a DSV - pretty much any change in the schema will be a breaking change.

Good luck

Mike

|||

I think Mike is right in his first statement. DSV are not necessary (and may be not recommended) when working in SSIS; just create your connection directly against the sources.