Help - SQL 2005 SSIS - MAS 3.61 ODBC Connection 2

[Kruken]

We currently have a SQL 2000 Datamart that queries the MAS 3.61 using the SOTAMAS90 ODBC connection in silent mode.

We are migrating our systems to new servers and have deployed an instance of SQL 2005 Enterprise edition with SSIS ... for the life of me i cannot pull in tables over the ODBC connection like i can in SQL 2000 ... has anyone had any luck with this ?

Thanks !

 

[Kruken]

As of yet the only way i have been able to pull data from MAS into SQL 2005 is by creating DTS jobs in SQL 2000 , Exporting them to files , importing them into SQL 2005 , and running the legacy dts jobs in SSIS packages.

Hopefully someone has found a more robust solution .... I did call Sage , and they informed me that SQL 2005 was in fact not supported , however from past experience i know that the solution will find itself in a community like this one before Sage ever discusses it ...

Thanks

 

[richcwi]

The reason the ODBC driver does not work in SQL Server is because the ODBC driver does not implement the MTS (Microsoft Transaction Server) interface. SQL Server implements MTS and if the ODBC driver does not - well then it just don't work. So forget about creating a linked server from SQL Server to MAS – you won’t be able to!

Some programs don't implement MTS (like Access or SSIS) so the driver behaves as expected (pretty much so).

We have SQL Server 2005 and Visual Studio 2005. You can connect to the MAS ODBC driver in Visual Studio (our development environment) and create SSIS (aka DTS) packages, test and publish those packages to SQL Server 2005. SSIS in SQL Server 2005 does not implement MTS so the package when executed will connect to the ODBC driver and extract MAS table data.

Let me know if you need any more info on how to do this.

Rich

 

[Kruken]

Thanks for the clarification ... I had come up with much of the same conclusions , except for getting the MAS ODBC connection working properly with SSIS , I am able to get the connection to work when using legacy dts packages from SQL 2000 in SSIS of SQL 2005 (.dts) however I am unable to connect using the MAS ODBC driver when creating new SSIS packages (.dtsx) from within visual studio 2005...

I cannot get the driver to list the tables when creating a data flow ... did you use any particular settings (i created a silent DSN)

Thanks

 

[richcwi]

Kruken,

First we are on MAS 3.71 and using ODBC driver 3.33.10.00 (which you can get from ProvideX - let me know if you need it).

You create a silent named DSN.

In the VS2005 Integration Services Project you first need to create a Connection Manager to the silent DSN. So in the Connection Manager tab right mouse and select "New ADO.NET Connection...". This will bring you to the "Configure ADO.NET Connection Manager" dialog. Click on the New button. In the "Provider" drop down select ".NET Providers\Odbc Data Provider". Then select "Use user or system data source name:" and in the drop down select your silent named DSN. Then click the "Test Connection" button. If its successful then you have a good connection manager with the name of your silent DSN.

In the "Data Flow" tab drag a "DataReader Source" control on to the canvas. Edit it's properties and in the "Connection Managers" tab select the name of the connection manager you just created above. Then click on the "Component Properties" tab and enter the sql statement for the table you want to extract data from - for example "select * from IMA_ProductLine". Then Click on the "Column Mappings" tab and all the columns in that table should appear.

I assume you are going to copy tables/fields in MAS to SQL Server tables. For each table you copy you will need one of the Data Readers (as described above) a Data Conversion task and a OLE DB Destination task (your SQL Server connection). You will have to use the Data Conversion task to convert WSTR to STR and DateTime to Date in order to load extracted data into SQL Server.

Let me know if this helps....

Rich

 

[Kruken]

Thanks very much for the info Rich ,

My migration to SQL 2005 is acutally part of my upgrade project to 3.71 , however im still testing the SQL 2005 datamart using 3.61 for the next couple weeks ,

Where exactly can i get the updateed providex drivers ?... I've often heard people mentioning upgrading the ODBC driver but never saw a mention of it on Sage's website.

Thanks very much for the detailed explanation ... will try this ASAP , I would much prefer to redesign my Jobs in SSIS to benefit from the new features , rather than using legacy dts jobs.


Thanks Again !

 

[richcwi]

Using MAS 3.xx the max ODBC upgrade covered under the MAS license key is ODBC V3.33.10.00

You can get this at

http://www.pvx.com/downloads/odbc/archive.htm

Download "ODBC Local Driver 3.33" (I do it without MDAC).

When you install select the "Read Only" driver (this is the one covered under the 3.xx license key). If you select the 'Read/Write" you will be prompted for license key information (ProvideX license key info - which you don't have) - (Writing to MAS tables is nearly impossible anyway!).

After the install, under ODBC Drivers, you will see "MAS 90 32-Bit ODBC Driver" Version 3.33.10.00 and "ProvideX 32-Bit ODBC Driver" Version 3.33.10.00. They are the same thing - just use the MAS one when creating ODBC entries.

Also after the install be sure to select each of your MAS dsn's in ODBC and "Configure" them again. Just click throught the tabs and run the Test Connection.

Rich

 

[BigLouie]

If you select the 'Read/Write" you will be prompted for license key information (ProvideX license key info - which you don't have) - (Writing to MAS tables is nearly impossible anyway!)."

The Read/Write ODBC driver was NOT designed for or intended for use with MAS90 or MAS200. Any attempts to use this driver will result in 100% data corruption 100% of the time.

 

[Kruken]

Thanks for the heads up BigLouie ... the reason i was aware of this beforehand was simply because , i believe this is the statement you have had to repeat the most in this forum , and i have seen it many times (Mostly in the context of VI)

Proceeding with the upgrade of the Providex ODBC Driver from version 3.21 to 3.33 now . Will let you know if it resolves my issues in SQL 2005 and Visual Studio.