SSIS SQL Server 2008 errors

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 col

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.

With only that change, the package will execute successfully

Also, an excellent answer from SO on the same issue

StackExchange Database Administrators Q#14044, answer score: 6