Download zip file from ftp server and insert into SQL DB

Jan 27, 2012 at 5:00 AM

Hi,   I want to download a zip file that contains roughly 25k records times 8 column of text, unzip it to a stream and insert the data into a MS SQL Server 2008 database table.  I also want to do this every 4 hours.  I've created SSIS packages that do exactly this, and scheduled them using a SQL Server Job.  For this task, I need to do everything on a shared Windows webserver.  I believe (hope) this can be done with ASP.net and C#.

I'm thinking that I can use the following solutions:

1) to launch my process every 4 hours:  http://www.codeproject.com/Articles/12117/Simulate-a-Windows-Service-using-ASP-NET-to-run-sc

2) to download my .zip file from the ftp server:  http://aspalliance.com/1187_Building_a_Simple_FTP_Application_Using_C_20.3

3) to unzip the file to a stream:  DotNetZip

4) to save the data to a sql server db:  

private void saveStreamToDB(StreamReader sr)
       
{
           
SqlConnection con = new SqlConnection(connString);
            string line = sr.ReadLine();
           
string[] value = line.Split(',');
           
DataTable dt = new DataTable();
           
DataRow row;
           
foreach (string dc in value)
           
{
                dt
.Columns.Add(new DataColumn(dc));
           
}

           
while ( !sr.EndOfStream )
           
{
                value
= sr.ReadLine().Split(',');
               
if(value.Length == dt.Columns.Count)
               
{
                    row
= dt.NewRow();
                    row
.ItemArray = value;
                    dt
.Rows.Add(row);
               
}
           
}
           
SqlBulkCopy bc = new SqlBulkCopy(con.ConnectionString, SqlBulkCopyOptions.TableLock);
            bc
.DestinationTableName = "tblparam_test";
            bc
.BatchSize = dt.Rows.Count;
            con
.Open();
            bc
.WriteToServer(dt);
            bc
.Close();
            con
.Close();
       
}

 

I'm wondering if this entire solution is feasible.  I'm also wondering if there's a better solution, other than SSIS.  I can't use SSIS because I don't have a dedicated server available for this, and I have limited financial resources.

Does anyone see major flaws with this solution?

Any and all suggestions are most welcome.

Thanks in advance,

Joe