Error when running DTS package re: NULL values

[Rexolio]

I have a flat file that I need to set up a DTS to extract the data and add to my SQL Server database table a few times a week.

I'm running into a problem:

insert error, column 1 ('id', DBTYPE_I4), status 10: Integrity violations; attempt to insert NULL data or data which violates contraints.

Here is the flat file:

name, street, address, city, zip
Adam, 123,    Main,    Polk, 30188
Joe,  2053,   Main,    Polk, 30188
Tom,  320,    Main,    Polk, 30188

Here is the table I'm trying to pull it into:

id, name, address, city, zip

I've mapped all of the fields in DTS. However, I choose <ignore> for Destination column id (because I have no option to remove "id" from the DTS package. If I just do a standard insert, since id is an identify field, it adds a number automatically.

Then, for transformation, I use:

Function Main()
	DTSDestination("name") = DTSSource("Col001")
	DTSDestination("address") = DTSSource("Col002") & " " & DTSSource("Col003")
	DTSDestination("city") = DTSSource("Col004")
	DTSDestination("zip") = DTSSource("Col005")
End Function

Right now I'm concerned about the error stating I'm trying to insert a NULL value into column id. How do I get around that.

However, I have also gotten an error stating that "DTSSource" is an invalid call or argument, so I can only assume that once I fix the NULL problem, this will come back.

PLEASE HELP!

 

[manjarikosaraju]

It might be possible that the file you are trying to import into SQL has some extra rows at the end which is causing the NULL value insert.

Try deleting those extra rows from the file and run the DTS package again.

OR

Remove do not allow null values constraint on the columns of the table you are inserting into.

Hope this helps.

-Manjari

 

[Rexolio]

It worked... after playing around, I realized that I could go into Local Packages and edit the DTS, removing the DESTINATION column that was causing the problem.

Since the destination column was the identity column, I simply removed it from being included/called.

Now it works great!

Thanks for the help!