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) :-

Create a new connection Manager

Select New Oledb Connection

Select the Provider as Microsoft Jet 4.0 Oledb Provider

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

Choose All. You would see a property called ExtendedProperties.

In that paste this without quotes : "text;HDR=Yes;FMT=Delimited"

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.

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)

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.

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.

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

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

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

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.

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

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.

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

I followed the steps as described above.But I get the error, when I test the connection:Connection ManagerTest connection failed because of an error in initializing provider. Unable to load the UDL file.

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

While importing the larger file, i get the below errorError at Data Flow Task [OLE DB Source [1]]: There is more than one data source column with the name "division#csv.NoName". The data source column names must be unique.

Note: The column names are unique in the flatfile. Also it runs successfully when i test with 100s of records