error output - ignore just one column?

When configuring error output, I want everything that is good in the row to make it to the destination, and then the offending column that is causing an error to be set to NULL, and then sent to the destination as well. In addition, I want to take the offending column's data, and route it over to an error holding table. I know about the ability to redirect the whole row, but I just sort of want to redirect just that column. For example....

Have a table with 5 columns

col1 int null,

col2 int null,

col3 char(3) null,

col4 bit null,

col5 int null

My data flow loads data from a flat file and has a record that looks like this

1 5 ABC R 3

I want the row to make it to the destination as follows....

1 5 ABC NULL 3

Then the offending data needs to go over to my error table

err# errcolumn errdata errdesc

1 col4 R Could not convert "R" to bit data type

Any way to do this?

You could create a custom component, or a script component. Otherwise, you'd need to redirect the row, and use a multicast to send it to the error destination and put the row back into the main flow, after correcting the data.|||And even though you've redirected the row, you can still just operate on one column. The problem is knowing which column is in error.|||

Yes - this is the problem - the users who review the etl loads want to see: "You had invalid data in the BirthDate column". They do not want to see - "You had invalid data somewhere on record # 26".

I really want to stay inside SSIS to do these data type checks, but I'm leaning towards shoving everything into the engine & using isdate(), isnumeric(), etc. to get the flagrant data conversion stuff cleaned up. Then bring it back into SSIS to do lookups & "softer" validations. My real table (unlike my example) has 50+ columns to do checks on, and I don't want 50 branches or more in my SSIS package, all with error flows, and unions everywhere.

Is there any way of keeping this in SSI S, using the Data conversion/Derived Column transforms, and capturing column specific errors, instead of generic row errors?

|||Take a look at this post - it may have an answer for you - http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1509836&SiteID=1|||I saw that yesterday - it looked promising, but I'm relatively sure that the folks who run the servers here won't allow anything other than the Microsoft released stuff onto the servers. I'm toying with a workaround right now that hopefully will work........I'll check back in.sql

error output - ignore just one column?

When configuring error output, I want everything that is good in the row to make it to the destination, and then the offending column that is causing an error to be set to NULL, and then sent to the destination as well. In addition, I want to take the offending column's data, and route it over to an error holding table. I know about the ability to redirect the whole row, but I just sort of want to redirect just that column. For example....

Have a table with 5 columns

col1 int null,

col2 int null,

col3 char(3) null,

col4 bit null,

col5 int null

My data flow loads data from a flat file and has a record that looks like this

1 5 ABC R 3

I want the row to make it to the destination as follows....

1 5 ABC NULL 3

Then the offending data needs to go over to my error table

err# errcolumn errdata errdesc

1 col4 R Could not convert "R" to bit data type

Any way to do this?

You could create a custom component, or a script component. Otherwise, you'd need to redirect the row, and use a multicast to send it to the error destination and put the row back into the main flow, after correcting the data.|||And even though you've redirected the row, you can still just operate on one column. The problem is knowing which column is in error.|||

Yes - this is the problem - the users who review the etl loads want to see: "You had invalid data in the BirthDate column". They do not want to see - "You had invalid data somewhere on record # 26".

I really want to stay inside SSIS to do these data type checks, but I'm leaning towards shoving everything into the engine & using isdate(), isnumeric(), etc. to get the flagrant data conversion stuff cleaned up. Then bring it back into SSIS to do lookups & "softer" validations. My real table (unlike my example) has 50+ columns to do checks on, and I don't want 50 branches or more in my SSIS package, all with error flows, and unions everywhere.

Is there any way of keeping this in SSI S, using the Data conversion/Derived Column transforms, and capturing column specific errors, instead of generic row errors?

|||Take a look at this post - it may have an answer for you - http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1509836&SiteID=1|||I saw that yesterday - it looked promising, but I'm relatively sure that the folks who run the servers here won't allow anything other than the Microsoft released stuff onto the servers. I'm toying with a workaround right now that hopefully will work........I'll check back in.

Error number: 37000

All,
I've been configuring Transactional replication in accordance with the
'High Availability' standards, and it seems the initial snapshots have been
transferred but when looking at my distribution agent I can see this error:
Last Command:
{CALL sp_MSdel_Alliance UniChem Norge (RBO)$Customer Posting Group ('ALA')}
Transaction sequence number and command ID of last execution batch are
0x0001A7C400002F8C000900000000 and 1.
Error Details:
Syntax error or access violation
(Source: MOSS-KIPPER (ODBC); Error number: 37000)
Anyone have a rough idea of what to look at?
James McM
sounds like your table name has some characters in it which are causing
problems.
What is the underlying table name?
You can work around this by right clicking on your publication, selecting
properties, articles, and then click on the browse button (the three
ellipses to the right of your table name), and click on the command tab.
Enter a proc name that looks like this:
sp_MSdel_Alliance_UniChem_Norge_RBO_Customer_Posti ng_Group
Do this for the update and insert procs as well, so they look like this:
sp_MSupd_Alliance_UniChem_Norge_RBO_Customer_Posti ng_Group
sp_MSins_Alliance_UniChem_Norge_RBO_Customer_Posti ng_Group
Now regenerate and redistribute your snapshot.
You also have the option of doing a no sync subscription, and then doing
validations to determine which tables are out of sync and manually sync
them.
To do a nosync subscription you will need to generate the procs.
To do this change the proc names as illustrated above and then on your
publisher in your publication database.
sp_addpublication 'dummy'
sp_addarticle 'dummy','tablename','tablename'
sp_scriptpublicationcustomprocs 'dummy' and in your results pane you will
find the procs. Copy these procs and run them in your subscription database.
"James McMorrough" <james.mcmorrough@.getrid.sqlpersonnel.com> wrote in
message news:OxNFmtOwEHA.3320@.TK2MSFTNGP14.phx.gbl...
> All,
> I've been configuring Transactional replication in accordance with the
> 'High Availability' standards, and it seems the initial snapshots have
> been
> transferred but when looking at my distribution agent I can see this
> error:
> Last Command:
> {CALL sp_MSdel_Alliance UniChem Norge (RBO)$Customer Posting Group
> ('ALA')}
> Transaction sequence number and command ID of last execution batch are
> 0x0001A7C400002F8C000900000000 and 1.
> Error Details:
> Syntax error or access violation
> (Source: MOSS-KIPPER (ODBC); Error number: 37000)
>
> Anyone have a rough idea of what to look at?
> James McM
>

Error message

I'm creating a vb application which uses ADO on an SQL 2000 db. I went through all the steps of configuring my ado control but when i try to set my data field in my text box for my table named orders I get the following error:

Invalid Object Name "orders"

How do i fix this?Has anyone found a way to fix this?