DTS Scheduling in SQL 2005

Hi all,

I am trying to create scheduled job for DTS. I have created new DTS in SQL 2005 but not able to create job out of it.

I tried creating Credential and then to create proxy with this:
Use master

CREATE CREDENTIAL [MyCredential] WITH IDENTITY = 'yourdomain\myWindowAccount', secret = 'WindowLoginPassword'

Use msdb

Sp_add_proxy @.proxy_name='MyProxy', @.credential_name='MyCredential'

Sp_grant_login_to_proxy @.login_name=' devlogin', @.proxy_name='MyProxy'

Sp_grant_proxy_to_subsystem @.proxy_name='MyProxy', @.subsystem_name='SSIS'

but could not get the job to schedule it. when i try to search for the DTS package(TestRev) it could not find it:

Is there a way that i can just call TSQL statement to run that dam DTS because it is too confusing to create job for DTS.

ThanksI have created new DTS in SQL 2005No, you didn't. DTS is not supported in 2005. Please clarify, and maybe we can answer you question.|||I mean to SSIS packages and would like to schedule it to run in job,|||OK, the job scheduler is a bit confusing, but it is going to be around for a while so you might as well bite the bullet and jump in.

And scheduling a package is not very difficult.

In the job scheduler, name your package and then set the type to "SQL Server Integration Services Package".

Set the package source in the General tab at the bottom of the window. (Not the General tab on the left of the window. I agreed it was a bit confusing...)|||i tried that and it says that the specified package could not be loaded from SQL server Database.

I select SQL server and then name of server and then name but not able to locate it.

thanks|||Where did you save your package? If you saved it to a file, you will need to point to the file name.|||I just started playing with SSBIDS

What a gdman pig|||i saved it on Server.

DTS permissions to create

How to I revoke the ability of non-sa non-dbo users from
creating DTS packages on a SQL 2000 server?
Thx!There's no specific stored procedure in msdb that I can
explicity deny that creates the DTS packages?
>--Original Message--
>You could try revoking or denying write permissions to
msdb. this is where dts package information is stored I
believe.
>In theory the creation of a package should fail if it
cannot be written to msdb.
>"Angelina" wrote:
>> How to I revoke the ability of non-sa non-dbo users
from
>> creating DTS packages on a SQL 2000 server?
>> Thx!
>.
>|||Angelina
To prevent users from adding DTS packages, you can deny EXEC permissions on
sp_add_dtspackage. Deny exec on sp_enum_dtspackages should stop users
browsing packages.
HTH,
Paul Ibison|||Thanks Paul,
Do I Deny the permissions to the guest account.
There are no actual users in the msdb database besides
guest and dbo. Or can I remove the guest account
completely. What's recommended?
>--Original Message--
>Angelina
>To prevent users from adding DTS packages, you can deny
EXEC permissions on
>sp_add_dtspackage. Deny exec on sp_enum_dtspackages
should stop users
>browsing packages.
>HTH,
>Paul Ibison
>
>.
>|||The rights are inherited by virtue of the guest user and the permissions of
the public role. The way I'd do it is remove (revoke and not deny) the exec
rights from the public role and then create your own role with exec rights
to the 2 procedures I mentioned. If a user needs to be able to use them,
then add him explicitly as a user in MSDB and into your role.
HTH,
Paul Ibison

DTS Package problem?

I have set up a couple of DTS local packages to run ActiveX scripts creating XML files and copying them to our webserver (on the same domain).

I can run them OK manually via the 'Execute' package command in the drop down list when I right click on them and also get the 'Package successfully run' message back on running them, but when I schedule them to run overnight I find the SQL Server Agent reports them as 'failed' .

What am I doing wrong?

This looks like a permissions issue...do you have a proxy account setup for the SQL Agent? Look here for info...http://www.support.microsoft.com/kb/890775

Brad Feaker

Ex nihilo, nihil fit

|||As referred if you are scheduling the DTS package then make sure to check privileges for the SQLAgent service account on those process, you can start the SQLAgent using a local account with Admin privleges to complete the task.

dts package help

I'm creating my first dts package. I've specified my sql server connection and my excel file connection. I've added a bulk insert to import my excel data into a work table.

I've specified the row delimiter format as {LF} and Tab for column, but when I try to execute the dts package it fails and I get the following message:

Bulk Insert fails: Column is too long in the data file for row 1, column 3. Make sure the field terminator area specified correctly.
Bulk Insert data conversion error (truncation) for row 1, column 2 (LastName).

I don't understand why I'm getting a truncation error, there should be plenty of space for the insertion? I'm missing something simple I'm sure.

Any help is appreciated.Why are you specifying bulk insert with {LF} and Tab delimiters? Excel does not store data in that format, and that is why your DTS package can't import it using that format.|||This is the first DTS package I'm attempting to create, so I apologize if my questions are novice. When I added the bulk insert it asks to the specify a format for the row and column delimiter. Isn't the row delimiter a carriage return and the column delimiter a tab in an Excel file? What should I be using? I'm using DTS designer in SQL Server 2000.

I appreciate the help. Thank you.|||You probably want to create a connection to your Excel file as an Excel file, rather than as a text file. Excel files do not have row or column delimiters because the file format itself logically provides those delimiters.

-PatP|||I double checked and I did specify an Excel file as the connection, not a text file.

It's when I specify the Bulk Inset task that I have the option of setting a format type or format file. I've tried both options, but still get the same error?|||Assuming you are using SQL 2000, create an "idiot" spreadsheet with two or three columns and then create a DTS job to import it into a table using the Import Export Wizard. Save that job, and look at it to see how the Wizard did the import.

-PatP|||You want to use an Excel connection as the source, a Microsoft OLE DB Provider for SQL Server as the destination, and a Transform Data Task to move your records.

DTS Import/Export Wizard

I have a problem here with my DTS Import/Export Wizard, according to MSDN Library stated below,

[When creating a table using the DTS Import/Export Wizard or DTS Designer, the owner of a table created at the destination is the current user (generally the dbo), regardless of who the owner is at the source. ]

My concern here is if i have a source table of owner A, how can I default the owner of a table created at the destination is A. The problem here is i have more that 20 table of owner A to transfer and i don't want the default to be dbo and i have to go and change each and everyone. Please advice.The most logical solution :

Register the server in EM with the login A. All tables will now be created with owner as A|||Originally posted by Enigma
The most logical solution :

Register the server in EM with the login A. All tables will now be created with owner as A

I had tried this logical solution. And it still give me dbo instead of user name A.

I try to import/export within the same server and i register the server using user A.

Please revert. Thank you very much.