DTS Ongoing problem Type Mismatch - HELP !

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

DTS Ongoing problem Type Mismatch - HELP !

Author

Topic

DaveRolph
Starting Member

15 Posts

Posted - 2006-02-19 : 06:55:03

I hope someone can help, I have spent hours trying to find the answer to this one, I have a standard pipe delimeted text file which I want to import using DTS and Active X

The said file contains a client id which is also stored in an existing SQL table along with address columns, all I want to do is lookup the address details.

I have tried all sorts including trying to trim and left the lookup key, the Client_ID is a VarChar(10)

dim tAddressArray
dim tPatientNoFull

Function Main()
tPatientNoFull = left("SOF" & DTSSource("CLIENT_ID"),1)
tAddressArray = DTSLookups("Address").Execute(tPatientNoFull)

DTSDestination("Address_1") = tAddressArray(0)
DTSDestination("Address_2") = tAddressArray(1)
DTSDestination("Address_3") = tAddressArray(2)
End Function

The Lookup is as follows

SELECT Address_1, Address_2, Address_3
FROM P_ADDRESS_DATA
WHERE (Patient_No_Full = '?')

I have also tried it wothout the ' marks around the ?, I hope someone can help I am tearing my hair out now, thanks

Dave

BTW I am Running Windows XP, SQL 2000 SP4

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2006-02-19 : 07:59:27

I'd suggest using BCP to import the file into a staging table then do the lookup in T-SQL by joining the staging table to the existing table on CLIENT_ID.

Mark

DaveRolph
Starting Member

15 Posts

Posted - 2006-02-19 : 08:59:47

Thanks for the reply Mark, this is the way I normally do it but because I have many simple but large tables I wanted to do it in one hit.

I would really like to persue this method as from what I have read it is certainly designed to do this.

I am grateful of your suggestion to work this another way but can you tell me that what I am doing is wronng ?

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2006-02-20 : 06:14:00

Hi Dave

quote:
because I have many simple but large tables I wanted to do it in one hit

Not quite sure I follow you, but if you're dealing with large data volumes, it will most likely be significantly quicker to use BCP and T-SQL in place of DTS Datapump with a lookup. I'm afraid I can't help with the lookup as I haven't done it that way for a very long time, and am using SQL 2005 so don't have DTS installed any longer to play around with.

Mark

DaveRolph
Starting Member

15 Posts

Posted - 2006-02-22 : 00:21:33

Thanks for the additional response, the table has many things I need to manipulate, because of this is looked easier to do it in Active-X.

In the end I created a seperate tSQL and stuck it at the end of the DTS, I am sure this is the same as the 2nd part of your suggestion.

My import works great now and it is very fast but I would still be interested in looking at better ways of importing data.

I have used BCP a couple of times before but it did seem more complicated than DTS and AFAIK did not give me the ability of manipulating the data, nowadays I write the DTS packages and then run them from the server, some of the imports go stright in, others go into holding tables and then TSQL Scripts do the mapping.

I would really appreciate some feedback on this from as many people as possible, I will be happy to re study the BCP Methods if this would help.

What I want is the best - EASIEST AND FASTEST (Yeah I know these don't often go together!) method of importing and manipulating data.

Thanks so far

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2006-02-22 : 03:33:12

Hi Dave, you're quite right, BCP is largely a method of getting data into your db very quickly (without transformation). Using this in conjunction with T-SQL for handling transformations will, in pretty much any given situation, give you the best performance. BCP is probably not as user-friendly as the DTS Datapump task, but once you've done a few, there's nothing much to it, and the use of format files aids maintainability without having to fight against the gui.

Mark

DaveRolph
Starting Member

15 Posts

Posted - 2006-02-22 : 14:24:56

Thanks for this Mark

Can you tell me if there is any decent guides ti BCP ?, one question can it make destination tables too or would it be worth using DTS to create the table first then revert to BCP for the increased performance ?

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2006-02-23 : 03:57:17

Hi Dave, With BCP, you need to create the tables first. If you're suggesting using a DTS Datapump task to create the tables, I'd advise against this. If you're using DTS to control flow, your best bet is to precede the BCP task with an Execute SQL task that runs the DDL to create the destination table. A slightly more esoteric approach that I've used before when needing to simply get a file into SQL server as is, is to define the files in XML and then transform this into both the DDL and the BCP format file via XSLT. This means that any changes only need to be made in one place. (You can also easily provide ready-made documentation of your files by transforming into text or html or some such). This clearly depends on how comfortable you are with XML/XSLT!

DaveRolph
Starting Member

15 Posts

Posted - 2006-02-23 : 04:36:24

ugh !, this is getting heavy !, so do you know of any decent on-line guides ?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources