Hi All,

I am using DTS Packages to Import Data from .dbf files to SQL Server
database. I am using Global variables for "Source DBF Path", "DBF File
Name", "SQL Server", "Database", "arget Table Name" (Which is SAME every
time).

I am using Source Connection (dbase 5), target connection (sql server),
Transform data Task (which will have field mapping settings) and Dynamic
Property Task which set the values of Global variables to required property
of each objects. This is working fine as till now I am using Source TABLE
Name.

But now My requirement is to Import the data in a perticular Sort Order from
the SOURCE .DBF tables. The Sort order is user selectable.

So I need the Change the "SourceSQL Statement" of the Data Pump task with
the required SQL query which will INCLUDE the Order By Clause based on the
value of a new Gloabl Variable.

Like if the new Gloabl Variable will have value "Date" then My SQL Query
should be set to "Select * from " + <TableNameGloablVariable> + " order by "
+ <SourceFieldName>

Here I wanted to access the <Source Field Name> as this will be different
for different source table.
So How do I set the Dynamic SQL Query Set with in the Package as per the
value of Gloabl variable and Assign the Query to "SourceSQLStatement"?

Thanks in Advance for any help or suggestions.

Prabhat

I think I get what you want and if I do then does this help

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

--
--

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


"Prabhat" <not_a_mail (AT) hotmail (DOT) com> wrote

Hi Allan,

Thanks. I have also got similar information from
http://support.microsoft.com/default...242391&sd=tech .

I have tried Like below:

Function Main()
Dim oPkg, oDataPump, sSQLStatement

' Build new SQL Statement
sSQLStatement = "SELECT * FROM " &
DTSGlobalVariables("gSourceFileName").Value & ""
Select Case DTSGlobalVariables("gSortOrder").Value
Case "Denomination"
sSQLStatement = sSQLStatement + " ORDER BY AMTRCVD "
Case "Date Paid"
sSQLStatement = sSQLStatement + " ORDER BY RDATE "
End Select

' Get reference to the DataPump Task
Set oPkg = DTSGlobalVariables.Parent
Set oDataPump = oPkg.Tasks("DTSTask_DTSDataPumpTask_1").CustomTask

' Assign SQL Statement to Source of DataPump
oDataPump.SourceSQLStatement = sSQLStatement

' Clean Up
Set oDataPump = Nothing
Set oPkg = Nothing

Main = DTSTaskExecResult_Success
End Function
=============================
But Still That did not help. I have taken one Active X Script Task and
written the above Code.
I am not Sure is that correct or not.
And again I am not sure When that Script Will be executed by the package -
In Which Order that will be executed. As I think My requirement is 1st the
Dynamic Task then ActiveX Script and then the Data Pump should work.


Or I need to do something in Workflow Property? If Yes What?


Thanks
Prabhat

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

are you using the Dynamic Properties task for something else because you do
not in this instance require it here.

You order should be

Active Script Task ----> On success Workflow Constraint ---> Data Pump task

--
--

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


"Prabhat" <not_a_mail (AT) hotmail (DOT) com> wrote

I could not get your reply. can u please elaborate again.

Thanks
Prabhat

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

In your package you will have an Active Script task.
This needs to fire first.
The way you do that is to make sure you use Workflow constraints

Introduction to Workflow
(http://www.sqldts.com/default.aspx?287)

Firing this first will set the properties of your DataPump task which will
follow.



--
--

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


"Prabhat" <not_a_mail (AT) hotmail (DOT) com> wrote

Thanks for the link.

But as My requirement is :
1) Complete the Dynamic Task (Because I assign Connection and Data file path
etc)
2) Complete the ActiveX Script task (used to change the Source SQL Script)
3) then Do the DataPump task.

I am able to set the the WorkFlow to 1) ActiveX ---> On Success ----->
DataPump.

But that does not help. As I have to set the Dynamic Task then AxtiveX and
then DataPump.

Any suggestion or Where I am missing the Flow/Logic. I am very new to DTS.

Thanks
Prabhat

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

So have the package look like this


Dynam Prop Task --> AX Task -->DataPump task


If you are going to have to use the AX task anyway you could drop the Dynam
Prop task and do it all in the AX Task

--
--

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


"Prabhat" <not_a_mail (AT) hotmail (DOT) com> wrote

Hi Allan,

Yes My Package is now Looks Like that.

I have added Work Flow for AX task as: On Success of Dynamic Mapping ---> AX
Task
and then Added Work flow for DataPump as: On Success of AX Task ----->
DataPump

So my package is: Dyn Prop ----> AX Task ---> DataPump.

So in this case your suggestion is good. I can Use only AX Task and Can Drop
Dyn Pro task. But Is there any advantages of the Ax task over Dyn Prop task
to assign Property?

Can I chat with U? My MSN id is: nathprabhat (AT) hotmail (DOT) com . I like your
suggestions a lot.

Thanks
Prabhat

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

The Dynam Properties task is a good gui. you do not have to know the object
model too much as MS have provided a very good interface here. For your
situation though you wanted to change the SourceSQLStatement through the use
of variables and AFAIK there is no way to do this in the Dynamic Properties
task.

I really have no preferences as to which I use but will generally use the AX
Script task as I have been doing this since SQL Server 7 and I keep
forgetting it is there.



--
--

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


"Prabhat" <not_a_mail (AT) hotmail (DOT) com> wrote