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();
foreach (string dc in value)
while ( !sr.EndOfStream )
value = sr.ReadLine().Split(',');
if(value.Length == dt.Columns.Count)
row = dt.NewRow();
row.ItemArray = value;
SqlBulkCopy bc = new SqlBulkCopy(con.ConnectionString, SqlBulkCopyOptions.TableLock);
bc.DestinationTableName = "tblparam_test";
bc.BatchSize = dt.Rows.Count;
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,