Generate Uniqueidentifyer/GUID in DTS

Question

Generate Uniqueidentifyer/GUID in DTS

JN-161845

SSCrazy

Points: 2052
More actions
February 14, 2005 at 1:09 pm

#61658

Hello,
I am importing data from a TEXT file into SQL Server 2000 table. The destination table in SQL Server has an ID column with Uniqueidentifier data type, which is GUID and needs to be populated using SQL Server function NEWID(). Now, I am importing data from the text file, which has other data, but no GUID. I am using ActiveX Script to get data from source text file and insert it onto the destination column/table. Following is my code:
'**********************************************************************
' Visual Basic Transformation Script
'************************************************************************
' Copy each source column to the destination column
Function Main()
DTSDestination("SourceID") = 1101
DTSDestination("ListID") = DTSSource("Col001")
DTSDestination("MyGUID") = NEWID()
SELECT CASE DTSSource("Col006")
  CASE "A"
   DTSDestination("CategoryID") = "AA"
  CASE "B"
   DTSDestination("CategoryID") = "BB"
  CASE "C"
   DTSDestination("CategoryID") = "CC"
  CASE "D"
   DTSDestination("CategoryID") = "DD"
  CASE ELSE
   DTSDestination("CategoryID") = "EE"
END SELECT
Main = DTSTransformStat_OK
End Function
**************************************
When I test the above transformation, I get VBScript runtime error that says, Type mismatch: NEWID.
Basically, I need to import data from the text file, but since it doesn't provide values for the field, MyGUID I need to generate it using NEWID() function and I thought assigning the function directly to destination column in the ActiveX Script would do the job, but it give me an error as listed above.
Does anyone know why I get such an error or if it's even possible to generate GUID the way I am trying to in data transformation using ActiveX Script in DTS? If not, any suggestions on a work around?
Thanks much.
JN

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

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

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 1

Have you tried assigning the DEFAULT for the column that you want to populate to be NEWID()?? This way you don't have to worry about it?

Good Hunting!

AJ Ahrens

webmaster@kritter.net

Edwin Frydendall SSCrazy Points: 2361 More actions · Answer 2

JN,

I recently went through the same thing. Look at the following ActiveX VBScript code:

Dim strConnectString, conServerDB, strUniqueIdentifier, strSQL

'Set up and open database connection.

strConnectString = "Provider=SQLOLEDB; Data Source=ServerName;" & _

"Initial Catalog=DBName;UID=User;password=Password"

Set conServerDB = CreateObject("ADODB.Connection")

conServerDB.Open strConnectString

'Get UniqueIdentifier

strSQL = "Select Convert(VarChar(36), NewId())"

strUniqueIdentifier = conServerDB.Execute(strSQL).Fields(0).Value

JN-161845 SSCrazy Points: 2052 More actions · Answer 3

Thanks for the replies.

If I put Default = NEWID() in the table desing then it works fine. I insert other records and a GUID is generated using NEWID() for default values for each new record that's inserted in my table. So I might not need to do anything in ActiveX Script, I think.

JN