Is data sent via DTS encrypted?

Hi all,

I may have a requirement to send data from a SQL Server at site A to an Oracle server at site B. These sites have no network connection between them, and the current suggestion is to use ftp, but the transfer (or username and password) will not be encrypted.

If I create a DTS package transferring data from site A, will that transfer be encrypted?

If not, is there an option with SQL Server DTS to ensure that the data is sent in an encrypted form?

Thanks in advance.No, data isn't automagically encrypted when using DTS. You can use a secure (VPN) link that will encrypt the data. You can put the data into a file and encrypt the file (if you do, compress it first to improve security). You can even create an encryption/decryption DTS task (using VC) and make that part of your DTS toolbox.

-PatP|||Thanks Pat.

Not sure that secure VPN is an option to be honest I'm afraid.

The current favourite is actually to ftp an encrypted .csv, I just wondered if DTS could handle the encryption and make my life easier :-)

BTW, what is VC ( or am I just having a blonde moment? )|||VC is Visual C, part of Microsoft's Visual Studio.

The only way I know to add new tasks to DTS is to write them in Visual C. This allows all kinds of interesting things to happen!

Blonde moments are expected, at least now and then. Unfortunately, I don't have blonde hair (heck, I don't have much hair), so I have to attribute those moments to other things... I'm not quite to the point where I can comfortably attribute them to senior moments yet, although from an IT perspective I'm already somewhat older than dirt!

Yes, go ahead and create the CSV file, then use an Execute Process (effectively a command line) Task to handle the encryption.

-PatP|||Thanks for all the advice Pat. I'll investigate that process.

I too am quite far from blonde, but hey, we all have the moments :-)|||Unless I'm having a Blonde moment I'm almost certain DTS tasks can be written in C# and VBScript (the tools I've used) just to name a few.|||I only know how to create a new task that you can add to the DTS designer Task Panel using Visual C. You can certainly add steps to a DTS package using C# or VBScript, but that is a completely different thing in my opinion.

-PatP|||Why not just create a stored procedure?|||There are more ways to skin that cat than there are cats, but that is no reason to stop looking for new ways!

A script or stored procedue would be easy to implement. A custom task would be more efficient to use, since you could for instance just create a step that generated your data, then feed it to the task just like you do with the FTP task.

I did something like this to move data to our mainframe using the 7.0 EM a while ago, and the task was wildly popular with our power users. It just saved them a lot of monkeying around for a task that they did rather frequently.

-PatP|||I only know how to create a new task that you can add to the DTS designer Task Panel using Visual C. You can certainly add steps to a DTS package using C# or VBScript, but that is a completely different thing in my opinion.

-PatP

Ah.. yes. Your looking to make a new task for the DTS toolbar. I missed that. C# could probably handle it but I have doubts about scripting languages.