Adding Active X Script to SSIS to influence a Data Transformation

Question

Adding Active X Script to SSIS to influence a Data Transformation

FistralAllstar

SSC Veteran

Points: 206
More actions
March 31, 2009 at 5:09 am

#129007

I am currently in the process of Migrating some existing DTS packages on SQL 2000 across to SQL 2005 and whilst some of the migrating has involved re-building the process manually, I am looking to re-create an Active X script within my new SSIS package.
Previously we had a process in place, which would first delete all the records from a table, and then import an up-to-date file from a specific location into a table on our SQL database. Within this was an active x data transformation which would only pick up the file, if the datemodified was not less than the current date. The file would run every night and it was important that we only picked up the file ran on that day. An example of the script used, is shown below:
Function Main()
Dim fs, g, t
Set fs = CreateObject("Scripting.FileSystemObject")
Set g = fs.GetFile("filelocation\filename.txt")
t = g.DateLastModified
IF t > Date Then
Main = DTSTransformStat_OK
End If
End Function
I was never responsible for writing this script as I have never had much experience with VB, but as SSIS uses VB.Net i was wondering if anyone could point me in the right direction of getting this to work. It just keep failing out everytime i run it. I have selected Active X script from the SSIS control function and simply copy and pasted the script but it's telling me the Function is not found?
Regards
Dan

Viewing 4 posts - 1 through 3 (of 3 total)

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

COZYROC One Orange Chip Points: 28499 More actions · Answer 1

FistralAllstar (3/31/2009)
I am currently in the process of Migrating some existing DTS packages on SQL 2000 across to SQL 2005 and whilst some of the migrating has involved re-building the process manually, I am looking to re-create an Active X script within my new SSIS package.
Previously we had a process in place, which would first delete all the records from a table, and then import an up-to-date file from a specific location into a table on our SQL database. Within this was an active x data transformation which would only pick up the file, if the datemodified was not less than the current date. The file would run every night and it was important that we only picked up the file ran on that day. An example of the script used, is shown below:
Function Main()
Dim fs, g, t
Set fs = CreateObject("Scripting.FileSystemObject")
Set g = fs.GetFile("filelocation\filename.txt")
t = g.DateLastModified
IF t > Date Then
Main = DTSTransformStat_OK
End If
End Function
I was never responsible for writing this script as I have never had much experience with VB, but as SSIS uses VB.Net i was wondering if anyone could point me in the right direction of getting this to work. It just keep failing out everytime i run it. I have selected Active X script from the SSIS control function and simply copy and pasted the script but it's telling me the Function is not found?
Regards
Dan

The code used in ActiveX is VBScript. The SSIS uses VB.NET . So you cannot use the same exact code. But the code is similar. Check below:

Imports System.IO

...

Public Sub Main()

Dim result As Integer

Dim inputDate As Date = Convert.ToDateTime(Dts.Variables("InputDate").Value)

Dim t As Date = File.GetLastWriteTime("filelocation\\filename.txt")

result = Dts.Results.Failure

If t > inputDate Then

result = Dts.Results.Success

End If

Dts.TaskResult = result

End Sub

You have to setup an InputDate variable for the script where you specify the date after which you want process.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

FistralAllstar SSC Veteran Points: 206 More actions · Answer 2

I'm a bit confused what you mean about the Input date? Can you give me an example? I want the script to look at the current date each time the script is run rather than manually enter a date.

I am very sorry about this. My understanding on VB is limited and i'm trying to work through this. Thank you for the script you have sent. It is very much appreciated!

Kind Regards

Dan

COZYROC One Orange Chip Points: 28499 More actions · Answer 3

Oh, then in this case use the script below. It will use the current date/time for comparison. Ignore the InputDate variable.