Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I just want to know how to import data from source to SQL Server 2008 database using SSIS.

I have a simple txt file (actually it's from this blog : http://blog.sqlauthority.com/2008/02/06/sql-server-import-csv-file-into-sql-server-using-bulk-insert-load-comma-delimited-file-into-sql-server/)

1,James,Smith,19750101
2,Meggie,Smith,19790122
3,Robert,Smith,20071101
4,Alex,Smith,20040202

I created a new table in SQL Server :

CREATE TABLE Test
(ID INT,
FirstName VARCHAR(50),
LastName VARCHAR(50),
BirthDate SMALLDATETIME)

I draged Data Flow Task, then Flat File Source and OLE DB Destination.

I created new Flat file connection, choose the text file, and in Advanced tab, there are 4 column, so I changed their data type as follow:

  • Column 0 : two-byte signed integer [DT_I2]
  • Column 1: String[DT-STR] - Outputcolumnwith :20
  • Column 2: String[DT-STR] - Outputcolumnwith :20
  • Column 3: database timestamp with precision [DT_DBTIMESTAMP2]

Then, set up new OLB DE Connection, edited it, set [dbo].[Test] under "Name of the table or the view" + mapped each column from input to destination.

But when I run it, these errors occurred :

Warning: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (4)
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.

Error: 0xC02020A1 at Data Flow Task, Flat File Source [52]: Data conversion failed. The data conversion for column "Column 3" returned status value 2 and status text "The value could not be converted because of a potential loss of data.".

Error: 0xC0209029 at Data Flow Task, Flat File Source [52]: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "output column "Column 3" (90)" failed because error code 0xC0209084 occurred, and the error row disposition on "output column "Column 3" (90)" 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 Data Flow Task, Flat File Source [52]: An error occurred while processing file "D:\New folder\text.txt" on data row 1.

Error: 0xC0047038 at Data Flow Task, SSIS.Pipeline: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Flat File Source" (52) 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.

What could be the cause for not import successfully in my case?

I'm using evaluation version on Windows 7.

Thanks so much!

FINALLY I COULD IMPORT FROM TXT FILE TO SQL 2008 DATABASE,

LET'S NAME THE 4 COLUMNS AND THEIR DATA TYPE AS :

id : two-byte signed integer [DT_I2]
firstname : String[DT-STR] - Outputcolumnwith :20
lastname: String[DT-STR] - Outputcolumnwith :20
yeardate: String[DT-STR] - Outputcolumnwith :20

THEN IN DERIVED COLUMN EDITOR :

Derived column name : yeardate
Derived column : replace 'yeardate'
Expression : (DT_DBTIMESTAMP)(SUBSTRING(yeardate,1,4) + "-" + SUBSTRING(yeardate,5,2) +     "-" + SUBSTRING(yeardate,7,2))
share|improve this question

migrated from stackoverflow.com Feb 28 '12 at 13:09

This question came from our site for professional and enthusiast programmers.

1  
I think this should go to dba.se. –  yogirk Feb 28 '12 at 8:59
    
Sorry, what is dba.se? –  Thanh Nguyen Feb 28 '12 at 9:01
    
when you run the package, what do you see under "Execution Results" tab? It should have more specific details about the error. –  yogirk Feb 28 '12 at 9:02
    
I edited my post with errors detaid, thanks –  Thanh Nguyen Feb 28 '12 at 9:13
1  
Why was this migrated? SSIS questions are valid on Stackoverflow. This is not a dba question. –  HLGEM Feb 28 '12 at 15:39
add comment

2 Answers

You've come across a fairly well-known problem. When SSIS is trying to import the data, it provides locale-aware conversions and the format YYYYMMDD doesn't convert nicely. You can use the "import as string and convert to actual datetime" approach you've outlined in your solution but that's slower and consumes more resources than using the native approach of telling SSIS to quit being so damn smart.

Right click on your flat file source and select the Show Advanced Editor. In the Input and Output Properties, expand Output Columns, find your column (BirthDate) and change the FastParse property from False to True.

Fast parse settings

With only that change, the package will execute successfully

Success

Also, an excellent answer from SO on the same issue

share|improve this answer
add comment

19750101 cannot be converted to smalldatetime.

pretty sure its a formatting issue.

try adding a derived column to format the value from the text file to a format that can be inserted into SQL Server

share|improve this answer
    
Why 19750101 cant not be converted to smalldatetime type? As i see from this page msdn.microsoft.com/en-us/library/aa258277(v=sql.80).aspx smalldatetime is from January 1, 1900, through June 6, 2079? Thanks. –  Thanh Nguyen Feb 28 '12 at 9:33
    
I agree with Diego. The SSIS package itself cannot convert 19750101 as DT_DBTIMESTAMP. If your input would be 1975-01-01 it could work. The format 19750101 can be read only as a string. In the package you should use the Derived Coulmn transformation to extract the part of the string –  luviktor Feb 28 '12 at 9:36
    
it can, but the data is in a format that SQL Server does not understand. You just have to format it –  Diego Feb 28 '12 at 9:38
    
@ luviktor, Diego Thanks, i understanded it. –  Thanh Nguyen Feb 28 '12 at 9:39
    
let us know if you manage to solve it –  Diego Feb 28 '12 at 9:50
show 2 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.