SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
mrichardson 57577
mrichardson 57577
SSC Journeyman
SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)

Group: General Forum Members
Points: 83 Visits: 83
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.
anthony.green
anthony.green
SSC-Insane
SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)

Group: General Forum Members
Points: 22974 Visits: 6519
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
When a question, really isn't a question - Jeff Smith
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


mrichardson 57577
mrichardson 57577
SSC Journeyman
SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)

Group: General Forum Members
Points: 83 Visits: 83
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.
anthony.green
anthony.green
SSC-Insane
SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)

Group: General Forum Members
Points: 22974 Visits: 6519
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
When a question, really isn't a question - Jeff Smith
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


mrichardson 57577
mrichardson 57577
SSC Journeyman
SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)

Group: General Forum Members
Points: 83 Visits: 83
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.
anthony.green
anthony.green
SSC-Insane
SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)

Group: General Forum Members
Points: 22974 Visits: 6519
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
When a question, really isn't a question - Jeff Smith
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


mrichardson 57577
mrichardson 57577
SSC Journeyman
SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)

Group: General Forum Members
Points: 83 Visits: 83
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?)
anthony.green
anthony.green
SSC-Insane
SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)

Group: General Forum Members
Points: 22974 Visits: 6519
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
When a question, really isn't a question - Jeff Smith
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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search