?
Solved

Importing data from a flat file (plain text) using SQL BIDS - How to handle date field?

Posted on 2009-05-07
3
Medium Priority
?
822 Views
Last Modified: 2013-11-30
Hi all!

I'm attempting to use BIDS to import a flat file which is just plain text, fixed width.  In it, there is a column with an 8 digit numeric value that we want SQL to view as the date (format is YYYYMMDD).  I'm attempting to figure out how to setup BIDS to read that as a date and I'm having all sorts of issues.  It gives me the error that is listed in "Code".  Through trial and error, I set the DataType under Advanced in the Flat File Connection Manager Editor to try DT_DBDATE, DT_DBTIMESTAMP, and DT_DATE all to no avail.  

I also tried changing the data in the flat file to YYYY-MM-DD format, but even though I expand the column from 8 to 10, it cuts off the date and doesn't show the days (i.e. 2008-12), and also tried my hand at using a data conversion technique, all without success.

Am I approaching this the wrong way?  Should I leave the Flat File config alone, and work on translating that value INTO a date?  I'm fairly new to BIDS/SSRS, and I'd appreciate any assistance you could offer.  Thank you in advance.
SSIS package "Wang Extract.dtsx" starting.
Information: 0x4004300A at Wang Extract, DTS.Pipeline: Validation phase is beginning.
Information: 0x4004300A at Wang Extract, DTS.Pipeline: Validation phase is beginning.
Information: 0x40043006 at Wang Extract, DTS.Pipeline: Prepare for Execute phase is beginning.
Information: 0x40043007 at Wang Extract, DTS.Pipeline: Pre-Execute phase is beginning.
Information: 0x402090DC at Wang Extract, Flat File Source [28148]: The processing of file "\\[file location]\[filename]" has started.
Information: 0x4004300C at Wang Extract, DTS.Pipeline: Execute phase is beginning.
Error: 0xC02020A1 at Wang Extract, Flat File Source [28148]: Data conversion failed. The data conversion for column "WangModifyDate" returned status value 2 and status text "The value could not be converted because of a potential loss of data.".
Error: 0xC0209029 at Wang Extract, Flat File Source [28148]: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "output column "WangModifyDate" (28789)" failed because error code 0xC0209084 occurred, and the error row disposition on "output column "WangModifyDate" (28789)" specifies failure on error. An error occurred on the specified object of the specified component.  There may be error messages posted before this with more information about the failure.
Error: 0xC0202092 at Wang Extract, Flat File Source [28148]: An error occurred while processing file "\\[file location]\[filename]" on data row 1.
Error: 0xC0047038 at Wang Extract: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.  The PrimeOutput method on component "Flat File Source" (28148) 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.
Error: 0xC0047021 at Wang Extract: 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.
Error: 0xC0047039 at Wang Extract: 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.
Error: 0xC0047021 at Wang Extract: 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.
Information: 0x40043008 at Wang Extract, DTS.Pipeline: Post Execute phase is beginning.
Information: 0x402090DD at Wang Extract, Flat File Source [28148]: The processing of file "\\[file location]\[filename]" has ended.
Information: 0x402090DF at Wang Extract, OLE DB Destination [30136]: The final commit for the data insertion has started.
Information: 0x402090E0 at Wang Extract, OLE DB Destination [30136]: The final commit for the data insertion has ended.
Information: 0x40043009 at Wang Extract, DTS.Pipeline: Cleanup phase is beginning.
Information: 0x4004300B at Wang Extract, DTS.Pipeline: "component "OLE DB Destination" (30136)" wrote 0 rows.
Task failed: Wang Extract
Warning: 0x80019002 at Wang Extract: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED.  The Execution method succeeded, but the number of errors raised (7) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "Wang Extract.dtsx" finished: Failure.

Open in new window

0
Comment
Question by:tscd
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 43

Expert Comment

by:Eugene Z
ID: 24330671
please check if you installed fresh service pack for sql server 2005..

How to identify your SQL Server version and edition
http://support.microsoft.com/kb/321185
0
 

Author Comment

by:tscd
ID: 24335452
9.00.4035.00      SP3      Standard Edition
0
 
LVL 43

Accepted Solution

by:
Eugene Z earned 1500 total points
ID: 24336435
try to have in your SSIS pack - middle step where you import data from text files as it is- and then insert converted in format that you need  data from the 'temp' table into your maint table
0

Featured Post

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question