Re: How do I run a .BAS file saved from a DTS package?

From: Allan Mitchell (allan_at_no-spam.sqldts.com)
Date: 05/05/04 

Date: Wed, 5 May 2004 20:54:00 +0100

You want, as far as I can tell, to take a DB and export all tables to text
files right?

How about this then?

How to export all tables in a database
(http://www.sqldts.com/default.aspx?299) 

-- 
-- 
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Tim Barbour" <tgbarboour@comcast.net> wrote in message
news:A81FBD92-7F75-4D47-8A5E-C3178DDD6073@microsoft.com...
> Thank you for your feedback.  I'm a newbie to SQL Server (about 4 month).
I have the Developers Edition and use SQL Server Enterprise Manager
extensively.  I have used DTS for import and export.  I run SQL queries to
produce 1,000's of small DB tables (each with few thousand records)
extracted (via sql scripts run using  SQL Query Analyzer) from a large
source data table.   I'd like to export these many small tables as text
files (using DTS | Export | Text functionality.  The process is simple
(column mapping, transformations, etc.).  I save the DTS Package to make
re-running easier.  I can save the DTS package as SQL Server, Structured
Storage File, or Visual Basic file.
>
> The DTS Designer is easy to understand and use.  However, my goal is to
use DTS Export to create 1000s of small text files from a large SQL DB.  To
do this with the Designer would require graphically constructing 1000s of
connections (text file destinations) from the one SQL Server DB (OLE data
provider).   Although this is do-able, to create the many flat files I need,
it's probably not the most efficient way of accomplishing my task.
>
> So, I'm wondering if I can build a small DTS package (using the Designer),
then save the Package as a Visual Basic file, and then modify the .BAS file
by introducing additional code (mostly via cut and paste and minor code
revisions) to add the many additional flat file connections I'd like as a
part of the DTS Package.  Does a saved Structured Storage File help me?
>
> I'm looking for a means of using the DTS export funtionality to accomplish
the above, but with code (if possible) not with the Designer, since the
Designer construction would be tedios to construct given my one big SQL
database table to many small text destination files.
>
> Can I use the DTS VB file?  Can I use SQL Query Analyzer to construct SQL
scripts to accomplish the DTS export process?  Can I use Visual Studio .NET
to create and run a DTS export program (.BAS or otherwise)?
>
> Thanks for your time reviewing this.  I'd like to use the DTS Designer as
you suggest, but it seems a bit impractical when 1000s of connections are
involved?  Can I somehow cut and paste Designer connections to build a small
construct into a large one quickly?
>
> I hope I've explained myself well enough.  I may be missing something that
may be obvious.
>
> Tim Barbour
> tgbarbour@comcast.net
>
>

Relevant Pages

  • Re: DTSRun and Batch file
    ... >> install the DTS DLLs on the local machine. ... So the DTS package is running ... >> user to start a SQL Agent Job that runs the package. ... Darren Green (SQL Server MVP) ...
    (microsoft.public.sqlserver.dts)
  • Re: Pls Help -- Upsizing from Access
    ... DTS is very nice btw. ... Jim ... work in SQL Server. ...
    (microsoft.public.sqlserver.programming)
  • Re: Using global variable in DTS packages
    ... Global Variables and SQL statements in DTS ... Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) www.SQLDTS.com - The site for all your DTS needs. ...
    (microsoft.public.sqlserver.dts)
  • Re: Maintain production DB with changes from development DB
    ... Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) www.SQLDTS.com - The site for all your DTS needs. ... Or will I have to write a script to update all 4 servers? ...
    (microsoft.public.sqlserver.dts)
  • RE: A couple of R2 Impressions
    ... I agree 110% with Ross M...why why why take away my DTS that I rely and works ... component or I can't use it...it is going to be hard enough to master SQL ... have concern over the decision to dramatically reduce functionality with the ... or good business for MS – it just alienates customers. ...
    (microsoft.public.windows.server.sbs)
Loading