Re: [U2] Error connecting to UD 5.2 from SQL Server 2008 SSIS [ad]
Thanks Tony, but I think I will just wait until I've convinced management to approve the upgrade to 7.2 I know that release can talk to SSIS. We are talking hundreds of files here for that conversion and maybe thousands of Oracle tables, and I don't think I'm going to create classes for every attribute in every UniData file and SQL statements to extract the data, translate and load it into Oracle tables. With SSIS it's nearly all just drag and drop - no code writing required - a proper ETL tool! Unfortunately it doesn't seem to be able to connect to UD 5.2 because the OLEDB versions from Informix and Microsoft don't want to talk to each other. I had hoped somebody here could tell me what string literals are supposed to be and why I can't get them. In the meantime they can carry on working with their spreadsheets :-) And maybe I can convince them to send me to school again, so I can understand all the buzz-words you throw around. Mecki Tony Gravagno wrote: From: Mecki Foerthmann Thanks Tony and Peter, but I don't think yet another piece of software is what I need. Your product might be useful for some of your clients, but I don't think it will help solve my problem. That's funny. I don't have an application and there is no real way to categorize our clients, except that they're mostly MV developers who had a problem to solve. Many of our clients have come from this forum. I think you're missing the point of the blog. It wasn't to sell a product (that one isn't ready yet anyway) but to explain how I'm able to create a wide range of solutions based on the way I approach problems. If you continue to think of each problem you present here as a nail, the only solutions you're going to consider are the ones that look like a hammer. Think differently and you won't need to buy other people's solutions, you can probably come up with your own. (That said, the tools I've chosen help me to do what I do, and because I'm happy with those tools I've chosen to sell them too.) I don't want to update any MV files, just load some data into a relational database every night, preferably as cubes. Setting the MV files up as SQL tables is not a problem in UD either. I've already done that and have a reasonably easy to use tool for that in Avante. The point of my blogs is that you don't need to do that, and FWIW, this forum is frequented by people who have had problems doing that. So I discourage the approach. I've even written Basic code to flatten a file with large multivalues because DTS UniSQL in UD 5.2 can't handle them properly. And you don't need to flatten files either. :) This is all trivial stuff. All I want is a cheap ETL tool that works with UD 5.2, so I can build a datawarehouse in Oracle and write reports using Cognos. mv.NET Solution Objects (one of the libraries in the package and not currently used in the solution described in the blog) allows you to create strongly typed classes for any application. You can use these with other applications that can make use of business rules via an Object Relational Model (ORM). So the external environment doesn't see Accounts or Files or Dicts, it seems a Customer class and properties that you've chosen to share with specific environments, like Name and City. My approach would be to create an SDK for Avante (ManFact, Dataflo, CUBS, Datatel, SHIMS, or any MV application), then create an interface for your external database of choice, then create a bridge between them. Again (refer to my blog) note that the approach here isn't to connect X to Y but to get interfaces for X and Y and then connect them in the middle. Then you can use your X and Y interfaces with any other project. If you connect X directly to Y now, you'll be here next month asking about W and Z. I have to use Oracle and Cognos, I don't have any say in that matter. That's fine, so ETL your data to Oracle as I've described, then use Cognos against that if you wish. What's the problem? Currently I use SQL Server 2000 and DTS. It works, but it uses ODBC and is not an ETL tool and produces only SS 2000 tables! I could of course use DTS and ODBC to bring the data first from UD to SS 2000 and then use SSIS to build my datawarehouse in Oracle, but do I really want to do this? No, you're smashing everything with a hammer. Pull your data from Unidata into Oracle and then work directly from there. Why involve SQL Server at all? OK, here's a completely different approach. Use MV BASIC to generate Oracle-specific SQL queries into an OS file. Loop on your data, wherever the files are, and populate a query template for each row of relational data. Then move those files manually or automatically to the other environment and execute the queries to insert new data into Oracle. This approach requires no connectivity, no MV interfaces, no data
Re: [U2] Error connecting to UD 5.2 from SQL Server 2008 SSIS [ad]
snip Tony Said Here's another common issue in this forum: People express a desire for a solution but what they really want is a free solution that they can use, understand, and maintain on their own. I'm sorry but when you're faced with complex issues, be prepared for solutions that you may not understand, and be prepared to pay for solutions or consulting time from people who understand how to solve the problems you express. You (all U2 IT people) are getting paid for your expertise by a company that is obviously spending lots of money on the IT infrastructure. By insisting you need everything for free and on your own terms you're just perpetuating the belief of your management that you don't need a budget while everyone else in the company apparently does - and they get it. MV development is cheap and easy. But when you need to interface with the outside world, you're not doing MV development any more and you need to face the same budgetary and educational concerns as anyone else who works with the same technologies. I just think MV people in general need to stop thinking that somehow they're going to magically understand everything when everyone else needs to go to school, and that BASIC will solve every problem. These beliefs are self-defeating for this entire market. /snip Loving it ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Error connecting to UD 5.2 from SQL Server 2008 SSIS [ad]
You may not want to do this due to volume of data your trying to pull, but... Have you tried setting up your UD5.2 server as a linked server in SQL, then creating views to your linked server. Then use SSIS to pull from the views, that way it doesn't know it isn't a SQL data source? Performance isn't bad if your limiting your I-desc and keep the data to single files in UD. Also use indexes in your UD views to limit your data. You can't pass variables for data selection this way. You can limit data by day using @date-99 type i-descriptors or just write a quick program to pull your data to a temp file, then pull the file for updating things incrementally for Cognos. Jeffrey Lettau ERP Systems Manager polkaudio -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Mecki Foerthmann Sent: Thursday, January 14, 2010 3:31 AM To: U2 Users List Subject: Re: [U2] Error connecting to UD 5.2 from SQL Server 2008 SSIS [ad] Thanks Tony, but I think I will just wait until I've convinced management to approve the upgrade to 7.2 I know that release can talk to SSIS. We are talking hundreds of files here for that conversion and maybe thousands of Oracle tables, and I don't think I'm going to create classes for every attribute in every UniData file and SQL statements to extract the data, translate and load it into Oracle tables. With SSIS it's nearly all just drag and drop - no code writing required - a proper ETL tool! Unfortunately it doesn't seem to be able to connect to UD 5.2 because the OLEDB versions from Informix and Microsoft don't want to talk to each other. I had hoped somebody here could tell me what string literals are supposed to be and why I can't get them. In the meantime they can carry on working with their spreadsheets :-) And maybe I can convince them to send me to school again, so I can understand all the buzz-words you throw around. Mecki Tony Gravagno wrote: From: Mecki Foerthmann Thanks Tony and Peter, but I don't think yet another piece of software is what I need. Your product might be useful for some of your clients, but I don't think it will help solve my problem. That's funny. I don't have an application and there is no real way to categorize our clients, except that they're mostly MV developers who had a problem to solve. Many of our clients have come from this forum. I think you're missing the point of the blog. It wasn't to sell a product (that one isn't ready yet anyway) but to explain how I'm able to create a wide range of solutions based on the way I approach problems. If you continue to think of each problem you present here as a nail, the only solutions you're going to consider are the ones that look like a hammer. Think differently and you won't need to buy other people's solutions, you can probably come up with your own. (That said, the tools I've chosen help me to do what I do, and because I'm happy with those tools I've chosen to sell them too.) I don't want to update any MV files, just load some data into a relational database every night, preferably as cubes. Setting the MV files up as SQL tables is not a problem in UD either. I've already done that and have a reasonably easy to use tool for that in Avante. The point of my blogs is that you don't need to do that, and FWIW, this forum is frequented by people who have had problems doing that. So I discourage the approach. I've even written Basic code to flatten a file with large multivalues because DTS UniSQL in UD 5.2 can't handle them properly. And you don't need to flatten files either. :) This is all trivial stuff. All I want is a cheap ETL tool that works with UD 5.2, so I can build a datawarehouse in Oracle and write reports using Cognos. mv.NET Solution Objects (one of the libraries in the package and not currently used in the solution described in the blog) allows you to create strongly typed classes for any application. You can use these with other applications that can make use of business rules via an Object Relational Model (ORM). So the external environment doesn't see Accounts or Files or Dicts, it seems a Customer class and properties that you've chosen to share with specific environments, like Name and City. My approach would be to create an SDK for Avante (ManFact, Dataflo, CUBS, Datatel, SHIMS, or any MV application), then create an interface for your external database of choice, then create a bridge between them. Again (refer to my blog) note that the approach here isn't to connect X to Y but to get interfaces for X and Y and then connect them in the middle. Then you can use your X and Y interfaces with any other project. If you connect X directly to Y now, you'll be here next month asking about W and Z. I have to use Oracle and Cognos, I don't have any say in that matter. That's fine, so ETL your data to Oracle as I've
Re: [U2] Error connecting to UD 5.2 from SQL Server 2008 SSIS [ad]
Thanks Jeff, I might give that a try. Even though the creating views bit doesn't sound too appealing. I actually have been thinking to start off by leaving it as is and pull the data from SS 2000 instead of UD 5.2 directly since the table structure is exactly the same and the updating of the SS 2000 tables with DTS every night is so far working OK. Once I get the upgrade to 7.2 done, I will have the SSIS projects for the datawarehouse all in place, only need to change the data source and everything should be working just fine. Mecki Original-Nachricht Datum: Thu, 14 Jan 2010 08:43:06 -0500 Von: Lettau, Jeff jlet...@polkaudio.com An: U2 Users List u2-users@listserver.u2ug.org Betreff: Re: [U2] Error connecting to UD 5.2 from SQL Server 2008 SSIS [ad] You may not want to do this due to volume of data your trying to pull, but... Have you tried setting up your UD5.2 server as a linked server in SQL, then creating views to your linked server. Then use SSIS to pull from the views, that way it doesn't know it isn't a SQL data source? Performance isn't bad if your limiting your I-desc and keep the data to single files in UD. Also use indexes in your UD views to limit your data. You can't pass variables for data selection this way. You can limit data by day using @date-99 type i-descriptors or just write a quick program to pull your data to a temp file, then pull the file for updating things incrementally for Cognos. Jeffrey Lettau -- GRATIS für alle GMX-Mitglieder: Die maxdome Movie-FLAT! Jetzt freischalten unter http://portal.gmx.net/de/go/maxdome01 ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Error connecting to UD 5.2 from SQL Server 2008 SSIS
This problem resembles my recent problem with ODBC between HP-Ux and MS-SQL. Tech Support at Rocket indicated that I would probably have to buy special (ODBC) drivers from companies that specialize in data integration because... ODBC is a Microsoft thing and this sort of connectivity is not bundled with most (maybe any) flavors of Unix. Rocket agreed that using UniObjects would be an easier alternative. --Bill -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Mecki Foerthmann Sent: Wednesday, January 13, 2010 7:39 AM To: U2 Users List Subject: [U2] Error connecting to UD 5.2 from SQL Server 2008 SSIS Hi I am trying to use SSIS to load UniData tables to SQL Server 2008. I already have been using DTS with SQL Server 2000 successfully for a while and I set up everything (System DSN and UCI Editor) the same on the 2008 server. The connection test works fine, but when I try to go further I get this error message. Cannot get string literals from the database connection Provider=Informix.UniOLEDB.1;Password=;Persist Security Info=True;User ID=admin;Data Source=LIVE_DATA;Location=LIVE_DATA;Mode=Read We are running Unidata 5.2 on Solaris. And I am using UniDK for that release on both SQL Server boxes. I had tried using a later version of UniDK before, but that didn't seem to want to connect to the older version of UD at all. Any ideas? Thanks Mecki ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Error connecting to UD 5.2 from SQL Server 2008 SSIS
Thanks Bill, I don't think it is an ODBC problem since SSIS connection managers unlike DTS don't use ODBC. They use OLEDB and connect even without the IBM U2 ODBC driver installed. I maybe have to add that both SQL Server 2000 and 2008 run under Windows 2003, so I don't think this is an OS issue either. If anybody has any suggestions how to use UniObjects with SSIS I would give that a try immediately. Easiest would of course be to upgrade to UD 7.2 but unfortunately I haven't been able to convince management to approve that. Mecki Original-Nachricht Datum: Wed, 13 Jan 2010 10:36:27 -0500 Von: Brutzman, Bill bi...@hkmetalcraft.com An: U2 Users List u2-users@listserver.u2ug.org Betreff: Re: [U2] Error connecting to UD 5.2 from SQL Server 2008 SSIS This problem resembles my recent problem with ODBC between HP-Ux and MS-SQL. Tech Support at Rocket indicated that I would probably have to buy special (ODBC) drivers from companies that specialize in data integration because... ODBC is a Microsoft thing and this sort of connectivity is not bundled with most (maybe any) flavors of Unix. Rocket agreed that using UniObjects would be an easier alternative. --Bill -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Mecki Foerthmann Sent: Wednesday, January 13, 2010 7:39 AM To: U2 Users List Subject: [U2] Error connecting to UD 5.2 from SQL Server 2008 SSIS Hi I am trying to use SSIS to load UniData tables to SQL Server 2008. I already have been using DTS with SQL Server 2000 successfully for a while and I set up everything (System DSN and UCI Editor) the same on the 2008 server. The connection test works fine, but when I try to go further I get this error message. Cannot get string literals from the database connection Provider=Informix.UniOLEDB.1;Password=;Persist Security Info=True;User ID=admin;Data Source=LIVE_DATA;Location=LIVE_DATA;Mode=Read We are running Unidata 5.2 on Solaris. And I am using UniDK for that release on both SQL Server boxes. I had tried using a later version of UniDK before, but that didn't seem to want to connect to the older version of UD at all. Any ideas? Thanks Mecki ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users -- GRATIS für alle GMX-Mitglieder: Die maxdome Movie-FLAT! Jetzt freischalten unter http://portal.gmx.net/de/go/maxdome01 ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Error connecting to UD 5.2 from SQL Server 2008 SSIS
Is 5.2 still supported? That may convince them that it is time to upgrade. I can't seem to find an availability matrix on Rocket's site. Jerry Banker -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Gerd Forthmann Sent: Wednesday, January 13, 2010 10:25 AM To: U2 Users List Subject: Re: [U2] Error connecting to UD 5.2 from SQL Server 2008 SSIS Thanks Bill, I don't think it is an ODBC problem since SSIS connection managers unlike DTS don't use ODBC. They use OLEDB and connect even without the IBM U2 ODBC driver installed. I maybe have to add that both SQL Server 2000 and 2008 run under Windows 2003, so I don't think this is an OS issue either. If anybody has any suggestions how to use UniObjects with SSIS I would give that a try immediately. Easiest would of course be to upgrade to UD 7.2 but unfortunately I haven't been able to convince management to approve that. Mecki Original-Nachricht Datum: Wed, 13 Jan 2010 10:36:27 -0500 Von: Brutzman, Bill bi...@hkmetalcraft.com An: U2 Users List u2-users@listserver.u2ug.org Betreff: Re: [U2] Error connecting to UD 5.2 from SQL Server 2008 SSIS This problem resembles my recent problem with ODBC between HP-Ux and MS-SQL. Tech Support at Rocket indicated that I would probably have to buy special (ODBC) drivers from companies that specialize in data integration because... ODBC is a Microsoft thing and this sort of connectivity is not bundled with most (maybe any) flavors of Unix. Rocket agreed that using UniObjects would be an easier alternative. --Bill -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Mecki Foerthmann Sent: Wednesday, January 13, 2010 7:39 AM To: U2 Users List Subject: [U2] Error connecting to UD 5.2 from SQL Server 2008 SSIS Hi I am trying to use SSIS to load UniData tables to SQL Server 2008. I already have been using DTS with SQL Server 2000 successfully for a while and I set up everything (System DSN and UCI Editor) the same on the 2008 server. The connection test works fine, but when I try to go further I get this error message. Cannot get string literals from the database connection Provider=Informix.UniOLEDB.1;Password=;Persist Security Info=True;User ID=admin;Data Source=LIVE_DATA;Location=LIVE_DATA;Mode=Read We are running Unidata 5.2 on Solaris. And I am using UniDK for that release on both SQL Server boxes. I had tried using a later version of UniDK before, but that didn't seem to want to connect to the older version of UD at all. Any ideas? Thanks Mecki ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users -- GRATIS für alle GMX-Mitglieder: Die maxdome Movie-FLAT! Jetzt freischalten unter http://portal.gmx.net/de/go/maxdome01 ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Error connecting to UD 5.2 from SQL Server 2008 SSIS [ad]
From: Gerd Forthmann I don't think it is an ODBC problem since SSIS connection managers unlike DTS don't use ODBC. They use OLEDB and connect even without the IBM U2 ODBC driver installed. In response to concerns expressed here and in all other posts like this, I've written a blog that describes a better approach, and a successful solution that implements the approach. See SQL Server and MV - Part 1 published today in my blog. If anybody has any suggestions how to use UniObjects with SSIS I would give that a try immediately. Since you asked: Contact me about mv.SSIS, which is a development component for SQL Server Integration Services. It's a super-set of mv.NET, and uses UniObjects _or_ UO.NET for transport with U2. This helps to avoid all of the problems documented in this thread. Easiest would of course be to upgrade to UD 7.2 but unfortunately I haven't been able to convince management to approve that. Not required with any of the solutions provided above. From: Brutzman, Bill This problem resembles my recent problem with ODBC between HP-Ux and MS-SQL. Tech Support at Rocket indicated that I would probably have to buy special (ODBC) drivers from companies that specialize in data integration because... ODBC is a Microsoft thing and this sort of connectivity is not bundled with most (maybe any) flavors of Unix. Rocket agreed that using UniObjects would be an easier alternative. The approach which I've documented would be exactly the same for all of these problems. Approach the problem properly and you won't need to worry about the tools. HTH Tony Gravagno Nebula Research and Development TG@ remove.pleaseNebula-RnD.com Nebula RD sells mv.NET and other Pick/MultiValue products worldwide, and provides related development services remove.pleaseNebula-RnD.com/blog Visit PickWiki.com! Contribute! http://Twitter.com/TonyGravagno ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Error connecting to UD 5.2 from SQL Server 2008 SSIS
You're right Jerry, since September of last year UD 5.2 isn't supported anymore . But firstly the company is planning to go to Oracle so they convinced the auditors to accept that. Furthermore we are an Epicor site, and from what I heard they made a special deal with IBM to keep supporting 5.2 for their customers a bit longer. Well, if you can call that what Epicor provide support, that is. All I got from them was, 'it should work, but you better find yourself an SQL expert'. I just thought it might be a cheap ETL solution to use SSIS to transfer data from UD to Oracle. Budget? As far as anything UD is concerned - zilch. They rather spend money on gadgets and Oracle consultants. Very frustrating, but what can you do? Mecki jpb-u2ug wrote: Is 5.2 still supported? That may convince them that it is time to upgrade. I can't seem to find an availability matrix on Rocket's site. Jerry Banker -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Gerd Forthmann Sent: Wednesday, January 13, 2010 10:25 AM To: U2 Users List Subject: Re: [U2] Error connecting to UD 5.2 from SQL Server 2008 SSIS Thanks Bill, I don't think it is an ODBC problem since SSIS connection managers unlike DTS don't use ODBC. They use OLEDB and connect even without the IBM U2 ODBC driver installed. I maybe have to add that both SQL Server 2000 and 2008 run under Windows 2003, so I don't think this is an OS issue either. If anybody has any suggestions how to use UniObjects with SSIS I would give that a try immediately. Easiest would of course be to upgrade to UD 7.2 but unfortunately I haven't been able to convince management to approve that. Mecki Original-Nachricht Datum: Wed, 13 Jan 2010 10:36:27 -0500 Von: Brutzman, Bill bi...@hkmetalcraft.com An: U2 Users List u2-users@listserver.u2ug.org Betreff: Re: [U2] Error connecting to UD 5.2 from SQL Server 2008 SSIS This problem resembles my recent problem with ODBC between HP-Ux and MS-SQL. Tech Support at Rocket indicated that I would probably have to buy special (ODBC) drivers from companies that specialize in data integration because... ODBC is a Microsoft thing and this sort of connectivity is not bundled with most (maybe any) flavors of Unix. Rocket agreed that using UniObjects would be an easier alternative. --Bill -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Mecki Foerthmann Sent: Wednesday, January 13, 2010 7:39 AM To: U2 Users List Subject: [U2] Error connecting to UD 5.2 from SQL Server 2008 SSIS Hi I am trying to use SSIS to load UniData tables to SQL Server 2008. I already have been using DTS with SQL Server 2000 successfully for a while and I set up everything (System DSN and UCI Editor) the same on the 2008 server. The connection test works fine, but when I try to go further I get this error message. Cannot get string literals from the database connection Provider=Informix.UniOLEDB.1;Password=;Persist Security Info=True;User ID=admin;Data Source=LIVE_DATA;Location=LIVE_DATA;Mode=Read We are running Unidata 5.2 on Solaris. And I am using UniDK for that release on both SQL Server boxes. I had tried using a later version of UniDK before, but that didn't seem to want to connect to the older version of UD at all. Any ideas? Thanks Mecki ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Error connecting to UD 5.2 from SQL Server 2008 SSIS [ad]
Thanks Tony and Peter, but I don't think yet another piece of software is what I need. Your product might be useful for some of your clients, but I don't think it will help solve my problem. I don't want to update any MV files, just load some data into a relational database every night, preferably as cubes. Setting the MV files up as SQL tables is not a problem in UD either. I've already done that and have a reasonably easy to use tool for that in Avante. I've even written Basic code to flatten a file with large multivalues because DTS UniSQL in UD 5.2 can't handle them properly. This is all trivial stuff. All I want is a cheap ETL tool that works with UD 5.2, so I can build a datawarehouse in Oracle and write reports using Cognos. I have to use Oracle and Cognos, I don't have any say in that matter. Currently I use SQL Server 2000 and DTS. It works, but it uses ODBC and is not an ETL tool and produces only SS 2000 tables! I could of course use DTS and ODBC to bring the data first from UD to SS 2000 and then use SSIS to build my datawarehouse in Oracle, but do I really want to do this? Well and later on I'll have to do the export of data from UD to the new Oracle based ERP package. I'll sure as hell will need a good ETL tool for that. This will require proper mapping into numerous Oracle tables and on D-day needs to bring everything over on a weekend. I had hoped with SSIS I had found just such a tool, if I could just get those blasted string literals from the database connection, whatever they are! Mecki Tony Gravagno wrote: From: Gerd Forthmann I don't think it is an ODBC problem since SSIS connection managers unlike DTS don't use ODBC. They use OLEDB and connect even without the IBM U2 ODBC driver installed. In response to concerns expressed here and in all other posts like this, I've written a blog that describes a better approach, and a successful solution that implements the approach. See SQL Server and MV - Part 1 published today in my blog. If anybody has any suggestions how to use UniObjects with SSIS I would give that a try immediately. Since you asked: Contact me about mv.SSIS, which is a development component for SQL Server Integration Services. It's a super-set of mv.NET, and uses UniObjects _or_ UO.NET for transport with U2. This helps to avoid all of the problems documented in this thread. Easiest would of course be to upgrade to UD 7.2 but unfortunately I haven't been able to convince management to approve that. Not required with any of the solutions provided above. From: Brutzman, Bill This problem resembles my recent problem with ODBC between HP-Ux and MS-SQL. Tech Support at Rocket indicated that I would probably have to buy special (ODBC) drivers from companies that specialize in data integration because... ODBC is a Microsoft thing and this sort of connectivity is not bundled with most (maybe any) flavors of Unix. Rocket agreed that using UniObjects would be an easier alternative. The approach which I've documented would be exactly the same for all of these problems. Approach the problem properly and you won't need to worry about the tools. HTH Tony Gravagno Nebula Research and Development TG@ remove.pleaseNebula-RnD.com Nebula RD sells mv.NET and other Pick/MultiValue products worldwide, and provides related development services remove.pleaseNebula-RnD.com/blog Visit PickWiki.com! Contribute! http://Twitter.com/TonyGravagno ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Error connecting to UD 5.2 from SQL Server 2008 SSIS [ad]
From: Mecki Foerthmann Thanks Tony and Peter, but I don't think yet another piece of software is what I need. Your product might be useful for some of your clients, but I don't think it will help solve my problem. That's funny. I don't have an application and there is no real way to categorize our clients, except that they're mostly MV developers who had a problem to solve. Many of our clients have come from this forum. I think you're missing the point of the blog. It wasn't to sell a product (that one isn't ready yet anyway) but to explain how I'm able to create a wide range of solutions based on the way I approach problems. If you continue to think of each problem you present here as a nail, the only solutions you're going to consider are the ones that look like a hammer. Think differently and you won't need to buy other people's solutions, you can probably come up with your own. (That said, the tools I've chosen help me to do what I do, and because I'm happy with those tools I've chosen to sell them too.) I don't want to update any MV files, just load some data into a relational database every night, preferably as cubes. Setting the MV files up as SQL tables is not a problem in UD either. I've already done that and have a reasonably easy to use tool for that in Avante. The point of my blogs is that you don't need to do that, and FWIW, this forum is frequented by people who have had problems doing that. So I discourage the approach. I've even written Basic code to flatten a file with large multivalues because DTS UniSQL in UD 5.2 can't handle them properly. And you don't need to flatten files either. :) This is all trivial stuff. All I want is a cheap ETL tool that works with UD 5.2, so I can build a datawarehouse in Oracle and write reports using Cognos. mv.NET Solution Objects (one of the libraries in the package and not currently used in the solution described in the blog) allows you to create strongly typed classes for any application. You can use these with other applications that can make use of business rules via an Object Relational Model (ORM). So the external environment doesn't see Accounts or Files or Dicts, it seems a Customer class and properties that you've chosen to share with specific environments, like Name and City. My approach would be to create an SDK for Avante (ManFact, Dataflo, CUBS, Datatel, SHIMS, or any MV application), then create an interface for your external database of choice, then create a bridge between them. Again (refer to my blog) note that the approach here isn't to connect X to Y but to get interfaces for X and Y and then connect them in the middle. Then you can use your X and Y interfaces with any other project. If you connect X directly to Y now, you'll be here next month asking about W and Z. I have to use Oracle and Cognos, I don't have any say in that matter. That's fine, so ETL your data to Oracle as I've described, then use Cognos against that if you wish. What's the problem? Currently I use SQL Server 2000 and DTS. It works, but it uses ODBC and is not an ETL tool and produces only SS 2000 tables! I could of course use DTS and ODBC to bring the data first from UD to SS 2000 and then use SSIS to build my datawarehouse in Oracle, but do I really want to do this? No, you're smashing everything with a hammer. Pull your data from Unidata into Oracle and then work directly from there. Why involve SQL Server at all? OK, here's a completely different approach. Use MV BASIC to generate Oracle-specific SQL queries into an OS file. Loop on your data, wherever the files are, and populate a query template for each row of relational data. Then move those files manually or automatically to the other environment and execute the queries to insert new data into Oracle. This approach requires no connectivity, no MV interfaces, no data modification - and it's completely free and you can do it yourself. This still follows my guideline of not attempting to do things directly. Well and later on I'll have to do the export of data from UD to the new Oracle based ERP package. I'll sure as hell will need a good ETL tool for that. This will require proper mapping into numerous Oracle tables and on D-day needs to bring everything over on a weekend. I had hoped with SSIS I had found just such a tool, if I could just get those blasted string literals from the database connection, whatever they are! Mecki All I can summarize with is that you asked for a way to get your data out with UniObjects, I proposed a plan for both a free and a product-based solution, and you said no. We've had many exchanges in these forums Mecki, and I have a lot of respect for you, no buts about it. My remaining comments refer to a more general you. Here's another common issue in this forum: People express a desire for a solution but what they really want is a free solution that they can use,
