asked on

DTS Import from Access - need to avoid certain duplicates

Hi,

I am new to SQL and not much of a programmer and this is a more complex scenario that I am trying to solve.

I am importing information from an Access Database. Access info changes daily and must be imported into my SQL table.

There are three fields:

GROUPID, CODEID, CODEDESC

Groupid and codeid are primary fields. However, Groupid can be duplicated as long as the Codeid is different. This is also controlled by the Dex Row ID.

I do not want to ever delete information from the SQL table, I only want to update any new information from Access into SQL. Rather than dealing with temp tables, i would like to execute a script when running the scheduled DTS.

Can anyone please help me with the script? I have no idea where to start.....

Access Database is called Dimension.
Access Table is called AnalysisCodeMaster.
Access fields are called, AnalysisGroup, AnalysisCode, Description.

SQL database is called FFIM
SQL table is called DTA00200
SQL fields are called, GROUPID, CODEID, CODEDESC.

I was able to execute a query using a temp table that does appear to work.

"insert dta00200(groupid,codeid,codedesc)
SELECT AnalysisGroup, AnalysisCode, Description

from AA where not exists(Select 1 from dta00200 where dta00200.codeid = aa.analysiscode)"

I would like something like the above to run with the DTS.
Thanks

namasi_navaretnam

There are may be many ways to do it. Here is one way.

1) Create a DTS to Tranfer data as is from access to MS SQL SERVER.
2) Upon completion of Step 1, create a DTS to insert rows into actual MS SQL Server table from temp table if the rows do not exist
3) Upon completion of Step 2, create a DTS to update actaul MS SQL table from temp table.

Alternative to Step 2)
Create a stored proc to accomplish step2 and step3 and shedule that stored proc to run.

HTH,

Namasi Navaretnam

wisk

ASKER

Thanks Namasi.

I would like to avoid the temp table if possible. do you know how I can include these steps at the time of running the original DTS?

thanks again

ASKER CERTIFIED SOLUTION

nmcdermaid

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

wisk

ASKER

both suggestions above appear to be good solutions.

nmcdermaid

I have tried connecting directly to the Access database but I received the following:

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: Could not find installable ISAM.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: ].

I do not have a login/pw on the file so I changed it too:

from OPENROWSET( 'Microsoft.Jet.OLEDB.4.0','\\XXXXXbase\XXXdatabase\firstf.mdb', AnalysisCodeMaster)

nmcdermaid

You will need to get some help on specifics of connection strings.

Look up the OPENROWSET function on MSDN.

I think there is a sight called www.connectionstrings.com which will help you build you connection string.

Also, that installable ISAM error often means that you need to reinstall MDAC.

Good luck!