Sequence flow in Control Flow and GETDATE()

Question

I'm new to SSIS.  I'm trying to create some logging in my process.  In my Control Flow I have an Execute SQL (to insert to a log tbl using GETDATE()), a Data Flow Task (that inserts to the log tbl using GETDATE()), and a final Execut SQL (to insert to a log tbl using GETDATE()).  I'd like this to really tell me when my whole process started, when things happened 'in the middle (in the data flow task)', and when it actually finished.  Sadly enough, sometimes when the whole process flows, the datetimestamp on the start / finish Execute SQLs come BEFORE the Date flow task datetimestamps.  This seems weird to me.  Why don't the GETDATE() values return sequentially increasing values at the 3 places where I use them?

---

Dale Krabill 216-252-7300 x6484 American Greetings

Answer 1

Did you connect the start and finish Execute SQL Tasks with precedence constraints to the dataflow?

---

MCTS, MCITP - Please mark posts as answered where appropriate.
Answer #1: Have you tried turning it off and on again?
Answer #2: It depends...

Answer 2

I did connect the start / finish SQLs to the data flow task in the control flow.  Looks to me by default the precedence constraint is already set.

---

Dale Krabill 216-252-7300 x6484 American Greetings

Answer 3

Is it possible to post a screenshot of the control flow?

---

MCTS, MCITP - Please mark posts as answered where appropriate.
Answer #1: Have you tried turning it off and on again?
Answer #2: It depends...

Answer 4

Here's the screen shot of the control flow.

---

Dale Krabill 216-252-7300 x6484 American Greetings

Answer 5

Ok, that seems to be correct.

Where do you log the GETDATE() for the dataflow task?

---

MCTS, MCITP - Please mark posts as answered where appropriate.
Answer #1: Have you tried turning it off and on again?
Answer #2: It depends...

Answer 6

In the Execute SQL tasks I use the GETDATE() right in the SQL code itself.  In the data flow task I use a Derived Column to build 3 columns of info: name, comment and date.  I use the GETDATE() function to populate the date column.  Then I map these 3 columns to the actual tbl.  Attached is a screen-shot of the tbl and 'out of order' results.

---

Dale Krabill 216-252-7300 x6484 American Greetings

Answer 7

Well, I don't have a clue. Very strange behaviour...

(I do know that SSIS has accuracy problems with milliseconds)

---

MCTS, MCITP - Please mark posts as answered where appropriate.
Answer #1: Have you tried turning it off and on again?
Answer #2: It depends...

Answer 8

Whew!  I don't feel so bad in my ignorance then if an expert doesn't know.  Sometimes the dates are properly ordered in the log according to events and sometimes not.  Is there some kind of a timer I could put before / after the data flow task to 'wait a bit'.  (would be a little hokey, but if it would make it work right)

---

Dale Krabill 216-252-7300 x6484 American Greetings

Answer 9

You could use an Execute SQL Task with the WAITFOR DELAY clause:

http://msdn.microsoft.com/en-us/library/ms187331.aspx

---

MCTS, MCITP - Please mark posts as answered where appropriate.
Answer #1: Have you tried turning it off and on again?
Answer #2: It depends...

Answer 10

Thx for the WAITFOR tip.  I've implemented that with a 5 second wait.  It works.  A little hokey, but it works.  I do wonder if there's some timing issue with using GETDATE() as a derived column function rather than sql insert function.

---

Dale Krabill 216-252-7300 x6484 American Greetings

Answer 11

Thx for the WAITFOR tip.  I've implemented that with a 5 second wait.  It works.  A little hokey, but it works.  I do wonder if there's some timing issue with using GETDATE() as a derived column function rather than sql insert function.

---

Dale Krabill 216-252-7300 x6484 American Greetings

Actually it shouldn't matter. The precedence constraint tells the final SQL task that it can only start after the dataflow has successfully finished. The GETDATE() function in the derived column isn't that accurate, but the row should still be inserted before the other one.

Strange strange...

---

MCTS, MCITP - Please mark posts as answered where appropriate.
Answer #1: Have you tried turning it off and on again?
Answer #2: It depends...

Answer 12

Is there a better way to track the date as I'm going than using GETDATE()?

---

Dale Krabill 216-252-7300 x6484 American Greetings

Answer 13

You could use a default constraint on the data column using TSQL's GETDATE() (or sysdatetime() if you're using SQL Server 2008 or up).

---

MCTS, MCITP - Please mark posts as answered where appropriate.
Answer #1: Have you tried turning it off and on again?
Answer #2: It depends...

Answer 14

I'm running 2008.  Not sure what you mean about column constraint, though.

---

Dale Krabill 216-252-7300 x6484 American Greetings

Answer 15

I'm running 2008.  Not sure what you mean about column constraint, though.

---

Dale Krabill 216-252-7300 x6484 American Greetings

You can define a default expression on a column. If a row is inserted and a value wasn't specified for this column, this default expression is evaluated and inserted.

http://www.mssqltips.com/sqlservertip/1425/working-with-default-constraints-in-sql-server/

---

MCTS, MCITP - Please mark posts as answered where appropriate.
Answer #1: Have you tried turning it off and on again?
Answer #2: It depends...

Answer 16

Oh, you're talking about a default value on the tbl column itself.  Gotcha.  Thx!

---

Dale Krabill 216-252-7300 x6484 American Greetings