Importing a zip folder

Hello all,
I will like to know how to create a process that unzips a folder and
import all its files into SQL Server 2000/2005.
Any suggestions will be greatly appreciated.
Thanks.You want to import it the files as binary objects into the SQL Server? I
haven't made a tool to do unzip and import all, but I have done a simple
binary file import utility for SQL 2000 enviornment that I can send over to
you if you want.
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"SQL Pupil" wrote:

> Hello all,
> I will like to know how to create a process that unzips a folder and
> import all its files into SQL Server 2000/2005.
> Any suggestions will be greatly appreciated.
>
> Thanks.
>|||> I will like to know how to create a process that unzips a folder and
> import all its files into SQL Server 2000/2005.
> Any suggestions will be greatly appreciated.
There are a number of approaches. One method is to create a DTS package
that runs a command-line extract utility and then an ActiveX Script like the
one example below. You can similarly do this with SSIS and a .Net Script
task.
If you want to get fancy and forego the command-line extract, you extract
directly from .Net Script using ICSharpCode.SharpZipLib.dll
(http://www.icsharpcode.net/OpenSour...b/Download.aspx).
'DTS ActiveX example
Const adOpenKeyset = 1
Const adLockOptimistic = 3
Const adTypeBinary = 1
Function Main()
Set conn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
Set fso = CreateObject("Scripting.FileSystemObject")
Set strm = CreateObject("ADODB.Stream")
ConnectionString = "Provider=SQLOLEDB" & _
";Data Source=MyServer" & _
";Initial Catalog=MyDatabase" & _
";Integrated Security=SSPI"
conn.Open ConnectionString
rs.Open "SELECT File_Name, File_Contents FROM dbo.MyTable", conn,
adOpenKeyset, adLockOptimistic
Set folder =
fso.GetFolder("C:\MyExtractedFiles")
Set fileList = folder.Files
For Each file In fileList
InsertFile(file.Name)
Next
conn.Close
Main = DTSTaskExecResult_Success
End Function
Sub InsertFile(FileName)
strm.Type = adTypeBinary
strm.Open
strm.LoadFromFile FileName
rs.AddNew
rs.Fields("File_name") = FileName
rs.Fields("File_Contents") = strm.Read
rs.Update
strm.Close
End Sub
Hope this helps.
Dan Guzman
SQL Server MVP
"SQL Pupil" <guido_a_perez@.yahoo.com> wrote in message
news:1182447929.191367.191100@.w5g2000hsg.googlegroups.com...
> Hello all,
> I will like to know how to create a process that unzips a folder and
> import all its files into SQL Server 2000/2005.
> Any suggestions will be greatly appreciated.
>
> Thanks.
>|||Sure, any help that you can provide or any ideas, they all are going
to be welcome.
Thanks again|||I don't know .NET but my code is pretty similar to what Dan has offered here
.
My code has some extra stuff, for pulling the table information and such on
the fly. It's still under fix up though, because I only use it for few
databases. So I can't say it will work all accross the board. But if you
want I can still send it ;-)
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Dan Guzman" wrote:

> There are a number of approaches. One method is to create a DTS package
> that runs a command-line extract utility and then an ActiveX Script like t
he
> one example below. You can similarly do this with SSIS and a .Net Script
> task.
> If you want to get fancy and forego the command-line extract, you extract
> directly from .Net Script using ICSharpCode.SharpZipLib.dll
> (http://www.icsharpcode.net/OpenSour...b/Download.aspx).
>
> 'DTS ActiveX example
> Const adOpenKeyset = 1
> Const adLockOptimistic = 3
> Const adTypeBinary = 1
> Function Main()
> Set conn = CreateObject("ADODB.Connection")
> Set rs = CreateObject("ADODB.Recordset")
> Set fso = CreateObject("Scripting.FileSystemObject")
> Set strm = CreateObject("ADODB.Stream")
> ConnectionString = "Provider=SQLOLEDB" & _
> ";Data Source=MyServer" & _
> ";Initial Catalog=MyDatabase" & _
> ";Integrated Security=SSPI"
> conn.Open ConnectionString
> rs.Open "SELECT File_Name, File_Contents FROM dbo.MyTable", conn,
> adOpenKeyset, adLockOptimistic
> Set folder =
> fso.GetFolder("C:\MyExtractedFiles")
> Set fileList = folder.Files
> For Each file In fileList
> InsertFile(file.Name)
> Next
> conn.Close
> Main = DTSTaskExecResult_Success
> End Function
> Sub InsertFile(FileName)
> strm.Type = adTypeBinary
> strm.Open
> strm.LoadFromFile FileName
> rs.AddNew
> rs.Fields("File_name") = FileName
> rs.Fields("File_Contents") = strm.Read
> rs.Update
> strm.Close
> End Sub
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "SQL Pupil" <guido_a_perez@.yahoo.com> wrote in message
> news:1182447929.191367.191100@.w5g2000hsg.googlegroups.com...
>|||Mohit, since I am in the process of gathering all the SQL info and
knowledge that I can get, anything that you can send me will be very
usefull.
Thanks again|||Emailed to your profile address ;-).
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"SQL Pupil" wrote:

> Mohit, since I am in the process of gathering all the SQL info and
> knowledge that I can get, anything that you can send me will be very
> usefull.
> Thanks again
>