Need Help to create a SSIS package.

[SQLDallas]

I got a DTS Package which was created in SQL 2000 using Activex Script. I need to create the same package in SQL 2005 using the same script. How can I write the same Activex Script in SSIS package?
I can provide the Activex script and DTS package if anybody wants.
It's just killing me! Is there anyone who can help me?

 

[AlexCuse]

You will need to write your activeX script (I presume it is vbScript?) in vb.net to use it in the new script task.

I believe there is a task to allow you to run the old activeX scripts available as well, but I would strongly recommend rewriting in vb.net.

Ignorance of certain subjects is a great part of wisdom

 

[SQLDallas]

Thank you Alex. I am new in VB and I did not write that ActivexScript(VB) in the DTS package and I do not know how to write that script in VB.Net. I am attaching one script here so that you can guide me. I really appriciate your time and effort. Thank you.
*******
'*******************************************************************************
' Visual Basic ActiveX Script
'
' This script determines is a previous archive of the Servers Alive log file
' was previously completed for the requested month. If the requested month
' was not previous archived, the processing continues to archive the data.
'*******************************************************************************

'*******************************************************************************
'
'*******************************************************************************

Function Main()

dim objFSO 'file system object to verify archive file does not exist
dim objEmail 'email object for error messages
dim strArchiveMonth 'requested month to archive
dim strArchiveBegin 'date to begin archiving data
dim strArchiveEnd 'date to end archiving data
dim strArchiveLog 'date to instert in log file name
dim strArchiveFile 'archive file name
dim strArchiveDir 'archive directory name
dim dtCurrDate 'current date
dim dtWorkDate 'work field for calculating archive date
dim strWorkYYYY 'work field for archive year
dim strWorkMM 'work field for archive month
dim strArchiveDay 'day for archiving
dim strErrorSubject 'error email subject
dim strErrorbody 'error email body
dim strWorkArchiveName 'full name of archive file

gv_ArchiveMonth = DTSGlobalVariables("gv_ArchiveMonth").Value
dtCurrDate = date()
strArchiveDay = "/01/"
strErrorSubject = ""
strErrorbody = ""

If gv_ArchiveMonth = "mm/yyyy" Then
'Default archive date (mm/yyyy) supplied, archive log data from month three months past
dtWorkDate = dateadd("m", -4, dtCurrDate)
strWorkYYYY = CStr(year(dtWorkDate))
strWorkMM = CStr(month(dtWorkDate))

'Make sure month is two digits to ensure consistent archive file name
If len(strWorkMM) = 1 Then
strWorkMM = "0" & strWorkMM
End If
strArchiveBegin = strWorkMM & strArchiveDay & strWorkYYYY
strArchiveLog = strWorkYYYY &strWorkMM
dtWorkDate = dateadd("m", -3, dtCurrDate)
strWorkYYYY = CStr(year(dtWorkDate))
strWorkMM = CStr(month(dtWorkDate))
strArchiveEnd = strWorkMM & strArchiveDay & strWorkYYYY
Else
'Specific month requested to be archived
strWorkMM = left(gv_ArchiveMonth, 2)
strWorkYYYY = right(gv_ArchiveMonth, 4)
dtWorkDate = strWorkMM & strArchiveDay & strWorkYYYY

If IsDate(dtWorkDate) and strWorkMM < CStr(Month(dtCurrDate)) and strWorkYYYY <= CStr(Year(dtCurrDate)) Then
'Valid date format supplied
strArchiveBegin = dtWorkDate
strArchiveLog = strWorkYYYY & strWorkMM
dtWorkDate = DateAdd("m", 1, strArchiveBegin)
strArchiveEnd = CStr(Month(dtWorkDate)) & strArchiveDay & CStr(Year(dtWorkDate))
Else
'Invalid date format must be mm/yyyy and less than current month

strErrorSubject = "SA Archive Error - Invalid Date Parameter"
strErrorbody = "Servers Alive log table archive request failed due to invalid date parameter of " & gv_ArchiveMonth & " supplied. Correct parameter input for the gv_ArchiveMonth global variable of DTS package, Servers Alive - Archive, then re-execute package."
End If
End If

If strErrorSubject = "" Then
'Determine if archive file already exists
Set objFSO = CreateObject("Scripting.FileSystemObject")
strArchiveFile = DTSGlobalVariables("gv_ArchiveFile").Value
strArchiveDir = DTSGlobalVariables("gv_ArchiveDir").Value
strWorkArchiveName = strArchiveDir & strArchiveFile & strArchiveLog & ".csv"

If objFSO.FileExists(strWorkArchiveName) Then
strErrorSubject = "SA Archive Error - Archive File Exists"
strErrorbody = "Servers Alive log table archive request failed because " & strWorkArchiveName & " exists."
End If
End If

'Process error
If strErrorSubject = "" Then
'Set global variables to use later to archive SA log file
DTSGlobalVariables("gv_StartDate").Value = strArchiveBegin
DTSGlobalVariables("gv_EndDate").Value = strArchiveEnd
DTSGlobalVariables("gv_LogFile").Value = strArchiveFile & strArchiveLog & ".csv"
DTSGlobalVariables("gv_LogDate").Value = strArchiveLog

Main = DTSTaskExecResult_Success
Else
'Set archive request month/year back to default
DTSGlobalVariables("gv_ArchiveMonth").Value = "mm/yyyy"
'Send error email
Set objEmail = CreateObject("CDO.Message")
objEmail.From = DTSGlobalVariables("gv_ErrorFrom").Value
objEmail.To = DTSGlobalVariables("gv_ErrorTo").Value
objEmail.Subject = strErrorSubject
objEmail.Textbody = strErrorbody
objEmail.Send

Main = DTSTaskExecResult_Failure
End If

End Function

 

[SQLBill]

SQLDallas,

Just as an FYI, there is a Microsoft SQL Server: SSIS forum (FORUM1555).

-SQLBill

Posting advice: FAQ481-4875

 

[AlexCuse]

Just as another FYI, asking someone to rewrite that script in VB.net is a rather tall order.

I suggest you have a look here: SSIS Backwards Compatibility

and here: Execute DTS 2000 Package Task

I'm not sure if it will work for you, because I don't think SSIS' object model supports Global Variables anymore (can you tell I haven't started converting yet), but it is worth a try.

If you can't execute the package in this fashion, take a look in the SSIS forum, but I think you may need to bring in a consultant (or find someone within your company familiar with VBScript or VB.net) to help you convert your package.

Hope this helps,

Alex




Ignorance of certain subjects is a great part of wisdom