DTS Query

Hey Folks,
Please have a look at the question below and let me know what can I do about it?

If I had to integrate a feed of end of day stock price information
for xome companies, how should I do it? The feed is to be be delivered once per trading day in a comma-separated format via an FTP site. The feed is to be used by some daily users in a web application.

Can I use DTS? Could someone provide me with more information as to how I should proceed with this?

--ThanksPersonally, I'd use a sproc and bcp...

DTS Problem

Hi Folks,
I have a two node cluster and I have a DTS package, which transfers data from a text file to the database. The problem is like it runs fine when the cluster is in its base node(Node1) but when ever the cluster fails overs (to node2) the dts job fails saying that the path not found.

1. The drive is a dependent resource to sql server.

2. When I try to run the DTS package itself (from node2) its running fine.

any insights guys?

with smiles
santhoshHow is the file referenced in the DTS package (ie, is it UNC)?

Is there possibly a permissions issue (Log in to Node two as the SQL Service account and attempt to run the DTS package)?

I have a similar set up with a two-node cluster and a DTS package that references a cluster disk. No issues that I am aware of.

Please provide a bit more detail.

Regards,

hmscott

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 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