DTS Import: 2012

Thursday, March 29, 2012

DTS Simple Question

I am just starting to learn DTS.
If I set up in a DTS package a series of 5 independant processes, each of
which Deletes rows out of it's respective table and then loads their
respective flat file into those tables.... Will all of these run
concurrently? I have them all tied to the same connection object, but that
should not prevent them from running concurrently...wil it?Jim,
Yes it will. if they use the same connection, it will prevent them from
running concurrently.
You can notice it in DTS Designer, when moving huge amount of data between
source and target using the same connection. The transformation steps
(datapumps) will run in sequence no matter if they were setup to run
concurrently.
AMB
"Jim Heavey" wrote:
> I am just starting to learn DTS.
> If I set up in a DTS package a series of 5 independant processes, each of
> which Deletes rows out of it's respective table and then loads their
> respective flat file into those tables.... Will all of these run
> concurrently? I have them all tied to the same connection object, but that
> should not prevent them from running concurrently...wil it?
>|||http://www.codeproject.com/useritems/DTS__VBNET_.asp

DTS Simple Question

I am just starting to learn DTS.
If I set up in a DTS package a series of 5 independant processes, each of
which Deletes rows out of it's respective table and then loads their
respective flat file into those tables.... Will all of these run
concurrently? I have them all tied to the same connection object, but that
should not prevent them from running concurrently...wil it?
Jim,
Yes it will. if they use the same connection, it will prevent them from
running concurrently.
You can notice it in DTS Designer, when moving huge amount of data between
source and target using the same connection. The transformation steps
(datapumps) will run in sequence no matter if they were setup to run
concurrently.
AMB
"Jim Heavey" wrote:

> I am just starting to learn DTS.
> If I set up in a DTS package a series of 5 independant processes, each of
> which Deletes rows out of it's respective table and then loads their
> respective flat file into those tables.... Will all of these run
> concurrently? I have them all tied to the same connection object, but that
> should not prevent them from running concurrently...wil it?
>

DTS shedule error

Hey,
I have a DTS and I can execute him with no error.
This DTS is sheduled every day. But when the DTS is started up from the
shedule, he always fails.
He gives follwing error and I don't understand what is wrong.
Executed as user: OGEWS140\SYSTEM. DTSRun: Loading... DTSRun:
Executing... DTSRun OnStart: DTSStep_DTSActiveScriptTask_1 DTSRun
OnError: DTSStep_DTSActiveScriptTask_1, Error = -2147220421 (8004043B)
Error string: The task reported failure on execution. Error source:
Microsoft Data Transformation Services (DTS) Package Help file:
sqldts80.hlp Help context: 1100 Error Detail Records: Error:
-2147220421 (8004043B); Provider Error: 0 (0) Error string: The task
reported failure on execution. Error source: Microsoft Data
Transformation Services (DTS) Package Help file: sqldts80.hlp Help
context: 1100 DTSRun OnFinish: DTSStep_DTSActiveScriptTask_1 DTSRun
OnStart: DTSStep_DTSExecuteSQLTask_2 DTSRun OnFinish:
DTSStep_DTSExecuteSQLTask_2 DTSRun: Package execution complete. Process
Exit Code 1. The step failed.
Tkx,
JacWhen you execute the package manually it is runs with your security
privileges. When you schedule it the user is OGEWS140\SYSTEM. I am guessing
that your system does not have privileges to do what you want.
"Jac" wrote:
> Hey,
> I have a DTS and I can execute him with no error.
> This DTS is sheduled every day. But when the DTS is started up from the
> shedule, he always fails.
> He gives follwing error and I don't understand what is wrong.
> Executed as user: OGEWS140\SYSTEM. DTSRun: Loading... DTSRun:
> Executing... DTSRun OnStart: DTSStep_DTSActiveScriptTask_1 DTSRun
> OnError: DTSStep_DTSActiveScriptTask_1, Error = -2147220421 (8004043B)
> Error string: The task reported failure on execution. Error source:
> Microsoft Data Transformation Services (DTS) Package Help file:
> sqldts80.hlp Help context: 1100 Error Detail Records: Error:
> -2147220421 (8004043B); Provider Error: 0 (0) Error string: The task
> reported failure on execution. Error source: Microsoft Data
> Transformation Services (DTS) Package Help file: sqldts80.hlp Help
> context: 1100 DTSRun OnFinish: DTSStep_DTSActiveScriptTask_1 DTSRun
> OnStart: DTSStep_DTSExecuteSQLTask_2 DTSRun OnFinish:
> DTSStep_DTSExecuteSQLTask_2 DTSRun: Package execution complete. Process
> Exit Code 1. The step failed.
> Tkx,
> Jac|||Hey,
I am not very well in security. So, how can I give the user OGEWS140\SYSTEM
all the security rights so he can execute a sheduled DTS
tkx,
Jac
"cathedr@.wa.state.gov" wrote:
> When you execute the package manually it is runs with your security
> privileges. When you schedule it the user is OGEWS140\SYSTEM. I am guessing
> that your system does not have privileges to do what you want.
> "Jac" wrote:
> > Hey,
> >
> > I have a DTS and I can execute him with no error.
> > This DTS is sheduled every day. But when the DTS is started up from the
> > shedule, he always fails.
> > He gives follwing error and I don't understand what is wrong.
> >
> > Executed as user: OGEWS140\SYSTEM. DTSRun: Loading... DTSRun:
> > Executing... DTSRun OnStart: DTSStep_DTSActiveScriptTask_1 DTSRun
> > OnError: DTSStep_DTSActiveScriptTask_1, Error = -2147220421 (8004043B)
> > Error string: The task reported failure on execution. Error source:
> > Microsoft Data Transformation Services (DTS) Package Help file:
> > sqldts80.hlp Help context: 1100 Error Detail Records: Error:
> > -2147220421 (8004043B); Provider Error: 0 (0) Error string: The task
> > reported failure on execution. Error source: Microsoft Data
> > Transformation Services (DTS) Package Help file: sqldts80.hlp Help
> > context: 1100 DTSRun OnFinish: DTSStep_DTSActiveScriptTask_1 DTSRun
> > OnStart: DTSStep_DTSExecuteSQLTask_2 DTSRun OnFinish:
> > DTSStep_DTSExecuteSQLTask_2 DTSRun: Package execution complete. Process
> > Exit Code 1. The step failed.
> >
> > Tkx,
> > Jac|||In Enterprise Manager, expand the server you are working on. Then expand the
Management Folder. Right-click the SQL Server Agent and choose Properties
from the pop-up menu. My guess is that you will find the Service startup
account set to System account. When you schedule a DTS package, the SQL
Server Agent is what starts the package. Since the Agent is starting the
package, that is the security context that the package runs in. If you need
the package to run in a different security context, you could specify that
the Agent use a different account. You could create a local account on the
server specifically for this purpose. If any of your jobs or packages will
need to perform administrator tasks, you should give the account appropriate
privileges on both the server and SQL server. The account will also need
privileges to any databases, procedures, etc. If your jobs or packages need
to communicate with other servers, you may need to use a domain account
rather than a local account. Another reason you may want to use a domain
account is in order to use SQL Mail. The startup account that I use for
Agent is a domain account that has an email account on Exchange.
Hope that helps...
Dan
"jac" wrote:
> Hey,
> I am not very well in security. So, how can I give the user OGEWS140\SYSTEM
> all the security rights so he can execute a sheduled DTS
> tkx,
> Jac
> "cathedr@.wa.state.gov" wrote:
> > When you execute the package manually it is runs with your security
> > privileges. When you schedule it the user is OGEWS140\SYSTEM. I am guessing
> > that your system does not have privileges to do what you want.
> >
> > "Jac" wrote:
> >
> > > Hey,
> > >
> > > I have a DTS and I can execute him with no error.
> > > This DTS is sheduled every day. But when the DTS is started up from the
> > > shedule, he always fails.
> > > He gives follwing error and I don't understand what is wrong.
> > >
> > > Executed as user: OGEWS140\SYSTEM. DTSRun: Loading... DTSRun:
> > > Executing... DTSRun OnStart: DTSStep_DTSActiveScriptTask_1 DTSRun
> > > OnError: DTSStep_DTSActiveScriptTask_1, Error = -2147220421 (8004043B)
> > > Error string: The task reported failure on execution. Error source:
> > > Microsoft Data Transformation Services (DTS) Package Help file:
> > > sqldts80.hlp Help context: 1100 Error Detail Records: Error:
> > > -2147220421 (8004043B); Provider Error: 0 (0) Error string: The task
> > > reported failure on execution. Error source: Microsoft Data
> > > Transformation Services (DTS) Package Help file: sqldts80.hlp Help
> > > context: 1100 DTSRun OnFinish: DTSStep_DTSActiveScriptTask_1 DTSRun
> > > OnStart: DTSStep_DTSExecuteSQLTask_2 DTSRun OnFinish:
> > > DTSStep_DTSExecuteSQLTask_2 DTSRun: Package execution complete. Process
> > > Exit Code 1. The step failed.
> > >
> > > Tkx,
> > > Jac|||Thanks a lot. That was a great help to me.
"cathedr@.wa.state.gov" wrote:
> In Enterprise Manager, expand the server you are working on. Then expand the
> Management Folder. Right-click the SQL Server Agent and choose Properties
> from the pop-up menu. My guess is that you will find the Service startup
> account set to System account. When you schedule a DTS package, the SQL
> Server Agent is what starts the package. Since the Agent is starting the
> package, that is the security context that the package runs in. If you need
> the package to run in a different security context, you could specify that
> the Agent use a different account. You could create a local account on the
> server specifically for this purpose. If any of your jobs or packages will
> need to perform administrator tasks, you should give the account appropriate
> privileges on both the server and SQL server. The account will also need
> privileges to any databases, procedures, etc. If your jobs or packages need
> to communicate with other servers, you may need to use a domain account
> rather than a local account. Another reason you may want to use a domain
> account is in order to use SQL Mail. The startup account that I use for
> Agent is a domain account that has an email account on Exchange.
> Hope that helps...
> Dan
> "jac" wrote:
> > Hey,
> >
> > I am not very well in security. So, how can I give the user OGEWS140\SYSTEM
> > all the security rights so he can execute a sheduled DTS
> >
> > tkx,
> > Jac
> >
> > "cathedr@.wa.state.gov" wrote:
> >
> > > When you execute the package manually it is runs with your security
> > > privileges. When you schedule it the user is OGEWS140\SYSTEM. I am guessing
> > > that your system does not have privileges to do what you want.
> > >
> > > "Jac" wrote:
> > >
> > > > Hey,
> > > >
> > > > I have a DTS and I can execute him with no error.
> > > > This DTS is sheduled every day. But when the DTS is started up from the
> > > > shedule, he always fails.
> > > > He gives follwing error and I don't understand what is wrong.
> > > >
> > > > Executed as user: OGEWS140\SYSTEM. DTSRun: Loading... DTSRun:
> > > > Executing... DTSRun OnStart: DTSStep_DTSActiveScriptTask_1 DTSRun
> > > > OnError: DTSStep_DTSActiveScriptTask_1, Error = -2147220421 (8004043B)
> > > > Error string: The task reported failure on execution. Error source:
> > > > Microsoft Data Transformation Services (DTS) Package Help file:
> > > > sqldts80.hlp Help context: 1100 Error Detail Records: Error:
> > > > -2147220421 (8004043B); Provider Error: 0 (0) Error string: The task
> > > > reported failure on execution. Error source: Microsoft Data
> > > > Transformation Services (DTS) Package Help file: sqldts80.hlp Help
> > > > context: 1100 DTSRun OnFinish: DTSStep_DTSActiveScriptTask_1 DTSRun
> > > > OnStart: DTSStep_DTSExecuteSQLTask_2 DTSRun OnFinish:
> > > > DTSStep_DTSExecuteSQLTask_2 DTSRun: Package execution complete. Process
> > > > Exit Code 1. The step failed.
> > > >
> > > > Tkx,
> > > > Jac

DTS shedule error

DTS services

HI to all,

well I am facing a very critical problem, Let me explain the situattion first.

We are managing two applications , One is using Microsoft Sql Server (Desktop application )and the other one is using the MYsql(Web application ).

Both applications are for the same business, having exactly the same database. Now we have to keep two different Data servers as we don't want to Re-code.

Is there any way that our both servers are sychonised. I mean Can we have a solution that the Copy of the data entered in the Mysql can be tranferred to the SQLserver and vice versa.

I guess the SQL server DTS (data transforming services) can help but i am not sure will it be possible.

THx and regards.In Books Online, you should look at Synchronization. That said, I am not sure how (or if) two way synchronization is possible with heterogenous databases (db's from 2 different vendors).

DTS Server

Hi,
Can someone tell me how to start DTS Server in SQL 2005 beta 2?
In "Server Configuration Manager", I see DTS Server's State is "Stopped", and Start Mode is "Other (Boot, System, Disabled or Unknown). Attempts to start (by doing: properties -> start) have failed.
In the tool "SQL Server 2005 Surface Area Configuration", I found the "start, stop, pause, resume" buttons grayed out.
david
SQL Server Agent depenedent services which means you have to install SQL Server Agent with a service account. Hope this helps.|||

Thanks for the fast response.
But, could you give me a little walkthrough for doing what you've suggested, namely:
" ... install SQL Server Agent with a service account ... " ?
With beta 2, I can see the tool MS SQL Server Management Studio, and I can see "SQL Server Agent", and in the "Logins" section a number of entries are listed.
But exactly what need to be done to get DTS started? (Pl outline the exact steps - I do not have prior exprience with SQL server admin, and the SQL2005 beta 2 documentation on system admin is mostly "blank" presently.)
Thanks.
david

|||

You need Admin experience to know how to install the SQL Server Agent with a service account most COM C++ programmers need it so they know. That said the quick and dirty solution is to reinstall SQL Server because after six years Microsoft installer now ask you to install SQL Server Agent with a service account. The other option try the links below for the configuration steps. Hope this helps.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_xp_aa-sz_4jxo.asp

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_xp_aa-sz_8sdm.asp

|||

Thanks for the detail info you've provided. I am going see what I can do with it. The codes in the links have syntax that resembles what I have seen in some stored procedures (EXEC, ... etc.) Since these are all new to me, I will ask you these 2 elementary questions:
(i) Using just SQL server (i.e. without using ASP.NET), what are some of the ways I may enter and run the sample codes in the links you've provided?
(ii) Assuming DTS is set up successfully and is running, where may I initiate the DTS function in SQL Server 2005?
I know the answer for SQL 2000, and that is simply by going to: Tool -> DTS, or alternately go to Tools -> Import/Export.
For SQL SERVER 2005, should I expect to be able to do the same to start up DTS?
The reason I am asking this is because I have somehow got DTS Server running, but I don't see a way to begin using DTS (I don't see DTS under the "Tools" tab)
Thanks again, for the previous rensponse.
David

|||Additional question:
Are DTS funcationality available for use from ASP.NET 2.0 Beta 2?|||The new the DTS is a full ETL(extraction transforamtion and loading) tool so you have to go to the first link I gave you to know more about it. Another option is the SQL Server BOL(books online) it is the best in the industry. Hope this helps

DTS Select using record position

Hi there, it has been a while since i have posted. I am in a
situation where I am stumped. I am learning to build a dts package
where I am connecting to a table in an AS400. This database is being
maintained by an outsourced company and therefore I can't change the
table structure or even ask them to. Anyway, this table currently has
about 104,000 records. I am building a package to check it and pull
out the most recent records and put them where they go in my SQL
Server 2000 tables. The only way I can think of to get the most
recent records is to use a global variable in the package to remember
the record count and then get those records from that record position
on. Problem is, I have no idea how I would go about selecting records
from a record position. Does anybody have any ideas or should I be
using a different approach? There are no time stamps to work from. I
was told that the AS400 records, including updates, are appended to
the table, which is why I thought this approach made sense. I would
truly appreciate any help.phantomtoe@.yahoo.com (Rowan) wrote in message news:<4bbf8d70.0403031553.6e5f7de7@.posting.google.com>...
> Hi there, it has been a while since i have posted. I am in a
> situation where I am stumped. I am learning to build a dts package
> where I am connecting to a table in an AS400. This database is being
> maintained by an outsourced company and therefore I can't change the
> table structure or even ask them to. Anyway, this table currently has
> about 104,000 records. I am building a package to check it and pull
> out the most recent records and put them where they go in my SQL
> Server 2000 tables. The only way I can think of to get the most
> recent records is to use a global variable in the package to remember
> the record count and then get those records from that record position
> on. Problem is, I have no idea how I would go about selecting records
> from a record position. Does anybody have any ideas or should I be
> using a different approach? There are no time stamps to work from. I
> was told that the AS400 records, including updates, are appended to
> the table, which is why I thought this approach made sense. I would
> truly appreciate any help.

How would you know where the recorded position is? Does the AS/400
have a row number field, or something similar? And what happens if
rows are deleted in the source table?

Assuming that the AS/400 table has a primary key, then you might be
able to create a linked server from your MSSQL server, and then use a
query like this to insert all the rows which don't exist:

insert into dbo.MyTable (col1, col2, ...)
select col1, col2, ...
from AS400...Table t
where not exists (select * from dbo.MyTable mt
where mt.PrimaryKey = t.PrimaryKey)

Updates would be more difficult, as you'd need to have some way of
identifying the rows which have been modified. You might want to
consider looking at heterogeneous replication for this, so that
changes on the AS/400 are automatically replicated to the SQL Server,
although you would probably need a third-party tool for this.

Simon

DTS Scripts (SQL Server 2000)

Hello,

I would like to know the DTS Scripts for converting Access Databases to SQL Server 2000. The Access Databases are located on multiple servers. The tables should be created the first time. Then the data should be copied from Access Databases located on multiple servers. No duplicate rows should be added to SQL Server tables. The data should be copied from Access Databases every 15 minutes. Please show me a example (code in DTS Scripts) which does this task. Smile

Thanks,

Vivek Gupta

You can use import export wizard in SQL enterprise manager and it will create DTS package for your, next you just have to modify it to accept input access file name as parameter, and maybe table name in it? But if your table structure is different in each database it will be hard to create single DTS package because DTS package will store table structure definition as fixed. Maybe instead of this you can write simple .Net application which will do this for your for any Access database location and table name.

Thanks

DTS Scripts

We are upgrading our current SQL2000 Server to new hardware. Is there a way
to transfer DTS packages from one server to another. Thanks.
Open the package, in the package menu, select save as...you can save it to
file or directly to another server
"Tom Reis" <reistom@.cdnet.cod.edu> wrote in message
news:uq4MB3IuFHA.3568@.TK2MSFTNGP10.phx.gbl...
> We are upgrading our current SQL2000 Server to new hardware. Is there a
> way
> to transfer DTS packages from one server to another. Thanks.
>
|||did you hear "Tom Reis" <reistom@.cdnet.cod.edu> say in
news:uq4MB3IuFHA.3568@.TK2MSFTNGP10.phx.gbl:

> We are upgrading our current SQL2000 Server to new hardware. Is there
> a way to transfer DTS packages from one server to another. Thanks.
>
http://www.sqldts.com/default.aspx?204
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogs
|||HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/default...b;en-us;314546
AMB
"Tom Reis" wrote:

> We are upgrading our current SQL2000 Server to new hardware. Is there a way
> to transfer DTS packages from one server to another. Thanks.
>
>
|||Hi,
Just open the DTS Package and save the package in text file it will be
saved as ii.DTS.
Copy that to another server and open the package and save in local
machine.
hope this help u
from
Doller
sql sql

DTS Scripts

We are upgrading our current SQL2000 Server to new hardware. Is there a way
to transfer DTS packages from one server to another. Thanks.Open the package, in the package menu, select save as...you can save it to
file or directly to another server
"Tom Reis" <reistom@.cdnet.cod.edu> wrote in message
news:uq4MB3IuFHA.3568@.TK2MSFTNGP10.phx.gbl...
> We are upgrading our current SQL2000 Server to new hardware. Is there a
> way
> to transfer DTS packages from one server to another. Thanks.
>|||did you hear "Tom Reis" <reistom@.cdnet.cod.edu> say in
news:uq4MB3IuFHA.3568@.TK2MSFTNGP10.phx.gbl:

> We are upgrading our current SQL2000 Server to new hardware. Is there
> a way to transfer DTS packages from one server to another. Thanks.
>
http://www.sqldts.com/default.aspx?204
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogs|||HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/defaul...kb;en-us;314546
AMB
"Tom Reis" wrote:

> We are upgrading our current SQL2000 Server to new hardware. Is there a wa
y
> to transfer DTS packages from one server to another. Thanks.
>
>|||Hi,
Just open the DTS Package and save the package in text file it will be
saved as ii.DTS.
Copy that to another server and open the package and save in local
machine.
hope this help u
from
Doller

DTS Scripts

We are upgrading our current SQL2000 Server to new hardware. Is there a way
to transfer DTS packages from one server to another. Thanks.Open the package, in the package menu, select save as...you can save it to
file or directly to another server
"Tom Reis" <reistom@.cdnet.cod.edu> wrote in message
news:uq4MB3IuFHA.3568@.TK2MSFTNGP10.phx.gbl...
> We are upgrading our current SQL2000 Server to new hardware. Is there a
> way
> to transfer DTS packages from one server to another. Thanks.
>|||did you hear "Tom Reis" <reistom@.cdnet.cod.edu> say in
news:uq4MB3IuFHA.3568@.TK2MSFTNGP10.phx.gbl:
> We are upgrading our current SQL2000 Server to new hardware. Is there
> a way to transfer DTS packages from one server to another. Thanks.
>
http://www.sqldts.com/default.aspx?204
--
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogs|||HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/default.aspx?scid=kb;en-us;314546
AMB
"Tom Reis" wrote:
> We are upgrading our current SQL2000 Server to new hardware. Is there a way
> to transfer DTS packages from one server to another. Thanks.
>
>|||Hi,
Just open the DTS Package and save the package in text file it will be
saved as ii.DTS.
Copy that to another server and open the package and save in local
machine.
hope this help u
from
Doller

DTS Script trying to re-execute another task in package

I'm new to the whole DTS scene and not impressed with the info I'm finding. Here's what I'm trying to do:

I have two recordsets generated by ExecteSQLTasks (created as output global variables). While I'm looping through one recordset in a script I would like to do data-driven updates of the second recordset based on data in the first. This seemed easy at first. I thought I could just do a little hoochie coochie:

Dim objPackage
Dim objTask
Dim objCustomTask

Set objPackage = DTSGlobalVariables.Parent

For each objTask in objPackage.Tasks
If objTask.CustomTask.Description = "The description I gave it!" Then
objTask.Properties("SQLStatement").Value = "My new SQL statement"
objTask.Execute
End If

And voila! I'd have my new recordset established as a new value for the global variable. But no! It doesn't seem to work that way. What's up with this?

FYI, I also tried to apply filters to the original recordset using:

rs.Filter = " table.field = " & varMyDataNotAString

But that didn't work either. Grrrr!

Anybody out there doing things like this and getting them to work?

Thanks!Check SQLDTS (http://www.sqldts.com) website and search for GLOBAL VARIABLES which will show code examples to use so.

HTH|||Actually, I'm pretty sure this is where I originally got the code in my initial post. The issue is that using this code did not refresh my global variable (and hence my recordset). I could change the SQL statement, but either the Task doesn't actually execute with the

objTask.CustomTask.Execute

or somehow the global variable isn't refreshed.

I plan to continue playing with it today.

Thanks for your reply!

DTS screws up dd/mm/yyyy dates

Hi

Unlike the colonies, we still use dd/mm/yyyy format in the UK. A bit like VB string concatentation, DTS likes to treat dd/mm/yyyy dates as mm/dd/yyyy if dd <=12.

I've run into this problem twice and been stumped both times. The first time I was setting up a new DTS and switched to BULK INSERT instead. This time I was editing an existing DTS but was able to alter the CSV export to store the dates as YYYY-MM-DD. I appreciate this is ISO whatisface and ODBC canoni-watsit and is the ideal format to transfer dates between RDBMS systems but...

is there a third way? Can you tell SQL Server to expect to see the incoming dates in DD/MM/YYYY format and respond accordingly? I expected the answer to be in the DateTimeString transformation but I cannot get it to work - errors (haven't got the precise error to hand but can supply).

Not urgent. Not even important. Just curious :)

TIA chapsIf you are purely in SQL Server, you can try setting the dateformat for the query:

set dateformat dmy
select convert(datetime, '20/4/2006'), getdate()

If this is in the VB code, or whatever equivalent you have, then I am not entirely certain what you can do beyond checking the locale settings of the machine it is running on.|||Hi

It's actually during the DTS transformation. This doesn't apply to that does it?

You've pointed me towards something though. I correct for this in VB when working in that. Presumably I can write a script and use that as an ActiveX script for the transformation?|||Yep. dateformat won't have any effect in a data pump task. I found an interesting bit of code over here:
http://www.a1vbcode.com/snippet-3656.asp
towards the bottom of the example that may help you along with reworking the transform task. Modifying the transform task is something that I have successfully avoided since about 1997 or so.|||The problem is with the 'intelligence' placed in the date parser in many db systems and not just SQL servers. The only way to 'defeat' the wrong dates is to specify all updates in SQL92 "dd MMM yyyy" form.

Hence 01 Feb 2006 will always be 01/02/2006 (UK) or 02/01/2006 (US) unless the SQL coding guys have screwed up the parsing into localisation (in which case it is not SQL92 complient and should be avoided at all costs).|||I've always been fond of the ISO standard 2006-04-20 because nearly everything either outright fails (old or poorly written code), or works fine (95 percent of everything I use).

-PatP|||The problem is with the 'intelligence' placed in the date parser in many db systems and not just SQL servers. The only way to 'defeat' the wrong dates is to specify all updates in SQL92 "dd MMM yyyy" form.

Hence 01 Feb 2006 will always be 01/02/2006 (UK) or 02/01/2006 (US) unless the SQL coding guys have screwed up the parsing into localisation (in which case it is not SQL92 complient and should be avoided at all costs).I'm just curious, but where in the standard did you find this particular date format referenced?

-PatP|||This time I was editing an existing DTS but was able to alter the CSV export to store the dates as YYYY-MM-DD. I appreciate this is ISO whatisface and ODBC canoni-watsit and is the ideal format to transfer dates between RDBMS systems
I've always been fond of the ISO standard 2006-04-20 because nearly everything either outright fails (old or poorly written code), or works fine (95 percent of everything I use).
:D

I pretty well always use ISO 8601. It just so happens that it was a bit of a pest to do so on this occasion and the name "Data Transformation Services" implies to me that I might be able to inform the process that incoming dates are dd/mm/yyyy.
It is no biggie and if the response is "you are transferring data between two data sources - use the universally recognised format you moron" then I will quite merrily accept the advice.

MCrowley - apologies for not getting back. I have to admit that it is because I never quite fathomed out how it related to the problem :o|||No worries, Pootle. The code example (I thought, but might be wrong) converts or interprets currency according to the client's locale. You might be able to use the code that detects the local locale, and use that to convert/interpret the dates coming in.

After re-reading your problem, the locale of the DTS client may not be the issue so much as the locale of the guy sending you this mess ;-).|||No worries, Pootle. The code example (I thought, but might be wrong) converts or interprets currency according to the client's locale. You might be able to use the code that detects the local locale, and use that to convert/interpret the dates coming in.

After re-reading your problem, the locale of the DTS client may not be the issue so much as the locale of the guy sending you this mess ;-).Lol - yup - converting isn't a problem (just a minor hassle) and the locale of the DTS client is known and, in my case, unvarying from server to server so it is probably a bit overkill as far as my situation is concerned.

I will have a word with myself for sending me this mess :p To be fair - the outputting program merely picked up the local settings and patriotically outputted the dates in good old British DD/MM/YYYY. I have since told it to output in the rather more sterile YYYY-MM-DD.

DTS schedult problem

I created a DTS package to transfer a table to a text file. Every time when
I click start manually, it'll run and transfer the data to a local txt file
with no problem. But when I schedule this DTS package and run it from the
SQL Agent - Jobs. It always fail to run. I don't know why. Anybody can help
me?|
| I created a DTS package to transfer a table to a text file. Every time
when
| I click start manually, it'll run and transfer the data to a local txt
file
| with no problem. But when I schedule this DTS package and run it from the
| SQL Agent - Jobs. It always fail to run. I don't know why. Anybody can
help
| me?
--
We need more information to help you. What errors are returned when the job
fails? Post it and we'll give it a shot.
Thanks,
--
Eric Cardenas
SQL Server support|||JL
I think that you cerated DTS package on the workstation not on the server ,
then SQL Agent on your wokstation is running under differnent account than
server.
Check it out.
"JL" <ljmagzine@.hotmail.com> wrote in message
news:eGGt1AgvDHA.2304@.TK2MSFTNGP12.phx.gbl...
> I created a DTS package to transfer a table to a text file. Every time
when
> I click start manually, it'll run and transfer the data to a local txt
file
> with no problem. But when I schedule this DTS package and run it from the
> SQL Agent - Jobs. It always fail to run. I don't know why. Anybody can
help
> me?
>|||VXJpIGlzIGNvcnJlY3QuICBJIHJhbiBpbnRvIHRoaXMgcHJvYmxlbSBhbmQgaXQgaGFkIG1lIHN0
dW1wZWQgZm9yIGEgd2hpbGUuDQoNClRoZSBzb2x1dGlvbiBpcyB0byBjcmVhdGUgdGhlIERUUyBw
YWNrYWdlIGFuZCBKb2Igd2hpbGUgcGh5c2ljYWxseSBhdCB0aGUgU1FMIFNlcnZlciBib3guDQoN
Cg0KIkpMIiA8bGptYWd6aW5lQGhvdG1haWwuY29tPiB3cm90ZSBpbiBtZXNzYWdlIG5ld3M6ZUdH
dDFBZ3ZESEEuMjMwNEBUSzJNU0ZUTkdQMTIucGh4LmdibC4uLg0KPiBJIGNyZWF0ZWQgYSBEVFMg
cGFja2FnZSB0byB0cmFuc2ZlciBhIHRhYmxlIHRvIGEgdGV4dCBmaWxlLiBFdmVyeSB0aW1lIHdo
ZW4NCj4gSSBjbGljayBzdGFydCBtYW51YWxseSwgaXQnbGwgcnVuIGFuZCB0cmFuc2ZlciB0aGUg
ZGF0YSB0byBhIGxvY2FsIHR4dCBmaWxlDQo+IHdpdGggbm8gcHJvYmxlbS4gQnV0IHdoZW4gSSBz
Y2hlZHVsZSB0aGlzIERUUyBwYWNrYWdlIGFuZCBydW4gaXQgZnJvbSB0aGUNCj4gU1FMIEFnZW50
IC0gSm9icy4gSXQgYWx3YXlzIGZhaWwgdG8gcnVuLiBJIGRvbid0IGtub3cgd2h5LiBBbnlib2R5
IGNhbiBoZWxwDQo+IG1lPw0KPiANCj4gsql sql

DTS Scheduling Issue

I have a DTS package that runs without any problems when I run it manually, however, when I schedule the package at a certain time no output is produced.

I have checked the Agent and it says the job is executing, however, it seems to have hung.

The log tells me DTSStep CreateProcessTask3 started at the correct time but there is nothing after that.

How do I check this? or find out what is causing it to hang?

Thanks!Try running profiler at the time the job start starts executing to see how far the job get's. What user account is the job run under?|||Hey tr1na,

Many thanks for your response it is appreciated. SQL server is new to me so apologies for the lack of knowledge

I have never run profiler before, I have had a look and it doesnt look that simple to run and also, I couldnt see where I could call my DTS package.

Anyway, in the package log file it states DTSStep CreateProcessTask3 started at 07.32 and well thats it! I have read somewhere that this could be a security issue and I should be logged in as SYSADMIN? However, I am currently Remote Desktop to the SERVER, login through NT, the package owner is my USER ID, how can I log into EM as SYSADMIN and try scheduling a DTS package under SYSADMIN, do you think this could be the problem?

I am not sure how to check the LOGIN ID.|||To view which user the job is running as right click the job and select "view job history" then tick "show step details". You should then see

"Executed as user: SYD\blah" etc|||Thanks!

Well, unfortunately, the job does not complete because it hangs so there is no job history, however, when I cancelled to job it tells me that the job was cancelled prior to completion by my "USER ID". So I suspect this is the USER ID being used to start the job?

Perhaps I need someway to remote desktop but login as SYSADMIN?|||when you run the job manually are you logged in as your userid. if so check the job runs under your user id.|||there are differences of environment when u execute a dts by right-clicking and thru jobs. for example if u refer a server name as (local) in connection, during right-clicked execution the database will be selected from the server where EM is installed but during execution thru job it will connect to server from where the job is running. similarly any disk file path/reference to COM component also changes with the execution mode. and this is often the source of problem reported by u...|||OK. The job did not run manually when logged in by me, it does run manually when logged in to the database as "sa"

upalsen:

Thank you for your response, it is appreciated, how can I get round this problem, is it a matter of logging in as SYSADMIN, hopefully it is as something as simple as this?|||there r a few settings under properties context menu (right-click) of SQL Server Agent. by default users other than sysadmin r not allowed to run cmdexec etc command. check the Job-System tab and Connection tab for such user specific issues. also a job can be saved under user other than sa. i am not sure exactly where your job is hanging but u can try the above combinations and check the result.|||Thank you.

OK. Well the check box is checked "Only users with SysAdmin priviledges can run CmdExec etc..."

So I uncheck the box, then it asks me to "Enter the proxy account SQL agent will use to run jobs owned by non-administrators"

Does this mean I need to create a "dummy" account and does it need to be a member of the sysadmin group or have specific priviledges?

Thank you.|||u give the name & password of the local m/c administrator and enter the machine name as domain. if u r having a domain controller in your network u can specify the details of a domain account as well.

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 scheduling and float problem

Hi
I have some DTS-package that import textfiles. When I run
then manually everything works fine, but when i schedule
them the fields containing floatvalues is becomming
integers instead. Any one who know whats the problem is?
/AnettHi,
Inside the data Transformation task, change the destination field to Float
data type inside the package.
Thanks
Hari
SQL Server MVP
"Anett" <anonymous@.discussions.microsoft.com> wrote in message
news:051c01c54019$a2377270$a401280a@.phx.gbl...
> Hi
> I have some DTS-package that import textfiles. When I run
> then manually everything works fine, but when i schedule
> them the fields containing floatvalues is becomming
> integers instead. Any one who know whats the problem is?
> /Anett
>

DTS scheduling and float problem

Hi
I have some DTS-package that import textfiles. When I run
then manually everything works fine, but when i schedule
them the fields containing floatvalues is becomming
integers instead. Any one who know whats the problem is?
/Anett
Hi,
Inside the data Transformation task, change the destination field to Float
data type inside the package.
Thanks
Hari
SQL Server MVP
"Anett" <anonymous@.discussions.microsoft.com> wrote in message
news:051c01c54019$a2377270$a401280a@.phx.gbl...
> Hi
> I have some DTS-package that import textfiles. When I run
> then manually everything works fine, but when i schedule
> them the fields containing floatvalues is becomming
> integers instead. Any one who know whats the problem is?
> /Anett
>

DTS scheduling and float problem

dts scheduling

Hi
i am trying to schedule a dts package in sqlserver 2005 but i really didn't
find any options like(schedule)
can anyone tell me how to schedule a dts pakage in sqlserver 2005
ur help is really appreciated
thanks
Message posted via http://www.droptable.com
In step of SQL Server Job select type as
SQL Server Integration Service Package and go on..
Regards
Amish
*** Sent via Developersdex http://www.codecomments.com ***

dts scheduling

Hi
i am trying to schedule a dts package in sqlserver 2005 but i really didn'
t
find any options like(schedule)
can anyone tell me how to schedule a dts pakage in sqlserver 2005
ur help is really appreciated
thanks
Message posted via http://www.droptable.comIn step of SQL Server Job select type as
SQL Server Integration Service Package and go on..
Regards
Amish
*** Sent via Developersdex http://www.codecomments.com ***

dts scheduling

Hi
i am trying to schedule a dts package in sqlserver 2005 but i really didn't
find any options like(schedule)
can anyone tell me how to schedule a dts pakage in sqlserver 2005
ur help is really appreciated
thanks
--
Message posted via http://www.sqlmonster.comIn step of SQL Server Job select type as
SQL Server Integration Service Package and go on..
Regards
Amish
*** Sent via Developersdex http://www.developersdex.com ***

DTS Scheduled Time

How can I tell when a DTS package is scheduled to run?
If I right click on the name, and select "Schedule Package", it brings up
default data (12:00 am Daily), and I KNOW that's not the scheduled time.
ThanksThat is the mechanism by which you set up a scheduled job via SQL Agent. By alter the parameters of the screen to the schedule you desire and clicking on OK, the GIU will build a scheduled job under the AGent with the same name as the DTS package.|||Now I see it. Thanks

DTS Scheduled Task Fails - Permission Denied

I searched the forum's threads on this, and while there were many results, none have helped so far.

I am running a DTS package that is an ACtiveX Script Task using VBScript. The script uses CreateObject() to create a FileSystemObject to copy an .MDB before importing the tables into SQL Server. I want to copy it because of Access' notoriety of corrupting, and this much data being pumped out of Access could force me to Compact & Repair. I would rather do that on a copy.

Function Main()

Dim FSO
Set FSO=CreateObject("Scripting.FileSystemObject")

FSO.CopyFile "\\<server>\<Share>\Data.mdb", "\\<server>\C$\DataCopy\Data.mdb"

Main = DTSTaskExecResult_Success
End Function

The DTS Package runs when I execute it from Ent. Manager, of course. It fails if scheduled, or course :(

I have set the Owner of the Scheduled Task to my domain account, which is also in the Adminstrators Group on the physical server with the SQL Server installation (Windows 2003 Server). I also did the unnecessary task of adding my domain account specifically to the destination folder, which is also Shared.

My sqlagent.exe service runs as SYSTEM on the server, so the SQLAgent should have no problem copying a file from one folder on the server to another.

The Scheduled Task fails with the common error:

DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSActiveScriptTask_1
DTSRun OnError: DTSStep_DTSActiveScriptTask_1, Error = -2147220482 (800403FE)
Error string: Error Code: 0
Error Source= Microsoft VBScript runtime error
Error Description: Permission denied
Error on Line 12
Error source: Microsoft Data Transformation Services (DTS) Package
Help file: sqldts80.hlp
Help context: 4500
Error Detail Records:
Error: -2147220482 (800403FE); Provider Error: 0 (0)
Error string: Error Code: 0
Error Source= Microsoft VBScript runtime error
Error Description: Permission denied
Error on Line 12
Error source: Microsoft Data Transformation Services (DTS) Package
Help file: sqldts80.hlp
Help context: 4500
DTSRun OnFinish: DTSStep_DTSActiveScriptTask_1
DTSRun: Package execution complete.

I checked this MS KB Article (http://support.microsoft.com/kb/q298725/), but the instructions after opening DCOMcnfg.exe do not follow what is shown in the WMI window on Windows 2003 Server :rolleyes: (i.e. there is no "Default Security" tab to click.)

I am at a loss here. Thanks for your help.I'll bump this once.|||I'm still working on it so not sure if what I suspect is the issue... but have a dts package that download a file, based on SQL statement from as/400 to sql table... runs great when I run it myself within the enterprise manager but when I try to call from a stored procedure via my exe it fails to run.

What I've deduced is that when I run it within the enterprise manager that it is, in essence, running on the client... locally and using my dll's, etc... however, when I run via stored procedure through my exe it is trying to run on the server, herein lies the problem (really strong guess at this point)... the server is not set up to connect to the as/400 and that's where it bombs.

I'm guessing that your sql server is having issues talking to your access database in much the same way that mine is having issues talking to the as/400. I've got the client software installed now and will be testing my theory shortly. Hope this helps you with your issue.

b|||Is the access db on the same server as Sql server?

If so, do non unc paths work? Such as :

FSO.CopyFile "c:\somefolder\Data.mdb", "c\DataCopy\Data.mdb"|||.. I have a package as well.
The thing is that if I put in a msgbox to execute directly before the ".deletefile", the package will run, but if I outcomment It it fails...
I can on the other hand do an ".copyfile" instead...
I only have the problem with the ".deletefile"
I would also appriciate all help i can get on this.

// selander

Function Main()

Dim Eso, E, E1, Ec
Set Eso = CreateObject("Scripting.FileSystemObject")
Set E = Eso.GetFolder("\\CLUST\SYS\IMPORT\ENT\")
Set Ec = E.Files
For Each E1 in Ec
if ucase(E1.name) = ucase(DTSGlobalVariables("strFileName").Value)+".ZIP" then
msgbox "FUBAR"
Eso.DeleteFile "\\CLUST\SYS\IMPORT\ENT\" + E1.name , True
exit for
end if
Next
Main = DTSTaskExecResult_Success
End Functionsql sql

DTS Scheduled job always fails

Hi,

I created and saved a DTS package to "SQL Server". When I "Execute Package" under the "Data Transformation -> Local Packages" folder in Enterprise Mgr, it executes perfectly.

However, when I Schedule it, the scheduled job Fails. The View Job History shows "The job failed. The Job was invoked by User sa. The last step to run was step 1 (ImportTest3)."

Could someone please help me with this? I don't understand why the manual execution works, but the scheduled job doesn't. Thanks for the help!Did you ever find a solution to this problem? I am having the same problem.

Originally posted by multiplex77
Hi,

I created and saved a DTS package to "SQL Server". When I "Execute Package" under the "Data Transformation -> Local Packages" folder in Enterprise Mgr, it executes perfectly.

However, when I Schedule it, the scheduled job Fails. The View Job History shows "The job failed. The Job was invoked by User sa. The last step to run was step 1 (ImportTest3)."

Could someone please help me with this? I don't understand why the manual execution works, but the scheduled job doesn't. Thanks for the help!|||You execute under different security contexts when executing a dts package manually versus scheduling it as a job. What is your dts script doing ?|||I set up a number of packages to process selected cubes in Analysis Services. The first job processes all dimensions and even that one fails.|||What message is returned for the failure ?|||ptcooper - In your case, you can execute the package and it succeeds - but when you schedule it as a job, it fails - is this true ?|||True, the problem is exactly as described by multiplex77. the only jobs that fail are the ones that are processing OLAP cubes/dimensions

Originally posted by rnealejr
ptcooper - In your case, you can execute the package and it succeeds - but when you schedule it as a job, it fails - is this true ?|||Who is the owner of the job ?|||The owner is sa and the password was specified when the DTS packages were saved. SLQ Server Agent is logged on as administrator.

Originally posted by rnealejr
Who is the owner of the job ?|||log the package and post the message(s) returned in the log.|||I don't see a way to log the package. I've never had to do that.
Originally posted by rnealejr
log the package and post the message(s) returned in the log.|||If the SQL Agent is running as the local administrator, is the local administrator a member of the local Olap Administrators group? Not sure if it defaults to this.|||The SQlSERVERAGENT does not log on as the local administrator. It logs on as a system wide administrator. We have been looking for the OLAP administrator group in the 'security' tab and the 'member of' tab for the administrator, but don't find it listed in any of the drop down lists. We have all the latest service packs installed.|||The Olap Administrators group is a local group created by the Analysis Services install. Anyone who is not a member of this group can not use the SQL Server tools to manage, modify, or otherwise peek at the cubes on that machine. Remember this only applies to the MMC tool. The OLAP Administrators group will not show up in the domain-wide dropdown lists, since it is local only to one machine. Does that help?|||Right click your "My Computer" -> Manage -> Local Users and Groups -> Groups -> Olap Admin ... Add the account that is used to run sql server agent.|||UPDATE: We discovered that the OLAP Administrator group had somehow been deleted from the Active directory. We tried re-installing service pack 3 for Analysis Services, recreating the OLAP Administrators group and setting the members, properties etc. None of this worked. We just re-installed Analysis Services and service pack 3 on the server and now the jobs will run from the workstation in EM|||The OLAP administrators group is not in AD. Never was. It is local to the Analysis Services machine.|||Our Analysis Services server is a Domain controller, and as such cannot use the Local Users.. snap in. You get directed to the Active Directory Users and Computers when you try opening it. (also ref. MS KB article 829738 'More Information')|||Once you installed it as a domain controller in ad, the local users/groups do not transfer - which is why the olap admin group never appeared. I am a little curious that other problems did not appear before this problem.

Thanks for posting the follow-up with the solution.

DTS Scheduled Job

I am getting the following error whenever I execute
the job via sql server job agent. When I execute
the job package manually via dts the result is good.
Should I mount the physical drive to the main server?
Executed as user: card\sql. DTSRun: Loading... DTSRun:
Executing...
DTSRun OnStart: Copy Data from Results to S:\test.xls
Step DTSRun OnError: Copy Data from Results to
S:\test.xls Step,
Error = -2147467259 (80004005)
Error string: Error opening datafile: The system cannot
find the path specified.
Error source: Microsoft Data Transformation Services Flat
File Rowset Provider
Help file: DTSFFile.hlp Help context: 0
Error Detail Records: Error: 3 (3); Provider Error:
3 (3)
Error string: Error opening datafile: The system cannot
find the path specified.
Error source: Microsoft Data Transformation Services Flat
File Rowset Provider
Help file: DTSFFile.hlp Help context: 0
DTSRun OnFinish: Copy Data from Results to S:\test.xls
Step
DTSRun: Package execution complete. Process Exit Code
1.
The step failed.DTS is a client tool and thus when you run it from EM it's running
on your pc. If the server doesn't have an S drive then you'll get this
error. It's best to use UNC paths to avoid ambiguity. Also check out
INF: How to Run a DTS Package as a Scheduled Job
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q269074
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"L Dominguez" <ldominguez@.cardionet.com> wrote in message
news:02af01c34730$9a2823b0$a601280a@.phx.gbl...
I am getting the following error whenever I execute
the job via sql server job agent. When I execute
the job package manually via dts the result is good.
Should I mount the physical drive to the main server?
Executed as user: card\sql. DTSRun: Loading... DTSRun:
Executing...
DTSRun OnStart: Copy Data from Results to S:\test.xls
Step DTSRun OnError: Copy Data from Results to
S:\test.xls Step,
Error = -2147467259 (80004005)
Error string: Error opening datafile: The system cannot
find the path specified.
Error source: Microsoft Data Transformation Services Flat
File Rowset Provider
Help file: DTSFFile.hlp Help context: 0
Error Detail Records: Error: 3 (3); Provider Error:
3 (3)
Error string: Error opening datafile: The system cannot
find the path specified.
Error source: Microsoft Data Transformation Services Flat
File Rowset Provider
Help file: DTSFFile.hlp Help context: 0
DTSRun OnFinish: Copy Data from Results to S:\test.xls
Step
DTSRun: Package execution complete. Process Exit Code
1.
The step failed.

DTS schedule job fails urgent pls help!

Hi all,

We have created DTS jobs which copies data from one server to another and truncates the destination before copying the data from source. when we run the job manually it runs fine, but when schedule its failing with the below errors.

DTSRun: Loading...DTSRun: Executing...DTSRun
OnStart: DTSStep_DTSExecuteSQLTask_1DTSRun OnFinish:
DTSStep_DTSExecuteSQLTask_1DTSRun OnStart: Copy Data
from CustomerMaster to
[OUTBOUND].[dbo].[CustomerMaster]
StepDTSRun OnError: Copy Data from CustomerMaster
to [OUTBOUND].[dbo].[CustomerMaster]
Step, Error = -2147008507 (80074005) Error string:
Unspecified error Error source: Microsoft Data
Transformation Services (DTS) Package Help file:
sqldts.hlp Help context: 1100Error Detail
Records:Error: -2147008507 (80074005); Provider
Error: 0 (0) Error string: Unspecified error
Error source: Microsoft Data Transformation Services
(DTS) Package Help file: sqldts.hlp Help context:
1100Error: -2147467259 (80004005); Provider Error:
0 (0) Error string: Timeout expired Error source:
Microsoft OLE DB Provider for SQL Server Help file:
Help context: 0DTSRun OnFinish: Copy Data from
CustomerMaster to
[OUTBOUND].[dbo].[CustomerMaster]
StepDTSRun: Package execution complete.
please suggest to over come this problem.

TIAMy guess is that the DTS job is scheduled to run as a different NT logon than the one you are using to test it interactively. The scheduled logon is probably missing one or more permissions needed for the DTS package.

-PatP

DTS schedule and oracle

Here's a weird problem. I've read everything I could find about permissions and scheduling a DTS but I'm still having problems. I've created a DTS on my production server (Windows 2000 Server) and it runs fine when I'm running it on the server. (I'm at the machine) When I schedule it, it starts to run and then it just hangs on step one. I tried removing steps from my dts to see which one is causing the problem and through process of elimination I found it was the step that connects to my oracle server. When I run the DTS manually I am logged in as SJKAdmin and the SQLServer Agent account is SQLAdmin. Both users are local administrators on both the production server and the server where the oracle DB is. I just can't figure it out. The real kicker is that I have a development server set up that's supposed to be identical to the production server and both the DTS (when run manually) and the scheduled jobs run without any problem. Any help would be greatly appreciated.

BrettHave you logged into the production server (@. the prodution server) as SQLAdmin and gone into Oracles Net Manager and tested the connection?

DTS schedule

I have 2 DTS and i want to make sure that DTS2 runs after the DTS1 has finished, how can i do this??
Thanks,
Joo MartinsCan you run dts2 within dts1 or does it have to be run outside of dts1 ?|||dts2 must run outside dts1|||When you schedule a DTS package using EM, it creates a job with the name same as the package. This job has only one step - with the name same as the package. However, you can add more steps to the job and specify the sequence in which they will run. The job will be under EM - Server - Management - SQL Server agent - Jobs.sql sql

DTS runs OK, but not scheduled job

Hi,
When I run DTS manually, it works fine. But when I run the scheduled job, it
failes.
The error said cannot find a file specified. It imports Excel file to
SQL2000 Server database. I set same domain user id for DTS creater and Agent
executer and job owner.
I read several articles same problem like this, but I haven't get solution...
--
Thank you,Masako
Where is located the EXCEL File?
It should be located on server and not on the your workstation.
"Masako" <Masako@.discussions.microsoft.com> wrote in message
news:949CB8A5-FC52-4D2D-9208-F8FD750EE40C@.microsoft.com...
> Hi,
> When I run DTS manually, it works fine. But when I run the scheduled job,
it
> failes.
> The error said cannot find a file specified. It imports Excel file to
> SQL2000 Server database. I set same domain user id for DTS creater and
Agent
> executer and job owner.
> I read several articles same problem like this, but I haven't get
solution...
> --
> Thank you,|||Hi Uri,
Does it have to? The Excel file is located on another server.
I had no problem like this job flow previous SQL Server. We used to Windows
NT server + SQL7, now new server is Windows2000 english version + SQL2000
Japanese version.
"Uri Dimant" wrote:
> Masako
> Where is located the EXCEL File?
> It should be located on server and not on the your workstation.
>
> "Masako" <Masako@.discussions.microsoft.com> wrote in message
> news:949CB8A5-FC52-4D2D-9208-F8FD750EE40C@.microsoft.com...
> > Hi,
> >
> > When I run DTS manually, it works fine. But when I run the scheduled job,
> it
> > failes.
> > The error said cannot find a file specified. It imports Excel file to
> > SQL2000 Server database. I set same domain user id for DTS creater and
> Agent
> > executer and job owner.
> >
> > I read several articles same problem like this, but I haven't get
> solution...
> >
> > --
> > Thank you,
>
>|||Maskao
Make sure that SQL Server Agent is running under Domain Account not a Local
account.
"Masako" <Masako@.discussions.microsoft.com> wrote in message
news:CC48C0DC-A9F9-4E3A-8277-F6C55BF6C588@.microsoft.com...
> Hi Uri,
> Does it have to? The Excel file is located on another server.
> I had no problem like this job flow previous SQL Server. We used to
Windows
> NT server + SQL7, now new server is Windows2000 english version + SQL2000
> Japanese version.
> "Uri Dimant" wrote:
> > Masako
> > Where is located the EXCEL File?
> > It should be located on server and not on the your workstation.
> >
> >
> > "Masako" <Masako@.discussions.microsoft.com> wrote in message
> > news:949CB8A5-FC52-4D2D-9208-F8FD750EE40C@.microsoft.com...
> > > Hi,
> > >
> > > When I run DTS manually, it works fine. But when I run the scheduled
job,
> > it
> > > failes.
> > > The error said cannot find a file specified. It imports Excel file to
> > > SQL2000 Server database. I set same domain user id for DTS creater and
> > Agent
> > > executer and job owner.
> > >
> > > I read several articles same problem like this, but I haven't get
> > solution...
> > >
> > > --
> > > Thank you,
> >
> >
> >

DTS runs manually, fails as scheduled job

I've mined countless threads all over the Net where people are having my problem, but none of the solutions I've seen seem to apply.

When I run a DTS package from Enterprise Manager, everything works great. When I schedule the same package as a job, it fails immediately and reports this error:

Executed as user: SERVER-NAME\SYSTEM. DTSRun: Loading... Error: -2147221005 (800401F3); Provider Error: 0 (0) Error string: Invalid class string Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 713. Process Exit Code 1. The step failed.

My hunch is the SERVER-NAME\SYSTEM.DTSRun account doesn't have sufficient privileges, but I can't find how/where to fix that.

Help, please.

Thanks,

Doug

Checkout the following links

http://www.sqlmag.com/articles/index.cfm?articleid=46723& - Security in SSIS
http://support.microsoft.com/?kbid=918760 - An SSIS package does not run when you call the SSIS package from a SQL Server Agent job step

Thanks,
Loonysan

|||

"Invalid class string" is not strictly a SQL Server error in DTS. Essentially it means in cannot find the CLSID for a required object when reading the package. This error can also occur when loading a package that contains a CustomTask which has not been registered on the current machine. (http://www.databasejournal.com/features/mssql/article.php/1461391)

PRB: "Invalid class string" error message when opening DTS package
(http://support.microsoft.com/?scid=kb%3Ben-us%3B288910&x=15&y=16)

The top two hits I got from Google, both say pretty much the same thing. Something is missing from your server, which causes the "Invalid class string", or "I cannot find your task".

FYI, general DTS info on jobs-

INF: How to Run a DTS Package as a Scheduled Job
(http://support.microsoft.com/kb/269074/en-us)

DTS runs great... but not from the job list

I have a DTS package that updates a database from a text file and it works
great when I run it manually from the DTS designer. However, I right click
on it and set it up to run automatically and it will not run. I went to
view the history of the job messages and it appears that the following is
the error for this task:
Executed as user: MAHC_SQL2\SYSTEM. DTSRun: Loading... DTSRun:
Executing... DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun
OnFinish: DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart:
DTSStep_DTSDataPumpTask_1 DTSRun OnError: DTSStep_DTSDataPumpTask_1,
Error = -2147467259 (80004005) Error string: Error opening datafile:
The network name cannot be found. Error source: Microsoft Data
Transformation Services Flat File Rowset Provider Help file:
DTSFFile.hlp Help context: 0 Error Detail Records: Error:
67 (43); Provider Error: 67 (43) Error string: Error opening
datafile: The network name cannot be found. Error source: Microsoft
Data Transformation Services Flat File Rowset Provider Help file:
DTSFFile.hlp Help context: 0 DTSRun OnFinish:
DTSStep_DTSDataPumpTask_1 DTSRun: Package execution complete. Process
Exit Code 1. The step failed.
Can anyone give me any clues as to what the problem could be?
Schoo
Have a look at this KB that details the common reasons a scheduled DTS
package will fail
INF: How to Run a DTS Package as a Scheduled Job
http://support.microsoft.com/default...;en-us;Q269074
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Schoo" <scott.schuman@.nospam.ma-hc.com> wrote in message
news:%23enkcwUrEHA.452@.TK2MSFTNGP09.phx.gbl...
>I have a DTS package that updates a database from a text file and it works
> great when I run it manually from the DTS designer. However, I right
> click
> on it and set it up to run automatically and it will not run. I went to
> view the history of the job messages and it appears that the following is
> the error for this task:
> Executed as user: MAHC_SQL2\SYSTEM. DTSRun: Loading... DTSRun:
> Executing... DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun
> OnFinish: DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart:
> DTSStep_DTSDataPumpTask_1 DTSRun OnError: DTSStep_DTSDataPumpTask_1,
> Error = -2147467259 (80004005) Error string: Error opening datafile:
> The network name cannot be found. Error source: Microsoft Data
> Transformation Services Flat File Rowset Provider Help file:
> DTSFFile.hlp Help context: 0 Error Detail Records: Error:
> 67 (43); Provider Error: 67 (43) Error string: Error opening
> datafile: The network name cannot be found. Error source:
> Microsoft
> Data Transformation Services Flat File Rowset Provider Help file:
> DTSFFile.hlp Help context: 0 DTSRun OnFinish:
> DTSStep_DTSDataPumpTask_1 DTSRun: Package execution complete. Process
> Exit Code 1. The step failed.
> Can anyone give me any clues as to what the problem could be?
> Schoo
>
|||Hi Schoo,
Thanks for you post and I would love to show my gratidue for MVP Jasper
Smith's providing that great KB:Q269074
From your Error Message, I foudn the error was "The network name cannot be
found" so that please check whether there is anything wrong with your
source and target of database. Make sure they are "online", also please
check the permission issue. It seems strange and I need more information to
make further troubleshooting.
Would you please generate a small DTS package and see whether it works
fine? To get a better picture of the problem we would like to reproduct the
issue on our end. To expedite the process, please provide us the detailed
reproduce steps.
Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Hi Schoo,
I wanted to post a quick note to see if you would like additional
assistance or information regarding this particular issue. We appreciate
your patience and look forward to hearing from you!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

Dts Run Problem

Hi all,

I can run my dts in a server which is on our network. And i send global variables and dts runs correctly.

But in another dts; the same method does not work. But i don't get any exception. I use Dts class of Framework (Interop.Dts.dll).

My ExecuteDts Function is :

public static void ExecuteDTS(string PackageName,params object[] DtsVariableValues) {string ServerName = ConnectionNames.OlapServerName;string ServerUserName = ConnectionNames.OlapUserName;string ServerPassword = ConnectionNames.OlapPassword;object pVarPersistStgOfHost =null; DTS.PackageClass DtsPackage =new DTS.PackageClass(); DtsPackage.LoadFromSQLServer(ServerName, ServerUserName, ServerPassword, DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_Default ,null,null,null, PackageName,ref pVarPersistStgOfHost);try {string[] DtsVariableNames =new String[DtsPackage.GlobalVariables.Count];int VariableIndex = 0; DtsPackage.UseTransaction =true;foreach (GlobalVariable globalin DtsPackage.GlobalVariables) { DtsVariableNames[VariableIndex] = global.Name; DtsPackage.GlobalVariables.Remove(global.Name); VariableIndex++; } VariableIndex = 0;foreach (String DtsVariableNamein DtsVariableNames) { DtsPackage.GlobalVariables.AddGlobalVariable(DtsVariableName, DtsVariableValues.GetValue(VariableIndex)); VariableIndex++; } DtsPackage.Execute(); }catch (Exception ex) { //Console.WriteLine(ex.Message); }finally { DtsPackage.UnInitialize(); DtsPackage =null; } }

Any idea?

Regards..

sourvil:
Hi all,
I can run my dts in a server which is on our network. And i send global variables and dts runs correctly.
But in another dts; the same method does not work. But i don't get any exception. I use Dts class of Framework (Interop.Dts.dll).

Can you give us more details about the two machines you are using?|||

My 2 dts are on the same machine, on the same sql server.

Hmm, i think also it would be permission issue but i could not solve it yet?

|||

I think if you give the DTC creater local admin previleges will solve the issue.

Good luck.

|||

I get the Step Error Code:80004005. Error opening datafile (it is a text source in my app) : Logon Failure: unknown user name or bad password. I think 'sa' login (for sql server 2000) does not have enough rights to edit that text file.

I could not solve it yet?

Regards..

|||

sourvil:
I get the Step Error Code:80004005. Error opening datafile (it is a text source in my app) : Logon Failure: unknown user name or bad password. I think 'sa' login (for sql server 2000) does not have enough rights to edit that text file.
I could not solve it yet?

Regards..

I think you typed the password incorrectly or Did you forget the sa password? (Try to login on the SQL Server, will you will be able to login successfully?).

Good luck..

|||

Thanks for your qucik reply. But other dts packages work correctly with this username/password. And also the first 2 steps (Activex Code and ExecuteSql Step) works fine. But in the third step, i read some data from Text File Source. And there is an accessable error in here. My SqlAgent's Username is a LocalAdmin in that shared folder's computer.

...

Step Error Description:Error opening datafile: Logon failure: unknown user name or bad password.

Step Error Code: 80004005

...

Any idea?

Regards..

|||

sourvil:
Thanks for your qucik reply. But other dts packages work correctly with this username/password. And also the first 2 steps (Activex Code and ExecuteSql Step) works fine. But in the third step, i read some data from Text File Source. And there is an accessable error in here. My SqlAgent's Username is a LocalAdmin in that shared folder's computer.
...
Step Error Description:Error opening datafile: Logon failure: unknown user name or bad password.
Step Error Code: 80004005
...
Any idea?

Regards..

Check out these links:
http://www.computerperformance.co.uk/Logon/code/code_80004005.htm
http://www.lazydba.com/sql/1__7259.html
http://tutorials.aspfaq.com/8000xxxxx-errors/80004005-errors.html

Good luck.

|||

Thanks for reply but my problm isn't solved yet. I read those articels. But my dts works fine from Designer, but it does not work from asp.net page.

I know that when i want to run dts from asp.net, SqlAgent's Domain Username runs instead of my current using login name. But dts cannot open the shared text file when i call it from asp.net page, and dts fails in that step.

Any other idea?

Regards..

|||

sourvil:
Thanks for reply but my problm isn't solved yet. I read those articels. But my dts works fine from Designer, but it does not work from asp.net page.
I know that when i want to run dts from asp.net, SqlAgent's Domain Username runs instead of my current using login name. But dts cannot open the shared text file when i call it from asp.net page, and dts fails in that step.

Any other idea?

Regards..

I guess it related to a permission on the folder where the filer is reside.
Make sure ASPNET user has the needed permissions on that folder (maybe this is the why its fail not sure).

Good luck.

|||

Ok, i will not solve it :(

But i used "master.xp_cmdshellDTSRUN" method and it worked. In fact, i do not want to use this method because of writing all Global Variable Names statically. But this solved my problem.

Thank you very muchCS4Ever,have a nice day..

Best regards.

|||

sourvil:
Ok, i will not solve it :(
But i used "master.xp_cmdshellDTSRUN" method and it worked. In fact, i do not want to use this method because of writing all Global Variable Names statically. But this solved my problem.

Thank you very muchCS4Ever,have a nice day..

Best regards.

Your are welcomesourvil.

Suggestion (will not solve the problem but will help you in the approach you decided to go with):

Have two DTS, one is the real one (has all the logic and transdormations) and the other have a task to run the first one.

This will help you a lot I guess,

Good luck.

DTS run from .NET

My dts package has to pick up a file which is in a mapped drive of the
sql server... (system1)...
when I run it from code the execution ends up in an error... but if I
have the drive mapped (drive where I have the source file) onto the
web server (system2) the execution goes through fine.. I donot want to
have the drive mapped onto the web server.. how can I do this..Try and use the UNC path.

Joel Scavone|||I did use a full netwok path and even did add identity impersonate in
the web.config... but does not help..

The package runs fine when I register the sql server my machine and run
the package..

Sigh! Don't know what wrong

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

DTS Rounding problem

I have a problem...
Data transformation rounds or truncate decimals!
I have an ODBC source in witch is a table with float numbers (ODBC Driver
publish it as float).
If I build a query form access or from excel with Query Analizer, I see all
decimal places, but when I try to insert data using DTS, float numbers will
be converted to its integer values.
A "select * from table", with table ODBC table, gives integer value when
runned from DTS to transform data from ODBC to MS-SQL Server table, and
gives float values from Access or other tools.
Where I can fix my problem?
Thanks.
Giorgioput convert(float,field) around your fields in the insert
example
declare @.i int
select @.i =1
select convert(float,@.i),@.i

http://sqlservercode.blogspot.com/

DTS re-uses old text file input

Hi All,

A simple DTS job I have is giving me fits. It is a straight copy column job from a pipe delimited text file into a table. The input file comes from a mapped drive linked to a shared filesystem on a sun solaris box.

The typical scenario. I run the DTS job to load 8000 rows from the input text file. Job succeeds.

A week later, the text file is updated with 9000 new rows. I run the DTS job with no changes and it loads 8000 rows from last week.

I reboot my Win XP pc and run DTS again. It now loads the 9000 new rows.

I tried mapping to a UNC to no avail.

Is it buffering the old file somewhere? I need help.

current environment:
SQL Server 2000 with all latest SP's and patches
Windows 2000 Server with all latest SP's and patches
Drive 'G' mapped to a shared filesystem on sun solaris via Samba?I don't think DTS will use such type of cache and read old settings, ensure the source file path is defined correctly and oin the next execution it may be reading old settings.|||I don't think DTS will use such type of cache and read old settings, ensure the source file path is defined correctly and oin the next execution it may be reading old settings.

Thanks Satya,

I'm not sure what you mean by "old settings"? Here's what I do know.

Drive 'G' is mapped to \\sun001\data

The input file name is weekly_updates.txt and does not change.

My DTS connection 1 properties uses
G:\weekly_updates.txt in the File Name box.

It always works correctly after a reboot, and more specifically, from any pc, not just mine. Any idea's?|||Might it be somehow related to Enterprise Manager? I wonder if there is a way to automatically force everything in EM to refresh. I dislike having to submit a job and then keep hitting refresh to see when it completes. I'm wondering if the "old" information stored in EM is related to my DTS issue?

Thursday, March 29, 2012

Blog Archive

About Me