March 11, 2008 at 8:51 am
Help!!!
Microsoft SQL Server Management Studio - 2005 - Version - 9.00.3042.00
This field used to import with no problem in previous sql versions but will not import to any data type no matter what the length.
I have a feeling it has something to do with the number of slashes.
Here's the field :
"IMAGING\FW2000\we013000\IMAGE\0\0\0\0\0\5\00000503.TIF"
Here's the error :
Error 0xc02020a1: Data Flow Task: Data conversion failed. The data conversion for column "Column 7" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
(SQL Server Import and Export Wizard)
Error 0xc020902a: Data Flow Task: The "output column "Column 7" (38)" failed because truncation occurred, and the truncation row disposition on "output column "Column 7" (38)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
(SQL Server Import and Export Wizard)
Error 0xc0202092: Data Flow Task: An error occurred while processing file "E:\IMAGING\FW2000\Document1.txt" on data row 1.
(SQL Server Import and Export Wizard)
Error 0xc0047038: Data Flow Task: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Source - Document1_txt" (1) returned error code 0xC0202092. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.
(SQL Server Import and Export Wizard)
Error 0xc0047021: Data Flow Task: SSIS Error Code DTS_E_THREADFAILED. Thread "SourceThread0" has exited with error code 0xC0047038. There may be error messages posted before this with more information on why the thread has exited.
(SQL Server Import and Export Wizard)
Error 0xc0047039: Data Flow Task: SSIS Error Code DTS_E_THREADCANCELLED. Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown. There may be error messages posted before this with more information on why the thread was cancelled.
(SQL Server Import and Export Wizard)
Error 0xc0047021: Data Flow Task: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread0" has exited with error code 0xC0047039. There may be error messages posted before this with more information on why the thread has exited.
(SQL Server Import and Export Wizard)
March 11, 2008 at 9:18 am
I believe you just need to make the column in the target table wider to hold that information.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 11, 2008 at 10:41 am
Thanks Jeff but I've done everything imaginable. It doesn't matter how wide you make it.
Wayne
March 11, 2008 at 12:16 pm
Jeff's right. It's likely a column length issue, but it might not be the destination. It could also be further upstream. If you double click the connector just before the data conversion task and check the metadata going in, then double click the connector just after you will likely find that the downstream width is shorter.
Failing this change the disposition on truncate so that the failing rows are output to a file. This should give you a clue as to whats causing the failure.
Kindest Regards,
Frank Bazan
March 11, 2008 at 4:31 pm
Thanks for stepping in, Frank... I'm just not a DTS Ninja...
--Jeff Moden
Change is inevitable... Change for the better is not.
March 11, 2008 at 10:33 pm
OK before I do this can you tell me why it works in all other previous versions of sql including NT 4.0?
March 12, 2008 at 4:18 am
Look, obviously you're frustrated by this, but really we're trying to help. By the sounds of it some changes have been made to your environment. Is it not possible that either the source/destination data, or the package you're running have changed?
There is no reason why the string you mentioned would fail to load if the column lengths are sufficient and of the correct datatypes. The error messages you provided are the messages that are generated when the strings received by a component are longer than it expects. I'd check again the column width for "Column 7" (I'd also check that column 7 is what you think it is) in your SSIS package and see if it's shorter than the length of the string in the source system. If so then you have your problem.
If that is not the case, then you'll have to start digging a bit deeper - but it's the most likely cause for this error.
Kindest Regards,
Frank Bazan
March 12, 2008 at 6:54 am
Try the sting value I have posted and try and load it into sql 2005 into a default generic table column and see what you get. You may have hit something by saying the environment is different.
It's not my environment but a clients.
I have modified the column to every data type available and maxed them out. I have checked the source to see if there is anything there like an unwanted invisble tab, LF or CR, etc. Other source fields which are longer in length are importing ok. It won't import using a default table with default columns.
I'm not frustrated anymore due to importing the source into sql 2003 and exporting the table and importing it into sql 2005 so I can move on.
March 12, 2008 at 7:25 am
That error seems to imply that SSIS thinks you're importing UNICODE data. Have you tried using nvarchar fields instead?
----------------------------------------------------------------------------------
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?
March 12, 2008 at 8:53 am
I have modified the column to every data type available and maxed them out. I have checked the source to see if there is anything there like an unwanted invisble tab, LF or CR, etc. Other source fields which are longer in length are importing ok. This field also won't import using a default table with default columns.
If you have sql 2005 available try importing the field and see what you get.
March 12, 2008 at 9:41 am
You need to look at the advanced properties of the "flat file source". I'm thinking it's trying to "default" to 50 characters on that column, and your path is 55. Try changing IT to output a wider amount (say - 100), and see if it gives you a different behavior.
----------------------------------------------------------------------------------
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?
March 12, 2008 at 11:55 am
Matt......Thank you very much! That was it. I upped the column # in the advanced properties of the flat file column and it worked!
If anyone in the thread was suggesting this I apologize for my not picking it up and my ignorance of sql 2005.
Why they would do this is somebody else's dream and an obstacle to simplicity.
Like I said before all previous versions imported it ok.
Thanks to all!
Wayne
March 12, 2008 at 6:52 pm
glad that worked out for you. Thanks for the feedback!
----------------------------------------------------------------------------------
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?
Viewing 13 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy