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.