trying to insert several csv files into a db table using a ssis package

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Data Warehousing » Data Transformation Services (DTS) » trying to insert several csv files into a db...

trying to insert several csv files into a db table using a ssis package

Rate Topic

Display Mode

Topic Options

Author

Message

mrichardson 57577

Posted Friday, November 16, 2012 6:01 AM

Grasshopper

Group: General Forum Members
Last Login: Monday, February 04, 2013 10:13 AM
Points: 19, Visits: 61

I have several .csv files and i'm trying to insert them into a database table.

all my files are saved from an excel spreadsheet - saved as .csv.
I'm trying to import these into a table varchar(50).

All my data in the single field being inserted into the table are only 7 characters long (uk postcodes) but changed to varchar 50 as it was giving me error about the field not being long enough.

Here are 14 screenshots of my table, my data and all my settings:
http://www.sypensions.org.uk/test/test.html

thanks,
mark.

Post #1385639

anthony.green

Posted Friday, November 16, 2012 6:04 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

can you copy out all of the output window and paste it in a txt file and attach it, the screen shot strips out the important bits at the end of the lines.

Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1385641

mrichardson 57577

Posted Monday, November 19, 2012 5:22 AM

Grasshopper

Group: General Forum Members
Last Login: Monday, February 04, 2013 10:13 AM
Points: 19, Visits: 61

here is my full error message:
[url=http://www.sypensions.org.uk/test/myimport.txt][/url]

http://www.sypensions.org.uk/test/myimport.txt

thanks.

Post #1386314

anthony.green

Posted Monday, November 19, 2012 5:26 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

Thats a warning, it wont cause the package to fail.

The screenshots detail Errors, one of which was inserting NULL into a column which didnt accept NULLs as well as other errors

Post #1386316

mrichardson 57577

Posted Monday, November 19, 2012 5:51 AM

Grasshopper

Group: General Forum Members
Last Login: Monday, February 04, 2013 10:13 AM
Points: 19, Visits: 61

I have made some recent changes since the first screenshot.
Such as making sure there are no spaces in the excel csv files, that they are all equal length and
making sure the ole db destination datatype is DT_STR etc.

and even though it no longer says error and now says 'warning & information messages'....
it does not write anything to my database table at all.

Post #1386327

anthony.green

Posted Monday, November 19, 2012 5:59 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

There is no data in the file for it to import.

The error states it reached to end of the file before importing anything and to check the field and row terminators and ensure your not skipping any rows that you dont need to skip.

Post #1386333

mrichardson 57577

Posted Monday, November 19, 2012 6:23 AM

Grasshopper

Group: General Forum Members
Last Login: Monday, February 04, 2013 10:13 AM
Points: 19, Visits: 61

thanks for the replies anthony.

I think i tried too many things at once and got a bit lost.
it's now importing ok !

for the txt file which shows the warning message....
I had moved all my csv files except one - which i had tried to amend to uses commas inside the file rather than columns.

I then put the csv files back to normal (with columns). and took note of the NULL message.
I changed the IDNo field to accept NULLs and it then it started working.

However, as for the IDNo field - i don't want to import this and didn't think I was importing it (because under mappings i only have column 0 mapped to 1 field called postcode - so didnt think this field should matter, but maybe it needs to be NULL if data is not being imported into it - due to data being iserted into the adjacent column?)

Post #1386345

anthony.green

Posted Monday, November 19, 2012 6:30 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

I am guessing the problem is that you haven't set IDNo as an identity to autopopulate with an ID number. The error is being thrown by the SQL server saying you cant insert a row as you havnt told me what value to put in the IDNo column as it cant take nulls, as like you say you only have the PostCode column mapped.

Post #1386354

« Prev Topic | Next Topic »

Permissions