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