sql server dts: Dynamic Query Order in DTS -- sql server dts

Re: Dynamic Query Order in DTS

Allan Mitchell
6/11/2004 12:41:50 PM

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

[quoted text, click to view]

Re: Dynamic Query Order in DTS

Allan Mitchell
6/11/2004 1:03:03 PM

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

[quoted text, click to view]

Dynamic Query Order in DTS

Prabhat
6/11/2004 2:06:51 PM

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

Re: Dynamic Query Order in DTS

Prabhat
6/11/2004 3:54:46 PM

Hi Allan,

Thanks. I have also got similar information from
http://support.microsoft.com/default.aspx?scid=kb;en-us;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

[quoted text, click to view]

Re: Dynamic Query Order in DTS

Prabhat
6/11/2004 4:06:49 PM

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

Thanks
Prabhat

[quoted text, click to view]

Re: Dynamic Query Order in DTS

Prabhat
6/11/2004 4:41:16 PM

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

[quoted text, click to view]

Re: Dynamic Query Order in DTS

Prabhat
6/11/2004 5:18:54 PM

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@hotmail.com . I like your
suggestions a lot.

Thanks
Prabhat

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server dts group.