DTS Remote Scheduling

Hello all,
I am moving data from sql to sql.
I have created my packages (structured storage files) via my computer
these packages are stored on the sql srever and I access them remotely
via my own computer.
My question is
1. How can I also REMOTELY schedule these packages or create jobs s
that these packages run out of office hours.
This is what I tried but it failed to work...
I open enterprise manager and clicked on to the server in question
I then clicked on 'MANAGEMENT' and 'JOBS'
I right clicked 'jobs' and clicked 'NEW JOB'
I then completed the four tabs (general, step, schedules
notifications).
After the package was supposed to have run, I viewed the job histor
and found out that it failed.
is there a way to be able to schedule a package (structured storag
file) from another computer
--
newomog
----
newomoge's Profile: http://www.msusenet.com/member.php?userid=253
View this thread: http://www.msusenet.com/t-187055672Why did it fail? The general reason that a DTS package will work when you
run it but fail when scheduled is due to permissions, specifically because
when run by a job (assuming the job is owned by a sysadmin) it will run as
the SQL Agent Service Account. Have a look at
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)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"newomoge" <newomoge.1r1e0s@.no-mx.msusenet.com> wrote in message
news:newomoge.1r1e0s@.no-mx.msusenet.com...
> Hello all,
> I am moving data from sql to sql.
> I have created my packages (structured storage files) via my computer.
> these packages are stored on the sql srever and I access them remotely
> via my own computer.
> My question is
> 1. How can I also REMOTELY schedule these packages or create jobs so
> that these packages run out of office hours.
> This is what I tried but it failed to work...
> I open enterprise manager and clicked on to the server in question
> I then clicked on 'MANAGEMENT' and 'JOBS'
> I right clicked 'jobs' and clicked 'NEW JOB'
> I then completed the four tabs (general, step, schedules,
> notifications).
> After the package was supposed to have run, I viewed the job history
> and found out that it failed.
> is there a way to be able to schedule a package (structured storage
> file) from another computer?
>
> --
> newomoge
> ---
> newomoge's Profile: http://www.msusenet.com/member.php?userid=2530
> View this thread: http://www.msusenet.com/t-1870556724
>sqlsql

DTS Remote Scheduling

Hello all,
I am moving data from sql to sql.
I have created my packages (structured storage files) via my computer.
these packages are stored on the sql srever and I access them remotely
via my own computer.
My question is
1. How can I also REMOTELY schedule these packages or create jobs so
that these packages run out of office hours.
This is what I tried but it failed to work...
I open enterprise manager and clicked on to the server in question
I then clicked on 'MANAGEMENT' and 'JOBS'
I right clicked 'jobs' and clicked 'NEW JOB'
I then completed the four tabs (general, step, schedules,
notifications).
After the package was supposed to have run, I viewed the job history
and found out that it failed.
is there a way to be able to schedule a package (structured storage
file) from another computer?
newomoge
newomoge's Profile: http://www.mswebservertalk.com/member.php?userid=2530
View this thread: http://www.mswebservertalk.com/t-1870556724
Why did it fail? The general reason that a DTS package will work when you
run it but fail when scheduled is due to permissions, specifically because
when run by a job (assuming the job is owned by a sysadmin) it will run as
the SQL Agent Service Account. Have a look at
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
"newomoge" <newomoge.1r1e0s@.no-mx.mswebservertalk.com> wrote in message
news:newomoge.1r1e0s@.no-mx.mswebservertalk.com...
> Hello all,
> I am moving data from sql to sql.
> I have created my packages (structured storage files) via my computer.
> these packages are stored on the sql srever and I access them remotely
> via my own computer.
> My question is
> 1. How can I also REMOTELY schedule these packages or create jobs so
> that these packages run out of office hours.
> This is what I tried but it failed to work...
> I open enterprise manager and clicked on to the server in question
> I then clicked on 'MANAGEMENT' and 'JOBS'
> I right clicked 'jobs' and clicked 'NEW JOB'
> I then completed the four tabs (general, step, schedules,
> notifications).
> After the package was supposed to have run, I viewed the job history
> and found out that it failed.
> is there a way to be able to schedule a package (structured storage
> file) from another computer?
>
> --
> newomoge
> newomoge's Profile: http://www.mswebservertalk.com/member.php?userid=2530
> View this thread: http://www.mswebservertalk.com/t-1870556724
>

DTS Remote Scheduling

Hello all,
I am moving data from sql to sql.
I have created my packages (structured storage files) via my computer.
these packages are stored on the sql srever and I access them remotely
via my own computer.
My question is
1. How can I also REMOTELY schedule these packages or create jobs so
that these packages run out of office hours.
This is what I tried but it failed to work...
I open enterprise manager and clicked on to the server in question
I then clicked on 'MANAGEMENT' and 'JOBS'
I right clicked 'jobs' and clicked 'NEW JOB'
I then completed the four tabs (general, step, schedules,
notifications).
After the package was supposed to have run, I viewed the job history
and found out that it failed.
is there a way to be able to schedule a package (structured storage
file) from another computer?
newomoge
---
newomoge's Profile: http://www.msmcse.ms/member.php?userid=2530
View this thread: http://www.msmcse.ms/t-1870556724Why did it fail? The general reason that a DTS package will work when you
run it but fail when scheduled is due to permissions, specifically because
when run by a job (assuming the job is owned by a sysadmin) it will run as
the SQL Agent Service Account. Have a look at
INF: How to Run a DTS Package as a Scheduled Job
http://support.microsoft.com/defaul...b;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
"newomoge" <newomoge.1r1e0s@.no-mx.msmcse.ms> wrote in message
news:newomoge.1r1e0s@.no-mx.msmcse.ms...
> Hello all,
> I am moving data from sql to sql.
> I have created my packages (structured storage files) via my computer.
> these packages are stored on the sql srever and I access them remotely
> via my own computer.
> My question is
> 1. How can I also REMOTELY schedule these packages or create jobs so
> that these packages run out of office hours.
> This is what I tried but it failed to work...
> I open enterprise manager and clicked on to the server in question
> I then clicked on 'MANAGEMENT' and 'JOBS'
> I right clicked 'jobs' and clicked 'NEW JOB'
> I then completed the four tabs (general, step, schedules,
> notifications).
> After the package was supposed to have run, I viewed the job history
> and found out that it failed.
> is there a way to be able to schedule a package (structured storage
> file) from another computer?
>
> --
> newomoge
> ---
> newomoge's Profile: http://www.msmcse.ms/member.php?userid=2530
> View this thread: http://www.msmcse.ms/t-1870556724
>

DTS Packages

Hi ,
I have some DTS packages to transfer data from text files on different
computers to SQL server tavles. Now I need information about these package
name, the source of the text files and output tables, I want to have table t
o
hole all these data. I am sure there is a way to do it. I looked
MSDB.sysTDSpackages and other related tables, but I still don't get the data
I need , any one here knows where are the DTS source and destinition
information stored?
Thanks .This isnt exactly what you're looking for but...
Save a Package as a Visual Basic File. Then have some program that reads
that file and grabs the info you need.
"Matthew Z" <MatthewZ@.discussions.microsoft.com> wrote in message
news:36F6A628-4931-4A56-AAEB-EB74B70386CB@.microsoft.com...
> Hi ,
> I have some DTS packages to transfer data from text files on different
> computers to SQL server tavles. Now I need information about these package
> name, the source of the text files and output tables, I want to have table
> to
> hole all these data. I am sure there is a way to do it. I looked
> MSDB.sysTDSpackages and other related tables, but I still don't get the
> data
> I need , any one here knows where are the DTS source and destinition
> information stored?
> Thanks .sqlsql

DTS Packages

Hi ,
I have some DTS packages to transfer data from text files on different
computers to SQL server tavles. Now I need information about these package
name, the source of the text files and output tables, I want to have table to
hole all these data. I am sure there is a way to do it. I looked
MSDB.sysTDSpackages and other related tables, but I still don't get the data
I need , any one here knows where are the DTS source and destinition
information stored?
Thanks .
This isnt exactly what you're looking for but...
Save a Package as a Visual Basic File. Then have some program that reads
that file and grabs the info you need.
"Matthew Z" <MatthewZ@.discussions.microsoft.com> wrote in message
news:36F6A628-4931-4A56-AAEB-EB74B70386CB@.microsoft.com...
> Hi ,
> I have some DTS packages to transfer data from text files on different
> computers to SQL server tavles. Now I need information about these package
> name, the source of the text files and output tables, I want to have table
> to
> hole all these data. I am sure there is a way to do it. I looked
> MSDB.sysTDSpackages and other related tables, but I still don't get the
> data
> I need , any one here knows where are the DTS source and destinition
> information stored?
> Thanks .

DTS Package: Data Loading ?

Hi,
Can someone help me with the following
I have to automate the process of loading the files from source to target.
I have to use partitioned tables which should be created automatically to
load the data. These tables should have different name each week(for e.g
customers40, customers48 etc)
Using Northwind Example for Customers table
The syntax is
declare @.tablestmt nvarchar(2555)
set @.tablestmt= 'create table customers'+ convert(char(8),datepart(wk,
getdate()),112)+'([CustomerID] [nchar] (5) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CompanyName] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ContactName] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ContactTitle] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Address] [nvarchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[City] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Region] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PostalCode] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Country] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Phone] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Fax] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)'
exec sp_executesql @.tablestmt
The problem is each week I get a file with different name. Using Nothwind
Example. Suppose I get a file CustomersA1. Next week I get the file with
the name CustomersA8. This file that I get each week has to go in a table
that is created at run time with different name(as described above). How do I
tell DTS that the file name changes every week. What tasks should I use.
All the task have specified path for the filenames. How do I tell the task
that filename changes every week.
Any example with syntax(for Northwind/Customers in this case) will be a
great help.
Thanks
Steve
Hi Steve,
well the only way I see is to use dynamic properties. Define a global
variable, assign the global to the specific data transformation task and use
a ActiveX task at the beginning of your packge to calculate the name of your
import table and store it in the global variable.
Regards,
Meinhard
"Steve" <Steve@.discussions.microsoft.com> schrieb im Newsbeitrag
news:20AE8726-8E58-4373-BF88-D06736C8FBAF@.microsoft.com...
> Hi,
> Can someone help me with the following
> I have to automate the process of loading the files from source to target.
> I have to use partitioned tables which should be created automatically to
> load the data. These tables should have different name each week(for e.g
> customers40, customers48 etc)
> Using Northwind Example for Customers table
> The syntax is
> declare @.tablestmt nvarchar(2555)
> set @.tablestmt= 'create table customers'+ convert(char(8),datepart(wk,
> getdate()),112)+'([CustomerID] [nchar] (5) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [CompanyName] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [ContactName] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ContactTitle] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Address] [nvarchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [City] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Region] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [PostalCode] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Country] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Phone] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Fax] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> )'
> exec sp_executesql @.tablestmt
>
> The problem is each week I get a file with different name. Using Nothwind
> Example. Suppose I get a file CustomersA1. Next week I get the file with
> the name CustomersA8. This file that I get each week has to go in a table
> that is created at run time with different name(as described above). How
> do I
> tell DTS that the file name changes every week. What tasks should I use.
> All the task have specified path for the filenames. How do I tell the
> task
> that filename changes every week.
> Any example with syntax(for Northwind/Customers in this case) will be a
> great help.
>
> Thanks
> Steve

DTS Package: Data Loading ?

Hi,
Can someone help me with the following
I have to automate the process of loading the files from source to target.
I have to use partitioned tables which should be created automatically to
load the data. These tables should have different name each week(for e.g
customers40, customers48 etc)
Using Northwind Example for Customers table
The syntax is
declare @.tablestmt nvarchar(2555)
set @.tablestmt= 'create table customers'+ convert(char(8),datepart(wk,
getdate()),112)+'([CustomerID] [nchar] (5) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CompanyName] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS N
OT NULL ,
[ContactName] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS N
ULL ,
[ContactTitle] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Address] [nvarchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[City] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Region] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PostalCode] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NU
LL ,
[Country] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Phone] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Fax] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)'
exec sp_executesql @.tablestmt
The problem is each week I get a file with different name. Using Nothwind
Example. Suppose I get a file CustomersA1. Next week I get the file with
the name CustomersA8. This file that I get each week has to go in a table
that is created at run time with different name(as described above). How do
I
tell DTS that the file name changes every week. What tasks should I use.
All the task have specified path for the filenames. How do I tell the task
that filename changes every week.
Any example with syntax(for Northwind/Customers in this case) will be a
great help.
Thanks
SteveHi Steve,
well the only way I see is to use dynamic properties. Define a global
variable, assign the global to the specific data transformation task and use
a ActiveX task at the beginning of your packge to calculate the name of your
import table and store it in the global variable.
Regards,
Meinhard
"Steve" <Steve@.discussions.microsoft.com> schrieb im Newsbeitrag
news:20AE8726-8E58-4373-BF88-D06736C8FBAF@.microsoft.com...
> Hi,
> Can someone help me with the following
> I have to automate the process of loading the files from source to target.
> I have to use partitioned tables which should be created automatically to
> load the data. These tables should have different name each week(for e.g
> customers40, customers48 etc)
> Using Northwind Example for Customers table
> The syntax is
> declare @.tablestmt nvarchar(2555)
> set @.tablestmt= 'create table customers'+ convert(char(8),datepart(wk,
> getdate()),112)+'([CustomerID] [nchar] (5) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [CompanyName] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT
> NULL ,
> [ContactName] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
> [ContactTitle] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_A
S NULL ,
> [Address] [nvarchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NUL
L ,
> [City] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Region] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
> [PostalCode] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
> [Country] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NUL
L ,
> [Phone] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
> [Fax] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> )'
> exec sp_executesql @.tablestmt
>
> The problem is each week I get a file with different name. Using Nothwind
> Example. Suppose I get a file CustomersA1. Next week I get the file with
> the name CustomersA8. This file that I get each week has to go in a table
> that is created at run time with different name(as described above). How
> do I
> tell DTS that the file name changes every week. What tasks should I use.
> All the task have specified path for the filenames. How do I tell the
> task
> that filename changes every week.
> Any example with syntax(for Northwind/Customers in this case) will be a
> great help.
>
> Thanks
> Steve

dts package to check dynamic file name

Hi all,

I have created a DTS package in SQL Server that uses file system obj to list the .csv files in a folder then runs and imports it into database. The problem that I run into is - the names of the .csv files will be dynamic based on the date and at times the hour.

The question is how to check ( I believe using Active X) for a new file each day and even each hour?

A sample of what my file names are:

CVG_IDO_1_20070703.csv

CVG_IDO_2_20070703.csv

CVG_IDO_3_20070703.csv

CVG_IDO_1_20070704.csv

CVG_IDO_2_20070704.csv

CVG_IDO_3_20070704.csv

Thank you!

A bit hard to say...what constitutes a new file? Are you trying to process all files in the directory? Are those all considered "new files"?

The following article on sqldts.com may help you out, depending on what your needs are with this:

Looping, Importing, and Archiving - http://www.sqldts.com/246.aspx

-Sue

|||

i believe this is time triggered. the last part of the filename is obvoiusly the date

prior to that is a number followed by an underscore.

if this number designate the latest file then you can easily script it.

well that is if your only going to pull out the latest file.

DTS package Scheduled Job does not run correctly

Hi
I have a DTS package that is running a VB script that reads all the
files from a folder one by one and processes them. After a file is
processed - file is read and the content entered in a table in one of
the databases - the file is moved to a different location.
The DTS package runs wonderfully if I run it manually (right click
Execute Package).
I scheduled this DTS package to run every hour (right click Schedule
Package and a job was created).
The corresponding job finishes successfull, my files are even moved to
a different location but no data is entered in my table.
The SQL Agent runs under an account that is system admin for our
network.
The job is owned by the same account.
Please help.
Thanks,
CarlyAre you using workflow, in your DTS package. If you are trying to execute any
step after inserting data into table try to do it 'OnSuccess' workflow and
see if it gets executed successfully.
--
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and time
asking back if its 2000 or 2005]
"Carly" wrote:
> Hi
> I have a DTS package that is running a VB script that reads all the
> files from a folder one by one and processes them. After a file is
> processed - file is read and the content entered in a table in one of
> the databases - the file is moved to a different location.
> The DTS package runs wonderfully if I run it manually (right click
> Execute Package).
> I scheduled this DTS package to run every hour (right click Schedule
> Package and a job was created).
> The corresponding job finishes successfull, my files are even moved to
> a different location but no data is entered in my table.
> The SQL Agent runs under an account that is system admin for our
> network.
> The job is owned by the same account.
> Please help.
> Thanks,
> Carly
>

DTS Package problem?

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

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

What am I doing wrong?

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

Brad Feaker

Ex nihilo, nihil fit

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

DTS Package error

Hi,
I am using a DTS package to retrieve data from one set of tables /flat files and load into another set of tables. The package is scheduled to run daily. After the failur/success of every step I get an email notification.
But sometimes the package starts, i get the email notification for that ..and then the package never proceeds. Neither I get a failure message nor the success message for the next step. When i checked in the log files I got the attached file, with the error code
8004043B.

This package calls some other packages. But what I feel is that its not completing even the first step.Hence the child packages are not being called.

Please help!!!!Enable logging for this package, so that the error message will be recorded in the log file.

Try setting the "Execute on main package thread" option. Right-click the
task, Workflow Properties, Options tab.|||Are you calling an external VB executable through your DTS ?

DTS Locking up

I am importing some files using DTS on a new install of SQL 2000. It is
randomly locking up. It will run x rows then stops. At that point, I
cannot do anything with SQL Server. I cannot even stop and restart the
service.
2k3 Small Business Server
SQL 2000 SP3a
4GB Ram
2TB array (3ware 7506-8)
3.2GHz CPU
Any thoughts?
Thanks,
ScottYou can take a look at this kb and try to determine the cause:
http://support.microsoft.com/kb/298475
-oj
"Scott Cadreau" <scadreau@.aros.net> wrote in message
news:oImdnW3CQaq1mJHfRVn-1Q@.aros.net...
>I am importing some files using DTS on a new install of SQL 2000. It is
>randomly locking up. It will run x rows then stops. At that point, I
>cannot do anything with SQL Server. I cannot even stop and restart the
>service.
> 2k3 Small Business Server
> SQL 2000 SP3a
> 4GB Ram
> 2TB array (3ware 7506-8)
> 3.2GHz CPU
> Any thoughts?
> Thanks,
> Scott
>

DTS job fails when scheduled from SQL Agent

Folks,
I have a DTS job that imports data from text files (specified as odbc connections) from a remote server into a sql table on the same SQL server that the job has been created on.
The job runs fine if execute directly from the server. If I schedule the same job on the server (through jobs) executing under the same user, the job fails with..

Executed as user: mydomain\mylogin . ...art: DTSStep_DTSActiveScriptTask_1 DTSRun OnFinish: DTSStep_DTSActiveScriptTask_1 DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005) Error string: [Microsoft][ODBC Text Driver] '(unknown)' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides. Error source: Microsoft OLE DB Provider for ODBC Drivers Help file: Help context: 0 Error Detail Records: Error: -2147467259 (80004005); Provider Error: 1023 (3FF) Error string: Error source: Help file: Help context: 0 DTSRun OnFinish: DTSStep_DTSDataPumpTask_1 Error: -2147220440 (80040428); Provider Error: 0 (0) Error string: Package failed because Step 'DTSStep_DTSDataPumpTask_1' failed. Error source: Microsoft Data Transformation Services ... Process Exit Code 1. The step failed.

How come it loses the path to the file when I dont run it directly?
Cheers
MickDTS runs in the context of the client machine when you run it directly. That means that if you run it from Enterprise Manager on your local PC then it uses the settings, drive mappings and ODBC drivers of your workstation. When a DTS package is run by SQL Agent, it uses the settings from the Server. You have to ensure that the server has all the settings that your local machine does.

Be sure not to use mapped drives to specify file locations -- use UNC instead. This is because a mapped drive only exists in the context of a logged in user. SQL Agent is a service and thus is not logged in.

I hope this makes some sense; I still find this a difficult topic to explain clearly even after dealing with it for five years.

Regards,

hmscott|||Thanks for that,
Thing is I have done every step from package creation to scheduling ON the server itself through terminal services. I thought SQL Agent would be aware of these server-based system DSN's. Ill have a go at UNC then.
Cheers
ML|||http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q269074 - KBA to schedule DTS as a scheduled job and troubleshoot any issues.

HTH|||Thanks folks,
I used UNC text file sources instead of odbc text connections. Worked great.
Cheers
Mick

DTS in Cluster environment!

Are there issues one should be aware of using running DTS in Clustered
environment?
For example, i need DTS read data from flat files and update tables in a
database.
I assume its not required to have this package(s) run on both servers in an
ACTIVE - ACTIVE environment.
All opinions are welcome.
Thanks
Tunji
You should have no problems. The DTS will exist inside the MSDB database so it will be in both of the Clustered servers. The flat file you need to read though needs to be in a stable location. (one that does not change owners in the clustered environmen
t).
|||Thanks Jeff!
Tunji Ogundeji
mcdba, ocp
www.geniant.com
"Jeff Duncan" <jduncan@.gtefc.org> wrote in message
news:2C6CE066-C7D8-4888-897D-06765ADDE53B@.microsoft.com...
> You should have no problems. The DTS will exist inside the MSDB database
so it will be in both of the Clustered servers. The flat file you need to
read though needs to be in a stable location. (one that does not change
owners in the clustered environment).

DTS in Cluster environment!

Are there issues one should be aware of using running DTS in Clustered
environment?
For example, i need DTS read data from flat files and update tables in a
database.
I assume its not required to have this package(s) run on both servers in an
ACTIVE - ACTIVE environment.
All opinions are welcome.
Thanks
TunjiYou should have no problems. The DTS will exist inside the MSDB database so
it will be in both of the Clustered servers. The flat file you need to rea
d though needs to be in a stable location. (one that does not change owners
in the clustered environmen
t).|||Thanks Jeff!
Tunji Ogundeji
mcdba, ocp
www.geniant.com
"Jeff Duncan" <jduncan@.gtefc.org> wrote in message
news:2C6CE066-C7D8-4888-897D-06765ADDE53B@.microsoft.com...
> You should have no problems. The DTS will exist inside the MSDB database
so it will be in both of the Clustered servers. The flat file you need to
read though needs to be in a stable location. (one that does not change
owners in the clustered environment).

DTS in Cluster environment!

Are there issues one should be aware of using running DTS in Clustered
environment?
For example, i need DTS read data from flat files and update tables in a
database.
I assume its not required to have this package(s) run on both servers in an
ACTIVE - ACTIVE environment.
All opinions are welcome.
Thanks
--
TunjiYou should have no problems. The DTS will exist inside the MSDB database so it will be in both of the Clustered servers. The flat file you need to read though needs to be in a stable location. (one that does not change owners in the clustered environment).|||Thanks Jeff!
--
Tunji Ogundeji
mcdba, ocp
www.geniant.com
"Jeff Duncan" <jduncan@.gtefc.org> wrote in message
news:2C6CE066-C7D8-4888-897D-06765ADDE53B@.microsoft.com...
> You should have no problems. The DTS will exist inside the MSDB database
so it will be in both of the Clustered servers. The flat file you need to
read though needs to be in a stable location. (one that does not change
owners in the clustered environment).

DTS Import txt File looping.

Hello,
Here i my problem;
I have a number of files with all the exact same format and i need to import
all of them into the same table.
As i never faced the problem before im thinking
to created a txt source and and sql server source with a data transfrom
between them. I want to change the the txt source connection "File Name"
properties for each file I need to import and loop until im done importing
all my files.(FYY: I hold all the table names in a sql table that I refreash
before importing)
So my question are ;
Is this the best approch to solve the issue with i data provided above? how
(high level - unless u want to give me the code ;-) )
or is there other ways that are better then this?
FYI: I cant use a bulk insert because the flat file layout it has follows.
H`HAVL1 1`HVAL2 2`HVAL3 3`HVAL4 4
D`DVAL1 1
D`DVAL1 1`DVAL2 2
D`DVAL1 1`DVAL2 2`DVAL3 3
D`DVAL1 1`DVAL2 2`DVAL3 3 ` HVAL4 4
i could use a bulk insert if the layout were as follows BUT its not.
H`HAVL1 1`HVAL2 2`HVAL3 3`HVAL4 4
D`DVAL1 1```
D`DVAL1 1`DVAL2 2``
D`DVAL1 1`DVAL2 2`DVAL3 3`
D`DVAL1 1`DVAL2 2`DVAL3 3 ` HVAL4 4
Thanks!!I just did this very thing. You're on the right track.
I disabled the transformation step using Disconnected Edit (in my case this
step is called DTSStep_DTSDataPumpTask_2). I then had an ActiveX script loop
thru the file names calling that transformation step.
Here's some code/pseudocode:
Set oPkg = DTSGlobalVariables.Parent
Set oFSO = CreateObject("Scripting.FileSystemObject")
Dim strThisFileName
strThisFileName = yourfirstfilename ' Get the first file name from
your table here.
' Loop thru your file names here
Do While NOT ...EOF
If oFSO.FileExists(strThisFileName) then
oPkg.Connections("YourSourceFileConnection").DataSource =
strThisFileName ' Set the filename
oPkg.Steps("DTSStep_DTSDataPumpTask_2").Execute
' Import file into table
else
exit do
end if
strThisFileName = yournextfilename
loop
Set oPkg= Nothing
Set oFSO = Nothing
"John Smith" <zzaro@.excite.com> wrote in message
news:O4gYZ96cGHA.1260@.TK2MSFTNGP05.phx.gbl...
> Hello,
> Here i my problem;
> I have a number of files with all the exact same format and i need to
> import all of them into the same table.
> As i never faced the problem before im thinking
> to created a txt source and and sql server source with a data transfrom
> between them. I want to change the the txt source connection "File Name"
> properties for each file I need to import and loop until im done importing
> all my files.(FYY: I hold all the table names in a sql table that I
> refreash before importing)
> So my question are ;
> Is this the best approch to solve the issue with i data provided above?
> how (high level - unless u want to give me the code ;-) )
> or is there other ways that are better then this?
> FYI: I cant use a bulk insert because the flat file layout it has follows.
>
> H`HAVL1 1`HVAL2 2`HVAL3 3`HVAL4 4
> D`DVAL1 1
> D`DVAL1 1`DVAL2 2
> D`DVAL1 1`DVAL2 2`DVAL3 3
> D`DVAL1 1`DVAL2 2`DVAL3 3 ` HVAL4 4
> i could use a bulk insert if the layout were as follows BUT its not.
> H`HAVL1 1`HVAL2 2`HVAL3 3`HVAL4 4
> D`DVAL1 1```
> D`DVAL1 1`DVAL2 2``
> D`DVAL1 1`DVAL2 2`DVAL3 3`
> D`DVAL1 1`DVAL2 2`DVAL3 3 ` HVAL4 4
>
> Thanks!!
>

DTS Import Text File Replacement - too many errors with DTS

I am using the DTS COM object to import a bunch of different text files.
some are 2 or 3 million rows. if there's an invalid row (can't find
row delimiter or column delimiter) sometimes DTS will crash and stop the
import... it even does it if I try to import it from the GUI, and going
into DTS tasks and messing with the errors rows, etc.
So, I am looking for a better way to batch import... can't use bulk
insert because of field qualifiers... any ideas? any 3rd party object i
can use to quickly import a large file?
i using c#, btw.
thanks
EdDid you look at the bcp utility? It ain't pretty, but for brute force,
it is hard to beat.|||Bulk insert and bcp both can use format file (i.e. you can specify your
custom terminator), batch size, and allowable max error. You should check
them out.
-oj
"Ed West" <west@.westville.com> wrote in message
news:OQxMbDxCFHA.3492@.TK2MSFTNGP12.phx.gbl...
>I am using the DTS COM object to import a bunch of different text files.
>some are 2 or 3 million rows. if there's an invalid row (can't find row
>delimiter or column delimiter) sometimes DTS will crash and stop the
>import... it even does it if I try to import it from the GUI, and going
>into DTS tasks and messing with the errors rows, etc.
> So, I am looking for a better way to batch import... can't use bulk insert
> because of field qualifiers... any ideas? any 3rd party object i can use
> to quickly import a large file?
> i using c#, btw.
> thanks
> Ed|||hi - many thanks, bcp/bulk insert is working great... i am creating a
format file dynamically.
thanks again
oj wrote:
> Bulk insert and bcp both can use format file (i.e. you can specify your
> custom terminator), batch size, and allowable max error. You should check
> them out.
>|||Hello - what about a Field Qualifier? how can I specify that? For some
files, the field qualifier is " and that is showing up in the database
field after being imported... thanks
- Ed
oj wrote:
> Bulk insert and bcp both can use format file (i.e. you can specify your
> custom terminator), batch size, and allowable max error. You should check
> them out.
>

DTS import

Hi Folks,
I want to import the contents of several text files into a single sql server
table, however I also want to add in an additonal field which will give some
sort of indication which source file the row came from.
So for example.
File1.csv
a, b, c
File2.csv
x, y, z
sqlTable
a, b, c, 1
x, y, z, 2
Any ideas how can I could do this, I know I could import to tempory tables
and set the indicator field with a default value and then copy this data
into the final table but this seems like I would be adding in unnecessary
steps.
Cheers in advance
Charlie.From the example given it would seem that you need
A. A loop
B. A Global Variable that you can set.
Basics are that on the setup of the package you map Source --> Destination
columns but you also (In an Active X transform) map a Global Variable to a
destination column.
At each iteration you can then set the GV to whatever value you want
(Perhaps name of file is good).
Here is a looping example.
Looping, Importing and Archiving
(http://www.sqldts.com/Default.aspx?246)
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org|||Thanks Allan,
It looks a bit complex, but I'll give it a go, I like a challenge!
Charlie.
"Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
news:u5SupPhbDHA.3312@.TK2MSFTNGP11.phx.gbl...
> From the example given it would seem that you need
> A. A loop
> B. A Global Variable that you can set.
>
> Basics are that on the setup of the package you map Source --> Destination
> columns but you also (In an Active X transform) map a Global Variable to a
> destination column.
> At each iteration you can then set the GV to whatever value you want
> (Perhaps name of file is good).
> Here is a looping example.
> Looping, Importing and Archiving
> (http://www.sqldts.com/Default.aspx?246)
> --
>
> Allan Mitchell (Microsoft SQL Server MVP)
> MCSE,MCDBA
> www.SQLDTS.com
> I support PASS - the definitive, global community
> for SQL Server professionals - http://www.sqlpass.org|||I've had a look at this and downloaded the sample file from the website, but
its a bit beyond me, I don't mind setting up seperate DTS packages for each
file import if this makes things simpler.
Charlie.
"charlie B" <Charlie.remove@.freeuk.com> wrote in message
news:RTF3b.222$b82.110168@.newsfep1-win.server.ntli.net...
> Thanks Allan,
> It looks a bit complex, but I'll give it a go, I like a challenge!
> Charlie.
> "Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
> news:u5SupPhbDHA.3312@.TK2MSFTNGP11.phx.gbl...
> > From the example given it would seem that you need
> >
> > A. A loop
> > B. A Global Variable that you can set.
> >
> >
> > Basics are that on the setup of the package you map Source -->
Destination
> > columns but you also (In an Active X transform) map a Global Variable to
a
> > destination column.
> > At each iteration you can then set the GV to whatever value you want
> > (Perhaps name of file is good).
> >
> > Here is a looping example.
> >
> > Looping, Importing and Archiving
> > (http://www.sqldts.com/Default.aspx?246)
> >
> > --
> >
> >
> > Allan Mitchell (Microsoft SQL Server MVP)
> > MCSE,MCDBA
> > www.SQLDTS.com
> > I support PASS - the definitive, global community
> > for SQL Server professionals - http://www.sqlpass.org
>|||OK.
Setting up another package for every file will be OK if you have 2 or 3
files but will quickly become unmanageable if you have 5000.
We can go through it if you wish.
Are you in the UK (email suggests this)
I am doing a presentation at MS Reading next week and this is one of the
things I am covering.
See here
http://www.sqlserverfaq.com/?eid=32
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

DTS Horror in SP3, SQL Server 2000

Has anyone found problems with transforming data from a SQL (Server) source to flat files, only starting with SP3?

We've got a problem which we thought was due to editing SP1 created packages with SP3 applied to the client, but we've narrowed it down to it being SP3 specific.

Basically we have done the following:
1. Create a new DTS package on a local server (Enterprise Manager - Data Transformation Services, Local Packages, right click, New Package)
2. Create a source connection, setting the Data Source to MS OLEDB Provider for SQL Server. Leave the server as local, and select a database. NT Authentication was used for the sample. Set it to a small table on the same SQL Server box as the DTS package is being created on. Use the whole table as the source, don't specify a query.
3. Create a destination file on the local box, and set its location to c:\testDTS.txt. Leave the file format settings at their default.
4. Click on Task menu... Transform Data Task... and select Source Connection and Destination Connection.
5. Double click on the Data Transformation task to set the column mappings.
6. Leave the source settings at their default (select a different table if the first one is too big to export)
7. Click on Destination tab and Click on the Populate from Source button, followed by the Execute button.
8. Click on the Define Columns button
9. After a few seconds, an MMC error is displayed, which is repeatable every time this procedure is followed.

Further investigation has shown that SP1 packages do not seem to exhibit this behaviour, unless they are being edited by a client that has SP3 applied.

The database software is SQL Server 2000 Developer Edition with SP1 applied.
The OS is Windows XP Professional with SP1.

Any feedback from others with this fault would be much appreciated, but I can't believe given the simplicity of the test that someone has not already encountered this since the release of SP3. I've built literally hundreds of DTS packages, but have only encountered this problem with SP3.A fix for this problem has been found. Although slightly different to the one we experienced, the problem is described at :
http://support.microsoft.com/default.aspx?scid=kb;en-us;814113

...and the hotfix for it can be downloaded at : http://www.microsoft.com/downloads/details.aspx?familyid=9814AE9D-BD44-40C5-ADD3-B8C99618E68D&displaylang=en

Hope this helps anyone else out there who has expereienced the same issues with the DTS GUI.

(With thanks to Paul Hiom at RHI/CSC in Birmingham for help with this).:)