DTS internals

Question

DTS internals

pveilleux

Ten Centuries

Points: 1363
More actions
May 18, 2007 at 7:43 am

#68414

good morning,
I was wondering if anyone knows or has a detailed document about the internals of what happens when a DTS package is executed. I'm trying to troubleshoot a problem and it doesn't appear as though the package is getting very far in its execution. Knowing the steps taken behind the scenes will help me choose a starting point for my troubleshooting.
thanks.....
Peter

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Adrian Nichols-360275 SSCertifiable Points: 7694 More actions · Answer 1

I don't have a document on DTS Internals, however, if you can post the task and error details we might be able to help you with your troubleshooting.

Ade

A Freudian Slip is when you say one thing and mean your mother.
For detail-enriched answers, ask detail-enriched questions...[/url]

pveilleux Ten Centuries Points: 1363 More actions · Answer 2

that's the thing is there was no error message, no data transfer, nothing. We ended up solving the problem - kind of - so I won't get into a lengthy explanation, but here's what happened:

a DTS package existed that would run a query thru an ODBC connection and output the results to a CSV file. There were several of these, one transformation dependent on the one before, but the problem existed with a single test transform, as well. We had changed the location of the output file, and it wasn't working anymore, even though the permissions on the share were Full Control for Everyone. When I would execute the package, it would sit there and look like it was running, but there was no connection into the database and there was no output file created. All of this was taking place on my local machine. If I would RDP into the server that houses SQL Server and run the package from there, it worked perfectly.

I'm not sure why this happened. In both cases I was logged into the machine and SQL Server with my network login, so it's not like I had different credentials. One thing I didn't ask is if a share could be restricted only to allow access from certain machines.

If anyone has any insight, that would be great, but we were able to get around this issue.

thanks.....

Steve Jones - SSC Editor SSC Guru Points: 735922 More actions · Answer 3

I'm not sure of where you could get the DTS internals, but one thing that might help is to save as a "VB File" and then execute it in a VB 6 environment. That way you could step through things.

mrpolecat SSCertifiable Points: 7014 More actions · Answer 4

Most of the time it is because the ODBC driver or connection is not on the PC you are running it from aka your pc. It is on the server so it runs fine from there. The funny thing about DTS is if you run it from enterprise manager it uses local resources so you have to make sure you have all of the necessary drivers and programs installed on the PC you are running it from. The same thing goes for file paths. C:\filename.txt is on your pc when you run it from there and on the server if you RDP in and run it on the server. Always best to use UNC for file paths.

pveilleux Ten Centuries Points: 1363 More actions · Answer 5

I have the correct DSN name. That's been my pet peeve with our developers so I instituted a naming convention for all production DSN names. The driver itself is a good point, but I haven't had any issues with other DTS packages, which is why this was concerning.

as for the paths, everything is using UNC.