March 12, 2010 at 2:13 pm
I'm trying to use SSIS to bulk load a table that has an identity column, and for this instance the ssis package will be loading records with a value for the identity column already. I choose "Keep Identity" on the OLE DB Destination, but the DBA states they won't give Alter Table permissions to the users. Is there any way that we can accomplish this? We will also have instances where we'll want to drop a value in an identity column through SSIS when we're loading child records as well. It would be nice if we could accomplish this without having to get into a heated discussion with the DBA's.
March 12, 2010 at 2:46 pm
I believe that "Keep Identity" is effectively SET IDENTITY_INSERT for a table, which per books online:
User must own the object, or be a member of the sysadmin fixed server role, or the db_owner and db_ddladmin fixed database roles.
Who will be executing the task in production? A user account or will it be run under the context of the SQL Server itself.
Typically DBAs will not allow this, I tend to agree.
CEWII
March 12, 2010 at 2:58 pm
The people running the SSIS will be users. I'm thinking that my best way for this is to create an agent job to run the SSIS, and hopefully the service account will be granted rights to alter the table for such purposes. It would be nice to be able to just grant alter on that column.
Thanx for the reply:-)
March 12, 2010 at 3:04 pm
One option would be to set up a staging table you could use for your bulk load task and add a stored proc to insert those data into the final table. Would allow testing for valid data, too.
I'll second Elliot: the DBA is (most probably) right when refusing tho give that high level of permissions.
March 12, 2010 at 3:06 pm
Mike Voissem (3/12/2010)
The people running the SSIS will be users. I'm thinking that my best way for this is to create an agent job to run the SSIS, and hopefully the service account will be granted rights to alter the table for such purposes. It would be nice to be able to just grant alter on that column.Thanx for the reply:-)
If it is run as "sa" then it is run under the context of the server itself, no permissions would need to be granted.
Also, I would really have to think about allowing users to run SSIS packages as themselves in a production environment. I'd have to see a heck of a business requirement and even then I don't think it would happen..
CEWII
March 12, 2010 at 3:13 pm
Elliott W (3/12/2010)
Also, I would really have to think about allowing users to run SSIS packages as themselves in a production environment. I'd have to see a heck of a business requirement and even then I don't think it would happen..
CEWII
I can't think of any such requirement leading to SSIS as the only option either.
Even if there would be tasks that cannot be done using stored procedures (e.g. uploading a file from client to server), the only thing that would be left for SSIS to do is to get the data to a point where stored procedures would take over (e.g. staging table or shared drive to continue the example).
March 12, 2010 at 3:17 pm
I am with Elliot and Lutz on this, Standard protocol is to build a SSIS package and test that in Dev environment and give package to DBA's in Prod, DBA's run the package on demand or create a job for the process.
March 12, 2010 at 3:25 pm
If I don't map the Identity Column, shouldn't it just get populated then? In just testing this out, if I don't map to the Identity Column and un-check "Keep Identity", I still get an error stating I do not have permission, which is rather odd. All the other tables in that database I can load just fine. And there are 2 other tables in the same schema that don't have an identity column that load just fine.
As for the user running SSIS jobs. We have a lot of files come in from clients for mailings, orders, etc. that in the old mainframe days, the users would load the file to the mainframe and run jobs to create reports. they wanted that same capability today. The large majority of our work is ad-hoc, and probably only 20% of our data keeps getting added to. The rest is somewhat transient, usually project specific and then it's gone. Very little accruing of data is taking place.
March 12, 2010 at 3:31 pm
Are you doing this in production? Did you test this in Integration?
March 12, 2010 at 9:13 pm
What is the nature of the data where you'd want to insert directly into an IDENTITY column?
--Jeff Moden
March 15, 2010 at 6:31 am
Old Hand, yes this was tested in test and it worked there. Of course I've got more rights there than I do in prod.
Jeff, sometimes the data we get comes from a client with the Identity column already populated (so we can tie the parent and child records together), and we need to maintain that value when we export the data and send back to the client. I can understand the who part about the DBA or Job's controlling the running of SSIS packages, except in these cases where so much of this is ad-hoc and our client service people need to turn things around pretty quickly.
In order to resolve, I'm just going to do as suggested and create the staging tables, and add an additional table to house the "Identity" should a client send one, so I can correlate the clients Identity to ours. That would probably cause less friction with the DBA's, and I'd be able to get things accomplished faster.
Thanx all for your input, it gives me a better perspective of how things should be.:hehe:
March 15, 2010 at 8:05 am
Trying to keep the CLIENT's identity value in YOUR identity column is a mistake.
Client A sends records with identities of 1-500.
Client B sends records with identities of 1-500.
Both want their identity values kept.
You can't have them both!
You should just let the identity column be what it is, a unique, meaningless id for the data in YOUR copy of the table.
If you want to maintain a copy of the client's ID value, you should add a column to the table called "ClientID" and populate it with what you want.
You might want to have it default to your identity value if you don't specify a client id value.
March 15, 2010 at 8:10 am
Thanx, this is the route I've chosen to take. Another point I failed to consider was that even if I used a unique client identifier to segregate clients, there's still the possibility that the client would send a duplicate record that could cause an error as well.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply