January 16, 2007 at 4:15 pm
Hi all.
Is it possible to call an Execute Process Task from a Script Task?
My scenario: I'm converting a DTS to SSIS that made heavy use of ActiveX tasks. I know vb.net fairly well some I'm converting them to Script Tasks.
In one of them it downloads a file, called JobDone.txt, from a mainframe. The file has one row in it containing the date and time that a job completed on a mainframe. It reads the first line of the file into a variable and checks to see if it matches today's date. If not, it sleeps for five minutes and then downloads the file again and checks the date, etc... keeps doing this for 2 hours each morning before start of business hours. If this step is successful at anypoint it continues with the rest of the DTS.
My current problem is that on the 2005 server our infrastructure team does not want to allow the use of xp_cmdshell, which is how I was doing the FTP of the JobDone.txt file in the ActiveX script. So, in the SSIS package I have everything working so far except the FTP part. I just need to download the file. I have an Execute Process Task setup to download it. If there is a way to call it while looping in the Script Task that would be ideal, or is there another way in the Script Task to do the FTP part? I looked into using a 3rd party DLL for the FTP, but it would require putting into the global assembly cache on the server, and that is also not allowed.
Thanks!
January 17, 2007 at 7:56 am
There is no way to "call" another task from within a Script Task. Given what you've said I'm sure there is another way around this though. What is your script task actually doing? Can you not do the looping using SSIS's For Loop Container?
-Jamie
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson
January 17, 2007 at 9:25 am
When I went back to working on it this morning I realized I could just use the Shell command and it's working perfectly. Here is the code in case anyone gets any use out of it...
Imports System
Imports System.Data
Imports System.IO
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
Public Sub Main()
Dim LastRunDate As Date
Dim TodaysDate As Date = Now.Date
Dim conn As New System.Data.SqlClient.SqlConnection
Dim SQLstr As String = String.Empty
Dim DA As New System.Data.SqlClient.SqlDataAdapter
Dim sqlcmd As New System.Data.SqlClient.SqlCommand
Dim errorText As String = String.Empty
Dim RunTimeLimit As Integer = 0
Dim DelayInSeconds As Integer = 0
Dim Developer As Boolean = False
Dim m_FilePath As String = String.Empty
Select Case System.Net.Dns.GetHostName
Case "CXXXXXXXXXX" 'SIT
conn.ConnectionString = "XXXXXXXXXXXX"
RunTimeLimit = 5 '5 Minutes
DelayInSeconds = 60 '60 Seconds
m_FilePath = "F:\LZ\OMART\CSDB_files\JobDone.txt"
Case "CXXXXXXXXXX" 'UAT
conn.ConnectionString = "XXXXXXXXXXXX"
RunTimeLimit = 120 '2 Hours
DelayInSeconds = 300 '5 Minutes
m_FilePath = "F:\LZ\OMART\CSDB_files\JobDone.txt"
Case "CXXXXXXXXXX" 'PROD
conn.ConnectionString = "XXXXXXXXXXXX"
RunTimeLimit = 120 '2 Hours
DelayInSeconds = 300 '5 Minutes
m_FilePath = "F:\LZ\OMART\CSDB_files\JobDone.txt"
Case "CXXXXXXXXXX" 'COP
conn.ConnectionString = "XXXXXXXXXXXX"
RunTimeLimit = 120 '2 Hours
DelayInSeconds = 300 '5 Minutes
m_FilePath = "F:\LZ\OMART\CSDB_files\JobDone.txt"
Case Else 'Developer Workstation so connect to DEV
conn.ConnectionString = "XXXXXXXXXXXX"
RunTimeLimit = 1 '1 Minute
DelayInSeconds = 10 '10 Seconds
m_FilePath = "F:\LZ\OMART\CSDB_files\JobDone.txt"
Developer = True
End Select
SQLstr = "SELECT MAX(LastRunDate) AS LastRunDate FROM dbo.tbl_DTS_Log WHERE (Status = 'Success')"
DA.SelectCommand = sqlcmd
sqlcmd.CommandText = SQLstr
sqlcmd.CommandType = System.Data.CommandType.Text
sqlcmd.Connection = conn
conn.Open()
Dim DS As New DataSet
Try
DA.Fill(DS, "Table1")
Catch ex As Exception
errorText = ex.ToString
Finally
conn.Close()
End Try
If Developer And errorText <> "" Then
System.Windows.Forms.MessageBox.Show(conn.ConnectionString, "Connection String")
System.Windows.Forms.MessageBox.Show(errorText, "Error Text")
End If
If errorText = String.Empty Then
For Each row As DataRow In DS.Tables("Table1").Rows
LastRunDate = CType(row.Item("LastRunDate"), Date)
Next
If Developer Then
System.Windows.Forms.MessageBox.Show(FormatDateTime(LastRunDate, DateFormat.ShortDate), "Last OMART Run Date")
System.Windows.Forms.MessageBox.Show(FormatDateTime(TodaysDate, DateFormat.ShortDate), "Todays Date")
End If
' If Dates match exit with failure
If FormatDateTime(TodaysDate, DateFormat.ShortDate) = FormatDateTime(LastRunDate, DateFormat.ShortDate) Then
Dts.TaskResult = Dts.Results.Failure
Else
' Dates don't match, keep processing
Dim StartTime As DateTime
Dim StopTime As DateTime
Dim FileDate As Date
' Set StartTime and StopTime
StartTime = Date.Now
StopTime = DateAdd(DateInterval.Minute, RunTimeLimit, StartTime)
' Attempt to get the job done file until the time limit is reached
Do While Date.Now < StopTime
' Call Task to FTP file
Shell("ftp -s:F:\LZ\ftp\CSDB_files\Job_Done_File.txt",
AppWinStyle.MinimizedNoFocus, True)
' Check for file existance
If File.Exists(m_FilePath) Then
Dim sr As StreamReader = File.OpenText(m_FilePath)
Dim filetext As String = String.Empty
' Last line in file contains the date & time of when the mainframe
' job finished.
Do While sr.Peek() >= 0
filetext = sr.ReadLine()
Loop
sr.Close()
If filetext <> "" Then
' File found and has data, set last line read in as the date
FileDate = CDate(filetext)
Else
' File is there but no data found so set file date to 1/1/1900
FileDate = CDate("1/1/1900")
End If
' Done working with file, delay for 10 seconds, to make sure it's
' not still in use and then delete it.
Delay(10)
File.Delete(m_FilePath)
Else
' File not there yet so set file date to 1/1/1900
FileDate = CDate("1/1/1900")
End If
If Developer Then
System.Windows.Forms.MessageBox.Show(FileDate.ToString, "File Date")
System.Windows.Forms.MessageBox.Show(TodaysDate.ToString, "Today")
End If
If FileDate = TodaysDate Then
' File is for today, ok to continue with rest of DTS
Dts.TaskResult = Dts.Results.Success
Exit Do
Else
' Set step to failure in case we've reached the time limit,
' sleep, then loop again.
Dts.TaskResult = Dts.Results.Failure
If Developer Then
System.Windows.Forms.MessageBox.Show("Sleeping...")
End If
' Sleep for X Seconds
Delay(DelayInSeconds)
End If
Loop
End If
Else
Dts.TaskResult = Dts.Results.Failure
End If
End Sub
#Region " Delay "
Public Function Delay(ByVal PauseTime As Integer) As Object
Dim StartTime As DateTime
Dim EndTime As DateTime
StartTime = Date.Now
EndTime = DateAdd(DateInterval.Second, PauseTime, StartTime)
Do While Date.Now < EndTime
'ZZZZZZ...ZZZZZZ......ZZZZZ....ZZZZZ...ZZZZZ....
Loop
End Function
#End Region
End Class
January 17, 2007 at 9:33 am
Hi Jake,
I'm glad you got this working but I'm interested to know why you put so much logic into the Script Task. Was it not an option to use some of the workflow capabilities of SSIS (e.g. conditional workflow, For Loop).
-Jamie
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson
January 17, 2007 at 9:48 am
I've only been working with SSIS for about 2 weeks now and have a client set deadline to get 5 DTS packages converted by the end of the month. I've completed 3 of them now. Given the timeline I'm forced to do it the quickest way I know how and that's using vb, as I learn more about SSIS I'll be able to go back and make improvements to these packages.
January 17, 2007 at 11:28 am
OK cool. That makes sense. Good luck with it.
-Jamie
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply