Hey all,

I've trying to use a dos batch file (through the dtsrnui executable)
to send 2 parameters to a dts package: a table name (aka, the 'input'
table from which I want to pull records from) and an output file name
(aka, the .txt file which I want the pulled data to be saved to). I've
basically got this part down pat as the executable comes with the
handy "generate" function, but please, read on.

The dts package consists of just 2 connection objects and a transform
data object. My sql query code w/in the Data transform object looks
something like this: select customerid, lastname, firstname, zipcode
from table_blah order by zipcode

My problem is thus: How do I incorporate the parameters passed from
the dos prompt into the sql query? Or can I? IOW, can I substitute
"table_blah" above for a variable name?

Any help, or even pointing me in the right direction would be
extremely appreciated.

So you have global variables that are passed through the DTSRUN interface to
a package.

1 changes the table name in the source
1 changes the text file location

So long as metadata is not changed in the source i.e. the attributes are not
named differently then we can approack it like this


If you want to have the whole table from the Source then you can change the
SourceObjectName of the datapump task using the Dynamic Properties task by
assigning your GV to the correct property.

If you want to selectively do a SELECT <list. FROM <table> then I would
recommend doing this in an Active script task and concatenating the GV into
the string. There are examples on the site.

Changing the destination filename is no problem either. Changethe
DataSource property of the text file connection object. again we have
examples on the site.

--
--

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


"Roy Anderson" <roy.anderson (AT) gmail (DOT) com> wrote

Thanks for the advice Allan. Terrific site btw.
I'm still having the same issues (i.e., lack of connectivity between dts
GV's and sql statements), though.

So, having inserted the sql statement into an Active script, I would no
longer need a sql statement w/in the Transform Data Task itself?

I'm uncertain how changing the "DataSource Property" will do anything? I
have it set
to write to a .txt file. If you're referring to referencing a global w/in
the "File Name:" field, exactly what is the standard syntax? I've tried @@,
%, ? preceding/after the variable name, and all combinations thereof with
consistent errors and can't seem to locate the relevant page on your site.

Thanks again!

Inline

--
--

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


"Roy Anderson" <roy.anderson (AT) gmail (DOT) com> wrote

No. The AX Script task will simply override at runtime any statement you
put in here.


The DataSource property is the location of the text file in this instance so
you can set it to write to wherever you want


Examples

Global Variables and SQL statements in DTS
(http://www.sqldts.com/Default.aspx?205)

How can I change the filename for a text file connection?
(http://www.sqldts.com/default.aspx?200)

Thanks a bunch Allan. It's appreciated. Your experience has been very
informative. Actually, I had already been to the two links you referenced
below and I've been getting the same error since I came to work this
morning. I'm ready to bang my head against the wall. Didn't want to announce
the error earlier because I figured I was just making some obvious mistake
(which I may be), but since I still can't piece it together.... I seem to be
doing everything by the book and in accordance with the website and msdn
recommendations to no avail...

The error occurs in my activex script here:
Set oPkg = DTSGlobalVariables.Parent
Set oDataPump = oPkg.Tasks("XYZ").CustomTask

Where "XYZ" is the name of my transform data task. Now, it parses just fine,
but when I try to run it, it comes back with "Task 'XYZ' not found" error?
Is it because the activex script is running from w/in the transform data
task?

I've tried every single name in my dts package at this point to no avail.
Have you ever experience this error?


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

You have a Task called XYZ?


dim tsk

For each tsk in DTSGlobalVariables.Parent.Tasks
msgbox tsk.Name
next


Anoth way is

Right click on the task | Workflow properties | options tab

at the top you will see something like DTSStep_DTSDataPumpTask_1


Change the Step to Task and try your script again




--
--

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


"Roy Anderson" <roy.anderson (AT) gmail (DOT) com> wrote