Cleanup / Foreign key

I have an address table I set up so that I can start our address campaign.
There is a field (open entry) called Business Type that I allowed the works
to enter to describe the business type. Now that the entry is complete ,
I'm in cleanup phase. Now, my question is: I know I need to make Business
Type a foreign key and select distinct to insert them into their own table,
but should I do that before I clean up the inconsistencies or after. There
are about 2000 different business types, some are minor like - Car Sales -
Used and Car Sales _ Used. I'm creating a system that allows users to
search based on business type.
Before or after, why?Before!
Why? Do not allow bad data into the database in the first place, it is a
bitch getting it out.
Jay
<programmingcodeATjards.com> wrote in message
news:uVedQl9%23HHA.320@.TK2MSFTNGP04.phx.gbl...
>I have an address table I set up so that I can start our address campaign.
>There is a field (open entry) called Business Type that I allowed the works
>to enter to describe the business type. Now that the entry is complete ,
>I'm in cleanup phase. Now, my question is: I know I need to make Business
>Type a foreign key and select distinct to insert them into their own table,
>but should I do that before I clean up the inconsistencies or after. There
>are about 2000 different business types, some are minor like - Car Sales -
>Used and Car Sales _ Used. I'm creating a system that allows users to
>search based on business type.
> Before or after, why?
>sqlsql

Checking data and emailing the results....

Hello,

I am trying to use “SQL Server Business Intelligence Development Studio” (what is the short name for this) to complete a check and then possibly email me depending on what happens.

The check is around two tables that I have. There is a chance that they will become out of sync with each other and so I want to know when a field (ACCOUNTID) has a value that is in one table and not the other. I can do the check but I would like the package to email me when there is a problem (and if possible email me the details) and do nothing when everything is OK.

Can anybody give me some hints to point me in the correct direction as I just seem to be chasing my tail at the moment,

Thanks,

Tyrone Fay wrote:

Hello,

I am trying to use “SQL Server Business Intelligence Development Studio” (what is the short name for this) to complete a check and then possibly email me depending on what happens.

BIDS!!

Tyrone Fay wrote:

The check is around two tables that I have. There is a chance that they will become out of sync with each other and so I want to know when a field (ACCOUNTID) has a value that is in one table and not the other. I can do the check but I would like the package to email me when there is a problem (and if possible email me the details) and do nothing when everything is OK.

Can anybody give me some hints to point me in the correct direction as I just seem to be chasing my tail at the moment,

Thanks,

How about loading all of the offending data into a flat file and then use the SendMail task to send that file to you as an attachment?

-Jamie

|||I like the idea... thank you,|||

Hi,

This is all working, but I am getting the file sent even when there are no values in it, which is causing some aggravation.

One solution I have tried is to wrap it with an error so that if there is no data to be returned the process errors and then the email is never sent. Yet this does mean that if you look at the agent it is not clear if the agent has an error or there was data to be sent and therefore this has caused the agent to error.

Does anybody have any ideas on a cleaner way (if possible) to make sure the file is only sent when there is data to be sent?

Thanks again,

|||

Instead of a file I use a variable.. then I have a script task to check if the variable is empty or not and crafting an appropriate message body based on the value of the variable before sending the email. I don't use a file at all.. I put all the errors etc into the message body.

You should be able to adapt this method for an attachment or not as well - just have the script output a value, have 2 email objects: one that goes with an attachment and one that doesnt and use an expression (or "Expression and Constraint") between them and the script. If its simple you can just do it all in the expression and skip the script object altogether.