Re: Repeating 3 Datapump actions between the same datasource/destinati

Can you not use something involving a global variable rowset?

Something like this

How to loop through a global variable Rowset
(http://www.sqldts.com/default.aspx?298)

--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - SQL Server 2005 Integration Services.
www.Konesans.com


"Patrick" <questions@xxxxxxxxxxxxxxxx> wrote in message news:5BA9D6B7-98FE-41FB-A19C-6C8A39B70D5A@xxxxxxxxxxxxxxxx
>I need to repeatedly call 3 stored procedures (only the first 2 stored
> procedure need to be called repeatedly) on a Sybase ASE 11 database to pull
> data into a SQL Server database table (with different parameters):
> e.g.
> exec p_PullDataForDates1 20050101,20050131
> exec p_PullDataForDates1 20050201,20050228
> exec p_PullDataForDates2 20050101,20050131
> exec p_PullDataForDates2 20050201,20050228
> exec p_PullDataForDates3 20050301,20050331
>
>
> I currently have a configureTask ActiveX Task prior to the DataTransfer/pump
> task that configure the SourceSQLStatement for the 2 pumps (to fill in the
> blanks for the stored procedure parameter).
>
> Questions:
> 1) Would it be the correct way to loop through by
> 1.1) Adding a global variable to denote which date I have done/am doing
> 1.2) Add workflow ActiveX script to the Pump task:
> Function Main()
> If DTSGlobalVariables( "gvCurrentDate").Value > DTSGlobalVariables(
> "gvLastDate").ValueThen
> Main = DTSStepScriptResult_DontExecuteTask
> else
> 'Rewire back to the configurePackage task to re-set stored
> proc parameters??
> DTSGlobalVariables.Parent. _
> Steps("DTSStep_DTSActiveScriptTask_1"). _
> ExecutionStatus = DTSStepExecStat_Waiting
> Main = DTSStepScriptResult_ExecuteTask
> End if
> 2) As I need to call the first 2 Stored Procedures repeated
> 2.1) Can I between the DataSource (UDL file pointing to a Sybase DB), and
> the destination (SQLServer), have *three* DataTransfer/pump tasks, each
> corresponding to a Stored Procedure call?? (I would add a global variable to
> stop the 3rd stored procedure from being called repeatedly)
> 2.2) If I can have 3 pumps between the datasource and destination, and
> select under Package Properties- Limit the maximum number of tasks executed
> in parallel=1, would I only get the DTS to instantiate one connection to
> sybase at a time (which is what i want)
> 2.3) Where do I put my ActiveX Script to do the looping (agains the pump for
> the first SP, second SP or the third SP??)
>
> Note the DTS is currently for SQLServer 7.0, although upgrading to SQLServer
> 2000 soon.


.

Relevant Pages

  • Re: Creating a view that uses a sp to retrive data
    ... exec msdb..sp_help_jobhistory') ... >>A view can call a stored procedure via loop back linked server. ...
    (microsoft.public.sqlserver.programming)
  • Re: noise words, @@ERROR, and stop and resume indexing
    ... EXEC sp_fulltext_catalog 'adsfull', 'stop' ... > 1) check noise words inside stored procedure ... > can be solved by changing the language specific file with noise words ... ...
    (microsoft.public.sqlserver.fulltext)
  • source text file as input parameter from a stored procedure
    ... I am using an active X script in combination with a global ... transform task that loads data from a .txt file to a table. ... I can also tell that the stored procedure is correctly passing in the ... EXEC sp_OAGeterrorinfo @object, @hr ...
    (microsoft.public.sqlserver.dts)
  • Re: Stored Procedure Fails Only When Called By Agent
    ... Stored Procedure 1: ... EXEC sp_addlinkedserver '10.1.2.10', 'SQL Server' ... LOCALMACHINE\myaccount/pass3, the same account that owns the job. ...
    (microsoft.public.sqlserver.replication)
  • Re: Please Help
    ... Remember, he wants to do this in a stored procedure, thus preventing the use ... exec sp_addlogin @user,@user ... > --create new sql login ... > --add to pubs datareader role ...
    (microsoft.public.sqlserver.security)