Thursday, March 29, 2012
DTS Simple Question
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
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
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
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
|||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:
[vbcol=seagreen]
> 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:
|||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:
[vbcol=seagreen]
> 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:
|||Thanks a lot. That was a great help to me.
"cathedr@.wa.state.gov" wrote:
[vbcol=seagreen]
> 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:
DTS shedule error
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
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 Hel
p
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 guessin
g
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: Erro
r:
> -2147220421 (8004043B); Provider Error: 0 (0) Error string: The tas
k
> reported failure on execution. Error source: Microsoft Data
> Transformation Services (DTS) Package Help file: sqldts80.hlp H
elp
> context: 1100 DTSRun OnFinish: DTSStep_DTSActiveScriptTask_1 DTSR
un
> OnStart: DTSStep_DTSExecuteSQLTask_2 DTSRun OnFinish:
> DTSStep_DTSExecuteSQLTask_2 DTSRun: Package execution complete. Proces
s
> 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:
[vbcol=seagreen]
> When you execute the package manually it is runs with your security
> privileges. When you schedule it the user is OGEWS140\SYSTEM. I am guess
ing
> that your system does not have privileges to do what you want.
> "Jac" wrote:
>|||In Enterprise Manager, expand the server you are working on. Then expand th
e
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:
[vbcol=seagreen]
> Hey,
> I am not very well in security. So, how can I give the user OGEWS140\SYSTE
M
> all the security rights so he can execute a sheduled DTS
> tkx,
> Jac
> "cathedr@.wa.state.gov" wrote:
>|||Thanks a lot. That was a great help to me.
"cathedr@.wa.state.gov" wrote:
[vbcol=seagreen]
> 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 ne
ed
> 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 th
e
> server specifically for this purpose. If any of your jobs or packages wil
l
> need to perform administrator tasks, you should give the account appropria
te
> privileges on both the server and SQL server. The account will also need
> privileges to any databases, procedures, etc. If your jobs or packages ne
ed
> 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:
>sqlsql
DTS services
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
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
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
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.
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
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
sqlsql
DTS Scripts
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
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 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
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 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+IG1lPw0KPiANCj4gsqlsql
DTS Scheduling Issue
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
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
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
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
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
>sqlsql