Workflow question
-
In my DTS package I have two connections:
GA2_remote, GA2_local
Let's say I have a Data Pump.
Then on successful completion of this data transfer
I want to do another Data Pump with the same
GA2_remote and GA2_local connections.
Can I do that?
If yes how do I control the workflow?
How do I say SQL Server that I want first
to run this data transformation and then that?
-
Just make copies of your current connections and then create another datapump task between the new connections.
Once you have the two Datapumps defined, create an "On Success" workflow betwen them.
You can acheive the same affect without copying the connections, but I think this way better illustrates what the package is doing.
--------------------
Colt 45 - the original point and click interface
-
Cant you just create multiple tasks between the two original connections, then create an 'On Completion' or 'On Success' workflow running back from the target connection to the source connection.
Double click the workflow line to get the properties up and you will find it gives you the options to set the source step, precedence and destination step for each of the tasks you have defined.
e.g. you have set 3 tasks between 2 connections (tasks - t1, t2 and t3). Create a workflow back from the destination connection to the source. Set the first source step to t1, precedence to your requirement, set Destination step to t2. Set the second source step to t2, precedence to your requirement, set destination step to t3. Delete all other options in the precedence window. This should fire all your tasks in the correct order.
I do agree with multiple connection objects making the dts easier to read. Depends on the complexity and size of the package sometimes though.....
Steve
We need men who can dream of things that never were.
-
Hi Steve,
Can you looak at
http://www3.sympatico.ca/3005/support/dts1.html
Is it what you mean?
I'm a little confused about workflow part.
Thanks,
Robert
-
Follow up with an ActiveX script and set the first task to waiting if what you want is to loop. You could also dynamically change the Source SQL string before you loop. Write your SQL string in an ActiveX, assign it to a Global string, then assign it to the transformation with the Dynamic properties task. You know you can also run transformations in parallel - no need run in succession unless there is a dependency issue.
[font="Courier New"]ZenDada[/font]
-
Ahhh... just re-read the question. Yes, Steve is absolutely right, if it's as simple as only a set of transformations between two connections, you put one "candy-cane" (workflow) between them pointing backwards. Then you can manage the order of all the transformations with just the one candy-cane. Use the green one if you need success, the purple one if you just need completion as your precedence.
If you have several steps in a package, the set-task-to-waiting approach is the way to loop an entire process. Say you have a list of report dates that you need to process - you can increment your way through your date range with a Global Date variable, and re-run the entire package until you use all your dates.
I also like parallel processing a lot. Great way to save time!
[font="Courier New"]ZenDada[/font]
-
Hi TnJMoseley,
I'm not sure I understand you.
What loop you are talking about?
My task is to transfer 10 tables but not the whole tables.
They are huge. I have some logic to reduce the number of records.
And I want to do a transfer nicely. Not run them all parallel.
I want to make sure t1 was transferred OK. Only then start transferring t2.
My problem is that the first transfer of t1 is my main criteria
and all subsequent transfers should be based on t1.ID
It would be nice to store t1.ID in a global virtual table and use it thoughout
the whole DTS.
Is it possible?
-
You bet!
After the first transformation, use an Exec SQL task to capture the id you need and select it as an output variable. Create copies of your two connections and do the rest of your transformations between those:
(trans 1) > (assign Global) > (trans 2-10)
Cx1 > Cx2 > ExecSQL > Cx1 > Cx2
In transformations 2 - 10, just put a question mark, ? , where you want the variable to be read in your SQL string. Assign your Global as an input variable in these transformations and it will replace the ? when it runs.
[font="Courier New"]ZenDada[/font]
-
Can you direct me to some good description of this technique?
I've never used Global Variables.
You said:
Exec SQL task to capture the id you need and select it as an output variable...
This output variable is an array? Cos my first transformation will return 12,000 records?
-
Oh, and you need 12000 id's? I thought you needed a single value. Well yes, you can store lists/tables/arrays as globals, but you may want to consider another approach. If you get Wrox SQL Server 2000 DTS, you can teach yourself within a couple of days enough to be very good at DTS. Don't let the size of the book intimidate you. The first few chapters are the basics. After that they get into ETL and Warehousing concepts.
First try opening the output variable GUI and playing around with it. You may just figure it out. Personally, I thought it was not very intuitive and needed the book.
Here is an excerpt for BOL that should get you started:
How to save an entire rowset into a global variable (Enterprise Manager)
To save an entire result set of a SELECT statement into a global variable using the Execute SQL task
From the Connection toolbar, drag a Microsoft® OLE DB Provider for SQL Server connection to the design sheet.
In the Database list, click pubs.
From the Task toolbar, drag an Execute SQL task to the Data Transformation Services (DTS) design sheet.
In the Execute SQL Properties dialog box, in the Existing connection list, click the pubs connection just created.
In the SQL statement box, type the following:
SELECT *
FROM titleauthor
Click Parameters, and then click Create Global Variables.
In the Name list, type Authors, and then in the Type list, click <other>. Leave the Value box empty.
Click the Output Parameters tab, click Rowset, and then in the Output Parameter Type list, select the Authors global variable.
This procedure assigns all records returned from the SELECT statement to be stored in the Authors global variable.
[font="Courier New"]ZenDada[/font]
-
Re:
Exec SQL task to capture the id you need and select it as an output variable...
This output variable is an array? Cos my first transformation will return 12,000 records?
You can't get an output variable from a transformation. You have to use an ActiveX script or an Execute SQL task. Use the Execute SQL task in this case. It's the thing that looks like a little yellow barrel with two red arrows.
[font="Courier New"]ZenDada[/font]
-
Ahaa!!!
So Global Variable in DTS can act as an array?
So in my transformations 2-10
how do I use Global Variable?
...WHERE RF_ID IN(select RF_ID from GlobalVariableName) ?
I'll go to "World's Biggest Book Store" on lunch
and buy that Wrox SQL Server 2000 DTS.
Thank you so much!
-
Hmmm... from what I just read in BOL...
To save an entire result set of a SELECT statement into a global variable using the Execute SQL task
From the Connection toolbar, drag a Microsoft® OLE DB Provider for SQL Server connection to the design sheet.
In the Database list, click pubs.
From the Task toolbar, drag an Execute SQL task to the Data Transformation Services (DTS) design sheet.
In the Execute SQL Properties dialog box, in the Existing connection list, click the pubs connection just created.
In the SQL statement box, type the following:
SELECT *
FROM titleauthor
Click Parameters, and then click Create Global Variables.
In the Name list, type Authors, and then in the Type list, click <other>. Leave the Value box empty.
Click the Output Parameters tab, click Rowset, and then in the Output Parameter Type list, select the Authors global variable.
This procedure assigns all records returned from the SELECT statement to be stored in the Authors global variable.
From the Task toolbar, drag a Microsoft® ActiveX® Script task onto the Data Transformation Services (DTS) design sheet.
In the ActiveX Script Properties dialog box, after the Function Main() statement, type the following Microsoft Visual Basic® Scripting Edition (VBScript) code:
dim countr
dim RS
set RS = CreateObject("ADODB.Recordset")
set RS = DTSGlobalVariables("Authors").value
for countr = 1 to RS.RecordCount
MsgBox "The author ID is " & RS.Fields("au_id").value
RS.MoveNext
Next
Main = DTSTaskExecResult_Success
On the design sheet, click the Execute SQL task, and then CTRL-click the ActiveX Script task.
On the Workflow menu, click On Success or On Completion.
The au_id column for each row returned from the SELECT statement is displayed.
Sounds like this is for use as an ADO recordset which you can loop through. But you can't access it directly in a sql query in a transformation. You could loop in the ActiveX and assign the id in each iteration to a global int. Then use that as an input variable. But 12000 times? Don't think that's what you really want.
Well, at least I leaned something new about the ADO recordset! I see THAT question asked a LOT on this forum!
Why don't you do this... stuff your 12000 id's into a temp table on the source connection and do an inner join on that with transformations 2-10? Then just drop the temp table when you are done.
[font="Courier New"]ZenDada[/font]
-
... stuff your 12000 id's into a temp table on the source connection and do an inner join on that with transformations 2-10? Then just drop the temp table when you are done.
Try it. It doesn't work.
This is what I inserted in transformation source connection:
--------------------------
SELECT RF_ID INTO #t1
FROM t1
SELECT * FROM #t1 INNER JOIN t1
ON #t1.RF_ID = t1.RF_ID
DROP TABLE #t1
--------------------------
I click Parse Query and get "Successfully parsed" message.
Destination:
-------------------------
CREATE TABLE [dbo].[t1] (
[RF_ID] [int] NOT NULL ,
[RISK_CLASS] [varchar] (15)
)
----------------------------
Then I click OK at the bottom ti finish Transformation and get an error:
"To continue, define transformations..."
-
Try separating the selects into two separate transformations. They need to go in opposite directions. You'll need a real table since you are going from one connection to another. If you were on the same connection for source and destination, you could create a global temp table as ##t in an Exec SQL task. Don't think that will work here though.
[font="Courier New"]ZenDada[/font]
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply