interesting update/insert trigger problem (null issue)

Ok so here is the issue. I am thinking I somehow have to clear the old data
after executing the trigger.
So I add a user Joe Brown with his info on a users table, a trigger fires
and dumps the duplicate data into a users-dup table (for other justifiable
purposes). Update does the same basic thing.
Works fine. Here is the problem. I then add another name. Jack Black and
his info, but he has some null values... like i don't know his address. So
now the trigger fires and all of the duplicate data is carried across to the
dup table... except where there was no data (NULL) in a field... it is
adding the last "real" data set in replace of the null. SO Jack Black has
Joe Brown's address in his field... since it was the last "not null" value
entered in that column.
Looking for ideas. Figured it is something simple, I am just missing. Like
some sort of purge call.
Below is the code I am using:
for insert:
CREATE TRIGGER insertUserMrktg ON [dbo].[USERS]
FOR INSERT
AS
insert into user_marketing (greeting, fName, lName, title, compName,
address, city, provState, fk_country,
zip, email, phone, phoneext, fax, fk_language, fk_segment, fk_job,
emailType, addedBy, fk_userID)
select greeting, fName, lName, title, compName, address, city, provState,
fk_country,
zip, email, phone, phoneext, fax, fk_language, fk_segment, fk_job,
emailType, addedBy, pk_userID
FROM Inserted
for update:
CREATE TRIGGER updateUserMrktg ON [dbo].[USERS]
FOR UPDATE
AS
update a
set a.greeting=b.greeting,
a.fName=b.fName,
a.lName=b.lName,
a.title=b.title,
a.compName=b.compName,
a.address=b.address,
a.city=b.city,
a.provState=b.provState,
a.fk_country=b.fk_country,
a.zip=b.zip,
a.email=b.email,
a.phone=b.phone,
a.phoneext=b.phoneext,
a.fax=b.fax,
a.fk_language=b.fk_language,
a.fk_segment=b.fk_segment,
a.fk_job=b.fk_job,
a.emailType=b.emailType,
a.addedBy=b.addedBy
FROM user_marketing a, users b
where a.fk_userID=
(SELECT pk_userID
FROM Inserted)
Thanks!Two questions.
1 - Why do you want to do this?
2 - How can we identify the last "real" data inserted in the table?, How do
you know it is "real" and not a fake like you are trying to do?
AMB
"cheezebeetle" wrote:

> Ok so here is the issue. I am thinking I somehow have to clear the old da
ta
> after executing the trigger.
> So I add a user Joe Brown with his info on a users table, a trigger fires
> and dumps the duplicate data into a users-dup table (for other justifiable
> purposes). Update does the same basic thing.
> Works fine. Here is the problem. I then add another name. Jack Black an
d
> his info, but he has some null values... like i don't know his address. S
o
> now the trigger fires and all of the duplicate data is carried across to t
he
> dup table... except where there was no data (NULL) in a field... it is
> adding the last "real" data set in replace of the null. SO Jack Black has
> Joe Brown's address in his field... since it was the last "not null" value
> entered in that column.
> Looking for ideas. Figured it is something simple, I am just missing. Li
ke
> some sort of purge call.
> Below is the code I am using:
> for insert:
> CREATE TRIGGER insertUserMrktg ON [dbo].[USERS]
> FOR INSERT
> AS
> insert into user_marketing (greeting, fName, lName, title, compName,
> address, city, provState, fk_country,
> zip, email, phone, phoneext, fax, fk_language, fk_segment, fk_job,
> emailType, addedBy, fk_userID)
> select greeting, fName, lName, title, compName, address, city, provState,
> fk_country,
> zip, email, phone, phoneext, fax, fk_language, fk_segment, fk_job,
> emailType, addedBy, pk_userID
> FROM Inserted
> for update:
> CREATE TRIGGER updateUserMrktg ON [dbo].[USERS]
> FOR UPDATE
> AS
> update a
> set a.greeting=b.greeting,
> a.fName=b.fName,
> a.lName=b.lName,
> a.title=b.title,
> a.compName=b.compName,
> a.address=b.address,
> a.city=b.city,
> a.provState=b.provState,
> a.fk_country=b.fk_country,
> a.zip=b.zip,
> a.email=b.email,
> a.phone=b.phone,
> a.phoneext=b.phoneext,
> a.fax=b.fax,
> a.fk_language=b.fk_language,
> a.fk_segment=b.fk_segment,
> a.fk_job=b.fk_job,
> a.emailType=b.emailType,
> a.addedBy=b.addedBy
> FROM user_marketing a, users b
> where a.fk_userID=
> (SELECT pk_userID
> FROM Inserted)
>
> Thanks!
>|||Hi
Triggers are executed per statement, which can update multiple rows. Using
where a.fk_userID= (SELECT pk_userID FROM Inserted) will return just one
arbitrary value. You are also not relating user_marketing to users
To keep user_marketing in step try:
update a
set a.greeting=b.greeting,
a.fName=b.fName,
a.lName=b.lName,
a.title=b.title,
a.compName=b.compName,
a.address=b.address,
a.city=b.city,
a.provState=b.provState,
a.fk_country=b.fk_country,
a.zip=b.zip,
a.email=b.email,
a.phone=b.phone,
a.phoneext=b.phoneext,
a.fax=b.fax,
a.fk_language=b.fk_language,
a.fk_segment=b.fk_segment,
a.fk_job=b.fk_job,
a.emailType=b.emailType,
a.addedBy=b.addedBy
FROM dbo.user_marketing a
JOIN Inserted b ON b.pk_userID = a.fk_userID
John
"cheezebeetle" wrote:

> Ok so here is the issue. I am thinking I somehow have to clear the old da
ta
> after executing the trigger.
> So I add a user Joe Brown with his info on a users table, a trigger fires
> and dumps the duplicate data into a users-dup table (for other justifiable
> purposes). Update does the same basic thing.
> Works fine. Here is the problem. I then add another name. Jack Black an
d
> his info, but he has some null values... like i don't know his address. S
o
> now the trigger fires and all of the duplicate data is carried across to t
he
> dup table... except where there was no data (NULL) in a field... it is
> adding the last "real" data set in replace of the null. SO Jack Black has
> Joe Brown's address in his field... since it was the last "not null" value
> entered in that column.
> Looking for ideas. Figured it is something simple, I am just missing. Li
ke
> some sort of purge call.
> Below is the code I am using:
> for insert:
> CREATE TRIGGER insertUserMrktg ON [dbo].[USERS]
> FOR INSERT
> AS
> insert into user_marketing (greeting, fName, lName, title, compName,
> address, city, provState, fk_country,
> zip, email, phone, phoneext, fax, fk_language, fk_segment, fk_job,
> emailType, addedBy, fk_userID)
> select greeting, fName, lName, title, compName, address, city, provState,
> fk_country,
> zip, email, phone, phoneext, fax, fk_language, fk_segment, fk_job,
> emailType, addedBy, pk_userID
> FROM Inserted
> for update:
> CREATE TRIGGER updateUserMrktg ON [dbo].[USERS]
> FOR UPDATE
> AS
> update a
> set a.greeting=b.greeting,
> a.fName=b.fName,
> a.lName=b.lName,
> a.title=b.title,
> a.compName=b.compName,
> a.address=b.address,
> a.city=b.city,
> a.provState=b.provState,
> a.fk_country=b.fk_country,
> a.zip=b.zip,
> a.email=b.email,
> a.phone=b.phone,
> a.phoneext=b.phoneext,
> a.fax=b.fax,
> a.fk_language=b.fk_language,
> a.fk_segment=b.fk_segment,
> a.fk_job=b.fk_job,
> a.emailType=b.emailType,
> a.addedBy=b.addedBy
> FROM user_marketing a, users b
> where a.fk_userID=
> (SELECT pk_userID
> FROM Inserted)
>
> Thanks!
>|||Thanks John...
That worked. Duh...
"John Bell" wrote:
> Hi
> Triggers are executed per statement, which can update multiple rows. Using
> where a.fk_userID= (SELECT pk_userID FROM Inserted) will return just one
> arbitrary value. You are also not relating user_marketing to users
> To keep user_marketing in step try:
> update a
> set a.greeting=b.greeting,
> a.fName=b.fName,
> a.lName=b.lName,
> a.title=b.title,
> a.compName=b.compName,
> a.address=b.address,
> a.city=b.city,
> a.provState=b.provState,
> a.fk_country=b.fk_country,
> a.zip=b.zip,
> a.email=b.email,
> a.phone=b.phone,
> a.phoneext=b.phoneext,
> a.fax=b.fax,
> a.fk_language=b.fk_language,
> a.fk_segment=b.fk_segment,
> a.fk_job=b.fk_job,
> a.emailType=b.emailType,
> a.addedBy=b.addedBy
> FROM dbo.user_marketing a
> JOIN Inserted b ON b.pk_userID = a.fk_userID
> John
>
> "cheezebeetle" wrote:
>

Interactively showing reports detailing results/stats of executing a package?

Hi,

I'm just starting out with SSIS and I'm currently looking to migrate some non-SSIS ETL processes to SSIS. Some of these processes are run manually depending on the results from a previous step. In these processes a user remotely executes an individual ETL program and is shown reports detailing the result of the program and various statistics about the data involved.

Is it possible to replicate this behaviour with SSIS / SSRS, or would the user have to execute a package and then manually find and bring up each of the relevant reports?

I'm new to SSIS and I'm still struggling to find out SSIS's capabilities, so I'd be really grateful if anyone could shed some light on this...

Thanks in advance,
Lawrie.
If you have one report, it is possible to stream data directly into SSRS from SSIS. In this case SSRS report uses SSIS as data source. See Books Online for detailed instructions.

Or (this works for multiple reports as well) you can simply start several IExplore.exe using Execute Process tasks and point them to URLs of appropriate SSRS reports - they are usually published to a web server, so you can view them in IE.

Integration services(data flow error)

when executing my data flow package that contains only one source and one destination

OLE db source -> SQL server destination

the following errors occurs in my output

Error: 0xC0202009 at Data Flow Task(infraction action), SQL Server Destination [3600]: An OLE DB error has occurred. Error code: 0x80040E14.

Error: 0xC0202071 at Data Flow Task(infraction action), SQL Server Destination [3600]: Unable to prepare the SSIS bulk insert for data insertion.

Error: 0xC004701A at Data Flow Task(infraction action), DTS.Pipeline: component "SQL Server Destination" (3600) failed the pre-execute phase and returned error code 0xC0202071.

i've checked the structure of my source and destination table but nothing seems to be wrong

if someone have ever faced these errors help me :D

Are you running the package on the same machine as the SQL Server instance that you are inserting into?

If not, it won't work. Use OLE DB Destination instead.

-Jamie

|||I have the same problem and everything is on the same machine. I am transfering from records from multiple tables in one database to a single table in another database. The destinations all have table lock OFF and it usually works. However, recently, I started getting this message. Also, if I rerun it, it happens on different tasks (the tasks are simultaneous 2 at a time).|||

Hi,

I'm having this issue as well, i.e. a package using the "SQL Server Destination" data flow component, loading a server on the same machine (a named instance) that the SSIS package is executing fails with an "Unable to prepare the SSIS bulk insert for data insertion." error. I suspect the windows account executing the package or the SQL login it's mapped to don't have some required permission, but I've tried making the sql account dbo, bulkadmin etc, and added to the local administrators group with no luck. I've seen this reported several times, but no solution?

Dave

|||

Hi guys,

I've mentioned some pre-requisites to getting SQL Server Destination working here:

Destination Adapter Comparison
(http://blogs.conchango.com/jamiethomson/archive/2006/08/14/SSIS_3A00_-Destination-Adapter-Comparison.aspx)

which may or may not be useful. I hope it is.

-Jamie

Integration services(data flow error)

when executing my data flow package that contains only one source and one destination

OLE db source -> SQL server destination

the following errors occurs in my output

Error: 0xC0202009 at Data Flow Task(infraction action), SQL Server Destination [3600]: An OLE DB error has occurred. Error code: 0x80040E14.

Error: 0xC0202071 at Data Flow Task(infraction action), SQL Server Destination [3600]: Unable to prepare the SSIS bulk insert for data insertion.

Error: 0xC004701A at Data Flow Task(infraction action), DTS.Pipeline: component "SQL Server Destination" (3600) failed the pre-execute phase and returned error code 0xC0202071.

i've checked the structure of my source and destination table but nothing seems to be wrong

if someone have ever faced these errors help me :D

Are you running the package on the same machine as the SQL Server instance that you are inserting into?

If not, it won't work. Use OLE DB Destination instead.

-Jamie

|||I have the same problem and everything is on the same machine. I am transfering from records from multiple tables in one database to a single table in another database. The destinations all have table lock OFF and it usually works. However, recently, I started getting this message. Also, if I rerun it, it happens on different tasks (the tasks are simultaneous 2 at a time).|||

Hi,

I'm having this issue as well, i.e. a package using the "SQL Server Destination" data flow component, loading a server on the same machine (a named instance) that the SSIS package is executing fails with an "Unable to prepare the SSIS bulk insert for data insertion." error. I suspect the windows account executing the package or the SQL login it's mapped to don't have some required permission, but I've tried making the sql account dbo, bulkadmin etc, and added to the local administrators group with no luck. I've seen this reported several times, but no solution?

Dave

|||

Hi guys,

I've mentioned some pre-requisites to getting SQL Server Destination working here:

Destination Adapter Comparison
(http://blogs.conchango.com/jamiethomson/archive/2006/08/14/SSIS_3A00_-Destination-Adapter-Comparison.aspx)

which may or may not be useful. I hope it is.

-Jamie

Integration services(data flow error)

when executing my data flow package that contains only one source and one destination

OLE db source -> SQL server destination

the following errors occurs in my output

Error: 0xC0202009 at Data Flow Task(infraction action), SQL Server Destination [3600]: An OLE DB error has occurred. Error code: 0x80040E14.

Error: 0xC0202071 at Data Flow Task(infraction action), SQL Server Destination [3600]: Unable to prepare the SSIS bulk insert for data insertion.

Error: 0xC004701A at Data Flow Task(infraction action), DTS.Pipeline: component "SQL Server Destination" (3600) failed the pre-execute phase and returned error code 0xC0202071.

i've checked the structure of my source and destination table but nothing seems to be wrong

if someone have ever faced these errors help me :D

Are you running the package on the same machine as the SQL Server instance that you are inserting into?

If not, it won't work. Use OLE DB Destination instead.

-Jamie

|||I have the same problem and everything is on the same machine. I am transfering from records from multiple tables in one database to a single table in another database. The destinations all have table lock OFF and it usually works. However, recently, I started getting this message. Also, if I rerun it, it happens on different tasks (the tasks are simultaneous 2 at a time).|||

Hi,

I'm having this issue as well, i.e. a package using the "SQL Server Destination" data flow component, loading a server on the same machine (a named instance) that the SSIS package is executing fails with an "Unable to prepare the SSIS bulk insert for data insertion." error. I suspect the windows account executing the package or the SQL login it's mapped to don't have some required permission, but I've tried making the sql account dbo, bulkadmin etc, and added to the local administrators group with no luck. I've seen this reported several times, but no solution?

Dave

|||

Hi guys,

I've mentioned some pre-requisites to getting SQL Server Destination working here:

Destination Adapter Comparison
(http://blogs.conchango.com/jamiethomson/archive/2006/08/14/SSIS_3A00_-Destination-Adapter-Comparison.aspx)

which may or may not be useful. I hope it is.

-Jamie

Integration services Globla variable and scheduling issue

I have one main package from which i am executing 5 other packages.I want to use one single DB connection in all the packages which i am declaring in Main package and it should be available in all the child packages. How can i Do this?

Few Variables are common is all the packages so i want to keep it Globally so how can i access those variables ?

How can i schedule package in sql server?

Thanks

Priyank Gajera wrote:

I have one main package from which i am executing 5 other packages.I want to use one single DB connection in all the packages which i am declaring in Main package and it should be available in all the child packages. How can i Do this?

You can use package configuration in your children packages based on a parent package variable to set the connection strings.

Priyank Gajera wrote:

Few Variables are common is all the packages so i want to keep it Globally so how can i access those variables ?

You meant, how the children packages can 'see' the value of a variable in the parent package? Foe that use package configuration as well as described in the previos answer.

Priyank Gajera wrote:

How can i schedule package in sql server?

Using SQL Agent; create a job to run your package. There are a lot of information around that in this forum.

Rafael Salas

|||

Parent Package configurations can get a little tricky. The easiest thing to do is set up an environment variable with your connection string, and then configure your packages to use that environment variable.

To configure your packages go to the SSIS-->Configuration menu, and then in the drop down choose Environment Variable. Map that to the connection string of the connection manager you are using.

Sql Agent will schedule your package to run. You can access it through the Sql Management Studio.

|||

Instead of using variable, Can I use the same connection for child packages which I am using in Main package ?

And if there is no other way then how can I asign a variable value(Connection Staring) to a Ole DB connection manager?

|||

Hi Rafael

Can we share Connections( Ole DB or FTP Comnnection) between packages?

Can you give one example of using parent package variable or if not possible, then please describe me the procedure.

Thanks

Priyank Gajera

|||Hi,

Thanks for the answer

I am able to get other variables or values in child package using parent package variable but I don't know how to assign any variable to OLD DB connection manager or FTP Connection manager(I am using both type of connection manager as well as Flat file connection)

So please guide me how to Assign connection string to FTP , OLD DB or Flat file connection managers?|||

Priyank Gajera wrote:

Hi,

Thanks for the answer

I am able to get other variables or values in child package using parent package variable but I don't know how to assign any variable to OLD DB connection manager or FTP Connection manager(I am using both type of connection manager as well as Flat file connection)

So please guide me how to Assign connection string to FTP , OLD DB or Flat file connection managers?

The procedure is pretty much described in Sean's post. Basically you need to place the connection string in some place is accessible for package configurations; this is Env. variable, registry key, xml file, etc. and the set the package configuration in you package to go there and 'grab' the connection string and assigned it to the connection string property of you connection manager.

Rafael Salas

Integration services Globla variable and scheduling issue

I have one main package from which i am executing 5 other packages.I want to use one single DB connection in all the packages which i am declaring in Main package and it should be available in all the child packages. How can i Do this?

Few Variables are common is all the packages so i want to keep it Globally so how can i access those variables ?

How can i schedule package in sql server?

Thanks

Priyank Gajera wrote:

I have one main package from which i am executing 5 other packages.I want to use one single DB connection in all the packages which i am declaring in Main package and it should be available in all the child packages. How can i Do this?

You can use package configuration in your children packages based on a parent package variable to set the connection strings.

Priyank Gajera wrote:

Few Variables are common is all the packages so i want to keep it Globally so how can i access those variables ?

You meant, how the children packages can 'see' the value of a variable in the parent package? Foe that use package configuration as well as described in the previos answer.

Priyank Gajera wrote:

How can i schedule package in sql server?

Using SQL Agent; create a job to run your package. There are a lot of information around that in this forum.

Rafael Salas

|||

Parent Package configurations can get a little tricky. The easiest thing to do is set up an environment variable with your connection string, and then configure your packages to use that environment variable.

To configure your packages go to the SSIS-->Configuration menu, and then in the drop down choose Environment Variable. Map that to the connection string of the connection manager you are using.

Sql Agent will schedule your package to run. You can access it through the Sql Management Studio.

|||

Instead of using variable, Can I use the same connection for child packages which I am using in Main package ?

And if there is no other way then how can I asign a variable value(Connection Staring) to a Ole DB connection manager?

|||

Hi Rafael

Can we share Connections( Ole DB or FTP Comnnection) between packages?

Can you give one example of using parent package variable or if not possible, then please describe me the procedure.

Thanks

Priyank Gajera

|||Hi,

Thanks for the answer

I am able to get other variables or values in child package using parent package variable but I don't know how to assign any variable to OLD DB connection manager or FTP Connection manager(I am using both type of connection manager as well as Flat file connection)

So please guide me how to Assign connection string to FTP , OLD DB or Flat file connection managers?|||

Priyank Gajera wrote:

Hi,

Thanks for the answer

I am able to get other variables or values in child package using parent package variable but I don't know how to assign any variable to OLD DB connection manager or FTP Connection manager(I am using both type of connection manager as well as Flat file connection)

So please guide me how to Assign connection string to FTP , OLD DB or Flat file connection managers?

The procedure is pretty much described in Sean's post. Basically you need to place the connection string in some place is accessible for package configurations; this is Env. variable, registry key, xml file, etc. and the set the package configuration in you package to go there and 'grab' the connection string and assigned it to the connection string property of you connection manager.

Rafael Salas