Hello,
I scheduled a dts package and it created automatically a job but the job fails with following error :
(reason: The system cannot find the file specified).
dts package is connecting the databases which exist on the server and I created this dts package on the same server as user sa.
I don't use a file as a data source or for something else in the package.
Server is SQL 2000 with SP3 and operating system is win2k.
I hope there is someone who has an idea...
elifElif:
What EXACTLY is you package doing? In other words, are you using ActiveX + VBS to create and Run the job?
RobbieD|||My package is copying a database into another one and changing some characters in some fields. I added a vb code to transformations screen for this.
The package works fine when executed. Problem is scheduling. I want to execute it at night because the people are working one of the databases which dts uses.
If I can not schedule this I have to schedule my own night or weekend. This is ridiculous.|||...SO you have already right-clicked the package in Enterprise Manager, selected Schedule Package - but that doesn't work, right?|||Originally posted by robbied111
...SO you have already right-clicked the package in Enterprise Manager, selected Schedule Package - but that doesn't work, right?
It worked and created the job. The problem is the "job". That does not work.|||I recall our DBA having the same issue:
I believe that the job may need to be scheduled directly on the server with the logged in user having admin priviledges on the server. It had less to do with SQL Server but was an effect of Windows 2000.
I may be offbase but that is what he found.
Originally posted by eguven
It worked and created the job. The problem is the "job". That does not work.|||The weather was stormy on friday, so we did not work.
Thanks for responses.
I'm working directly on the server and I'm user 'sa'.
I also prepared and scheduled the dts package on the server.
And this job does not work.
I have read something on the msdn related the user priviliges problem with this issue. That's why I did everything on the server as they said.
There is something else in my problem. I don't really understand.|||Dear friends,
I solved by myself this crazy problem. I wanted to share solution with you. When I scheduled the dts package, a job was created automatically. But it uses DTSRUN utility to run the package. This utility can only be run in the "c:\Program Files\Microsoft SQL Server\80\Tools\Binn " directory. I don't know why... But there is no path information in the job definition. That's why I recevied "File not found" message. I added this path to job and it worked. No need to be smart when working with a microft. We must just think simple.
bye
elif|||You might have removed it from the path variable while making some config settings ... otherwise it is automatically added when you install sql server .. You can view and set the path variables in System properties .. Advanced Tab ... Environment Variables ... No need to blame MS for your own fault. :)
Showing posts with label automatically. Show all posts
Showing posts with label automatically. Show all posts
Thursday, March 22, 2012
Friday, February 24, 2012
DTS launch from SQL Server Agent
Hi,
I want to build a task that does 3 things automatically at a fixed time interval:
1) Refresh the data (copy newly inserted data from a datamart to a datawarehouse)
2) Run a program (exe) to produce some external reports
3) Refresh an OLAP Cube with the new data so that users can access it
Basically, using SQL Server Agent, setting up steps 1 and 2 was pretty straightforward (for step 1, the main point of entry is a single sp).
I ran into some trouble figuring the easiest way to complete step 3. I created a DTS package with one object that updates the Cude on my server. This seems to be working fine. Then, I created step 3 in Agent and chose 'CmdExec' as the type of command. I then referenced the external exe 'dtsrun' to execute my DTS package.
Everything seems fine but I'm not sure I used the "best" way of doing things. Since the SQL Server Agent and DTS manager are both SQL Server tools (so is the Analysis services), I'm wondering if there wasn't an easier (thus more robust) way of completing my 3rd step?
Anyone? How is this done where you've worked?
Thanks,
Skip.Well, that is the technique that MSDN (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_dts_pkgmng_9vck.asp) suggests.
You could always make the DTS Package into a SQL Agent job (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_dts_pkgmng_71v2.asp), then run the job using sp_start_job (http://msdn.microsoft.com/library/en-us/tsqlref/ts_sp_sa-sz_11uq.asp) (but that is deviant).
-PatP|||As always I suggest this kBA http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q269074 to help DTS to schedule as a job.
I want to build a task that does 3 things automatically at a fixed time interval:
1) Refresh the data (copy newly inserted data from a datamart to a datawarehouse)
2) Run a program (exe) to produce some external reports
3) Refresh an OLAP Cube with the new data so that users can access it
Basically, using SQL Server Agent, setting up steps 1 and 2 was pretty straightforward (for step 1, the main point of entry is a single sp).
I ran into some trouble figuring the easiest way to complete step 3. I created a DTS package with one object that updates the Cude on my server. This seems to be working fine. Then, I created step 3 in Agent and chose 'CmdExec' as the type of command. I then referenced the external exe 'dtsrun' to execute my DTS package.
Everything seems fine but I'm not sure I used the "best" way of doing things. Since the SQL Server Agent and DTS manager are both SQL Server tools (so is the Analysis services), I'm wondering if there wasn't an easier (thus more robust) way of completing my 3rd step?
Anyone? How is this done where you've worked?
Thanks,
Skip.Well, that is the technique that MSDN (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_dts_pkgmng_9vck.asp) suggests.
You could always make the DTS Package into a SQL Agent job (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_dts_pkgmng_71v2.asp), then run the job using sp_start_job (http://msdn.microsoft.com/library/en-us/tsqlref/ts_sp_sa-sz_11uq.asp) (but that is deviant).
-PatP|||As always I suggest this kBA http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q269074 to help DTS to schedule as a job.
DTS job failing execution when scheduled, works fine manually.
My DTS Package work fine if I Execute it manually, but I need to do it automatically just after midnight. I defined my schedule and made sure the job was present in the SQL Server Agent>Jobs, but it fails and the Job History shows the following error:
DTSRun: Loading... DTSRun: Executing... DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005) Error string: [Microsoft][ODBC Microsoft Access Driver] Cannot start your application. The workgroup information file is missing or opened exclusively by another user. Error source: Microsoft OLE DB Provider for ODBC Drivers Help file: Help context: 0 Error Detail Records: Error: -2147467259 (80004005); Provider Error: 1901 (76D) Error string: [Microsoft][ODBC Microsoft Access Driver] Cannot start your application. The workgroup information file is missing or opened exclusively by another user. Error source: Microsoft OLE DB Provider for ODBC Drivers Help file: Help context: 0 DTSRun OnFinish: DTSStep_DTSDataPumpTask_1 DTSRun: Package execution complete. Process Exit Code 1. The step failed.
Help!!!Whenever I've had these types of issues, they had to do with the sqlserveragent not having permissions to a particular resource. You might want to start looking in that direction.|||I guess you are tired of getting up at midnight :-)
Does this help at all?PRB: Need to Map to Default Admin Account and Use NULL for Password In Order to Query Linked Server to Access Database.
Terri|||I figured it out, I was using a mapped drive and apparently NT Services do not recognize them. I used the UNC path in my DSN connection and all is well.
Thanks for the replies.
DTS job error
Hi,
I scheduled a DTS, with right click above it, for every 2
hours. A job is automatically created to control the
execution that a specified. I save the DTS within SQL
Server with dba user. If i change DBA password, my job
execution reports an error saying, login failed for user
dba ..., i don't have this user within any step regarding
the DTS, why this happens ? if i delete the job and
recreate it using right click above the DTS, then the job
start working fine. Any ideas ?
Thanks a lot
Mike
I would guess that you may have scheduled the packages using
the DTS schedule package functionality - you right click on
the package on select Schedule Package. In this case, the
job will create an encrypted string for the dtsrun utility -
the command in the job step looks like:
DTSRUN /~Zxxxxxxxxxxxx.
The login and password that is encrypted in this string is
based upon what login and password was used in Enterprise
Manager to register the server. If you change the password
that was used when scheduling the package, the password
embedded in the encrypted string is no longer valid.
You can either change the dtsrun commands in the jobs, just
entering the dtsrun parameters yourself.
Or you can use the dtsrunui utility to generate a new
encrypted command. From the advanced button on the dtsrunui
utility, there is an option to generate a command line. It
will be based on whatever login you use when the dtsrunui
utility starts.
-Sue
On Thu, 1 Apr 2004 06:48:24 -0800, "Mike"
<anonymous@.discussions.microsoft.com> wrote:
>Hi,
>I scheduled a DTS, with right click above it, for every 2
>hours. A job is automatically created to control the
>execution that a specified. I save the DTS within SQL
>Server with dba user. If i change DBA password, my job
>execution reports an error saying, login failed for user
>dba ..., i don't have this user within any step regarding
>the DTS, why this happens ? if i delete the job and
>recreate it using right click above the DTS, then the job
>start working fine. Any ideas ?
>Thanks a lot
>Mike
|||I guess you are saving the package in msdb.
It uses the username and password to load the package not to run it.
You can create the job yourself and code the dtsrun command so that you can change it. It means that the password will be in clear though.
I prefer to save packages to storage files and load them from there - it nmakes them easier to handle.
|||Thanks a lot Sue, that makes sense, but if i edit EM
properties and specify other user than the user that i
used to save the package, for instance sa, i get the error
login failed for user sa ... ?? This is strange. I've
got errors for all the users i used.
Any idea ?
>--Original Message--
>I would guess that you may have scheduled the packages
using
>the DTS schedule package functionality - you right click
on
>the package on select Schedule Package. In this case, the
>job will create an encrypted string for the dtsrun
utility -
>the command in the job step looks like:
>DTSRUN /~Zxxxxxxxxxxxx.
>The login and password that is encrypted in this string is
>based upon what login and password was used in Enterprise
>Manager to register the server. If you change the password
>that was used when scheduling the package, the password
>embedded in the encrypted string is no longer valid.
>You can either change the dtsrun commands in the jobs,
just
>entering the dtsrun parameters yourself.
>Or you can use the dtsrunui utility to generate a new
>encrypted command. From the advanced button on the
dtsrunui
>utility, there is an option to generate a command line. It
>will be based on whatever login you use when the dtsrunui
>utility starts.
>-Sue
>On Thu, 1 Apr 2004 06:48:24 -0800, "Mike"
><anonymous@.discussions.microsoft.com> wrote:
2
regarding
job
>.
>
|||Sorry, the login failed is for dba
you and Nigel are
right, sql server use dba for load the DTS under msdb
database and then fails when i change the password.
Thanks a lot
>--Original Message--
>Thanks a lot Sue, that makes sense, but if i edit EM
>properties and specify other user than the user that i
>used to save the package, for instance sa, i get the
error
>login failed for user sa ... ?? This is strange.
I've
>got errors for all the users i used.
>Any idea ?
>
>using
>on
>utility -
is
password
>just
>dtsrunui
It
>2
user
>regarding
>job
>.
>
I scheduled a DTS, with right click above it, for every 2
hours. A job is automatically created to control the
execution that a specified. I save the DTS within SQL
Server with dba user. If i change DBA password, my job
execution reports an error saying, login failed for user
dba ..., i don't have this user within any step regarding
the DTS, why this happens ? if i delete the job and
recreate it using right click above the DTS, then the job
start working fine. Any ideas ?
Thanks a lot
Mike
I would guess that you may have scheduled the packages using
the DTS schedule package functionality - you right click on
the package on select Schedule Package. In this case, the
job will create an encrypted string for the dtsrun utility -
the command in the job step looks like:
DTSRUN /~Zxxxxxxxxxxxx.
The login and password that is encrypted in this string is
based upon what login and password was used in Enterprise
Manager to register the server. If you change the password
that was used when scheduling the package, the password
embedded in the encrypted string is no longer valid.
You can either change the dtsrun commands in the jobs, just
entering the dtsrun parameters yourself.
Or you can use the dtsrunui utility to generate a new
encrypted command. From the advanced button on the dtsrunui
utility, there is an option to generate a command line. It
will be based on whatever login you use when the dtsrunui
utility starts.
-Sue
On Thu, 1 Apr 2004 06:48:24 -0800, "Mike"
<anonymous@.discussions.microsoft.com> wrote:
>Hi,
>I scheduled a DTS, with right click above it, for every 2
>hours. A job is automatically created to control the
>execution that a specified. I save the DTS within SQL
>Server with dba user. If i change DBA password, my job
>execution reports an error saying, login failed for user
>dba ..., i don't have this user within any step regarding
>the DTS, why this happens ? if i delete the job and
>recreate it using right click above the DTS, then the job
>start working fine. Any ideas ?
>Thanks a lot
>Mike
|||I guess you are saving the package in msdb.
It uses the username and password to load the package not to run it.
You can create the job yourself and code the dtsrun command so that you can change it. It means that the password will be in clear though.
I prefer to save packages to storage files and load them from there - it nmakes them easier to handle.
|||Thanks a lot Sue, that makes sense, but if i edit EM
properties and specify other user than the user that i
used to save the package, for instance sa, i get the error
login failed for user sa ... ?? This is strange. I've
got errors for all the users i used.
Any idea ?
>--Original Message--
>I would guess that you may have scheduled the packages
using
>the DTS schedule package functionality - you right click
on
>the package on select Schedule Package. In this case, the
>job will create an encrypted string for the dtsrun
utility -
>the command in the job step looks like:
>DTSRUN /~Zxxxxxxxxxxxx.
>The login and password that is encrypted in this string is
>based upon what login and password was used in Enterprise
>Manager to register the server. If you change the password
>that was used when scheduling the package, the password
>embedded in the encrypted string is no longer valid.
>You can either change the dtsrun commands in the jobs,
just
>entering the dtsrun parameters yourself.
>Or you can use the dtsrunui utility to generate a new
>encrypted command. From the advanced button on the
dtsrunui
>utility, there is an option to generate a command line. It
>will be based on whatever login you use when the dtsrunui
>utility starts.
>-Sue
>On Thu, 1 Apr 2004 06:48:24 -0800, "Mike"
><anonymous@.discussions.microsoft.com> wrote:
2
regarding
job
>.
>
|||Sorry, the login failed is for dba
you and Nigel areright, sql server use dba for load the DTS under msdb
database and then fails when i change the password.
Thanks a lot
>--Original Message--
>Thanks a lot Sue, that makes sense, but if i edit EM
>properties and specify other user than the user that i
>used to save the package, for instance sa, i get the
error
>login failed for user sa ... ?? This is strange.
I've
>got errors for all the users i used.
>Any idea ?
>
>using
>on
>utility -
is
password
>just
>dtsrunui
It
>2
user
>regarding
>job
>.
>
DTS job error
Hi,
I scheduled a DTS, with right click above it, for every 2
hours. A job is automatically created to control the
execution that a specified. I save the DTS within SQL
Server with dba user. If i change DBA password, my job
execution reports an error saying, login failed for user
dba ..., i don't have this user within any step regarding
the DTS, why this happens ? if i delete the job and
recreate it using right click above the DTS, then the job
start working fine. Any ideas '
Thanks a lot
MikeI would guess that you may have scheduled the packages using
the DTS schedule package functionality - you right click on
the package on select Schedule Package. In this case, the
job will create an encrypted string for the dtsrun utility -
the command in the job step looks like:
DTSRUN /~Zxxxxxxxxxxxx.
The login and password that is encrypted in this string is
based upon what login and password was used in Enterprise
Manager to register the server. If you change the password
that was used when scheduling the package, the password
embedded in the encrypted string is no longer valid.
You can either change the dtsrun commands in the jobs, just
entering the dtsrun parameters yourself.
Or you can use the dtsrunui utility to generate a new
encrypted command. From the advanced button on the dtsrunui
utility, there is an option to generate a command line. It
will be based on whatever login you use when the dtsrunui
utility starts.
-Sue
On Thu, 1 Apr 2004 06:48:24 -0800, "Mike"
<anonymous@.discussions.microsoft.com> wrote:
>Hi,
>I scheduled a DTS, with right click above it, for every 2
>hours. A job is automatically created to control the
>execution that a specified. I save the DTS within SQL
>Server with dba user. If i change DBA password, my job
>execution reports an error saying, login failed for user
>dba ..., i don't have this user within any step regarding
>the DTS, why this happens ? if i delete the job and
>recreate it using right click above the DTS, then the job
>start working fine. Any ideas '
>Thanks a lot
>Mike|||I guess you are saving the package in msdb.
It uses the username and password to load the package not to run it.
You can create the job yourself and code the dtsrun command so that you can
change it. It means that the password will be in clear though.
I prefer to save packages to storage files and load them from there - it nma
kes them easier to handle.|||Thanks a lot Sue, that makes sense, but if i edit EM
properties and specify other user than the user that i
used to save the package, for instance sa, i get the error
login failed for user sa ... '? This is strange. I've
got errors for all the users i used.
Any idea ?
>--Original Message--
>I would guess that you may have scheduled the packages
using
>the DTS schedule package functionality - you right click
on
>the package on select Schedule Package. In this case, the
>job will create an encrypted string for the dtsrun
utility -
>the command in the job step looks like:
>DTSRUN /~Zxxxxxxxxxxxx.
>The login and password that is encrypted in this string is
>based upon what login and password was used in Enterprise
>Manager to register the server. If you change the password
>that was used when scheduling the package, the password
>embedded in the encrypted string is no longer valid.
>You can either change the dtsrun commands in the jobs,
just
>entering the dtsrun parameters yourself.
>Or you can use the dtsrunui utility to generate a new
>encrypted command. From the advanced button on the
dtsrunui
>utility, there is an option to generate a command line. It
>will be based on whatever login you use when the dtsrunui
>utility starts.
>-Sue
>On Thu, 1 Apr 2004 06:48:24 -0800, "Mike"
><anonymous@.discussions.microsoft.com> wrote:
>
2
regarding
job
>.
>|||Sorry, the login failed is for dba you and Nigel are
right, sql server use dba for load the DTS under msdb
database and then fails when i change the password.
Thanks a lot
>--Original Message--
>Thanks a lot Sue, that makes sense, but if i edit EM
>properties and specify other user than the user that i
>used to save the package, for instance sa, i get the
error
>login failed for user sa ... '? This is strange.
I've
>got errors for all the users i used.
>Any idea ?
>
>using
>on
>utility -
is
password
>just
>dtsrunui
It
>2
user
>regarding
>job
>.
>
I scheduled a DTS, with right click above it, for every 2
hours. A job is automatically created to control the
execution that a specified. I save the DTS within SQL
Server with dba user. If i change DBA password, my job
execution reports an error saying, login failed for user
dba ..., i don't have this user within any step regarding
the DTS, why this happens ? if i delete the job and
recreate it using right click above the DTS, then the job
start working fine. Any ideas '
Thanks a lot
MikeI would guess that you may have scheduled the packages using
the DTS schedule package functionality - you right click on
the package on select Schedule Package. In this case, the
job will create an encrypted string for the dtsrun utility -
the command in the job step looks like:
DTSRUN /~Zxxxxxxxxxxxx.
The login and password that is encrypted in this string is
based upon what login and password was used in Enterprise
Manager to register the server. If you change the password
that was used when scheduling the package, the password
embedded in the encrypted string is no longer valid.
You can either change the dtsrun commands in the jobs, just
entering the dtsrun parameters yourself.
Or you can use the dtsrunui utility to generate a new
encrypted command. From the advanced button on the dtsrunui
utility, there is an option to generate a command line. It
will be based on whatever login you use when the dtsrunui
utility starts.
-Sue
On Thu, 1 Apr 2004 06:48:24 -0800, "Mike"
<anonymous@.discussions.microsoft.com> wrote:
>Hi,
>I scheduled a DTS, with right click above it, for every 2
>hours. A job is automatically created to control the
>execution that a specified. I save the DTS within SQL
>Server with dba user. If i change DBA password, my job
>execution reports an error saying, login failed for user
>dba ..., i don't have this user within any step regarding
>the DTS, why this happens ? if i delete the job and
>recreate it using right click above the DTS, then the job
>start working fine. Any ideas '
>Thanks a lot
>Mike|||I guess you are saving the package in msdb.
It uses the username and password to load the package not to run it.
You can create the job yourself and code the dtsrun command so that you can
change it. It means that the password will be in clear though.
I prefer to save packages to storage files and load them from there - it nma
kes them easier to handle.|||Thanks a lot Sue, that makes sense, but if i edit EM
properties and specify other user than the user that i
used to save the package, for instance sa, i get the error
login failed for user sa ... '? This is strange. I've
got errors for all the users i used.
Any idea ?
>--Original Message--
>I would guess that you may have scheduled the packages
using
>the DTS schedule package functionality - you right click
on
>the package on select Schedule Package. In this case, the
>job will create an encrypted string for the dtsrun
utility -
>the command in the job step looks like:
>DTSRUN /~Zxxxxxxxxxxxx.
>The login and password that is encrypted in this string is
>based upon what login and password was used in Enterprise
>Manager to register the server. If you change the password
>that was used when scheduling the package, the password
>embedded in the encrypted string is no longer valid.
>You can either change the dtsrun commands in the jobs,
just
>entering the dtsrun parameters yourself.
>Or you can use the dtsrunui utility to generate a new
>encrypted command. From the advanced button on the
dtsrunui
>utility, there is an option to generate a command line. It
>will be based on whatever login you use when the dtsrunui
>utility starts.
>-Sue
>On Thu, 1 Apr 2004 06:48:24 -0800, "Mike"
><anonymous@.discussions.microsoft.com> wrote:
>
2
regarding
job
>.
>|||Sorry, the login failed is for dba
you and Nigel areright, sql server use dba for load the DTS under msdb
database and then fails when i change the password.
Thanks a lot
>--Original Message--
>Thanks a lot Sue, that makes sense, but if i edit EM
>properties and specify other user than the user that i
>used to save the package, for instance sa, i get the
error
>login failed for user sa ... '? This is strange.
I've
>got errors for all the users i used.
>Any idea ?
>
>using
>on
>utility -
is
password
>just
>dtsrunui
It
>2
user
>regarding
>job
>.
>
Friday, February 17, 2012
dts import text file -- max char per line 352?
I am trying to import a text file into my table.
However, the max varchar length is automatically set to 352.
It needs to be longer. Even after manually changing it, sure enough it truncates after 352.
(We put the first 16 characters into one field, and then try to get the next 800, but it only allows 352)
Any ideas?
`LeThe VARCHAR() datatype can be up to 8000 characters long. If you need more than that, you have my condolences and you need to resort to a TEXT column instead.
I'm not sure why your data is only importing 352 characters. 255 is a common problem, where people don't reset the maximum column width supported by Query Analyzer from the default value. I don't think I've ever seen 352 as a limit.
-PatP|||Don't have answer to question but Pat, how do you modify the max column width in QA to > 255 characters ?
Cheers|||Hit Shift-Ctrl-o, click the results tab, near dead center.
-PatP|||Pat
Thx that's really cool !
However, the max varchar length is automatically set to 352.
It needs to be longer. Even after manually changing it, sure enough it truncates after 352.
(We put the first 16 characters into one field, and then try to get the next 800, but it only allows 352)
Any ideas?
`LeThe VARCHAR() datatype can be up to 8000 characters long. If you need more than that, you have my condolences and you need to resort to a TEXT column instead.
I'm not sure why your data is only importing 352 characters. 255 is a common problem, where people don't reset the maximum column width supported by Query Analyzer from the default value. I don't think I've ever seen 352 as a limit.
-PatP|||Don't have answer to question but Pat, how do you modify the max column width in QA to > 255 characters ?
Cheers|||Hit Shift-Ctrl-o, click the results tab, near dead center.
-PatP|||Pat
Thx that's really cool !
Subscribe to:
Posts (Atom)