SSIS SQL Server 2008 errors

Question

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))

Answer 1

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

Answer 2

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