Connecting to a ADP Database via SQL2005 SSIS

Post Reply
pchappo
Posts: 2
Joined: Wed Sep 02, 2009 3:48 pm

Connecting to a ADP Database via SQL2005 SSIS

Post by pchappo » Wed Sep 02, 2009 3:53 pm

Hi all,

i was wondering if anyone here has had any success in connecting to an ADP database using SQL 2005 SSIS?

PeterCameron
Posts: 21
Joined: Tue May 12, 2009 8:55 pm

Re: Connecting to a ADP Database via SQL2005 SSIS

Post by PeterCameron » Wed Sep 02, 2009 5:43 pm

Tried and failed.

But you can install the SQL 2000 Integration components and use them instead. Obviously not ideal, but it works.

It really is easier to export the data from KCML into text and then use SSIS I find.

UKMonkeyMAn
Posts: 21
Joined: Tue May 19, 2009 7:54 pm

Re: Connecting to a ADP Database via SQL2005 SSIS

Post by UKMonkeyMAn » Thu Sep 03, 2009 12:03 pm

We went down a very different route on this.

Although the exporting to a text is a tried and tested method for exporting data from a K-Isam database it does take a far amount of time and requires a bit more work than using ODBC. However ODBC comes at a cost per user, unless you know how to reset the licence :)

For a mass data extract we use the SQL utility, this is a command line SQL interface into the K-Isam database. It uses a very limited ODBC 2.0 syntax and certain commands don;t work properly, but once you learn it then you can get what you very quickly.

If your setup for ODBC then the utility will work, there's not much documentation on it, and everything i know is done to trial and error - If you search for SQL utility in the KCML manual you'll get some info on it.

The exe is call sql and can be found in the KCML folder.

The SQL utility is unsupported by ADP, but it requires no licence.

Additionally there is apparently an apach module for the SQL utility, which to some extent can be demonstrated in the kwebserver - Although i have never managed to find this mod file and ADP have never passed it on, despite asking for it serveral times.

One more thing on the SQL utility is updating. The manual does indicate that it is possible with the -m flag, however I managed to get an update to work once, but the result was that the entire table became locked and a reboot was required. Also i've never since managed to get an update to work again, mainly due to a bad memory.

pchappo
Posts: 2
Joined: Wed Sep 02, 2009 3:48 pm

Re: Connecting to a ADP Database via SQL2005 SSIS

Post by pchappo » Thu Sep 03, 2009 2:30 pm

Hi,

thanks for the responses! - the SQL utility sounds like the ideal way to go. Where can i get a copy of this sql utility program, as i have checked my KCML folder and it is not there.
cheers

peter

UKMonkeyMAn
Posts: 21
Joined: Tue May 19, 2009 7:54 pm

Re: Connecting to a ADP Database via SQL2005 SSIS

Post by UKMonkeyMAn » Thu Sep 03, 2009 4:28 pm

Should just be called sql or sql.exe if you are on windows.


Easiest way is to down the Kcml server from http://www.kcml.com, uncompress and then cpio the image file. Then just copy the sql file into your kcml folder.

Operation will depend on your kcml version, if you have the old sources file then make sure that kconf.xml is removed (best to rename to be safe) as this file overrides sources when using ODBC or the sql tool.

Let me know if you have any problems

vvidkish
Posts: 10
Joined: Mon Oct 26, 2009 7:55 pm

Re: Connecting to a ADP Database via SQL2005 SSIS

Post by vvidkish » Mon Aug 16, 2010 8:13 pm

I tried connecting from MS Access to SQL 2005 and was successful. In MS Access, I linked tables through Kerridge ODBC Client Driver and was able to read the information in .NET applications. However, with MS Access limitations in displaying the number of columns, you will have trouble if you dont get the last column in New Vehicle Stock file for eg. To overcome this,currently working on a link using MS Excel (connecting to the same tables through ODBC) and saving a DQY file. But unable to read the DQY files in SQL. If any of you have similar experiences, let me know.

faisalmsc
Posts: 1
Joined: Wed Oct 13, 2010 12:28 pm

Re: Connecting to a ADP Database via SQL2005 SSIS

Post by faisalmsc » Wed Oct 13, 2010 3:30 pm

I connected SSIS and ADP by ODBC successfully. Any one need help contact me faisalmsc@gmail.com

Post Reply

Who is online

Users browsing this forum: No registered users and 1 guest