Hi,

I have created a DTS package that needs to be run multiple times with a
single column's data changed. Rather than create multiple DTS packages for
each, is there a way I can easily change this column's value when I run the
package?

Ie. have 1 or more options that I set when I run the package?

Thanks,
Lee

I am unsure as to your needs.

If a row of data changes in your table you need to run a DTS package and run
it multiple times?

Why?

Can you give us a repro so we can see what you mean?


--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
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


"Lee Swanson" <lee (AT) lessspam (DOT) ibc.com.au> wrote

Sorry I will try to explain it better

I have a DTS package that imports data from a text file to a database table
and to each record is inserted an ID to link it to a record in another
table.

so I have one record in one table that links to many records in the table I
am importing to. I need to import data for more than one of these records.

what I would like to do is be able to set the record it links to when I run
the DTS package rather than have to create multiple DTS packages to hardcode
each ID.

Lee.


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

OK

So you are importing into the MANY side of a ONE --> MANY link.
When you import to the many you need to grab the ONE side value.

Personally I would

1. Import the text file into a working table. Anything else with text
files involves row by row processing.
2. You can then JOIN on this table to your ONE table and pick up the
corresponding value. Instead then of using a tablename as the source for
the datapump you would have a SQL Statement.

You can use this method when your text file is flattened and you need to
import it into multiple tables. You import the ONE side first and then you
do the MANY side. You can use things like Lookups for this but again this
is row by row.

Enjoy


--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
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


"Lee Swanson" <lee (AT) lessspam (DOT) ibc.com.au> wrote

I'm not sure that this solves my problem.

As the row that the table will join with will be different each import (and
not able to be determined by the imported data) how will I find the row to
which I want to link?

Basically my only solution is to be able to select an option when I run the
package. I would need to select a couple of options and the values could be
placed in variables to be used in the vb code transformation.

Is there any way of creating a nice UI that I can select values for
variables with before running the package?

Lee.

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

ok
So we need to back up a little then

You have how many versions of text files?
From how many tables are you going to be retrieving the lookup value.

You can easily pass to the package, using the object model or the /A switch
to DTSRUN, values which set Global Variables. These can be used to build up
SQL Statements.

By not being able to determine the table from which you need to do a look
up from the data takes away a lot of the dynamicism of the package


If you have many versions of text files (Differing structures) and many
lookup tables, many differing fields within the same lookup table then it
starts to get a little more tricky.

You can look to use a Linked server to the text file. This will to some
extent allow you to trat it like a proper table ("Notice the only to some
extent")



--

----------------------------

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
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


"Lee Swanson" <lee (AT) nospam (DOT) ibc.com.au> wrote

DTSRUN is a command line command?

I will have lots of text files that will be delivered on a regular basis in
the same format.

I dont need to look anything up as I will know the IDs to link each import
to. If I can set some global variables, that will be fine.

I was hoping to create a nice interface for it. I guess I could create a VB
app to run the dts package, then I can draw from lookup tables etc and
select what I want from list boxes.





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

Ah OK.

Same format text files just different options is easy.

You can easily loop through the text files.


You can pass the Global Variable values in through, as mentioned, DTSRUN
(cmdline tool) and the /A switch.
You can also set values using the object model very easily.

You can also have the package read from an INI file amongst other things
using the Dynamic Properties task.




--

----------------------------

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
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


"Lee Swanson" <lee (AT) nospam (DOT) ibc.com.au> wrote

Thanks for your help Allan.

I think I will attempt to create a vb front-end to allow me to easily set
variables from lookup tables etc.

Lee.

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