DTS transferring data via the client PC

[SkidsMcCoy]

Hi,

We've just introduced an implementation of SQL Server to use as an adjunct to our data warehouse - the idea is we set up data marts as required, when we need a more flexible 'sandpit' environment to work with large volumes of data.

So far, this is working fine except for one thing - when creating connections in DTS, Enterprise manager seems only to see ODBC connections on the local client PC. This means that any data transfer between our warehouse and SQL Server travels via the client PC, rather than direct. This is obviously not ideal for a number of reasons, not the least of which is the very slow transfer speeds.

I assume this is a configuration option - can anyone tell me how to fix it?

Many thanks in advance

 

[LogicalmanUS]

SkidsMcCoy,

I agree with your observations, and when you kick off a DTS package from a client PC, then it run via that client. I tend to use the client PC (mine) to create the DTS package and then schedule it via SQL Server Agent, which means it is then run from the server itself.

Logicalman

 

[SkidsMcCoy]

That's really helpful - thanks. For packages only involving SQL Server databases, that works fine now.

I still have a problem with my data warehouse, though. When I create the DTS package, the connection options are based on my client PC's ODBC settings - and when I run the package via SQL Server Agent, it can't find the connection. The server has the correct drivers installed, though the DSN has a different name.

Do I have to create a File DSN for this? Or make sure that the naming of ODBC connections is consistent? Or something else? All ideas welcome.

 

[LogicalmanUS]

SkidsMcCoy,

Hmm, nice one, I haven't come across that problem, yet (give it time!!).

From your original description of setting up the connections in the DTS package (I take it you are setting up connection objects, and then creating a Transform Data Task), the fact you cannot see the other server would indicate that it is not available, ergo, cannot be connected to etc.

Can you see the server and SQL Instance from the server you are currently on? If so, you might want to type the full path to the target server on the first screen of the connection dialogue box as the destination, and see if it can connect that way, other than using the 'Other (ODBC Database)' selection.

Logicalman