Invalid Procedure Call Problem

[williadn48]

I am trying to import data from Access to SQL. Since I am a newbie at DTS Activex scripting, I think the syntax is not correct.

ERROR: Invalid procedure call or argument: DTSSource on line 2.
Function Main()

DTSDestination("FStartTime") = Trim(DTSSource("Date")) + " " + Trim(DTSSource("Start Time"))
DTSDestination("FEndTime") = Trim(DTSSource("Date")) + " " + Trim(DTSSource("End Time"))
DTSDestination("TransxStatus") = "Function Completed"
DTSDestination("Access_tblMain_Key") = DTSSource("Key")
DTSDestination("EntryDate") = Trim(DTSSource("Date")) + " " + Trim(DTSSource("Start Time"))

if DTSSource("Associate") = "Tarabiya, Danielle" then
DTSDestination("AssociateID") = 999999
end if


DTSDestination("QCBatchesAudited") = DTSSource("Batches Audited")

if DTSSource("Function") = "Prep" then
DTSDestination("FuncArea") = 32

If DTSSource("Sub-Function") = "Select Sub-Function" then
DTSDestination("SubFunc") = 105
end if
If DTSSource("Sub-Function") = "" then
DTSDestination("SubFunc") = 105
end if
If DTSSource("Sub-Function") = "e-file" then
DTSDestination("SubFunc") = 105
end if

If DTSSource("Sub-Function") = "Research-Mail" then
DTSDestination("SubFunc") = 106
end if

If DTSSource("Sub-Function") = "Return-Mail" then
DTSDestination("SubFunc") = 104
end if

end if

if DTSSource("Function") = "Scan" then
DTSDestination("FuncArea") = 33
If DTSSource("Sub-Function") = "Select Sub-Function" then
DTSDestination("SubFunc") = 109
end if
If DTSSource("Sub-Function") = "" then
DTSDestination("SubFunc") = 109
end if
end if
if DTSSource("Function") = "Lunch" then

DTSDestination("FuncArea") = 39
DTSDestination("SubFunc") = 122

end if
if DTSSource("Function") = "Microfilm" then

DTSDestination("FuncArea") = 39
DTSDestination("SubFunc") = 122

end if
if DTSSource("Sub-Function") = "Light" then
DTSDestination("SubFunc") = 112

end if
if DTSSource("Sub-Function") = "Heavy" then
DTSDestination("SubFunc") = 115

end if
if DTSSource("Sub-Function") = "Mod1" then
DTSDestination("SubFunc") = 113

end if
if DTSSource("Sub-Function") = "Mod 1" then
DTSDestination("SubFunc") = 113

end if
if DTSSource("Sub-Function") = "Mod2" then
DTSDestination("SubFunc") = 114

end if
if DTSSource("Sub-Function") = "VerifyDE" then
DTSDestination("SubFunc") = 116

end if
if DTSSource("Sub-Function") = "CS" then
DTSDestination("SubFunc") = 117

end if

if DTSSource("Comments") <> "Enter Comments " then
DTSDestination("Comments") = DTSSource("Comments")
end if
Main = DTSTransformStat_OK
End Function

Anyone have any clues what might be wrong?

 

[AlexCuse]

In vbScript, you need to use the ampersand (&) instead of the plus sign (+) that you are trying to use to concatenate values.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[williadn48]

Still getting the same error.

I replaced the first 4 lines:
DTSDestination("FStartTime") = DTSSource("Date") & DTSSource("Start Time")
DTSDestination("FEndTime") = DTSSource("Date") & DTSSource("End Time")
DTSDestination("TransxStatus") = "Function Completed"
DTSDestination("Access_tblMain_Key") = DTSSource("Key")
DTSDestination("EntryDate") = DTSSource("Date") & DTSSource("Start Time")

Still no luck.

 

[AlexCuse]

Is it still on the same line?

If so, I'd bet this is a problem:

DTSDestination("FEndTime") = Trim(DTSSource("Date")) + " " + Trim(DTSSource("End Time"))

The concatenation of these two values is probably resulting in an invalid datetime value (I presume that is what FEndTime is, if its' suppose to store the combination of your Date and EndTime columns).

Try padding the time with zeros (you need 08:00 rather than 8:00)

DTSDestination("FEndTime") = Left("0" + Trim(DTSSource("Date")), [b]4[/b]) + " " + Trim(DTSSource("End Time"))

Where I have the 4 is where you enter the number of positions you need to include.

If this doesn't work, we need to see data types (for both Access and SQL Server columns) and some sample data. And if this is a one-off job you might be better off using Access queries (if thats' what you're more comfortable with) to load your table via access' linked table feature.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[williadn48]

I'm confused. In your example, you padded the Date field instead of the time field? I am assuming that the code should look like this then:

DTSDestination("FEndTime") = Trim(DTSSource("Date")) + " " + Left("0" + Trim(DTSSource("End Time")), 6)

Example input data looks like: 1/5/2007 1:59:48 PM

What about the +'s ? Should they be &'s ?

Thanks.

 

[williadn48]

I made the changes that Alex suggested and still got the same errors. I flipped the padding from Date to End Time and still got the same errors.

Now what? Help! Under a deadline!

Thanks.

Source "Date" is Access 2000. Datatype = Date/Time Short Date with a default format value set to =Format(Now(),"m/d/yyyy").

Source "End Time" is Access 2000. Datatype = Long Time.

Destination "FEndTime" is SQL 2000. Datatype = Datetime.

 

[AlexCuse]

Yeah those should have been ampersands, sorry...

Also, it should have been Right, and the padding should've been on the time portion. I was trying to do two things at once, I apologize. You might need to use Format function to convert both to strings as well (otherwise you can't really combine them)

If you are under a deadline, you may be better off doing this from access, as it seems that is where you are more comfortable.

A quick fix would be to combine your date/time values in access using the dateadd function a few times, and then inserting this new column in your table.



Ignorance of certain subjects is a great part of wisdom

 

[williadn48]

Thanks, but I don't really know Access either.

I do not want to add dates, I simply need to concatenate them. Is there a function for that?

 

[AlexCuse]

What you need to concatenate is strings. Have a look at this:

http://msdn2.microsoft.com/en-us/library/8aebkz6s.aspx

If you format one as short date and one as time, you might be able to concatenate them?

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[AlexCuse]

If that does not work, maybe you should try forum329

The experts there would probably know what you need to do to get your columns into a string format.

Good Luck,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[williadn48]

Here is a select statement that I am trying to run in Access:

SELECT Date AS EntryDate(DateAdd([Start Time],0,Date))

...but it doesn't work.

This one doesn't work either:

SELECT Date AS [EntryDate(DateAdd([Start Time],0,Date))]

 

[AlexCuse]

You need to add the hour, minute, and second individually. I suggest you read the Access help section on DateAdd.

But, if you are only trying to concatenate then really what you want to do is query your access table using the Format function (in your DTS Source, you can switch from table to SQL Query). Format function will give you two strings, that are easily concatenated by vbScript (or in the query even). Just make sure they are formatted in a way that they will produce a valid datetime value for SQL Server (something like '20070501 01:29:00')

Hope this helps,

Alex


Ignorance of certain subjects is a great part of wisdom

 

[williadn48]

OK...this one ran but came back with #ERROR in all the cells. ???

SELECT DateAdd('[Start Time]',0,[Date]) AS EntryDate

 

[AlexCuse]

Did you read the help section? I am only 95% sure how it works with access, but I think its'

<pseudo-code>
DateAdd(Increment as String, Number to add as Integer, StartDate as DateTime)

Still, what you really want to do is get everything into string formats that can be concatenated.

Hope it helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[williadn48]

I GOT IT !!!
SELECT ([Date] & " " & [Start Time]) AS EntryDate

 

[williadn48]

Well, still get the same DTS error on the same line. ???
DTSDestination("FStartTime") = DTSSource("StartTime")

StartTime looks good in Access. ?????
What should I do now? CSTR ???

Anyone ???

 

[williadn48]

GOT IT!!!

CDATE!!! I finally made it pass the first few date lines in my DTS VB Script !!!

Thanks.

 

[williadn48]

Now I am getting the invalid procedure call error on this line (line 7):

Ln 7: if DTSSource("Associate") = "Tarabiya, Danielle" then
DTSDestination("AssociateID") = "999999"
end if

 

[AlexCuse]

Are you sure that the column names are spelled correctly?

Ignorance of certain subjects is a great part of wisdom