Hi,
doing my first steps in SSIS I wanted to copy data from 2 different SQL 2000 database servers to a SQL 2005 Data warehouse. For not having to deploy additional views and procedures to the individual systems I chose to create a Data Source View to create an abstract view on the different data sources. I found out that I can have named queries pointing to the two different data sources in the same view.
1 Project, 2 Data Sources, 1 Data Source View with 3 Named Queries
When I now add a Data Flow Task to the Control Flow how can I specify my DSV as Source for Transformations? I even added both OLEDB Connections to the Connection Manager but the Named Queries from my DSV do not appear at all. I even tried "SELECT from [myNamedQueryFromDSV]" but without success.
The description available at http://msdn2.microsoft.com/en-us/ms403395.aspx is bullshit. There is nothing to expand about the "Connection Manager" in the Data Flow Window. I can add a OLEDB Source as described in the above HOWTO and double click it. But the dropdown field for Connection Manager does offer only the two OLEDB Connections and nothing more. Among the items of the access mode "Tables and Views" the named queries not appear. It does not even work with a homogeneous Data Source View.
How can I make it work? Ain't there a better (working) HOWTO out there on how to enable DSV als Data Flow Task data sources? Do I have to wait for SP2 to solve the problem or is it not possible by intention?
Cheers,
Frank
You say you "added both OLEDB Connections to the Connection Manager." I am not sure exactly what you mean, but I wonder if you right-clicked in the Connection Managers tray and added an OLEDB connection. That would not help you use a Data Source View.
As the splendidly written and accurate article at http://msdn2.microsoft.com/en-us/ms403395.aspx describes through its links, you should in fact add a Connection from a Data Source: http://msdn2.microsoft.com/en-us/ms403395.aspx.
You can tell if you have a Connection Manager that enables access to Data Source Views by looking at the icon. If it looks like a Data Source icon, you're in business. If it looks like an OLEDB Connection Manager icon, then you will not be able to see any Data Source Views.
Donald
|||Hi Donald,
thanks for replying. Your hint about the Icon that should be different led me to the right actions.
But regarding the quality and accurateness of the article I have to disagree.
Please look add the following quote taken directly from the article:
"After a data source and its data sources views are in the Integration Services project, you add must add a connection manager that references the data source to the package. When you add the connection manager based on a data source to a package, Integration Services adds an OLE DB type connection manager. Only sources, transformations, and destinations that can use an OLE DB connection manager can use data source views."
It tells that an OLE DB Type connection manager is added and that DSVs only work with OLE DB connection managers. There's no real usable hint, that I need to create some-kind-of-reference connection. From reading the article I thought I need to add the OLE DB connection that I used already for the DSV. "New Connection from Datasource" as an option is not quite clear in its meaning. Where's the difference to the normal OLEDB Connection? It looks to be linking to that OLE DB connection underneath anyway.
And one problem remains: As my DSV does have Named Queries pointing to both OLE DB datasource connections (2x datasource 1, 1x datasource 2) I only see the 2 first named queries using the connection manager but I never see the 3rd from the other datasource regardless if I also create a "New Connection from Datasource" from the second one and specify that one for the OLE DB Source. So heterogeneous DSVs are not supported?
Best regards, Frank
|||The doc is indeed accurate, although maybe I should not overpraise its style. :-) The following extracts do seem very clear to me and even include a link to detailed info on how to add the data source.
... a connection manager that references the data source
... add the connection manager based on a data source
Before you can use a data source view in a package, you must add the data source, on which the data source view is built, to the package. For more information, see How to: Add a Data Source Reference to a Package.
However, the reader is always right, and if you did not find the docs helpful, then that is fair enough. The information you needed was there, but you could not find it. It would be great if you could provide some detail on how you think they could have been better worded, or more clearly structured, to help us help others.
Meanwhile, you are indeed correct that you can only use one (the default) data source in a data source view with SSIS.
Donald
|||Hello once again,
Donald Farmer wrote:
Before you can use a data source view in a package, you must add the data source, on which the data source view is built, to the package. For more information, see How to: Add a Data Source Reference to a Package.
This is exactly one of the misleading statements. I shall add the data source on which the DSV was built. And for the DSV I have added two OLE DB Data Sources to the project. So my conclusion would be to reuse them. When I right-click the connection manager of the package I can choose between several types of connections to be added. And as the article also states that DSVs only work with OLE DB connections my first choice would be to select "New OLE DB Connection ...". Doing so pops up a dialog with the OLE DB Data Sources I have already defined in the project and used for the DSV.
Maybe this would be a better explanation:
Before you can use a data source view in a package, you must add a data source reference ("New Connection from Datasource") to the project data source object, on which the data source view is built, to the package. Your project data source object should be an OLE DB data source. For more information, ...
And for the future it would be nice if the SQL 2005 Development team could allow something like "New Connection to Data Source View" which would be (imho) very user friendly.
As for the restriction that only the Named Queries / Table Queries basing on the default data source in a DSV can be used in packages - a hint in the documentation would be very appreciated - and surely not only by me. Is that a restriction by intention or is it a bug going to solved in the future?
I highly appreciate your dealing with this topic
Kind regards, Frank
|||I agree that the documentation is not clear. I'm still struggling trying to get this setup.