how to insert data to DB table based on file exten

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

how to insert data to DB table based on file exten

Author

Topic

dhani
Posting Yak Master

132 Posts

Posted - 2008-10-31 : 16:48:02

hello All,

can any one provide me some guide lines to achieve below solution

please

1) Loop through the all files in a folder
2) check the extension
if extension is .mke then insert the data to Market Table
if extension is .pse then insert the data to ParkSpace table
if extension is .qix then insert the data to quicrec table
if extension is .bati then insert the data to bankAuto table
........
else
mail to user says that file ext (actual extension) not find

if step 2 is successfull then
3) mail to user that no of records inserted to table

Thanks in advance
asin

mct
Starting Member

4 Posts

Posted - 2008-10-31 : 19:26:47

What version of Sql Server are you using ?

-Shiva
[url]mycodetrip.com[/url]

mct
Starting Member

4 Posts

Posted - 2008-10-31 : 19:35:29

If you are using Sql Server 2005, one way of doing it is as follows.

1. Create a Sql Server CLR stored procedure that takes in the folder name as parameter.
2. In the CLR sp, you use the System.IO namespace to read file names matching the extensions. [url]http://msdn.microsoft.com/en-us/library/6yk7a1b0.aspx[/url]
3. Use ADO.Net with SqlBulkCopy class (new in .Net 2.0) to bulk load the data into the respective tables.

If you are using Sql Server 2000, then it might get a little "hairy" ;)

-Shiva
[url]mycodetrip.com[/url]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-01 : 02:44:49

In SSIS you do it as follows
1.Add a ForEachLoop container to loop through files in your folder example below

http://www.sqlis.com/post/Looping-over-files-with-the-Foreach-Loop.aspx
2. use a conditional output task to check the extension of file and make four output lines
3. Add OLEDB destination task on each output to insert the data from file to corresponding tables.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources