March 2, 2012 at 10:43 am
Hi,
We have a new Attunity Connect driver to access this legacy relational database platform. We have an ODBC DSN setup which easily lets us access data via Excel 2007/2010.
Within SSIS I can also connect to the Tandem server in the Server Explorer window, simply adding it as an ODBC server. This exposes all the tables in the SQL catalog defined in the ODBC DSN. This lets us Retrieve Data using the right-click context option and the data flows forth. Lovely so far.
However, I am now struggling with exposing the same list of tables in a Data Flow / Data Flow Source connection.
I have added a data flow task to the control flow, and then added an ADO.NET, data flow source object to the data flow. I have also created a Tandem data source, which is using the System.Data.Odbc Provider with the standard Connection string of "Dsn=TandemCatalogName;Uid=:pwd=*******".
Then in the ADO.NET source editor I have created a new connection, using that ODBC DSN. however, when I try to expose the list of tables that are displayed in the Solution Explorer panel, I get an error
"Could not retrieve the table information for the connection manager "Tandem Name". Object reference not set to an instance of an object.".
I know that we only have the ODBC drivers (Attunity) and not the ADO .NET versions. However, when I use the OLE DB data flow source object, it does not expose the DSN and I don't know how to specify the server name as you would for a SQL Server.
I have read in MS Help online that there is meant to be an ODBC Connection Manager, as well as many others that don't display in the Toolbox (eg. SMO, SMTP, WMI, FTP etc).
Do I need to use a different Control Flow object, to contextually reveal the ODBC Connection Manager, as all the help suggests is there to use and I can't find any download links for it.
Interestingly, an Attunity option does appear within the OLE DB Source Editor, Native OLE DB list. Its called "Attunity OLEDB Provider and Query Processor for Heterogeneous Data Sources". I am assuming because it appears in this list it is deployed on my PC, but at this stage I can't verify either way. It could be that all client drivers deploy but we only have the ODBC Server component.
I am an experienced Tandem DBA and a beginner SQL Server SSIS designer, for context on what I currently understand. I have created and run some basic packages that load data from one SQL Server 2008 database to another merging in records from CSV files etc. The above issue is using Visual Studio 2008, Version 9.0.30729 and Tandem NonStop SQL / MP on G06.18.
Thanks in anticipation of any help.
Cheers
Eric
March 3, 2012 at 4:45 am
So I have been able to create an ODBC Connection in the Connection Managers pane at the bottom of the SSIS project area, by right clicking and choosing the last option, "New Connection". This exposes the full list of connection manager types.
This means I have an ODBC connection manager, using the previously mentioned DSN and the 'Test Connection' works fine.
Now I am stuck on which Data Flow Sources object to use to within the Data Flow workspace. The ODBC connection manager I have created is not exposed in either the ADO NET or OLE DB objects.
Do I need to use a different type of Control Flow Item to access an ODBC data feed, such as Execute SQL Task?
March 3, 2012 at 12:31 pm
The solution to this is to use a SQL query rather than Table or View in the ADO.NET connection source editor.:-D
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply