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/OpenSource/SharpZipLib/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:
> > 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/OpenSource/SharpZipLib/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.
> >
>|||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
>