Geeks With Blogs
Sathya Narayanan Srinivasan .NET Developer

Friends,

    Sorry that I dint blog for a very long time. Hope I will continue to blog frequently from now on.

 How to Import and Export CSV files directly in SSIS using OleDB Source Task (without converting to excel and reading) :-

  1. Create a new connection Manager
  2. Select New Oledb Connection
  3. Select the Provider as Microsoft Jet 4.0 Oledb Provider
  4. After choosing that on the same window at the left you will be having two tabs to switch over. one would be All (Which mentions the properties of the connection) and other would be the connection info
  5. Choose All. You would see a property called ExtendedProperties.
  6. In that paste this without quotes : "text;HDR=Yes;FMT=Delimited"
  7. There will be another property called Mode, which will mention 16 (readonly) and if you want to make that Read/Write change it to 19.
  8. In the Server or file name text box under the connection tab, give the path of the csv file. Give it only till the folder name and not the csv file itself. (Ex: If your file is c:\sathya\contact.csv give it as c:\sathya)
  9. And you can use this Oledb Connection in Oledb Source Task, Lookup task and Oledb Destination task also if you have changed the mode to 19 i.e. read/write.
  10. While writing the query to fetch from csv, since you have given only up to the folder as the datasource you need to write query like "select * from contact.csv"


I struggled for more than 2 days to find all these things.
So thought of posting this and it would be of some help to anyone who desparately needs this.

Keep watching my blog for further updates and articles on new topics and troubleshooting tips like this.

Posted on Tuesday, June 22, 2010 9:43 AM | Back to top


Comments on this post: How to Import and Export CSV files directly in SSIS using OleDB Source Task

# re: How to Import and Export CSV files directly in SSIS using OleDB Source Task
Requesting Gravatar...
Hey question is it possible to loop over the csv files?
So i am trying to import multiple csv files using this connec manager but how do i dynamically set the connection to pull in all the csv files?
I was thnking of a foreach lop container but what foreach enumerator do i use?
I would really appreciate your help!
Thanks
Left by Arthur on Mar 31, 2011 9:34 PM

# re: How to Import and Export CSV files directly in SSIS using OleDB Source Task
Requesting Gravatar...
Hi ,
I want to loop a bunch of csv files .and use microsoft jet 4.0 oledb provider to get rid of the """ quotes. I repeatedly get the column delimetre not found.
Please send the screen shots fo the process.

Iam having around 6000 csv files in a folder which i want to import to a table.

Reply will be higly apprciated

Thanks '
Sandeep.
Left by sandeep on Apr 14, 2012 3:01 AM

# re: How to Import and Export CSV files directly in SSIS using OleDB Source Task
Requesting Gravatar...
Thank you for posting this; it works great for my situation!
Left by Bill Quinn on May 12, 2012 5:12 PM

# re: How to Import and Export CSV files directly in SSIS using OleDB Source Task
Requesting Gravatar...
Hai, I create oledb source and Flat File Designation. After successful package, the Column Header not insert in csv file.
I need to column header in csv file
Left by Dhivya on Jun 13, 2012 3:42 PM

# re: How to Import and Export CSV files directly in SSIS using OleDB Source Task
Requesting Gravatar...
I have copied yours steps in this SO post:

http://stackoverflow.com/questions/11078656/multi-file-connection-manager-in-ssis

I hope this not will a problem.
Left by dani on Jun 18, 2012 1:13 PM

# re: How to Import and Export CSV files directly in SSIS using OleDB Source Task
Requesting Gravatar...
Hi ,
You need to select the checkbox Column names in first data row and then you will get the Column Header in your CSV file. (if your OLEDB source have columns)
Left by Ank on Dec 05, 2012 3:56 PM

# re: How to Import and Export CSV files directly in SSIS using OleDB Source Task
Requesting Gravatar...
Thank you very much for this! You saved me a ton of time.
Left by Teresa on Jan 07, 2013 4:43 AM

# re: How to Import and Export CSV files directly in SSIS using OleDB Source Task
Requesting Gravatar...
Thanks For posting this. It's working perfectly.
Left by Annamalai on Jan 24, 2013 2:30 PM

# re: How to Import and Export CSV files directly in SSIS using OleDB Source Task
Requesting Gravatar...
Brilliant stuff. Went thru almost 30 different site but couldnt find solution to this issue.You are really good!!
Left by Varun Sahay on Mar 18, 2013 12:57 PM

# re: How to Import and Export CSV files directly in SSIS using OleDB Source Task
Requesting Gravatar...
Wonderful! Many thanks for this great article. That was realy helpful.
Left by Sherry on Oct 31, 2013 7:32 AM

# re: How to Import and Export CSV files directly in SSIS using OleDB Source Task
Requesting Gravatar...
Is it possible to export 2 different data sets generated from 2 sql select statements to 2 different worksheets in same csv file? Can anyone please help me on this.. it is urgent..
Left by Tanu on Jan 15, 2014 12:42 PM

# re: How to Import and Export CSV files directly in SSIS using OleDB Source Task
Requesting Gravatar...
Hi, I want to be able to export a sql server table to a csv file, but need to put quotes around some/all of the fields exported, do you have a sample ssis application you could send me, so screen shots of the process, I am using Visual Studio SSIS 2008.
Left by Sunny on Feb 11, 2014 10:24 PM

# re: How to Import and Export CSV files directly in SSIS using OleDB Source Task
Requesting Gravatar...
It works great. Thank you.
Left by Mike on Mar 21, 2014 12:58 AM

# re: How to Import and Export CSV files directly in SSIS using OleDB Source Task
Requesting Gravatar...
I followed the steps as described above.
But I get the error, when I test the connection:
Connection Manager
Test connection failed because of an error in initializing provider. Unable to load the UDL file.

How to fix this?

Thanks in advance!
Left by simanne on Dec 16, 2014 3:28 PM

# re: How to Import and Export CSV files directly in SSIS using OleDB Source Task
Requesting Gravatar...
Great post. very helpful
Left by Enoch on Apr 01, 2015 6:33 PM

Your comment:
 (will show your gravatar)


Copyright © sathya | Powered by: GeeksWithBlogs.net | Join free