Dynamic Properties Task & SourceSQLStatement

#1

I'm trying to import from a dBase table whose name changes every day
based on the date. So I add a Dynamic Property Task, select the copy
data task in the tree, then select the SourceSQLStatement property. At
this point I have a choice of how to set the value -- based on a
query, ini file, a constant value, etc. What I am trying to do is
concatenate an SQL statement where the table name is the name of the
text file without an extension (because that's how dBase works). So I
choose 'Query' and enter something like

select 'select `field1`, `field2` from `L' + <sql to create name based
on date> + '`'

When I execute the dynamic task, nothing happens. The SQL for the copy
data task does not change. However, when I use the dynamic task to set
the SourceSQLStatement property to a CONSTANT (instead of Query) such
as "blah blah blah" and execute it, the SQL for the copy data task
DOES change. What am I going wrong?? Or is there a better way to
accomplish this? Totally new to DTS.... Thanks!

#2

OK so how do you pick up the name of the text file. Personally I would use
an Active Script task and the FSO to get the name of the file and assign to
a Global Variable. I would then use the Global Variable and build the
SELECT statement

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

--

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

"Patrick Huffer" <patrickhuffer (AT) hotmail (DOT) com> wrote

Quote:

I'm trying to import from a dBase table whose name changes every day
based on the date. So I add a Dynamic Property Task, select the copy
data task in the tree, then select the SourceSQLStatement property. At
this point I have a choice of how to set the value -- based on a
query, ini file, a constant value, etc. What I am trying to do is
concatenate an SQL statement where the table name is the name of the
text file without an extension (because that's how dBase works). So I
choose 'Query' and enter something like

select 'select `field1`, `field2` from `L' + <sql to create name based
on date> + '`'

When I execute the dynamic task, nothing happens. The SQL for the copy
data task does not change. However, when I use the dynamic task to set
the SourceSQLStatement property to a CONSTANT (instead of Query) such
as "blah blah blah" and execute it, the SQL for the copy data task
DOES change. What am I going wrong?? Or is there a better way to
accomplish this? Totally new to DTS.... Thanks!

#3

In message <c9116053.0401300821.7bd906d5 (AT) posting (DOT) google.com>, Patrick
Huffer <patrickhuffer (AT) hotmail (DOT) com> writes

Quote:

I'm trying to import from a dBase table whose name changes every day
based on the date. So I add a Dynamic Property Task, select the copy
data task in the tree, then select the SourceSQLStatement property. At
this point I have a choice of how to set the value -- based on a
query, ini file, a constant value, etc. What I am trying to do is
concatenate an SQL statement where the table name is the name of the
text file without an extension (because that's how dBase works). So I
choose 'Query' and enter something like

select 'select `field1`, `field2` from `L' + <sql to create name based
on date> + '`'

When I execute the dynamic task, nothing happens. The SQL for the copy
data task does not change. However, when I use the dynamic task to set
the SourceSQLStatement property to a CONSTANT (instead of Query) such
as "blah blah blah" and execute it, the SQL for the copy data task
DOES change. What am I going wrong?? Or is there a better way to
accomplish this? Totally new to DTS.... Thanks!

Since there is no expression support in Dynamic Properties, and you
cannot use parameter support for the name of a table, I would use
script.

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

--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org

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

Dynamic Properties Task & SourceSQLStatement

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

Dynamic Properties Task & SourceSQLStatement - 01-30-2004 , 10:21 AM

Re: Dynamic Properties Task & SourceSQLStatement - 01-30-2004 , 10:38 AM

Re: Dynamic Properties Task & SourceSQLStatement - 01-30-2004 , 02:32 PM