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.
| Author |
Topic |
|
dajv
Starting Member
3 Posts |
 Posted - 2005-03-03 : 20:09:23
|
| G'day,I ahve created a DTS package using Enterprise Manager and I am having a problem with creating lookups in DTS. This never used to happen - it worked as expected when I was working on this DTS package a few months ago, and now that I've gone back to work on it again it is exhibiting this behaviour...Whenever I create a lookup (this is with an INSERT) with SQL like the following:INSERT INTO MyTable (FileType, FileID, ContactID, Reference, Method)VALUES (?, ?, ?, ?, 1)... Enterprise Manager insists on adding the prefix to the table name, as in:INSERT INTO [DatabaseName].dbo.MyTable (FileType, FileID, ContactID, Reference, Method)VALUES (?, ?, ?, ?, 1)This happens after clicking OK to save the changes to the lookup. When I go back into the SQL using the [...] button, I can see it was changed.This is extremely frustrating, because I use the DTS package in a utility I have written in VB to convert databases with arbitrary names and so I need the DTS package to not reference any database name otherwise it will not work. (Already, I have to programmatically remove the references to the source and destination database names in the DestinationObjectName and SourceObjectName properties of each task in the DTS package that Enterprise Manager insists on adding - but this is another story.)Does anyone know why Enterprise Manager might be forcing me to have the [database name].dbo in front of the table names in my INSERT SQL? It never used to do this on my machine, so I can't understand why it has decided to start doing it now. Is there a way to stop this from happening?Any advice is much appreciated.Thanks |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-03-03 : 20:13:04
|
| Think you can get rid of it by using disconnected edit.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
dajv
Starting Member
3 Posts |
Posted - 2005-03-06 : 17:59:19
|
| Thanks alot nr.How do I use disconnected edit? I've looked at Package Properties->Disconnected Edit but it just lets me change the properties of the packageCheers,Dave |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-03-06 : 18:28:47
|
| In idsconnected edit you should be able to select the task and edit the properties without the designer changing things on save.Never tried it for a lookup though.I usually put things in global variables and apply them in a dynamic properties or activex task.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
dajv
Starting Member
3 Posts |
Posted - 2005-03-07 : 19:12:54
|
| Ahh, I see now - I didn't realise you could change all settings related to all the DTS Tasks as well as the package itself. Thanks for that. Seems like a hack, but it works. As a bonus, now I don't have to have my tasks named DTSTask_DTSDataPumpTask_1 etc! |
|
|
|
|
|
|
|
|
DTS lookups - table path being prefixed