Package Validation Error

I have a package which takes a text file and imports data into a SQL table. It returns the Package Validation Error:

Error at Data Flow Task [SQL Server Destination [1430]]: Failure inserting into the read-only column "ZipCode".

The column ZipCode in the sql table is a varchar(50) column. I do not understand why it thinks the column is a read only column; if someone could shed some light on this I would greatly appreciate it.

Thanks!

GN

Take a closer look at the table, perhaps it is a calculated column. What is the CREATE TABLE statement for that table?|||

It's not a calculated column. It is just a standard varchar column. The create table script generated by SQL is:

CREATETABLE [dbo].[ZipToState](

[ZipCode] [varchar](50)COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL,

[StateAbbr] [varchar](2)COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL,

[City] [varchar](50)COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL,

[PreferredCityName] [varchar](50)COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL,

[ID] [int] IDENTITY(1,1)NOTNULL

)ON [PRIMARY]

Actually I'm starting to think that it may actually be a permissions issue.

Thanks!

GN

|||

Okay I've confirmed that it is a permission error.

When I use my login, which has sysadmin privleges, for the database connection there is no problem inserting into the table fields. When I use another login that does not have sysadmin privleges then the package errors out stating the field is read-only. I've the login the bulkadmin server role but I get the same error. I've also given the login the explicit server permission Administer bulk operations but I keep getting the same error. Additionally the login has insert, delete, and select permissions for the table. So does anyone know what permissions are needed for the login to be able to perform the bulk insert via the package?

Thanks!

Package Validation Error

I have a package which takes a text file and imports data into a SQL table. It returns the Package Validation Error:

Error at Data Flow Task [SQL Server Destination [1430]]: Failure inserting into the read-only column "ZipCode".

The column ZipCode in the sql table is a varchar(50) column. I do not understand why it thinks the column is a read only column; if someone could shed some light on this I would greatly appreciate it.

Thanks!

GN

Take a closer look at the table, perhaps it is a calculated column. What is the CREATE TABLE statement for that table?|||

It's not a calculated column. It is just a standard varchar column. The create table script generated by SQL is:

CREATE TABLE [dbo].[ZipToState](

[ZipCode] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[StateAbbr] [varchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[City] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[PreferredCityName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[ID] [int] IDENTITY(1,1) NOT NULL

) ON [PRIMARY]

Actually I'm starting to think that it may actually be a permissions issue.

Thanks!

GN

|||

Okay I've confirmed that it is a permission error.

When I use my login, which has sysadmin privleges, for the database connection there is no problem inserting into the table fields. When I use another login that does not have sysadmin privleges then the package errors out stating the field is read-only. I've the login the bulkadmin server role but I get the same error. I've also given the login the explicit server permission Administer bulk operations but I keep getting the same error. Additionally the login has insert, delete, and select permissions for the table. So does anyone know what permissions are needed for the login to be able to perform the bulk insert via the package?

Thanks!

Overwrite Excel spreadsheet

Hi,

I have a DTS package that imports a file with data,
performs several checks,
puts all invalid rows in a table,
exports the table to a excel spreadsheet.

My problem is that it appends the data every time. I want it to overwrite the existing spreadsheet. I can't find any option for that.

I use the "Transform Data Task" to do this.

Any suggestions?Do an EXECUTE PROCESS Task and supply a DELETE Command...

But I didn't think it did an append...|||I had the same problem this week. I made a template of the excel sheet, selected the columns needed and created a named range on the excel sheet, and added it as a linked server. Once it's linked like this you can treat the named range like a table and use a delete from statement to clear the sheet.

Run this command from QA to make the excel sheet a linked server, 'excelsource' can be any name you want to give the connection.

sp_addlinkedserver 'excelsource', 'jet 4.0', 'Microsoft.Jet.OLEDB.4.0',
'c:\temp\filename.xls', null, 'excel 5.0'

If the named range on the excel sheet is also named excelsource use

delete from excelsource...excelsource