Insert into table with identity column - data flow task SSIS 2008

[John Brown]

Hello All,

I am copying records into a table with an identity column and I want to
preserve the values from the source table. I know that I can do this by
enabling the "Keep identity" option in the OleDB destination.

However, I had tried another approach using ADO.NET source and destination
nodes, because I had been lectured in another newsgroup that ADO.NET was
better than sliced bread. I created "Execute SQL Task" event handlers for the
data flow task:

OnPreExecute - set identity_insert mytable on
On PostExecute - set identity_insert mytable off

The OnPreExecute handler fires, but then the package complains that
identity_insert is not ON, and fails to insert the records. Does anyone know
why it didn't work?

Regards,
Alias John Brown.

[Paul Shapiro]

Try running SQL Profiler to capture the exact command sequence being sent to
the server.

Here’s what SQL Books OnLine has to say:
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.

At any time, only one table in a session can have the IDENTITY_INSERT
property set to ON. If a table already has this property set to ON, and a
SET IDENTITY_INSERT ON statement is issued for another table, SQL Server
returns an error message that states SET IDENTITY_INSERT is already ON and
reports the table it is set ON for.

If the value inserted is larger than the current identity value for the
table, SQL Server automatically uses the new inserted value as the current
identity value.

The setting of SET IDENTITY_INSERT is set at execute or run time and not at
parse time.

"John Brown" <John...@discussions.microsoft.com> wrote in message
news:359F4DC3-AAA8-4D37...@microsoft.com...

[John Brown]

"Paul Shapiro" wrote:

> Try running SQL Profiler to capture the exact command sequence being sent to
> the server.
>

Good idea.

> Here’s what SQL Books OnLine has to say:
> 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.

For what it is worth, my account is a sysadmin. The OnPreExecute task
succeeds. Also, I can execute the code that I am trying to put in the package
in a query window.

>
> At any time, only one table in a session can have the IDENTITY_INSERT
> property set to ON. If a table already has this property set to ON, and a
> SET IDENTITY_INSERT ON statement is issued for another table, SQL Server
> returns an error message that states SET IDENTITY_INSERT is already ON and
> reports the table it is set ON for.
>

Which is why OnPreExecute sets identity_insert on for the table and
OnPostExecute turns it off. The database is on my notebook, so I am sure
that no other process is accessing the database in any way.

Regards,
Alias John Brown.

[Todd C]

ADO.Net better than sliced bread?
As in a Lexus is better than a Ford F 150?
But what if you want to haul 500 lbs of cement and 20 2x4's?

It may be better for some things, but not all. And if OLE DB works for you
and you have trouble with ADO.Net, then which do YOU think is better?

Regarding your using a Pre and Post Execute SQL Task to turn IDENTITY_INSERT
on and off, make sure that they, and the Destination (ADO) adapter all use
the same Connection Manager, and set that CM's "RetainSameConnection" to True.

It *might* help. Keep us posted
--
Todd C
MCTS SQL Server 2005

[John Brown]

"Todd C" wrote:

> ADO.Net better than sliced bread?
> As in a Lexus is better than a Ford F 150?
> But what if you want to haul 500 lbs of cement and 20 2x4's?
>
> It may be better for some things, but not all. And if OLE DB works for you
> and you have trouble with ADO.Net, then which do YOU think is better?
>

I agree with with you, and I am happy to use whatever works, but some people
don't see it that way. They must have the newest, latest and greatest. Still,
I am belatedly trying to learn the various .NET technologies, so I use them
when I can.

> Regarding your using a Pre and Post Execute SQL Task to turn IDENTITY_INSERT
> on and off, make sure that they, and the Destination (ADO) adapter all use
> the same Connection Manager, and set that CM's "RetainSameConnection" to True.
>

RetainSameConnection = True did the trick. Thanks.

I wonder why they didn't give the ADO.Net destination the same properties as
the OLE DB destination? As I said, in OLE DB, there is a "retain identity"
checkbox, but the ADO.NET does not have it.

Regards,
Alias John Brown.