ActiveX Script in DTS

I have a large csv import into a SQL Server data base and I am trying to
transform a field to DATETIME before it gets to the database. The data in
the field is '20030100' or something similar the first four characters are
year the next two are month an the last are place holders as the mainframe
application does not transfer its DATETIME fields nicely. there for I want
to do something like this
Function Main()
DTSDestination("TRANCD") = Left( DTSSource("TRANCD") , 6 ) + '28'
Main = DTSTransformStat_OK
End Function
but it fails. I have also tried setting the value to Left(
DTSSource("TRANCD") , 6 ) & '28' and to concat( Left( DTSSource("TRANCD") ,
6 ) , '28').
Each one files but if I just set it to the Left( DTSSource("TRANCD") , 6 )
it is ok. It appears that I am trying to get 28 into the field wrong. Any
help out there?
Andrew C. Madsen
Information Architect
Harley-Davidson Motor Company
Never mind. I found it. I used single quotes instead of double quotes around
the 28.
Andrew C. Madsen
Information Architect
Harley-Davidson Motor Company
"Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
news:OjQ7iARPEHA.1276@.TK2MSFTNGP11.phx.gbl...
> I have a large csv import into a SQL Server data base and I am trying to
> transform a field to DATETIME before it gets to the database. The data in
> the field is '20030100' or something similar the first four characters are
> year the next two are month an the last are place holders as the mainframe
> application does not transfer its DATETIME fields nicely. there for I want
> to do something like this
> Function Main()
> DTSDestination("TRANCD") = Left( DTSSource("TRANCD") , 6 ) + '28'
> Main = DTSTransformStat_OK
> End Function
> but it fails. I have also tried setting the value to Left(
> DTSSource("TRANCD") , 6 ) & '28' and to concat( Left( DTSSource("TRANCD")
,
> 6 ) , '28').
> Each one files but if I just set it to the Left( DTSSource("TRANCD") , 6 )
> it is ok. It appears that I am trying to get 28 into the field wrong. Any
> help out there?
> --
> Andrew C. Madsen
> Information Architect
> Harley-Davidson Motor Company
>
|||Ok mind again. This function fails because for some reason it does not like
converting the string value "20030128" into a DATETIME. Up to now I have
been loading the csv file into the database then running a stored procedure
that runs an update on the column that has the bogus datetime then I have
been loading that across to another table that has the date time field and
that works I believe because the TRANSDT field in the first is varchar. I
realize that I could convert the column in the first table but I would like
to do it in the initial load. Any suggestions?
Andrew C. Madsen
Information Architect
Harley-Davidson Motor Company
"Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
news:ekFkHFRPEHA.680@.TK2MSFTNGP11.phx.gbl...
> Never mind. I found it. I used single quotes instead of double quotes
around[vbcol=seagreen]
> the 28.
> --
> Andrew C. Madsen
> Information Architect
> Harley-Davidson Motor Company
> "Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
> news:OjQ7iARPEHA.1276@.TK2MSFTNGP11.phx.gbl...
in[vbcol=seagreen]
are[vbcol=seagreen]
mainframe[vbcol=seagreen]
want[vbcol=seagreen]
DTSSource("TRANCD")[vbcol=seagreen]
> ,
6 )[vbcol=seagreen]
Any
>
|||If anyone is interested. The solution I came up with is this:
Function Main()
Dim DateStrY
Dim DateStrM
Dim DateStrD
Dim DateStr
Dim HOLD
DateStrY = LEFT(DTSSource("TRANDT"),4)
DateStrM = Mid( DTSSource("TRANDT") , 5 ,2 )
DateStrD = "28"
DateStr = DateStrM + "/" + DateStrD + "/" + DateStrY
DTSDestination("TRANDT") =DateStr
Main = DTSTransformStat_OK
End Function
I needed to reformat the string as a date because the transform will not
recognize 20030128 as a date but does recognize 01/28/2003.
Andrew C. Madsen
Information Architect
Harley-Davidson Motor Company
"Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
news:OjQ7iARPEHA.1276@.TK2MSFTNGP11.phx.gbl...
> I have a large csv import into a SQL Server data base and I am trying to
> transform a field to DATETIME before it gets to the database. The data in
> the field is '20030100' or something similar the first four characters are
> year the next two are month an the last are place holders as the mainframe
> application does not transfer its DATETIME fields nicely. there for I want
> to do something like this
> Function Main()
> DTSDestination("TRANCD") = Left( DTSSource("TRANCD") , 6 ) + '28'
> Main = DTSTransformStat_OK
> End Function
> but it fails. I have also tried setting the value to Left(
> DTSSource("TRANCD") , 6 ) & '28' and to concat( Left( DTSSource("TRANCD")
,
> 6 ) , '28').
> Each one files but if I just set it to the Left( DTSSource("TRANCD") , 6 )
> it is ok. It appears that I am trying to get 28 into the field wrong. Any
> help out there?
> --
> Andrew C. Madsen
> Information Architect
> Harley-Davidson Motor Company
>