Blog Stats
  • Posts - 25
  • Articles - 1
  • Comments - 169
  • Trackbacks - 0

 

How to Import and Export CSV files directly in SSIS using OleDB Source Task

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.


Feedback

# re: How to Import and Export CSV files directly in SSIS using OleDB Source Task

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 3/31/2011 9:34 PM | Arthur

# re: How to Import and Export CSV files directly in SSIS using OleDB Source Task

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. 4/14/2012 3:01 AM | sandeep

# re: How to Import and Export CSV files directly in SSIS using OleDB Source Task

Gravatar Thank you for posting this; it works great for my situation! 5/12/2012 5:12 PM | Bill Quinn

# re: How to Import and Export CSV files directly in SSIS using OleDB Source Task

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 6/13/2012 3:42 PM | Dhivya

# re: How to Import and Export CSV files directly in SSIS using OleDB Source Task

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. 6/18/2012 1:13 PM | dani

# re: How to Import and Export CSV files directly in SSIS using OleDB Source Task

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) 12/5/2012 3:56 PM | Ank

# re: How to Import and Export CSV files directly in SSIS using OleDB Source Task

Gravatar Thank you very much for this! You saved me a ton of time. 1/7/2013 4:43 AM | Teresa

# re: How to Import and Export CSV files directly in SSIS using OleDB Source Task

Gravatar Thanks For posting this. It's working perfectly. 1/24/2013 2:30 PM | Annamalai

# re: How to Import and Export CSV files directly in SSIS using OleDB Source Task

Gravatar Brilliant stuff. Went thru almost 30 different site but couldnt find solution to this issue.You are really good!! 3/18/2013 12:57 PM | Varun Sahay

# re: How to Import and Export CSV files directly in SSIS using OleDB Source Task

Gravatar Wonderful! Many thanks for this great article. That was realy helpful. 10/31/2013 7:32 AM | Sherry

# re: How to Import and Export CSV files directly in SSIS using OleDB Source Task

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.. 1/15/2014 12:42 PM | Tanu

# re: How to Import and Export CSV files directly in SSIS using OleDB Source Task

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. 2/11/2014 10:24 PM | Sunny

# re: How to Import and Export CSV files directly in SSIS using OleDB Source Task

Gravatar It works great. Thank you. 3/21/2014 12:58 AM | Mike

# re: How to Import and Export CSV files directly in SSIS using OleDB Source Task

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!
12/16/2014 3:28 PM | simanne

Post A Comment
Title:
Name:
Email:
Comment:
Verification:
 

 

 

Copyright © sathya