Please help with my VB Script in my DTS package -- sql server dts

Conan Kelly
12/30/2005 11:20:35 AM

Hello all,

I have a list of text data files (*.TXT) that I'm trying to import
into my DB. The files have the year and month the in the file name
(for example: ABC0301.TXT would be for the month of Jan-03). One of
the first steps in my package is an ActiveX Script Task that I want to
extract the year and the month from the file name, calculate the last
day of the month, and store it into a global variable so it can be
populated in to a "Month End Date" field in the table during import.

I think one of the biggest problems I'm having is getting strings to
be dates or strings to be integers. It appears that you can't use the
AS keyword in the VB Script when declaring variables to limit the data
types of those variables.

Following is my code. It keeps failing and I can't figure out why.
The error message I'm getting with this code is "Type Mismatch:
'pdteEOMDate' on line 33". I've tried to use DateSerial and DateValue
for pstrCurrentMonth and pstrMonthEnd, and I've also tried CInt for
pstrMonth with no luck. I still get the Type Mismatch error. It just
moves to different lines/variables each time I try something new.

(FYI: The Public Function EOMonth has previously worked, as is, in
ActiveX Script Transformations. Also, my Global variable "pstrMEDate"
is a DATE data type (not STRING), but I can't even get to that point
to know if that will cause problems or not).

Thanks in advance for any help anyone can provide,

Conan Kelly

'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************

Function Main()
dim pstrMonth
dim pstrYear
dim pstrFileName
dim pstrCurrentMonth
dim pstrMonthEnd

pstrFileName = DTSGlobalVariables("gstrFileName").Value
pstrYear = Mid( pstrFileName , 4 , 2 )
pstrMonth = Mid(pstrFileName , 6 , 2 )
pstrCurrentMonth = pstrMonth & "/" & 01 & "/" & pstrYear
pstrMonthEnd = pstrMonth & "/" & EOMonth(pstrCurrentMonth) & "/" &
pstrYear

DTSGlobalVariables("pstrMEDate").Value = pstrMonthEnd

Main = DTSTaskExecResult_Success
End Function

Public Function EOMonth(pdteEOMDate) 'pstrEOMDate)
' This function finds the last day of the month of the date passed
to the function.
' The last day of the month will be an interger that can be stored
in a variable

Dim pintLeapYear
Dim pintMonth
' Dim pdteEOMDate

' pdteEOMDate = DateValue( pstrEOMDate )
pintMonth = Month(pdteEOMDate)

Select Case pintMonth
Case 1
EOMonth = 31
Case 2
pintLeapYear = Year(pdteEOMDate) Mod 4
If pintLeapYear = 0 Then
EOMonth = 29
Else
EOMonth = 28
End If
Case 3
EOMonth = 31
Case 4
EOMonth = 30
Case 5
EOMonth = 31
Case 6
EOMonth = 30
Case 7
EOMonth = 31
Case 8
EOMonth = 31
Case 9
EOMonth = 30
Case 10
EOMonth = 31
Case 11
EOMonth = 30
Case 12
EOMonth = 31

End Select
End Function

Conan Kelly
12/30/2005 1:01:36 PM

NEVER MIND....I FIGURED IT OUT....DUH!!!!!!!!

I used some message boxes to do some debugging and realized that the
pstrFileName variable had the whole path along with the file name
stored in it. What was being stored in the Month and Year variables
were other characters from the path, not the month and year from the
file name. Luckily all of the file names are the same length, so all
I needed to do to fix the problem was a "Right([file name global
variable],11)".

Thanks for the help anyways. I hope my mistakes will help others,

Conan Kelly

[quoted text, click to view]

sql server dts : Please help with my VB Script in my DTS package