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

#1

You will see in the .Bas file there is an Execute method and a
SaveToSQLServer method.

'goPackage.SaveToSQLServer "(local)", "sa", ""
goPackage.Execute
tracePackageError goPackage
goPackage.Uninitialize

You can add this code with mods to a class in VB.net and you will also need
to add a ref to the DTS Package Object Library.

Personally I would create the package in DTS Designer and manipulate through
the object model.
--
--

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" <tgbarbour (AT) comcast (DOT) net> wrote

Quote:

I'd like to run a Visual Basic (.BAS) file that I've saved from a DTS
package (save DTS package as Visual Basic file). More specifically, I'd

like to either use and then modify a created DTS package (the connections
and tasks flowchart), or write a new original VB program that performs DTS
export functions.

Quote:

I have a SQL database that I'd like to use as the source data to create
many sub-set flat files (text destination) as export txt files. I can

create a DTS connections and tasks flowchart to do this, but I'd rather use
a VB program. I can save a VB file from the DTS construction, but once
saved how can I use this file, i.e., in general, how do I run a .bas file in
Visual Studio .NET?

Quote:

Thanks for your assistance.

Tim Barbour
tgbarbour (AT) comcast (DOT) net

#2

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 (AT) comcast (DOT) net> wrote

Quote:

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.

Quote:

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.

Quote:

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?

Quote:

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.

Quote:

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

Quote:

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?

Quote:

I hope I've explained myself well enough. I may be missing something that
may be obvious.

Tim Barbour
tgbarbour (AT) comcast (DOT) net

#3

I think you've hit the mark, right on

Thanks for the lead and the resources of SQLDTS.com. I think I'll learn a lot here

I haven't tried the BulkExportTask yet I'm new to the notion of connections and tasks and the whole of DTS as well, but anxious to learn more. I need to first find out how to implement this task (the code) in DTS. I see that I have a .dll file of the task, resulting from the download you pointed me to

I copied this file to my SQL Server (MS$SQL) Binn folder and now see that the task icon shows up in the DTS tasks panel. I'm getting a "specified module not found" error message

I'm making progress, but need to look at the doucmentation that came with the download to see how to implement it. It sounds like just what I need.

Thanks again

Tim Barbou

#4

Thanks for your reference to the Bulk Export Task. I've used it to create text files sourced in an SQL table

I've tried to use the other two downloads that were cited in the bulk export pages, in particular the Loop through a global variable... and How to export all files in a database..

I see that a .DTS file is included in the extracted files on these two downloads. I 'm new to DTS, so how do I open the .DTS file(s)? To see the referenced samples? How do I use these task funcationalities with the Bulk Export Task to create text files for each SQL DB table

I've created one text file from one DB table using the Bulk Export Task, but haven't figured out how to do implement the looping to do the text exporting of all tables in a SQL DB

Thanks for your help on this

Tim Barbou
tgbarbour (AT) comcast (DOT) net

#5

Right Click on the Data Transformation Services Folder | Open Package

--
--

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" <tgbarbour (AT) comcast (DOT) net> wrote

Quote:

Thanks for your reference to the Bulk Export Task. I've used it to create
text files sourced in an SQL table.

I've tried to use the other two downloads that were cited in the bulk
export pages, in particular the Loop through a global variable... and How to

export all files in a database...

Quote:

I see that a .DTS file is included in the extracted files on these two
downloads. I 'm new to DTS, so how do I open the .DTS file(s)? To see the

referenced samples? How do I use these task funcationalities with the Bulk
Export Task to create text files for each SQL DB table.

Quote:

I've created one text file from one DB table using the Bulk Export Task,
but haven't figured out how to do implement the looping to do the text

exporting of all tables in a SQL DB.

Quote:

Thanks for your help on this.

Tim Barbour
tgbarbour (AT) comcast (DOT) net

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

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

microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts

Re: How do I run a .BAS file saved from a DTS package? - 05-05-2004 , 09:41 AM

Re: How do I run a .BAS file saved from a DTS package? - 05-05-2004 , 02:54 PM

Re: How do I run a .BAS file saved from a DTS package? - 05-05-2004 , 04:56 PM

Re: How do I run a .BAS file saved from a DTS package? - 05-05-2004 , 06:26 PM

Re: How do I run a .BAS file saved from a DTS package? - 05-06-2004 , 12:34 AM