November 30, 2008 at 9:06 am
here goes the activex script in dts. I want to migrate this script to ssis.
Should i use activex script task of dts or script task. and how can i assign value to a variable dynamically. Please help
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main()
'Declare the variables for the backslash
Dim oDTSPackage,oConnection ,sPath
Const csBackslash = "\"
sPath= DTSGlobalVariables("gvcFilePath").Value
MsgBox DTSGlobalVariables("gvcFilePath").Value
If Right(sPath, 1) <> csBackslash Then
sPath = sPath & csBackslash
End If
' Set the DTS object.
Set oDTSPackage = DTSGlobalVariables.Parent
DTSGlobalVariables("gvcFilePath").Value = sPath
sFileName = DTSGlobalVariables("gvcFileName").Value
oDTSPackage.Connections("S & P Entity Daily").DataSource = sPath & sFileName
'The S&P feed files are located in the seperate directory. the directory name is in the format yyyy-mm-dd.
Dim sDirectoryPrefix
Dim sDate
sDirectoryPrefix = "DAILY\GISFEntity_TXT\"
sDate = mid(DTSGlobalVariables("gvcFileName").Value, 33,10) & "\"
DTSGlobalVariables("gvcFTPSourcefilename").Value = sFileName &";" & sDirectoryPrefix & sDate & ";;"
Main = DTSTaskExecResult_Success
End Function
November 30, 2008 at 5:29 pm
You need to rewrite this in script task using VB.Net.You cannot migrate this kind of activeXscript to SSIS
November 30, 2008 at 9:09 pm
Thanks! How will I dynamically assign the value of variables.
I want source path and source file name to be assigned at the run time. that is wat the script does.
December 1, 2008 at 6:49 am
Public Sub Main()
Try
Dim gvLogFile As String
gvLogFile = Dts.Variables("gvLogFile").Value.ToString()
Dim gvStrVSP As String
gvStrVSP = Dts.Variables("gvStrVSPDir").Value.ToString() ''"U:\Information Technology\Private and PHI\Datawarehouse\VSP\vsptest" '''
' MsgBox("1")
Dim FILE_NAME As String = gvLogFile
Dim objWriter As New System.IO.StreamWriter(FILE_NAME, True)
Dim mon As Integer
Dim yr As Integer
Dim dd As Integer
Dim RunDt As String
Dim pdd As String
Dim PdDt As String
Dim StrFileee As String
Dim StrFile As String
If Not File.Exists(gvLogFile) = True Then
File.Create(gvLogFile)
objWriter.WriteLine("New File Created")
End If
If System.IO.File.Exists(FILE_NAME) = True Then
'MsgBox("FileExists")
objWriter.WriteLine("Package started at " & DateAndTime.Now)
objWriter.WriteLine("In lastFile Exists Routine " & DateAndTime.Now)
'objWriter.Close()
'MsgBox("gvStrVSP)
Else
objWriter.WriteLine("File Does Not Exist")
End If
Dim FolderFile As FileInfo() = Nothing
Dim objcurrentdir As New DirectoryInfo(gvStrVSP)
Dim tempFile As FileInfo
FolderFile = objcurrentdir.GetFiles()
Dim strFilename As String '= tempFile.ToString()
strFilename = ""
For Each tempFile In FolderFile
strFilename = tempFile.ToString()
' MsgBox("strFile to Temp")
If strFilename.Substring(0, 3) = "VSp" And strFilename.Substring(strFilename.Length - 4, 4) = ".txt" And (strFilename.Length = 15) Then
' MsgBox(strFilename)
StrFileee = Trim(Left(strFilename, strFilename.Length))
StrFile = gvStrVSP & "\" & StrFileee
MsgBox("TrimTxtFil+" & StrFile)
'Dim mon As Integer
'Dim yr As Integer
'Dim dd As Integer
'Dim RunDt As String
'Dim PdDt As String
''''mon = strFilename.Substring(8, 2)
mon = Convert.ToInt32(strFilename.Substring(7, 2).ToString())
' MsgBox(mon)
yr = Convert.ToInt32(strFilename.Substring(3, 4).ToString())
'MsgBox(yr)
dd = Convert.ToInt32(strFilename.Substring(9, 2).ToString())
'MsgBox(dd)
RunDt = yr & "-" & mon & "-" & dd
MsgBox(RunDt)
If (IsDate(RunDt) = False) Then
objWriter.WriteLine("File has an incorrect date format")
Dts.TaskResult = Dts.Results.Failure
Exit Sub
End If
Select Case mon
Case 1, 3, 5, 7, 8, 10, 12
pdd = "31"
Exit Select
Case 4, 6, 9, 11
pdd = "30"
Exit Select
Case 2
If (yr Mod 4) = 0 Then
pdd = "29"
Else
pdd = "28"
End If
End Select
PdDt = yr & "-" & mon & "-" & pdd
objWriter.WriteLine("RunDt:" & RunDt)
objWriter.WriteLine(StrFileee & "Detected" & DateAndTime.Now)
objWriter.WriteLine("Source:" & StrFile)
objWriter.WriteLine(" File closed " & DateAndTime.Now)
objWriter.Close()
End If
' Dim vars As Variables
'Dts.VariableDispenser.LockOneForWrite("gvRunDt", vars)
'vars(0).Value = RunDt
'vars.Unlock()
StrFile = CStr(Dts.Variables("gvFileName").Value)
StrFile = CStr(Dts.Connections("sample").ConnectionString)
'Dts.Connections("sample").ConnectionString = CStr(StrFile)
RunDt = CStr(Dts.Variables("gvRunDt").Value)
'MsgBox(Dts.Variables("gvRunDt").Value)
PdDt = CStr(Dts.Variables("gvPdDt").Value)
'Dts.Variables("gvPdDt").Value = PdDt
'Dts.Variables("gvFileName").Value = StrFile
Next
Dts.TaskResult = Dts.Results.Success
Catch ex As Exception
MsgBox("Error Message: " + ex.Message())
End Try
End Sub
End Class
Hope this might help you.........It will have al the things go through it carefully..........
December 1, 2008 at 11:18 pm
Thanks Sudheer !
I am trying the same thing, but i want to create the variable as Global.
I have created two ScriptTasks (ST1 and ST2) and "Package Level" variable Test and initialized to 1.
Now, In ST1 i have changed the value of Test = 0 (Dts.Variables("Test").Value=0). When i read the "Test" in ST2 i get the value as 1 only.
In other words i want to create Test as something like reference variable in C++.
Can you please help me how can i do that.Thank You.
December 2, 2008 at 6:33 am
Did you wite that variable in readwritevariable in script task editor..
ReadWriteVariables:Globalvar1,globalvar2
The variable which you want to change the value at run time
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply