April 7, 2008 at 6:53 am
We have a package with a single Data Flow Task. This has an OLEDB source connector with a sql command to extract the columns from the source system and write them to an OLE DB destination.
Despite increasing the column size of the destination, the package insists it is still 100 (the pre change size). We have rebuilt the package, refreshed the links, rebuilt parts of the package and even changed the SQL to truncate the incoming column to 100 characters, but it fails because it finds a record with 278 characters in the field.
Even when we edit the OLE DB source and click preview, we get this error. Can anyone explain why, and how to fix it please?
April 7, 2008 at 7:26 am
What error do you get when you click preview on the destination component?
SSIS maintains schema information in XML on the input and output of every component. It is very sensitive about data type changes. Sometimes, you have to go into the advanced editor of a component, go to the inputs and outputs, and edit the column information manually.
For your situation, this is probably not the case - it sounds like you have a simple OLEDB destination component and if it is set to validate the external meta-data it should give you a warning when you change the schema and self-adjust when you open the editor. Is it possible you turned the ValidateExternalMetadata property to false or set something to defer validation?
April 7, 2008 at 7:50 am
Thanks for this. You're so right about it being sensitive!
I was not delaying validation or anything like that, and I had already tried changing the settings in the advanced editor, in the source and the destination. However, each time I had a warning message on error output, saying the field lengths did not match and asking me if I wanted to SSIS to sort it out. I had changed the lengths of all occurrences of the field, so I didn't understand it, but it wouldn't work if I didn't allow SSIS to change it. SSIS simply set it back to its original size.
I have now rebuilt the package from scratch, copying and pasting the original settings, and it is running. That's not really an option for us once we go live on our development though, so I'd still like to investigate further.
I think you were definitely on the right lines with your ideas, so if you have any other suggestions please let me know. I will report back if I have any success
April 7, 2008 at 8:00 am
Keep in mind that SSIS tracks the column size in several places. Changes the destination's size is nice, but you also need to do it in the "input" properties as well. If you don't change the anticipated input column's width, you will also get truncation-type errors.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 7, 2008 at 9:30 am
My OLE DB Source Input and Output properties tab, in the Advanced Editor has the offending column in OLE DB Source Output External columns and Output Columns, and I can change the size in both these places. It also shows the column on the OLE F+DB Source Error Output, Output Columns, but when I change it here I get an error:
'Property value is not valid' - Error at Data Flow Task [OLE DB Source [1]]: The data type for "output "OLE DB Source Error Output" (12)" cannot be modified in the error "output column "short_description" (6111)".
Error at Data Flow Task [OLE DB Source [1]]: Failed to set property "DataType" on "output column "short_description" (6111)".'
It won't accept any other value than the one already in it, and it doesn't matter if I have a destination for error output or not. When I exit the editor and hover the mouse over the OLEDB Source box I can see there is still a problem 'The output column "short_description" (6111) on the error output has properties that do not match the properties of its corresponding data source column.'
The OLEDB Destination Input and Output properties have an entry for the column in External columns, which I was able to change successfully and an entry in Input columns, which shows the new value.
The Metadata on the Data Flow Path Editor also shows the correct new length for this column.
So I have now changed or checked everything I can, and except for the Error output from the oledb source, which I am not interested in, everything is showing the new column size. I still have my error on the OLEDB Source so I select the advanced editor once again and get the message:
'The component is not in a valid state. The validation errors are:
Error at Data Flow Task [OLE DB Source [1]]: The output column "short_description" (6111) on the error output has properties that do not match the properties of its corresponding data source column.
Do you want the component to fix these erros automatically?'
If I say 'Yes'. The external column in the source is set back to 100.
I can't see anything else to change.
June 24, 2008 at 7:22 am
I had the same issue, after long research...i found that the modified column widths not updated with in the SSIS schema, then i have changed the column width properties in the advanced editor of the oledbsource component,then it started working fine! I hope it will work for u
Thanks
Siva
June 26, 2008 at 5:03 am
I was also unable to get SSIS to refresh the definition of the source and/or destination tables after those table specifications were changed. Worse, I could find no way to get the mismatches listed, other than through the (much too small) tip box that came up when mousing over the error icon in the bad transform.
I finally just gave up and deleted and redefined the input and output transforms, which (when I had gotten the tables to be defined consistently) picked up the revised definitions and fixed things.
July 5, 2008 at 12:41 am
Hi Guys,
I found a way to get around this problem. I was also facing the same issue after changing the column size of a table.If you try to change the column size from Advanced editor you run into whole lot of error messages.
Simply change the column size from XML instead of GUI.Right click the package in Solution explorer and go to VIEW CODE. you will be presented with some weird XML code.Just browse this code and find your area of ineterest.
August 6, 2008 at 5:24 pm
Hi Guys,
If you need to change the column size, just go to the Advance Editor (Input/Output Properties tab) and change only the size defined in Output Columns, not the External columns. Else you'll get the above mentioned errors.
External columns seems to be dynamic to me and and change size per package execution. You will only get truncation errors if the size of data imported is more then what is defined in Output columns.
I won't suggest changing the source xml directly unless you know what your doing.
Hope this helps
Thanks
July 14, 2010 at 12:22 pm
SSIS is a maintenance nightmare! Avoid SSIS like the plague. It's for people who shouldn't be coding at all! About the only exception I can think of is if you already have code and need to wrap it in SSIS so you can control bandwidth without having to process data in chunks!
July 14, 2010 at 1:26 pm
Vernon Jimmerson-306463 (7/14/2010)
SSIS is a maintenance nightmare! Avoid SSIS like the plague. It's for people who shouldn't be coding at all! About the only exception I can think of is if you already have code and need to wrap it in SSIS so you can control bandwidth without having to process data in chunks!
Did you have a bad day at the office?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 14, 2010 at 3:40 pm
Vernon Jimmerson-306463 (7/14/2010)
SSIS is a maintenance nightmare! Avoid SSIS like the plague. It's for people who shouldn't be coding at all! About the only exception I can think of is if you already have code and need to wrap it in SSIS so you can control bandwidth without having to process data in chunks!
I would tend to disagree, mileage may vary..
CEWII
July 16, 2010 at 3:15 am
Vernon Jimmerson-306463 (7/14/2010)
SSIS is a maintenance nightmare! Avoid SSIS like the plague. It's for people who shouldn't be coding at all! About the only exception I can think of is if you already have code and need to wrap it in SSIS so you can control bandwidth without having to process data in chunks!
I hear only people saying such things when they do not know how SSIS works, what it does and how it is supposed to work.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 20, 2010 at 12:27 am
(using a friendly positive tone with a little smart *** worked in!)
Or if they've had to deal with it for 15 years! There a thing called T-SQL code and BCP. You can script it, you can search for things in it. It's just SQL statements for the most part. If you want some special processing you can always write a CLR or two. You can script the CLRs also and look though the code easily. No graphical interface that you have to click on to figure you what the heck is going on! Code doesn't take an act of god to figure you how the data is being transformed or why the last guy wrote it with a loop control and a control for every data element being transformed or why he doesn't understand what row duplication is or unique record constraints.
If you can't script it into some readable code that can be searched like text than I don't like it.
That being said SSIS might be useful occasionally (wizard) but for the most part T-SQL, CLRs and BCP do fine and can be written in an object oriented way.
July 20, 2010 at 12:36 am
You might guess I've been working on an SSIS package recently. I might have mentioned I am not a fan of them.
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply