Recent Posts | - May, 2023-5,(1)
- February, 2023-2,(1)
- November, 2022-11,(1)
- July, 2022-7,(2)
- March, 2022-3,(1)
- November, 2021-11,(2)
- August, 2021-8,(2)
- July, 2021-7,(2)
- June, 2021-6,(1)
- May, 2021-5,(1)
- March, 2021-3,(1)
- February, 2021-2,(2)
- January, 2021-1,(7)
- December, 2020-12,(3)
- March, 2020-3,(2)
- February, 2020-2,(1)
- December, 2019-12,(2)
- November, 2019-11,(1)
- October, 2019-10,(1)
- September, 2019-9,(1)
- August, 2019-8,(1)
- May, 2019-5,(1)
- April, 2019-4,(2)
- March, 2019-3,(2)
- December, 2018-12,(1)
- November, 2018-11,(4)
- July, 2018-7,(1)
- May, 2018-5,(3)
- April, 2018-4,(2)
- February, 2018-2,(3)
- January, 2018-1,(3)
- November, 2017-11,(2)
- August, 2017-8,(1)
- June, 2017-6,(3)
- May, 2017-5,(5)
- February, 2017-2,(1)
- December, 2016-12,(1)
- October, 2016-10,(2)
- September, 2016-9,(1)
- August, 2016-8,(1)
- July, 2016-7,(1)
- March, 2016-3,(2)
- February, 2016-2,(3)
- December, 2015-12,(5)
- November, 2015-11,(5)
- September, 2015-9,(1)
- August, 2015-8,(2)
- July, 2015-7,(1)
- March, 2015-3,(2)
- February, 2015-2,(1)
- December, 2014-12,(4)
- July, 2014-7,(2)
- June, 2014-6,(2)
- May, 2014-5,(3)
- April, 2014-4,(3)
- March, 2014-3,(1)
- December, 2013-12,(2)
- November, 2013-11,(1)
- July, 2013-7,(1)
- June, 2013-6,(2)
- May, 2013-5,(1)
- March, 2013-3,(3)
- February, 2013-2,(3)
- January, 2013-1,(1)
- December, 2012-12,(3)
- November, 2012-11,(1)
- October, 2012-10,(1)
- September, 2012-9,(1)
- August, 2012-8,(1)
- July, 2012-7,(6)
- June, 2012-6,(1)
- April, 2012-4,(1)
- March, 2012-3,(3)
- February, 2012-2,(3)
- January, 2012-1,(4)
- December, 2011-12,(3)
- October, 2011-10,(3)
- September, 2011-9,(1)
- August, 2011-8,(10)
- July, 2011-7,(2)
- June, 2011-6,(7)
- March, 2011-3,(2)
- February, 2011-2,(3)
- January, 2011-1,(1)
- September, 2010-9,(1)
- August, 2010-8,(2)
- June, 2010-6,(1)
- May, 2010-5,(1)
- April, 2010-4,(3)
- March, 2010-3,(2)
- February, 2010-2,(3)
- January, 2010-1,(1)
- December, 2009-12,(3)
- November, 2009-11,(3)
- October, 2009-10,(2)
- September, 2009-9,(5)
- August, 2009-8,(3)
- July, 2009-7,(9)
- June, 2009-6,(2)
- May, 2009-5,(2)
- April, 2009-4,(9)
- March, 2009-3,(6)
- February, 2009-2,(4)
- January, 2009-1,(10)
- December, 2008-12,(5)
- November, 2008-11,(5)
- October, 2008-10,(13)
- September, 2008-9,(10)
- August, 2008-8,(7)
- July, 2008-7,(8)
- June, 2008-6,(12)
- May, 2008-5,(14)
- April, 2008-4,(12)
- March, 2008-3,(17)
- February, 2008-2,(10)
- January, 2008-1,(16)
- December, 2007-12,(6)
- November, 2007-11,(4)
|
|
|
|
SSIS still a goto tool
|
12/18/2012 10:23:44 AM
|
|
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(), "<!--.*?-->", "");
file.Close();
Dts.Events.FireInformation(0, "Download File", "writing file", string.Empty, 0, ref fireAgain);
System.IO. StreamWriter writefile = new System.IO.StreamWriter(xmlDocFile,false);
// System.IO.StreamWriter writefile = new System.IO.StreamWriter("c:\\server\\bob.xml");
writefile.Write(validXml);
writefile.Close();
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;
}
}
}
|
Blog Home
|
|