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: [email protected] [mailto:[email protected]] 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 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, 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. > > Tony Gravagno > Nebula Research and Development > TG@ remove.pleaseNebula-RnD.com > remove.pleaseNebula-RnD.com/blog > Visit PickWiki.com! Contribute! > http://Twitter.com/TonyGravagno > > > _______________________________________________ > U2-Users mailing list > [email protected] > http://listserver.u2ug.org/mailman/listinfo/u2-users > > _______________________________________________ U2-Users mailing list [email protected] http://listserver.u2ug.org/mailman/listinfo/u2-users Disclaimer: This email may contain confidential and/or privileged information. It is intended only for the person or persons to whom it is addressed. Any unauthorized review, use, or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email or telephone and destroy all copies of the original message. Please consider the environment before printing this email. _______________________________________________ U2-Users mailing list [email protected] http://listserver.u2ug.org/mailman/listinfo/u2-users
