tag:blogger.com,1999:blog-13789796081574290602024-03-13T15:44:58.083-04:00Another BI & Programming Blog - Jason YousefSharing my daily SQL and BI challenges, tips and tricks.Jason Yhttp://www.blogger.com/profile/01992333660672251175noreply@blogger.comBlogger126125tag:blogger.com,1999:blog-1378979608157429060.post-46158587365449428412022-05-24T10:25:00.003-04:002022-05-24T10:25:19.515-04:00SSRS Font Weight expressions<p><span style="font-family: arial;">Recently I was tasked to edit an old SSRS report, the report has multiple columns and I need to change the font weight to BOLD based on a specific value.</span></p><p><span style="font-family: arial;">It's so simple but annoying that it needs to be done manually to all the columns that you need the expression to check. which was 25 columns in my case!</span></p><p><span style="font-family: arial;">Steps:</span></p><p></p><ol style="text-align: left;"><li><span style="font-family: arial;">Right-click on the column and select "text Box Properties"</span></li><li><span style="font-family: arial;">Select "Font" from the left menu</span></li><li><span style="font-family: arial;">Next to BOLD click on the expression menu</span></li><li><span style="font-family: arial;">In the expression screen; write the expression based on your column and the condition.</span></li></ol><div><div class="separator" style="clear: both; text-align: center;"><div class="separator" style="clear: both; text-align: center;"><span style="font-family: arial;"><br /></span></div><span style="font-family: arial;"><br /><br /></span></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhy1DuINC_03-6-oX-TI8jLgsGYr1t0sudSaWgd_Twq-WJnntwzY-wGSPGG-ryFCIFnDFnB722wgGhJdrht4d2ke4rdsO7y_L4IMAQ0BoNXljr-O2Ilg92vrXi0w7y0OJzm463mtO9s2Q3yLNSSXU9-hXvubkLHQMGaxqGpcTGcjNpHxToz8Y1maJjq3g/s637/ssrs%20font%20weight%201.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><span style="font-family: arial;"><img border="0" data-original-height="541" data-original-width="637" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhy1DuINC_03-6-oX-TI8jLgsGYr1t0sudSaWgd_Twq-WJnntwzY-wGSPGG-ryFCIFnDFnB722wgGhJdrht4d2ke4rdsO7y_L4IMAQ0BoNXljr-O2Ilg92vrXi0w7y0OJzm463mtO9s2Q3yLNSSXU9-hXvubkLHQMGaxqGpcTGcjNpHxToz8Y1maJjq3g/s16000/ssrs%20font%20weight%201.PNG" /></span></a></div><span style="font-family: arial;"><br /><br /><br /><br /></span></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEioM0LAXMQN8od2GThxtaQbGZIrPTBUdLf9I-XrkcpSHdER-O1o7i0tmV8k0-R76c2eQe-rPriLoj-df6gKzLQUKWHsWUTAKJRsSx-ifq7A_daqkfAayjpJh-X6JynHtD1RlICgObG3GMTPxQfNPMYlLKnb_uJAH5hpEsOAf75WABfiLsZhYyvCHfwD4g/s912/ssrs%20font%20weight%202.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><span style="font-family: arial;"><img border="0" data-original-height="616" data-original-width="912" height="432" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEioM0LAXMQN8od2GThxtaQbGZIrPTBUdLf9I-XrkcpSHdER-O1o7i0tmV8k0-R76c2eQe-rPriLoj-df6gKzLQUKWHsWUTAKJRsSx-ifq7A_daqkfAayjpJh-X6JynHtD1RlICgObG3GMTPxQfNPMYlLKnb_uJAH5hpEsOAf75WABfiLsZhYyvCHfwD4g/w640-h432/ssrs%20font%20weight%202.PNG" width="640" /></span></a></div><span style="font-family: arial;"><br /></span><p></p><p><span style="font-family: arial;">=IIF(Fields!JB.Value > 0, "Bold","Normal")</span></p><p style="background-color: white; border: 0px; box-sizing: inherit; clear: both; color: #232629; font-size: 15px; font-stretch: inherit; font-variant-east-asian: inherit; font-variant-numeric: inherit; line-height: inherit; margin-bottom: var(--s-prose-spacing); margin-left: 0px; margin-right: 0px; margin-top: 0px; padding: 0px; vertical-align: baseline;"><span style="font-family: arial;">So here I'm setting the Property "FontWeight" of the textbox to be "Bold" when the value is greater than 0, otherwise, you're setting it to "Normal".</span></p><p style="background-color: white; border: 0px; box-sizing: inherit; clear: both; color: #232629; font-size: 15px; font-stretch: inherit; font-variant-east-asian: inherit; font-variant-numeric: inherit; line-height: inherit; margin-bottom: var(--s-prose-spacing); margin-left: 0px; margin-right: 0px; margin-top: 0px; padding: 0px; vertical-align: baseline;"><span style="font-family: arial;"><br /></span></p><p style="background-color: white; border: 0px; box-sizing: inherit; clear: both; color: #232629; font-size: 15px; font-stretch: inherit; font-variant-east-asian: inherit; font-variant-numeric: inherit; line-height: inherit; margin-bottom: var(--s-prose-spacing); margin-left: 0px; margin-right: 0px; margin-top: 0px; padding: 0px; vertical-align: baseline;"><span style="font-family: arial;">hope that helps!</span></p>Jason Yhttp://www.blogger.com/profile/01992333660672251175noreply@blogger.com0tag:blogger.com,1999:blog-1378979608157429060.post-53781346180204171162022-04-08T08:50:00.025-04:002023-04-02T08:59:26.618-04:00Splunk - Mini Blog - Why learn Splunk after the age of 40! my Journey from MSBI to Splunk certified!<p><span style="font-family: arial;">Why would you learn Splunk! Why particularly I choose Splunk!</span></p><p><span style="font-family: arial;">In today's world, data is king! we know that as a BI developers already !! and cybersecurity is a critical component of every organization's IT infrastructure. As a business intelligence and data scientist developer, you may have heard of Splunk, a powerful software platform used for analyzing and monitoring machine-generated data.</span></p><p><span style="font-family: arial;">If you are looking to enhance your career and be open to new opportunities in the new hot trend of cybersecurity, learning Splunk and getting certified can be a game-changer for you, you still also can write dashboards, reports and alerts in Splunk! Yep!!</span></p><p><span style="font-family: arial;">So, why should you learn Splunk and get certified in it? Let's take a closer look at some of the reasons why Splunk is the future of cybersecurity.</span></p><p><span style="font-family: arial;"><b>Splunk is a Leader in the Cybersecurity Industry</b></span></p><p><span style="font-family: arial;">Splunk is one of the most widely used cybersecurity tools in the industry. It has been recognized as a leader in the Gartner Magic Quadrant for Security Information and Event Management (SIEM) for multiple years. Splunk's popularity is due to its ability to collect and analyze large amounts of data, providing organizations with valuable insights into their IT infrastructure's security posture.</span></p><p><span style="font-family: arial;"><b>Growing Demand for Splunk Professionals</b></span></p><p><span style="font-family: arial;">As cybersecurity becomes increasingly important, the demand for Splunk professionals is on the rise. According to a recent report by Burning Glass Technologies, job postings for Splunk professionals have grown by over 70% in the past year alone. This trend is expected to continue as more organizations recognize the importance of cybersecurity and the role that Splunk can play in it.</span></p><p><span style="font-family: arial;"><b>Splunk Offers a Variety of Job Opportunities</b></span></p><p><span style="font-family: arial;">Learning Splunk opens up a world of job opportunities in the cybersecurity industry. Some of the roles that you can pursue with a Splunk certification include Security Engineer, Security Analyst, Security Architect, and Security Operations Center (SOC) Analyst. These roles offer competitive salaries, and the demand for Splunk professionals continues to grow.</span></p><p><span style="font-family: arial;"><b>Enhance Your Data Analytics Skills</b></span></p><p><span style="font-family: arial;">Splunk is not just a cybersecurity tool; it is also a powerful data analytics platform. Learning Splunk can help you develop your data analytics skills, including data collection, processing, analysis, and visualization. These skills are highly sought after in the business intelligence and data science industries.</span></p><p><span style="font-family: arial;"><b>Splunk is Open-Source and Easy to Learn</b></span></p><p><span style="font-family: arial;">Splunk is an open-source platform, which means that anyone can download and use it for free. Additionally, Splunk offers a comprehensive set of online training courses that are designed to help users learn the platform quickly and efficiently. These courses cover everything from the basics of Splunk to advanced topics like Splunk Enterprise Security and Splunk Machine Learning Toolkit.</span></p><p><span style="font-family: arial;">In <b>conclusion</b>, learning Splunk and getting certified in it can be an excellent career move for business intelligence and data scientist developers who want to expand their skillset and be open to new opportunities in the growing field of cybersecurity. </span></p><p><span style="font-family: arial;">As more organizations recognize the importance of cybersecurity and the role that Splunk can play in it, the demand for Splunk professionals will only continue to grow. By learning Splunk, you can position yourself for success in this exciting and rapidly growing industry.</span></p><p><span style="font-family: arial;"><br /></span></p>Jason Yhttp://www.blogger.com/profile/01992333660672251175noreply@blogger.com0tag:blogger.com,1999:blog-1378979608157429060.post-46528022492308371272022-04-06T08:39:00.043-04:002023-04-02T08:47:59.622-04:00Splunk - Mini Blog - How to learn Splunk after the age of 40! my Journey to get Splunk certified!<p><span style="font-family: arial;">Learning Splunk, or any new technology, can be challenging at any age, but it's never too late to start. In this series I'll blog and document my personal experience at age of 40 to learn about Splunk to get certified to further enhance my career</span></p><p><span style="font-family: arial;">Here are some tips that can help you learn Splunk quickly and effectively:</span></p><p></p><ul style="text-align: left;"><li><span style="font-family: arial;">Start with the basics: Familiarize yourself with the Splunk architecture and its core concepts, such as indexing, search, and reporting.</span></li><ul><li><span style="font-family: arial;">Why?</span></li><li><span style="font-family: arial;">How?</span></li><li><span style="font-family: arial;">When?</span></li></ul></ul><ul style="text-align: left;"><li><span style="font-family: arial;">Get hands-on experience: Splunk is a tool that can be learned best by doing. Create your own instances and practice with sample data to get a feel for how it works.</span></li><ul><li><span style="font-family: arial;">How?</span></li><li><span style="font-family: arial;">Cost?</span></li><li><span style="font-family: arial;">When?</span></li></ul></ul><ul style="text-align: left;"><li><span style="font-family: arial;">Take online courses: There are many online courses and tutorials available that can help you learn Splunk quickly. Consider taking an online course to get a comprehensive understanding of the tool.</span></li><ul><li><span style="font-family: arial;">Where?</span></li><li><span style="font-family: arial;">Cost?</span></li><li><span style="font-family: arial;">When?</span></li></ul></ul><ul style="text-align: left;"><li><span style="font-family: arial;">Join the Splunk Community: The Splunk community is a great resource for learning and getting support. Join the Splunk community forums and attend webinars to stay updated with the latest developments and learn from experts.</span></li><ul><li><span style="font-family: arial;">Immerse yourself!</span></li><li><span style="font-family: arial;">How?</span></li><li><span style="font-family: arial;">When?</span></li></ul></ul><ul style="text-align: left;"><li><span style="font-family: arial;">Practice, practice, practice: The more you use Splunk, the more comfortable you will become with it. Consider taking on a small project or working on real-life scenarios to get hands-on experience.</span></li><ul><li><span style="font-family: arial;">How?</span></li><li><span style="font-family: arial;">What?</span></li><li><span style="font-family: arial;">When?</span></li><li><span style="font-family: arial;">Cost?</span></li></ul></ul><ul style="text-align: left;"><li><span style="font-family: arial;">Get certified: Splunk offers several certification programs, such as the Splunk Certified Power User, Splunk Certified Admin, and Splunk Certified Architect. These certifications can help validate your skills and demonstrate your proficiency in Splunk.</span></li><ul><li><span style="font-family: arial;">What?</span></li><li><span style="font-family: arial;">How?</span></li><li><span style="font-family: arial;">When?</span></li><li><span style="font-family: arial;">Am I ready?</span></li><li><span style="font-family: arial;">Cost?</span></li><li><span style="font-family: arial;">Certification path?</span></li><li><span style="font-family: arial;">What's next?</span></li></ul></ul><p></p><p><span style="font-family: arial;">Remember, learning a new technology can be challenging, but with perseverance and dedication, you can achieve your goal.</span></p><p><span style="font-family: arial;"><br /></span></p><p><span style="font-family: arial;">In the next steps I'll elaborate on each of the bullet points, I'll blog step by step what I did and how to follow my foot steps.</span></p><p><br /></p>Jason Yhttp://www.blogger.com/profile/01992333660672251175noreply@blogger.com0tag:blogger.com,1999:blog-1378979608157429060.post-3446388960452734152017-07-01T22:30:00.000-04:002018-07-01T22:31:36.785-04:00Power query for ExcelI was looking for the download link for office 2013, here it is if you ever need it.<br />
<br />
Download from here :<br />
<br />
<a href="https://www.microsoft.com/en-us/download/details.aspx?id=39379" target="_blank">https://www.microsoft.com/en-us/download/details.aspx?id=39379</a>Anonymoushttp://www.blogger.com/profile/11002295961186057290noreply@blogger.com0tag:blogger.com,1999:blog-1378979608157429060.post-90704932599715815012017-06-15T15:20:00.001-04:002017-06-15T15:20:50.048-04:00Open SSRS Linked URLS in a new window<span style="font-family: Arial, Helvetica, sans-serif;">Greetings....as I mentioned before sometimes I use my blog posts as a way to remind myself of the stuff that I need frequently and never keep to memory.</span><div>
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span></div>
<div>
<span style="font-family: Arial, Helvetica, sans-serif;">I needed to add a link in a new SSRS report and the link has to open in a new browser's window.</span></div>
<div>
<div>
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span></div>
<div>
<span style="font-family: Arial, Helvetica, sans-serif;">Just Right click on your report field, select "Text Box Properties", select "Action", click on the "FX" sign to write that special expression.</span></div>
<div>
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span></div>
<div>
<br /></div>
<blockquote class="tr_bq">
<span style="background-color: yellow; font-family: Arial, Helvetica, sans-serif;">="javascript:void(window.open('" + Fields!URL.Value + "','_blank'))"</span></blockquote>
<div>
<span style="background-color: yellow; font-family: Arial, Helvetica, sans-serif;"><br /></span></div>
<div>
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span></div>
<div>
<span style="font-family: Arial, Helvetica, sans-serif;">Assuming that your URL is dynamic and the column name is "URL" in your dataset</span></div>
<div>
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span></div>
<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="https://3.bp.blogspot.com/-4dfv6QcpgpM/WULdAUVrYeI/AAAAAAAABTQ/Q1o4K8IAdSQWs50Y624VeRC_-RX2CVHSwCLcBGAs/s1600/SSRS%2Blink%2Bin%2Bnew%2Bwindow.PNG" imageanchor="1" style="margin-left: auto; margin-right: auto;"><span style="font-family: Arial, Helvetica, sans-serif;"><img border="0" data-original-height="688" data-original-width="889" height="247" src="https://3.bp.blogspot.com/-4dfv6QcpgpM/WULdAUVrYeI/AAAAAAAABTQ/Q1o4K8IAdSQWs50Y624VeRC_-RX2CVHSwCLcBGAs/s320/SSRS%2Blink%2Bin%2Bnew%2Bwindow.PNG" width="320" /></span></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;"><span style="font-family: Arial, Helvetica, sans-serif;">SSRS Link in new Browser's Window</span></td></tr>
</tbody></table>
<div>
<span style="font-family: Arial, Helvetica, sans-serif;">Hope that helps...</span></div>
</div>
Anonymoushttp://www.blogger.com/profile/11002295961186057290noreply@blogger.com0tag:blogger.com,1999:blog-1378979608157429060.post-58212043044914015282014-04-15T16:27:00.001-04:002014-04-15T16:27:22.836-04:00T-SQL Month Name,Number,Start and End of month<div dir="ltr" style="text-align: left;" trbidi="on">
<span style="font-family: Arial, Helvetica, sans-serif;">Hi,</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">Today's requirement is to create a month's table, to be used in a SSRS report parameters, to be passed to a stored procedure.</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">the parameters needs to have Month Name, Number, Abbreviation, Start and End of month.</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-xLxZ66oQ0O8/U02U4e1wL4I/AAAAAAAACbc/J_FGwup6Mn4/s1600/MonthName.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><span style="font-family: Arial, Helvetica, sans-serif;"><img border="0" src="http://2.bp.blogspot.com/-xLxZ66oQ0O8/U02U4e1wL4I/AAAAAAAACbc/J_FGwup6Mn4/s1600/MonthName.png" height="197" width="400" /></span></a></div>
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">Please find the T-sql code below:</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<br />
<div class="csharpcode">
<pre class="alt"><i><span style="color: blue; font-family: Arial, Helvetica, sans-serif;">;<span class="kwrd">WITH</span> months (MonthNumber)</span></i></pre>
<pre><i><span style="color: blue; font-family: Arial, Helvetica, sans-serif;"><span class="kwrd">AS</span> (<span class="kwrd">SELECT</span> 1</span></i></pre>
<pre class="alt"><i><span style="color: blue; font-family: Arial, Helvetica, sans-serif;"> <span class="kwrd">UNION</span> <span class="kwrd">ALL</span></span></i></pre>
<pre><i><span style="color: blue; font-family: Arial, Helvetica, sans-serif;"> <span class="kwrd">SELECT</span> MonthNumber + 1</span></i></pre>
<pre class="alt"><i><span style="color: blue; font-family: Arial, Helvetica, sans-serif;"> <span class="kwrd">FROM</span> months</span></i></pre>
<pre><i><span style="color: blue; font-family: Arial, Helvetica, sans-serif;"> <span class="kwrd">WHERE</span> MonthNumber < 12)</span></i></pre>
<pre class="alt"><i><span style="color: blue; font-family: Arial, Helvetica, sans-serif;"><span class="kwrd">SELECT</span> MonthNumber,</span></i></pre>
<pre><i><span style="color: blue; font-family: Arial, Helvetica, sans-serif;"> DATENAME(<span class="kwrd">month</span>, DATEADD(<span class="kwrd">month</span>, MonthNumber, -1)) <span class="kwrd">AS</span> [MonthName],</span></i></pre>
<pre class="alt"><i><span style="color: blue; font-family: Arial, Helvetica, sans-serif;"> <span class="kwrd">UPPER</span>(<span class="kwrd">LEFT</span>(DATENAME(<span class="kwrd">month</span>, DATEADD(<span class="kwrd">month</span>, MonthNumber, -1)), 3)) <span class="kwrd">AS</span> MonthAbbr,</span></i></pre>
<pre><i><span style="color: blue; font-family: Arial, Helvetica, sans-serif;"> DATEADD(<span class="kwrd">month</span>, DATEDIFF(<span class="kwrd">month</span>, 0, <span class="kwrd">CONVERT</span> (<span class="kwrd">VARCHAR</span> (2), MonthNumber) + <span class="str">'/1/'</span> + <span class="kwrd">CONVERT</span> (<span class="kwrd">VARCHAR</span> (4), <span class="kwrd">YEAR</span>(GETDATE()))), 0) <span class="kwrd">AS</span> StartOfMonth,</span></i></pre>
<pre class="alt"><i><span style="color: blue; font-family: Arial, Helvetica, sans-serif;"> DATEADD(d, -1, DATEADD(m, DATEDIFF(m, 0, <span class="kwrd">CONVERT</span> (<span class="kwrd">VARCHAR</span> (2), MonthNumber) + <span class="str">'/1/'</span> + <span class="kwrd">CONVERT</span> (<span class="kwrd">VARCHAR</span> (4), <span class="kwrd">YEAR</span>(GETDATE()))) + 1, 0)) <span class="kwrd">AS</span> EndOfMonth</span></i></pre>
<pre><i><span style="color: blue; font-family: Arial, Helvetica, sans-serif;"><span class="kwrd">FROM</span> months</span></i></pre>
<pre class="alt"><span style="font-family: Arial, Helvetica, sans-serif;"><i><span style="color: blue;"><span class="kwrd">ORDER</span> <span class="kwrd">BY</span> MonthNumber; </span></i> </span></pre>
<pre class="alt"><span style="font-family: Arial, Helvetica, sans-serif;">
</span></pre>
<pre class="alt"><span style="font-family: Arial, Helvetica, sans-serif;">
</span></pre>
</div>
<span style="font-family: Arial, Helvetica, sans-serif;">Hope that helps..</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;">Jason</span></div>
Jason Yhttp://www.blogger.com/profile/01992333660672251175noreply@blogger.com0tag:blogger.com,1999:blog-1378979608157429060.post-11687266949987750272013-09-01T14:10:00.000-04:002015-12-02T12:56:48.625-05:00SSIS trim leading zero from a string<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
<span style="font-family: "arial" , "helvetica" , sans-serif;">Today I was checking a question on one of the forums about trimming a leading zero from a string, and the string has characters, also has middle and ending zeros, so the methods that comes to your mind quickly won't work !! If you're thinking about converting to INT then to a string again !</span><br />
<span style="font-family: "arial" , "helvetica" , sans-serif;"></span><br />
<span style="font-family: "arial" , "helvetica" , sans-serif;">so I thought about a script component that uses the .NET (String.TrimStart Method) you can read about it more here : </span><a href="http://msdn.microsoft.com/en-us/library/system.string.trimstart.aspx"><span style="font-family: "arial" , "helvetica" , sans-serif;">http://msdn.microsoft.com/en-us/library/system.string.trimstart.aspx</span></a><br />
<span style="font-family: "arial" , "helvetica" , sans-serif;"></span><br />
<span style="font-family: "arial" , "helvetica" , sans-serif;">here's the input scenarios that I came with and the output results....</span><br />
<span style="font-family: "arial" , "helvetica" , sans-serif;"></span><br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://1.bp.blogspot.com/-6-q_GAGE0A4/UdsAd3yh1yI/AAAAAAAABMI/AaUgsUhO0Zc/s1600/7-8-2013+2-07-55+PM.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="162" src="http://1.bp.blogspot.com/-6-q_GAGE0A4/UdsAd3yh1yI/AAAAAAAABMI/AaUgsUhO0Zc/s320/7-8-2013+2-07-55+PM.png" width="320" /></a></div>
<br />
<span style="font-family: "arial";"></span><br />
<span style="font-family: "arial" , "helvetica" , sans-serif;"><u>Steps:</u></span><br />
<span style="font-family: "arial" , "helvetica" , sans-serif;"></span><br />
<span style="font-family: "arial" , "helvetica" , sans-serif;">1-I created a flat file (TXT) with punch of rows to test<br />2-Only one output from my flat file for simplicity, named Col0</span><span style="font-family: "arial" , "helvetica" , sans-serif;"><br />3-script component (transformation Type), using Col0 as input.</span><br />
<span style="font-family: "arial";"></span><br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://1.bp.blogspot.com/-kf2Xmuq_mYM/UjmA5qkpqdI/AAAAAAAAB0g/xR_YuxcU0dw/s1600/scripttask1.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="276" src="http://1.bp.blogspot.com/-kf2Xmuq_mYM/UjmA5qkpqdI/AAAAAAAAB0g/xR_YuxcU0dw/s320/scripttask1.png" width="320" /></a></div>
<span style="font-family: "arial" , "helvetica" , sans-serif;"><br />4-created an output column Col0Cleaned<br />
</span><br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://4.bp.blogspot.com/-862SCx4N-mo/UjmAyV8EWrI/AAAAAAAAB0Y/N90EPqwjpec/s1600/scripttask2.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="276" src="http://4.bp.blogspot.com/-862SCx4N-mo/UjmAyV8EWrI/AAAAAAAAB0Y/N90EPqwjpec/s320/scripttask2.png" width="320" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="http://3.bp.blogspot.com/-vayTDTc13UM/UjmA-wqsV_I/AAAAAAAAB0o/ys8jOzLbJw0/s1600/scripttask3.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="241" src="http://3.bp.blogspot.com/-vayTDTc13UM/UjmA-wqsV_I/AAAAAAAAB0o/ys8jOzLbJw0/s320/scripttask3.png" width="320" /></a></div>
<span style="font-family: "arial" , "helvetica" , sans-serif;"><br />5-added the above highlighted line... and that's' it!<br />
<br />
</span><br />
<span style="font-family: "arial" , "helvetica" , sans-serif;">Please let me know if you have any scenarios that this solutions couldn't fix!</span><br />
<span style="font-family: "arial" , "helvetica" , sans-serif;">package and solution can be downloaded from <a href="https://www.box.com/s/w54piamwkwldeko2i97v" target="_blank">here</a>....</span><br />
<span style="font-family: "arial" , "helvetica" , sans-serif;"></span><br />
<br />
<span style="font-family: "arial" , "helvetica" , sans-serif;">Hope that helps ......</span><br />
<span style="font-family: "arial" , "helvetica" , sans-serif;"><br /></span> </div>
Jason Yhttp://www.blogger.com/profile/01992333660672251175noreply@blogger.com0tag:blogger.com,1999:blog-1378979608157429060.post-11736818712506356882013-09-01T10:11:00.000-04:002013-09-18T06:18:58.557-04:00SSRS Strip Domain from User!UserID<div dir="ltr" style="text-align: left;" trbidi="on">
<span style="font-family: Arial, Helvetica, sans-serif;">In SSRS, if you want to be fancy and display the USER NAME in the header or footer of your reports, like what I do. SSRS uses Domain\UserName format... So someone this morning was asking in the forums about how to strip the domain out.</span><br />
<br />
<span style="font-family: Arial, Helvetica, sans-serif;">So I provided two solutions...</span><br />
<br />
<br />
<span style="font-family: Arial, Helvetica, sans-serif;">="Welcome " & MID(User!UserID,InStr(User!UserID,"\")+1, Len(User!UserID)) </span><br />
<br />
<span style="font-family: Arial, Helvetica, sans-serif;">="Welcome " & =right(User!UserID, InStr(StrReverse(User!UserID),"\")-1)</span><br />
<br />
<span style="font-family: Arial, Helvetica, sans-serif;">Hope that helps someone...</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /> </span><br />
<br /></div>
Jason Yhttp://www.blogger.com/profile/01992333660672251175noreply@blogger.com2tag:blogger.com,1999:blog-1378979608157429060.post-62646323352258014272013-04-30T08:50:00.002-04:002013-04-30T08:50:43.531-04:00SQL server date formats<div dir="ltr" style="text-align: left;" trbidi="on">
<span style="font-family: Arial, Helvetica, sans-serif;">Today, I've stumbled upon a nice resource of various SQL server date formats, and here I'm sharing it.</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"></span><br />
<a href="http://www.sql-server-helper.com/tips/date-formats.aspx"><span style="font-family: Arial, Helvetica, sans-serif;">http://www.sql-server-helper.com/tips/date-formats.aspx</span></a><br />
<span style="font-family: Arial, Helvetica, sans-serif;"></span><br />
<span style="font-family: Arial, Helvetica, sans-serif;">hope it helps when converting from one format to another !</span></div>
Jason Yhttp://www.blogger.com/profile/01992333660672251175noreply@blogger.com0tag:blogger.com,1999:blog-1378979608157429060.post-67132228896682120272013-04-17T09:38:00.000-04:002013-04-26T09:57:34.257-04:00Change SSIS Package Store location<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
SSIS stored the packages on 2 locations, on MSDB DB and on the file system.<br />
<br />
If you're using the file system, it's very important to make sure that your package store location <br />
points to the location of your packages.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-UJdJ_4yu4fA/UXqBnprw0jI/AAAAAAAABFQ/22hGw0KjrSU/s1600/SrvrBefore.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="220" src="http://2.bp.blogspot.com/-UJdJ_4yu4fA/UXqBnprw0jI/AAAAAAAABFQ/22hGw0KjrSU/s400/SrvrBefore.png" width="400" /></a></div>
<br />
The default packages location is:<br />
<br />
C:\Program Files\Microsoft SQL Server\90\DTS\Packages<br />
C:\Program Files\Microsoft SQL Server\100\DTS\Packages<br />
C:\Program Files\Microsoft SQL Server\110\DTS\Packages<br />
<br />
and the file that needs to be edited, by default located at :<br />
<br />
(2005) C:\Program Files\Microsoft SQL Server\90\DTS\Binn\MsDtsSrvr.ini.xml<br />
(2008) C:\Program Files\Microsoft SQL Server\100\DTS\Binn\MsDtsSrvr.ini.xml<br />
(2012) C:\Program Files\Microsoft SQL Server\110\DTS\Binn\MsDtsSrvr.ini.xml<br />
<br />
Just make sure to search for "MsDtsSrvr.ini.xml", you might have installed SQL server on a different <br />
drive or location.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://3.bp.blogspot.com/-q2ozfF6hc_k/UXqBtZvOFdI/AAAAAAAABFY/hiDg8WdKzyc/s1600/FieBefore.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="92" src="http://3.bp.blogspot.com/-q2ozfF6hc_k/UXqBtZvOFdI/AAAAAAAABFY/hiDg8WdKzyc/s400/FieBefore.png" width="400" /></a></div>
<br />
Edit that XML file to point to your current location....<br />
<br />
After you modify the service configuration file, you must restart the service to use the updated service configuration.<br />
<br />
Please refer to this post on how to restart the SSIS service. <a href="http://www.jasonyousef.com/2013/03/restart-ssis-service.html">http://www.jasonyousef.com/2013/03/restart-ssis-service.html</a><br />
<br />
now refresh your SSIS FIle System Folder.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-fvyZjLoaYvI/UXqHDVBp-pI/AAAAAAAABF4/azGyvuozi6A/s1600/SrvrAfter.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="400" src="http://2.bp.blogspot.com/-fvyZjLoaYvI/UXqHDVBp-pI/AAAAAAAABF4/azGyvuozi6A/s400/SrvrAfter.png" width="201" /></a></div>
<br />
Now, I can refer you to this article if you need to get the list of these packages <a href="http://www.simple-talk.com/sql/t-sql-programming/the-tsql-of-text-files/">http://www.simple-talk.com/sql/t-sql-programming/the-tsql-of-text-files/</a><br />
<br />
Hope that helps!</div>
Jason Yhttp://www.blogger.com/profile/01992333660672251175noreply@blogger.com0tag:blogger.com,1999:blog-1378979608157429060.post-64118664081714647212013-03-05T09:46:00.000-05:002013-04-26T09:46:26.199-04:00Restart SSIS Service<div dir="ltr" style="text-align: left;" trbidi="on">
If you need to check if the SSIS service is installed or if whether it's running or not.<br />
<br />
If you need to know more about the SSIS service, let me refer you to this post...<a href="http://www.ssistalk.com/2009/11/04/ssis-what-does-the-ssis-service-actually-do-anyway/">http://www.ssistalk.com/2009/11/04/ssis-what-does-the-ssis-service-actually-do-anyway/</a><br />
<br />
Anyway... <br />
<br />
You can pull the RUN command and type SERVICES.MSC and you'll get to the list of the locally installed services, or go to :<br />
CONTROL PANEL --> ADMINISTRATIVE TOOLS --> SERVICES<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://4.bp.blogspot.com/-1TAckndiQAw/UXqEfMGWszI/AAAAAAAABFk/LwmfS8jMr-M/s1600/services.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="312" src="http://4.bp.blogspot.com/-1TAckndiQAw/UXqEfMGWszI/AAAAAAAABFk/LwmfS8jMr-M/s320/services.png" width="320" /></a></div>
Search for SQL server Integration Services, by clicking on S or scrolling down.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-dHsBHKejaZY/UXqEnieCfxI/AAAAAAAABFs/BoMiT8WdxYM/s1600/services2.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="261" src="http://2.bp.blogspot.com/-dHsBHKejaZY/UXqEnieCfxI/AAAAAAAABFs/BoMiT8WdxYM/s320/services2.png" width="320" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
</div>
right click and you'll get all the options like START/RESTART/STOP.<br />
<br />
Hope that helps!</div>
Jason Yhttp://www.blogger.com/profile/01992333660672251175noreply@blogger.com0tag:blogger.com,1999:blog-1378979608157429060.post-15104725053013647012012-12-03T14:01:00.001-05:002012-12-05T11:20:25.189-05:00SSIS - Split records and export to flat files <div dir="ltr" style="text-align: left;" trbidi="on">
<span style="font-family: Arial, Helvetica, sans-serif;">Today's issue that we've a source database Sybase ASE, and old version is being used as a replica, I'm trying to extract some records, it's almost 750K to 1 Mil records and it needs to be exported to flat files and sent somewhere for further processing.</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"></span><br />
<span style="font-family: Arial, Helvetica, sans-serif;">The files has to be no more than 10K records a file and it needs to get a specific naming convention...</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"></span><br />
<span style="font-family: Arial, Helvetica, sans-serif;">I've done it differently than what I'm showing in the demo here... and I'll explain why...</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"></span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"></span><br />
<span style="font-family: Arial, Helvetica, sans-serif;">the main component in the package is the "For Loop Container", then inside the container I'll have a "Data Flow Task".</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"></span><br />
<span style="font-family: Arial, Helvetica, sans-serif;">I'll create 4 variables as shown below..</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"></span><br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-5iiX6166RdM/ULkYtTiN5II/AAAAAAAAAuo/qihVkfOuCns/s1600/Package.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><span style="font-family: Arial, Helvetica, sans-serif;"><img border="0" height="163" src="http://2.bp.blogspot.com/-5iiX6166RdM/ULkYtTiN5II/AAAAAAAAAuo/qihVkfOuCns/s400/Package.png" width="400" /></span></a></div>
<div class="separator" style="clear: both; text-align: center;">
<span style="font-family: Arial, Helvetica, sans-serif;"> </span></div>
<div class="separator" style="clear: both; text-align: center;">
<span style="font-family: Arial, Helvetica, sans-serif;"> </span></div>
<div class="separator" style="clear: both; text-align: center;">
<span style="font-family: Arial, Helvetica, sans-serif;"> </span></div>
<span style="font-family: Arial, Helvetica, sans-serif;">Add a "For Loop Container", configure as attached.. to use the variables and set the limit to 100K here</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"></span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"></span><br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-F5hN6PdzEKY/ULkivI_GljI/AAAAAAAAAvQ/1M2k8CkgmZI/s1600/ForLoop.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><span style="font-family: Arial, Helvetica, sans-serif;"><img border="0" height="338" src="http://2.bp.blogspot.com/-F5hN6PdzEKY/ULkivI_GljI/AAAAAAAAAvQ/1M2k8CkgmZI/s400/ForLoop.png" width="400" /></span></a></div>
<div class="separator" style="clear: both; text-align: center;">
<span style="font-family: Arial, Helvetica, sans-serif;"> </span></div>
<div class="separator" style="clear: both; text-align: center;">
<span style="font-family: Arial, Helvetica, sans-serif;"> </span></div>
<div class="separator" style="clear: both; text-align: center;">
<span style="font-family: Arial, Helvetica, sans-serif;"> </span></div>
<span style="font-family: Arial, Helvetica, sans-serif;"> Basically the first task, is a "Script Task", I'm only using it to debug my variables in a message box, enable or disable to debug, you could use "BreakPoints" which I've used in my original package and here only it's for demo.</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"></span><br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-Mv0HvSFEfRk/ULkiwXznlvI/AAAAAAAAAvY/K-YiyVdFNEc/s1600/Script1.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><span style="font-family: Arial, Helvetica, sans-serif;"><img border="0" height="271" src="http://2.bp.blogspot.com/-Mv0HvSFEfRk/ULkiwXznlvI/AAAAAAAAAvY/K-YiyVdFNEc/s320/Script1.png" width="320" /></span></a></div>
<div class="separator" style="clear: both; text-align: center;">
<span style="font-family: Arial, Helvetica, sans-serif;"> </span></div>
<span style="font-family: Arial, Helvetica, sans-serif;"> The way I'm debugging is so easy, just to display a message with the current variables' value to check if I'm heading in the right way! again use breakpoints, add a watch and check your variables values...</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"></span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"></span><br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://4.bp.blogspot.com/-EI1s7PBjGso/ULkixjCA28I/AAAAAAAAAvg/XNoApfQmHbY/s1600/Script2.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><span style="font-family: Arial, Helvetica, sans-serif;"><img border="0" height="179" src="http://4.bp.blogspot.com/-EI1s7PBjGso/ULkixjCA28I/AAAAAAAAAvg/XNoApfQmHbY/s320/Script2.png" width="320" /></span></a></div>
<div class="separator" style="clear: both; text-align: center;">
<span style="font-family: Arial, Helvetica, sans-serif;"> </span></div>
<span style="font-family: Arial, Helvetica, sans-serif;"> The data flow task...</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"></span><br />
<span style="font-family: Arial, Helvetica, sans-serif;">I'm using a data generator task, to create my input rows....you can use your own source tables of course, I was reading from a sybase ASE database, an old version which doesn't support the Row_Number() function, so I couldn't build a column for the row numbers, and yes...I thought about a SQL server staging table...yes but it wasn't an option !</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"></span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"></span><br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://1.bp.blogspot.com/-VFdVbdBK_AQ/ULkizOHF-DI/AAAAAAAAAvo/pwIhEVhnaNM/s1600/DFT1.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><span style="font-family: Arial, Helvetica, sans-serif;"><img border="0" height="230" src="http://1.bp.blogspot.com/-VFdVbdBK_AQ/ULkizOHF-DI/AAAAAAAAAvo/pwIhEVhnaNM/s320/DFT1.png" width="320" /></span></a></div>
<div class="separator" style="clear: both; text-align: center;">
<span style="font-family: Arial, Helvetica, sans-serif;"> </span></div>
<span style="font-family: Arial, Helvetica, sans-serif;"> I'm generating 520,000! yes weired number but I want to show the rest of the 20K records will get into a file of their own!</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"></span><br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-eVisQVJxSsE/ULkizzamreI/AAAAAAAAAvw/DyaxyDDfiLs/s1600/DFT2.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><span style="font-family: Arial, Helvetica, sans-serif;"><img border="0" height="274" src="http://2.bp.blogspot.com/-eVisQVJxSsE/ULkizzamreI/AAAAAAAAAvw/DyaxyDDfiLs/s320/DFT2.png" width="320" /></span></a></div>
<div class="separator" style="clear: both; text-align: center;">
<span style="font-family: Arial, Helvetica, sans-serif;"> </span></div>
<span style="font-family: Arial, Helvetica, sans-serif;"></span><br />
<span style="font-family: Arial, Helvetica, sans-serif;">Just a row count of all the rows , please note that it'll run 6 times and it's not efficient...</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;">You could use an "Execute sql task" to get you count(*) in the "Control Flow" and that will run 1 time, but here I'm using the data generator task....</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"></span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"></span><br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://1.bp.blogspot.com/-L4czg4L5Plw/ULki0jMW9hI/AAAAAAAAAv4/LR5Zut3zAiQ/s1600/DFT3.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><span style="font-family: Arial, Helvetica, sans-serif;"><img border="0" height="300" src="http://1.bp.blogspot.com/-L4czg4L5Plw/ULki0jMW9hI/AAAAAAAAAv4/LR5Zut3zAiQ/s320/DFT3.png" width="320" /></span></a></div>
<div class="separator" style="clear: both; text-align: center;">
<span style="font-family: Arial, Helvetica, sans-serif;"> </span></div>
<span style="font-family: Arial, Helvetica, sans-serif;">Here it's my optional "Script component" to create a Row Number column, again I'm using that because I'm using the data generator task..or if you're reading from a flat file directly or if Row_number is not an option and no staging SQL table either !</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"></span><br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-o6JwUma4YRU/ULki1gQkoOI/AAAAAAAAAwA/QuPGaMdI3Zc/s1600/DFT4.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><span style="font-family: Arial, Helvetica, sans-serif;"><img border="0" height="264" src="http://2.bp.blogspot.com/-o6JwUma4YRU/ULki1gQkoOI/AAAAAAAAAwA/QuPGaMdI3Zc/s320/DFT4.png" width="320" /></span></a></div>
<span style="font-family: Arial, Helvetica, sans-serif;"></span><br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://4.bp.blogspot.com/-AoxiwbMIPm0/ULki4k_ijiI/AAAAAAAAAwI/2uSEiTDIUBc/s1600/DFT5.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><span style="font-family: Arial, Helvetica, sans-serif;"><img border="0" height="258" src="http://4.bp.blogspot.com/-AoxiwbMIPm0/ULki4k_ijiI/AAAAAAAAAwI/2uSEiTDIUBc/s320/DFT5.png" width="320" /></span></a></div>
<div class="separator" style="clear: both; text-align: center;">
<span style="font-family: Arial, Helvetica, sans-serif;"> </span></div>
<span style="font-family: Arial, Helvetica, sans-serif;"> Conditional split to filter which rows where exactly need to write to our destination...</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"></span><br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://4.bp.blogspot.com/-fNdnvQQV4D0/ULki5xipwTI/AAAAAAAAAwQ/5Q7yCDNRDlw/s1600/DFT6.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><span style="font-family: Arial, Helvetica, sans-serif;"><img border="0" height="264" src="http://4.bp.blogspot.com/-fNdnvQQV4D0/ULki5xipwTI/AAAAAAAAAwQ/5Q7yCDNRDlw/s320/DFT6.png" width="320" /></span></a></div>
<span style="font-family: Arial, Helvetica, sans-serif;"></span><br />
<span style="font-family: Arial, Helvetica, sans-serif;">And here I'm setting the flat file destination..</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"></span><br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://1.bp.blogspot.com/-z3GyviV3Sco/ULki6_gTFOI/AAAAAAAAAwY/uscKGWGJJfI/s1600/DFT7.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><span style="font-family: Arial, Helvetica, sans-serif;"><img border="0" height="276" src="http://1.bp.blogspot.com/-z3GyviV3Sco/ULki6_gTFOI/AAAAAAAAAwY/uscKGWGJJfI/s320/DFT7.png" width="320" /></span></a></div>
<span style="font-family: Arial, Helvetica, sans-serif;"></span><br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-LuwjrISeq_I/ULkjSXNGVII/AAAAAAAAAwg/4a4uTyWd-zg/s1600/Empty+Folder.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"></a><span style="font-family: Arial, Helvetica, sans-serif;"> </span></div>
<span style="font-family: Arial, Helvetica, sans-serif;"></span><br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://4.bp.blogspot.com/-N14cZEX5X4U/ULkjTEfxkTI/AAAAAAAAAwo/l7uUwb6YWPA/s1600/FileConn1.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><span style="font-family: Arial, Helvetica, sans-serif;"><img border="0" height="288" src="http://4.bp.blogspot.com/-N14cZEX5X4U/ULkjTEfxkTI/AAAAAAAAAwo/l7uUwb6YWPA/s320/FileConn1.png" width="320" /></span></a></div>
<span style="font-family: Arial, Helvetica, sans-serif;"></span><br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-00X0L_bihRo/ULkjT_Bss_I/AAAAAAAAAww/wU2qG-keuwU/s1600/FileConn2.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><span style="font-family: Arial, Helvetica, sans-serif;"><img border="0" height="288" src="http://2.bp.blogspot.com/-00X0L_bihRo/ULkjT_Bss_I/AAAAAAAAAww/wU2qG-keuwU/s320/FileConn2.png" width="320" /></span></a></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-SmH_VHI7WsA/ULkjWwHPxQI/AAAAAAAAAxY/PS7YHGobs4M/s1600/UpdateCount1.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"></a><span style="font-family: Arial, Helvetica, sans-serif;"> </span></div>
<span style="font-family: Arial, Helvetica, sans-serif;"></span><br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-yUWqlsGLaV0/ULkjXYnDsJI/AAAAAAAAAxg/R_0I_XjmBAs/s1600/UpdateCount2.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><span style="font-family: Arial, Helvetica, sans-serif;"></span></a><br /></div>
<span style="font-family: Arial, Helvetica, sans-serif;"></span><br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-4Q5uHKcjNTk/ULkjgDzR8KI/AAAAAAAAAxo/ZaTUeU6Bd7A/s1600/DFT8.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><span style="font-family: Arial, Helvetica, sans-serif;"><img border="0" height="276" src="http://2.bp.blogspot.com/-4Q5uHKcjNTk/ULkjgDzR8KI/AAAAAAAAAxo/ZaTUeU6Bd7A/s320/DFT8.png" width="320" /></span></a></div>
<span style="font-family: Arial, Helvetica, sans-serif;"></span><br />
<span style="font-family: Arial, Helvetica, sans-serif;">I've an expression over the connection string to get me the file name dynamically to reflect which records in the file.</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"></span><br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://1.bp.blogspot.com/-uUIrpDQYCOM/ULkjUWO2CtI/AAAAAAAAAw4/8t49WaAiWMU/s1600/FileConn3.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><span style="font-family: Arial, Helvetica, sans-serif;"><img border="0" height="190" src="http://1.bp.blogspot.com/-uUIrpDQYCOM/ULkjUWO2CtI/AAAAAAAAAw4/8t49WaAiWMU/s320/FileConn3.png" width="320" /></span></a></div>
<div class="separator" style="clear: both; text-align: center;">
<span style="font-family: Arial, Helvetica, sans-serif;"> </span></div>
<div align="left" class="separator" style="clear: both; text-align: center;">
<span style="font-family: Arial, Helvetica, sans-serif;"></span></div>
<div class="separator" style="clear: both; text-align: left;">
<span style="font-family: Arial, Helvetica, sans-serif;">The last step here is a script task to update the variables' values...I'm not really sure why I've it in the control flow, but you can do it in the data flow task !</span></div>
<div class="separator" style="clear: both; text-align: center;">
<span style="font-family: Arial, Helvetica, sans-serif;"> </span></div>
<div class="separator" style="clear: both; text-align: center;">
<span style="font-family: Arial, Helvetica, sans-serif;"> </span></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="http://3.bp.blogspot.com/-Cc_vQ0EfSgw/ULkjV82oX8I/AAAAAAAAAxI/sg8kx29g_ws/s1600/Script1.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><span style="font-family: Arial, Helvetica, sans-serif;"><img border="0" height="271" src="http://3.bp.blogspot.com/-Cc_vQ0EfSgw/ULkjV82oX8I/AAAAAAAAAxI/sg8kx29g_ws/s320/Script1.png" width="320" /></span></a></div>
<div class="separator" style="clear: both; text-align: center;">
<span style="font-family: Arial, Helvetica, sans-serif;"> </span></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-yUWqlsGLaV0/ULkjXYnDsJI/AAAAAAAAAxg/R_0I_XjmBAs/s1600/UpdateCount2.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><span style="font-family: Arial, Helvetica, sans-serif;"><img border="0" height="199" src="http://2.bp.blogspot.com/-yUWqlsGLaV0/ULkjXYnDsJI/AAAAAAAAAxg/R_0I_XjmBAs/s320/UpdateCount2.png" width="320" /></span></a></div>
<div class="separator" style="clear: both; text-align: center;">
<span style="font-family: Arial, Helvetica, sans-serif;"> </span></div>
<div class="separator" style="clear: both; text-align: left;">
<span style="font-family: Arial, Helvetica, sans-serif;">That's it! the folder when it's empty...</span></div>
<div class="separator" style="clear: both; text-align: center;">
<span style="font-family: Arial, Helvetica, sans-serif;"> </span></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-LuwjrISeq_I/ULkjSXNGVII/AAAAAAAAAwg/4a4uTyWd-zg/s1600/Empty+Folder.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><span style="font-family: Arial, Helvetica, sans-serif;"><img border="0" height="190" src="http://2.bp.blogspot.com/-LuwjrISeq_I/ULkjSXNGVII/AAAAAAAAAwg/4a4uTyWd-zg/s320/Empty+Folder.png" width="320" /></span></a></div>
<div class="separator" style="clear: both; text-align: center;">
<span style="font-family: Arial, Helvetica, sans-serif;"> </span></div>
<div class="separator" style="clear: both; text-align: left;">
<span style="font-family: Arial, Helvetica, sans-serif;">The folder when it got the files !!</span></div>
<div class="separator" style="clear: both; text-align: center;">
<span style="font-family: Arial, Helvetica, sans-serif;"> </span></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="http://1.bp.blogspot.com/-5oYIunDNVAw/ULkjVKmQR8I/AAAAAAAAAxA/Mt_amxJWQaA/s1600/Files+in+the+folder.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><span style="font-family: Arial, Helvetica, sans-serif;"><img border="0" height="182" src="http://1.bp.blogspot.com/-5oYIunDNVAw/ULkjVKmQR8I/AAAAAAAAAxA/Mt_amxJWQaA/s320/Files+in+the+folder.png" width="320" /></span></a></div>
<div class="separator" style="clear: both; text-align: center;">
<span style="font-family: Arial, Helvetica, sans-serif;"> </span></div>
<span style="font-family: Arial, Helvetica, sans-serif;">Wrap up... again.. you don't need to debug your variables</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;">use the data generator task...</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;">use the row number task</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"> </span><br />
<span style="font-family: Arial, Helvetica, sans-serif;">or even the split task ..Yes !! if you're reading from your source table and not from a data generator task, you might build your query to use the 2 variables and get only rows where the RN between these 2 variables... I didn't illustrate that here for simplicity.</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"> </span><br />
<span style="font-family: Arial, Helvetica, sans-serif;">it'd look like that...</span><br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-yR3Fzq7m6Ck/UL9zqsnU4cI/AAAAAAAAAyY/f_Js3bjttcs/s1600/no+split.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><span style="font-family: Arial, Helvetica, sans-serif;"><img border="0" height="271" src="http://2.bp.blogspot.com/-yR3Fzq7m6Ck/UL9zqsnU4cI/AAAAAAAAAyY/f_Js3bjttcs/s320/no+split.png" width="320" /></span></a></div>
<div class="separator" style="clear: both; text-align: center;">
<span style="font-family: Arial, Helvetica, sans-serif;"></span> </div>
<span style="font-family: Arial, Helvetica, sans-serif;"> And yes I've an index on the RN column ...</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"></span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"></span><br />
<span style="font-family: Arial, Helvetica, sans-serif;">The last screen shot here is not meant for people with weak hearts !! it's scary!!</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"></span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"></span><br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://3.bp.blogspot.com/-g1jQpYmP5MY/UL9zsO3mnZI/AAAAAAAAAyg/-Dyyz3ZWfz0/s1600/Last.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><span style="font-family: Arial, Helvetica, sans-serif;"><img border="0" height="150" src="http://3.bp.blogspot.com/-g1jQpYmP5MY/UL9zsO3mnZI/AAAAAAAAAyg/-Dyyz3ZWfz0/s320/Last.png" width="320" /></span></a></div>
<span style="font-family: Arial, Helvetica, sans-serif;"></span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"> </span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"> </span><br />
<span style="font-family: Arial, Helvetica, sans-serif;">Hope I've explained it clearly... and hit me up if you have a better way of doing it...</span></div>
Jason Yhttp://www.blogger.com/profile/01992333660672251175noreply@blogger.com2tag:blogger.com,1999:blog-1378979608157429060.post-20327828618670396062012-11-21T11:25:00.000-05:002012-11-30T11:30:16.209-05:00NJSQL Presentation - Let's talk dynamic<div dir="ltr" style="text-align: left;" trbidi="on">
<span style="font-family: Arial, Helvetica, sans-serif;">Let's talk dynamic - SSIS variables and expressions</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"></span><br />
<span style="font-family: Arial, Helvetica, sans-serif;">So yesterday I was honrored to speak before the NJSQL group about "SSIS variables and expressions" I do apologize for my lengthy presentation as I went so deep into small details... but I was just trying to go with the flow, and naswering the questions...</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"></span><br />
<span style="font-family: Arial, Helvetica, sans-serif;">I've attached a RAR file, containing the slide deck along with the demo
scripts. I hope you get as much out of SQL Server
as I have, and Thanks for coming!</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"></span><br />
<span style="font-family: Arial, Helvetica, sans-serif;">The slides can downloaded from </span><a href="https://www.box.com/s/9lvnf2cy3mdbluy8ykqo"><span style="font-family: Arial, Helvetica, sans-serif;">https://www.box.com/s/9lvnf2cy3mdbluy8ykqo</span></a></div>
Jason Yhttp://www.blogger.com/profile/01992333660672251175noreply@blogger.com0tag:blogger.com,1999:blog-1378979608157429060.post-64803053396974094322012-11-16T10:27:00.003-05:002012-11-16T10:27:45.079-05:00SSRS - Subscription comments location.<div dir="ltr" style="text-align: left;" trbidi="on">
<span style="font-family: Arial, Helvetica, sans-serif;">Today I was facing a client's problem in SSRS to edit the comments text for over 120 reports to point to a server name (Host Name) instead of IP, to begin with, it was a bad idea and judgment from the person who created the report. and secondly it's rarely to see a server that gets an IP change too!</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"></span><br />
<span style="font-family: Arial, Helvetica, sans-serif;">But the two issues could happen and it's ok, let's face it and fix it.</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"></span><br />
<span style="font-family: Arial, Helvetica, sans-serif;">Here's a screen shot, If you're not familiar with the subscription comments.</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"></span><br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://4.bp.blogspot.com/-Nrcr4WqRBD8/UKZW423ZuOI/AAAAAAAAAtw/iWC5tRIkyZU/s1600/1.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><span style="font-family: Arial, Helvetica, sans-serif;"><img border="0" height="286" src="http://4.bp.blogspot.com/-Nrcr4WqRBD8/UKZW423ZuOI/AAAAAAAAAtw/iWC5tRIkyZU/s400/1.png" width="400" /></span></a></div>
<span style="font-family: Arial, Helvetica, sans-serif;"></span><br />
<span style="font-family: Arial, Helvetica, sans-serif;">To locate it, just navigate to SQL server management studio (SSMS), and browse the :</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"></span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><span style="color: red;"><strong>[ReportServer].[dbo].[Subscriptions]</strong> </span><span style="color: black;">Table</span></span><br />
<strong><span style="color: red; font-family: Arial, Helvetica, sans-serif;"></span></strong><br />
<span style="color: red;"><span style="font-family: Arial, Helvetica, sans-serif;"><span style="color: black;">the column that has the comments is</span><strong> [ExtensionSettings]</strong></span></span><br />
<strong><span style="color: red; font-family: Arial, Helvetica, sans-serif;"></span></strong><br />
<span style="color: black; font-family: Arial, Helvetica, sans-serif;">just replace the text using your T-SQL query and you're done! the data type of that column in nText, so it won't with T-SQL replace function.</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"></span><br />
<span style="font-family: Arial, Helvetica, sans-serif;">so you might use...</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"></span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"></span><br />
<code style="font-size: 12px;"><span style="color: black;"><br /></span><span style="color: blue; font-family: Arial, Helvetica, sans-serif;">UPDATE </span><span style="font-family: Arial, Helvetica, sans-serif;"><span style="color: black;">S<br /> </span><span style="color: blue;">SET </span><span style="color: black;">S.ExtensionSettings </span><span style="color: blue;">= </span></span><span style="font-family: Arial, Helvetica, sans-serif;"><span style="color: black;">T.ExtensionSettings <br /> </span><span style="color: blue;">FROM </span><span style="color: black;">[ReportServer].[dbo].[Subscriptions] </span><span style="color: blue;">AS </span></span><span style="font-family: Arial, Helvetica, sans-serif;"><span style="color: black;">S<br /> </span><span style="color: blue;">JOIN </span></span><span style="font-family: Arial, Helvetica, sans-serif;"><span style="color: grey;">(<br /> </span><span style="color: blue;">SELECT </span><span style="color: black;">[SubscriptionID]</span><span style="color: grey;">, </span><span style="color: magenta;">REPLACE</span><span style="color: grey;">(</span><span style="color: magenta;">CONVERT</span><span style="color: grey;">(</span><span style="color: blue;">VARCHAR</span><span style="color: grey;">(</span><span style="color: magenta;">MAX</span><span style="color: grey;">),</span><span style="color: black;">[ExtensionSettings]</span><span style="color: grey;">),</span><span style="color: red;">'162.162.162.162'</span><span style="color: grey;">,</span><span style="color: red;">'HostNameServer'</span><span style="color: grey;">) </span></span><span style="font-family: Arial, Helvetica, sans-serif;"><span style="color: black;">ExtensionSettings<br /> </span><span style="color: blue;">FROM </span><span style="color: black;">[ReportServer].[dbo].[Subscriptions]</span><span style="color: grey;">) </span><span style="color: blue;">AS </span></span><span style="font-family: Arial, Helvetica, sans-serif;"><span style="color: black;">T<br /> </span><span style="color: blue;">ON </span><span style="color: black;">S.SubscriptionID </span><span style="color: blue;">= </span><span style="color: black;">T.[SubscriptionID] </span></span></code><span style="font-family: Arial, Helvetica, sans-serif;">
</span><br />
<strong><span style="color: red;"></span></strong><span style="font-family: Arial, Helvetica, sans-serif;"> </span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"></span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"></span><br />
<span style="font-family: Arial, Helvetica, sans-serif;">Hope that helps someone...</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;">Jason</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"></span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"></span><br />
<span style="font-family: Arial, Helvetica, sans-serif;">Code has been formatted using...http://extras.sqlservercentral.com/prettifier/prettifier.aspx</span></div>
Jason Yhttp://www.blogger.com/profile/01992333660672251175noreply@blogger.com1tag:blogger.com,1999:blog-1378979608157429060.post-8857619896450897152012-10-31T12:43:00.000-04:002012-10-31T12:43:02.897-04:00MSDTC on server 'Server' is unavailable.<div dir="ltr" style="text-align: left;" trbidi="on">
<div style="text-align: left;">
<span style="font-family: Arial, Helvetica, sans-serif;">Just a quick post on an error that I got today...</span></div>
<div style="text-align: left;">
<span style="font-family: Arial, Helvetica, sans-serif;"></span> </div>
<div style="text-align: left;">
<span style="font-family: Arial, Helvetica, sans-serif;"></span> </div>
<div style="text-align: center;">
<span style="background-color: yellow; font-family: Times, "Times New Roman", serif;">Msg 8501, Level 16, State 3, Line 3</span></div>
<div style="text-align: center;">
<span style="background-color: yellow; font-family: Times, "Times New Roman", serif;">
</span></div>
<div style="text-align: center;">
<span style="background-color: yellow; font-family: Times, "Times New Roman", serif;">MSDTC on server 'ODCLG29' is unavailable.</span></div>
<div style="text-align: left;">
<span style="font-family: Arial, Helvetica, sans-serif;"></span> </div>
<div style="text-align: left;">
<span style="font-family: Arial, Helvetica, sans-serif;"></span> </div>
<div style="text-align: left;">
<span style="font-family: Arial, Helvetica, sans-serif;">That means the "Distributed Transaction Coordinator" service is not running.</span></div>
<div style="text-align: left;">
<span style="font-family: Arial, Helvetica, sans-serif;"></span> </div>
<div style="text-align: left;">
<span style="font-family: Arial, Helvetica, sans-serif;">ways to get to it...</span></div>
<div style="text-align: left;">
<span style="font-family: Arial, Helvetica, sans-serif;"></span> </div>
<div style="text-align: left;">
<span style="font-family: Arial, Helvetica, sans-serif;">Type "services.msc" in the run or search command.</span></div>
<div style="text-align: left;">
<span style="font-family: Arial, Helvetica, sans-serif;">or go to Control Panel --> administrative tools ---> Services</span></div>
<div style="text-align: left;">
<span style="font-family: Arial, Helvetica, sans-serif;"></span> </div>
<div style="text-align: left;">
<span style="font-family: Arial, Helvetica, sans-serif;"></span> </div>
<div style="text-align: left;">
<span style="font-family: Arial, Helvetica, sans-serif;">look for Distributed Transaction Coordinator, you may double click to start it or right click and select start.</span></div>
<div style="text-align: left;">
</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="http://4.bp.blogspot.com/-81jBCUB3rM0/UJFT-HAaCvI/AAAAAAAAAtI/FmC3kMW0ylA/s1600/1.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><span style="font-family: Arial, Helvetica, sans-serif;"><img border="0" height="221" src="http://4.bp.blogspot.com/-81jBCUB3rM0/UJFT-HAaCvI/AAAAAAAAAtI/FmC3kMW0ylA/s400/1.png" width="400" /></span></a></div>
<div style="text-align: left;">
<span style="font-family: Arial, Helvetica, sans-serif;"></span> </div>
<div style="text-align: left;">
<span style="font-family: Arial, Helvetica, sans-serif;">You may further read about it here...</span></div>
<div style="text-align: left;">
<a href="http://technet.microsoft.com/en-us/library/dd337629(v=ws.10).aspx"><span style="font-family: Arial, Helvetica, sans-serif;">http://technet.microsoft.com/en-us/library/dd337629(v=ws.10).aspx</span></a></div>
<div style="text-align: left;">
<span style="font-family: Arial, Helvetica, sans-serif;"></span> </div>
<div style="text-align: left;">
</div>
</div>
Jason Yhttp://www.blogger.com/profile/01992333660672251175noreply@blogger.com0tag:blogger.com,1999:blog-1378979608157429060.post-41006708980736974422012-10-10T10:13:00.000-04:002012-10-12T13:43:53.561-04:00SSIS Foreach Loop Container continue or error<div dir="ltr" style="text-align: left;" trbidi="on">
<span style="font-family: Arial,Helvetica,sans-serif;">A recent question about how to skip a failure inside a Foreach Loop Container , I'm sharing the answer as it's easy and apparently not everybody is aware of it.</span><br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://3.bp.blogspot.com/-GFLGTugKxn8/T0ZV5LtfgGI/AAAAAAAAARs/aw3YWl4LgGk/s1600/before1.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><span style="font-family: Arial,Helvetica,sans-serif;"><img border="0" height="136" src="http://3.bp.blogspot.com/-GFLGTugKxn8/T0ZV5LtfgGI/AAAAAAAAARs/aw3YWl4LgGk/s400/before1.jpg" width="400" /></span></a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
<span style="font-family: Arial,Helvetica,sans-serif;">So let's assume it's only a simple Foreach Loop Container that's looping over a files on a network location or even the local disk, getting the file names into a variable and passing it to a file system task to move these files over to another location.</span></div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
<span style="font-family: Arial,Helvetica,sans-serif;">The first step will be by creating an OnError event handler for the file system task, and what I mean by that is </span></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://1.bp.blogspot.com/-Uo76tRVOkK0/T0ZV7egWbZI/AAAAAAAAASE/PeUEsHy4Ykk/s1600/1.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><span style="font-family: Arial,Helvetica,sans-serif;"><img border="0" height="200" src="http://1.bp.blogspot.com/-Uo76tRVOkK0/T0ZV7egWbZI/AAAAAAAAASE/PeUEsHy4Ykk/s400/1.jpg" width="400" /></span></a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
<span style="font-family: Arial,Helvetica,sans-serif;">just click on Event Handlers from the top and then click on the BLUE link in the event handlers and that's it :)</span></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://4.bp.blogspot.com/-uUEolVLrDKg/T0ZV6_mvEfI/AAAAAAAAAR8/Assk8b2FcG8/s1600/2-1.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><span style="font-family: Arial,Helvetica,sans-serif;"><img border="0" height="215" src="http://4.bp.blogspot.com/-uUEolVLrDKg/T0ZV6_mvEfI/AAAAAAAAAR8/Assk8b2FcG8/s400/2-1.jpg" width="400" /></span></a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div align="left" class="separator" style="clear: both; text-align: center;">
<span style="font-family: Arial,Helvetica,sans-serif;"></span></div>
<span style="font-family: Arial,Helvetica,sans-serif;"> Then look under system variables in the variables window, find the "Propagate" variable and set it to false.</span><br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://3.bp.blogspot.com/-yNkLdhzQkNY/T0ZV6MIUPBI/AAAAAAAAAR0/LyY8ie_FDyc/s1600/3.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><span style="font-family: Arial,Helvetica,sans-serif;"><img border="0" height="370" src="http://3.bp.blogspot.com/-yNkLdhzQkNY/T0ZV6MIUPBI/AAAAAAAAAR0/LyY8ie_FDyc/s400/3.jpg" width="400" /></span></a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
<span style="font-family: Arial,Helvetica,sans-serif;">So now The Foreach Loop Container is not aware that the file system task
has failed therefore does not cause the container to fail and exit.<br /><br />OPTIONAL--you
can use the failure Precedence Constraint "red branch" to do any other
processing, such as to send a notification email with the name of the
failed image!!</span></div>
<br />
<span style="font-family: Arial,Helvetica,sans-serif;">Hope that helps someone!</span><br />
<span style="font-family: Arial,Helvetica,sans-serif;"></span><br />
<div class="separator" style="clear: both; text-align: left;">
<span style="font-family: Arial,Helvetica,sans-serif;"><br /></span></div>
<span style="font-family: Arial,Helvetica,sans-serif;">
</span><br />
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
</div>
Jason Yhttp://www.blogger.com/profile/01992333660672251175noreply@blogger.com3tag:blogger.com,1999:blog-1378979608157429060.post-56440375075937491852012-08-29T08:53:00.000-04:002012-09-05T08:53:41.108-04:00Skype as a windows update!<div dir="ltr" style="text-align: left;" trbidi="on">
Today I got the normal windows update notification in my tool bar, and was surprised that it's showing Skype as an Important Update! I searched for the KB number and found out that's it's now part of windows updates! <br />
<br />
<a href="http://support.microsoft.com/kb/2692954">http://support.microsoft.com/kb/2692954</a><br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://4.bp.blogspot.com/-SMKhSO_21_c/UEdK-gnPGSI/AAAAAAAAArU/oI4fXvH3mAs/s1600/skype.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="173" src="http://4.bp.blogspot.com/-SMKhSO_21_c/UEdK-gnPGSI/AAAAAAAAArU/oI4fXvH3mAs/s320/skype.jpg" width="320" /></a></div>
</div>
Jason Yhttp://www.blogger.com/profile/01992333660672251175noreply@blogger.com0tag:blogger.com,1999:blog-1378979608157429060.post-41770420113319780292012-08-14T22:39:00.002-04:002012-08-14T22:46:55.469-04:00T-SQL Tuesday #33 – Trick Shot – SSRS User Input<div dir="ltr" style="text-align: left;" trbidi="on">
<div class="separator" style="clear: both; text-align: left;">
<a href="http://4.bp.blogspot.com/-MaA9zAerGkY/UCsLXzMUXZI/AAAAAAAAAqQ/C6U-5lnSMkw/s1600/TSQL2sDay150x150.jpg" imageanchor="1" style="clear: right; float: right; margin-bottom: 1em; margin-left: 1em;"></a></div>
<a href="http://www.mikefal.net/2012/08/07/invitation-to-t-sql-tuesday-33-trick-shots-tsql2sday/" imageanchor="1" style="clear: right; float: right; margin-bottom: 1em; margin-left: 1em;"><img border="0" src="http://4.bp.blogspot.com/-MaA9zAerGkY/UCsLXzMUXZI/AAAAAAAAAqQ/C6U-5lnSMkw/s1600/TSQL2sDay150x150.jpg" /></a><span style="font-family: Arial, Helvetica, sans-serif;">It’s T-SQL Tuesday time again, and this time it's about Trick Shots, my trick today is using SSRS to accept user input!</span><br />
<br />
<span style="font-family: Arial, Helvetica, sans-serif;">Of course it’s not the best way to use SSRS as it’s a presentation layer and so limited in doing that using Parameters and sub-Reports which will fire a code or a stored procedure to update or delete a record…but IT COULD BE DONE!!</span><br />
<br />
<strong><span style="font-family: Arial, Helvetica, sans-serif;">The Trick:</span></strong><br />
<br />
<span style="font-family: Arial, Helvetica, sans-serif;">1-I started by creating a new project in VS 2008, “Report Server Project” type.</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://1.bp.blogspot.com/-TuOcp27hYHU/TtkRy0SgNlI/AAAAAAAAAI4/KvTLbrF1qBE/s1600/1.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><span style="font-family: Arial, Helvetica, sans-serif;"><img border="0" height="297" src="http://1.bp.blogspot.com/-TuOcp27hYHU/TtkRy0SgNlI/AAAAAAAAAI4/KvTLbrF1qBE/s400/1.jpg" width="400" /></span></a></div>
<span style="font-family: Arial, Helvetica, sans-serif;"> </span><br />
<br />
<span style="font-family: Arial, Helvetica, sans-serif;">2-I’m using SQL 2008 R2 as my testing platform…I’ve created a test DB called “Admin”</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;">CREATE DATABASE [Admin]</span><br />
<br />
<span style="font-family: Arial, Helvetica, sans-serif;">3-I created a test table called “Employees” contains only 3 columns.</span><br />
<br />
<blockquote class="tr_bq">
<span style="font-family: Arial, Helvetica, sans-serif;">Create TABLE Employees</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"> (</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"> ID int IDENTITY (1,1),</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"> Name varchar(50),</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"> Comments varchar(MAX)</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"> )</span></blockquote>
<br />
<span style="font-family: Arial, Helvetica, sans-serif;">4- Inserted 4 test records…</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"> </span><br />
<blockquote class="tr_bq">
<span style="font-family: Arial, Helvetica, sans-serif;">Insert Employees VALUES('Jason', 'Lazy Employee')</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"> ,('Yousef', NULL)</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"> ,('John', 'Anything Goes')</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"> ,('Smith', NULL)</span></blockquote>
<span style="font-family: Arial, Helvetica, sans-serif;"> </span><br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://1.bp.blogspot.com/-YkSLEAr5ROk/TtkSATNmXgI/AAAAAAAAAK4/s-Mh-hBFaLM/s1600/table.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><span style="font-family: Arial, Helvetica, sans-serif;"><img border="0" src="http://1.bp.blogspot.com/-YkSLEAr5ROk/TtkSATNmXgI/AAAAAAAAAK4/s-Mh-hBFaLM/s1600/table.jpg" /></span></a></div>
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">5-back to VS, Created a Shared Data Source to my Admin DB</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://1.bp.blogspot.com/-oPDGVlqKbhk/TtkRzgMFzVI/AAAAAAAAAJA/3tsD0fkll80/s1600/2.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><span style="font-family: Arial, Helvetica, sans-serif;"><img border="0" height="196" src="http://1.bp.blogspot.com/-oPDGVlqKbhk/TtkRzgMFzVI/AAAAAAAAAJA/3tsD0fkll80/s400/2.jpg" width="400" /></span></a></div>
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">6-created 3 Blank reports, using the shared Data Source.</span><br />
<br />
<span style="font-family: Arial, Helvetica, sans-serif;">1- To display the records which in the table.</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;">2- A user summary and confirm screen of which record will be updated and with what.</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;">3- A done screen, which will call a stored procedure or just have a simple inline T-SQL query.</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://1.bp.blogspot.com/-svHv60mKShs/TtkR9YVZOrI/AAAAAAAAAKY/nfhrRpoO27Q/s1600/datasources.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><span style="font-family: Arial, Helvetica, sans-serif;"><img border="0" height="335" src="http://1.bp.blogspot.com/-svHv60mKShs/TtkR9YVZOrI/AAAAAAAAAKY/nfhrRpoO27Q/s400/datasources.jpg" width="400" /></span></a></div>
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">That’s the trick in the second report, you need to hide the ID and keep the Comments Parameter visible and allow it to accept NULL Value, so it display the record before you need to enter the value. And also if you need an empty comment, such as to clear the comments field or whatever.</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">7-The design…</span><br />
<br />
<span style="font-family: Arial, Helvetica, sans-serif;"><strong>Report1: 1ReportsToDB.rdl</strong></span><br />
<span style="font-family: Arial, Helvetica, sans-serif;">Just a basic table, drag the 3 fields to the table, added an extra field with an expression of =CHR(0254) and Font of: Wingdings Just to display a shape without using an image.</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;">Please refer to my article for shapes in SSRS </span><a href="http://asqlb.blogspot.com/2011/06/ever-wanted-shapes-and-check-boxes-in.html"><span style="font-family: Arial, Helvetica, sans-serif;">http://asqlb.blogspot.com/2011/06/ever-wanted-shapes-and-check-boxes-in.html</span></a><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-PaDJ-c9_zMg/TtkR12mA0EI/AAAAAAAAAJQ/qAXkWwfi9Lo/s1600/4.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><span style="font-family: Arial, Helvetica, sans-serif;"><img border="0" height="77" src="http://2.bp.blogspot.com/-PaDJ-c9_zMg/TtkR12mA0EI/AAAAAAAAAJQ/qAXkWwfi9Lo/s400/4.jpg" width="400" /></span></a></div>
<div class="separator" style="clear: both; text-align: center;">
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span></div>
<span style="font-family: Arial, Helvetica, sans-serif;">Also I added an action to that text field to go to report 2 and pass the ID.</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://4.bp.blogspot.com/-M3YyNa5sjgY/TtkR0_f16AI/AAAAAAAAAJI/tpoSOXFzSVQ/s1600/3.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><span style="font-family: Arial, Helvetica, sans-serif;"><img border="0" height="363" src="http://4.bp.blogspot.com/-M3YyNa5sjgY/TtkR0_f16AI/AAAAAAAAAJI/tpoSOXFzSVQ/s400/3.jpg" width="400" /></span></a></div>
<br />
<span style="font-family: Arial, Helvetica, sans-serif;"> </span><br />
<strong><span style="font-family: Arial, Helvetica, sans-serif;">Report2: 2ReportsToDB-Confirm.rdl</span></strong><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">Again a simple table with the basic 3 fields and added another field for the new Comments value which is using an expression of: =Parameters!Comments.Value and a field for a confirmation symbol, yes again using my favorite shapes font WINGDINGS , with action to run the 3rd report and pass the ID and the new comments.</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-7CGxU5chvVk/TtkR2tD13hI/AAAAAAAAAJY/TNiPDMu7mJ0/s1600/5.jpg" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><span style="font-family: Arial, Helvetica, sans-serif;"><img border="0" height="88" src="http://2.bp.blogspot.com/-7CGxU5chvVk/TtkR2tD13hI/AAAAAAAAAJY/TNiPDMu7mJ0/s640/5.jpg" width="640" /></span></a></div>
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://3.bp.blogspot.com/-cNyby-_cTD0/TtkR3cnZf_I/AAAAAAAAAJg/c7pWvgsLaic/s1600/6.jpg" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><span style="font-family: Arial, Helvetica, sans-serif;"><img border="0" height="580" src="http://3.bp.blogspot.com/-cNyby-_cTD0/TtkR3cnZf_I/AAAAAAAAAJg/c7pWvgsLaic/s640/6.jpg" width="640" /></span></a></div>
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;"> </span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<strong><span style="font-family: Arial, Helvetica, sans-serif;">Report3: 3ReportsToDB-Done.rdl</span></strong><br />
<br />
<span style="font-family: Arial, Helvetica, sans-serif;">I added 3 text fields, but it’s all optional (for fun).</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">Just with an action to go back to the first report and the summary or what was done!!</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-vL_w8xxWWng/TtkR4GWJMwI/AAAAAAAAAJo/l5_fYi4H0Dk/s1600/7.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><span style="font-family: Arial, Helvetica, sans-serif;"><img border="0" height="189" src="http://2.bp.blogspot.com/-vL_w8xxWWng/TtkR4GWJMwI/AAAAAAAAAJo/l5_fYi4H0Dk/s640/7.jpg" width="640" /></span></a></div>
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">Lets’ run and test it….</span><br />
<br />
<span style="font-family: Arial, Helvetica, sans-serif;"> </span><br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-xC52ofA0kao/TtkR5Iv0yvI/AAAAAAAAAJw/dh0Jluv85iI/s1600/8.jpg" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><span style="font-family: Arial, Helvetica, sans-serif;"><img border="0" height="187" src="http://2.bp.blogspot.com/-xC52ofA0kao/TtkR5Iv0yvI/AAAAAAAAAJw/dh0Jluv85iI/s640/8.jpg" width="640" /></span></a></div>
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;"> </span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">I’ll click next to my name on the check mark to update my comments…</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;">That’s what you get at first because we allowed the NULL, that’s the trick to see the table before the parameter gets initialized.</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-bBQxdZ8rekY/TtkR6AIZXbI/AAAAAAAAAJ4/glILIQWFlyM/s1600/9.jpg" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><span style="font-family: Arial, Helvetica, sans-serif;"><img border="0" height="145" src="http://2.bp.blogspot.com/-bBQxdZ8rekY/TtkR6AIZXbI/AAAAAAAAAJ4/glILIQWFlyM/s640/9.jpg" width="640" /></span></a></div>
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">Remove the NULL check mark and enter the new comments….</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-j6bQ51ocqlU/TtkR66Pl8xI/AAAAAAAAAKA/Glc7FxI1F9A/s1600/10.jpg" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><span style="font-family: Arial, Helvetica, sans-serif;"><img border="0" height="158" src="http://2.bp.blogspot.com/-j6bQ51ocqlU/TtkR66Pl8xI/AAAAAAAAAKA/Glc7FxI1F9A/s640/10.jpg" width="640" /></span></a></div>
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">You’ll get the confirmation above…</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;">Now click on the check mark to confirm the change, yes you guessed it right…it’s the wingdings shapes again ¿</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;">And that’s what you get..</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://4.bp.blogspot.com/-NEJYR8HJ4FY/TtkR7rEMeWI/AAAAAAAAAKI/EZ54Lu3gwpU/s1600/11.jpg" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><span style="font-family: Arial, Helvetica, sans-serif;"><img border="0" height="280" src="http://4.bp.blogspot.com/-NEJYR8HJ4FY/TtkR7rEMeWI/AAAAAAAAAKI/EZ54Lu3gwpU/s640/11.jpg" width="640" /></span></a></div>
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;"> </span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">I’ll click on my GO Back, to the first report to check the data.</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;">Done.. my record was updated in the DB.</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://4.bp.blogspot.com/-U6vb7Z-3Om0/TtkR8ZhpdtI/AAAAAAAAAKQ/cattiSvuU1I/s1600/12.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><span style="font-family: Arial, Helvetica, sans-serif;"><img border="0" height="188" src="http://4.bp.blogspot.com/-U6vb7Z-3Om0/TtkR8ZhpdtI/AAAAAAAAAKQ/cattiSvuU1I/s640/12.jpg" width="640" /></span></a></div>
<br />
<br />
<span style="font-family: Arial, Helvetica, sans-serif;">Again that’s for the purpose of showing that SSRS is more than a presentation layer and that was the simplest part.</span><br />
<br />
<strong><span style="font-family: Arial, Helvetica, sans-serif;">What I Learned</span></strong><br />
<br />
<span style="font-family: Arial, Helvetica, sans-serif;"> that it could get more complicated and done more professionally using Custom .NET code, functions or even a simple stored procedure that knows to delete or update or even insert new record based on a flag.</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">Hope that helps someone…</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">The project can be downloaded from</span><br />
<a href="http://www.box.com/s/sskhxfppful1l7sbytzh"><span style="font-family: Arial, Helvetica, sans-serif;">http://www.box.com/s/sskhxfppful1l7sbytzh</span></a><br />
<span style="font-family: Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Arial, Helvetica, sans-serif;">References:</span><br />
<a href="http://asqlb.blogspot.com/2011/06/ever-wanted-shapes-and-check-boxes-in.html"><span style="font-family: Arial, Helvetica, sans-serif;">http://asqlb.blogspot.com/2011/06/ever-wanted-shapes-and-check-boxes-in.html</span></a><br />
<a href="http://www.purplehell.com/riddletools/wingdingschart.htm"><span style="font-family: Arial, Helvetica, sans-serif;">http://www.purplehell.com/riddletools/wingdingschart.htm</span></a><br />
<div style="text-align: center;">
<br /></div>
</div>
Jason Yhttp://www.blogger.com/profile/01992333660672251175noreply@blogger.com0tag:blogger.com,1999:blog-1378979608157429060.post-85383791723012262602012-08-14T19:26:00.004-04:002012-08-14T22:19:28.622-04:00Loop over .sql files using SSIS<div dir="ltr" style="text-align: left;" trbidi="on">
This is fairly easy and simple, the requirement is to loop over a folder full of .sql files, that has some queries and run them on the server!<br />
<br />
This could be a stress testing queries, create multiple object or drop and recreate indexes queries as in my actual requirement.<br />
<br />
We'll need a "For EachLoop container "to iterate over the folder, read the files' location into a STRING variable.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://3.bp.blogspot.com/-8VZhkg9FumY/UCreSjCVOpI/AAAAAAAAAoY/uKE1HnbfIVY/s1600/1.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="85" src="http://3.bp.blogspot.com/-8VZhkg9FumY/UCreSjCVOpI/AAAAAAAAAoY/uKE1HnbfIVY/s320/1.jpg" width="320" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://1.bp.blogspot.com/-OnsIpnxvQ8I/UCreWLh0WXI/AAAAAAAAAog/2AK2mBVGkTg/s1600/2.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="269" src="http://1.bp.blogspot.com/-OnsIpnxvQ8I/UCreWLh0WXI/AAAAAAAAAog/2AK2mBVGkTg/s320/2.jpg" width="320" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://4.bp.blogspot.com/-A3-KEIojz8g/UCreb1jxhWI/AAAAAAAAAoo/f1m9tVh-zc8/s1600/3.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="270" src="http://4.bp.blogspot.com/-A3-KEIojz8g/UCreb1jxhWI/AAAAAAAAAoo/f1m9tVh-zc8/s320/3.jpg" width="320" /></a></div>
<br />
Add a "Execute SQL Task" inside the container, setup the SQL server connection, then setup the query to be from a file connection.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://3.bp.blogspot.com/-KizsF6sHGSw/UCrejMxvv8I/AAAAAAAAAow/1ecTFKAOGVs/s1600/4.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="269" src="http://3.bp.blogspot.com/-KizsF6sHGSw/UCrejMxvv8I/AAAAAAAAAow/1ecTFKAOGVs/s320/4.jpg" width="320" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<br />
create the file connection, pointing to one of the files.<br />
set the delay validation to true, then setup an expression over the Connection String property.<br />
<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://4.bp.blogspot.com/-nYy1kc9Uo8I/UCrem2GmKGI/AAAAAAAAAo4/4p_GiZpPg70/s1600/5.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="255" src="http://4.bp.blogspot.com/-nYy1kc9Uo8I/UCrem2GmKGI/AAAAAAAAAo4/4p_GiZpPg70/s400/5.jpg" width="400" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
That's it!!<br />
<br />
The package can be downloaded at: <a href="https://www.box.com/s/928b2f993c819b64dd53">https://www.box.com/s/928b2f993c819b64dd53</a><br />
Hope that helps someone...</div>
Jason Yhttp://www.blogger.com/profile/01992333660672251175noreply@blogger.com0tag:blogger.com,1999:blog-1378979608157429060.post-84885182261166006632012-08-02T22:10:00.000-04:002012-08-14T22:18:44.569-04:00Attach AdventureWorks 2012<div dir="ltr" style="text-align: left;" trbidi="on">
Quick and easy, but I see people in forums are asking about it...<br />
You may download the .MDF file from here: <a href="http://msftdbprodsamples.codeplex.com/releases/view/55330">http://msftdbprodsamples.codeplex.com/releases/view/55330</a><br />
I'd typically download and save it with the rest of the data files, maybe at the default location or any other drive if you prefer.<br />
<br />
The default location would be: <br />
C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA<br />
<br />
Then, right click on your DATABASES under your server name and select ATTACH<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-ELMRnAbXD0I/UCsGAjyGW0I/AAAAAAAAApo/we6IxO8tKCs/s1600/0.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://2.bp.blogspot.com/-ELMRnAbXD0I/UCsGAjyGW0I/AAAAAAAAApo/we6IxO8tKCs/s1600/0.png" /></a></div>
<br />
Then you'll get the following screen, where you would normally navigate to the download directory and select the advntureworks2012.mdf file.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://4.bp.blogspot.com/-HKKpmxg9FmA/UCsGm8O6lsI/AAAAAAAAApw/tZhLr3hYTDA/s1600/1.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="269" src="http://4.bp.blogspot.com/-HKKpmxg9FmA/UCsGm8O6lsI/AAAAAAAAApw/tZhLr3hYTDA/s320/1.png" width="320" /></a></div>
<br />
After you click on OK, you will see that the log file is missing and SQL server will display "Not Found", just highlight the log file and click on remove, then OK.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-6T1UFkklsUc/UCsGqDEoEYI/AAAAAAAAAp4/b9YkrrDGcRc/s1600/2.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="264" src="http://2.bp.blogspot.com/-6T1UFkklsUc/UCsGqDEoEYI/AAAAAAAAAp4/b9YkrrDGcRc/s320/2.png" width="320" /></a></div>
<br />
You're done!! attached ans ready for testing...</div>
Jason Yhttp://www.blogger.com/profile/01992333660672251175noreply@blogger.com0tag:blogger.com,1999:blog-1378979608157429060.post-77160119975577490492012-07-23T18:00:00.000-04:002012-08-14T22:19:07.008-04:00Blogger, blogspot Contact Form<div dir="ltr" style="text-align: left;" trbidi="on">
<span style="font-family: Arial, Helvetica, sans-serif;">I've always thinking about a contact form for my blog, and blogger.com doesn't offer it or it's only me whom can't find it!</span><br />
<br />
<span style="font-family: Arial, Helvetica, sans-serif;">Anyway I've discovered this site </span><a href="https://www.emailmeform.com/"><span style="font-family: Arial, Helvetica, sans-serif;">https://www.emailmeform.com</span></a><span style="font-family: Arial, Helvetica, sans-serif;"> they give you free forms directly to your E-mail based on templates or your customized form.</span><br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-j4wqkshdmRI/UCrM07k6MNI/AAAAAAAAAm4/GyYbsM0ZSCc/s1600/1.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><span style="font-family: Arial, Helvetica, sans-serif;"><img border="0" height="60" src="http://2.bp.blogspot.com/-j4wqkshdmRI/UCrM07k6MNI/AAAAAAAAAm4/GyYbsM0ZSCc/s320/1.jpg" width="320" /></span></a></div>
<br />
<span style="font-family: Arial, Helvetica, sans-serif;">Just hop to their web site, click on "FREE SIGNUP", fill the form and you'll be ready.</span><br />
<br />
<span style="font-family: Arial, Helvetica, sans-serif;">The free account gives you the ability to create up to 5 forms and 200 submission a month limit, and you could use the CAPTCHA Verification for spam prevention.</span><br />
<br />
<span style="font-family: Arial, Helvetica, sans-serif;">After you sign-up and create your form, grab the code as shown below</span><br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://4.bp.blogspot.com/-VVy_rJUQP24/UCrNyvaxmEI/AAAAAAAAAnI/F7XoLfbIac4/s1600/2.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><span style="font-family: Arial, Helvetica, sans-serif;"><img border="0" height="320" src="http://4.bp.blogspot.com/-VVy_rJUQP24/UCrNyvaxmEI/AAAAAAAAAnI/F7XoLfbIac4/s320/2.jpg" width="294" /></span></a></div>
<br />
<span style="font-family: Arial, Helvetica, sans-serif;">and paste it into a new page into your blogger blog...</span><br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-n6YvmCg_kLg/UCrOCb0ZX5I/AAAAAAAAAnY/HI6fF7uP5pU/s1600/3.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><span style="font-family: Arial, Helvetica, sans-serif;"><img border="0" height="277" src="http://2.bp.blogspot.com/-n6YvmCg_kLg/UCrOCb0ZX5I/AAAAAAAAAnY/HI6fF7uP5pU/s320/3.jpg" width="320" /></span></a></div>
<span style="font-family: Arial, Helvetica, sans-serif;">and you're done...now you'll have a nice way to interact with your readers.</span><br />
<br />
<a href="http://www.jasonyousef.com/p/contact.html"><span style="font-family: Arial, Helvetica, sans-serif;">http://www.jasonyousef.com/p/contact.html</span></a></div>
Jason Yhttp://www.blogger.com/profile/01992333660672251175noreply@blogger.com0tag:blogger.com,1999:blog-1378979608157429060.post-22451785776360946542012-07-09T14:06:00.002-04:002012-07-09T14:06:48.784-04:00The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.<div dir="ltr" style="text-align: left;" trbidi="on">
<div style="font-family: Arial,Helvetica,sans-serif;">
<span style="font-size: small;">Today I got that error message while using the import and Export wizard on windows 7, running SQL Server 2008 R2, 64 Bit.</span></div>
<div style="font-family: Arial,Helvetica,sans-serif;">
<br /></div>
<div class="separator" style="clear: both; font-family: Arial,Helvetica,sans-serif; text-align: center;">
<span style="font-size: small;"><a href="http://1.bp.blogspot.com/-fjRk_2aLxrI/T_sdMhIlGBI/AAAAAAAAAl4/uXeZj2U2bJ8/s1600/Error.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="115" src="http://1.bp.blogspot.com/-fjRk_2aLxrI/T_sdMhIlGBI/AAAAAAAAAl4/uXeZj2U2bJ8/s400/Error.jpg" width="400" /></a></span></div>
<div style="font-family: Arial,Helvetica,sans-serif;">
<span style="font-size: small;"><br /></span></div>
<div style="font-family: Arial,Helvetica,sans-serif;">
<span style="font-size: small;">I was importing from an Excel 2010 sheet.</span></div>
<div style="font-family: Arial,Helvetica,sans-serif;">
<br /></div>
<div style="font-family: Arial,Helvetica,sans-serif;">
<span style="font-size: small;">The solution is to download and install the "</span><span style="font-size: small;"><b>2007 Office System Driver: Data Connectivity Components</b>"</span></div>
<h1 id="top" style="font-family: Arial,Helvetica,sans-serif; font-weight: normal;">
<span style="font-size: small;">From: <a href="http://www.microsoft.com/en-us/download/details.aspx?id=23734" target="_blank">http://www.microsoft.com/en-us/download/details.aspx?id=23734</a></span></h1>
<h1 id="top" style="font-family: Arial,Helvetica,sans-serif; font-weight: normal;">
<span style="font-size: small;">Hope that helps someone...</span></h1>
<h1 id="top" style="font-family: Arial,Helvetica,sans-serif;">
<span style="font-size: small;"> </span></h1>
<div style="font-family: Arial,Helvetica,sans-serif;">
</div>
<div style="font-family: Arial,Helvetica,sans-serif;">
<span style="font-size: small;"><br /></span></div>
<div style="font-family: Arial,Helvetica,sans-serif;">
<span style="font-size: small;"><br /></span></div>
</div>Jason Yhttp://www.blogger.com/profile/01992333660672251175noreply@blogger.com0278 Broadway, Manhattan, NY 10007, USA40.714269 -74.00597216.354643000000003 -114.4356595 65.073895 -33.5762845tag:blogger.com,1999:blog-1378979608157429060.post-66180889560501863742012-05-22T12:45:00.000-04:002012-05-29T13:22:51.748-04:00SQL Server Import and Export Wizard step by step explained. Building your first SSIS package.<div dir="ltr" style="text-align: left;" trbidi="on">
<span style="font-family: Arial, Helvetica, sans-serif;">Hi, Today I will be talking about the SQL Server Import and Export Wizard, which is one of the easiest ways to develop your first SQL Server Integration Services (SSIS) package, and it so useful to export and import data from multiple sources to a various destinations, varies from relational database tables to text files.</span><br />
<br />
<span style="font-family: Arial, Helvetica, sans-serif;">Through the demo I will be using SQL Server 2012 on top of a Windows 2008 Sever, but the wizard is exactly the same as it was in erlier versions.</span><br />
<br />
<span style="font-family: Arial, Helvetica, sans-serif;">You start by learning the different ways to get the wizard up, and it is straight forward and can be launched from various locations</span><br />
<br />
<span style="font-family: Arial, Helvetica, sans-serif;">1-from the run commend or the command prompt, you might type "DTSWizard.exe" and hit enter, which will get the wizard up and running..</span><br />
<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-acoZmxPonPs/T8T-m82G2EI/AAAAAAAAAjg/3NllxDB_mek/s1600/Import-Export000.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><span style="font-family: Arial, Helvetica, sans-serif;"><img border="0" height="164" src="http://2.bp.blogspot.com/-acoZmxPonPs/T8T-m82G2EI/AAAAAAAAAjg/3NllxDB_mek/s320/Import-Export000.png" width="320" /></span></a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<span style="font-family: Arial, Helvetica, sans-serif;"> 2-By right click on your database---> Tasks--->"Import Data" or "Export Data"</span><br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://4.bp.blogspot.com/-yqsZdL740h4/T8T-nv11LiI/AAAAAAAAAjo/wB_u2-gd3hQ/s1600/Import-Export.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><span style="font-family: Arial, Helvetica, sans-serif;"><img border="0" height="320" src="http://4.bp.blogspot.com/-yqsZdL740h4/T8T-nv11LiI/AAAAAAAAAjo/wB_u2-gd3hQ/s320/Import-Export.png" width="243" /></span></a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<span style="font-family: Arial, Helvetica, sans-serif;"> 3-From SQL Server Data Tools (SSDT) , right click on "SSIS Packages" and select "SSIS Import and Export Wizard"</span><br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://1.bp.blogspot.com/-CZvAA_TfI7s/T8T-odGOKvI/AAAAAAAAAjw/fMG8YmX5vjI/s1600/Import-Export00.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><span style="font-family: Arial, Helvetica, sans-serif;"><img border="0" height="207" src="http://1.bp.blogspot.com/-CZvAA_TfI7s/T8T-odGOKvI/AAAAAAAAAjw/fMG8YmX5vjI/s320/Import-Export00.png" width="320" /></span></a></div>
<br />
<span style="font-family: Arial, Helvetica, sans-serif;">4-From the start menu, navigate to "Microsoft SQL Server 2012" and select the "Import and Export Data" of your windows version 32 Bit /Vs 64 Bit"</span><br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://3.bp.blogspot.com/-rsWP5UhSlm4/T8T-o-L3jAI/AAAAAAAAAj4/y9F0wUJwtuM/s1600/Import-Export0.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><span style="font-family: Arial, Helvetica, sans-serif;"><img border="0" height="320" src="http://3.bp.blogspot.com/-rsWP5UhSlm4/T8T-o-L3jAI/AAAAAAAAAj4/y9F0wUJwtuM/s320/Import-Export0.png" width="257" /></span></a></div>
<br />
<strong><span style="font-family: Arial, Helvetica, sans-serif;">The Wizard...</span></strong><br />
<br />
<span style="font-family: Arial, Helvetica, sans-serif;">Once the wizard is up, you'll be welcomed with the following screen, unless you click on "Do not show this starting page again", so any subsequent runs this screen will not appear.</span><br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://4.bp.blogspot.com/-IQ71bGjrQEA/T8T-p-n37FI/AAAAAAAAAkA/oDoa4CTmBlA/s1600/Import-Export1.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><span style="font-family: Arial, Helvetica, sans-serif;"><img border="0" height="320" src="http://4.bp.blogspot.com/-IQ71bGjrQEA/T8T-p-n37FI/AAAAAAAAAkA/oDoa4CTmBlA/s320/Import-Export1.png" width="314" /></span></a></div>
<br />
<span style="font-family: Arial, Helvetica, sans-serif;">Press on next and you'll be prompted with the Data Source, which could be any data source you need to pull information from. such as Database table, excel file, flat files (Text File) or a delimited file.</span><br />
<br />
<span style="font-family: Arial, Helvetica, sans-serif;">I will be using my local SQL server engine to pull information from, specifically from the "Adventureworks Database".</span><br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://3.bp.blogspot.com/-QTH4HAZdQuM/T8T-qkeWGDI/AAAAAAAAAkI/6iraBrrI__4/s1600/Import-Export2.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><span style="font-family: Arial, Helvetica, sans-serif;"><img border="0" height="320" src="http://3.bp.blogspot.com/-QTH4HAZdQuM/T8T-qkeWGDI/AAAAAAAAAkI/6iraBrrI__4/s320/Import-Export2.png" width="312" /></span></a></div>
<br />
<span style="font-family: Arial, Helvetica, sans-serif;">Next, you will need to specify your destination. Here I will be saving the table's data to an Excel file</span><br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://1.bp.blogspot.com/-bU6xH-3iMps/T8T-rWwwcII/AAAAAAAAAkQ/elhWpUlR2Ts/s1600/Import-Export3.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><span style="font-family: Arial, Helvetica, sans-serif;"><img border="0" height="320" src="http://1.bp.blogspot.com/-bU6xH-3iMps/T8T-rWwwcII/AAAAAAAAAkQ/elhWpUlR2Ts/s320/Import-Export3.png" width="313" /></span></a></div>
<br />
<span style="font-family: Arial, Helvetica, sans-serif;">Or a flat file (Text file)</span><br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-t0mUIcyoC3s/T8T-sZlpD3I/AAAAAAAAAkY/3IoqX57wb3o/s1600/Import-Export4.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><span style="font-family: Arial, Helvetica, sans-serif;"><img border="0" height="320" src="http://2.bp.blogspot.com/-t0mUIcyoC3s/T8T-sZlpD3I/AAAAAAAAAkY/3IoqX57wb3o/s320/Import-Export4.png" width="315" /></span></a></div>
<br />
<span style="font-family: Arial, Helvetica, sans-serif;">Next, you might let the wizard guide you through your tables or views, or write your ready query to pull your desired information.</span><br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://3.bp.blogspot.com/-MMtgVZSdQbc/T8T-tIx8s6I/AAAAAAAAAkg/qRyEXSkh5X0/s1600/Import-Export5.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><span style="font-family: Arial, Helvetica, sans-serif;"><img border="0" height="320" src="http://3.bp.blogspot.com/-MMtgVZSdQbc/T8T-tIx8s6I/AAAAAAAAAkg/qRyEXSkh5X0/s320/Import-Export5.png" width="317" /></span></a></div>
<br />
<span style="font-family: Arial, Helvetica, sans-serif;">I choose the first option and here is the screen to select which table or a view I need to pull information from.</span><br />
<br />
<span style="font-family: Arial, Helvetica, sans-serif;">You could click on "Preview" to check your data, or "Edit Mapping" to map only the desired column to an output in your text file.</span><br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://1.bp.blogspot.com/-A3zivsFBf0o/T8T-t4Hg6EI/AAAAAAAAAko/9C8PEDCDjU0/s1600/Import-Export6.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><span style="font-family: Arial, Helvetica, sans-serif;"><img border="0" height="320" src="http://1.bp.blogspot.com/-A3zivsFBf0o/T8T-t4Hg6EI/AAAAAAAAAko/9C8PEDCDjU0/s320/Import-Export6.png" width="313" /></span></a></div>
<div align="left" class="separator" style="clear: both; text-align: center;">
<span style="font-family: Arial, Helvetica, sans-serif;"></span></div>
<div align="left" class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
<span style="font-family: Arial, Helvetica, sans-serif;">Clicking on Edit Mapping would give you the following screen shot.</span></div>
<div class="separator" style="clear: both; text-align: left;">
<span style="font-family: Arial, Helvetica, sans-serif;"></span></div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div align="left" class="separator" style="clear: both; text-align: center;">
<span style="font-family: Arial, Helvetica, sans-serif;"></span></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="http://4.bp.blogspot.com/-x1HaQ6BeOEI/T8UCxHDUNhI/AAAAAAAAAls/2XjGLRgb4Xg/s1600/Import-Export6-1.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><span style="font-family: Arial, Helvetica, sans-serif;"><img border="0" height="156" src="http://4.bp.blogspot.com/-x1HaQ6BeOEI/T8UCxHDUNhI/AAAAAAAAAls/2XjGLRgb4Xg/s320/Import-Export6-1.png" width="320" /></span></a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<span style="font-family: Arial, Helvetica, sans-serif;"> Next would prompt you to "Run Immediately" the package, or save it for future use or edit.</span><br />
<br />
<span style="font-family: Arial, Helvetica, sans-serif;">I will select all the options, since I need to run it and save it for later use, i will be saving the package in the MSDB database, you could use the file system if that is what you are looking for.</span><br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://1.bp.blogspot.com/-aiMbpkQQ0p8/T8T-urVBuII/AAAAAAAAAkw/k1YQwqNqmuY/s1600/Import-Export7.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><span style="font-family: Arial, Helvetica, sans-serif;"><img border="0" height="320" src="http://1.bp.blogspot.com/-aiMbpkQQ0p8/T8T-urVBuII/AAAAAAAAAkw/k1YQwqNqmuY/s320/Import-Export7.png" width="314" /></span></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
<span style="font-family: Arial, Helvetica, sans-serif;">Next the wizard will display a confirmation summary.</span></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="http://1.bp.blogspot.com/-Qst-xX744YI/T8T-xAnYThI/AAAAAAAAAlA/ssxJukbmZCk/s1600/Import-Export9.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><span style="font-family: Arial, Helvetica, sans-serif;"><img border="0" height="320" src="http://1.bp.blogspot.com/-Qst-xX744YI/T8T-xAnYThI/AAAAAAAAAlA/ssxJukbmZCk/s320/Import-Export9.png" width="313" /></span></a></div>
<br />
<span style="font-family: Arial, Helvetica, sans-serif;">Clicking on finish SOMETIMES turns to be successful and displays the following screenshot.</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;">Otherwise, it will tell you what happened wrong and you will have the option to go back to fix it.</span><br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://4.bp.blogspot.com/-jTZMBD-rdmk/T8T-x9LTOhI/AAAAAAAAAlI/vcc1K16KoG4/s1600/Import-Export10.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><span style="font-family: Arial, Helvetica, sans-serif;"><img border="0" height="320" src="http://4.bp.blogspot.com/-jTZMBD-rdmk/T8T-x9LTOhI/AAAAAAAAAlI/vcc1K16KoG4/s320/Import-Export10.png" width="312" /></span></a></div>
<br />
<span style="font-family: Arial, Helvetica, sans-serif;">Checking the file existence, and voila! it has been created.</span><br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-razsV_sHsEI/T8T-yr5ofhI/AAAAAAAAAlQ/71n3ecRZTH8/s1600/Import-Export11.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><span style="font-family: Arial, Helvetica, sans-serif;"><img border="0" height="81" src="http://2.bp.blogspot.com/-razsV_sHsEI/T8T-yr5ofhI/AAAAAAAAAlQ/71n3ecRZTH8/s320/Import-Export11.png" width="320" /></span></a></div>
<br />
<span style="font-family: Arial, Helvetica, sans-serif;">Connecting to the SSIS for checking on the package if it was saved as promised or not, and voila!! it was saved, now you might edit it, run it or schedule it to run at your desired interval using the "SQL Server Agent"</span><br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://4.bp.blogspot.com/-DSS29eACzdU/T8T-y1nR9PI/AAAAAAAAAlY/lrXkwdQIyfQ/s1600/Import-Export12.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><span style="font-family: Arial, Helvetica, sans-serif;"><img border="0" src="http://4.bp.blogspot.com/-DSS29eACzdU/T8T-y1nR9PI/AAAAAAAAAlY/lrXkwdQIyfQ/s1600/Import-Export12.png" /></span></a></div>
<span style="font-family: Arial, Helvetica, sans-serif;">Hope that helps someone, and please let me know if it wasn't clear enough.</span><br />
<br />
<span style="font-family: Arial, Helvetica, sans-serif;">For more info about the wizard, please check </span><a href="http://msdn.microsoft.com/en-us/library/ms141209.aspx"><span style="font-family: Arial, Helvetica, sans-serif;">http://msdn.microsoft.com/en-us/library/ms141209.aspx</span></a></div>Jason Yhttp://www.blogger.com/profile/01992333660672251175noreply@blogger.com0tag:blogger.com,1999:blog-1378979608157429060.post-20821413499554731252012-05-11T14:28:00.000-04:002012-05-11T14:28:00.267-04:00Change SSIS 2012 Catalog DB encryption algorithm<div dir="ltr" style="text-align: left;" trbidi="on">
<span style="font-family: Arial, Helvetica, sans-serif;">Hi,</span><br />
<br />
<span style="font-family: Arial, Helvetica, sans-serif;">By default, when you install the SSIS catalog, the encryption algorithm is <span class="code">AES_256..</span></span><br />
<span class="code"><span style="font-family: Arial, Helvetica, sans-serif;">You can read about the various configurable options here.. </span><a href="http://msdn.microsoft.com/en-us/library/ff878147.aspx"><span style="font-family: Arial, Helvetica, sans-serif;">http://msdn.microsoft.com/en-us/library/ff878147.aspx</span></a></span><br />
<br />
<span class="code" style="font-family: Arial, Helvetica, sans-serif;">To change it, you'll need to get the database in a single user mode, then use the "catalog.configure_catalog" SP to change it. Yes you'd use T-SQL to control that now...</span><br />
<br />
<span class="code"><span style="font-family: Arial, Helvetica, sans-serif;">you may read more about it here </span><a href="http://msdn.microsoft.com/en-us/library/ff878042%28v=sql.110%29.aspx"><span style="font-family: Arial, Helvetica, sans-serif;">http://msdn.microsoft.com/en-us/library/ff878042%28v=sql.110%29.aspx</span></a></span><br />
<br />
<span class="code" style="font-family: Arial, Helvetica, sans-serif;">so the whole script would be..</span><br />
<br />
<div style="background: white; border-color: gray; border-style: solid; border-width: 0.1em 0.1em 0.1em 0.8em; color: black; overflow: auto; padding: 0.2em 0.6em; width: auto;">
<pre style="line-height: 125%; margin: 0px;"><span style="font-family: Arial, Helvetica, sans-serif;"><span style="color: green; font-weight: bold;">select</span> <span style="color: #303030;">*</span> <span style="color: green; font-weight: bold;">from</span> <span style="color: green; font-weight: bold;">catalog</span>.catalog_properties
<span style="color: green; font-weight: bold;">ALTER</span> <span style="color: green; font-weight: bold;">DATABASE</span> SSISDB
<span style="color: green; font-weight: bold;">SET</span> SINGLE_USER
<span style="color: green; font-weight: bold;">WITH</span> <span style="color: green; font-weight: bold;">ROLLBACK</span> <span style="color: green; font-weight: bold;">IMMEDIATE</span>;
<span style="color: green; font-weight: bold;">GO</span>
<span style="color: green; font-weight: bold;">catalog</span>.configure_catalog <span style="background-color: #fff0f0;">'ENCRYPTION_ALGORITHM'</span>,<span style="background-color: #fff0f0;">'AES_192'</span>
<span style="color: green; font-weight: bold;">select</span> <span style="color: #303030;">*</span> <span style="color: green; font-weight: bold;">from</span> <span style="color: green; font-weight: bold;">catalog</span>.catalog_properties
<span style="color: green; font-weight: bold;">ALTER</span> <span style="color: green; font-weight: bold;">DATABASE</span> SSISDB
<span style="color: green; font-weight: bold;">SET</span> MULTI_USER;
<span style="color: green; font-weight: bold;">GO</span>
</span></pre>
</div>
<br />
<span style="font-family: Arial, Helvetica, sans-serif;">Please note that you need to be a SSIS_Admin or SysAdmin to be able to do that...</span><br />
<br />
<span style="font-family: Arial, Helvetica, sans-serif;">Hope that helps someone...</span></div>Jason Yhttp://www.blogger.com/profile/01992333660672251175noreply@blogger.com0tag:blogger.com,1999:blog-1378979608157429060.post-34158692694459073742012-05-07T01:00:00.000-04:002012-05-13T09:52:24.006-04:00A DBA’s Ethics T-SQL Tuesday #30<div dir="ltr" style="text-align: left;" trbidi="on">
<a href="http://chrisshaw.wordpress.com/2012/04/30/a-dbas-ethics-t-sql-tuesday-30/" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><span style="font-family: Arial, Helvetica, sans-serif;"><img alt="http://sqlserverpedia.com/blog/sql-server-bloggers/a-dba%E2%80%99s-ethics-t-sql-tuesday-30/" border="0" src="http://2.bp.blogspot.com/-69xIAwLzDwg/T3-UZTK37KI/AAAAAAAAAWc/5WW7NBBz6rw/s1600/T-SQLLogo.jpg" title="t-sql-tuesday-029" /></span></a><span style="font-family: Arial, Helvetica, sans-serif;">Hi, this month's T-SQL Tuesday is hosted by Chris Shaw, and the topic is around the DBA's ethics at the work place...</span><br />
<span style="font-family: Arial;">The database administrator (DBA) is the person whose responsible for managing the relational database and its access permissions.</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;">Well, first of all I am not a DBA and never been one, But I am a developer, and I must admit that I share the same ethics and responsibilities towards the data that I deal with. The ethics here are cross all database professionals whose has access to the back end of the company's data, whom can access sensitive information about patients in health care organizations, or sensitive financial information about clients in financial organizations.</span><br />
<span style="font-family: Arial, Helvetica, sans-serif;">Every database professional should be striving for technical excellence to the best of his/her knowledge. they should and constantly invest in their career by learning and gain new skills.</span><br />
<br />
<span style="font-family: Arial, Helvetica, sans-serif;">Privacy and confidentiality is the most important. Obtaining permissions and documenting access rights is vital in an audit situation and data theft. And any DBA should be aware of his/her responsibility regarding the data and how to protect it from others. </span><br />
<br />
<span style="font-family: Arial;">Every DBA or database professional should have manners and conduct business in a matter of integrity, can't access information they shouldn't access, or try to snoop around client's personal information, or even around work co-workers personal information. and resist the temptation.</span><br />
<span style="font-family: Arial;">So the question is, what's stopping the DBA? well it's his/her ethics towards the Job, career and the employer.</span></div>Jason Yhttp://www.blogger.com/profile/01992333660672251175noreply@blogger.com0