tag:blogger.com,1999:blog-294380132018-11-16T04:56:06.731-05:00Rafael SalasBusiness Intelligence with an accentRafael Salashttps://plus.google.com/116687955206452017583noreply@blogger.comBlogger87125http://farm5.static.flickr.com/4134/4773191870_8082d64448_m.jpgRafaelShttps://feedburner.google.comSubscribe with My Yahoo!Subscribe with BloglinesSubscribe with NetvibesSubscribe with PageflakesSubscribe with The Free DictionarySubscribe with Bitty BrowserSubscribe with Podcast ReadySubscribe with Daily RotationSubscribe with My AOLSubscribe with WikioSubscribe with Excite MIXSubscribe with GoogleSubscribe with NewsGatorSubscribe with WebwagSubscribe with Live.comSubscribe with PlusmoThank you for subscribing to my blog!tag:blogger.com,1999:blog-29438013.post-74463016990836352712014-09-26T17:24:00.000-04:002014-09-26T17:24:12.011-04:00SQL Saturday Charlotte - BI Edition is Next Week! <br /><div class="Saletel" style="margin: 0in 0in 0pt; mso-pagination: none; text-align: center;"><strong><span style="color: #006699;"><span style="font-family: &quot;Arial Narrow&quot;,&quot;sans-serif&quot;; font-size: 16pt; line-height: 118%; mso-ligatures: none;"><a href="http://sqlsaturday.com/330/eventhome.aspx" target="_blank">SQLSaturday Charlotte&nbsp;is the largest event serving Business Intelligence&nbsp;and SQL Server professionals in the Charlotte region</a></span><span style="font-family: &quot;Andalus&quot;,&quot;serif&quot;; font-size: 9pt; line-height: 118%; mso-ligatures: none;"><o:p></o:p></span></span></strong></div><br /><div class="Sale" style="margin: 0in 0in 0pt; mso-pagination: none;"><span style="font-family: &quot;Segoe UI Symbol&quot;,&quot;sans-serif&quot;; font-size: 12pt; line-height: 118%; mso-ligatures: none;"><a href="http://sqlsaturday.com/330/eventhome.aspx" target="_blank">SQLSaturday Charlotte</a> – BI Edition is only a week away and I would like to share some information about it: <o:p></o:p></span></div><br /><div class="Sale" style="margin: 0in 0in 0pt 0.5in; mso-list: l0 level1 lfo1; mso-pagination: none; text-indent: -0.25in;"><!--[if !supportLists]--><a href="http://3.bp.blogspot.com/-Hkwju_4F06M/VCXYLFRnl7I/AAAAAAAAAvI/JX3YWeYM6ZY/s1600/Capture.JPG" imageanchor="1" style="clear: right; float: right; margin-bottom: 1em; margin-left: 1em;"><img border="0" src="http://3.bp.blogspot.com/-Hkwju_4F06M/VCXYLFRnl7I/AAAAAAAAAvI/JX3YWeYM6ZY/s1600/Capture.JPG" height="281" width="400" /></a><span style="font-family: Symbol; font-size: 12pt; line-height: 118%; mso-bidi-font-family: Symbol; mso-fareast-font-family: Symbol; mso-ligatures: none;"><span style="mso-list: Ignore;">·<span style="font-size-adjust: none; font-stretch: normal; font: 7pt/normal &quot;Times New Roman&quot;;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </span></span></span><!--[endif]--><span style="font-family: &quot;Segoe UI Symbol&quot;,&quot;sans-serif&quot;; font-size: 12pt; line-height: 118%; mso-ligatures: none;"><a href="http://sqlsaturday.com/330/schedule.aspx" target="_blank">We have more than 40 sessions</a> across different technical and professional development tracks.<o:p></o:p></span></div><br /><div class="Sale" style="margin: 0in 0in 0pt 0.5in; mso-list: l0 level1 lfo1; mso-pagination: none; text-indent: -0.25in;"><!--[if !supportLists]--><span style="font-family: Symbol; font-size: 12pt; line-height: 118%; mso-bidi-font-family: Symbol; mso-fareast-font-family: Symbol; mso-ligatures: none;"><span style="mso-list: Ignore;">·<span style="font-size-adjust: none; font-stretch: normal; font: 7pt/normal &quot;Times New Roman&quot;;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </span></span></span><!--[endif]--><span style="font-family: &quot;Segoe UI Symbol&quot;,&quot;sans-serif&quot;; font-size: 12pt; line-height: 118%; mso-ligatures: none;">There is content for all skill levels<o:p></o:p></span></div><br /><div class="Sale" style="margin: 0in 0in 0pt 0.5in; mso-list: l0 level1 lfo1; mso-pagination: none; text-indent: -0.25in;"><!--[if !supportLists]--><span style="font-family: Symbol; font-size: 12pt; line-height: 118%; mso-bidi-font-family: Symbol; mso-fareast-font-family: Symbol; mso-ligatures: none;"><span style="mso-list: Ignore;">·<span style="font-size-adjust: none; font-stretch: normal; font: 7pt/normal &quot;Times New Roman&quot;;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </span></span></span><!--[endif]--><span style="font-family: &quot;Segoe UI Symbol&quot;,&quot;sans-serif&quot;; font-size: 12pt; line-height: 118%; mso-ligatures: none;">We have a great lineup of regional and national speakers.<o:p></o:p></span></div><br /><div class="Sale" style="margin: 0in 0in 0pt 0.5in; mso-list: l0 level1 lfo1; mso-pagination: none; text-indent: -0.25in;"><!--[if !supportLists]--><span style="font-family: Symbol; font-size: 12pt; line-height: 118%; mso-bidi-font-family: Symbol; mso-fareast-font-family: Symbol; mso-ligatures: none;"><span style="mso-list: Ignore;">·<span style="font-size-adjust: none; font-stretch: normal; font: 7pt/normal &quot;Times New Roman&quot;;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </span></span></span><!--[endif]--><span style="font-family: &quot;Segoe UI Symbol&quot;,&quot;sans-serif&quot;; font-size: 12pt; line-height: 118%; mso-ligatures: none;">There are many opportunities to network and connect with other professionals and companies in the area.</span></div><div class="Sale" style="margin: 0in 0in 0pt; mso-pagination: none;"><span style="font-family: &quot;Segoe UI Symbol&quot;,&quot;sans-serif&quot;; font-size: 12pt; line-height: 118%; mso-ligatures: none;"><o:p>&nbsp;</o:p></span></div><br /><div class="MsoNormal" style="margin: 0in 0in 6pt; mso-pagination: none;"><span style="font-family: &quot;Segoe UI Symbol&quot;,&quot;sans-serif&quot;; font-size: 12pt; line-height: 118%; mso-bidi-font-family: &quot;Times New Roman&quot;; mso-ligatures: none;">Space is limited, so if you are planning to attend I urge you to <a href="http://sqlsaturday.com/330/register.aspx" target="_blank">sign up</a> soon and join the 400 professionals that have already registered. </span></div><div class="MsoNormal" style="margin: 0in 0in 6pt; mso-pagination: none;"><span style="font-family: &quot;Segoe UI Symbol&quot;,&quot;sans-serif&quot;; font-size: 12pt; line-height: 118%; mso-bidi-font-family: &quot;Times New Roman&quot;; mso-ligatures: none;"></span>&nbsp;</div><div class="MsoNormal" style="margin: 0in 0in 6pt; mso-pagination: none;"><span style="font-family: &quot;Segoe UI Symbol&quot;,&quot;sans-serif&quot;; font-size: 12pt; line-height: 118%; mso-bidi-font-family: &quot;Times New Roman&quot;; mso-ligatures: none;">I hope to see you next Saturday!<o:p></o:p></span></div><div class="feedflare">
<a href="http://feeds.feedburner.com/~ff/RafaelS?a=jgEZuXk78t0:K7REX6b_KFY:yIl2AUoC8zA"><img src="http://feeds.feedburner.com/~ff/RafaelS?d=yIl2AUoC8zA" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=jgEZuXk78t0:K7REX6b_KFY:63t7Ie-LG7Y"><img src="http://feeds.feedburner.com/~ff/RafaelS?d=63t7Ie-LG7Y" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=jgEZuXk78t0:K7REX6b_KFY:-BTjWOF_DHI"><img src="http://feeds.feedburner.com/~ff/RafaelS?i=jgEZuXk78t0:K7REX6b_KFY:-BTjWOF_DHI" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=jgEZuXk78t0:K7REX6b_KFY:V_sGLiPBpWU"><img src="http://feeds.feedburner.com/~ff/RafaelS?i=jgEZuXk78t0:K7REX6b_KFY:V_sGLiPBpWU" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=jgEZuXk78t0:K7REX6b_KFY:gIN9vFwOqvQ"><img src="http://feeds.feedburner.com/~ff/RafaelS?i=jgEZuXk78t0:K7REX6b_KFY:gIN9vFwOqvQ" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=jgEZuXk78t0:K7REX6b_KFY:I97M6haO00k"><img src="http://feeds.feedburner.com/~ff/RafaelS?d=I97M6haO00k" border="0"></img></a>
</div><img src="http://feeds.feedburner.com/~r/RafaelS/~4/jgEZuXk78t0" height="1" width="1" alt=""/>Rafael Salashttps://plus.google.com/116687955206452017583noreply@blogger.com0http://www.rafael-salas.com/2014/09/sql-saturday-charlotte-bi-edition-is.htmltag:blogger.com,1999:blog-29438013.post-23486468244546099842014-02-19T23:20:00.000-05:002014-02-19T23:20:42.733-05:00Power Query: Consuming SSIS Upgrade Wizard Output File<br />Have you ever had the need to create summary and detailed reports to understand the results of a SSIS upgrade exercise such as&nbsp; packages by&nbsp; type of&nbsp; errors and&nbsp; warnings; the number of packages that upgraded successfully/unsuccessfully?<br /><br />Upgrading SSIS packages is in most cases a straightforward exercise using the SSIS upgrade wizard. However, the output file produced by the wizard with the upgrade results (warning, errors, upgrade status, etc.) is very verbose and formatted in a way that makes it very hard to consume when upgrading a large number of packages. <br /><br />I recently experienced this pain when working in a project that required to upgrade more than<strong> 1,800 SSIS</strong> packages that produced an upgrade output file with more than <strong>35,000 lines</strong> worth of messages! See snippet of the output file below.<br /><br /><a href="http://lh3.ggpht.com/-27571SkWCZc/UwV-NnVjNqI/AAAAAAAAAuM/0cEiXyZW3gQ/s1600-h/SNAGHTML2a116fde%25255B6%25255D.png"><img alt="SNAGHTML2a116fde" border="0" src="http://lh5.ggpht.com/-3rw71yX0Cnc/UwV-ObsCqlI/AAAAAAAAAuU/kBR8uXRUJWQ/SNAGHTML2a116fde_thumb%25255B3%25255D.png?imgmax=800" height="354" style="background-image: none; border: 0px currentColor; display: inline; padding-left: 0px; padding-right: 0px; padding-top: 0px;" title="SNAGHTML2a116fde" width="588" /></a><br /><br /><br />Since I typically use the output file to get a better sense of the effort involved to complete the upgrade of all packages, I quickly realized that for this project I would need to come up with a way to consume such a large output file. Fortunately, I was able to create an excel report like the one below using Power Query in a very simple way. As you can see below, it is very easy to get a sense of how difficult&nbsp;the upgrade is going to be. In the example below, I can quickly see there are only 14 packages that had errors ad 599 with warnings. Additionally, I can easily get a break down of the type of errors and the get the list of packages by message code/type.<br /><br /><br /><a href="http://lh6.ggpht.com/-OjVwzXm0W8c/UwV-O-qRU_I/AAAAAAAAAuc/r7OibH6CR1Y/s1600-h/image%25255B5%25255D.png"><img alt="image" border="0" src="http://lh6.ggpht.com/-haWPVw8-ORo/UwV-PToDKsI/AAAAAAAAAuk/dfOyT0pxM_E/image_thumb%25255B3%25255D.png?imgmax=800" height="293" style="background-image: none; border: 0px currentColor; display: inline; padding-left: 0px; padding-right: 0px; padding-top: 0px;" title="image" width="640" /></a><br /><br />If you are interested in creating a similar report, you can download a copy of a report template I created: <a href="https://onedrive.live.com/redir?resid=9E3064285B330F2E!39935&amp;authkey=!AC-HjP4W0oNQh4Q&amp;ithint=file%2c.xlsx" target="_blank"><strong>SSISUpgradeResultsReport Template.xlsx</strong></a><br /><br /><br />Note: Make sure you have <a href="http://office.microsoft.com/en-us/excel/download-microsoft-power-query-for-excel-FX104018616.aspx" target="_blank">Power Query for Excel</a> installed and you edit <strong>pqSSISUpgradeReport</strong> Power Query query to point to the location where your SSIS upgrade wizard output file is stored ( See query below) <br /><br /><span style="font-family: Courier New; font-size: xx-small;">let<br />&nbsp;&nbsp;&nbsp; Source = Table.FromColumns({Lines.FromBinary(File.Contents<span style="background-color: yellow;">("D:\SSIS\SSIS Upgrade report all packages 2013 10 09.txt")</span>)}),<br />&nbsp;&nbsp;&nbsp; FilteredRowsBlanks = Table.SelectRows(Source, each ([Column1] &lt;&gt; "" and [Column1] &lt;&gt; "#(tab)" and [Column1] &lt;&gt; "#(tab)Messages" and [Column1] &lt;&gt; "The Upgrade is Complete")),<br />&nbsp;&nbsp;&nbsp; InsertedCustom = Table.AddColumn(FilteredRowsBlanks, "Custom", each if Text.Range([Column1],0,19)="- Upgrading package" then [Column1] else null),<br />&nbsp;&nbsp;&nbsp; FillDown = Table.FillDown( InsertedCustom,"Custom"),<br />&nbsp;&nbsp;&nbsp; FilteredRows1 = Table.SelectRows(FillDown, each not Text.Contains([Column1], "- Upgrading package")),<br />&nbsp;&nbsp;&nbsp; SplitColumnDelimiter = Table.SplitColumn(FilteredRows1,"Custom",Splitter.SplitTextByEachDelimiter({"("}, null, true),2),<br />&nbsp;&nbsp;&nbsp; ReplacedValue = Table.ReplaceValue(SplitColumnDelimiter,")","",Replacer.ReplaceText,{"Custom.2"}),<br />&nbsp;&nbsp;&nbsp; ReplacedValue1 = Table.ReplaceValue(ReplacedValue,"- Upgrading package","",Replacer.ReplaceText,{"Custom.1"}),<br />&nbsp;&nbsp;&nbsp; SplitColumnDelimiter1 = Table.SplitColumn(ReplacedValue1,"Custom.1",Splitter.SplitTextByEachDelimiter({"\"}, null, true),2),<br />&nbsp;&nbsp;&nbsp; RenamedColumns = Table.RenameColumns(SplitColumnDelimiter1,{{"Column1", "Mesage"}, {"Custom.1.1", "Folder"}, {"Custom.1.2", "PackageName"}, {"Custom.2", "UpgradeResult"}}),<br />&nbsp;&nbsp;&nbsp; ReorderedColumns = Table.ReorderColumns(RenamedColumns,{"Folder", "PackageName", "UpgradeResult", "Mesage"}),<br />&nbsp;&nbsp;&nbsp; SplitColumnDelimiter2 = Table.SplitColumn(ReorderedColumns,"Mesage",Splitter.SplitTextByEachDelimiter({":"}, null, false),2),<br />&nbsp;&nbsp;&nbsp; RenamedColumns1 = Table.RenameColumns(SplitColumnDelimiter2,{{"Mesage.1", "Message Code"}, {"Mesage.2", "Message"}}),<br />&nbsp;&nbsp;&nbsp; TrimmedMessageCode = Table.TransformColumns(RenamedColumns1,{{"Message Code", Text.Trim}}),<br />&nbsp;&nbsp;&nbsp; Lowercase = Table.TransformColumns(TrimmedMessageCode,{{"Message Code", Text.Trim}, {"PackageName", Text.Lower}})<br />in<br />&nbsp;&nbsp;&nbsp; Lowercase</span><div class="feedflare">
<a href="http://feeds.feedburner.com/~ff/RafaelS?a=ndMxmMTdPPE:48R8BqRtDRA:yIl2AUoC8zA"><img src="http://feeds.feedburner.com/~ff/RafaelS?d=yIl2AUoC8zA" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=ndMxmMTdPPE:48R8BqRtDRA:63t7Ie-LG7Y"><img src="http://feeds.feedburner.com/~ff/RafaelS?d=63t7Ie-LG7Y" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=ndMxmMTdPPE:48R8BqRtDRA:-BTjWOF_DHI"><img src="http://feeds.feedburner.com/~ff/RafaelS?i=ndMxmMTdPPE:48R8BqRtDRA:-BTjWOF_DHI" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=ndMxmMTdPPE:48R8BqRtDRA:V_sGLiPBpWU"><img src="http://feeds.feedburner.com/~ff/RafaelS?i=ndMxmMTdPPE:48R8BqRtDRA:V_sGLiPBpWU" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=ndMxmMTdPPE:48R8BqRtDRA:gIN9vFwOqvQ"><img src="http://feeds.feedburner.com/~ff/RafaelS?i=ndMxmMTdPPE:48R8BqRtDRA:gIN9vFwOqvQ" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=ndMxmMTdPPE:48R8BqRtDRA:I97M6haO00k"><img src="http://feeds.feedburner.com/~ff/RafaelS?d=I97M6haO00k" border="0"></img></a>
</div><img src="http://feeds.feedburner.com/~r/RafaelS/~4/ndMxmMTdPPE" height="1" width="1" alt=""/>Rafael Salashttps://plus.google.com/116687955206452017583noreply@blogger.com0http://www.rafael-salas.com/2014/02/power-query-consuming-ssis-upgrade.htmltag:blogger.com,1999:blog-29438013.post-71082998215699954322013-09-27T22:45:00.000-04:002013-09-27T22:48:44.990-04:00PASS Summit: First Timer Questions<span style="font-family: inherit;">Besides </span><a href="http://www.sqlpass.org/summit/2013/Sessions/SessionDetails.aspx?sid=4549" target="_blank"><span style="font-family: inherit;">speaking at PASS Summit 2013</span></a><span style="font-family: inherit;">, I am also volunteering as PASS buddy to help a small group of people that&nbsp;is attending the&nbsp;conference for the first time and make sure they have a good Summit experience .</span><br /><span style="font-family: inherit;"></span><br /><span style="font-family: inherit;">I have gotten very good questions from my group and I thought I would share them along with my recommendations - in case they are relevant to you as well.</span><br /><span style="font-family: inherit;"></span><br /><div class="MsoNormal" style="margin: 0in 0in 8pt;"><strong><span style="font-family: inherit;">Q. I see an interesting session and I was wondering how I could get further information about it beforehand so I am confident it covers what I want.</span></strong></div><div class="MsoNormal" style="margin: 0in 0in 8pt;"><span style="font-family: inherit;">A. Few ways come to mind:</span></div><div class="MsoListParagraphCxSpFirst" style="margin: 0in 0in 0pt 0.5in; mso-list: l0 level1 lfo1; text-indent: -0.25in;"><!--[if !supportLists]--><span style="font-family: inherit;"><span style="mso-bidi-font-family: Symbol; mso-fareast-font-family: Symbol;"><span style="mso-list: Ignore;">·<span style="font-size-adjust: none; font-stretch: normal; font: 7pt/normal &quot;Times New Roman&quot;;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </span></span></span><!--[endif]-->Read the abstract of the session: </span><a href="http://www.sqlpass.org/summit/2013/Sessions.aspx"><span style="color: #0563c1; font-family: inherit;">http://www.sqlpass.org/summit/2013/Sessions.aspx</span></a><span style="font-family: inherit;"></span></div><div class="MsoListParagraphCxSpLast" style="margin: 0in 0in 8pt 0.5in; mso-list: l0 level1 lfo1; text-indent: -0.25in;"><!--[if !supportLists]--><span style="font-family: inherit;"><span style="mso-bidi-font-family: Symbol; mso-fareast-font-family: Symbol;"><span style="mso-list: Ignore;">·<span style="font-size-adjust: none; font-stretch: normal; font: 7pt/normal &quot;Times New Roman&quot;;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </span></span></span><!--[endif]-->Connect with the speaker and ask them directly. You can read speakers Bios and most of them list their blog, website and twitter. This method gets extra points as you network at the same time.</span></div><div class="MsoNormal" style="margin: 0in 0in 8pt;"><strong><span style="font-family: inherit;">Q. There are so many sessions, which one would you recommend?</span></strong></div><div class="MsoNormal" style="margin: 0in 0in 8pt;"><span style="font-family: inherit;">A. Ah! a tricky question. I would recommend to start by looking at the schedule and the session abstracts and make sure you consider levels (beginner, intermediate, advanced, etc.) when making your selection. I recommend you use the schedule builder to mark the sessions you want to attend:<span style="mso-spacerun: yes;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </span></span><a href="http://www.sqlpass.org/summit/2013/UserLogin/tabid/9758/Default.aspx?returnurl=%2fsummit%2f2013%2fSessions%2fBuildSchedule.aspx"><span style="color: #0563c1; font-family: inherit;">http://www.sqlpass.org/summit/2013/UserLogin/tabid/9758/Default.aspx?returnurl=%2fsummit%2f2013%2fSessions%2fBuildSchedule.aspx</span></a><span style="font-family: inherit;"><span style="mso-spacerun: yes;">&nbsp;</span>and carry the PDF format of the schedule: </span><a href="http://www.sqlpass.org/LinkClick.aspx?fileticket=eNh5qYO0Qkc%3d&amp;tabid=9664"><span style="color: #0563c1; font-family: inherit;">http://www.sqlpass.org/LinkClick.aspx?fileticket=eNh5qYO0Qkc%3d&amp;tabid=9664</span></a><span style="font-family: inherit;"><span style="mso-spacerun: yes;">&nbsp;</span>with you at all times. Keep in mind that you can buy the recording of ALL session for $125; which means you can always watch that session that you couldn’t catch. </span><a href="http://www.sqlpass.org/summit/2013/Sessions/SessionRecordings.aspx"><span style="color: #0563c1; font-family: inherit;">http://www.sqlpass.org/summit/2013/Sessions/SessionRecordings.aspx</span></a><span style="font-family: inherit;"><span style="mso-spacerun: yes;">&nbsp;</span><span style="mso-spacerun: yes;">&nbsp;</span><o:p>&nbsp;</o:p></span></div><span style="font-family: inherit;"></span><br /><div class="MsoNormal" style="margin: 0in 0in 8pt;"><strong><span style="font-family: inherit;">Q. I would like to connect and network with some speakers. What is the best way to approach them?</span></strong></div><div class="MsoNormal" style="margin: 0in 0in 8pt;"><span style="font-family: inherit;">A. ah!<span style="mso-spacerun: yes;">&nbsp; </span>This is another tricky one as we all have different styles and level of comfort when approaching people we don’t know. Most speakers will arrive 10-15 minutes before their sessions and will hang out and take questions at the end. You can approach them at those times and introduce yourself. You can follow up with a LinkedIn invite, Twitter, etc. Note: the minutes before the presentation may be stressful for the speaker, so be mindful of their time.</span></div><div class="MsoNormal" style="margin: 0in 0in 8pt;"><strong><span style="font-family: inherit;">Q. This is my first time coming to town. What are good places to hang out/eat, etc.?</span></strong></div><div class="MsoNormal" style="margin: 0in 0in 8pt;"><span style="font-family: inherit;">A. Downtown Charlotte, or Uptown as we call it, is very walkable. There is also a free shuttle service provided by the city that runs during the day: </span><a href="http://charmeck.org/city/charlotte/cats/Bus/ridingcats/Pages/GoldRush.aspx"><span style="color: #0563c1; font-family: inherit;">http://charmeck.org/city/charlotte/cats/Bus/ridingcats/Pages/GoldRush.aspx</span></a><span style="font-family: inherit;"><span style="mso-spacerun: yes;">&nbsp;</span></span></div><div class="MsoNormal" style="margin: 0in 0in 8pt;"><span style="font-family: inherit;"><span style="mso-spacerun: yes;">&nbsp;&nbsp;&nbsp;&nbsp; </span>Our friends from SQLSentry are also providing a Shuttle services at night time with stops in interesting places: </span><a href="http://greg.blogs.sqlsentry.net/2013/09/pass-summiteers-guide-to-charlotte-part.html"><span style="color: #0563c1; font-family: inherit;">http://greg.blogs.sqlsentry.net/2013/09/pass-summiteers-guide-to-charlotte-part.html</span></a><span style="font-family: inherit;"><span style="mso-spacerun: yes;">&nbsp;</span></span></div><div class="MsoNormal" style="margin: 0in 0in 8pt;"><span style="mso-spacerun: yes;"></span><span style="font-family: inherit;"><span style="mso-spacerun: yes;">&nbsp;&nbsp;&nbsp;&nbsp; </span>As always, there is an app for that. I use Yelp! To checkout places when I visit a new city. This link shows things around the convention center: </span><a href="http://www.yelp.com/search?find_desc=&amp;find_loc=210+E+Trade+St&amp;ns=1&amp;ls=1a811fb8b865826d#find_loc=210+E+Trade+St,+charlotte+NC"><span style="color: #0563c1; font-family: inherit;">http://www.yelp.com/search?find_desc=&amp;find_loc=210+E+Trade+St&amp;ns=1&amp;ls=1a811fb8b865826d#find_loc=210+E+Trade+St,+charlotte+NC</span></a><span style="font-family: inherit;"><span style="mso-spacerun: yes;">&nbsp;</span><span style="mso-spacerun: yes;">&nbsp;</span><o:p></o:p></span></div><strong><span style="font-family: inherit;">Q. Will WI-FI be available at the convention center?</span></strong><br /><span style="font-family: inherit;">A. Yes, but keep in mind there will be a few thousand people trying to use it as well.<o:p></o:p></span><br /><span style="font-family: inherit;"></span><br /><strong><span style="font-family: inherit;">Q.<span style="mso-spacerun: yes;">&nbsp; </span>Should I carry my laptop or tablet?</span></strong><br /><div class="MsoNormal" style="margin: 0in 0in 8pt;"><span style="font-family: inherit;">A. I would recommend to go around as light-weighted as possible as days are long and you want to save as much energy as you can. I would carry the lightest device I can live with. Please leave the server at home.</span></div><div class="MsoNormal" style="margin: 0in 0in 8pt;"><strong><span style="font-family: inherit;">Q. I heard there other activities and after hour events. How do I know about them?</span></strong></div><div class="MsoNormal" style="margin: 0in 0in 8pt;"><span style="font-family: inherit;">A. PASS Summit website has a “Connect at PASS Summit” page: </span><a href="http://www.sqlpass.org/summit/2013/Connect.aspx"><span style="color: #0563c1; font-family: inherit;">http://www.sqlpass.org/summit/2013/Connect.aspx</span></a><span style="font-family: inherit;"><span style="mso-spacerun: yes;">&nbsp;</span></span></div><div class="MsoNormal" style="margin: 0in 0in 8pt;"><span style="font-family: inherit;"><span style="mso-spacerun: yes;">&nbsp;&nbsp;&nbsp;&nbsp; </span>Events range from Luncheons, Karaoke parties to 5k walk/runs so there is something for every taste. If you have a Twitter account, then you may want to follow @SQLPass and watch #SQLPASS and #Summit13 hash tags. If you don’t have an account, then just get one!<o:p>&nbsp;</o:p></span></div><span style="font-family: inherit;"></span><br /><div class="MsoNormal" style="margin: 0in 0in 8pt;"><strong><span style="font-family: inherit;">Q. What about Airport- Convention center transportation?</span></strong></div><div class="MsoNormal" style="margin: 0in 0in 8pt;"><span style="font-family: inherit;"><span style="mso-spacerun: yes;">&nbsp;A. </span>Taxis:<span style="mso-spacerun: yes;">&nbsp; </span>flat rate from CLT airport to center city $25</span></div><div class="MsoNormal" style="margin: 0in 0in 8pt;"><span style="font-family: inherit;"><span style="mso-spacerun: yes;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </span>Bus: Route 5, departs from Airport every 20 minutes, and it is a 25 minutes ride to the Charlotte Transportation Center (CTC) which is 2 blocks from convention center. Cost: $2 </span><a href="http://charmeck.org/city/charlotte/cats/Bus/routes/Pages/default.aspx"><span style="color: #0563c1; font-family: inherit;">http://charmeck.org/city/charlotte/cats/Bus/routes/Pages/default.aspx</span></a><span style="font-family: inherit;"><o:p></o:p></span></div><div class="feedflare">
<a href="http://feeds.feedburner.com/~ff/RafaelS?a=gr4Y-R6_EqE:YfO3ewhqdfw:yIl2AUoC8zA"><img src="http://feeds.feedburner.com/~ff/RafaelS?d=yIl2AUoC8zA" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=gr4Y-R6_EqE:YfO3ewhqdfw:63t7Ie-LG7Y"><img src="http://feeds.feedburner.com/~ff/RafaelS?d=63t7Ie-LG7Y" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=gr4Y-R6_EqE:YfO3ewhqdfw:-BTjWOF_DHI"><img src="http://feeds.feedburner.com/~ff/RafaelS?i=gr4Y-R6_EqE:YfO3ewhqdfw:-BTjWOF_DHI" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=gr4Y-R6_EqE:YfO3ewhqdfw:V_sGLiPBpWU"><img src="http://feeds.feedburner.com/~ff/RafaelS?i=gr4Y-R6_EqE:YfO3ewhqdfw:V_sGLiPBpWU" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=gr4Y-R6_EqE:YfO3ewhqdfw:gIN9vFwOqvQ"><img src="http://feeds.feedburner.com/~ff/RafaelS?i=gr4Y-R6_EqE:YfO3ewhqdfw:gIN9vFwOqvQ" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=gr4Y-R6_EqE:YfO3ewhqdfw:I97M6haO00k"><img src="http://feeds.feedburner.com/~ff/RafaelS?d=I97M6haO00k" border="0"></img></a>
</div><img src="http://feeds.feedburner.com/~r/RafaelS/~4/gr4Y-R6_EqE" height="1" width="1" alt=""/>Rafael Salashttps://plus.google.com/116687955206452017583noreply@blogger.com0http://www.rafael-salas.com/2013/09/pass-summit-first-timer-questions.htmltag:blogger.com,1999:blog-29438013.post-16161964499730302742013-09-16T11:00:00.000-04:002013-09-16T11:00:00.406-04:00PASS Summit and SQL Saturday Combo!&nbsp;<a href="http://1.bp.blogspot.com/-0vsHss39r0I/UjSuldZWTfI/AAAAAAAAAmc/B7TwVlGFb2A/s1600/Summit2013.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="http://1.bp.blogspot.com/-0vsHss39r0I/UjSuldZWTfI/AAAAAAAAAmc/B7TwVlGFb2A/s1600/Summit2013.png" height="100" width="320" /></a><br /><a href="http://www.sqlsaturday.com/237/eventhome.aspx" style="clear: right; float: right; margin-bottom: 1em; margin-left: 1em;" target="_blank"><img alt="sqlsat237_web" border="0" src="http://lh5.ggpht.com/-Ifjn95vFJSE/UjSn05NtLzI/AAAAAAAAAlw/wKqb2lsYY4c/sqlsat237_web_thumb%25255B2%25255D.png?imgmax=800" height="96" style="background-image: none; border-width: 0px; display: inline; margin: 0px 0px 0px 8px; padding-left: 0px; padding-right: 0px; padding-top: 0px;" title="sqlsat237_web" width="200" /></a><br /><br /><br /><br /><br /><br /><br />There are a lot of things that are better when enjoyed together and we all have our favorites, such as eggs &amp; bacon, beach sand &amp; bare feet, cookies &amp; milk and of course <strong><a href="http://www.sqlpass.org/summit/2013/" target="_blank">PASS Summit</a> &amp;&nbsp;<a href="http://www.sqlsaturday.com/default.aspx" target="_blank">SQL Saturday</a>.</strong><br /><br />This year, Charlotte, NC will be hosting PASS Summit and a SQL Saturday. This means you get the opportunity to attend 2 days of pre-conferences and 3 days loaded with more than 190 sessions that PASS Summit offers, and a whole free extra day of learning and sharing offered by <a href="http://www.sqlsaturday.com/237/eventhome.aspx" target="_blank">SQL Saturday Charlotte</a> all in the same week and city!<br /><h2>Have you register yet?</h2>The registrations to both events are still open, and if you are still wondering if wondering if they are worth it, you should know that more than <a href="http://www.sqlpass.org/summit/2013/About.aspx" target="_blank">5,000 people from more than 50 countries think they are</a>.<br />PASS Summit: <a href="http://www.sqlpass.org/RegisterforSQLPASS.aspx" target="_blank" title="http://www.sqlpass.org/RegisterforSQLPASS.aspx">http://www.sqlpass.org/RegisterforSQLPASS.aspx</a> – $200 off with <em><strong>SUM223CH </strong>code!</em><br />SQL Saturday Charlotte: <a href="http://www.sqlsaturday.com/237/register.aspx" target="_blank" title="http://www.sqlsaturday.com/237/register.aspx">http://www.sqlsaturday.com/237/register.aspx</a><br /><br />﻿﻿﻿<div class="separator" style="clear: both; text-align: center;"><a href="http://www.sqlpass.org/summit/2013/Sessions/SessionDetails.aspx?sid=4549" target="_blank"><img border="0" src="http://2.bp.blogspot.com/-TfSbp1uWCZc/UjSt4JMqt0I/AAAAAAAAAmU/HO7Y4zjOkag/s1600/PASS_2013_SpeakingButton_180x180.png" /></a></div><div class="separator" style="clear: both; text-align: center;">&nbsp;</div><div class="feedflare">
<a href="http://feeds.feedburner.com/~ff/RafaelS?a=NR0fdtswY9Y:i9jV6ckRSGs:yIl2AUoC8zA"><img src="http://feeds.feedburner.com/~ff/RafaelS?d=yIl2AUoC8zA" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=NR0fdtswY9Y:i9jV6ckRSGs:63t7Ie-LG7Y"><img src="http://feeds.feedburner.com/~ff/RafaelS?d=63t7Ie-LG7Y" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=NR0fdtswY9Y:i9jV6ckRSGs:-BTjWOF_DHI"><img src="http://feeds.feedburner.com/~ff/RafaelS?i=NR0fdtswY9Y:i9jV6ckRSGs:-BTjWOF_DHI" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=NR0fdtswY9Y:i9jV6ckRSGs:V_sGLiPBpWU"><img src="http://feeds.feedburner.com/~ff/RafaelS?i=NR0fdtswY9Y:i9jV6ckRSGs:V_sGLiPBpWU" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=NR0fdtswY9Y:i9jV6ckRSGs:gIN9vFwOqvQ"><img src="http://feeds.feedburner.com/~ff/RafaelS?i=NR0fdtswY9Y:i9jV6ckRSGs:gIN9vFwOqvQ" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=NR0fdtswY9Y:i9jV6ckRSGs:I97M6haO00k"><img src="http://feeds.feedburner.com/~ff/RafaelS?d=I97M6haO00k" border="0"></img></a>
</div><img src="http://feeds.feedburner.com/~r/RafaelS/~4/NR0fdtswY9Y" height="1" width="1" alt=""/>Rafael Salashttps://plus.google.com/116687955206452017583noreply@blogger.com0http://www.rafael-salas.com/2013/09/pass-summit-and-sql-saturday-combo.htmltag:blogger.com,1999:blog-29438013.post-59347499212797219182013-09-04T11:20:00.000-04:002013-09-04T22:23:45.167-04:00Power Query: Stand Alone Vs. Corporate versionOne thing that caught me off guard as I was trying to use Power Query against Power BI for Office 365 was the fact that there are 2 versions of Power Query. This actually seem to have <a href="http://social.technet.microsoft.com/Forums/en-US/bd9173ac-b18a-481c-a6e1-4f4189aaf85f/no-enterpise-options" target="_blank">confused at least one other person already</a>, so here is some information in case you face the same situation:<a href="http://lh3.ggpht.com/-UVtV0Nu_nh0/UifqdXoSDmI/AAAAAAAAAlA/y-j-rAfyI70/s1600-h/twins%25255B6%25255D.jpg"><img align="right" alt="twins" border="0" src="http://lh4.ggpht.com/-CQoMN8oJnJQ/Uifqd3kh93I/AAAAAAAAAlI/ykCrP3VEooY/twins_thumb%25255B4%25255D.jpg?imgmax=800" height="225" style="background-image: none; border-width: 0px; display: inline; float: right; margin: 2px 0px 0px 14px; padding-left: 0px; padding-right: 0px; padding-top: 0px;" title="twins" width="319" /></a><br />Microsoft Power Query for Excel - version 1.5.3296.2082<br /><a href="http://www.microsoft.com/en-us/download/details.aspx?id=39379">http://www.microsoft.com/en-us/download/details.aspx?id=39379</a> <br />&nbsp; Microsoft Power Query <strong>Preview -</strong> version 2.6.3387.121<br /><a href="http://www.microsoft.com/en-us/download/details.aspx?id=39933">http://www.microsoft.com/en-us/download/details.aspx?id=39933</a><br /><h3>How are they different? </h3>Version 1.x is the first release of Power Query the one that <a href="http://blogs.msdn.com/b/dataexplorer/archive/2013/07/06/quot-data-explorer-quot-is-now-microsoft-power-query-for-excel.aspx" target="_blank">reached General availability</a> on July 8 2013. This means, among other things, it is safe to run it in production environments. This is also the “standalone” version meaning it does not have the options to connect and share Queries via <a href="http://office.microsoft.com/en-us/excel/power-bi-FX104080667.aspx" target="_blank">Power BI for Office 365</a> Version 2.x is&nbsp; the the so called “corporate” which means has the options to interact with Office 365 Power BI sites. It is important to note that as of this date, this version is in the&nbsp; PREVIEW stage which is some sort of beta period where features and functionality are still being finalized&nbsp; and its use in production environments is not recommended. &nbsp; So, if you are trying to use Power Query for Excel against Power BI for Office 365 make sure you have the 2.x version! <br /><br /><br /><br /><br /><div class="feedflare">
<a href="http://feeds.feedburner.com/~ff/RafaelS?a=vr2Rp-5xos0:LNmGDjNDoCY:yIl2AUoC8zA"><img src="http://feeds.feedburner.com/~ff/RafaelS?d=yIl2AUoC8zA" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=vr2Rp-5xos0:LNmGDjNDoCY:63t7Ie-LG7Y"><img src="http://feeds.feedburner.com/~ff/RafaelS?d=63t7Ie-LG7Y" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=vr2Rp-5xos0:LNmGDjNDoCY:-BTjWOF_DHI"><img src="http://feeds.feedburner.com/~ff/RafaelS?i=vr2Rp-5xos0:LNmGDjNDoCY:-BTjWOF_DHI" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=vr2Rp-5xos0:LNmGDjNDoCY:V_sGLiPBpWU"><img src="http://feeds.feedburner.com/~ff/RafaelS?i=vr2Rp-5xos0:LNmGDjNDoCY:V_sGLiPBpWU" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=vr2Rp-5xos0:LNmGDjNDoCY:gIN9vFwOqvQ"><img src="http://feeds.feedburner.com/~ff/RafaelS?i=vr2Rp-5xos0:LNmGDjNDoCY:gIN9vFwOqvQ" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=vr2Rp-5xos0:LNmGDjNDoCY:I97M6haO00k"><img src="http://feeds.feedburner.com/~ff/RafaelS?d=I97M6haO00k" border="0"></img></a>
</div><img src="http://feeds.feedburner.com/~r/RafaelS/~4/vr2Rp-5xos0" height="1" width="1" alt=""/>Rafael Salashttps://plus.google.com/116687955206452017583noreply@blogger.com0http://www.rafael-salas.com/2013/09/power-query-stand-alone-vs-corporate.htmltag:blogger.com,1999:blog-29438013.post-85587941919895192142013-09-03T23:18:00.001-04:002013-09-14T20:46:13.519-04:00Power BI for Office 365: Installing Power BI App from SharePoint StoreI got my invitation email to the preview of Power BI for Office 365 few days ago and while the provisioning process was pretty straight forward, I run into a minor issue that needed few extra steps that were not covered in the <a href="http://click.email.office.com/?qs=e703c248dec48b2c8546f27b9e6a73490db56894bf152d84c1f5347ab63b111fef16b40f3585ab6f"><u>Power BI Provisioning Video</u></a>. In my case, I did not have Power BI as one of the available apps of the team site I wanted to use as shown in the video (~3:45 in video). Fortunately, there is a <a href="http://office.microsoft.com/en-us/excel/redir/XT104103232.aspx" target="_blank">provisioning guide</a> document that provide the steps to manually install Power BI app from the SharePoint store.<br /><a href="http://lh6.ggpht.com/-ac25IkLWO80/UiameT1-_yI/AAAAAAAAAks/vocfOqBlnwE/s1600-h/image%25255B6%25255D.png"><img align="right" alt="image" border="0" src="http://lh5.ggpht.com/-7mTrhs3ixY8/Uiame5G_dkI/AAAAAAAAAk0/XzBum9xeOQY/image_thumb%25255B4%25255D.png?imgmax=800" height="171" style="background-image: none; border: 0px currentColor; display: inline; float: right; margin: 7px 53px 0px 29px; padding-left: 0px; padding-right: 0px; padding-top: 0px;" title="image" width="320" /></a><br />Thanks to <a href="http://social.technet.microsoft.com/profile/gilad%20elyashar/?ws=usercard-mini">Gilad Elyashar</a>&nbsp; for pointing me in the right direction to get this issue solved by replying to <a href="http://social.technet.microsoft.com/Forums/en-US/d5d353df-d43a-4ddc-8124-552d303699f8/no-power-bi-under-site-contents?prof=required" target="_blank">my post</a> in the power BI forum. If you run into this or any other issue, I encourage you to make use of the <a href="http://social.technet.microsoft.com/Forums/en-US/home?category=powerbi&amp;filter=alltypes&amp;sort=lastpostdesc" target="_blank">Power BI forums</a> to get answer to your questions<div class="feedflare">
<a href="http://feeds.feedburner.com/~ff/RafaelS?a=fO6jc_G9UF4:fL6G4QHB58o:yIl2AUoC8zA"><img src="http://feeds.feedburner.com/~ff/RafaelS?d=yIl2AUoC8zA" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=fO6jc_G9UF4:fL6G4QHB58o:63t7Ie-LG7Y"><img src="http://feeds.feedburner.com/~ff/RafaelS?d=63t7Ie-LG7Y" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=fO6jc_G9UF4:fL6G4QHB58o:-BTjWOF_DHI"><img src="http://feeds.feedburner.com/~ff/RafaelS?i=fO6jc_G9UF4:fL6G4QHB58o:-BTjWOF_DHI" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=fO6jc_G9UF4:fL6G4QHB58o:V_sGLiPBpWU"><img src="http://feeds.feedburner.com/~ff/RafaelS?i=fO6jc_G9UF4:fL6G4QHB58o:V_sGLiPBpWU" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=fO6jc_G9UF4:fL6G4QHB58o:gIN9vFwOqvQ"><img src="http://feeds.feedburner.com/~ff/RafaelS?i=fO6jc_G9UF4:fL6G4QHB58o:gIN9vFwOqvQ" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=fO6jc_G9UF4:fL6G4QHB58o:I97M6haO00k"><img src="http://feeds.feedburner.com/~ff/RafaelS?d=I97M6haO00k" border="0"></img></a>
</div><img src="http://feeds.feedburner.com/~r/RafaelS/~4/fO6jc_G9UF4" height="1" width="1" alt=""/>Rafael Salashttps://plus.google.com/116687955206452017583noreply@blogger.com0http://www.rafael-salas.com/2013/09/power-bi-for-office-365-installing.htmltag:blogger.com,1999:blog-29438013.post-42649146668864775442013-08-12T09:15:00.004-04:002013-08-12T09:15:39.758-04:00Power Query: Creating a Function to Correct and and Verify Addresses via External APIOne of the features I found very helpful in <a href="http://www.rafael-salas.com/2013/03/data-explorer-for-excel-next-piece-of.html" target="_blank">Power Query for Excel</a> (former codename Data Explorer)is the ability to interact with external APIs as this opens the door to many interesting use cases. In this post, I want to show how we can use Melissa Data <a href="http://datamarket.azure.com/dataset/melissadata/addresscheck" target="_blank">Address Check - Verify, Correct, Geocode US and Canadian Addresses</a> service available in <a href="http://datamarket.azure.com/" target="_blank">Windows Azure Marketplace</a> to create a Power Query parameterized function that can be later referenced and re-used by other queries. This address cleansing service offers up to 1000 records per month for free, but you would have to pay a monthly fee beyond that point. Although the the terms functions and APIs may intimidate some of us, you will find that the Power Query makes the process rather simple, at least in this case.<br /><br /><strong>Before you start</strong><br />In order to complete the steps in this post you will need:<br /><ul><li>A <a href="http://datamarket.azure.com/" target="_blank">Windows Azure Marketplace</a> account (this is free)</li><li>An Active subscription to Melissa Data <a href="http://datamarket.azure.com/dataset/melissadata/addresscheck" target="_blank">Address Check - Verify, Correct, Geocode US and Canadian Addresses</a> – The free 1,000 records per month subscription works fine.</li><li>Understand and configure <a href="http://office.microsoft.com/en-us/excel-help/privacy-levels-HA104009800.aspx" target="_blank">Power Query privacy levels</a> so data sources can be combined. In this example I enable Fast Combined option</li></ul><h2>Getting familiar with the API</h2>We will start by spending some time on getting familiar with the service we want to use. In this case, the <a href="https://datamarket.azure.com/dataset/melissadata/addresscheck#schema" target="_blank">details page</a> of Melissa Data service we want to use has the basic information we need to get started: the <strong>Service root URL</strong> and <strong>Input Parameters</strong><br /><a href="http://lh5.ggpht.com/-Aph9Taga43U/UghpvKLIgnI/AAAAAAAAAhQ/NL1VREslGh0/s1600-h/image%25255B4%25255D.png"><img alt="image" border="0" height="292" src="http://lh5.ggpht.com/-sW_ZjHCodzw/UghpvWJnAYI/AAAAAAAAAhU/ZVJdTutagOQ/image_thumb%25255B2%25255D.png?imgmax=800" style="background-image: none; border-width: 0px; display: inline; margin: 9px 0px 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;" title="image" width="644" /></a><br /><br /><h2>Using the API within Power Query</h2>Power Query for Excel has many options hen it comes to source data (e.g. From Web, From Database, From Other sources, etc.) so it took me a little of guessing as to which option to use in order to get send and receive data to this API. It turns out that using <strong>From Odata Feed</strong> and entering the <strong>Service root URL</strong> does the trick.<br /><a href="http://lh6.ggpht.com/-Cv5SJ-tE_Tw/Ughpv7HavqI/AAAAAAAAAhc/jFwFw7pb_CM/s1600-h/image%25255B8%25255D.png"><img alt="image" border="0" height="231" src="http://lh3.ggpht.com/-BzBiwVrLkgI/UghpwJIGIFI/AAAAAAAAAho/raBTmgJnnxE/image_thumb%25255B4%25255D.png?imgmax=800" style="background-image: none; border: 0px currentColor; display: inline; margin: 9px 0px 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;" title="image" width="644" /></a><br /><br />Upon clicking OK, we notice that Power Query detects the <strong>SuggestedAddresses</strong> function exposed by the API, and provide us with an <strong>Invoke</strong> option within the Query editor.<br /><a href="http://lh4.ggpht.com/-FXetSVkah3s/UghpwfTCMhI/AAAAAAAAAhw/hn4fJHDLj9o/s1600-h/image%25255B12%25255D.png"><img alt="image" border="0" height="139" src="http://lh5.ggpht.com/-FhSVT7UxXic/Ughpxrmqg-I/AAAAAAAAAh4/OQXc6lO57vg/image_thumb%25255B6%25255D.png?imgmax=800" style="background-image: none; border: 0px currentColor; display: inline; margin: 9px 0px 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;" title="image" width="644" /></a><br /><br />Let’s go ahead and use the <strong>Invoke</strong> button. You will get a new dialog prompting you for the input parameters. As we saw earlier, the services requires an address value, the maximum numbers of suggested address – this is in case the service has multiple suggested address – and the minimal level of confidence – a value from 0 to 1, representing the level of confidence of the service that the cleansing results are accurate. Let’s go ahead and enter some values to test it.<br /><a href="http://lh3.ggpht.com/-0d5tXnIf6Ac/UghpyJ6yF9I/AAAAAAAAAiA/UvaO7Y3fl8c/s1600-h/image%25255B21%25255D.png"><img alt="image" border="0" height="228" src="http://lh5.ggpht.com/-uWYwVaU0_g8/UghpydPLsSI/AAAAAAAAAiI/unbo-AFOKcQ/image_thumb%25255B11%25255D.png?imgmax=800" style="background-image: none; border: 0px currentColor; display: inline; margin: 9px 0px 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;" title="image" width="644" /></a><br /><br />As you can see, the results returned by the API are nicely displayed in a tabular format inside of the preview pane of the query editor, ad if you click done, the results are place in an Excel table, where they can be easily refreshed and further combined with other data sets. Quite easy!<br /><a href="http://lh4.ggpht.com/-YtEbG8Q8gMo/Ughpy5GYBYI/AAAAAAAAAiQ/pXvz6zw9dwg/s1600-h/image%25255B25%25255D.png"><img alt="image" border="0" height="123" src="http://lh4.ggpht.com/-tMdjA7-W4MM/UghpzYxqZcI/AAAAAAAAAiU/GxgEi8UMVEU/image_thumb%25255B13%25255D.png?imgmax=800" style="background-image: none; border: 0px currentColor; display: inline; margin: 9px 0px 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;" title="image" width="644" /></a><br /><br />But, do you see a caveat here? so far, we just were able to call the Service API for a single address value that we have to type in the <strong>invoke function</strong> dialog, which won't work if we have to clean 100’s or perhaps thousands of addresses at the time. So, how could we invoke this function for each record in let’s say, another data set?<br /><h2>Invoking a function for each record in a table</h2>First, let’s revisit the function query we just created to remove the <strong>InvokeSuggestedAddresses </strong>step and rename the query so we give it a more friendlier name.This should leave our function ready to be invoked from other queries.<br /><a href="http://lh4.ggpht.com/-IWDU7sFlMVE/UghpzgHxuvI/AAAAAAAAAic/Xa3hA5liLrE/s1600-h/image%25255B36%25255D.png"><img alt="image" border="0" height="121" src="http://lh4.ggpht.com/-jyQAZ7DmZxo/Ughpz9MgMqI/AAAAAAAAAik/V26zdDKOEFA/image_thumb%25255B18%25255D.png?imgmax=800" style="background-image: none; border: 0px currentColor; display: inline; margin: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;" title="image" width="644" /></a><br /><br />Now, let’s suppose we have an Excel table with multiple address records that we want to send to Melissa Data service for cleansing and validation. In this case, we will create a new Power Query using the<strong> From Table</strong> option as shown below.<br /><a href="http://lh5.ggpht.com/-pyiC3I1ZuAw/Ughp0S7n61I/AAAAAAAAAiw/VrvGPe7hQlo/s1600-h/image%25255B40%25255D.png"><img alt="image" border="0" height="410" src="http://lh5.ggpht.com/-dkOyt3uTtaw/Ughp0jm6x3I/AAAAAAAAAi4/w4zchtlspfg/image_thumb%25255B20%25255D.png?imgmax=800" style="background-image: none; border: 0px currentColor; display: inline; margin: 9px 0px 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;" title="image" width="644" /></a><br /><br />When the query editor opens, select the column containing the address to be cleaned, in our case the address column, and right-click. Then select the <strong>Insert Column --&gt;&nbsp;Custom</strong> option. Once the <strong>Insert Custom Column</strong> dialog opens, enter the following expression to invoke the function we just created<strong>.</strong> Click Ok.<br /><br /><span style="background-color: #a5a5a5; font-family: Courier New;">fCleanAddress([Address],1,0)</span><br /><span style="background-color: #a5a5a5; font-family: Courier New;"></span><br /><span style="background-color: white;">As you can see, all we are doing is invoking fCleanAddress function and passing the 3 required parameters. The address parameter will be given by the Address column available in the data set – That’s the trick! MaximumSuggestions and MinimumConfidence are&nbsp;hardcoded to 1 and 0 respectively - You can change that if you want, or read those values from your datset.</span><br /><br /><a href="http://lh4.ggpht.com/-VRnOpHCi6B0/Ughp1ASP5-I/AAAAAAAAAjA/Ybg5GMtHdxM/s1600-h/image%25255B44%25255D.png"><img alt="image" border="0" height="225" src="http://lh6.ggpht.com/-TebJi3PT4Ts/Ughp1ulbJHI/AAAAAAAAAjI/ynQOG3LkXjE/image_thumb%25255B22%25255D.png?imgmax=800" style="background-image: none; border: 0px currentColor; display: inline; margin: 9px 0px 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;" title="image" width="644" /></a><br /><br />You should see now a new column called <strong>custom</strong> (feel free to rename it) and the work Table as it values. This means the results in the new column need to be further expanded in order to be able to see the detailed results from each call to Melissa Data API.<br /><a href="http://lh6.ggpht.com/-HyeYSmG92TM/Ughp2OpRewI/AAAAAAAAAjQ/nj4C2rK9xao/s1600-h/image%25255B51%25255D.png"><img alt="image" border="0" height="158" src="http://lh3.ggpht.com/-K6yKq4gD0ko/Ughp2YdZuGI/AAAAAAAAAjY/KZDC9crIMws/image_thumb%25255B25%25255D.png?imgmax=800" style="background-image: none; border: 0px currentColor; display: inline; padding-left: 0px; padding-right: 0px; padding-top: 0px;" title="image" width="644" /></a><br /><br />You can expand the <strong>custom</strong> column fields by clicking in the double arrow icon next to the column name. At this point you are presented with all available fields and have the ability to select/unselect the desired fields.<br /><a href="http://lh4.ggpht.com/-Hsq-jozW_iI/Ughp24ZIYPI/AAAAAAAAAjg/4ykJCxT4HA4/s1600-h/image%25255B55%25255D.png"><img alt="image" border="0" height="366" src="http://lh5.ggpht.com/-lOset4FC58U/Ughp3BXU_VI/AAAAAAAAAjo/9xmRbUnoKmk/image_thumb%25255B27%25255D.png?imgmax=800" style="background-image: none; border: 0px currentColor; display: inline; margin: 9px 0px 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;" title="image" width="644" /></a><br />Once you are happy with the Selection of fields, click OK and then Done to get the query results in an excel table<br /><div class="separator" style="clear: both; text-align: center;"><a href="http://2.bp.blogspot.com/-hfbP54Eh7os/UgjfeUszi_I/AAAAAAAAAkM/hzPwpGESk1w/s1600/8-12-2013+12-31-42+AM.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="105" src="http://2.bp.blogspot.com/-hfbP54Eh7os/UgjfeUszi_I/AAAAAAAAAkM/hzPwpGESk1w/s320/8-12-2013+12-31-42+AM.png" width="320" /></a></div><br /><h2>Taking it further</h2>The example above, uses an static Excel table as the starting point, but you can use this same approach to invoke the function and get API results for records coming from other types of sources that are more dynamic in nature such as queries <strong>From Web</strong> or <strong>From Database.</strong> by the way, I would not expect this type of solution to have split of a second response times as in this case data goes to the internet and back and we have no control over the API layer. <br /><br /><div class="MsoNormal" style="margin: 0in 0in 8pt 0.25in;"><span style="font-family: Calibri;">Preview version warning – the information in this post is current for July 2013 Power Query preview (Version: 1.5.3296.1161)&nbsp;&nbsp;and it may no be subject to change for RTM version.<o:p></o:p></span></div><div class="feedflare">
<a href="http://feeds.feedburner.com/~ff/RafaelS?a=_EDwuLxZIQM:L1NaS1a8rDU:yIl2AUoC8zA"><img src="http://feeds.feedburner.com/~ff/RafaelS?d=yIl2AUoC8zA" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=_EDwuLxZIQM:L1NaS1a8rDU:63t7Ie-LG7Y"><img src="http://feeds.feedburner.com/~ff/RafaelS?d=63t7Ie-LG7Y" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=_EDwuLxZIQM:L1NaS1a8rDU:-BTjWOF_DHI"><img src="http://feeds.feedburner.com/~ff/RafaelS?i=_EDwuLxZIQM:L1NaS1a8rDU:-BTjWOF_DHI" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=_EDwuLxZIQM:L1NaS1a8rDU:V_sGLiPBpWU"><img src="http://feeds.feedburner.com/~ff/RafaelS?i=_EDwuLxZIQM:L1NaS1a8rDU:V_sGLiPBpWU" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=_EDwuLxZIQM:L1NaS1a8rDU:gIN9vFwOqvQ"><img src="http://feeds.feedburner.com/~ff/RafaelS?i=_EDwuLxZIQM:L1NaS1a8rDU:gIN9vFwOqvQ" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=_EDwuLxZIQM:L1NaS1a8rDU:I97M6haO00k"><img src="http://feeds.feedburner.com/~ff/RafaelS?d=I97M6haO00k" border="0"></img></a>
</div><img src="http://feeds.feedburner.com/~r/RafaelS/~4/_EDwuLxZIQM" height="1" width="1" alt=""/>Rafael Salashttps://plus.google.com/116687955206452017583noreply@blogger.com0http://www.rafael-salas.com/2013/08/power-query-creating-function-to.htmltag:blogger.com,1999:blog-29438013.post-18591458410749115772013-03-25T12:09:00.001-04:002013-03-25T12:16:21.179-04:00Why I am attending PASS Business Analytics Conference in Chicago<a href="http://passbaconference.com/" target="_blank">PASS BA conference</a>&nbsp; is less than 3 weeks away and if you are still pondering whether to attend, let me share with you few good reasons – my reasons of course– to attend. <br /><ol><a href="http://passbaconference.com/Portals/301/images/PASS_BAC_I'm%20Attending_Banner.png" imageanchor="1" style="clear: right; float: right; margin-bottom: 1em; margin-left: 1em;"><img alt="" border="0" height="250" src="http://passbaconference.com/Portals/301/images/PASS_BAC_I'm Attending_Banner.png" style="margin-top: 0px;" width="250" /></a><li>Catching up. The last couple of years&nbsp; Analytics and BI landscape has experimented dramatic changes as new technologies make their way in and the heavy buzzword marketing machinery from vendors makes harder to separate the wheat from the shaft. You can use this conference to see if <a href="http://passbaconference.com/Sessions/SessionDetails.aspx?sid=4115#.UVBipbbD-Co" target="_blank">Big Data</a> is really for you, or get your head around <a href="http://passbaconference.com/Sessions/BuildSchedule.aspx" target="_blank">Advanced data analytics</a> and perhaps to find out if <a href="http://passbaconference.com/Sessions/SessionDetails.aspx?sid=4063#.UVBd7bbD-Co" target="_blank">Data Science</a> is real or just marketing fiction.</li><li>Agenda &amp; Speakers. The lineup of speakers is great and topics are well balanced across 5 different tracks that promises to deliver first-hand real world experiences: Big data, Advanced Analytics, Data Visualization, Information delivery and collaboration &amp; strategy and Architecture.</li><li>Networking. Yes, the usual but important part we tend to miss when we go to conferences. Being around 1000s of professionals with similar challenges and interests is a unique opportunity to get connected and learn how others are doing it.</li></ol>I will be one of PASS networking ambassadors on Thursday evening event and be hanging out at either Experience Lounge or the Microsoft Kiosk, so if you decide to attend, &nbsp;please make sure to stop by and say hi.<br /><br />This is a 2 days conference plus a pre-conference day and <a href="http://passbaconference.com/Register.aspx#.UVB2trbD-Co" target="_blank">registrations are still open</a> and you can get&nbsp; $200 off the registration fee by using&nbsp; code BAC945MVP. <div class="feedflare">
<a href="http://feeds.feedburner.com/~ff/RafaelS?a=-iZVAsAIh0Y:m1u2blPSD6w:yIl2AUoC8zA"><img src="http://feeds.feedburner.com/~ff/RafaelS?d=yIl2AUoC8zA" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=-iZVAsAIh0Y:m1u2blPSD6w:63t7Ie-LG7Y"><img src="http://feeds.feedburner.com/~ff/RafaelS?d=63t7Ie-LG7Y" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=-iZVAsAIh0Y:m1u2blPSD6w:-BTjWOF_DHI"><img src="http://feeds.feedburner.com/~ff/RafaelS?i=-iZVAsAIh0Y:m1u2blPSD6w:-BTjWOF_DHI" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=-iZVAsAIh0Y:m1u2blPSD6w:V_sGLiPBpWU"><img src="http://feeds.feedburner.com/~ff/RafaelS?i=-iZVAsAIh0Y:m1u2blPSD6w:V_sGLiPBpWU" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=-iZVAsAIh0Y:m1u2blPSD6w:gIN9vFwOqvQ"><img src="http://feeds.feedburner.com/~ff/RafaelS?i=-iZVAsAIh0Y:m1u2blPSD6w:gIN9vFwOqvQ" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=-iZVAsAIh0Y:m1u2blPSD6w:I97M6haO00k"><img src="http://feeds.feedburner.com/~ff/RafaelS?d=I97M6haO00k" border="0"></img></a>
</div><img src="http://feeds.feedburner.com/~r/RafaelS/~4/-iZVAsAIh0Y" height="1" width="1" alt=""/>Rafael Salashttps://plus.google.com/116687955206452017583noreply@blogger.com0http://www.rafael-salas.com/2013/03/why-am-i-attending-pass-business.htmltag:blogger.com,1999:blog-29438013.post-29178880239804519422013-03-03T09:56:00.001-05:002013-03-03T16:43:46.914-05:00Honor to be part of SQL Saturday Richmond and SQL Saturday Jacksonville 2013<br />Next Friday, I will be heading out to Richmond,VA to attend and present at <a href="http://www.sqlsaturday.com/187/eventhome.aspx" target="_blank">SLQ Saturday #187</a>. This is the second time SQLSaturday is coming to Richmond and my first time going there so I am very excited about joining the party. With a <a href="http://www.sqlsaturday.com/187/schedule.aspx">2 full tracks packed with BI content</a>, there is<a href="http://www.sqlsaturday.com/default.aspx"><img align="right" alt="SQLSaturday Home" height="123" src="http://www.sqlsaturday.com/images/header_logo.png" style="display: inline; float: right; margin: 12px 0px 0px 23px;" width="351" /></a> plenty of good sessions and speakers to choose from. You can register at the <a href="http://www.sqlsaturday.com/187/register.aspx">event page</a> (note: the event seems sold out now, but you can still get in the wait list) Then in April 26th, I will be in Jacksonville, FL for <a href="http://www.sqlsaturday.com/215/eventhome.aspx" target="_blank">SQLSaturday #215</a>. Jacksonville has hosted a SQL Saturday from the early days SQL Saturday&nbsp; – They actually hosted <a href="http://www.sqlsaturday.com/3/eventhome.aspx">SQLSaturday&nbsp; #3</a>, so there is a great of experience among the organizing team I am sure! There are 3 full tracks of BI at this event, so if you are trying to get started on BI or get up to speed with the latest tools be sure to attend. It will be fun I promise. Register for this event at <a href="http://www.sqlsaturday.com/215/register.aspx">http://www.sqlsaturday.com/215/register.aspx</a> In both events, I will talk about SQL Server Data Quality services, a new set of tools in SQL Server 2012 that helps you addressing data cleansing and data duplication issues at an enterprise-level. I delivered this same presentation at the PASS Summit last year and got great feedback – so , catch it if you can. &nbsp;<a href="http://www.sqlsaturday.com/default.aspx" target="_blank">About SQLSaturday</a>: <em>PASS SQLSaturday’s are free 1-day training events for SQL Server professionals that focus on local speakers, providing a variety of high-quality technical sessions, and making it all happen through the efforts of volunteers. Whether &nbsp;you're attending a SQLSaturday or thinking about hosting your own, we think you'll find it's a great way to spend a Saturday – or any day.</em><br /><br /><br /><br /><br /><div class="feedflare">
<a href="http://feeds.feedburner.com/~ff/RafaelS?a=zmBiW01eDC4:Gb6IGSWV9Bw:yIl2AUoC8zA"><img src="http://feeds.feedburner.com/~ff/RafaelS?d=yIl2AUoC8zA" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=zmBiW01eDC4:Gb6IGSWV9Bw:63t7Ie-LG7Y"><img src="http://feeds.feedburner.com/~ff/RafaelS?d=63t7Ie-LG7Y" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=zmBiW01eDC4:Gb6IGSWV9Bw:-BTjWOF_DHI"><img src="http://feeds.feedburner.com/~ff/RafaelS?i=zmBiW01eDC4:Gb6IGSWV9Bw:-BTjWOF_DHI" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=zmBiW01eDC4:Gb6IGSWV9Bw:V_sGLiPBpWU"><img src="http://feeds.feedburner.com/~ff/RafaelS?i=zmBiW01eDC4:Gb6IGSWV9Bw:V_sGLiPBpWU" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=zmBiW01eDC4:Gb6IGSWV9Bw:gIN9vFwOqvQ"><img src="http://feeds.feedburner.com/~ff/RafaelS?i=zmBiW01eDC4:Gb6IGSWV9Bw:gIN9vFwOqvQ" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=zmBiW01eDC4:Gb6IGSWV9Bw:I97M6haO00k"><img src="http://feeds.feedburner.com/~ff/RafaelS?d=I97M6haO00k" border="0"></img></a>
</div><img src="http://feeds.feedburner.com/~r/RafaelS/~4/zmBiW01eDC4" height="1" width="1" alt=""/>Rafael Salashttps://plus.google.com/116687955206452017583noreply@blogger.com1http://www.rafael-salas.com/2013/03/honor-to-be-at-sql-saturday-richmond.htmltag:blogger.com,1999:blog-29438013.post-12111428722918564722013-03-01T00:48:00.003-05:002013-08-12T01:01:22.286-04:00Power Query for Excel: The Next Piece Of The Self-Service PuzzleMicrosoft released the preview of Power Query <strike>Data Explorer</strike> for Excel, a new add-in for Excel 2010 and 2013 that promises to bring powerful capabilities&nbsp;for searching, transforming, shaping and merging data to excel users.&nbsp;A clear sign that Microsoft’ strategy to democratize BI is still in full swing and perhaps aligned&nbsp;with industry demands for <a href="http://blogs.msdn.com/b/microsoft_business_intelligence1/archive/2012/07/18/microsoft-named-a-self-service-business-intelligence-leader-by-forrester-research.aspx" target="_blank">Business Intelligence self-service platforms</a>. <br /><br />From <a href="http://www.microsoft.com/en-us/download/details.aspx?id=36803" target="_blank">Data Explorer preview download</a> page:<br /><strong><em>Microsoft “Data Explorer” Preview for Excel is a new add-in that provides a seamless experience for <u>data discovery, </u></em></strong><br /><strong><em><u>data transformation and enrichment</u> for Information Workers, BI professionals and other users</em>.</strong><br /><br /><h1>Do you mean&nbsp; Self –Service ETL?</h1>Maybe, but in any case Data Explorer sounds a very exciting proposal given that data integration is the biggest bottle neck most BI initiatives experience, and where more often users must rely on technologies and skillset accessible only via IT departments.<br /><h1>Data Explorer First Glance</h1>Once it is installed, Data Explorer manifest as new tab in the Excel ribbon.<br /><a href="http://lh4.ggpht.com/-lOmlQvQCNKQ/UTA93mr1ZAI/AAAAAAAAAfE/TW1R_aYeRC0/s1600-h/image%25255B5%25255D.png"><img alt="image" border="0" height="90" src="http://lh3.ggpht.com/-mFw_7O3gdq0/UTA935vbo4I/AAAAAAAAAfM/suKhiA4QNLQ/image_thumb%25255B3%25255D.png?imgmax=800" style="background-image: none; border: 0px currentColor; display: inline; padding-left: 0px; padding-right: 0px; padding-top: 0px;" title="image" width="640" /></a><br /><br />From there you can do:<br /><h3>Online Search</h3>Search for data sources on the web – at the moment all results seemed constraint to Wikipedia pages<br /><div class="separator" style="clear: both; text-align: center;"><a href="http://lh6.ggpht.com/-Q5aNV0Wco9M/UTA94O4dnWI/AAAAAAAAAfU/bTGHxTpupN0/s1600-h/SNAGHTML1ec4666%25255B3%25255D.png" style="margin-left: 1em; margin-right: 1em;"><img alt="SNAGHTML1ec4666" border="0" height="227" src="http://lh4.ggpht.com/-lY8rXwW79d0/UTA94m4ZCzI/AAAAAAAAAfc/bZpC1IgXPjo/SNAGHTML1ec4666_thumb.png?imgmax=800" style="background-image: none; border: 0px currentColor; display: block; float: none; margin-left: auto; margin-right: auto; padding-left: 0px; padding-right: 0px; padding-top: 0px;" title="SNAGHTML1ec4666" width="640" /></a></div><h3>&nbsp;</h3><h3>Get External Data</h3>These options provides the ability to connect and import data from a number of sources. A query is the unit of work when getting external data – a query contains 1 or more steps<br /><ul> <div class="separator" style="clear: both; text-align: center;"><a href="http://lh4.ggpht.com/-8-7m2xZyH1c/UTA943o4EwI/AAAAAAAAAfk/ia70RlRbdsk/s1600-h/SNAGHTML1fabbc1%25255B3%25255D.png" style="margin-left: 1em; margin-right: 1em;"><img align="right" alt="SNAGHTML1fabbc1" border="0" height="136" src="http://lh4.ggpht.com/-o747kLfJ2ns/UTA95eo2CtI/AAAAAAAAAfs/kVeoLS7GwYA/SNAGHTML1fabbc1_thumb.png?imgmax=800" style="background-image: none; border: 0px currentColor; display: inline; float: right; padding-left: 0px; padding-right: 0px; padding-top: 0px;" title="SNAGHTML1fabbc1" width="400" /></a></div><ul><li>From Web: Once you provide a URL, it allows you to import data from a webpage in tabular format. I was able to easily obtain data from quite a few pages (e.g <a href="http://zipatlas.com/us/zip-code-comparison/median-household-income.htm" target="_blank">Zip Codes with the Highest Median Household Income in the United States</a>), although I imagine there are constraints as how the data is layout in the page for data explorer to work.</li><li>From File: quite a few options to load data from files in different formats, including XML and Excel.</li><li>From Database: a predefined list of major relational database types. I found some major players missing from the list such as Sybase and Netezza – I would imagine support for additional providers will be added later on.</li><li>From Other Sources: several interesting options here such as SharePoint lists, <a href="http://www.odata.org/" target="_blank">Odata</a> and <a href="http://hadoop.apache.org/docs/stable/hdfs_user_guide.html" target="_blank">HDFS</a></li></ul></ul><br /><a href="http://lh4.ggpht.com/-f4XY2-cBxUI/UTA95n9ZNEI/AAAAAAAAAf0/vpvtBZi89Q0/s1600-h/SNAGHTML1fee6c5%25255B3%25255D.png"><img alt="SNAGHTML1fee6c5" border="0" height="97" src="http://lh4.ggpht.com/-IAQ-vhIYncs/UTA950gARNI/AAAAAAAAAf8/lZJ2n2ZAHck/SNAGHTML1fee6c5_thumb.png?imgmax=800" style="background-image: none; border: 0px currentColor; display: block; float: none; margin-left: auto; margin-right: auto; padding-left: 0px; padding-right: 0px; padding-top: 0px;" title="SNAGHTML1fee6c5" width="244" /></a><br />Notice that each time you use these options a new query is created and then you are given the opportunity to transform, manipulate and shape the data. What is even more powerful is that each action is recorded in the form of a ‘step’ that get replayed each time the query is refresh. Query steps are expressed via formulas that are based new set of functions. <a href="http://office.microsoft.com/en-us/excel-help/microsoft-codename-data-explorer-formula-reference-HA104003946.aspx?CTT=5&amp;origin=HA104003958" target="_blank">See “Data Explorer” formula reference</a> for more details.<br /><br /><h3>Combine Data</h3>Once data has been imported you can combine it.<br /><ul> <ul><li>Merge: Allows you to bring columns from 2 different queries together – similar to a&nbsp; JOIN in the SQL relational world.</li><li>Append: Creates a new query with all records of a first query followed by the rows of a second one – similar to what a UNION ALL does in the SQL relational world does.</li></ul></ul><br />I created a quick sample workbook that gets data from <a href="http://services.odata.org/Northwind/Northwind.svc/" target="_blank">Northwind Odata feed</a> and another external web page (top <a href="http://zipatlas.com/us/zip-code-comparison/median-household-income.htm" target="_blank">Zip Codes with the Highest Median Household Income in the United States</a>). It is not the most meaningful scenario but will let you get the general idea of how this stuff works. You can see a copy this sample here: <br /><br /><iframe frameborder="0" height="346" scrolling="no" src="https://skydrive.live.com/embed?cid=9E3064285B330F2E&amp;resid=9E3064285B330F2E%2128031&amp;authkey=AIrZbnVXi8eTaYU&amp;em=2" width="402"></iframe> <br /><h1>What’s next?</h1>You can download Data Explorer and start learning about it:<br /><a href="http://www.microsoft.com/en-us/download/details.aspx?id=36803" target="_blank">Microsoft "Data Explorer" Preview for Excel</a><br /><a href="http://office.microsoft.com/en-us/excel-help/start-page-HA104003813.aspx" target="_blank">Data Explorer Help</a><br /><a href="http://blogs.msdn.com/b/dataexplorer/">Microsoft "Data Explorer" Preview for Excel</a> blog<br /><h1>Summary</h1>Data Explorer could play a key role in Microsoft self-service BI platform&nbsp; as it reduces the friction typical BI users experience when searching, collecting and connecting source data. However, as a component of a major system it will be interesting to see what steps Microsoft will take. Few things come to mind<br /><ul><li>What would the “sharing” story be?</li><li>What would the progression/graduation from self-service to&nbsp; IT-managed solution looks like? Perhaps a Data Explorer to SSIS path – I am sure many of us will like that</li><li>Would we get a tighter integration with the other Excel “Powers” (PowerPivot &amp; PowerView)</li><li>Will it integrate with Data Quality and Master data Services?</li><li>Will IT be given tools that allows it to have&nbsp;the some sort of oversight ?</li></ul><br /><br /><ul></ul><div class="feedflare">
<a href="http://feeds.feedburner.com/~ff/RafaelS?a=Pk76aColFWc:0tlOAJHRM38:yIl2AUoC8zA"><img src="http://feeds.feedburner.com/~ff/RafaelS?d=yIl2AUoC8zA" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=Pk76aColFWc:0tlOAJHRM38:63t7Ie-LG7Y"><img src="http://feeds.feedburner.com/~ff/RafaelS?d=63t7Ie-LG7Y" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=Pk76aColFWc:0tlOAJHRM38:-BTjWOF_DHI"><img src="http://feeds.feedburner.com/~ff/RafaelS?i=Pk76aColFWc:0tlOAJHRM38:-BTjWOF_DHI" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=Pk76aColFWc:0tlOAJHRM38:V_sGLiPBpWU"><img src="http://feeds.feedburner.com/~ff/RafaelS?i=Pk76aColFWc:0tlOAJHRM38:V_sGLiPBpWU" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=Pk76aColFWc:0tlOAJHRM38:gIN9vFwOqvQ"><img src="http://feeds.feedburner.com/~ff/RafaelS?i=Pk76aColFWc:0tlOAJHRM38:gIN9vFwOqvQ" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=Pk76aColFWc:0tlOAJHRM38:I97M6haO00k"><img src="http://feeds.feedburner.com/~ff/RafaelS?d=I97M6haO00k" border="0"></img></a>
</div><img src="http://feeds.feedburner.com/~r/RafaelS/~4/Pk76aColFWc" height="1" width="1" alt=""/>Rafael Salashttps://plus.google.com/116687955206452017583noreply@blogger.com0http://www.rafael-salas.com/2013/03/data-explorer-for-excel-next-piece-of.htmltag:blogger.com,1999:blog-29438013.post-82255616490666527822012-10-10T07:58:00.001-04:002012-10-10T07:59:02.321-04:00SQL Saturday 174 Charlotte BI Edition Countdown<p>SQL Saturday is coming to town on Oct. 27th loaded with BI goodness for everybody taste and needs.<img style="margin: 13px 0px 0px 15px; display: inline; float: right" align="right" src="http://www.sqlsaturday.com/images/sqlsat174_web.png"> As you, know 2012 is new version year for SQL Server and this is a great opportunity to come and learn the new and the so-no-much new in Microsoft BI from top notch speakers (<a href="http://sqlsaturday.com/174/schedule.aspx" target="_blank">event schedule</a>). </p> <p>If you are new to SQL Saturdays, you should know this is 1 full day conference bringing high quality training and networking opportunities; and the price is about right: <strong>free</strong>.</p> <p>Registrations are still open, and I encourage to act now as we anticipate to go into waiting list in the next week or so (<a href="http://sqlsaturday.com/174/register.aspx" target="_blank">register here</a>)</p> <p>I hope to see you there!</p> <div class="feedflare">
<a href="http://feeds.feedburner.com/~ff/RafaelS?a=c6KDNWZFWUg:xihtDm9eOiQ:yIl2AUoC8zA"><img src="http://feeds.feedburner.com/~ff/RafaelS?d=yIl2AUoC8zA" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=c6KDNWZFWUg:xihtDm9eOiQ:63t7Ie-LG7Y"><img src="http://feeds.feedburner.com/~ff/RafaelS?d=63t7Ie-LG7Y" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=c6KDNWZFWUg:xihtDm9eOiQ:-BTjWOF_DHI"><img src="http://feeds.feedburner.com/~ff/RafaelS?i=c6KDNWZFWUg:xihtDm9eOiQ:-BTjWOF_DHI" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=c6KDNWZFWUg:xihtDm9eOiQ:V_sGLiPBpWU"><img src="http://feeds.feedburner.com/~ff/RafaelS?i=c6KDNWZFWUg:xihtDm9eOiQ:V_sGLiPBpWU" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=c6KDNWZFWUg:xihtDm9eOiQ:gIN9vFwOqvQ"><img src="http://feeds.feedburner.com/~ff/RafaelS?i=c6KDNWZFWUg:xihtDm9eOiQ:gIN9vFwOqvQ" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=c6KDNWZFWUg:xihtDm9eOiQ:I97M6haO00k"><img src="http://feeds.feedburner.com/~ff/RafaelS?d=I97M6haO00k" border="0"></img></a>
</div><img src="http://feeds.feedburner.com/~r/RafaelS/~4/c6KDNWZFWUg" height="1" width="1" alt=""/>Rafael Salashttps://plus.google.com/116687955206452017583noreply@blogger.com0http://www.rafael-salas.com/2012/10/sql-saturday-174-charlotte-bi-edition.htmltag:blogger.com,1999:blog-29438013.post-80331260891146474012012-08-13T09:00:00.000-04:002012-08-13T09:00:07.737-04:00SSIS 2012 and My Old ETL Framework<br />Few weeks ago, I was working on adapting certain pieces of an existing SSIS framework created with an earlier version of SSIS to make it work with SSIS 2012.<br />In this post, I want to share some of my findings and design notes around auditing-logging-re-cover ability of that ETL framework and how I got them to play along with SSIS 2012 and the project deployment model.<br /><h3>An old good practice: ETL execution logging</h3>It is a general good practice to generate and capture an ‘adequate’ level of execution logging information in your ETL applications or systems. The idea is that ETL developers and admins can use that information to determine if and when a processes has started, if and when it fails, and when it completes. From there, one can start doing more interesting things such as creating reports to monitor the execution of processes, develop auditing capabilities – e.g. what instance of a process touched what rows –, and even use this information to create a custom&nbsp; processes/package restart and recovery capabilities.<br />Almost every ETL framework I have seen and worked have functionality embedded, and it is typically implemented inside of the control flow of the packages using a pattern like this:<br /><div align="center"><strong>Log begging of process and generate ID&gt;&gt;&nbsp;&nbsp; process logic &gt;&gt; Log end of process</strong></div>Typically, this is done at the <em>master</em> package level and in many cases something similar is done inside of each <em>child</em> package. In every case, an unique ID is generated as soon as the main (or master) package starts. This ID is the logical unit of ETL process execution. Under my design,&nbsp; no ID is generated until the previous one has been marked as completed – even if the process is executed&nbsp; multiple times due to failures.<br />People use different names for this ID or unit of work:&nbsp; run_id, application_instance_id, ETL_control_id, etc. In this reincarnation of ETL framework I am working on these days, I call&nbsp; it ETLbatchID, and I use&nbsp; a table like the one in the picture below to store them and keep track of them<br /><div align="center"><a href="http://lh6.ggpht.com/-RZ8nSeAPzbw/UCh-dOd9g_I/AAAAAAAAAbk/1FR9NvkunZQ/s1600-h/image17.png"><img alt="image" border="0" height="348" src="http://lh5.ggpht.com/-w0cX_tAEWik/UCh-djMiL-I/AAAAAAAAAbs/05wJYtZxzJQ/image_thumb8%25255B1%25255D.png?imgmax=800" style="background-image: none; border-width: 0px; display: inline; padding-left: 0px; padding-right: 0px; padding-top: 0px;" title="image" width="286" /></a></div>As you see, This table not only gives me the ID, but also provides other important information such as start time, end time and the number of time the process was re-started.<br /><h3>But wait, do we still need this technique in SSIS 2012? I heard logging is automatic…</h3>In my case, the short answer is yes. See, SSIS 2012 brings a great deal of manageability and auditing improvements that automatically tracks and logs execution of packages. Each time we <span style="color: black;">run a package in the SSIS server, an execution_id is generated and logged in SSISDB – the SSIS catalog. It even goes as far as associating that execution_id to all executable – such as tasks, children packages - that ran under that package.Though this is great, it has its caveats. <span style="color: black;">One of the problems is that SSIS 2012 server issues a new execution_id each time you execute a package, so if the process fails and I restart the master package , I would get a second execution_id, hence having to track 2 different ID values without having an straightforward way to group them</span><span style="color: black;">. If you are curious, just run queries against the executions view in SSIDB as you run packages: <span style="color: black;"><a href="https://login.live.com/login.srf?wa=wsignin1.0&amp;rpsnv=11&amp;checkda=1&amp;ct=1344831426&amp;rver=6.0.5276.0&amp;wp=MCLBI&amp;wlcxt=msdn%24msdn%24msdn&amp;wreply=http%3a%2f%2fmsdn.microsoft.com%2fen-us%2flibrary%2fff878089.aspx&amp;lc=1033&amp;id=254354&amp;mkt=en-US" target="_blank">[catalog].[executions]</a> view in SISDB database.</span></span></span><br /><br /><span style="color: black;">The way most ETL frameworks work, there needs to be control over the unit of auditing of the ETL processes. For instance,&nbsp; all sub process and records affected by an execution of the ETL process can be identified using an unique ID, even</span><span style="color: black;"> if the process has to be run multiple times&nbsp; (e.g failures). </span><br /><h3>Bringing the 2 together</h3>Since I wanted to keep the same level of control over the ETL auditing unit of my old framework, but still take advantage of the goodies SSIDB offers me, I ended up creating a bridge table that will associate the custom ETLBatchIDs with the SSIDB execution_ids and extending the framework’s logging process to populate it via execute sql tasks. <br /><a href="http://lh6.ggpht.com/-dXHd94JC618/UCh-e21yIwI/AAAAAAAAAb0/Cn1Cy2yzn8M/s1600-h/image22.png"><img alt="image" border="0" height="396" src="http://lh4.ggpht.com/-2gZ-D5Qst_w/UCh-flaBCGI/AAAAAAAAAb8/IkK6ipLM1Ys/image_thumb11.png?imgmax=800" style="background-image: none; border-width: 0px; display: inline; padding-left: 0px; padding-right: 0px; padding-top: 0px;" title="image" width="789" /></a><br />Notice that I did not add a FK constraint between the custom tables and the SSISDB (dotted line) as I wanted to minimize the impact this could have had over SSIDB cleanup processes.<br />Whit this setup,&nbsp; I now have the ability to keep the unit of work/auditing the user was used to – ETL Batch ID – but still be able to join to the built-in SSIDB execution tables and views, which open the door to a richer ETL auditing and reporting experience. For instance, I am able to see how many SSISDB executions_IDs were required before a given ETLBatchID was completed and access the execution details of each one of them, like in the case of ETLBatchID=1007 below<br /><br /><a href="http://lh5.ggpht.com/-Qa9mjy3Iw1M/UCh-gpRCInI/AAAAAAAAAcE/U7Fzt46bwMk/s1600-h/image12.png"><img alt="image" border="0" height="274" src="http://lh4.ggpht.com/-9dehYKy9fpU/UCh-htJho4I/AAAAAAAAAcM/rAbFLBjTues/image_thumb8.png?imgmax=800" style="background-image: none; border-width: 0px; display: inline; padding-left: 0px; padding-right: 0px; padding-top: 0px;" title="image" width="829" /></a><br /><br />Notice that I still have a lot of testing to do, so if you decide to use this approach, use it at your own risk. On the other,&nbsp;&nbsp; I would love to hear your feedback if you know of a different way of doing this, or if you catch any flaws in my approach.<br /><br />-rafa<div class="feedflare">
<a href="http://feeds.feedburner.com/~ff/RafaelS?a=6HXUpz1IsU8:dSLFsBvWBF4:yIl2AUoC8zA"><img src="http://feeds.feedburner.com/~ff/RafaelS?d=yIl2AUoC8zA" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=6HXUpz1IsU8:dSLFsBvWBF4:63t7Ie-LG7Y"><img src="http://feeds.feedburner.com/~ff/RafaelS?d=63t7Ie-LG7Y" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=6HXUpz1IsU8:dSLFsBvWBF4:-BTjWOF_DHI"><img src="http://feeds.feedburner.com/~ff/RafaelS?i=6HXUpz1IsU8:dSLFsBvWBF4:-BTjWOF_DHI" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=6HXUpz1IsU8:dSLFsBvWBF4:V_sGLiPBpWU"><img src="http://feeds.feedburner.com/~ff/RafaelS?i=6HXUpz1IsU8:dSLFsBvWBF4:V_sGLiPBpWU" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=6HXUpz1IsU8:dSLFsBvWBF4:gIN9vFwOqvQ"><img src="http://feeds.feedburner.com/~ff/RafaelS?i=6HXUpz1IsU8:dSLFsBvWBF4:gIN9vFwOqvQ" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=6HXUpz1IsU8:dSLFsBvWBF4:I97M6haO00k"><img src="http://feeds.feedburner.com/~ff/RafaelS?d=I97M6haO00k" border="0"></img></a>
</div><img src="http://feeds.feedburner.com/~r/RafaelS/~4/6HXUpz1IsU8" height="1" width="1" alt=""/>Rafael Salashttps://plus.google.com/116687955206452017583noreply@blogger.com9http://www.rafael-salas.com/2012/08/ssis-2012-and-my-old-etl-framework.htmltag:blogger.com,1999:blog-29438013.post-41889403716720137182012-06-19T08:23:00.001-04:002012-06-19T08:28:29.089-04:00Honored To Be Speaking At PASS Summit 2012<div class="wlWriterEditableSmartContent" id="scid:8747F07C-CDE8-481f-B0DF-C6CFD074BF67:561a62e6-d53f-4238-9a92-ead8c26c3dec" style="display: inline; float: left; margin: 0px; padding: 0px;"><div class="separator" style="clear: both; text-align: center;"><a href="http://2.bp.blogspot.com/-PpZUdP9CF9k/T-BwSnhAu7I/AAAAAAAAAbE/RQTbrPeRVMc/s1600/PASS_2012_SpeakingButton_250x250.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://2.bp.blogspot.com/-PpZUdP9CF9k/T-BwSnhAu7I/AAAAAAAAAbE/RQTbrPeRVMc/s1600/PASS_2012_SpeakingButton_250x250.jpg" /></a></div></div><br />PASS Summit is the largest and perhaps the most influential SQL Server event in the world with more than 170 technical sessions and drawing 3 to 4 thousand people every year. Why I am telling you all this? Because, I was greatly surprised when I learned 2 of my submissions were selected, one of them as alternate,&nbsp; for this year PASS Summit in Seattle.This is my first time presenting at such great event and I fell both excited and honored about making the list of speakers. <br />A big thanks goes to ,my employer <a href="http://intellinet.com/" target="_blank">Intellinet</a> for sponsoring my trip there and to my team and coworkers for helping me refining the abstracts. Thanks Melissa, Leo and Laura!<br /><table border="1" cellpadding="2" cellspacing="0" style="width: 541px;"> <tbody><tr> <td valign="top" width="216">TRACK</td> <td valign="top" width="245">Session</td> <td valign="top" width="78">Tags</td></tr><tr> <td valign="top" width="216">BI Platform Architecture, Development &amp; Administration</td> <td valign="top" width="245">Waiter, There's a Fly In My Data</td> <td valign="top" width="78">Accepted</td></tr><tr> <td valign="top" width="216">BI Platform Architecture, Development &amp; Administration</td> <td valign="top" width="245">SSIS 2012: What is in that project?</td> <td valign="top" width="78">Alternate*</td></tr></tbody></table>*Alternate means that it has been accepted as an alternate session in the event that an accepted session is declined or canceled.&nbsp; <br /><br />Browse the full schedule&nbsp;in <a href="http://www.sqlpass.org/summit/2012/Sessions/ConferenceSessions.aspx?spid=65&amp;p=1&amp;preferred=false" target="_blank">PASS website</a>. I hope you can attend.<br />I will see you in Seattle!<div class="feedflare">
<a href="http://feeds.feedburner.com/~ff/RafaelS?a=s0o5I11Fiew:nS64-MRJXl8:yIl2AUoC8zA"><img src="http://feeds.feedburner.com/~ff/RafaelS?d=yIl2AUoC8zA" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=s0o5I11Fiew:nS64-MRJXl8:63t7Ie-LG7Y"><img src="http://feeds.feedburner.com/~ff/RafaelS?d=63t7Ie-LG7Y" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=s0o5I11Fiew:nS64-MRJXl8:-BTjWOF_DHI"><img src="http://feeds.feedburner.com/~ff/RafaelS?i=s0o5I11Fiew:nS64-MRJXl8:-BTjWOF_DHI" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=s0o5I11Fiew:nS64-MRJXl8:V_sGLiPBpWU"><img src="http://feeds.feedburner.com/~ff/RafaelS?i=s0o5I11Fiew:nS64-MRJXl8:V_sGLiPBpWU" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=s0o5I11Fiew:nS64-MRJXl8:gIN9vFwOqvQ"><img src="http://feeds.feedburner.com/~ff/RafaelS?i=s0o5I11Fiew:nS64-MRJXl8:gIN9vFwOqvQ" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=s0o5I11Fiew:nS64-MRJXl8:I97M6haO00k"><img src="http://feeds.feedburner.com/~ff/RafaelS?d=I97M6haO00k" border="0"></img></a>
</div><img src="http://feeds.feedburner.com/~r/RafaelS/~4/s0o5I11Fiew" height="1" width="1" alt=""/>Rafael Salashttps://plus.google.com/116687955206452017583noreply@blogger.com0http://www.rafael-salas.com/2012/06/honored-to-be-speaking-at-pass-summit.htmltag:blogger.com,1999:blog-29438013.post-71171972605983819792012-04-16T23:29:00.001-04:002012-04-16T23:29:37.125-04:00Presenting at Triangle SQL Server User Group April 16th<p>I am honored to be presenting at Raleigh’s <a href="http://www.tripass.org/dnn/" target="_blank">TriPass</a> group tomorrow Tuesday at 5:30PM. In this session, I will be presenting the new server features in SSIS 2012 and how to create, deployed and managed solutions using the new deployment model. You can check out the slides for this session <a title="Presentation" href="http://www.rafael-salas.com/2012/04/presentation-ssis-2012-new-server-and.html" target="_blank">in my earlier post</a>.</p> <p>I hope you can attend.</p> <div class="feedflare">
<a href="http://feeds.feedburner.com/~ff/RafaelS?a=kVlVo0IfZGA:g9FUSj5uXoY:yIl2AUoC8zA"><img src="http://feeds.feedburner.com/~ff/RafaelS?d=yIl2AUoC8zA" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=kVlVo0IfZGA:g9FUSj5uXoY:63t7Ie-LG7Y"><img src="http://feeds.feedburner.com/~ff/RafaelS?d=63t7Ie-LG7Y" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=kVlVo0IfZGA:g9FUSj5uXoY:-BTjWOF_DHI"><img src="http://feeds.feedburner.com/~ff/RafaelS?i=kVlVo0IfZGA:g9FUSj5uXoY:-BTjWOF_DHI" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=kVlVo0IfZGA:g9FUSj5uXoY:V_sGLiPBpWU"><img src="http://feeds.feedburner.com/~ff/RafaelS?i=kVlVo0IfZGA:g9FUSj5uXoY:V_sGLiPBpWU" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=kVlVo0IfZGA:g9FUSj5uXoY:gIN9vFwOqvQ"><img src="http://feeds.feedburner.com/~ff/RafaelS?i=kVlVo0IfZGA:g9FUSj5uXoY:gIN9vFwOqvQ" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=kVlVo0IfZGA:g9FUSj5uXoY:I97M6haO00k"><img src="http://feeds.feedburner.com/~ff/RafaelS?d=I97M6haO00k" border="0"></img></a>
</div><img src="http://feeds.feedburner.com/~r/RafaelS/~4/kVlVo0IfZGA" height="1" width="1" alt=""/>Rafael Salashttps://plus.google.com/116687955206452017583noreply@blogger.com0http://www.rafael-salas.com/2012/04/presenting-at-triangle-sql-server-user.htmltag:blogger.com,1999:blog-29438013.post-46485071391049364832012-04-14T07:13:00.001-04:002012-04-14T07:17:05.590-04:00Presentation: SSIS 2012 - New Server and Project Deployment ModelI will be delivering this presentation in a number of events in the upcoming weeks and want to shares slides to give you a better idea what the session is about. Enjoy it!<br /><br /><br /><div id="__ss_12537473" style="width: 425px;"><strong style="display: block; margin-bottom: 4px; margin-left: 0px; margin-right: 0px; margin-top: 12px; text-align: center;"><a href="http://www.slideshare.net/rfsalas/ssis-2012-new-server-and-project-deployment-modelmodel" title="SSIS 2012: New Server and Project Deployment Modelmodel">SSIS 2012: New Server and Project Deployment Model</a></strong><br /><div style="text-align: center;"><object height="355" id="__sse12537473" width="425"><param name="movie" value="http://static.slidesharecdn.com/swf/ssplayer2.swf?doc=ssis2012projectmodelandcatalog-sqlsatatlanta2012share-120414060426-phpapp01&amp;stripped_title=ssis-2012-new-server-and-project-deployment-modelmodel&amp;userName=rfsalas" /> <param name="allowFullScreen" value="true"/> <param name="allowScriptAccess" value="always"/> <param name="wmode" value="transparent"/> <embed name="__sse12537473" src="http://static.slidesharecdn.com/swf/ssplayer2.swf?doc=ssis2012projectmodelandcatalog-sqlsatatlanta2012share-120414060426-phpapp01&amp;stripped_title=ssis-2012-new-server-and-project-deployment-modelmodel&amp;userName=rfsalas" type="application/x-shockwave-flash" allowscriptaccess="always" allowfullscreen="true" wmode="transparent" width="425" height="355"></embed></object></div><br /><div style="padding: 5px 0 12px;">View more <a href="http://www.slideshare.net/">presentations</a> from <a href="http://www.slideshare.net/rfsalas">rfsalas</a>.</div></div><div class="feedflare">
<a href="http://feeds.feedburner.com/~ff/RafaelS?a=pbERDznVyBw:yBvvTtl2qw4:yIl2AUoC8zA"><img src="http://feeds.feedburner.com/~ff/RafaelS?d=yIl2AUoC8zA" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=pbERDznVyBw:yBvvTtl2qw4:63t7Ie-LG7Y"><img src="http://feeds.feedburner.com/~ff/RafaelS?d=63t7Ie-LG7Y" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=pbERDznVyBw:yBvvTtl2qw4:-BTjWOF_DHI"><img src="http://feeds.feedburner.com/~ff/RafaelS?i=pbERDznVyBw:yBvvTtl2qw4:-BTjWOF_DHI" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=pbERDznVyBw:yBvvTtl2qw4:V_sGLiPBpWU"><img src="http://feeds.feedburner.com/~ff/RafaelS?i=pbERDznVyBw:yBvvTtl2qw4:V_sGLiPBpWU" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=pbERDznVyBw:yBvvTtl2qw4:gIN9vFwOqvQ"><img src="http://feeds.feedburner.com/~ff/RafaelS?i=pbERDznVyBw:yBvvTtl2qw4:gIN9vFwOqvQ" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=pbERDznVyBw:yBvvTtl2qw4:I97M6haO00k"><img src="http://feeds.feedburner.com/~ff/RafaelS?d=I97M6haO00k" border="0"></img></a>
</div><img src="http://feeds.feedburner.com/~r/RafaelS/~4/pbERDznVyBw" height="1" width="1" alt=""/>Rafael Salashttps://plus.google.com/116687955206452017583noreply@blogger.com0http://www.rafael-salas.com/2012/04/presentation-ssis-2012-new-server-and.htmltag:blogger.com,1999:blog-29438013.post-12629588078374070562012-03-16T09:00:00.000-04:002012-03-16T13:02:41.129-04:00Got a New Team!<br /><br /><div style="text-align: left;"><span id="goog_1780287412"></span>﻿<span id="goog_1780287415"></span>﻿﻿﻿﻿</div><div style="text-align: left;"><span id="goog_1780287432"></span>﻿﻿﻿<span id="goog_1780287439"></span>﻿﻿﻿<br /><table align="left" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody><tr><td style="text-align: center;"><a href="http://www.flickr.com/photos/dom_w/2772194802/" target="_blank"><img align="left" height="270" src="http://farm4.staticflickr.com/3278/2772194802_8fb000f432_z.jpg?zz=1" style="display: inline; margin: 0px auto;" width="400" /></a></td></tr><tr><td class="tr-caption" style="text-align: center;"><span style="font-family: Verdana, sans-serif; font-size: small;">A really fun team!</span></td></tr></tbody></table><span id="goog_1780287440"></span>﻿﻿﻿<span id="goog_1780287433"></span><span id="goog_1780287416"></span>﻿<span id="goog_1780287413"></span></div>A few weeks&nbsp;back, I took a big step and decided to change jobs. I joined one of the most talented and fun teams I have ever seen, where I get to hang out with Geoff Hiten (<a href="http://twitter.com/#!/SQLCraftsman" target="_blank">@SQLCraftman</a> | <a href="http://weblogs.sqlteam.com/geoffh/Default.aspx" target="_blank">blog</a>), Melissa Coates (<a href="http://twitter.com/#!/SQLChick" target="_blank">@SQLChick</a> | <a href="http://www.sqlchick.com/entries/2011/4/17/technology-consulting-as-an-employment-option.html" target="_blank">blog</a>) and <span class="full-name"><span class="given-name">Michael</span> <span class="family-name">Oryszak</span></span>(<a href="http://twitter.com/#!/next_connect" target="_blank">@next_connect</a> | <a href="http://www.mikeoryszak.com/" target="_blank">blog</a>), all of them well known for their expertise and contributions to the technical community.&nbsp; I also get to work for a company that thinks and breaths community support, that delivers solution using Microsoft technologies, and to top it off, takes client engagement and satisfaction to the next level. <br /><br />So, where did I go, and what will I be doing? Thanks for asking! I joined <a href="http://www.intellinet.com/" target="_blank">Intellinet,</a>&nbsp;as a Principal Consultant in the BI practice. This position&nbsp; puts me back in a consulting role, and as a bonus I get to work from home – when not at client sites – which I am already loving!<br />Cheers,<br />Rafa<span id="goog_1780287446"></span><a href="http://draft.blogger.com/"></a><span id="goog_1780287447"></span><div class="feedflare">
<a href="http://feeds.feedburner.com/~ff/RafaelS?a=lNTpvScL8Gw:vi4pkcWxpmA:yIl2AUoC8zA"><img src="http://feeds.feedburner.com/~ff/RafaelS?d=yIl2AUoC8zA" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=lNTpvScL8Gw:vi4pkcWxpmA:63t7Ie-LG7Y"><img src="http://feeds.feedburner.com/~ff/RafaelS?d=63t7Ie-LG7Y" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=lNTpvScL8Gw:vi4pkcWxpmA:-BTjWOF_DHI"><img src="http://feeds.feedburner.com/~ff/RafaelS?i=lNTpvScL8Gw:vi4pkcWxpmA:-BTjWOF_DHI" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=lNTpvScL8Gw:vi4pkcWxpmA:V_sGLiPBpWU"><img src="http://feeds.feedburner.com/~ff/RafaelS?i=lNTpvScL8Gw:vi4pkcWxpmA:V_sGLiPBpWU" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=lNTpvScL8Gw:vi4pkcWxpmA:gIN9vFwOqvQ"><img src="http://feeds.feedburner.com/~ff/RafaelS?i=lNTpvScL8Gw:vi4pkcWxpmA:gIN9vFwOqvQ" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=lNTpvScL8Gw:vi4pkcWxpmA:I97M6haO00k"><img src="http://feeds.feedburner.com/~ff/RafaelS?d=I97M6haO00k" border="0"></img></a>
</div><img src="http://feeds.feedburner.com/~r/RafaelS/~4/lNTpvScL8Gw" height="1" width="1" alt=""/>Rafael Salashttps://plus.google.com/116687955206452017583noreply@blogger.com4http://www.rafael-salas.com/2012/03/got-new-team.htmltag:blogger.com,1999:blog-29438013.post-57769143476976341732012-03-13T09:00:00.000-04:002012-03-13T09:43:46.912-04:00SQL Server 2012 General AvailabilityDid you hear it already?<br />﻿<table cellpadding="0" cellspacing="0" class="tr-caption-container" style="float: right; margin-left: 1em; text-align: right;"><tbody><tr><td style="text-align: center;"><a href="http://2.bp.blogspot.com/-ykI_uj1LnMs/T15RSqu9_LI/AAAAAAAAAZA/Al4VmBHnY2I/s1600/3668185197_8fe06d2bcc.jpg" imageanchor="1" style="clear: right; margin-bottom: 1em; margin-left: auto; margin-right: auto;"><img border="0" height="266" src="http://2.bp.blogspot.com/-ykI_uj1LnMs/T15RSqu9_LI/AAAAAAAAAZA/Al4VmBHnY2I/s400/3668185197_8fe06d2bcc.jpg" width="400" /></a></td></tr><tr><td class="tr-caption" style="text-align: center;"><strong>SQL Server 2012 is here</strong></td></tr></tbody></table>Last week, SQL Server 2012 was <a href="http://blogs.technet.com/b/dataplatforminsider/archive/2012/03/06/sql-server-2012-released-to-manufacturing.aspx" target="_blank">released to manufacturing (RTM)</a> and general availability is expected on April first – That’s right, on April Fool’s Day. Now, if you don’t want to wait until then, you can <a href="http://www.microsoft.com/download/en/details.aspx?id=29066&amp;ocid=soc-n-at-loc--TI-MG&amp;WT.mc_id=soc-n-at-loc--TI-MG" target="_blank">download a 6 months evaluation copy here</a>, or from your MSDN subscription – if you have one of course.<br />With that out f the way, here are few links that are worthy your attention: <br /><br /><a href="http://msdn.microsoft.com/en-us/library/bb500435(v=sql.110).aspx" target="_blank">What’s new in SQL Server 2012</a>. An overview of what’s is new or changing in each of the SQL Server products. You may find that not all changes are listed there, specially small ones.<br /><br /><a href="http://msdn.microsoft.com/en-us/library/ms130214(v=sql.110).aspx" target="_blank">SQL Server 2012 Books Online</a>. Product online help.<br /><br /><a href="http://msdn.microsoft.com/en-us/data/gg427686" target="_blank">SQL Server Database Tools</a>. Do you remember Data dude projects and BIDS? well, now they are coming under a single roof called SQL Server Database Tools (SSTD), but&nbsp;still using Visual Studio shell – No! you don’t need Visual Studio licenses for it, it is free for SQL Server users. <a href="http://msdn.microsoft.com/en-us/data/hh322942" target="_blank">You can read SSTD Frequently Asked Questions</a>, follow the <a href="http://blogs.msdn.com/b/ssdt/" target="_blank">SSTD’s blog</a>&nbsp; or read <a href="http://msdn.microsoft.com/en-us/library/hh272686(v=vs.103).aspx" target="_blank">Books Online</a> to get up to speed. This is probably the most exciting change for us database developers as it brings declarative development experience to the every SQL Server developer – believe me, something you want use if you take database development seriously.<br /><br />SQL <a href="http://technet.microsoft.com/en-us/sqlserver/hh780961" target="_blank">Server Data Quality Services (DQS).</a> This tool is the new kid on the block and one of the componets&nbsp;the Entirprise Information Management (EIM) plataform. DQS functionality comes handy when dealing with data quality and cleasing scenarios where <a href="http://en.wikipedia.org/wiki/Identity_resolution" target="_blank">identity resolution</a> and matching are required.<br /><br />The <a href="http://www.microsoft.com/sqlserver/en/us/future-editions/sql2012-licensing.aspx" target="_blank">licensing model</a> and numbers of <a href="http://www.microsoft.com/sqlserver/en/us/future-editions/sql2012-editions.aspx" target="_blank">editions</a> being offered are also changing. Although,&nbsp; I am excited about a new BI edition being offered, you may find out that some features that are arguably BI hardcore features are included only in the Enterprise edition.<div class="feedflare">
<a href="http://feeds.feedburner.com/~ff/RafaelS?a=Fw33MRIYJ7k:qUk0_9Z0Io4:yIl2AUoC8zA"><img src="http://feeds.feedburner.com/~ff/RafaelS?d=yIl2AUoC8zA" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=Fw33MRIYJ7k:qUk0_9Z0Io4:63t7Ie-LG7Y"><img src="http://feeds.feedburner.com/~ff/RafaelS?d=63t7Ie-LG7Y" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=Fw33MRIYJ7k:qUk0_9Z0Io4:-BTjWOF_DHI"><img src="http://feeds.feedburner.com/~ff/RafaelS?i=Fw33MRIYJ7k:qUk0_9Z0Io4:-BTjWOF_DHI" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=Fw33MRIYJ7k:qUk0_9Z0Io4:V_sGLiPBpWU"><img src="http://feeds.feedburner.com/~ff/RafaelS?i=Fw33MRIYJ7k:qUk0_9Z0Io4:V_sGLiPBpWU" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=Fw33MRIYJ7k:qUk0_9Z0Io4:gIN9vFwOqvQ"><img src="http://feeds.feedburner.com/~ff/RafaelS?i=Fw33MRIYJ7k:qUk0_9Z0Io4:gIN9vFwOqvQ" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=Fw33MRIYJ7k:qUk0_9Z0Io4:I97M6haO00k"><img src="http://feeds.feedburner.com/~ff/RafaelS?d=I97M6haO00k" border="0"></img></a>
</div><img src="http://feeds.feedburner.com/~r/RafaelS/~4/Fw33MRIYJ7k" height="1" width="1" alt=""/>Rafael Salashttps://plus.google.com/116687955206452017583noreply@blogger.com0http://www.rafael-salas.com/2012/03/sql-server-2012-general-availability.htmltag:blogger.com,1999:blog-29438013.post-81408974465470949262012-02-15T08:51:00.001-05:002012-02-15T09:24:50.641-05:00SQL Saturday #111 Atlanta: Taking Your SSIS Skills To The Next LevelWith the <a href="http://www.sqlsaturday.com/eventhome.aspx" target="_blank">SQL Saturday in Atlanta</a> approaching quickly, I want to point out one more reason, and my personal favorite, to attend it: SSIS. This particular SQL Saturday comes loaded with great lineup of speakers and session in SSIS. First, there is a full day of SSIS offered as pre-conference ($99-$109) where <a href="http://sqlblog.com/blogs/andy_leonard/" target="_blank">Andy Leonard</a> and <a href="http://www.mattmasson.com/" target="_blank">Matt Masson</a> take you from how to spell SSIS to building robust ETL solutions. The next day, you will have 5 more sessions on different topics and version of SSIS (including one by me):<br /><a href="http://www.sqlsaturday.com/viewsession.aspx?sat=111&amp;sessionid=7492">Hope Foley<br />Super-size Your SSIS Breakfast Sandwich: Performan</a>ce <br /><a href="http://www.sqlsaturday.com/viewsession.aspx?sat=111&amp;sessionid=6905">Rafael Salas<br />SSIS 2012: Project Deployment Model and Catalog</a> <br /><a href="http://www.sqlsaturday.com/viewsession.aspx?sat=111&amp;sessionid=6875">Andy Leonard<br />Designing an SSIS Framework </a> <br /><a href="http://www.sqlsaturday.com/viewsession.aspx?sat=111&amp;sessionid=7065">Matt Masson<br />SSIS in SQL Server 2012: Deployment and Management</a><br /><a href="http://www.sqlsaturday.com/viewsession.aspx?sat=111&amp;sessionid=7144">Patrick LeBlanc<br />High Availability for SSIS in SQL Server 2012</a><br />Remember that attending Saturday sessions is free, so make sure you register and come. I hope you see you there.<div class="feedflare">
<a href="http://feeds.feedburner.com/~ff/RafaelS?a=EtHDH9RDbcs:yttjg2bqbRg:yIl2AUoC8zA"><img src="http://feeds.feedburner.com/~ff/RafaelS?d=yIl2AUoC8zA" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=EtHDH9RDbcs:yttjg2bqbRg:63t7Ie-LG7Y"><img src="http://feeds.feedburner.com/~ff/RafaelS?d=63t7Ie-LG7Y" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=EtHDH9RDbcs:yttjg2bqbRg:-BTjWOF_DHI"><img src="http://feeds.feedburner.com/~ff/RafaelS?i=EtHDH9RDbcs:yttjg2bqbRg:-BTjWOF_DHI" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=EtHDH9RDbcs:yttjg2bqbRg:V_sGLiPBpWU"><img src="http://feeds.feedburner.com/~ff/RafaelS?i=EtHDH9RDbcs:yttjg2bqbRg:V_sGLiPBpWU" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=EtHDH9RDbcs:yttjg2bqbRg:gIN9vFwOqvQ"><img src="http://feeds.feedburner.com/~ff/RafaelS?i=EtHDH9RDbcs:yttjg2bqbRg:gIN9vFwOqvQ" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=EtHDH9RDbcs:yttjg2bqbRg:I97M6haO00k"><img src="http://feeds.feedburner.com/~ff/RafaelS?d=I97M6haO00k" border="0"></img></a>
</div><img src="http://feeds.feedburner.com/~r/RafaelS/~4/EtHDH9RDbcs" height="1" width="1" alt=""/>Rafael Salashttps://plus.google.com/116687955206452017583noreply@blogger.com0http://www.rafael-salas.com/2012/02/sql-saturday-111-atlanta-taking-your.htmltag:blogger.com,1999:blog-29438013.post-54424824867464315132012-01-31T09:32:00.002-05:002012-01-31T09:32:20.869-05:00SSIS 2012: Better Execute Package TaskExecute Package Task had some rough edges in previous versions of SSIS. to name a few, you had to create a connection manager to be used by each task/package, and there was not a simply way to parameterize the connection managers all at once. Passing values from parent to children packages was something that did not have a good story either.<br /><table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody><tr><td style="text-align: center;"><a href="http://lh6.ggpht.com/-CGaG8448dOI/TxO1MY75MlI/AAAAAAAAAWk/dOFuv_x97v4/s1600-h/image%25255B4%25255D.png" style="margin-left: auto; margin-right: auto;"><img alt="image" border="0" height="178" src="http://lh5.ggpht.com/-1kjOQw9sLyM/TxO1NSTgbgI/AAAAAAAAAWs/3xkefyh2mE4/image_thumb%25255B2%25255D.png?imgmax=800" style="background-image: none; border-bottom-color: initial; border-bottom-style: initial; border-bottom-width: 0px; border-left-color: initial; border-left-style: initial; border-left-width: 0px; border-right-color: initial; border-right-style: initial; border-right-width: 0px; border-top-color: initial; border-top-style: initial; border-top-width: 0px; display: inline; padding-left: 0px; padding-right: 0px; padding-top: 0px;" title="image" width="612" /></a></td></tr><tr><td class="tr-caption" style="text-align: center;">A&nbsp;plethora of connection managers in the old days...</td></tr></tbody></table><h3> What has&nbsp;changed&nbsp;in SSIS 2012?</h3>With SSIS 2012 when you use the new&nbsp;<a href="http://www.rafael-salas.com/2012/01/ssis-2012-project-deployment-model-and.html" target="_blank">project deployment model</a>,&nbsp;the Execute package task is now easier to setup and configure:<br />For one, we just need to select the name of the package – any package within the current project – from a drop down list, as we will save quite a few clicks a we don’t need to create a configuration manager each time.<br /><table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody><tr><td style="text-align: center;"><a href="http://lh3.ggpht.com/-CwmbmA44UVE/TxO1N6VwWBI/AAAAAAAAAW0/JIKAQT68iNU/s1600-h/image%25255B18%25255D.png" style="margin-left: auto; margin-right: auto;"><img alt="image" border="0" height="220" src="http://lh5.ggpht.com/-HfztCjWW0hY/TxO1OSzUbYI/AAAAAAAAAW8/pETxzffWNNg/image_thumb%25255B12%25255D.png?imgmax=800" style="background-image: none; border-bottom-color: initial; border-bottom-style: initial; border-bottom-width: 0px; border-left-color: initial; border-left-style: initial; border-left-width: 0px; border-right-color: initial; border-right-style: initial; border-right-width: 0px; border-top-color: initial; border-top-style: initial; border-top-width: 0px; display: inline; padding-left: 0px; padding-right: 0px; padding-top: 0px;" title="image" width="558" /></a></td></tr><tr><td class="tr-caption" style="text-align: center;">Point and click</td></tr></tbody></table><br />Second, we have the ability to bind the parameters of the child package to variable or parameters in the parent package, and once again, just by selecting the appropriate values from the dropdown lists.<br /><br /><table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody><tr><td style="text-align: center;"><a href="http://lh5.ggpht.com/-qYR2NJqG7yg/TxO1O0m6Z6I/AAAAAAAAAXE/nWzM25_paHY/s1600-h/image%25255B19%25255D.png" style="margin-left: auto; margin-right: auto;"><img alt="image" border="0" height="291" src="http://lh3.ggpht.com/-P7C_qxD0wWc/TxO1PjZhDHI/AAAAAAAAAXM/2G1JShasJZU/image_thumb%25255B13%25255D.png?imgmax=800" style="background-image: none; border-bottom-color: initial; border-bottom-style: initial; border-bottom-width: 0px; border-left-color: initial; border-left-style: initial; border-left-width: 0px; border-right-color: initial; border-right-style: initial; border-right-width: 0px; border-top-color: initial; border-top-style: initial; border-top-width: 0px; display: inline; padding-left: 0px; padding-right: 0px; padding-top: 0px;" title="image" width="562" /></a></td></tr><tr><td class="tr-caption" style="text-align: center;">Nice: The execute package task 'sees' the parameters defined in the child package</td></tr></tbody></table>Third, there are no connection managers to maintain and configure. <br /><br />Now,&nbsp;remember these benefits are only available&nbsp;when using the new&nbsp;<a href="http://www.rafael-salas.com/2012/01/ssis-2012-project-deployment-model-and.html" target="_blank">project deployment model</a>.<br /><h3> </h3><div class="feedflare">
<a href="http://feeds.feedburner.com/~ff/RafaelS?a=0uANd2RUgNA:Cs44dDQslBc:yIl2AUoC8zA"><img src="http://feeds.feedburner.com/~ff/RafaelS?d=yIl2AUoC8zA" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=0uANd2RUgNA:Cs44dDQslBc:63t7Ie-LG7Y"><img src="http://feeds.feedburner.com/~ff/RafaelS?d=63t7Ie-LG7Y" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=0uANd2RUgNA:Cs44dDQslBc:-BTjWOF_DHI"><img src="http://feeds.feedburner.com/~ff/RafaelS?i=0uANd2RUgNA:Cs44dDQslBc:-BTjWOF_DHI" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=0uANd2RUgNA:Cs44dDQslBc:V_sGLiPBpWU"><img src="http://feeds.feedburner.com/~ff/RafaelS?i=0uANd2RUgNA:Cs44dDQslBc:V_sGLiPBpWU" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=0uANd2RUgNA:Cs44dDQslBc:gIN9vFwOqvQ"><img src="http://feeds.feedburner.com/~ff/RafaelS?i=0uANd2RUgNA:Cs44dDQslBc:gIN9vFwOqvQ" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=0uANd2RUgNA:Cs44dDQslBc:I97M6haO00k"><img src="http://feeds.feedburner.com/~ff/RafaelS?d=I97M6haO00k" border="0"></img></a>
</div><img src="http://feeds.feedburner.com/~r/RafaelS/~4/0uANd2RUgNA" height="1" width="1" alt=""/>Rafael Salashttps://plus.google.com/116687955206452017583noreply@blogger.com8http://www.rafael-salas.com/2012/01/ssis-2012-better-execute-package-task.htmltag:blogger.com,1999:blog-29438013.post-6252706591070982792012-01-26T12:14:00.002-05:002012-01-26T12:14:43.738-05:00Two Tricks When Adding Packages To A SSIS ProjectIf you need to add multiple packages to a project you may have noticed that the ‘Add existing package’ option is rather irritating as you have to do it one at the time. So, here is a trick to add several package all at once: <br /><div class="wlWriterEditableSmartContent" id="scid:8747F07C-CDE8-481f-B0DF-C6CFD074BF67:cb2b8e9c-4248-4fb6-a625-f22a64be8ab4" style="display: inline; float: right; margin: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;"><a href="http://lh5.ggpht.com/-KV67vxkuaA0/TyDDd3ylvAI/AAAAAAAAAXw/mBsFZTqCwR0/1-25-2012%2525209-24-25%252520PM-8x6%25255B1%25255D.jpg?imgmax=800" rel="thumbnail" title="A trick your carpal tunnel will thank"><img border="0" height="272" src="http://lh4.ggpht.com/-KY0eJzKktko/TyDDeSTO_YI/AAAAAAAAAX4/vJJPopgoh9Q/1-25-2012%2525209-24-25%252520PM%25255B23%25255D.png?imgmax=800" width="368" /></a></div><ul><li>Right click in the project name in the solution explorer</li><li>Select <i>add existing item</i></li><li>Navigate to the folder where the packages reside, and use CTRL+left click to select packages.</li><li>Click the <i>Add</i> button</li><div class="wlWriterEditableSmartContent" id="scid:8747F07C-CDE8-481f-B0DF-C6CFD074BF67:f70c13e7-6357-42c1-89c5-e87930316bf7" style="display: inline; float: none; margin: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;"><a href="http://lh3.ggpht.com/-M5pDqTtsDqY/TyDDfCAqivI/AAAAAAAAAYA/mWEMVsnDcbY/1-25-2012%2525209-25-01%252520PM-8x6.jpg?imgmax=800" rel="thumbnail" title=""><img border="0" height="322" src="http://lh4.ggpht.com/-pKQ3YsGgeAE/TyDDf950JSI/AAAAAAAAAYI/FtTfmutAlXc/1-25-2012%2525209-25-01%252520PM%25255B16%25255D.png?imgmax=800" width="360" /></a></div></ul><br /><h3>Wait, you said there were two tricks!</h3>Indeed. Matt Masson shows a different way of doing this in <a href="http://www.mattmasson.com/index.php/2012/01/ssis-quick-tip-copy-paste-packages-into-a-visual-studio-project/" target="_blank">his blog</a>. Actually, was his post the one that prompted me to write mine<img alt="Smile" class="wlEmoticon wlEmoticon-smile" src="http://lh4.ggpht.com/-l6M3-DJLjMA/TyDDgLWvSbI/AAAAAAAAAYQ/TzmPqeq5Um4/wlEmoticon-smile%25255B2%25255D.png?imgmax=800" style="border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none;" /><br />As with most things, <b><a href="http://en.wikipedia.org/wiki/There's_more_than_one_way_to_do_it" target="_blank">TMTOWTDI</a></b><div class="feedflare">
<a href="http://feeds.feedburner.com/~ff/RafaelS?a=AzuOxkSYwk4:KmWR4j81HpI:yIl2AUoC8zA"><img src="http://feeds.feedburner.com/~ff/RafaelS?d=yIl2AUoC8zA" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=AzuOxkSYwk4:KmWR4j81HpI:63t7Ie-LG7Y"><img src="http://feeds.feedburner.com/~ff/RafaelS?d=63t7Ie-LG7Y" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=AzuOxkSYwk4:KmWR4j81HpI:-BTjWOF_DHI"><img src="http://feeds.feedburner.com/~ff/RafaelS?i=AzuOxkSYwk4:KmWR4j81HpI:-BTjWOF_DHI" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=AzuOxkSYwk4:KmWR4j81HpI:V_sGLiPBpWU"><img src="http://feeds.feedburner.com/~ff/RafaelS?i=AzuOxkSYwk4:KmWR4j81HpI:V_sGLiPBpWU" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=AzuOxkSYwk4:KmWR4j81HpI:gIN9vFwOqvQ"><img src="http://feeds.feedburner.com/~ff/RafaelS?i=AzuOxkSYwk4:KmWR4j81HpI:gIN9vFwOqvQ" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=AzuOxkSYwk4:KmWR4j81HpI:I97M6haO00k"><img src="http://feeds.feedburner.com/~ff/RafaelS?d=I97M6haO00k" border="0"></img></a>
</div><img src="http://feeds.feedburner.com/~r/RafaelS/~4/AzuOxkSYwk4" height="1" width="1" alt=""/>Rafael Salashttps://plus.google.com/116687955206452017583noreply@blogger.com1http://www.rafael-salas.com/2012/01/two-tricks-when-adding-packages-to-ssis.htmltag:blogger.com,1999:blog-29438013.post-52310324995159517962012-01-10T09:00:00.000-05:002012-04-07T22:16:50.389-04:00SSIS 2012: A Peek to Data TapsNote: The example in post is based on SSIS 2012 RCO<br />Data taps are one of the new features in SSIS 2012 in the data and performance troubleshooting <br /><div class="wlWriterEditableSmartContent" id="scid:8747F07C-CDE8-481f-B0DF-C6CFD074BF67:6e545d5c-6dd1-4c82-ae80-a8fe78cf8f6c" style="display: inline; float: right; margin: 0px; padding: 0px;"><a href="http://lh4.ggpht.com/-WcTM40eRcQg/TwKRSrqbRRI/AAAAAAAAAU8/sydzqofDvEk/Water%252520Tap-8x6.jpg?imgmax=800" rel="thumbnail" title="Big data tap in action"><img border="0" height="469" src="http://lh4.ggpht.com/-wx8jMwGRkAs/TwOrxrCGXDI/AAAAAAAAAVc/hLVaZLEOynA/Water-Tap29.png?imgmax=800" width="332" /></a></div>category. In a nutshell, they allow us, at <strong>runtime</strong>,&nbsp; to choose a given <strong>path from a data flow</strong> and capture a copy of the data at that specific point of the data flow in a .csv file on a given <strong>execution instance</strong> of the package.<br /><br />Let's break it down some more:<br /><b>Run-time</b>. Data taps are a runtime artifact, so as developer, you don't &nbsp;define them as you design and develop packages in SSDT. Rather, you do it&nbsp;after&nbsp;the packages are deployed to the server by way of running some stored procedures in SSMS.<br /><strong>Data Flow Path</strong>: Data taps are defined at the data flow level, and within a data flow, at the path level. Paths are the blue or red arrows in the data flow that connect inputs and outputs of data flow components. You can read the books online definition of a path <a href="http://msdn.microsoft.com/en-us/library/ms140080(SQL.110).aspx" target="_blank">here</a>. <br /><strong>Execution Instance</strong>: A data tap captures data in a given data flow path and stores it in a .csv file. This happens within a single execution &nbsp;instance of the package. That means we need to add the data tap each time we run the package during our troubleshooting exercise.<br /><br />You could think of data taps in a similar way you think about data viewers&nbsp;in <strike>BIDS</strike> SSDT, except you don’t have to edit the package to add them.<br /><h3> Setting up a Data Tap</h3>Now, let’s see an example and some pictures to show how you can add a data tap to a data flow.<br />First, we create a package with a data flow in it and deploy it to the SSIS server. For this example, I have a pretty straight forward data flow that gets a list of 4 products via OLE DB Source, does some string manipulation and then loads the data into an OLE DB destination.&nbsp; As you can see in the picture, this data flow has 2 paths (blue arrows linking data flow components). Note the value of <em>IdenificationString</em> property of the data flow path as we will use it later.<br />.&nbsp; <br /><div class="wlWriterEditableSmartContent" id="scid:8747F07C-CDE8-481f-B0DF-C6CFD074BF67:0e75ec19-26d3-4f94-990e-dea160ad73d9" style="display: inline; float: none; margin: 0px; padding: 0px;"><div class="separator" style="clear: both; text-align: center;"><a href="http://lh6.ggpht.com/-lWSIKIZc42g/TwOryLMKndI/AAAAAAAAAVk/UasDrDpFdBE/data-flow-path-1-8x6.jpg?imgmax=800" rel="thumbnail" title="Data flow and path properties"><img border="0" height="330" src="http://lh5.ggpht.com/-U24UuAGPtbo/TwOrymIn_hI/AAAAAAAAAVs/egaJL8P8s8A/data-flow-path-143.png?imgmax=800" width="580" /></a></div></div>We will need some pieces of information about the package we want to tap data from before we can setup the data tap:<br /><br /><strong>Folder Name</strong>: The folder within SSIS server where the package is deployed. <br /><strong>Project Name</strong>: The name of the SSIS project that holds the package<br /><strong>Package Name</strong>: well, just the package name. <br /><strong>Data Flow Path ID</strong>: The value of the<em>&nbsp;IdentificationString&nbsp;</em>property of the data flow path where we want to add the data tap (highlighted in yellow in the picture above). <br /><strong>Data flow task path</strong>: The path of the data flow task within the package. <strike>As opposed to getting the Dataflow path ID,&nbsp;getting&nbsp;the task path &nbsp;is tricky as it is not readily&nbsp;displayed&nbsp;in SSDT, an you&nbsp;typically&nbsp;have to resort to some sort of trick to get it. I will share a trick you can use to get value of the property path in another post.&nbsp;In this example, let’s assume we do know that the path to the data flow is</strike><span style="color: #4bacc6; text-decoration: line-through;"> </span><span style="color: black;"><em style="text-decoration: line-through;">\Package\Load Product </em>Update: The path to the data flow task is readily&nbsp;available&nbsp;in the packagePath property of the task. &nbsp;Thanks to David Joubert for pointing that out!</span><br /><b><br /></b><br /><span style="color: black;"><br /></span><br />Once we have this information, it is time to add the data tap(s) and execute the package. We do all this in SQL Server Management Studio by running 3 stored procedures that are built-in the SSIS catalog:<br /><ol><li>Create a execution instance for the package by running <em>[<span class="kwrd">catalog</span>].[create_execution]</em> stored procedure </li><li>Add the data tap(s) by running <em>[<span class="kwrd">catalog</span>].[add_data_tap] </em> </li><li>Run the package by running <em>[<span class="kwrd">catalog</span>].[start_execution]</em> package</li></ol>Here is the script I used while running my sample package:<br /><br /><a href="https://raw.github.com/gist/1558450/f0a254d68a047f6199e6f53566c195cfcf06e192/gistfile1.sql">https://raw.github.com/gist/1558450/f0a254d68a047f6199e6f53566c195cfcf06e192/gistfile1.sql</a><br /><script src="https://gist.github.com/1558450.js?file=gistfile1.sql"></script> <br />If everything goes well, you will have the .csv files in the <em><drive>:\Program Files\Microsoft SQL Server\110\DTS\DataDumps</drive></em> folder of the machine you are running the stored procedures. You can always use the built-in package execution reports to validate the execution of the package was successful or to&nbsp; look for error messages generated during the execution of the package.<br /><br /><br /><br /><table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody><tr><td style="text-align: center;"><a href="http://lh4.ggpht.com/-qJOFjpt0Lvg/TwOr0DfwI1I/AAAAAAAAAV0/_VhQQh4O9pw/s1600-h/image5.png" style="margin-left: auto; margin-right: auto;"><img alt="image" border="0" height="245" src="http://lh5.ggpht.com/-I2n4LEbyOu4/TwOr1J7RAtI/AAAAAAAAAV8/E6PTyA9i6MM/image_thumb3.png?imgmax=800" style="background-image: none; border-width: 0px; display: inline; padding-left: 0px; padding-right: 0px; padding-top: 0px;" title="image" width="618" /></a></td></tr><tr><td class="tr-caption" style="text-align: center;">Folder with data tap files</td></tr></tbody></table><br /><h3> Is That it?</h3>Almost. The example in the post is quite simplistic and the bare&nbsp;minimum I could come up with to get a data tap working.&nbsp;There is additional information in books online about them that you may want to review:<br /><br /><a href="http://msdn.microsoft.com/en-us/library/hh230989(SQL.110).aspx">catalog.add_data_tap</a><br /><a href="http://msdn.microsoft.com/en-us/library/hh230990(SQL.110).aspx">catalog.remove_data_tap</a><br /><a href="http://msdn.microsoft.com/en-us/library/hh230985(SQL.110).aspx">catalog.execution_data_taps</a><br /><a href="http://msdn.microsoft.com/en-us/library/hh230986(SQL.110).aspx">catalog.execution_data_statistics</a><br /><br /><br /><h3> Conclusion</h3><br />Troubleshooting data issues can get hairy at times and data taps are an extra tool in our belt that can help in cases where logging (which has also improve a lot in SSIS 2012) and dumps may not give us the required level of information. In the other hand,&nbsp; I would have appreciated a more seamless user experience, specially when getting the&nbsp;parameters&nbsp;needed by the stored procedures, and perhaps having more control over the file format and its content. As it stands, we may always need to open a copy of the package in SSDT to get the required metadata and the jump back to SSMS to complete the work. It would be great to have a point and click interface within SSMS that allows to navigate the structure of the package/dataflow&nbsp;and let us add a data fow with few clicks. I would like to see an&nbsp;interface&nbsp;like the 'package explorer' in BIDS for this, but unfortunately&nbsp;<a href="https://connect.microsoft.com/SQLServer/feedback/details/622405/denali-ssis-add-package-explorer-tree-in-bids-to-ssms" target="_blank">that suggestion</a> did not get too much traction last time around and it was closed as 'won't fix'. &nbsp;may be in SSIS 2014?<div class="feedflare">
<a href="http://feeds.feedburner.com/~ff/RafaelS?a=hIYfOO7PqX4:ULHVXphngzY:yIl2AUoC8zA"><img src="http://feeds.feedburner.com/~ff/RafaelS?d=yIl2AUoC8zA" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=hIYfOO7PqX4:ULHVXphngzY:63t7Ie-LG7Y"><img src="http://feeds.feedburner.com/~ff/RafaelS?d=63t7Ie-LG7Y" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=hIYfOO7PqX4:ULHVXphngzY:-BTjWOF_DHI"><img src="http://feeds.feedburner.com/~ff/RafaelS?i=hIYfOO7PqX4:ULHVXphngzY:-BTjWOF_DHI" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=hIYfOO7PqX4:ULHVXphngzY:V_sGLiPBpWU"><img src="http://feeds.feedburner.com/~ff/RafaelS?i=hIYfOO7PqX4:ULHVXphngzY:V_sGLiPBpWU" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=hIYfOO7PqX4:ULHVXphngzY:gIN9vFwOqvQ"><img src="http://feeds.feedburner.com/~ff/RafaelS?i=hIYfOO7PqX4:ULHVXphngzY:gIN9vFwOqvQ" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=hIYfOO7PqX4:ULHVXphngzY:I97M6haO00k"><img src="http://feeds.feedburner.com/~ff/RafaelS?d=I97M6haO00k" border="0"></img></a>
</div><img src="http://feeds.feedburner.com/~r/RafaelS/~4/hIYfOO7PqX4" height="1" width="1" alt=""/>Rafael Salashttps://plus.google.com/116687955206452017583noreply@blogger.com6http://www.rafael-salas.com/2012/01/ssis-2012-quick-peek-to-data-taps.htmltag:blogger.com,1999:blog-29438013.post-87485930955267402572012-01-03T10:30:00.000-05:002012-01-03T10:57:08.224-05:00SSIS 2012: Project Deployment Model and Build ConfigurationsWith the introduction of the new project deployment model in SSIS 2012, we now have the ability to deploy a project to&nbsp; a SSIS server and use <a href="http://www.rafael-salas.com/2010/11/denali-ctp-1-ssis-parameters-bring-them.html" target="_blank">parameters</a> to packages and projects. With this, there may be instances where we need to change the name of the SSIS server and the value of parameters as we develop, debug and test packages within SQL Server Data Tools. Depending on the number of servers and parameters your project and package may have, making those changes may be rather tedious and prone to error. I am thinking in the many times people have mistakenly used the wrong connection strings when debugging a package in the old days - I have done it many time.<br />The good news is that projects using the new project deployment model in SSIS 2012 leverages <a href="http://msdn.microsoft.com/en-us/library/kkz9kefa.aspx" target="_blank">build configurations from Visual Studio</a>&nbsp; in a more meaningful way than in earlier versions, making most of these changes a lot simpler. How? I am glad you ask. For one, both, project and package parameters can get their values from build configurations. For two, some project level properties can also be set via build configurations (things like Deployment Server name and project path); which makes&nbsp;debugging&nbsp;and deploying packages in SSDT (formerly&nbsp;known as BIDS)against different SSIS servers much simpler.<br /><br /><br /><h3> Binding Parameter values to Build Configurations</h3>For this, you just need to go to the parameters tab (project or package) and click button with the weird icon. The tooltip actually says “Add Parameter to Configurations'”.<br /><div class="separator" style="clear: both; text-align: center;"><a href="http://lh3.ggpht.com/-t0uBfSovcZY/Tv6xqfLj5fI/AAAAAAAAAT8/h_IEwKdTNSs/12-30-2011%25252011-20-39%252520PM-8x6.jpg?imgmax=800" rel="thumbnail" style="margin-left: 1em; margin-right: 1em;" title="The magic button"><img border="0" height="240" src="http://lh6.ggpht.com/-RtuXVhHbkWY/Tv6xrPdHDJI/AAAAAAAAAUE/wpHLSEClybs/12-30-2011%25252011-20-39%252520PM%25255B46%25255D.png?imgmax=800" width="357" /></a></div><br />Then you have the opportunity to choose the parameters you want to associate with the multiple build configurations you may have created. In the picture below, you can see how my package has 5 parameters and I had created 2 build configurations, one called “Test” and one called ‘Development”. Now you can enter a value for each parameter and configuration combination.<br /><br /><div class="wlWriterEditableSmartContent" id="scid:8747F07C-CDE8-481f-B0DF-C6CFD074BF67:f784af0a-5e70-40ec-9da2-371a7116f90c" style="display: inline; float: none; margin: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;"><a href="http://lh3.ggpht.com/-CYXnt586vbs/Tv6xsDV8UEI/AAAAAAAAAUM/-IIOzeqYJC8/12-31-2011%25252012-55-25%252520AM-8x6.jpg?imgmax=800" rel="thumbnail" title="Assigning values to each Paramter-Configuration"><img border="0" height="499" src="http://lh6.ggpht.com/-5VzqGOtaRus/Tv6xteQSuFI/AAAAAAAAAUU/x0KIM8lUEU8/12-31-2011%25252012-55-25%252520AM%25255B10%25255D.png?imgmax=800" width="580" /></a></div><br /><br />So, how is this helpful? Well, Assuming that I need to change the value of the 5 parameters at design time as I debug the package in 2 different machines, I just need to switch between build configurations and to have all parameter values changed at once. Switching between build configurations is as simple as selecting a value from a dropdown list.<br /><br /><br /><div class="wlWriterEditableSmartContent" id="scid:8747F07C-CDE8-481f-B0DF-C6CFD074BF67:50b392f2-48de-4442-8d79-b69c9702a15b" style="display: inline; float: right; margin: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;"><a href="http://lh4.ggpht.com/-MKiHY5O2UA0/Tv6xuGbxFTI/AAAAAAAAAUc/WS0mkGWNrV4/12-31-2011%2525201-10-49%252520AM-8x6.jpg?imgmax=800" rel="thumbnail" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;" title="2 clicks to switch between configurations"><img border="0" height="197" src="http://lh6.ggpht.com/-IHNpOjBRAz4/Tv6xvIgbinI/AAAAAAAAAUk/RU3-11PMUf0/12-31-2011%2525201-10-49%252520AM%25255B5%25255D.png?imgmax=800" width="420" /></a></div><br /><br /><br /><br /><br /><br /><br /><br /><h3> </h3><h3> </h3><h3> </h3><h3> </h3><h3> </h3><h3> Binding Project Properties to Build Configurations</h3>Similarly, there are some Build, Deploy and debugging properties of the project that can be assigned using build configurations. For that go properties of the project and select the the properties and the configuration and assigned the desired value.<br /><br /><div class="wlWriterEditableSmartContent" id="scid:8747F07C-CDE8-481f-B0DF-C6CFD074BF67:11656e6d-1eda-4510-bf76-014e4207cc0b" style="display: inline; float: none; margin: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;"><div class="separator" style="clear: both; text-align: center;"><a href="http://lh4.ggpht.com/-o6e9AriXr0s/Tv6xv_16X-I/AAAAAAAAAUs/zCNuzPDlUJg/12-31-2011%25252012-50-02%252520AM-8x6.jpg?imgmax=800" rel="thumbnail" style="margin-left: 1em; margin-right: 1em;" title="Just like in SSRS or SSAS projects"><img border="0" height="436" src="http://lh5.ggpht.com/-Su_0vBT2WnY/Tv6xxAAqs8I/AAAAAAAAAU0/S-3OT2kV3xk/12-31-2011%25252012-50-02%252520AM%25255B3%25255D.png?imgmax=800" width="580" /></a></div></div><br /><br />Lastly, keep in mind that build configurations are not new to SSIS 2012 projects, but I honestly did not find them helpful until now.<div class="feedflare">
<a href="http://feeds.feedburner.com/~ff/RafaelS?a=YVNkZXnZijQ:lCSdwuFBVSE:yIl2AUoC8zA"><img src="http://feeds.feedburner.com/~ff/RafaelS?d=yIl2AUoC8zA" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=YVNkZXnZijQ:lCSdwuFBVSE:63t7Ie-LG7Y"><img src="http://feeds.feedburner.com/~ff/RafaelS?d=63t7Ie-LG7Y" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=YVNkZXnZijQ:lCSdwuFBVSE:-BTjWOF_DHI"><img src="http://feeds.feedburner.com/~ff/RafaelS?i=YVNkZXnZijQ:lCSdwuFBVSE:-BTjWOF_DHI" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=YVNkZXnZijQ:lCSdwuFBVSE:V_sGLiPBpWU"><img src="http://feeds.feedburner.com/~ff/RafaelS?i=YVNkZXnZijQ:lCSdwuFBVSE:V_sGLiPBpWU" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=YVNkZXnZijQ:lCSdwuFBVSE:gIN9vFwOqvQ"><img src="http://feeds.feedburner.com/~ff/RafaelS?i=YVNkZXnZijQ:lCSdwuFBVSE:gIN9vFwOqvQ" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=YVNkZXnZijQ:lCSdwuFBVSE:I97M6haO00k"><img src="http://feeds.feedburner.com/~ff/RafaelS?d=I97M6haO00k" border="0"></img></a>
</div><img src="http://feeds.feedburner.com/~r/RafaelS/~4/YVNkZXnZijQ" height="1" width="1" alt=""/>Rafael Salashttps://plus.google.com/116687955206452017583noreply@blogger.com5http://www.rafael-salas.com/2012/01/ssis-2012-project-deployment-model-and.htmltag:blogger.com,1999:blog-29438013.post-24005450697821151762011-12-28T14:02:00.000-05:002011-12-31T01:53:45.598-05:00SSIS 2012: Parameters and Variables, what is the difference?Note: The information on this post is based SSIS 2012 RC0<br /><br />This is a question I ran across recently while browsing the <a href="http://social.msdn.microsoft.com/Forums/en-US/sqldenintegrationservices/thread/9e883460-278f-44fa-9f2a-b2ca265e1a22" target="_blank">SSIS 2012 pre-lease forum</a>. While there may be multiple ways to look at the differences and similarities among them, I thought I would give it a shot and share some of my observations. <a href="http://lh4.ggpht.com/-kvRmQqL0TrQ/Tvy5kJhPlpI/AAAAAAAAARs/OZVzvA8YRLo/s1600-h/IMAG0833%25255B6%25255D.jpg"><img align="right" alt="IMAG0833" border="0" height="225" src="http://lh3.ggpht.com/-cW2DNVFmSS4/Tvy5kqvIWxI/AAAAAAAAAR0/YwD5Ksni3CI/IMAG0833_thumb%25255B3%25255D.jpg?imgmax=800" style="background-image: none; border-bottom: 0px; border-left: 0px; border-right: 0px; border-top: 0px; display: inline; float: right; margin: 0px 0px 0px 9px; padding-left: 0px; padding-right: 0px; padding-top: 0px;" title="IMAG0833" width="328" /></a><br />If you are familiar with earlier versions of SSIS, it is worth noting that the concept and usability of variables have not changed much in the newer version. on the other hand, it &nbsp;is also important to point that <a href="http://www.rafael-salas.com/2010/11/denali-ctp-1-ssis-parameters-bring-them.html" target="_blank">parameters are one of the new features introduced in SSIS 2012</a> – available when using the new <strong><em>project deployment model</em></strong> - that promises to address some of the shortcomings of package configurations – which are still available.<br /><br /><h3> <span style="font-size: large;"> Scope</span></h3>Parameters can be defined at 2 different levels:<br /><blockquote>Project. These type of parameters are available to all packages within the SSIS project. Think about them as global parameters. They come handy as it makes really easy to share a given value, such as the path to a file share or the name of a server, across all packages in a project. </blockquote>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Package. These type of parameters are meant to affect only the package on which they were defined.<br /><br />Variables are richer in scope as they can be scoped at the package, container, task or event handler level.<br /><h3> </h3><h3> <span style="font-size: large;"> Execution Time behavior</span></h3><div><br /></div>The value of a parameter can’t change within the execution instance of a package. That means, its value remains the same for the entire execution of the package. Honestly, I don’t envision this being an issue in most cases.<br />On the other hand, the value of variables can change during the execution of the package, which make them suitable for scenarios where, for example,&nbsp; looping, conditional or any other type of logic where the values of the variable must change during the execution of the package.<br /><h3> </h3><h3> <span style="font-size: large;"> Consumption and Usage</span></h3><div><br /></div>Parameters are applied via expressions on the properties which are intended to be parameterized. The value of a parameter can be set at development time, after the package is deployed to the SSIS server (via SSMS) or at run time (SSMS or SS agent job) and seem the way to go to affect package execution <a href="http://lh4.ggpht.com/-W507ugj6wUA/Tvy5k0Z0XSI/AAAAAAAAAR8/UXrcE39aRWU/s1600-h/image%25255B7%25255D.png"><img align="right" alt="image" border="0" height="236" src="http://lh4.ggpht.com/-RaBEGzyeAWY/Tvy5lTyW-EI/AAAAAAAAASE/jiBC3qDBLDQ/image_thumb%25255B5%25255D.png?imgmax=800" style="background-image: none; border-bottom-color: initial; border-bottom-style: initial; border-bottom-width: 0px; border-left-color: initial; border-left-style: initial; border-left-width: 0px; border-right-color: initial; border-right-style: initial; border-right-width: 0px; border-top-color: initial; border-top-style: initial; border-top-width: 0px; display: inline; float: right; margin-bottom: 0px; margin-left: 12px; margin-right: 0px; margin-top: 12px; padding-left: 0px; padding-right: 0px; padding-top: 0px;" title="image" width="400" /></a>without having to modify the package. At design time, you can quickly get access to the “parameterize” window by right clicking on a task, container or connection manager, which saves few click when compared to using the expression property of the object. Additionally, parameter values can be set via execute package task, when a package is called from another package.<br /><br />The value of a variable can be set in many ways within a package and its value can be further referenced by other expressions, containers, tasks or components.<br />Note: Expressions are not new to this version of SSIS and something you should put in your short list of things to learn if you are serious about SSIS. <br /><h3> </h3><h3> <span style="font-size: large;"> Data types</span></h3><div><br /></div>When compared to variables, parameters do not support the following data types: Char, DBNull, Object.<br /><h3> </h3><h3> Visual Studio Configuration Manager</h3><div><br /></div><a href="http://lh5.ggpht.com/-FL1FXQPq2j0/Tvy5lyZdhVI/AAAAAAAAASM/DWWOsv-e74o/s1600-h/SNAGHTML1ac6e626%25255B5%25255D.png"><img align="left" alt="SNAGHTML1ac6e626" border="0" height="219" src="http://lh5.ggpht.com/-51SaqDT_GmM/Tvy5m2ahMtI/AAAAAAAAASU/A76DPStwbjQ/SNAGHTML1ac6e626_thumb%25255B2%25255D.png?imgmax=800" style="background-image: none; border-bottom-color: initial; border-bottom-style: initial; border-bottom-width: 0px; border-left-color: initial; border-left-style: initial; border-left-width: 0px; border-right-color: initial; border-right-style: initial; border-right-width: 0px; border-top-color: initial; border-top-style: initial; border-top-width: 0px; display: inline; float: left; margin-bottom: 0px; margin-left: 0px; margin-right: 24px; margin-top: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;" title="SNAGHTML1ac6e626" width="400" /></a>One of the nicest improvements I have seen in&nbsp; the RC0 build is the ability to bind parameters to the design time configurations offered by SQL Server Database Tools (formerly known as BIDS), which comes very handy when designing and&nbsp; and debugging packages in <strike>Visual Studio </strike>in SQL Server Data Tools.<br /><br /><h3> <span style="color: black;"><br /></span></h3><h3> <span style="color: black;"><br /></span></h3><h3> <span style="color: black; font-size: large;">Conclusion</span></h3><div><span style="color: black;"><br /></span></div>Parameters are the new kids on the block, and it is clear they are being treated as 1st class citizen within the new project deployment model. Although variables and parameters&nbsp; have some similarities, trying to find out which one is better may not be the right thing to do. In my opinion, it is more a matter of understanding their capabilities and&nbsp; choosing the right tool for the job. <br />I am pretty sure there may be more aspects to parameters and variables&nbsp; than the ones I listed here, so feel free to leave your feedback in the comments below with anything I may missed.<div class="feedflare">
<a href="http://feeds.feedburner.com/~ff/RafaelS?a=Veo_-wJS1jw:oGI36HCHCNQ:yIl2AUoC8zA"><img src="http://feeds.feedburner.com/~ff/RafaelS?d=yIl2AUoC8zA" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=Veo_-wJS1jw:oGI36HCHCNQ:63t7Ie-LG7Y"><img src="http://feeds.feedburner.com/~ff/RafaelS?d=63t7Ie-LG7Y" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=Veo_-wJS1jw:oGI36HCHCNQ:-BTjWOF_DHI"><img src="http://feeds.feedburner.com/~ff/RafaelS?i=Veo_-wJS1jw:oGI36HCHCNQ:-BTjWOF_DHI" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=Veo_-wJS1jw:oGI36HCHCNQ:V_sGLiPBpWU"><img src="http://feeds.feedburner.com/~ff/RafaelS?i=Veo_-wJS1jw:oGI36HCHCNQ:V_sGLiPBpWU" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=Veo_-wJS1jw:oGI36HCHCNQ:gIN9vFwOqvQ"><img src="http://feeds.feedburner.com/~ff/RafaelS?i=Veo_-wJS1jw:oGI36HCHCNQ:gIN9vFwOqvQ" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=Veo_-wJS1jw:oGI36HCHCNQ:I97M6haO00k"><img src="http://feeds.feedburner.com/~ff/RafaelS?d=I97M6haO00k" border="0"></img></a>
</div><img src="http://feeds.feedburner.com/~r/RafaelS/~4/Veo_-wJS1jw" height="1" width="1" alt=""/>Rafael Salashttps://plus.google.com/116687955206452017583noreply@blogger.com9http://www.rafael-salas.com/2011/12/ssis-2012-parameters-and-variables-what.htmltag:blogger.com,1999:blog-29438013.post-23429635034291199502011-05-10T09:55:00.001-04:002011-05-10T09:55:57.594-04:00SQL Rally 2011 is this week!<p><a href="http://www.sqlpass.org/sqlrally/2011/orlando/"><img style="background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: inline; float: right; border-top: 0px; border-right: 0px; padding-top: 0px" title="SQLRally_Banner_240x120_2" border="0" alt="SQLRally_Banner_240x120_2" align="right" src="http://lh4.ggpht.com/_6PiGGMKTQ7I/TclD7NMC9iI/AAAAAAAAARQ/ngamKQvnYx4/SQLRally_Banner_240x120_2%5B3%5D.jpg?imgmax=800" width="124" height="244"></a>SQL Rally inaugural edition is in Orlando, FL this week and&nbsp; I am delighted to be attending as speaker. This will be a great opportunity for me to get good technical content and to catch up with old friends and meet new people. This conference comes with 2 special bonuses for me: I will not have to travel to the other side of the country – big savings there! - and will get to enjoy of Orlando's warmer weather.</p> <p>What is my session about? I am glad you asked. I will be talking about one of my favorite topics: SSIS and ETL architecture, this time from a sinister angle:</p> <p><strong><a href="http://www.sqlpass.org/sqlrally/2011/orlando/Agenda/Sessions/SessionsBI.aspx#session836" target="_blank">Helping Your Data Warehouse Succeed: 10 Mistakes to Avoid in Data Integration</a></strong></p> <p>If you happen to be attending and&nbsp; are interested in learning from my mistakes, please feel free to attend this session.</p> <p>&nbsp;</p> <p>&nbsp;</p> <p><a href="http://www.sqlpass.org/sqlrally/2011/orlando/"><img style="background-image: none; border-bottom: 0px; border-left: 0px; margin: 1px auto 0px; padding-left: 0px; padding-right: 0px; display: block; float: none; border-top: 0px; border-right: 0px; padding-top: 0px" title="SQLRally_Banner_728x90" border="0" alt="SQLRally_Banner_728x90" src="http://lh6.ggpht.com/_6PiGGMKTQ7I/TclD7ekLlxI/AAAAAAAAARU/-CrlNiDadS4/SQLRally_Banner_728x90%5B4%5D.jpg?imgmax=800" width="469" height="78"></a></p> <div class="feedflare">
<a href="http://feeds.feedburner.com/~ff/RafaelS?a=hrcTtdBXuCY:CQRuUOH1vjA:yIl2AUoC8zA"><img src="http://feeds.feedburner.com/~ff/RafaelS?d=yIl2AUoC8zA" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=hrcTtdBXuCY:CQRuUOH1vjA:63t7Ie-LG7Y"><img src="http://feeds.feedburner.com/~ff/RafaelS?d=63t7Ie-LG7Y" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=hrcTtdBXuCY:CQRuUOH1vjA:-BTjWOF_DHI"><img src="http://feeds.feedburner.com/~ff/RafaelS?i=hrcTtdBXuCY:CQRuUOH1vjA:-BTjWOF_DHI" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=hrcTtdBXuCY:CQRuUOH1vjA:V_sGLiPBpWU"><img src="http://feeds.feedburner.com/~ff/RafaelS?i=hrcTtdBXuCY:CQRuUOH1vjA:V_sGLiPBpWU" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=hrcTtdBXuCY:CQRuUOH1vjA:gIN9vFwOqvQ"><img src="http://feeds.feedburner.com/~ff/RafaelS?i=hrcTtdBXuCY:CQRuUOH1vjA:gIN9vFwOqvQ" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=hrcTtdBXuCY:CQRuUOH1vjA:I97M6haO00k"><img src="http://feeds.feedburner.com/~ff/RafaelS?d=I97M6haO00k" border="0"></img></a>
</div><img src="http://feeds.feedburner.com/~r/RafaelS/~4/hrcTtdBXuCY" height="1" width="1" alt=""/>Rafael Salashttps://plus.google.com/116687955206452017583noreply@blogger.com0http://www.rafael-salas.com/2011/05/sql-rally-2011-is-this-week.htmltag:blogger.com,1999:blog-29438013.post-80181233512594975532011-03-16T08:54:00.001-04:002011-03-16T08:54:32.690-04:00Are you attending SQL Saturday #70 in Columbia, SC ?<p>This is a quick reminder: <a href="http://www.sqlsaturday.com/70/eventhome.aspx" target="_blank">SQL Saturday #70</a> is this coming Saturday March 19th in Columbia, SC. <a href="http://www.sqlsaturday.com/70/schedule.aspx" target="_blank">The schedule</a> promises to be a day full of exciting sessions and good speakers.<a href="http://lh6.ggpht.com/_6PiGGMKTQ7I/TYCzBsWbJ8I/AAAAAAAAARI/pUH9Ns8U00k/s1600-h/image%5B2%5D.png"><img style="background-image: none; border-right-width: 0px; margin: 20px 0px 0px 34px; padding-left: 0px; padding-right: 0px; display: inline; float: right; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px" title="image" border="0" alt="image" align="right" src="http://lh5.ggpht.com/_6PiGGMKTQ7I/TYCzCPp00HI/AAAAAAAAARM/I324j8V1dyw/image_thumb.png?imgmax=800" width="244" height="101"></a></p> <p>BI seems to get increasing tractions at this type of events, and this year there are 3 tracks dedicated to BI topics. I have been given the honor of delivering a session:&nbsp; </p> <p><em><strong>DW-ETL: 10 Mistakes to Avoid</strong></em></p> <p><em>With data being the focal point of a data warehouse, its data integration component becomes a critical point of failure of the whole system, and to make matters worse, we, as developers, face increasingly complex scenarios and requirements such as larger number of systems and volumes of data, data quality problems and complex business logic.&nbsp;&nbsp; In this session Rafael will share a compilation of pitfalls and mistakes he has seen –and made!– across many implementations, and offers recommendations to avoid them. We will look at problems related to metadata, data quality, monitoring, system reliability and some SSIS specific issues among other common problems. </em></p> <p>Ah! and if you haven’t registered yet, I encourage you to do it right away (<a href="http://www.sqlsaturday.com/register.aspx" target="_blank">registration link</a>). Last thing, I heard is that space is running out. The cost for attending is about right: Free!</p> <div class="feedflare">
<a href="http://feeds.feedburner.com/~ff/RafaelS?a=SCIPfBli2cw:I2TpL9bJcm4:yIl2AUoC8zA"><img src="http://feeds.feedburner.com/~ff/RafaelS?d=yIl2AUoC8zA" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=SCIPfBli2cw:I2TpL9bJcm4:63t7Ie-LG7Y"><img src="http://feeds.feedburner.com/~ff/RafaelS?d=63t7Ie-LG7Y" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=SCIPfBli2cw:I2TpL9bJcm4:-BTjWOF_DHI"><img src="http://feeds.feedburner.com/~ff/RafaelS?i=SCIPfBli2cw:I2TpL9bJcm4:-BTjWOF_DHI" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=SCIPfBli2cw:I2TpL9bJcm4:V_sGLiPBpWU"><img src="http://feeds.feedburner.com/~ff/RafaelS?i=SCIPfBli2cw:I2TpL9bJcm4:V_sGLiPBpWU" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=SCIPfBli2cw:I2TpL9bJcm4:gIN9vFwOqvQ"><img src="http://feeds.feedburner.com/~ff/RafaelS?i=SCIPfBli2cw:I2TpL9bJcm4:gIN9vFwOqvQ" border="0"></img></a> <a href="http://feeds.feedburner.com/~ff/RafaelS?a=SCIPfBli2cw:I2TpL9bJcm4:I97M6haO00k"><img src="http://feeds.feedburner.com/~ff/RafaelS?d=I97M6haO00k" border="0"></img></a>
</div><img src="http://feeds.feedburner.com/~r/RafaelS/~4/SCIPfBli2cw" height="1" width="1" alt=""/>Rafael Salashttps://plus.google.com/116687955206452017583noreply@blogger.com0http://www.rafael-salas.com/2011/03/are-you-attending-sql-saturday-70-in.html