September 28, 2011 at 6:56 am
We run an SSIS package using DTEXEC from the commandline from a fileserver.
I made sure the package uses SSPI and runs under a specific Windows user.
The package needs to read and write in a specific database so the windows user is added in this specific database in the db_datareader en db_datawriter roles.
Now the following error occurs when running the package.
Description: The external metadata column collection is out of synchronization with the data source columns. The "external metadata column "column_name" (57)" needs to be removed from the external metadata column collection.
Probably the user does not have sufficient permissions to read the column metadata from the database.
When I use the db_owner role the problem is fixed. However I would rather not use this role because of it's excessive permissions. Is there an alternative solution for this problem?
__________________
MS-SQL / SSIS / SSRS junkie
Visit my blog at dba60k.net
September 29, 2011 at 9:01 am
I am not sure why you are not having the issue when you run as a login/user in the db_owner role. Regardless, if you are getting by with a db_owner member then it is only because it is masking the real issue which is that the SSIS package's expectation of the database schema is different from the actual database schema. You need to refresh the metadata in the SSIS package to remove that problem. See if this page helps:
http://followtheheard.blogspot.com/2007/10/ssis-external-metadata-refresh.html
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
October 3, 2011 at 3:08 am
Thanks for your reply. Yes this could be a metadata sync problem, but I already tried refreshing the SSIS metadata.
The problem occurs in the validation phase of the data flow components. I guess because SSIS always reads the actual column metadata from the sys.tables. However we also use stored procedures as a source in data flows. I guess SSIS tries to retrieve the stored procedure text and needs owner permissions for this.
Think I'm gonna try to remove the SP's to see if that's the problem.
__________________
MS-SQL / SSIS / SSRS junkie
Visit my blog at dba60k.net
October 4, 2011 at 7:14 am
peterjonk (10/3/2011)
Thanks for your reply. Yes this could be a metadata sync problem, but I already tried refreshing the SSIS metadata.The problem occurs in the validation phase of the data flow components.
You said the user is in db_datareader and db_datawriter roles. Please confirm the user has been granted execute permission on the stored procedures.
However we also use stored procedures as a source in data flows. I guess SSIS tries to retrieve the stored procedure text and needs owner permissions for this.
You said the user is in db_datareader and db_datawriter roles. Please confirm the user has been granted execute permission on the stored procedures.
Also, are you using an OLE DB Data Source to call the stored procedure? If so, are the stored procedures using IF...ELSE blocks to decide which resultset to return and do some resultsets being returned from the proc have different shapes, i.e. a different number of columns or data types?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
October 5, 2011 at 2:31 pm
I double checked the execute permissions on the SP's and some didn't have the right permissions.
The package now works fine with db_datareader and writer and the execute permissions on the SP's. Thank you.
__________________
MS-SQL / SSIS / SSRS junkie
Visit my blog at dba60k.net
October 5, 2011 at 4:09 pm
Cool, happy you got it sorted!
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply