dba rookie - dts automation

Question

dba rookie - dts automation

Annie G.

Mr or Mrs. 500

Points: 569
More actions
November 15, 2006 at 12:09 pm

#76609

Greetings from a dba rookie; I’m a mainframe convert and need help!
I have been given the task to set up and automate a dts that takes a .txt file, which can contain any date, compare it to the current date. If the ‘days’ are equal then run a 2^nd dts if not, continue comparing the dates until they match. I was able to load the .txt date into my table, and tried using the following ActiveX to code test the condition, but it errors out with ‘Invalid procedure call or argument: ‘datadiff’.
Function Main()
if datediff(dd, pk12_date, current_timestamp) = 0 Then Main = DTSTaskExecResult_Success
End Function

Thanking you in advance, Annie G.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Vic Rauch-303403 SSCommitted Points: 1576 More actions · Answer 1

Annie,

The dd in your datediff code, what is it? Is it a variable that contains the value "d"? Or are you trying to pass a "d" to datediff with the dd being the actual value? If dd is not a variable, then you will need to replace the dd with "d".

HTH, Vic

[font="Comic Sans MS"]Vic[/font]
www.vicrauch.com

Annie G. Mr or Mrs. 500 Points: 569 More actions · Answer 2

Hi Vic,

Thank you for responding, you are my first cyber contact;-)

The 'dd' is the DATEPART parameter for 'day' that specifies which part of the date to calculate the difference. If the .txt day is not equal to current_timestamp then I must automate it to keep checking until both days match. I also tried using getdate() with the day parameter, but receive an error 'wrong number of arguments or invalid property assignment: 'day'

Thanks, Annie G.

Cdarck SSC Enthusiast Points: 174 More actions · Answer 3

The sintax should be vbscript while you working with ActiveX. Not Sql. Try this, will work.

Function Main()

if datediff("d", pk12_date, current_timestamp) = 0 Then Main = DTSTaskExecResult_Success

End Function

'Cdrack.

Annie G. Mr or Mrs. 500 Points: 569 More actions · Answer 4

You made my day!!! just when I was about to convert back to the mainframe (only kidding;-):

Now for part II, since the .txt file gets created every day at 4:00 pm ET, what would be the best logical way to 'continue' testing for the datediff, since the next dts should run ONLY when the conditions is true.

thanks in advance for 'sharing the knowledge'

Annie G.

Annie G. Mr or Mrs. 500 Points: 569 More actions · Answer 5

Hello again,

The 'datediff' syntax in ActiveX didn't error out, but when I execute the complete dts, when the pk12_date (11/22/2006) is not the same as the current_timestamp, it shouldn't execute the next dts, but it does...

I only want the next dts to execute if both dates match, but it's not happening...any ideas???

Vic Rauch-303403 SSCommitted Points: 1576 More actions · Answer 6

When I have a situation like this, that is something happens that I can see can not happen, I do a "Debug.Print pk12_date, current_timestamp, DateDiff("d", pk12_date, current_timestamp)" This will print (to the "debug" window, press Ctrl/G to get the "debug" window) the two dates and the result of the math so you can view it and see what is wrong.

HTH, Vic

[font="Comic Sans MS"]Vic[/font]
www.vicrauch.com

Annie G. Mr or Mrs. 500 Points: 569 More actions · Answer 7

Hello Vic,

Forgive my ignorance, but where exactly I can issue this command from? I normally remote to the server I'm working on and when I press the ctrl/g nothing happens!

Annie G. Mr or Mrs. 500 Points: 569 More actions · Answer 8

Hello cyber world,

I was able to resolve the date comparison issue by using the 'Dynamic Properties Task's Globalvariables' to assign the date loaded in the .txt file, and then using ActiveX to test the condition.

Function Main()

if datediff("d", DTSGlobalVariables("TestDate").Value, Now) = 0 then

'if 1=1 then

Main = DTSTaskExecResult_Success

else

Main = 100 / 0 'DTSTaskExecResult_Failure

end if

End Function

My dilema now is that I need to set a condition, that when the .txt date and current date match, run a 2nd dts otherwise keep 'looping'. Any ideas how or where, will be greatly appreciated!

Thanks Annie G.