I have a small table consisting of three columns (identity, varchar, and
datetime). I have to import a text file containing strings into the table.
Each line of the text file is a row to be imported. Since the datetime
column can't be empty I'm modifying the transformation script. I've added:
DTSDestination("dt") = Now. I have no idea if that will work or not. My
problem is coming from the identity column. I have no identity number to
provide and it won't work without one. How can I modify my script to tell
it to create it's own number? That's why I set the column to identity -- so
I wouldn't have to provide a number.

Here's my script:

Function Main()
DTSDestination("vrtext") = DTSSource("Col002")
DTSDestination("dt") = Now
Main = DTSTransformStat_OK
End Function

Any help would be greatly appreciated.

Have a read of my article here

Problems With IDENTITY() and the DataPump task.
(http://www.sqldts.com/default.aspx?293)


And also have a look at this

Formatting Character Data into Datetime fields
(http://www.sqldts.com/default.aspx?249)


Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org