Re: Dynamic Query Order in DTS

From: Allan Mitchell (allan_at_no-spam.sqldts.com)
Date: 06/11/04 

Date: Fri, 11 Jun 2004 11:36:40 +0100

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@hotmail.com> wrote in message
news:u03Vh55TEHA.2408@tk2msftngp13.phx.gbl...
> 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
>
> "Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message
> news:uvIGUH5TEHA.3336@TK2MSFTNGP10.phx.gbl...
> > 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@hotmail.com> wrote in message
> > news:OPCrS94TEHA.1012@TK2MSFTNGP09.phx.gbl...
> > > 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
> > >
> > >
> > >
> > >
> >
> >
>
>

Relevant Pages

  • Re: Iterative loop through SQL expression
    ... Allan: ... I was able to accomplish this with help from your book, "Pro SQL 2005 IS", ... with an Execute SQL Task, a Script Task, and the ForEach ... I've tried to set up the ForEach Loop ...
    (microsoft.public.sqlserver.dts)
  • Re: Problem with SQL Task in MS SQL Server DTS package
    ... I'm not an SQL expert but it looks to me like I'm using the ... How could I word my query without the aliases? ... "Allan Mitchell" wrote: ... DTS is unable to offer you ...
    (microsoft.public.sqlserver.dts)
  • Re: INSERT Problem
    ... You were right Allan. ... SQL told me that it was not a valid ... >is invalid syntax in SQL Server. ... >> in your query? ...
    (microsoft.public.sqlserver.dts)
  • Re: Using global variable in Data Transform Task (SQL Server 2000)
    ... I have a Data Transform Task with the following in the SOURCE tab: ... declare @sql varchar, @servername sysname ... NO_DETAILLANT'') ora inner join BRANCHOFFICE sql on ... that I initialized previously with a Dynamic Properties Task. ...
    (microsoft.public.sqlserver.dts)
  • Re: DTS with SQL Express 2005
    ... Thanks Allan ... I suspect that the Import/Export utility may have been removed from the ... release version of SQL Express ...
    (microsoft.public.sqlserver.dts)
Loading