Its to slow when I handle XML field in SQL 2005!

It's to slow when I handle XML field in SQL 2005!

I find that it's slow when I handle XML field (maybe only stored 200kb date) in SQL 2005! Especially when I insert a sub tree to XML field !
I have indexed the XML field.

Is it possible that the XML field is not ripe in SQL 2005 database?

Can you quantify "slow"?|||I made a online bookmark program with ASP.Net 2.0+ SQL 2005, I stored bookmarks in a XML Field, I find it's very slow when I insert, edit or delete bookmark, especially import bookmark to a folder selected.you can try it athttp://www.hellocw.com/onlinebookmark/Default.aspx|||

I'm not sure what you mean by very slow... I just tried your site, clicking on the recently added pages was instaneous... The page came up quickly, and I even created an account, added a page to a couple folders (root, sports), and I couldn't see it taking any time at all.

So..What is slow?

|||Hi,i too need the functionality of importing and exporting bookmarks to my website will u plshelp me in this i mean will u pls give me some code sample.

its been a while since Ive used sql....

...and I was using mySql b4, but on ms sql server when you create a table whats the syntax for specifying a field that auto increments for each new record added?

'm trying the following:

1> create table maillist(id autoincrement(), email varchar)
2> go
Msg 170, Level 15, State 1, Server ****, Line 1
Line 1: Incorrect syntax near ')'.
1>IDENTITY is the keyword used in SQL Server

CREATE TABLE [dbo].[Debug] (
[DebugID] [int] IDENTITY (1, 1) NOT NULL ,
[DateEntered] [datetime] NULL ,
[Message] [nvarchar] (4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

|||i take it

dbo = the db name

debug = the table

dbugid = my field name

int = type

what is: on [primary]?|||what does the (1,1) signify?|||The seed value and incremental step for the identity column. (e.g starts from 1 and increases 1 at a time 1,2,3,4,5...)|||ahhh

I getcha :)|||dbo is the owner of the table (dbo is best).
Debug is the table.
debugid is the field name.

Primary is the file group (you can normally ignore it, this is script generated by the Enterprise Manager).

Iteration through many tables to perform update

...Ok so I have one field that exists in an many ARCHIVE tables(approx 25).
The field name is the same throughout these tables and each table is created
in weekly intervals.
I want to be able to update the field for all the rows in each of these
table in one lump of an update sProc.
My initial ideas areto query sysobjects for the tables I want and then
iterate through them one by one until the update is complete (maybe using
dynamic sql). This is a once off update and performance time is the key.
Any ideas / examples?>I forgot to add, My table has 10million rows and i estimate an runtime of
5.5hrs. Here's the clincher though. I only have a 5 hr window to complete th
e
update.
"marcmc" wrote:

> ...Ok so I have one field that exists in an many ARCHIVE tables(approx 25)
.
> The field name is the same throughout these tables and each table is creat
ed
> in weekly intervals.
> I want to be able to update the field for all the rows in each of these
> table in one lump of an update sProc.
> My initial ideas areto query sysobjects for the tables I want and then
> iterate through them one by one until the update is complete (maybe using
> dynamic sql). This is a once off update and performance time is the key.
> Any ideas / examples?>
>|||Are all these tables of the same structure, i.e. exact same column names and
data types across all tables? If so, it sounds like a partitioned view.
You could then update the particular column.
Briefly, you create CHECK constraints on the partitioning column on each
table. The partitioning column must be part of the primary key. Then you
create a view like:
create view MyView
as
select * from MyTable1
union all
select * from MyTable2
union all
...
go
update MyView
set
MyCol = 'XYZ'
As for your window, large updates take time. You may want to do a
background iterative method, as long as logical consistency is not an issue.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:8661C481-F4F0-4ED4-97E2-74BB47D70F4A@.microsoft.com...
I forgot to add, My table has 10million rows and i estimate an runtime of
5.5hrs. Here's the clincher though. I only have a 5 hr window to complete
the
update.
"marcmc" wrote:

> ...Ok so I have one field that exists in an many ARCHIVE tables(approx
> 25).
> The field name is the same throughout these tables and each table is
> created
> in weekly intervals.
> I want to be able to update the field for all the rows in each of these
> table in one lump of an update sProc.
> My initial ideas areto query sysobjects for the tables I want and then
> iterate through them one by one until the update is complete (maybe using
> dynamic sql). This is a once off update and performance time is the key.
> Any ideas / examples?>
>

Issues with Image data

Hello.
We're running SQL Server 2000.
I am considering creating Image columns for one of our databases. Currently
we have a varchar field that holds the path to the actual image which is
stored on an NTFS file system. The average image size is less than 256K.
What issues arise when considering Backup/Restore and Database recovery.
Any other insights are welcome.
Thanks in advance,
Mike"Mike Lopez" <MichaelLopez@.inds.com> wrote in message
news:Oz%23B9ZG7EHA.1400@.TK2MSFTNGP11.phx.gbl...
> Hello.
> We're running SQL Server 2000.
> I am considering creating Image columns for one of our databases.
> Currently we have a varchar field that holds the path to the actual image
> which is stored on an NTFS file system. The average image size is less
> than 256K.
> What issues arise when considering Backup/Restore and Database recovery.
> Any other insights are welcome.
> Thanks in advance,
> Mike
>
>
There are all kinds of performance issues to think about.
1. Someone may correct me on this one, but.. Each image will store a 16
byte pointer in the row in your regular base table. That pointer will then
point to a private 8k data page where your image will be stored. That means
that each image will take up at least 8k of database storage space and in
your case, a chain of 32 8k data pages that will need to be read from and
written to. That is a killer on disk i/o for SQL Server.
2. Having those images in the same table *could* really slow down the table
on your joins. You may wish to place the images in a 1 to 1 relationship
in a separate table.
2a. If you place those images in a separate table, you *could* put that one
table on it's own filegroup and then perform filegroup backups and restores.
This may improve your backup and recovery strategy especially if the images
don't change often. (eg. You would back up the images filegroup far less.
Restores would be quicker as you only need to do the affected filegroups and
TLogs.)
3. If you use the images frequently (reads/writes) put them on the fastest
read/write drives as possible. I would suggest a RAID 1 (striped - no
parity) and mirror that drive (RAID 0) if you need that type of failsafe.
HTH
Rick Sawtell
MCT, MCSD, MCDBA|||Thank you Rick. Yes, that helps.
I'm leaning towards keeping the current system intact (image file in a
directory, image file's path in table column). I feel there's too much
overhead involved. I also don't like the idea that the image would not be
directly viewable by third-party image viewers.
Thanks again,
Mike
"Rick Sawtell" <quickening@.msn.com> wrote in message
news:eoFr3FO7EHA.2180@.TK2MSFTNGP10.phx.gbl...
> "Mike Lopez" <MichaelLopez@.inds.com> wrote in message
> news:Oz%23B9ZG7EHA.1400@.TK2MSFTNGP11.phx.gbl...
>
> There are all kinds of performance issues to think about.
> 1. Someone may correct me on this one, but.. Each image will store a 16
> byte pointer in the row in your regular base table. That pointer will
> then point to a private 8k data page where your image will be stored.
> That means that each image will take up at least 8k of database storage
> space and in your case, a chain of 32 8k data pages that will need to be
> read from and written to. That is a killer on disk i/o for SQL Server.
> 2. Having those images in the same table *could* really slow down the
> table on your joins. You may wish to place the images in a 1 to 1
> relationship in a separate table.
> 2a. If you place those images in a separate table, you *could* put that
> one table on it's own filegroup and then perform filegroup backups and
> restores. This may improve your backup and recovery strategy especially if
> the images don't change often. (eg. You would back up the images
> filegroup far less. Restores would be quicker as you only need to do the
> affected filegroups and TLogs.)
> 3. If you use the images frequently (reads/writes) put them on the
> fastest read/write drives as possible. I would suggest a RAID 1
> (striped - no parity) and mirror that drive (RAID 0) if you need that type
> of failsafe.
>
> HTH
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>sql

Issues with Image data

Hello.
We're running SQL Server 2000.
I am considering creating Image columns for one of our databases. Currently
we have a varchar field that holds the path to the actual image which is
stored on an NTFS file system. The average image size is less than 256K.
What issues arise when considering Backup/Restore and Database recovery.
Any other insights are welcome.
Thanks in advance,
Mike
"Mike Lopez" <MichaelLopez@.inds.com> wrote in message
news:Oz%23B9ZG7EHA.1400@.TK2MSFTNGP11.phx.gbl...
> Hello.
> We're running SQL Server 2000.
> I am considering creating Image columns for one of our databases.
> Currently we have a varchar field that holds the path to the actual image
> which is stored on an NTFS file system. The average image size is less
> than 256K.
> What issues arise when considering Backup/Restore and Database recovery.
> Any other insights are welcome.
> Thanks in advance,
> Mike
>
>
There are all kinds of performance issues to think about.
1. Someone may correct me on this one, but.. Each image will store a 16
byte pointer in the row in your regular base table. That pointer will then
point to a private 8k data page where your image will be stored. That means
that each image will take up at least 8k of database storage space and in
your case, a chain of 32 8k data pages that will need to be read from and
written to. That is a killer on disk i/o for SQL Server.
2. Having those images in the same table *could* really slow down the table
on your joins. You may wish to place the images in a 1 to 1 relationship
in a separate table.
2a. If you place those images in a separate table, you *could* put that one
table on it's own filegroup and then perform filegroup backups and restores.
This may improve your backup and recovery strategy especially if the images
don't change often. (eg. You would back up the images filegroup far less.
Restores would be quicker as you only need to do the affected filegroups and
TLogs.)
3. If you use the images frequently (reads/writes) put them on the fastest
read/write drives as possible. I would suggest a RAID 1 (striped - no
parity) and mirror that drive (RAID 0) if you need that type of failsafe.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
|||Thank you Rick. Yes, that helps.
I'm leaning towards keeping the current system intact (image file in a
directory, image file's path in table column). I feel there's too much
overhead involved. I also don't like the idea that the image would not be
directly viewable by third-party image viewers.
Thanks again,
Mike
"Rick Sawtell" <quickening@.msn.com> wrote in message
news:eoFr3FO7EHA.2180@.TK2MSFTNGP10.phx.gbl...
> "Mike Lopez" <MichaelLopez@.inds.com> wrote in message
> news:Oz%23B9ZG7EHA.1400@.TK2MSFTNGP11.phx.gbl...
>
> There are all kinds of performance issues to think about.
> 1. Someone may correct me on this one, but.. Each image will store a 16
> byte pointer in the row in your regular base table. That pointer will
> then point to a private 8k data page where your image will be stored.
> That means that each image will take up at least 8k of database storage
> space and in your case, a chain of 32 8k data pages that will need to be
> read from and written to. That is a killer on disk i/o for SQL Server.
> 2. Having those images in the same table *could* really slow down the
> table on your joins. You may wish to place the images in a 1 to 1
> relationship in a separate table.
> 2a. If you place those images in a separate table, you *could* put that
> one table on it's own filegroup and then perform filegroup backups and
> restores. This may improve your backup and recovery strategy especially if
> the images don't change often. (eg. You would back up the images
> filegroup far less. Restores would be quicker as you only need to do the
> affected filegroups and TLogs.)
> 3. If you use the images frequently (reads/writes) put them on the
> fastest read/write drives as possible. I would suggest a RAID 1
> (striped - no parity) and mirror that drive (RAID 0) if you need that type
> of failsafe.
>
> HTH
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>

Issues with Image data

Hello.
We're running SQL Server 2000.
I am considering creating Image columns for one of our databases. Currently
we have a varchar field that holds the path to the actual image which is
stored on an NTFS file system. The average image size is less than 256K.
What issues arise when considering Backup/Restore and Database recovery.
Any other insights are welcome.
Thanks in advance,
Mike"Mike Lopez" <MichaelLopez@.inds.com> wrote in message
news:Oz%23B9ZG7EHA.1400@.TK2MSFTNGP11.phx.gbl...
> Hello.
> We're running SQL Server 2000.
> I am considering creating Image columns for one of our databases.
> Currently we have a varchar field that holds the path to the actual image
> which is stored on an NTFS file system. The average image size is less
> than 256K.
> What issues arise when considering Backup/Restore and Database recovery.
> Any other insights are welcome.
> Thanks in advance,
> Mike
>
>
There are all kinds of performance issues to think about.
1. Someone may correct me on this one, but.. Each image will store a 16
byte pointer in the row in your regular base table. That pointer will then
point to a private 8k data page where your image will be stored. That means
that each image will take up at least 8k of database storage space and in
your case, a chain of 32 8k data pages that will need to be read from and
written to. That is a killer on disk i/o for SQL Server.
2. Having those images in the same table *could* really slow down the table
on your joins. You may wish to place the images in a 1 to 1 relationship
in a separate table.
2a. If you place those images in a separate table, you *could* put that one
table on it's own filegroup and then perform filegroup backups and restores.
This may improve your backup and recovery strategy especially if the images
don't change often. (eg. You would back up the images filegroup far less.
Restores would be quicker as you only need to do the affected filegroups and
TLogs.)
3. If you use the images frequently (reads/writes) put them on the fastest
read/write drives as possible. I would suggest a RAID 1 (striped - no
parity) and mirror that drive (RAID 0) if you need that type of failsafe.
HTH
Rick Sawtell
MCT, MCSD, MCDBA|||Thank you Rick. Yes, that helps.
I'm leaning towards keeping the current system intact (image file in a
directory, image file's path in table column). I feel there's too much
overhead involved. I also don't like the idea that the image would not be
directly viewable by third-party image viewers.
Thanks again,
Mike
"Rick Sawtell" <quickening@.msn.com> wrote in message
news:eoFr3FO7EHA.2180@.TK2MSFTNGP10.phx.gbl...
> "Mike Lopez" <MichaelLopez@.inds.com> wrote in message
> news:Oz%23B9ZG7EHA.1400@.TK2MSFTNGP11.phx.gbl...
>> Hello.
>> We're running SQL Server 2000.
>> I am considering creating Image columns for one of our databases.
>> Currently we have a varchar field that holds the path to the actual image
>> which is stored on an NTFS file system. The average image size is less
>> than 256K.
>> What issues arise when considering Backup/Restore and Database recovery.
>> Any other insights are welcome.
>> Thanks in advance,
>> Mike
>>
>
> There are all kinds of performance issues to think about.
> 1. Someone may correct me on this one, but.. Each image will store a 16
> byte pointer in the row in your regular base table. That pointer will
> then point to a private 8k data page where your image will be stored.
> That means that each image will take up at least 8k of database storage
> space and in your case, a chain of 32 8k data pages that will need to be
> read from and written to. That is a killer on disk i/o for SQL Server.
> 2. Having those images in the same table *could* really slow down the
> table on your joins. You may wish to place the images in a 1 to 1
> relationship in a separate table.
> 2a. If you place those images in a separate table, you *could* put that
> one table on it's own filegroup and then perform filegroup backups and
> restores. This may improve your backup and recovery strategy especially if
> the images don't change often. (eg. You would back up the images
> filegroup far less. Restores would be quicker as you only need to do the
> affected filegroups and TLogs.)
> 3. If you use the images frequently (reads/writes) put them on the
> fastest read/write drives as possible. I would suggest a RAID 1
> (striped - no parity) and mirror that drive (RAID 0) if you need that type
> of failsafe.
>
> HTH
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>

Issue: How to convert a database field from varchar to datetime..?

Can anyone help me on this!
I've got more than a 1000 records in a SQL server database.
The problem is that the the date field is set to varchar, and that gives a lot of trouble. (for example by sorting a table, it's a mess)
How can i make sure that i will have a table with the date field set to datettime en that those 1000 records still will be in it.
thanks in advance!

You should be able to just convert them to datetime select cast(varDateField as dateTime) from yourTable. The only problem you will have is if you dont have date fields in any of the data.

Nick

Issue with the ForEachLoop Task in SSIS

Hi,

I am using a SQL task to execute a stored procedure which returns a single field with multiple records. I want the records returned by the stored procedure to be processed one by one within a ForEachLoop container. How do I assign the records one by one to one variable and use it in a Script task running inside the ForEachLoop container.

I am using 2 tasks in my package.

In my first task I call a SQL task that executes a stored procedure which returns a list of reference numbers (TrackData). This works perfectly.

However, in my second task I must use the ForEachLoop task to loop through the above list and set the value of var_TrackData (a user variable declared by me) with the value of the TrackData present in it during that particular loop.

I am not sure how to go about the second task. Any help would be greatly appreciated.

Create an object variable.

In the execute sql task set the resultset to the variable name (set result name to 0).

Create a variable to hold the reference numbers (int or string?)

Create a for each loop task

Set the collection to for each ADO enumerator

Set the ADO object source variable to your object variable name (which contains the resultset).

In variable mappings set the variable to the variable name you created to hold the reference numbers

Set the precednce so that the execute sql runs before the for each llop.

Now when this runs the object variable will be set to the resultset the the for each loop will itterate through it setting the variable to the reference number for each loop.

|||

See if this post gives you some idea of how you can do it:

http://rafael-salas.blogspot.com/2006/12/import-header-line-tables-into-dynamic_22.html

Issue with FORMAT use in textboxes when null value

SQL 2005 Dev, VS 2005

I use the following in my VS-created reports:

FORMAT("ShortDate", Fields!someField.value)

However, when the field returns a null field what displays in the tex box instead is the words "ShortDate". Can this be corrected?

Thanks in advance

Please reverse the order of the arguments, and use DateTime format strings as shown in the MSDN documentation:

* Standard datetime formats:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconstandarddatetimeformatstrings.asp

* Custom datetime formats:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconstandarddatetimeformatstrings.asp

In your case, you should try e.g. =Format(Fields!someField.Value, "d")

Alternatively, you can set the textbox Value property to =Fields!someValue.Value and set the Format property of the textbox to the desired format code.

-- Robert

Issue with fields without value

Hi all of you,

I'm loading a Sql destination table via flat file. Everything is file but when one concrete field is empty in my file

it generates an error. Why?

The first 10000 lines have data from 75 position till 85 in my file. But the 10001 no but at the same time I want to load

the rest of the fields

In my Sql table, of course that fields keep nulls.

When I ran DTS this casuistry worked fine.

[Destino 193_TMP [28]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Invalid character value for cast specification". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Invalid character value for cast specification". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Invalid character value for cast specification".

Thanks in advance for your help and thoughts,

Hi ya,

It seems that when you're casting it, it doesn't appear to be of right value for the casting function. If you don't have a casting function do put in the casting function so that null value is ignored or recognised.

Hope that helps

Cheers

Rizwan

|||

I belive the problem is that you're trying to insert a value with the wrong type in your destination. That's what it is with me when i get this error.

Issue On Views

I have a view which has a date field. However, this date field is in the form of a number: 20041401. There are also places where the date is 0 (zero). I want to get this date to look like 1/14/04. I did this by creating a view on the first view, and used case to set the 0 (zero) values to null, and then I created another view on the last one to convert the not null values to a date using cast. Is there a simpler way to do what I want? Pretty soon I will lose track of all my views.
Thanks.Maybe you should cast or convert the datetime column the right way in your first view.|||I can't because the date string is in the form of a number. When I execute the view, and it comes across a 0 (zero) I get an error. Because cast can't convert a number to char.|||Not sure I follow..but...

DECLARE @.x int
SELECT @.x = 20041401
SELECT CONVERT(varchar(10),
CONVERT(datetime,SUBSTRING(CONVERT(char(8),@.x),7,2 )
+ '/' + SUBSTRING(CONVERT(char(8),@.x),5,2)
+ '/' + SUBSTRING(CONVERT(char(8),@.x),1,4)),1)|||Originally posted by exdter
I can't because the date string is in the form of a number. When I execute the view, and it comes across a 0 (zero) I get an error. Because cast can't convert a number to char.

Really?

SELECT CONVERT(varchar(10),0)|||That's what I need. Thanks.|||I strongly agree with the notion that, if you're dealing with date-type information that hasn't been stored as a Date field (and which, e.g. for application compatibility reasons, can't be converted now), it is highly desirable that you should use a base view that Casts the value to a date; then base other views on that.|||And doen't that make it an non-updateable view?|||Thanks.

issue oledb

I use oledb to insert the data to respective tables..

then i have a look up where i check for unique field of the data i inserted which shows up with an error No matter what i do...

It will help us help you if you post that error.|||

Here is the error

[Lookup 3 [39523]] Error: Row yielded no match during lookup.

[Lookup 3 [39523]] Error: The "component "Lookup 3" (39523)" failed because error code 0xC020901E occurred, and the error row disposition on "output "Lookup Output" (39525)" specifies failure on error. An error occurred on the specified object of the specified component.

[DTS.Pipeline] Error: The ProcessInput method on component "Lookup 3" (39523) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.

[DTS.Pipeline] Error: Thread "WorkThread0" has exited with error code 0xC0209029.

this is what i do..and shows the about error mentioned above..

After inserting using oledb command in the lookup i look up for two columns and then get respective key field to that columns and then i insert it to other table using oledb

|||

The Lookup component treats the no matches as errors; that is what you are seeing. Are you expecting to have a match for every row that pass trhough the Lup component?

if so; make sure the mapping inside of the lookup is right and the table/query that supports it has all the expected values.

If not; configure the error output of the component to either redirect or ignored the no matches.

|||

yes i am looking to see if there is a match using lookup.There should be a match as i am inserting the records just before having a lookup...But the lookup shows error(red). i have been looking for data in the table and the lookup which i am pointing to...they dont have any problem...one possible reson might be the lookup is running even before respective record is been inserted as oledb command is slow and lookup is fast..

|||

With the default settings, the lookup will cache all of the data as soon as the dataflow begins. If you are inserting the records in the dataflow, the lookup will not have them in cache. You can avoid this by going to the Advanced tab in the lookup editor and selecting Enable Memory Restriction. This will have an impact on performance, as now the lookup will query the database for each row passing through it.

If you have a lot of repeated values that you are trying to look up, you could play with the Enable caching options to see if you can get decent performance. With this option set, it doesn't cache anything at the start, but rows are cached as they are looked up from the source. If it doesn't find the row in cache, it will check the database.

|||thank You for your prompt reply.....this seem to work now....I have one more question for you...i use oledb as i transform incoming data to multiple tables at a time....which is very slow.....what would be the best solution for fast performance? if script component how do i need to do it please let me know how to use this or any links would help..|||

Please mark the answers that were helpful.

Have you looked at using a multicast with several OLEDB destinations?

|||No i havent used....when i connected oledb output to multicast is didnt show up with incoming columns..|||I just want to increase the performance since oledb is slow...|||

sureshv wrote:

No i havent used....when i connected oledb output to multicast is didnt show up with incoming columns..

You should have a source component connected to the multicast. You can then drag multiple outputs from the multicast to your destinations (OLEDB Destinations, not OLEDB Commands).

|||hey is there a way to enter some of the incoming data into one table and get the key to it and then insert the rest of the data along with the key into another table...I do it using oledb command which works...but is there any other way i can implement it which is faster...

issue oledb

I use oledb to insert the data to respective tables..

then i have a look up where i check for unique field of the data i inserted which shows up with an error No matter what i do...

It will help us help you if you post that error.|||

Here is the error

[Lookup 3 [39523]] Error: Row yielded no match during lookup.

[Lookup 3 [39523]] Error: The "component "Lookup 3" (39523)" failed because error code 0xC020901E occurred, and the error row disposition on "output "Lookup Output" (39525)" specifies failure on error. An error occurred on the specified object of the specified component.

[DTS.Pipeline] Error: The ProcessInput method on component "Lookup 3" (39523) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.

[DTS.Pipeline] Error: Thread "WorkThread0" has exited with error code 0xC0209029.

this is what i do..and shows the about error mentioned above..

After inserting using oledb command in the lookup i look up for two columns and then get respective key field to that columns and then i insert it to other table using oledb

|||

The Lookup component treats the no matches as errors; that is what you are seeing. Are you expecting to have a match for every row that pass trhough the Lup component?

if so; make sure the mapping inside of the lookup is right and the table/query that supports it has all the expected values.

If not; configure the error output of the component to either redirect or ignored the no matches.

|||

yes i am looking to see if there is a match using lookup.There should be a match as i am inserting the records just before having a lookup...But the lookup shows error(red). i have been looking for data in the table and the lookup which i am pointing to...they dont have any problem...one possible reson might be the lookup is running even before respective record is been inserted as oledb command is slow and lookup is fast..

|||

With the default settings, the lookup will cache all of the data as soon as the dataflow begins. If you are inserting the records in the dataflow, the lookup will not have them in cache. You can avoid this by going to the Advanced tab in the lookup editor and selecting Enable Memory Restriction. This will have an impact on performance, as now the lookup will query the database for each row passing through it.

If you have a lot of repeated values that you are trying to look up, you could play with the Enable caching options to see if you can get decent performance. With this option set, it doesn't cache anything at the start, but rows are cached as they are looked up from the source. If it doesn't find the row in cache, it will check the database.

|||thank You for your prompt reply.....this seem to work now....I have one more question for you...i use oledb as i transform incoming data to multiple tables at a time....which is very slow.....what would be the best solution for fast performance? if script component how do i need to do it please let me know how to use this or any links would help..|||

Please mark the answers that were helpful.

Have you looked at using a multicast with several OLEDB destinations?

|||No i havent used....when i connected oledb output to multicast is didnt show up with incoming columns..|||I just want to increase the performance since oledb is slow...|||

sureshv wrote:

No i havent used....when i connected oledb output to multicast is didnt show up with incoming columns..

You should have a source component connected to the multicast. You can then drag multiple outputs from the multicast to your destinations (OLEDB Destinations, not OLEDB Commands).

|||hey is there a way to enter some of the incoming data into one table and get the key to it and then insert the rest of the data along with the key into another table...I do it using oledb command which works...but is there any other way i can implement it which is faster...

Issue Exporting Date format to a delimited file.

In exporting from a OLEDB connection to a flat file.

In the originating table the field for DOB is in a varchar(10) format ex. 01/17/2007. The flat file connection destination is setup as a DT_STR. When you look at the OLEDB connection table preview you see it as 01/17/2007. When it is export to the delimited <CR><LF> <|> pipe delimited the format looks like this 01/17/2007 00:00:00. The issue would be resolved with a right ragged fixed width file. But this is not the requirement for the project format fot the file. I have tried delete and recreating the connections, and even tried doing a data conversation from the OLEDB connection to a char(10). Also, thourgh the transformation services with out any luck. On the flat file data connection I am using expressions to map to a declared variable path and variable name and I listed the expression language below also:

@.[User::varPATH]+ @.[User::varFileName]+ RIGHT("0" + (DT_WSTR, 2) MONTH( GETDATE() ), 2) + RIGHT("0" + (DT_WSTR, 2) DAY( GETDATE() ), 2) +RIGHT("0" + (DT_WSTR, 4) YEAR( GETDATE() ), 4) + ".txt"

If you can give some help in getting the file to export to a delimited "|" file in the format of "01/17/2007" this would be greatly aprreciated. I also forgot to mention that I have also tried putting a text qualifier in like" on the flat file destination column layout and get the other format still.

Thanks in advance.

Scott

Try a derived column transformation with the following:

(DT_STR,10,1252)(DT_DBDATE)[columnname]|||I could not reproduce your issue; if the column in the source table is varchar(10); I don't see how you can get the time part in the flat file. Unless you have something else between the source component and the flat destination that cast the DOB column to a datetime.|||

Rafael Salas wrote:

I could not reproduce your issue; if the column in the source table is varchar(10); I don't see how you can get the time part in the flat file. Unless you have something else between the source component and the flat destination that cast the DOB column to a datetime.

I agree. My guess is that the OLE source table is really a datetime field.|||Try again... It looks like it's auto correcting to a DT_DBDATE by default.|||

Wolfsvein wrote:

Try again... It looks like it's auto correcting to a DT_DBDATE by default.

Nope, not going to happen.

I just created a table:
CREATE TABLE [dbo].[forums_test3](
[column1] [char](40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_forums_test3_column1] DEFAULT (''),
[datecolumn] [varchar](12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

Then I inserted data:
insert into forums_test3 values (1,'01/17/2007')
insert into forums_test3 values (2,'01/18/2007')
insert into forums_test3 values (3,'01/19/2007')

Then I created a package with one data flow in it. Inside that data flow, I added an OLE DB source connector which points to the aforementioned table. I chose to add both columns to the data flow.

Then I added a flat file destination. I created a new flat file connection for a delimited file, where the vertical bar ("|") was the column separator.

I executed the package and got the following results:
01/17/2007|1
01/18/2007|2
01/19/2007|3

Somewhere, you have incorrect metadata.|||I ran the same test and got same results. What else do you have in the data flow? go to the advanced properties of the source component and check the data type of the column...|||The advance says DT_STR|||Are you using expression in the export of your table too?|||If you read the above state carefully it said the source was a varchar(10) and I even through sql try to create it as a varchar(10) on a convert statement.|||I even deleted the connection recreated like I said in the oringal description of the issue.|||

My test was a atable with only one column varchar(10); 2 rows with sothing like '01/12/2007'. The dataflow was an OLE Db souce and a Flat file destination. The connection manager for the flat file was created as delimited. Header row delimiter: Vertical bar (|); text qualifier: <None>

This is the code of the package:

<?xml version="1.0"?><DTS:Executable xmlns:DTS="www.microsoft.com/SqlServer/Dts" DTS:ExecutableType="MSDTS.Package.1"><DTS:Property DTS:Name="PackageFormatVersion">2</DTS:Property><DTS:Property DTS:Name="VersionComments"></DTS:Property><DTS:Property DTS:Name="CreatorName">NAM\rsalas</DTS:Property><DTS:Property DTS:Name="CreatorComputerName">KLPKA91</DTS:Property><DTS:Property DTS:Name="CreationDate" DTS:DataType="7">1/17/2007 10:59:52 AM</DTS:Property><DTS:Property DTS:Name="PackageType">5</DTS:Property><DTS:Property DTS:Name="ProtectionLevel">1</DTS:Property><DTS:Property DTS:Name="MaxConcurrentExecutables">-1</DTS:Property><DTS:Property DTS:Name="PackagePriorityClass">0</DTS:Property><DTS:Property DTS:Name="VersionMajor">1</DTS:Property><DTS:Property DTS:Name="VersionMinor">0</DTS:Property><DTS:Property DTS:Name="VersionBuild">5</DTS:Property><DTS:Property DTS:Name="VersionGUID">{8AC2348D-2416-4AE5-8A16-7BA2B820B7AA}</DTS:Property><DTS:Property DTS:Name="EnableConfig">0</DTS:Property><DTS:Property DTS:Name="CheckpointFileName"></DTS:Property><DTS:Property DTS:Name="SaveCheckpoints">0</DTS:Property><DTS:Property DTS:Name="CheckpointUsage">0</DTS:Property><DTS:Property DTS:Name="SuppressConfigurationWarnings">0</DTS:Property>

<DTS:ConnectionManager><DTS:Property DTS:Name="DelayValidation">0</DTS:Property><DTS:Property DTS:Name="ObjectName">Flat File Connection Manager 2</DTS:Property><DTS:Property DTS:Name="DTSID">{259C2EAB-15E8-40EB-8B91-EA273C23249E}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName">FLATFILE</DTS:Property><DTS:ObjectData><DTS:ConnectionManager><DTS:Property DTS:Name="FileUsageType">0</DTS:Property><DTS:Property DTS:Name="Format">Delimited</DTS:Property><DTS:Property DTS:Name="LocaleID">1033</DTS:Property><DTS:Property DTS:Name="Unicode">0</DTS:Property><DTS:Property DTS:Name="HeaderRowsToSkip">0</DTS:Property><DTS:Property DTS:Name="HeaderRowDelimiter" xml:space="preserve">_x007C_</DTS:Property><DTS:Property DTS:Name="ColumnNamesInFirstDataRow">0</DTS:Property><DTS:Property DTS:Name="RowDelimiter" xml:space="preserve"></DTS:Property><DTS:Property DTS:Name="DataRowsToSkip">0</DTS:Property><DTS:Property DTS:Name="TextQualifier">&lt;none&gt;</DTS:Property><DTS:Property DTS:Name="CodePage">1252</DTS:Property>

<DTS:FlatFileColumn><DTS:Property DTS:Name="ColumnType">Delimited</DTS:Property><DTS:Property DTS:Name="ColumnDelimiter" xml:space="preserve">_x000D__x000A_</DTS:Property><DTS:Property DTS:Name="ColumnWidth">0</DTS:Property><DTS:Property DTS:Name="MaximumWidth">10</DTS:Property><DTS:Property DTS:Name="DataType">129</DTS:Property><DTS:Property DTS:Name="DataPrecision">0</DTS:Property><DTS:Property DTS:Name="DataScale">0</DTS:Property><DTS:Property DTS:Name="TextQualified">-1</DTS:Property><DTS:Property DTS:Name="ObjectName">DOB</DTS:Property><DTS:Property DTS:Name="DTSID">{264F0CBC-933F-4506-BD4D-1CBB50E3507D}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:FlatFileColumn><DTS:Property DTS:Name="ConnectionString">C:\Temp\test3.txt</DTS:Property></DTS:ConnectionManager></DTS:ObjectData></DTS:ConnectionManager>

<DTS:ConnectionManager><DTS:Property DTS:Name="DelayValidation">0</DTS:Property><DTS:Property DTS:Name="ObjectName">klpka91.RafLab</DTS:Property><DTS:Property DTS:Name="DTSID">{D3313824-77A8-4270-A6EA-65C620E688C0}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName">OLEDB</DTS:Property><DTS:ObjectData><DTS:ConnectionManager><DTS:Property DTS:Name="Retain">0</DTS:Property><DTS:Property DTS:Name="ConnectionString">Data Source=klpka91;Initial Catalog=RafLab;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;</DTS:Property></DTS:ConnectionManager></DTS:ObjectData></DTS:ConnectionManager>

<DTS:PackageVariable><DTS:Property DTS:Name="PackageVariableValue" DTS:DataType="8">&lt;Package xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:dwd="http://schemas.microsoft.com/DataWarehouse/Designer/1.0"&gt;&lt;dwd:DtsControlFlowDiagram&gt;&lt;dwd:BoundingTop&gt;1000&lt;/dwd:BoundingTop&gt;&lt;dwd:Layout&gt;&lt;dds&gt;

&lt;diagram fontclsid="{0BE35203-8F91-11CE-9DE3-00AA004BB851}" mouseiconclsid="{0BE35204-8F91-11CE-9DE3-00AA004BB851}" defaultlayout="Microsoft.DataWarehouse.Layout.GraphLayout" defaultlineroute="Microsoft.DataWarehouse.Layout.GraphLayout" version="7" nextobject="5" scale="100" pagebreakanchorx="0" pagebreakanchory="0" pagebreaksizex="0" pagebreaksizey="0" scrollleft="0" scrolltop="0" gridx="150" gridy="150" marginx="1000" marginy="1000" zoom="100" x="21987" y="15346" backcolor="15334399" defaultpersistence="2" PrintPageNumbersMode="3" PrintMarginTop="0" PrintMarginBottom="635" PrintMarginLeft="0" PrintMarginRight="0" marqueeselectionmode="1" mousepointer="0" snaptogrid="0" autotypeannotation="1" showscrollbars="0" viewpagebreaks="0" donotforceconnectorsbehindshapes="1" backpictureclsid="{00000000-0000-0000-0000-000000000000}"&gt;

&lt;font&gt;

&lt;ddsxmlobjectstreamwrapper binary="01010000900180380100065461686f6d61" /&gt;

&lt;/font&gt;

&lt;mouseicon&gt;

&lt;ddsxmlobjectstreamwrapper binary="6c74000000000000" /&gt;

&lt;/mouseicon&gt;

&lt;/diagram&gt;

&lt;layoutmanager&gt;

&lt;ddsxmlobj /&gt;

&lt;/layoutmanager&gt;

&lt;ddscontrol controlprogid="DdsShapes.DdsObjectManagedBridge.1" tooltip="Data Flow Task" left="0" top="3598" logicalid="2" controlid="1" masterid="0" hint1="0" hint2="0" width="3598" height="1164" noresize="0" nomove="0" nodefaultattachpoints="0" autodrag="1" usedefaultiddshape="1" selectable="1" showselectionhandles="1" allownudging="1" isannotation="0" dontautolayout="0" groupcollapsed="0" tabstop="1" visible="1" snaptogrid="0"&gt;

&lt;control&gt;

&lt;ddsxmlobjectstreaminitwrapper binary="000800000e0e00008c040000" /&gt;

&lt;/control&gt;

&lt;layoutobject&gt;

&lt;ddsxmlobj&gt;

&lt;property name="LogicalObject" value="{CB60B1AF-9423-4C5F-B970-BA09DA63F1ED}" vartype="8" /&gt;

&lt;property name="ShowConnectorSource" value="0" vartype="2" /&gt;

&lt;/ddsxmlobj&gt;

&lt;/layoutobject&gt;

&lt;shape groupshapeid="0" groupnode="0" /&gt;

&lt;/ddscontrol&gt;

&lt;/dds&gt;&lt;/dwd:Layout&gt;&lt;/dwd:DtsControlFlowDiagram&gt;&lt;/Package&gt;</DTS:Property><DTS:Property DTS:Name="Namespace">dts-designer-1.0</DTS:Property><DTS:Property DTS:Name="ObjectName">{0FA525CC-1C4A-465D-8460-DB43EE25F44A}</DTS:Property><DTS:Property DTS:Name="DTSID">{EBC145DF-B9E7-4928-B523-27AA69956DA8}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:PackageVariable>

<DTS:PackageVariable><DTS:Property DTS:Name="PackageVariableValue" DTS:DataType="8">&lt;TaskHost xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:dwd="http://schemas.microsoft.com/DataWarehouse/Designer/1.0"&gt;&lt;dwd:DtsDataFlowDiagram&gt;&lt;dwd:BoundingTop&gt;-500&lt;/dwd:BoundingTop&gt;&lt;dwd:Layout&gt;&lt;dds&gt;

&lt;diagram fontclsid="{0BE35203-8F91-11CE-9DE3-00AA004BB851}" mouseiconclsid="{0BE35204-8F91-11CE-9DE3-00AA004BB851}" defaultlayout="Microsoft.DataWarehouse.Layout.GraphLayout" defaultlineroute="Microsoft.DataWarehouse.Layout.GraphLayout" version="7" nextobject="12" scale="100" pagebreakanchorx="0" pagebreakanchory="0" pagebreaksizex="0" pagebreaksizey="0" scrollleft="0" scrolltop="-1500" gridx="150" gridy="150" marginx="1000" marginy="1000" zoom="100" x="21987" y="14420" backcolor="15334399" defaultpersistence="2" PrintPageNumbersMode="3" PrintMarginTop="0" PrintMarginBottom="635" PrintMarginLeft="0" PrintMarginRight="0" marqueeselectionmode="1" mousepointer="0" snaptogrid="0" autotypeannotation="1" showscrollbars="0" viewpagebreaks="0" donotforceconnectorsbehindshapes="0" backpictureclsid="{00000000-0000-0000-0000-000000000000}"&gt;

&lt;font&gt;

&lt;ddsxmlobjectstreamwrapper binary="01000000900144420100065461686f6d61" /&gt;

&lt;/font&gt;

&lt;mouseicon&gt;

&lt;ddsxmlobjectstreamwrapper binary="6c74000000000000" /&gt;

&lt;/mouseicon&gt;

&lt;/diagram&gt;

&lt;layoutmanager&gt;

&lt;ddsxmlobj /&gt;

&lt;/layoutmanager&gt;

&lt;ddscontrol controlprogid="DdsShapes.DdsObjectManagedBridge.1" tooltip="OLE DB Source" left="27" top="1084" logicalid="4" controlid="1" masterid="0" hint1="0" hint2="0" width="3598" height="1164" noresize="0" nomove="0" nodefaultattachpoints="0" autodrag="1" usedefaultiddshape="1" selectable="1" showselectionhandles="1" allownudging="1" isannotation="0" dontautolayout="0" groupcollapsed="0" tabstop="1" visible="1" snaptogrid="0"&gt;

&lt;control&gt;

&lt;ddsxmlobjectstreaminitwrapper binary="000800000e0e00008c040000" /&gt;

&lt;/control&gt;

&lt;layoutobject&gt;

&lt;ddsxmlobj&gt;

&lt;property name="LogicalObject" value="{CB60B1AF-9423-4C5F-B970-BA09DA63F1ED}/components/1" vartype="8" /&gt;

&lt;property name="ShowConnectorSource" value="0" vartype="2" /&gt;

&lt;/ddsxmlobj&gt;

&lt;/layoutobject&gt;

&lt;shape groupshapeid="0" groupnode="0" /&gt;

&lt;/ddscontrol&gt;

&lt;ddscontrol controlprogid="DdsShapes.DdsObjectManagedBridge.1" tooltip="Flat File Destination" left="0" top="3369" logicalid="5" controlid="2" masterid="0" hint1="0" hint2="0" width="3598" height="1164" noresize="0" nomove="0" nodefaultattachpoints="0" autodrag="1" usedefaultiddshape="1" selectable="1" showselectionhandles="1" allownudging="1" isannotation="0" dontautolayout="0" groupcollapsed="0" tabstop="1" visible="1" snaptogrid="0"&gt;

&lt;control&gt;

&lt;ddsxmlobjectstreaminitwrapper binary="000800000e0e00008c040000" /&gt;

&lt;/control&gt;

&lt;layoutobject&gt;

&lt;ddsxmlobj&gt;

&lt;property name="LogicalObject" value="{CB60B1AF-9423-4C5F-B970-BA09DA63F1ED}/components/87" vartype="8" /&gt;

&lt;property name="ShowConnectorSource" value="0" vartype="2" /&gt;

&lt;/ddsxmlobj&gt;

&lt;/layoutobject&gt;

&lt;shape groupshapeid="0" groupnode="0" /&gt;

&lt;/ddscontrol&gt;

&lt;ddscontrol controlprogid="MSDDS.Polyline" left="1400" top="1849" logicalid="6" controlid="3" masterid="0" hint1="0" hint2="0" width="826" height="2020" noresize="0" nomove="0" nodefaultattachpoints="1" autodrag="0" usedefaultiddshape="0" selectable="1" showselectionhandles="0" allownudging="1" isannotation="0" dontautolayout="0" groupcollapsed="0" tabstop="1" visible="1" snaptogrid="0"&gt;

&lt;control&gt;

&lt;ddsxmlobj&gt;

&lt;polyline endtypedst="3" endtypesrc="1" usercolor="32768" linestyle="0" linerender="1" customendtypedstid="0" customendtypesrcid="0" adornsvisible="1" /&gt;

&lt;/ddsxmlobj&gt;

&lt;/control&gt;

&lt;layoutobject&gt;

&lt;ddsxmlobj&gt;

&lt;property name="LogicalObject" value="{CB60B1AF-9423-4C5F-B970-BA09DA63F1ED}/paths/242" vartype="8" /&gt;

&lt;property name="Virtual" value="0" vartype="11" /&gt;

&lt;property name="VisibleAP" value="0" vartype="3" /&gt;

&lt;/ddsxmlobj&gt;

&lt;/layoutobject&gt;

&lt;connector lineroutestyle="Microsoft.DataWarehouse.Layout.GraphLayout" sourceid="1" destid="2" sourceattachpoint="7" destattachpoint="6" segmenteditmode="0" bendpointeditmode="0" bendpointvisibility="2" relatedid="0" virtual="0"&gt;

&lt;point x="1826" y="2248" /&gt;

&lt;point x="1826" y="2808" /&gt;

&lt;point x="1799" y="2808" /&gt;

&lt;point x="1799" y="3369" /&gt;

&lt;/connector&gt;

&lt;/ddscontrol&gt;

&lt;/dds&gt;&lt;/dwd:Layout&gt;&lt;dwd:PersistedViewPortTop&gt;-1500&lt;/dwd:PersistedViewPortTop&gt;&lt;/dwd:DtsDataFlowDiagram&gt;&lt;dwd:DtsComponentDesignerPropertiesList&gt;&lt;dwd:DtsComponentDesignTimeProperty&gt;&lt;dwd:key xsi:type="xsd:string"&gt;1 DataSourceViewID&lt;/dwd:key&gt;&lt;/dwd:DtsComponentDesignTimeProperty&gt;&lt;dwd:DtsComponentDesignTimeProperty&gt;&lt;dwd:key xsi:type="xsd:string"&gt;1 TableInfoObjectType&lt;/dwd:key&gt;&lt;dwd:value xsi:type="xsd:string"&gt;Table&lt;/dwd:value&gt;&lt;/dwd:DtsComponentDesignTimeProperty&gt;&lt;/dwd:DtsComponentDesignerPropertiesList&gt;&lt;/TaskHost&gt;</DTS:Property><DTS:Property DTS:Name="Namespace">dts-designer-1.0</DTS:Property><DTS:Property DTS:Name="ObjectName">{CB60B1AF-9423-4C5F-B970-BA09DA63F1ED}</DTS:Property><DTS:Property DTS:Name="DTSID">{34FC20EC-A4C2-4FED-8961-52AD0EBF2AF4}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:PackageVariable>

<DTS:PackageVariable><DTS:Property DTS:Name="PackageVariableValue" DTS:DataType="8">&lt;PipelinePath xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:dwd="http://schemas.microsoft.com/DataWarehouse/Designer/1.0"&gt;&lt;dwd:DestinationName&gt;Flat File Destination Input&lt;/dwd:DestinationName&gt;&lt;dwd:SourceName&gt;OLE DB Source Output&lt;/dwd:SourceName&gt;&lt;/PipelinePath&gt;</DTS:Property><DTS:Property DTS:Name="Namespace">dts-designer-1.0</DTS:Property><DTS:Property DTS:Name="ObjectName">{CB60B1AF-9423-4C5F-B970-BA09DA63F1ED}-242</DTS:Property><DTS:Property DTS:Name="DTSID">{3C016A24-AED7-4B6F-88B9-BF48E8B6B5D6}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:PackageVariable><DTS:Property DTS:Name="ForceExecValue">0</DTS:Property><DTS:Property DTS:Name="ExecValue" DTS:DataType="3">0</DTS:Property><DTS:Property DTS:Name="ForceExecutionResult">-1</DTS:Property><DTS:Property DTS:Name="Disabled">0</DTS:Property><DTS:Property DTS:Name="FailPackageOnFailure">0</DTS:Property><DTS:Property DTS:Name="FailParentOnFailure">0</DTS:Property><DTS:Property DTS:Name="MaxErrorCount">1</DTS:Property><DTS:Property DTS:Name="ISOLevel">1048576</DTS:Property><DTS:Property DTS:Name="LocaleID">1033</DTS:Property><DTS:Property DTS:Name="TransactionOption">1</DTS:Property><DTS:Property DTS:Name="DelayValidation">0</DTS:Property>

<DTS:LoggingOptions><DTS:Property DTS:Name="LoggingMode">0</DTS:Property><DTS:Property DTS:Name="FilterKind">1</DTS:Property><DTS:Property DTS:Name="EventFilter" DTS:DataType="8"></DTS:Property></DTS:LoggingOptions>

<DTS:Executable DTS:ExecutableType="DTS.Pipeline.1"><DTS:Property DTS:Name="ExecutionLocation">0</DTS:Property><DTS:Property DTS:Name="ExecutionAddress"></DTS:Property><DTS:Property DTS:Name="TaskContact">Performs high-performance data extraction, transformation and loading;Microsoft Corporation; Microsoft SQL Server v9; (C) 2004 Microsoft Corporation; All Rights Reserved;http://www.microsoft.com/sql/support/default.asp;1</DTS:Property><DTS:Property DTS:Name="ForceExecValue">0</DTS:Property><DTS:Property DTS:Name="ExecValue" DTS:DataType="3">0</DTS:Property><DTS:Property DTS:Name="ForceExecutionResult">-1</DTS:Property><DTS:Property DTS:Name="Disabled">0</DTS:Property><DTS:Property DTS:Name="FailPackageOnFailure">0</DTS:Property><DTS:Property DTS:Name="FailParentOnFailure">0</DTS:Property><DTS:Property DTS:Name="MaxErrorCount">1</DTS:Property><DTS:Property DTS:Name="ISOLevel">1048576</DTS:Property><DTS:Property DTS:Name="LocaleID">-1</DTS:Property><DTS:Property DTS:Name="TransactionOption">1</DTS:Property><DTS:Property DTS:Name="DelayValidation">0</DTS:Property>

<DTS:LoggingOptions><DTS:Property DTS:Name="LoggingMode">0</DTS:Property><DTS:Property DTS:Name="FilterKind">1</DTS:Property><DTS:Property DTS:Name="EventFilter" DTS:DataType="8"></DTS:Property></DTS:LoggingOptions><DTS:Property DTS:Name="ObjectName">Data Flow Task</DTS:Property><DTS:Property DTS:Name="DTSID">{CB60B1AF-9423-4C5F-B970-BA09DA63F1ED}</DTS:Property><DTS:Property DTS:Name="Description">Data Flow Task</DTS:Property><DTS:Property DTS:Name="CreationName">DTS.Pipeline.1</DTS:Property><DTS:Property DTS:Name="DisableEventHandlers">0</DTS:Property><DTS:ObjectData><pipeline id="0" name="pipelineXml" description="pipelineXml" defaultBufferMaxRows="10000" engineThreads="5" defaultBufferSize="10485760" BLOBTempStoragePath="" bufferTempStoragePath="" runInOptimizedMode="true">

<components>

<component id="1" name="OLE DB Source" componentClassID="{2C0A8BE5-1EDC-4353-A0EF-B778599C65A0}" description="OLE DB Source" localeId="-1" usesDispositions="true" validateExternalMetadata="True" version="7" pipelineVersion="0" contactInfo="OLE DB Source;Microsoft Corporation;Microsoft SqlServer v9; (C) 2005 Microsoft Corporation; All Rights Reserved; http://www.microsoft.com/sql/support;7">

<properties>

<property id="2" name="CommandTimeout" dataType="System.Int32" state="default" isArray="false" description="The number of seconds before a command times out. A value of 0 indicates an infinite time-out." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">0</property>

<property id="3" name="OpenRowset" dataType="System.String" state="default" isArray="false" description="Specifies the name of the database object used to open a rowset." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">[dbo].[test1]</property>

<property id="4" name="OpenRowsetVariable" dataType="System.String" state="default" isArray="false" description="Specifies the variable that contains the name of the database object used to open a rowset." typeConverter="" UITypeEditor="" containsID="false" expressionType="None"></property>

<property id="5" name="SqlCommand" dataType="System.String" state="default" isArray="false" description="The SQL command to be executed." typeConverter="" UITypeEditor="Microsoft.DataTransformationServices.Controls.ModalMultilineStringEditor, Microsoft.DataTransformationServices.Controls, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" containsID="false" expressionType="None"></property>

<property id="6" name="SqlCommandVariable" dataType="System.String" state="default" isArray="false" description="The variable that contains the SQL command to be executed." typeConverter="" UITypeEditor="" containsID="false" expressionType="None"></property>

<property id="7" name="DefaultCodePage" dataType="System.Int32" state="default" isArray="false" description="Specifies the column code page to use when code page information is unavailable from the data source." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">1252</property>

<property id="8" name="AlwaysUseDefaultCodePage" dataType="System.Boolean" state="default" isArray="false" description="Forces the use of the DefaultCodePage property value when describing character data." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">false</property>

<property id="9" name="AccessMode" dataType="System.Int32" state="default" isArray="false" description="Specifies the mode used to access the database." typeConverter="AccessMode" UITypeEditor="" containsID="false" expressionType="None">0</property>

<property id="15" name="ParameterMapping" dataType="System.String" state="default" isArray="false" description="The mappings between the parameters in the SQL command and variables." typeConverter="" UITypeEditor="" containsID="false" expressionType="None"></property></properties>

<connections>

<connection id="10" name="OleDbConnection" description="The OLE DB runtime connection used to access the database." connectionManagerID="{D3313824-77A8-4270-A6EA-65C620E688C0}"/></connections>

<outputs>

<output id="11" name="OLE DB Source Output" description="" exclusionGroup="0" synchronousInputId="0" deleteOutputOnPathDetached="false" hasSideEffects="false" dangling="false" isErrorOut="false" isSorted="false" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed"><outputColumns>

<outputColumn id="240" name="DOB" description="" lineageId="240" precision="0" scale="0" length="10" dataType="str" codePage="1252" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="Conversion" errorRowDisposition="FailComponent" truncationRowDisposition="FailComponent" externalMetadataColumnId="239"/></outputColumns><externalMetadataColumns isUsed="True">

<externalMetadataColumn id="239" name="DOB" description="" precision="0" scale="0" length="10" dataType="str" codePage="1252"/></externalMetadataColumns></output>

<output id="12" name="OLE DB Source Error Output" description="" exclusionGroup="0" synchronousInputId="0" deleteOutputOnPathDetached="false" hasSideEffects="false" dangling="false" isErrorOut="true" isSorted="false" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed"><outputColumns>

<outputColumn id="241" name="DOB" description="" lineageId="241" precision="0" scale="0" length="10" dataType="str" codePage="1252" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0"/>

<outputColumn id="13" name="ErrorCode" description="" lineageId="13" precision="0" scale="0" length="0" dataType="i4" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="1" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0"/>

<outputColumn id="14" name="ErrorColumn" description="" lineageId="14" precision="0" scale="0" length="0" dataType="i4" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="2" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0"/></outputColumns><externalMetadataColumns isUsed="False"/></output>

</outputs>

</component>

<component id="87" name="Flat File Destination" componentClassID="{A1DF9F6D-8EE4-4EF0-BB2E-D526130D7A7B}" description="Flat File Destination" localeId="1033" usesDispositions="false" validateExternalMetadata="True" version="0" pipelineVersion="0" contactInfo="Flat File Destination;Microsoft Corporation;Microsoft SqlServer v9; (C) 2005 Microsoft Corporation; All Rights Reserved; http://www.microsoft.com/sql/support;0">

<properties>

<property id="90" name="Overwrite" dataType="System.Boolean" state="default" isArray="false" description="Specifies whether the data will overwrite or append to the destination file." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">true</property>

<property id="91" name="Header" dataType="System.Null" state="default" isArray="false" description="Specifies the text to write to the destination file before any data is written." typeConverter="" UITypeEditor="" containsID="false" expressionType="Notify"/></properties>

<connections>

<connection id="89" name="FlatFileConnection" description="" connectionManagerID="{259C2EAB-15E8-40EB-8B91-EA273C23249E}"/></connections>

<inputs>

<input id="88" name="Flat File Destination Input" description="" hasSideEffects="true" dangling="false" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed"><inputColumns>

<inputColumn id="264" name="" description="" lineageId="240" usageType="readOnly" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="263"/>

</inputColumns><externalMetadataColumns isUsed="True">

<externalMetadataColumn id="263" name="DOB" description="" precision="0" scale="0" length="10" dataType="str" codePage="1252"/></externalMetadataColumns></input>

</inputs>

</component>

</components>

<paths>

<path id="242" name="OLE DB Source Output" description="" startId="11" endId="88"/>

</paths></pipeline></DTS:ObjectData></DTS:Executable><DTS:Property DTS:Name="ObjectName">TableDateToAFile</DTS:Property><DTS:Property DTS:Name="DTSID">{0FA525CC-1C4A-465D-8460-DB43EE25F44A}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName">MSDTS.Package.1</DTS:Property><DTS:Property DTS:Name="DisableEventHandlers">0</DTS:Property></DTS:Executable>

|||If this is a meta data issue how do you clear out bad meta data. Is there a place where you can look at the meta data directly.|||

Wolfsvein wrote:

If this is a meta data issue how do you clear out bad meta data. Is there a place where you can look at the meta data directly.

You'll need to look at the advanced properties of all of your components and look at the input/output columns to ensure they match.|||We have a winner.