Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

trying to insert several csv files into a db table using a ssis package Expand / Collapse
Author
Message
Posted Friday, November 16, 2012 6:01 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 06, 2013 4:06 AM
Points: 27, Visits: 82
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
Posted Friday, November 16, 2012 6:04 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, August 28, 2013 2:37 AM
Points: 5,075, Visits: 4,834
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
Posted Monday, November 19, 2012 5:22 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 06, 2013 4:06 AM
Points: 27, Visits: 82
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
Posted Monday, November 19, 2012 5:26 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, August 28, 2013 2:37 AM
Points: 5,075, Visits: 4,834
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




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 #1386316
Posted Monday, November 19, 2012 5:51 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 06, 2013 4:06 AM
Points: 27, Visits: 82
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
Posted Monday, November 19, 2012 5:59 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, August 28, 2013 2:37 AM
Points: 5,075, Visits: 4,834
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.




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 #1386333
Posted Monday, November 19, 2012 6:23 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 06, 2013 4:06 AM
Points: 27, Visits: 82
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
Posted Monday, November 19, 2012 6:30 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, August 28, 2013 2:37 AM
Points: 5,075, Visits: 4,834
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.



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 #1386354
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse