I am working on a project where I need to generate a flat file using sql
data. The machine that I need to load the flat file on is remote relative
to the location of the sql server. I am thinking about using dts (creating
a dts package on the sql server) and running the package remotely from the
client, using osql or something.

The flat file is straight forward except for concatenating "E-" to the
beginning of each field in the first column of data. I think I can probably
use the ActiveX script task to make this transformation but I have no
experience with the ActiveX script task tool. Maybe you know of an easier
way. What are your thoughts?

Not sure what you want here.

Do you want to generate a text file based on data in SQL Server and for the
First field's data prefix it with E-

If yes then no Acripting is necessary as this, or at least a modified
version of it will work in your Source SQL Statement

SELECT 'E-' + col1,...........
FROM TABLE
WHERE <filter conditions>

If you want to read from a text file and put it into SQL Server doing the
same append then you would use an Active Script transform.

In there you would have, similar to

Function Main

DTSDestination("ColName") = "E-" & DTSSource("ColName")
....
...
..
Main = DTSTransformStat_OK

End Function



--
--

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

"Rick" <bob (AT) bob (DOT) net> wrote

Thank you for the quick reply. You were right though I had to CAST it
this way 'E-' + CAST(MID AS varchar(10)) As OrderID.

Now, can I run this sp remotely using osql and create a text file?

"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

Have a look at BCP first.


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