Around the farm I always used to carry knife, a long time ago I switched to a Leatherman multi-tool, it just has more utility.  SSIS provides that same utility.

Recently I ran into a need to download an XML File, that had URL's in it to other files that needed to be downloaded via HTTP and imported into a database.  What a pain in the ass.  XML is not my favorite, and downloading via HTTP isn't either, my preference would have been just to do an FTP get with a wild card, but architects are not DBA's and why would you consult with someone technical for the requirements ?

Anyway, SSIS made this job very simple, use XML and loops to download and import data.  It became very simple and SSIS impressed me.

What made this example even more fun was the XML was malformed, and I had to use regex to remove the malformed lines !   That is a whole other topic on how to remove comments from XML.

The key to doing this is embedding a script task in a loop to do the download,  the rest is basic SSIS import stuff, here is what I used:

string xmlDocFile = "";

try

// Logging start of download

bool fireAgain = true;

Dts.Events.FireInformation(0, "Download File", "Start downloading ", string.Empty, 0, ref fireAgain);

// Create a webclient to download a file

WebClient mySSISWebClient = new WebClient();

// Download file and use the Flat File Connectionstring (D:\SourceFiles\Products.xml)

// to save the file (and replace the existing file)

// build dynamic download URL for {website removed to protect the innocent}

mySSISWebClient.DownloadFile("http://somewebsite.com/SubDir/" +

Dts.Variables["YearMonthDay"].Value.ToString() + "/xml/siteData.xml",

"C:\\server\\Download\\" +

Dts.Variables["YearMonthDay"].Value.ToString()

+ "_siteData.xml");

// set the user variable, used in the data import

Dts.Variables["xmlimportfile"].Value = "C:\\server\\Download\\" +

Dts.Variables["YearMonthDay"].Value.ToString() +

"_siteData.xml";

xmlDocFile = "C:\\server\\Download\\" +

Dts.Variables["YearMonthDay"].Value.ToString() +

"_siteData.xml";

// Logging end of download

Dts.Events.FireInformation(0, "Download File", "Finished downloading", string.Empty, 0, ref fireAgain);

// Quit Script Task succesful

Dts.Events.FireInformation(0, "Download File", "Starting remove comments.", string.Empty, 0, ref fireAgain);

System.IO.StreamReader file = new System.IO.StreamReader(xmlDocFile);

string validXml = Regex.Replace(file.ReadToEnd(), "<!--.*?-->", "");

Dts.Events.FireInformation(0, "Download File", "writing file", string.Empty, 0, ref fireAgain);

System.IO.StreamWriter writefile = new System.IO.StreamWriter(xmlDocFile,false);

Dts.Events.FireInformation(0, "Download File", "Comments removed.", string.Empty, 0, ref fireAgain);

Dts.TaskResult = (int)ScriptResults.Success;

catch (Exception ex)

// Logging why download failed

Dts.Events.FireError(0, "Download File", "Download failed: " + ex.Message, string.Empty, 0);

// Quit Script Task unsuccesful

Dts.TaskResult = (int)ScriptResults.Failure;