Is it possible to execute DTS in SP?

Hi, i have a DTS then generate a CSV file, is it possible to execute the DTS
within a stored procedure?
Moreover, is it possible to dynamic change the current database in SQL Query
Analyzer by execute a Transact-SQL? As i know,
Use DB1 <== this command can change the current database
but is it possible to do the following:
declare @.dbName as char(255)
set @.dbName = 'DB2'
use @.dbName <== currently, this is not valid
Many thanks
MartinIndirectly yes,
You can execute DTS from a console window meaning it's command based. Now
SQL also have xp_cmdshell to execute commands in console.
So if you combine xp_cmdshell and tell it to execute dtsrun you should get
it working.
Have a look at the dtsrun, and xp_cmdshell in books online(sql help)
Hope it's a pointer in the right direction.
"Atenza" wrote:

> Hi, i have a DTS then generate a CSV file, is it possible to execute the D
TS
> within a stored procedure?
> Moreover, is it possible to dynamic change the current database in SQL Que
ry
> Analyzer by execute a Transact-SQL? As i know,
> Use DB1 <== this command can change the current database
> but is it possible to do the following:
> declare @.dbName as char(255)
> set @.dbName = 'DB2'
> use @.dbName <== currently, this is not valid
>
> Many thanks
> Martin
>
>|||> is it possible to execute the DTS
> within a stored procedure?
http://www.sqldts.com/default.aspx?210

> is it possible to dynamic change the current database in SQL Query
> Analyzer by execute a Transact-SQL?
It's easier to do this in DTS with a parameterized SQL task or Dynamic
Properties task. In Transact SQL you would have to use Dynamic SQL (not
recommended) and wrap all your code in an EXEC statatement.
David Portas
SQL Server MVP
--|||Thanks!!
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:A287DCD5-FC0C-48E6-8C6F-0A362941D6AD@.microsoft.com...
> http://www.sqldts.com/default.aspx?210
>
> It's easier to do this in DTS with a parameterized SQL task or Dynamic
> Properties task. In Transact SQL you would have to use Dynamic SQL (not
> recommended) and wrap all your code in an EXEC statatement.
> --
> David Portas
> SQL Server MVP
> --
>|||Thanks!!
"Mal .mullerjannie@.hotmail.com>" <<removethis> wrote in message
news:469E6F3C-B4A6-41B0-A041-1F43E6142197@.microsoft.com...
> Indirectly yes,
> You can execute DTS from a console window meaning it's command based. Now
> SQL also have xp_cmdshell to execute commands in console.
> So if you combine xp_cmdshell and tell it to execute dtsrun you should get
> it working.
> Have a look at the dtsrun, and xp_cmdshell in books online(sql help)
> Hope it's a pointer in the right direction.
> "Atenza" wrote:
>
DTS
Query

Is it possible to create a DTS package using Microsoft Sql server management studio

Is it possible to create a DTS package using microsoft sql server management studio. if yes? How can i do it.

Any help is appreciated.

Regards,

Karen

I'm not sure if it is possible to create a DTS package in SSMS but it is possible to edit and maintain existing DTS packages. The SQL Server 2000 DTS Designer Components are part of the 2005 Feature Pack.

http://www.microsoft.com/downloads/details.aspx?familyid=50b97994-8453-4998-8226-fa42ec403d17&displaylang=en

I would highly recommend using SSIS for any new development.

|||

Thanks a lot for your answer,

Cause i have couple of spread sheets which i need to import to database from a web based application, and i not sure as how can i import the values from the excel spread sheet to the database..

regards,

Karen

sql

Is it possible to call a DTS package in a stored procedure

Hi,

I wanted to know if i can a DTS package using a stored procedure and if yes how should i do it.

Regards,

Karen

You should be able to use XP_cmdshell or DTS run for this, Look at this link

http://www.databasejournal.com/features/mssql/article.php/1459181

Thanks

Raj

Is it even possible to Import a flat file into SQL 2005?

Is it possible to import a flat file in 2005? It was simple in 2000. The DTS wizard pops-up when I dragged a file into the enterprise Manager. Nothing like that happens in 2005. HELP!
I'm hoping someone knows of a wizard
I am by no means an expert in SQL or T-SQL.
Thanks is advance
Does the import/export wizard not do what you want?

-Jamie|||Thanks for the reply. Where can I find the import/export wizard in 2005?
|||http://sqljunkies.com/WebLog/knight_reign/archive/2005/06/10/15785.aspx
K|||

Sweet.

Thanks

Is DTS Support in 2005 Going Away Permanently at Some Point?

I hope this is the right forum to post my question in.

Does anyone know if or when Microsoft will discontinue support for 2000 DTS Legacy packages within SQL Server 2005? We’re in the process of migrating from 2000 to 2005 and have a lot of packages to migrate to Integration Services. We’re having problems migrating the packages because quite a few of them are complex and won’t run after migrating to IS without a major rewrite. Right now it seems that those packages will run just fine under the Legacy folder on the 2005 instance with the data connections pointed to 2005 databases. We’d just like to plan appropriately if we absolutely have to migrate those packages to IS at some point soon. We realize that we'll need to create new packages using IS, though.
So I’d appreciate it if anyone has heard anything to please let me know. I apologize if this has been asked before, but I couldn’t seem to find any posts on it.

Thanks!

Here's some info

http://blogs.conchango.com/jamesrowlandjones/archive/2007/02/18/08.04.08-is-SQL2K-day.aspx

|||Thanks!|||

Hi, your question was good and info noted before was good as well.
I thought I would point you to the official deprecation notice as well.

http://msdn2.microsoft.com/en-us/library/ms403408.aspx

Its intentionally vauge regarding timelines but the idea is to inform you of general direction and changes. You question was if at some point SQL '2005' would not support DTS 2000 pacakges, I think its fair to say no, because 2005 is out the door and in customer hands.

The real question is for how lmany future versions of SQL server support DTS 2000 pacakges. There obviously has to be a limit for practicality reasons. MSFT would like to support everything all the time but thats not realistic as it would continually hinder inovations and changes. So, the reason the depecation noitce is vauge on timeline as the exact version support is stopped may depend on how versions evolve with new features and such. Things will not just get pulled out from under you, but there is a realistic limit. The intent of the notice is similar to the blog, to inform you its needed at some point and formulating a strategy sooner rather than later is a good thing.

hope that helps

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.