tag:blogger.com,1999:blog-79702833940925372532024-03-14T02:55:42.987+05:30BI MoronSurender Singh Bhadauriahttp://www.blogger.com/profile/17542565963836942496noreply@blogger.comBlogger5125tag:blogger.com,1999:blog-7970283394092537253.post-192251704928878312011-10-18T21:43:00.000+05:302011-10-18T21:43:23.884+05:30SSIS : Continue Looping after a child task Failure<div dir="ltr" style="text-align: left;" trbidi="on">
<div class="Section1">
<div class="MsoNormal">
It is a very common scenario that we want the loop to continue even in case of a child task failing. We can implement this in the following manner.<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoListParagraph" style="mso-list: l0 level1 lfo2; text-indent: 0.25in;">
1.<span style="font: 7pt "Times New Roman";"> </span>Create your loop and configure it<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal" style="text-indent: .5in;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiOYRZrLF1SsZSWUzVsS_bnv8Wfo442NSShdM21Ggj0yCvoSjeiVraVW0ivfFPJ87Bxv7bghW7XPmrtyNVOF0DTtpI0RJjbqXVw_Dflx-YDJbT5AL9Gt1jUQGeiuL5qXlXf2zgeSs2ufDQ/s1600/image002-751424.jpg"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5664770458276300434" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiOYRZrLF1SsZSWUzVsS_bnv8Wfo442NSShdM21Ggj0yCvoSjeiVraVW0ivfFPJ87Bxv7bghW7XPmrtyNVOF0DTtpI0RJjbqXVw_Dflx-YDJbT5AL9Gt1jUQGeiuL5qXlXf2zgeSs2ufDQ/s320/image002-751424.jpg" /></a><o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoListParagraph" style="mso-list: l0 level1 lfo2; text-indent: 0.25in;">
2.<span style="font: 7pt "Times New Roman";"> </span>Add a Sequence Container to that Loop<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal" style="text-indent: .5in;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEidLnTI_kWXrrhQATQLXZYi-72QOdeVhy7YbRUYkG_RO6nOSaHDuL0k9_bQXRtzeZxfDs0Ls9KEUpGD0w78LYgJtxgJTw5x-nBWuS60qbzLzkVJpaLjEKDMDRbK6oxEV6sT7R2bsBjNE6E/s1600/image004-752355.jpg"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5664770462560355138" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEidLnTI_kWXrrhQATQLXZYi-72QOdeVhy7YbRUYkG_RO6nOSaHDuL0k9_bQXRtzeZxfDs0Ls9KEUpGD0w78LYgJtxgJTw5x-nBWuS60qbzLzkVJpaLjEKDMDRbK6oxEV6sT7R2bsBjNE6E/s320/image004-752355.jpg" /></a><o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoListParagraph" style="mso-list: l0 level1 lfo2; text-indent: 0.25in;">
3.<span style="font: 7pt "Times New Roman";"> </span>Set the Sequence Container’s MaximumErrorCount Property To 0 (Zero)<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal" style="text-indent: .5in;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhn1O_YSvFKhY4C6Y7Wo-G-Ql6G1dxWMxeLyyiwdPOtnxUeWr_mJ5Fp-URocSrH1UvjPblxVhamppLBUQuN2A0pP0ba4xJyaDm0SmFSl0byqkQMykBvNIbkLh0EKzCgRdntaPK4Uzp_bro/s1600/image011-753337.jpg"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5664770463255910642" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhn1O_YSvFKhY4C6Y7Wo-G-Ql6G1dxWMxeLyyiwdPOtnxUeWr_mJ5Fp-URocSrH1UvjPblxVhamppLBUQuN2A0pP0ba4xJyaDm0SmFSl0byqkQMykBvNIbkLh0EKzCgRdntaPK4Uzp_bro/s320/image011-753337.jpg" /></a><o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoListParagraph" style="mso-list: l0 level1 lfo2; text-indent: 0.25in;">
4.<span style="font: 7pt "Times New Roman";"> </span>Create an OnError Event Handler for the Sequence Container <o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal" style="text-indent: .5in;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEihdnI9EhFCTI5ng1EgoGhQbMjdWKb5dX4a4SSD1yF8EyL_g0yZ0sHXd6AQyDyrySpRlvRfpyRZXZpbFCmy5959wsuoPEBcRL5Ey0fwKZ9whIt79bq-pi1TVsU1a4Gsi4BFzD2POowbsCU/s1600/image013-754197.jpg"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5664770468662874962" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEihdnI9EhFCTI5ng1EgoGhQbMjdWKb5dX4a4SSD1yF8EyL_g0yZ0sHXd6AQyDyrySpRlvRfpyRZXZpbFCmy5959wsuoPEBcRL5Ey0fwKZ9whIt79bq-pi1TVsU1a4Gsi4BFzD2POowbsCU/s320/image013-754197.jpg" /></a><o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal" style="text-indent: .5in;">
(You can create a custom error logging mechanism here)<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal" style="text-indent: .5in;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgV2LytzmMiya-ygF0KAOSPRlEFK_dtEZr5G2t_gyZL4-J6qJA2hVDDypL_d2lXOmAUbG0OBbxCYqtyL1jCl-XZFLECs9lwo90EpCAxUzvIRoN2uRNcLk5P2RfAxtqdTMKyltf1-qNby98/s1600/image018-755621.jpg"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5664770471790716098" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgV2LytzmMiya-ygF0KAOSPRlEFK_dtEZr5G2t_gyZL4-J6qJA2hVDDypL_d2lXOmAUbG0OBbxCYqtyL1jCl-XZFLECs9lwo90EpCAxUzvIRoN2uRNcLk5P2RfAxtqdTMKyltf1-qNby98/s320/image018-755621.jpg" /></a><o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoListParagraph" style="mso-list: l0 level1 lfo2; text-indent: 0.25in;">
5.<span style="font: 7pt "Times New Roman";"> </span>Set the system variable Propagate to False when in the Event Handler Page<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal" style="text-indent: .5in;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhMrMfGC2CCKKdl33CxZrWUSATl9ZcJkSE1Zp0u1ZeUPYlG5aoHGq68L8Vz_wtFm2dTM7pWX2uJuuY58WIjFczDXPoAxEeX3H0kORIfgn6VKDJZfZosHgFtXG2scA-EohnQMTeLon7q7rA/s1600/image020-756467.jpg"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5664770475910091394" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhMrMfGC2CCKKdl33CxZrWUSATl9ZcJkSE1Zp0u1ZeUPYlG5aoHGq68L8Vz_wtFm2dTM7pWX2uJuuY58WIjFczDXPoAxEeX3H0kORIfgn6VKDJZfZosHgFtXG2scA-EohnQMTeLon7q7rA/s320/image020-756467.jpg" /></a><o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal" style="text-indent: .5in;">
You can see the system variables by clicking the gray button on the Variables Window<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal" style="text-indent: .5in;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiehn7PAa5yD_1dalxuvTZ669Aa0HW83twH1KMKfNM_K2rIw_MUn6d72u0BqLkrAXHzuLOkrgQlBZa01W0l274I54bdHGbZySGIKK0ZBjkOy4OTtAqXm56Qbqy36StxGSPOeRRv0FElObU/s1600/image024-757493.jpg"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5664770480161280754" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiehn7PAa5yD_1dalxuvTZ669Aa0HW83twH1KMKfNM_K2rIw_MUn6d72u0BqLkrAXHzuLOkrgQlBZa01W0l274I54bdHGbZySGIKK0ZBjkOy4OTtAqXm56Qbqy36StxGSPOeRRv0FElObU/s320/image024-757493.jpg" /></a><o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoListParagraph" style="mso-list: l0 level1 lfo2; text-indent: 0.25in;">
6.<span style="font: 7pt "Times New Roman";"> </span>Add your Child Task in the Sequence Container<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal" style="text-indent: .5in;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEidN1U3tZt_hVgsvDtBAWl6U2qcG7MntvQG81d9yk5v6RvlBeeuVO1R_Y20giEtk_-9mdZi9nZdI4Lg-Fq8Im9ZnkgrhNwnUCHzj86Dal7FnUBCGtS-Dpf-BaCh6AcZpjds07KNo3KschA/s1600/image025-758650.jpg"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5664770487210640290" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEidN1U3tZt_hVgsvDtBAWl6U2qcG7MntvQG81d9yk5v6RvlBeeuVO1R_Y20giEtk_-9mdZi9nZdI4Lg-Fq8Im9ZnkgrhNwnUCHzj86Dal7FnUBCGtS-Dpf-BaCh6AcZpjds07KNo3KschA/s320/image025-758650.jpg" /></a><o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal" style="margin-left: .5in;">
In My case I have added a script task that is throwing a dummy Exception using<o:p></o:p></div>
<div class="MsoNormal" style="margin-left: .5in;">
<span style="color: blue; font-family: "Courier New"; font-size: 10pt;">Throw</span><span style="font-family: "Courier New"; font-size: 10pt;"> <span style="color: blue;">New</span> ApplicationException(<span style="color: #a31515;">"Test Exception"</span>)<o:p></o:p></span></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoListParagraph" style="mso-list: l0 level1 lfo2; text-indent: 0.25in;">
7.<span style="font: 7pt "Times New Roman";"> </span>Execute the package and done. The Script task will fail but the Sequence container and the Loop Container will execute successfully<o:p></o:p></div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal" style="text-indent: .5in;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhmmlu0Z4ScFUGRYIOHIt8lCbaF7-Jfto6KQW82emU2QRvn9Y6372DXdsWaCts3frMTtINrsmLYY1tJeT9xzM9-YuTjVnQltqhoh40t_zc6UkSHpGiJXH7E48G0lA-P-KPuhf4uqdVfHY4/s1600/image027-759653.jpg"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5664770492313586850" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhmmlu0Z4ScFUGRYIOHIt8lCbaF7-Jfto6KQW82emU2QRvn9Y6372DXdsWaCts3frMTtINrsmLYY1tJeT9xzM9-YuTjVnQltqhoh40t_zc6UkSHpGiJXH7E48G0lA-P-KPuhf4uqdVfHY4/s320/image027-759653.jpg" /></a><o:p></o:p></div>
</div>
<br /></div>Surender Singh Bhadauriahttp://www.blogger.com/profile/17542565963836942496noreply@blogger.com0tag:blogger.com,1999:blog-7970283394092537253.post-65389051343468112242011-10-17T18:46:00.000+05:302011-10-17T18:46:54.964+05:30SSRS : Passing Parameters to a Report and control Its Display using URL<div dir="ltr" style="text-align: left;" trbidi="on"><div class="Section1"><div class="MsoNormal" > We can use SSRS Reports to be displayed as images directly in our own web page and we can also perform an export without even displaying the report or embed the report in our own Custom UI. This is possible by altering the Url for the Report. We can use the ‘rc:’ and ‘rs:’ parameters of SSRS.</div><div class="MsoNormal" > These parameters help us to customize the way the reports are rendered.</div><div class="MsoNormal" ></div><br />
<br />
<div class="MsoNormal" ><b>rc:</b></div><div class="MsoNormal" style="text-indent: 0.5in;">This parameter prefix means Report Client Command. It is used to alter the way the report is rendered. The most useful parameters included in this are:</div><div class="MsoListParagraph" style="margin-bottom: 0pt; margin-left: 0.75in; mso-list: l1 level2 lfo1; mso-margin-top-alt: 0in; text-indent: 0.25in;"><span class="parameter"><span style="mso-list: Ignore;">1)<span style="font-family: "Times New Roman";"> </span></span></span><span class="parameter">Parameters</span></div><div class="MsoListParagraph" style="margin-bottom: 0pt; margin-left: 1.25in; mso-list: l1 level3 lfo1; mso-margin-top-alt: 0in; text-indent: -9pt;"><span style="font-family: Symbol;"><span style="mso-list: Ignore;">·<span style="font-family: "Times New Roman";"> </span></span></span> It shows or hides the parameters area of the toolbar. It take the values as True, False or Collapsed.</div><div class="MsoListParagraph" style="margin-bottom: 0pt; margin-left: 1.25in; mso-list: l1 level3 lfo1; mso-margin-top-alt: 0in; text-indent: -9pt;"><span style="font-family: Symbol;"><span style="mso-list: Ignore;">·<span style="font-family: "Times New Roman";"> </span></span></span><b>True</b> Means that the Parameters section is displayed. It is the default Value.</div><div class="MsoListParagraph" style="margin-bottom: 0pt; margin-left: 1.25in; mso-list: l1 level3 lfo1; mso-margin-top-alt: 0in; text-indent: -9pt;"><span style="font-family: Symbol;"><span style="mso-list: Ignore;">·<span style="font-family: "Times New Roman";"> </span></span></span><b>False</b> Means that the Parameters section is displayed and the user cannot toggle it to be displayed.</div><div class="MsoListParagraph" style="margin-bottom: 0pt; margin-left: 1.25in; mso-list: l1 level3 lfo1; mso-margin-top-alt: 0in; text-indent: -9pt;"><span style="font-family: Symbol;"><span style="mso-list: Ignore;">·<span style="font-family: "Times New Roman";"> </span></span></span><b>Collapsed</b> Means that the parameters are hidden but can be toggled by the user.<br />
<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiNV_ZeS1NffvLjcS_uWp40XtPHz4PEsLrOTs8L0mVtUNtl1sf4UIeUjEL0g_Y9_KOz4LeixUfuShMaytUqYjQxyFxt6NCCmVaOx6spdT79WfxL5dfWPQXUgN4qLIusERegjp2K1XD9yWY/s1600/image004-716375.jpg"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5664427566765384978" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiNV_ZeS1NffvLjcS_uWp40XtPHz4PEsLrOTs8L0mVtUNtl1sf4UIeUjEL0g_Y9_KOz4LeixUfuShMaytUqYjQxyFxt6NCCmVaOx6spdT79WfxL5dfWPQXUgN4qLIusERegjp2K1XD9yWY/s320/image004-716375.jpg" /></a><br />
</div><div class="MsoListParagraph" style="margin-bottom: 0pt; margin-left: 0.75in; mso-list: l1 level2 lfo1; mso-margin-top-alt: 0in; text-indent: 0.25in;"><span style="mso-list: Ignore;">2)<span style="font-family: "Times New Roman";"> </span></span>Toolbar</div><div class="MsoListParagraph" style="margin-bottom: 0pt; margin-left: 1.25in; mso-list: l1 level3 lfo1; mso-margin-top-alt: 0in; text-indent: -9pt;"><span style="font-family: Symbol;"><span style="mso-list: Ignore;">·<span style="font-family: "Times New Roman";"> </span></span></span>It shows or hides the toolbar. It takes the values as either True or False. </div><div class="MsoListParagraph" style="margin-bottom: 0pt; margin-left: 1.25in; mso-list: l1 level3 lfo1; mso-margin-top-alt: 0in; text-indent: -9pt;"><span style="font-family: Symbol;"><span style="mso-list: Ignore;">·<span style="font-family: "Times New Roman";"> </span></span></span><b>True</b> means that the toolbar is displayed. It is the default Value.</div><div class="MsoListParagraph" style="margin-bottom: 0pt; margin-left: 1.25in; mso-list: l1 level3 lfo1; mso-margin-top-alt: 0in; text-indent: -9pt;"><span style="font-family: Symbol;"><span style="mso-list: Ignore;">·<span style="font-family: "Times New Roman";"> </span></span></span><b>False</b> means that the toolbar is displayed.<br />
<br />
<b><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEganeI3a04f47fxgcykC4fk6CN22EhJS6GGQ8kRtkwQPEJCTeK-9DD7T_b7sla_87Apr4S9oKksyV-U5RdI94s52Wl5bA1vfUT5-iJVoj5qKBu5Ywx5EdF8s89ds0A_mU9AiCctiAENQD8/s1600/image008-716851.jpg"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5664427569202921938" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEganeI3a04f47fxgcykC4fk6CN22EhJS6GGQ8kRtkwQPEJCTeK-9DD7T_b7sla_87Apr4S9oKksyV-U5RdI94s52Wl5bA1vfUT5-iJVoj5qKBu5Ywx5EdF8s89ds0A_mU9AiCctiAENQD8/s320/image008-716851.jpg" /></a></b><br />
<br />
<b></b></div><div class="MsoListParagraph" style="margin-bottom: 0pt; margin-left: 0.75in; mso-list: l1 level2 lfo1; mso-margin-top-alt: 0in; text-indent: 0.25in;"><span class="parameter"><span style="mso-list: Ignore;">3)<span style="font-family: "Times New Roman";"> </span></span></span> <span class="parameter">Section<b></b></span></div><div class="MsoListParagraph" style="margin-bottom: 0pt; margin-left: 1.25in; mso-list: l1 level3 lfo1; mso-margin-top-alt: 0in; text-indent: -9pt;"><span class="parameter"><span style="font-family: Symbol;"><span style="mso-list: Ignore;">·<span style="font-family: "Times New Roman";"> </span></span></span></span><span class="parameter">It specifies the page number of the report to be displayed.<b></b></span></div><div class="MsoListParagraph" style="margin-bottom: 0pt; margin-left: 1.25in; mso-list: l1 level3 lfo1; mso-margin-top-alt: 0in; text-indent: -9pt;"><span style="font-family: Symbol;"><span style="mso-list: Ignore;">·<span style="font-family: "Times New Roman";"> </span></span></span>The default value is <span class="input">1</span>.<b></b></div><div class="MsoListParagraph" style="margin-bottom: 0pt; margin-left: 1.25in; mso-list: l1 level3 lfo1; mso-margin-top-alt: 0in; text-indent: -9pt;"><span style="font-family: Symbol;"><span style="mso-list: Ignore;">·<span style="font-family: "Times New Roman";"> </span></span></span>If its value is less than 1 then page no 1 is displayed.<b></b></div><div class="MsoListParagraph" style="margin-bottom: 0pt; margin-left: 1.25in; mso-list: l1 level3 lfo1; mso-margin-top-alt: 0in; text-indent: -9pt;"><span style="font-family: Symbol;"><span style="mso-list: Ignore;">·<span style="font-family: "Times New Roman";"> </span></span></span>If its value is greater than total pages in report then the last page is displayed.<b></b></div><div class="MsoListParagraph" style="margin-bottom: 0pt; margin-left: 0.75in; mso-list: l1 level2 lfo1; mso-margin-top-alt: 0in; text-indent: 0.25in;"><span style="mso-list: Ignore;">4)<span style="font-family: "Times New Roman";"> </span></span>Stylesheet<b></b></div><div class="MsoListParagraph" style="margin-bottom: 0pt; margin-left: 1.25in; mso-list: l1 level3 lfo1; mso-margin-top-alt: 0in; text-indent: -9pt;"><span style="font-family: Symbol;"><span style="mso-list: Ignore;">·<span style="font-family: "Times New Roman";"> </span></span></span>It specifies the CSS that has to be applied to the viewer<b></b></div><div class="MsoListParagraph" style="margin-bottom: 0pt; margin-left: 0.75in; mso-list: l1 level2 lfo1; mso-margin-top-alt: 0in; text-indent: 0.25in;"><span class="parameter"><span style="mso-list: Ignore;">5)<span style="font-family: "Times New Roman";"> </span></span></span><span class="parameter">Zoom<b></b></span></div><div class="MsoListParagraph" style="margin-bottom: 0pt; margin-left: 1.25in; mso-list: l1 level3 lfo1; mso-margin-top-alt: 0in; text-indent: -9pt;"><span class="parameter"><span style="font-family: Symbol;"><span style="mso-list: Ignore;">·<span style="font-family: "Times New Roman";"> </span></span></span></span><span class="parameter">It specified the zooming factor for the report<b></b></span></div><div class="MsoNormal" ></div><br />
<br />
<div class="MsoNormal" ><b>rs:</b></div><div class="MsoNormal" style="text-indent: 0.5in;">This parameter prefix means Report Server Command. It is used to alter the way the report is executed. The most useful parameters included in this are: </div><div class="MsoListParagraph" style="margin-bottom: 0pt; margin-left: 0.75in; mso-list: l2 level1 lfo3; mso-margin-top-alt: 0in; text-indent: 0.25in;"><span class="parameter"><span style="mso-list: Ignore;">1)<span style="font-family: "Times New Roman";"> </span></span></span>Format<span class="parameter"><b> </b></span></div><div class="MsoListParagraph" style="margin-bottom: 0pt; margin-left: 1.5in; mso-list: l0 level1 lfo4; mso-margin-top-alt: 0in; text-indent: 0.25in;"><span class="parameter"><span style="font-family: Symbol;"><span style="mso-list: Ignore;">·<span style="font-family: "Times New Roman";"> </span></span></span></span><span class="parameter">It specifies the format in which the report has to be exported.<b></b></span></div><div class="MsoListParagraph" style="margin-bottom: 0pt; margin-left: 1.5in; mso-list: l0 level1 lfo4; mso-margin-top-alt: 0in; text-indent: 0.25in;"><span class="parameter"><span style="font-family: Symbol;"><span style="mso-list: Ignore;">·<span style="font-family: "Times New Roman";"> </span></span></span></span><span class="parameter">The report is not rendered instead it is given as a downloadable file in the requested format.<b></b></span></div><div class="MsoListParagraph" style="margin-bottom: 0pt; margin-left: 1.5in; mso-list: l0 level1 lfo4; mso-margin-top-alt: 0in; text-indent: 0.25in;"><span class="parameter"><span style="font-family: Symbol;"><span style="mso-list: Ignore;">·<span style="font-family: "Times New Roman";"> </span></span></span></span><span class="parameter">The valid formats are :<b></b></span></div><div class="MsoListParagraph" style="margin-bottom: 0pt; margin-left: 2in; mso-list: l0 level2 lfo4; mso-margin-top-alt: 0in; text-indent: 0.25in;"><span style="font-family: "Courier New";"><span style="mso-list: Ignore;">o<span style="font-family: "Times New Roman";"> </span></span></span>HTML3.2</div><div class="MsoListParagraph" style="margin-bottom: 0pt; margin-left: 2in; mso-list: l0 level2 lfo4; mso-margin-top-alt: 0in; text-indent: 0.25in;"><span style="font-family: "Courier New";"><span style="mso-list: Ignore;">o<span style="font-family: "Times New Roman";"> </span></span></span>HTML4.0</div><div class="MsoListParagraph" style="margin-bottom: 0pt; margin-left: 2in; mso-list: l0 level2 lfo4; mso-margin-top-alt: 0in; text-indent: 0.25in;"><span style="font-family: "Courier New";"><span style="mso-list: Ignore;">o<span style="font-family: "Times New Roman";"> </span></span></span>MHTML</div><div class="MsoListParagraph" style="margin-bottom: 0pt; margin-left: 2in; mso-list: l0 level2 lfo4; mso-margin-top-alt: 0in; text-indent: 0.25in;"><span style="font-family: "Courier New";"><span style="mso-list: Ignore;">o<span style="font-family: "Times New Roman";"> </span></span></span>IMAGE</div><div class="MsoListParagraph" style="margin-bottom: 0pt; margin-left: 2in; mso-list: l0 level2 lfo4; mso-margin-top-alt: 0in; text-indent: 0.25in;"><span style="font-family: "Courier New";"><span style="mso-list: Ignore;">o<span style="font-family: "Times New Roman";"> </span></span></span>EXCEL</div><div class="MsoListParagraph" style="margin-bottom: 0pt; margin-left: 2in; mso-list: l0 level2 lfo4; mso-margin-top-alt: 0in; text-indent: 0.25in;"><span style="font-family: "Courier New";"><span style="mso-list: Ignore;">o<span style="font-family: "Times New Roman";"> </span></span></span>WORD</div><div class="MsoListParagraph" style="margin-bottom: 0pt; margin-left: 2in; mso-list: l0 level2 lfo4; mso-margin-top-alt: 0in; text-indent: 0.25in;"><span style="font-family: "Courier New";"><span style="mso-list: Ignore;">o<span style="font-family: "Times New Roman";"> </span></span></span>CSV</div><div class="MsoListParagraph" style="margin-bottom: 0pt; margin-left: 2in; mso-list: l0 level2 lfo4; mso-margin-top-alt: 0in; text-indent: 0.25in;"><span style="font-family: "Courier New";"><span style="mso-list: Ignore;">o<span style="font-family: "Times New Roman";"> </span></span></span>PDF</div><div class="MsoListParagraph" style="margin-bottom: 0pt; margin-left: 2in; mso-list: l0 level2 lfo4; mso-margin-top-alt: 0in; text-indent: 0.25in;"><span style="font-family: "Courier New";"><span style="mso-list: Ignore;">o<span style="font-family: "Times New Roman";"> </span></span></span>XML</div><div class="MsoListParagraph" style="margin-bottom: 0pt; margin-left: 0.75in; mso-list: l2 level1 lfo3; mso-margin-top-alt: 0in; text-indent: 0.25in;"><span style="mso-list: Ignore;">2)<span style="font-family: "Times New Roman";"> </span></span>Command</div><div class="MsoListParagraph" style="margin-bottom: 0pt; margin-left: 1.5in; mso-list: l2 level3 lfo3; mso-margin-top-alt: 0in; text-indent: 0.25in;"><span style="font-family: Symbol;"><span style="mso-list: Ignore;">·<span style="font-family: "Times New Roman";"> </span></span></span>It specifies the action to apply to the path, such as render a report or list all of the items in a folder. Defaults to Render for reports.</div><div class="MsoListParagraph" style="margin-bottom: 0pt; margin-left: 1.5in; mso-list: l2 level3 lfo3; mso-margin-top-alt: 0in; text-indent: 0.25in;"><span style="font-family: Symbol;"><span style="mso-list: Ignore;">·<span style="font-family: "Times New Roman";"> </span></span></span>It takes the following values</div><div class="MsoListParagraph" style="margin-bottom: 0pt; margin-left: 2in; mso-list: l2 level4 lfo3; mso-margin-top-alt: 0in; text-indent: 0.25in;"><span style="font-family: "Courier New";"><span style="mso-list: Ignore;">o<span style="font-family: "Times New Roman";"> </span></span></span>Render (Default)</div><div class="MsoListParagraph" style="margin-bottom: 0pt; margin-left: 2in; mso-list: l2 level4 lfo3; mso-margin-top-alt: 0in; text-indent: 0.25in;"><span style="font-family: "Courier New";"><span style="mso-list: Ignore;">o<span style="font-family: "Times New Roman";"> </span></span></span>ListChildren</div><div class="MsoListParagraph" style="margin-bottom: 0pt; margin-left: 2in; mso-list: l2 level4 lfo3; mso-margin-top-alt: 0in; text-indent: 0.25in;"><span style="font-family: "Courier New";"><span style="mso-list: Ignore;">o<span style="font-family: "Times New Roman";"> </span></span></span>GetResourceContents</div><div class="MsoListParagraph" style="margin-bottom: 0pt; margin-left: 2in; mso-list: l2 level4 lfo3; mso-margin-top-alt: 0in; text-indent: 0.25in;"><span style="font-family: "Courier New";"><span style="mso-list: Ignore;">o<span style="font-family: "Times New Roman";"> </span></span></span>GetDataSourceContents</div><div class="MsoListParagraph" style="margin-bottom: 0pt; margin-left: 0.75in; mso-list: l2 level1 lfo3; mso-margin-top-alt: 0in; text-indent: 0.25in;"><span style="mso-list: Ignore;">3)<span style="font-family: "Times New Roman";"> </span></span>Snapshot</div><div class="MsoListParagraph" style="margin-bottom: 0pt; margin-left: 1.5in; mso-list: l2 level3 lfo3; mso-margin-top-alt: 0in; text-indent: 0.25in;"><span style="font-family: Symbol;"><span style="mso-list: Ignore;">·<span style="font-family: "Times New Roman";"> </span></span></span>It specifies the snapshot of the report to be rendered</div><div class="MsoNormal" ></div><br />
<br />
<div class="MsoNormal" style="text-indent: 0.5in;">If the report has parameters, these parameters can be passed by specifying the parameters in the URL as </div><div class="MsoNormal" ></div><br />
<br />
<div class="MsoNormal" style="text-indent: 0.5in;"><i>‘&ParameterName=ParameterValue’<br />
</i></div><div class="MsoNormal" style="text-indent: 0.5in;"><i>Note *</i></div><div class="MsoNormal" style="margin-bottom: 0pt; margin-left: 1in; mso-margin-top-alt: 0in; text-indent: 0.5in;">The Multivalued parameters are passed as comma separated values.</div><div class="MsoNormal" style="text-indent: 0.5in;"></div><br />
<br />
<div class="MsoNormal" style="margin-bottom: 0pt; margin-left: 1in; mso-margin-top-alt: 0in; text-indent: 0.5in;">The Date type parameters must be passed as MM/dd/yyyy</div><div class="MsoNormal" style="text-indent: 0.5in;"></div><br />
<br />
<div class="MsoNormal" style="margin-bottom: 0pt; margin-left: 1in; mso-margin-top-alt: 0in; text-indent: 0.5in;">If the rc: parameters is set to false then the parameters values must be passed in the URL or the default values must be specified else it will throw an exception</div><div class="MsoNormal" ><i>Example</i></div><div class="MsoNormal" ><i></i><br />
<div class="MsoNormal" ><i><a href="http://server/ReportServer/Pages/ReportViewer.aspx?%2fBlog%2fSSRS_Blog%2fABC&rs:Command=Render&Color=silver">http://Server/ReportServer/Pages/ReportViewer.aspx?%2fBlog%2fSSRS_Blog%2fABC&rs:Command=Render&Color=silver</a></i></div><div class="MsoNormal" ><i></i><br />
<div class="MsoNormal" ><i><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjNzn53tnc0r1n2WQsUWzaW24DmC_MjwECOZYZ2KjGEOA9qpyYodvWqkeES7AGoSz-GgCmPhrTyBr1yxBFi-rG6eB1LQHYujpKla0VduGzyXvmH8tsULdMIbuddr7KLeSS6C-nkbTajzwE/s1600/image009-717301.jpg"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5664427570899392610" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjNzn53tnc0r1n2WQsUWzaW24DmC_MjwECOZYZ2KjGEOA9qpyYodvWqkeES7AGoSz-GgCmPhrTyBr1yxBFi-rG6eB1LQHYujpKla0VduGzyXvmH8tsULdMIbuddr7KLeSS6C-nkbTajzwE/s320/image009-717301.jpg" /></a></i></div><div class="MsoNormal" ></div><br />
</div></div></div> </div>Surender Singh Bhadauriahttp://www.blogger.com/profile/17542565963836942496noreply@blogger.com0tag:blogger.com,1999:blog-7970283394092537253.post-16140952489901480322011-10-17T18:35:00.000+05:302011-10-17T18:35:00.175+05:30SSIS : Using Proxy Account to execute a Package<div dir="ltr" style="text-align: left;" trbidi="on"><div class="Section1"><div class="MsoNormal" style=" text-indent: 0.25in;">Sometimes we need the package to execute with the credentials and rights of a certain user and we don’t want the Integration Services to be executed through that user. So in such a case we can use a proxy account to execute the package. The use of proxy account is not limited to just the SSIS packages we can use it for other tasks as well. Let’s see how can we create a proxy account.</div><div class="MsoNormal" ></div><div class="MsoListParagraph" style="; mso-list: l1 level1 lfo1; text-indent: 0.25in;"><span style="mso-list: Ignore;">1.<span style="font-family: "Times New Roman";"> </span></span>Open the SQL Server Management Studio<br />
<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiM1dCQgAtZeXb2goTfyZmKCbXiKcpFThGgRpqGnzPjTZbjasj6SyjpvPbic2MqYt97MbWg1gv-xxnbphHJZjbmKrak1hjtAc7lonLeCqesp3sVQv-SFBMD3oF9dGK09rjyYJev95uPSBY/s1600/image007-758115.jpg"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5664358597580158402" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiM1dCQgAtZeXb2goTfyZmKCbXiKcpFThGgRpqGnzPjTZbjasj6SyjpvPbic2MqYt97MbWg1gv-xxnbphHJZjbmKrak1hjtAc7lonLeCqesp3sVQv-SFBMD3oF9dGK09rjyYJev95uPSBY/s320/image007-758115.jpg" /></a><br />
</div><div class="MsoListParagraph" style="mso-list: l1 level1 lfo1; text-indent: 0.25in;"><span style="mso-list: Ignore;">2.<span style="font-family: "Times New Roman";"> </span></span>Login to the server on which you want to create a proxy account<br />
<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhGEOoAPgRNjJobLUHKS7Sc3h6Z73Go8JNqlai4f_cXnls83306iAk0rbmGdqIGGI0GRDZkcoxDBBvmKN4O8Dh22d2JC1VS9vtzzcxcUmSknM1z8DSZZn_31nQQ5_ehdJoUKH7Hzz1adOQ/s1600/image008-758837.jpg"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5664358597829145666" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhGEOoAPgRNjJobLUHKS7Sc3h6Z73Go8JNqlai4f_cXnls83306iAk0rbmGdqIGGI0GRDZkcoxDBBvmKN4O8Dh22d2JC1VS9vtzzcxcUmSknM1z8DSZZn_31nQQ5_ehdJoUKH7Hzz1adOQ/s320/image008-758837.jpg" /></a><br />
</div><div class="MsoListParagraph" style="mso-list: l1 level1 lfo1; text-indent: 0.25in;"><span style="mso-list: Ignore;">3.<span style="font-family: "Times New Roman";"> </span></span>Select New Credentials by right clicking on Credentials under Security<br />
<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiRqEKabnQV2YDb98xJ0chXlhwS8QGqpCHdMUkytlSlep_6DhearXb7W2gNhPgiGdQrvsG2mGzJ_qWHA008vRDBmxOSiSWS6Anp1t1zZxYJAVBEtGJ9Zx2SHWtvSmCI8oWmYAb0AMJoW-g/s1600/image009-759531.jpg"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5664358604466792882" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiRqEKabnQV2YDb98xJ0chXlhwS8QGqpCHdMUkytlSlep_6DhearXb7W2gNhPgiGdQrvsG2mGzJ_qWHA008vRDBmxOSiSWS6Anp1t1zZxYJAVBEtGJ9Zx2SHWtvSmCI8oWmYAb0AMJoW-g/s320/image009-759531.jpg" /></a><br />
</div><div class="MsoListParagraph" style="mso-list: l1 level1 lfo1; text-indent: 0.25in;"><span style="mso-list: Ignore;">4.<span style="font-family: "Times New Roman";"> </span></span>Give some name to the credential being created<br />
<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhsHTEapDCqDdFE7nLcugiEPTtWk8gP7uRk-Tx1txmHvgq3IiRLJrncuFFpTNKWgoHZVOpv5QgnRUkx2KOmxJBPUAX2lBX1ClXn4Oprt5YYuIQROINm8zMmCoW6OgMW-jH91Tb1_p7BPM0/s1600/image011-761046.jpg"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5664358611700004482" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhsHTEapDCqDdFE7nLcugiEPTtWk8gP7uRk-Tx1txmHvgq3IiRLJrncuFFpTNKWgoHZVOpv5QgnRUkx2KOmxJBPUAX2lBX1ClXn4Oprt5YYuIQROINm8zMmCoW6OgMW-jH91Tb1_p7BPM0/s320/image011-761046.jpg" /></a><br />
</div><div class="MsoListParagraph" style=" mso-list: l1 level1 lfo1; text-indent: 0.25in;"><span style="mso-list: Ignore;">5.<span style="font-family: "Times New Roman";"> </span></span>Select the user or type its name in the Identity Box<br />
<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgf2UuWhyxfVGFewYYj2fNQVa50aB5Zm_IJRP6FssQgow0rSpseITDAYityzi44yQynXD_Ey-5is3bsLHn0s5-7Nm4i6dkkyLf2VXdmpI6uLa8ySg43mIplUsWLtKr27HXqmo86WEwwap8/s1600/image014-762056.jpg"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5664358615979813170" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgf2UuWhyxfVGFewYYj2fNQVa50aB5Zm_IJRP6FssQgow0rSpseITDAYityzi44yQynXD_Ey-5is3bsLHn0s5-7Nm4i6dkkyLf2VXdmpI6uLa8ySg43mIplUsWLtKr27HXqmo86WEwwap8/s320/image014-762056.jpg" /></a><br />
</div><div class="MsoListParagraph" style=" mso-list: l1 level1 lfo1; text-indent: 0.25in;"><span style="mso-list: Ignore;">6.<span style="font-family: "Times New Roman";"> </span></span>Enter the password for the selected user and Hit OK<br />
<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjYK4-33fPACBaD_a3eyJHdSkoKRE_8AUR2TVmbvsJA1zd4qdIqpJYc6lUtN-K5bLqeCWMPgvEmCzadEa57h6RPjgChj_ctE-AmdPJ2b0GnVWSJSQWAQ62gdmTtaeKtBddrGQQjvto3JeI/s1600/image016-762813.jpg"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5664358618096480866" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjYK4-33fPACBaD_a3eyJHdSkoKRE_8AUR2TVmbvsJA1zd4qdIqpJYc6lUtN-K5bLqeCWMPgvEmCzadEa57h6RPjgChj_ctE-AmdPJ2b0GnVWSJSQWAQ62gdmTtaeKtBddrGQQjvto3JeI/s320/image016-762813.jpg" /></a><br />
</div><div class="MsoListParagraph" style="mso-list: l1 level1 lfo1; text-indent: 0.25in;"><span style="mso-list: Ignore;">7.<span style="font-family: "Times New Roman";"> </span></span>The credential is now created<br />
<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjp_jA54cFuZx6SMIrEEUtMsVR4f-cc5LbYLE8z4xSi-KkWyI4BvD2ZqkzAEsQJPNdejz_j9rCnWtAqmB0CUAVuRcGBxldeFClf2U5BHwBnf3cJ0QctezEAcee22Qs3Ny5NMOT8HAlehr0/s1600/image025-764268.jpg"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5664358624807406690" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjp_jA54cFuZx6SMIrEEUtMsVR4f-cc5LbYLE8z4xSi-KkWyI4BvD2ZqkzAEsQJPNdejz_j9rCnWtAqmB0CUAVuRcGBxldeFClf2U5BHwBnf3cJ0QctezEAcee22Qs3Ny5NMOT8HAlehr0/s320/image025-764268.jpg" /></a><br />
</div><div class="MsoListParagraph" style="mso-list: l1 level1 lfo1; text-indent: 0.25in;"><span style="mso-list: Ignore;">8.<span style="font-family: "Times New Roman";"> </span></span>Now right click SSIS Package Execution under Proxies in SQL Server Agent and select New Proxy<br />
<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiUjATdccdXCX9zFpq4karex-45hf8dyw3ZfOLGXO6xC9WCnIf79GHDdqiSSpwFNfFj2yEbDTpZVpIxqX2JPwlYjSIpDUkB4MEEoQ_CK54ecC6PpDt5N-S4ErLETD7pd5ppBcvQB4GDid8/s1600/image026-765108.jpg"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5664358630557756754" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiUjATdccdXCX9zFpq4karex-45hf8dyw3ZfOLGXO6xC9WCnIf79GHDdqiSSpwFNfFj2yEbDTpZVpIxqX2JPwlYjSIpDUkB4MEEoQ_CK54ecC6PpDt5N-S4ErLETD7pd5ppBcvQB4GDid8/s320/image026-765108.jpg" /></a><br />
</div><div class="MsoListParagraph" style="mso-list: l1 level1 lfo1; text-indent: 0.25in;"><span style="mso-list: Ignore;">9.<span style="font-family: "Times New Roman";"> </span></span>Give a name to your Proxy<br />
<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEim9cC5HqkPLNy4ZIAtWDWBUA6eTM1Uya143QHYkfuLi3Vo1NnES9_-L-_d9AoPQwy_jhSSxFOgqM7-SXVNTAXS6tqb6yQd2s0wtcs4QsRPPC7Ot3mOrQ017gEuWRalLKHp6sX7D_c8bfY/s1600/image027-765936.jpg"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5664358628981940914" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEim9cC5HqkPLNy4ZIAtWDWBUA6eTM1Uya143QHYkfuLi3Vo1NnES9_-L-_d9AoPQwy_jhSSxFOgqM7-SXVNTAXS6tqb6yQd2s0wtcs4QsRPPC7Ot3mOrQ017gEuWRalLKHp6sX7D_c8bfY/s320/image027-765936.jpg" /></a><br />
</div><div class="MsoListParagraph" style="mso-list: l1 level1 lfo1; text-indent: 0.25in;"><span style="mso-list: Ignore;">10.<span style="font-family: "Times New Roman";"> </span></span>Select the credential <i>‘Admin’ </i>created earlier and then select the role for the proxy and Hit OK<br />
<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjKCF0HBxhWxM8cc6hpbBgRC1N_qXkQOpFwDpbwou8DngA5ICqYkAkbi_zXjvqXABJh_ZtK1zsm4fDw66r4TzNb50iLxWPGLKzEdBNcMSb7DZMQ9ylTEIkRoRx_lhbT_zrs8B5f28MHUqM/s1600/image029-767456.jpg"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5664358636375665218" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjKCF0HBxhWxM8cc6hpbBgRC1N_qXkQOpFwDpbwou8DngA5ICqYkAkbi_zXjvqXABJh_ZtK1zsm4fDw66r4TzNb50iLxWPGLKzEdBNcMSb7DZMQ9ylTEIkRoRx_lhbT_zrs8B5f28MHUqM/s320/image029-767456.jpg" /></a><br />
</div><div class="MsoNormal" style="text-indent: 0.25in;">So now the proxy has been created, lets configure a SSIS package in a job using this proxy.</div><div class="MsoNormal" style="margin-right: 525.05pt; text-indent: 0.25in;"></div><br />
<br />
<div class="MsoListParagraph" style="mso-list: l0 level1 lfo2; text-indent: 0.25in;"><span style="mso-list: Ignore;">1.<span style="font-family: "Times New Roman";"> </span></span>Create a new job by selecting New Job after right clicking Jobs under SQL Server Agent<br />
<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhvZ_0LoqJyVU75doPy9h32NvV6S6ybBPLCRFFWk2q25f99ZcQXRCJwCVUUgw1w7DZo4cBVrkUMmLZ3B3ExX8tli7dnqxI8NR4aW61GBJUzSfRNgA2VpGc1aCGaNSQqtOHNSTepLxrYgGQ/s1600/image035-767956.jpg"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5664358640271207186" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhvZ_0LoqJyVU75doPy9h32NvV6S6ybBPLCRFFWk2q25f99ZcQXRCJwCVUUgw1w7DZo4cBVrkUMmLZ3B3ExX8tli7dnqxI8NR4aW61GBJUzSfRNgA2VpGc1aCGaNSQqtOHNSTepLxrYgGQ/s320/image035-767956.jpg" /></a><br />
</div><div class="MsoListParagraph" style="mso-list: l0 level1 lfo2; text-indent: 0.25in;"><span style="mso-list: Ignore;">2.<span style="font-family: "Times New Roman";"> </span></span>Give a name to the job<br />
<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhjGOYB_zxehedmt_O8XY2MIzxHX6OgIY89UMxFrPwf3k7Agg_xOIRMeF0hDyW3cX2FldxVq6FLBXDhEB3as7t0uEpZcoWraYTBWm2ypGDZNNOwgFzLPXuf4tBSAA5PTjLtbe5_gqaKsWM/s1600/image036-769723.jpg"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5664358647816621794" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhjGOYB_zxehedmt_O8XY2MIzxHX6OgIY89UMxFrPwf3k7Agg_xOIRMeF0hDyW3cX2FldxVq6FLBXDhEB3as7t0uEpZcoWraYTBWm2ypGDZNNOwgFzLPXuf4tBSAA5PTjLtbe5_gqaKsWM/s320/image036-769723.jpg" /></a><br />
</div><div class="MsoListParagraph" style=" mso-list: l0 level1 lfo2; text-indent: 0.25in;"><span style="mso-list: Ignore;">3.<span style="font-family: "Times New Roman";"> </span></span>Go to steps Page and click on New<br />
<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgDBCYS0wjXaOWMFAGlN_PZP0ggtAiQcCD5Mt8et1Z4BGMZ0bNr31lMQI8_VBtSp5PavF85E55xxwbCHSuHZrIdh1gJ1DdqdPyQTnhq55q9gq4rM12e2-y4mAC6k-b7_sU4Zxp3Q8uA614/s1600/image038-770263.jpg"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5664358652708831778" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgDBCYS0wjXaOWMFAGlN_PZP0ggtAiQcCD5Mt8et1Z4BGMZ0bNr31lMQI8_VBtSp5PavF85E55xxwbCHSuHZrIdh1gJ1DdqdPyQTnhq55q9gq4rM12e2-y4mAC6k-b7_sU4Zxp3Q8uA614/s320/image038-770263.jpg" /></a><br />
</div><div class="MsoListParagraph" style="mso-list: l0 level1 lfo2; text-indent: 0.25in;"><span style="mso-list: Ignore;">4.<span style="font-family: "Times New Roman";"> </span></span>Now select <i>‘Admin’ </i>in Run As and schedule the package<br />
<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjCAueKr8DvdDfy-tKjwgApkDGp_gc1-QXRqLyUTF0yU8T1dC66YSdMgZpUvH_d6Ba2qrqHACkU5hSq4YOsmbOEQb8I6Vkw3XZo2oNjJRsIvidAiL6Ee4am9QMS8TG1sVNtRzTC8nQrsuw/s1600/image042-771244.jpg"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5664358653740358146" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjCAueKr8DvdDfy-tKjwgApkDGp_gc1-QXRqLyUTF0yU8T1dC66YSdMgZpUvH_d6Ba2qrqHACkU5hSq4YOsmbOEQb8I6Vkw3XZo2oNjJRsIvidAiL6Ee4am9QMS8TG1sVNtRzTC8nQrsuw/s320/image042-771244.jpg" /></a><br />
</div><div class="MsoListParagraph" style="mso-list: l0 level1 lfo2; text-indent: 0.25in;"><span style="mso-list: Ignore;">5.<span style="font-family: "Times New Roman";"> </span></span>And it’s done the job will be executed using the credentials of the selected user.</div></div></div>Surender Singh Bhadauriahttp://www.blogger.com/profile/17542565963836942496noreply@blogger.com1tag:blogger.com,1999:blog-7970283394092537253.post-91657072289422889602011-10-16T22:40:00.000+05:302011-10-16T23:36:13.689+05:30Transact SQL : Running SQL Server Agent Job Status<div dir="ltr" style="text-align: left;" trbidi="on">As we know the SQL Server Agent Job Activity monitor is a great tool but it doesn't shows us at what time the job has started execution and how much time has passed by. So we can use the below script to get the list of running jobs along with the time period for which they are running<br />
<br />
<table border="1" cellpadding="0" cellspacing="0" class="MsoTableGrid" style="border-collapse: collapse; border: none;"><tbody>
<tr> <td style="border: solid windowtext 1.0pt; padding: 0in 5.4pt 0in 5.4pt; width: 6.65in;" valign="top" width="638"><br />
<div style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-autospace: none;"><span style="color: blue; font-family: "Courier New"; font-size: 10pt;">create</span><span style="font-family: "Courier New"; font-size: 10pt;"> <span style="color: blue;">table </span>#enum_job<span style="color: blue;"> </span><span style="color: grey;"> </span></span></div><div style="line-height: normal; margin-bottom: 0in;"><span style="font-family: "Courier New"; font-size: 10pt;"><span style="color: grey;">(</span></span></div><div style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-autospace: none;"><span style="font-family: "Courier New"; font-size: 10pt;">Job_ID <span style="color: blue;"><br />
uniqueidentifier</span><span style="color: grey;">,</span></span></div><div style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-autospace: none;"><span style="font-family: "Courier New"; font-size: 10pt;">Last_Run_Date <span style="color: blue;"><br />
int</span><span style="color: grey;">,</span></span></div><div style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-autospace: none;"><span style="font-family: "Courier New"; font-size: 10pt;">Last_Run_Time <span style="color: blue;"><br />
int</span><span style="color: grey;">,</span></span></div><div style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-autospace: none;"><span style="font-family: "Courier New"; font-size: 10pt;">Next_Run_Date <span style="color: blue;"><br />
int</span><span style="color: grey;">,</span></span></div><div style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-autospace: none;"><span style="font-family: "Courier New"; font-size: 10pt;">Next_Run_Time <span style="color: blue;"><br />
int</span><span style="color: grey;">,</span></span></div><div style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-autospace: none;"><span style="font-family: "Courier New"; font-size: 10pt;">Next_Run_Schedule_ID <span style="color: blue;">int</span><span style="color: grey;">,</span></span></div><div style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-autospace: none;"><span style="font-family: "Courier New"; font-size: 10pt;">Requested_To_Run <span style="color: blue;">int</span><span style="color: grey;">,</span></span></div><div style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-autospace: none;"><span style="font-family: "Courier New"; font-size: 10pt;">Request_Source <span style="color: blue;">int</span><span style="color: grey;">,</span></span></div><div style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-autospace: none;"><span style="font-family: "Courier New"; font-size: 10pt;">Request_Source_ID <span style="color: blue;">varchar</span><span style="color: grey;">(</span>100<span style="color: grey;">),</span></span></div><div style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-autospace: none;"><span style="font-family: "Courier New"; font-size: 10pt;">Running <span style="color: blue;"><br />
int</span><span style="color: grey;">,</span></span></div><div style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-autospace: none;"><span style="font-family: "Courier New"; font-size: 10pt;">Current_Step <span style="color: blue;"><br />
int</span><span style="color: grey;">,</span></span></div><div style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-autospace: none;"><span style="font-family: "Courier New"; font-size: 10pt;">Current_Retry_Attempt <span style="color: blue;">int</span><span style="color: grey;">,</span></span></div><div style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-autospace: none;"><span style="color: blue; font-family: "Courier New"; font-size: 10pt;">State</span><span style="font-family: "Courier New"; font-size: 10pt;"> <span style="color: blue;">int</span></span></div><div style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-autospace: none;"><span style="color: grey; font-family: "Courier New"; font-size: 10pt;">)</span><span style="font-family: "Courier New"; font-size: 10pt;"> </span></div><div style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-autospace: none;"><span style="color: blue; font-family: "Courier New"; font-size: 10pt;">insert</span><span style="font-family: "Courier New"; font-size: 10pt;"> <span style="color: blue;">into </span>#enum_job<span style="color: blue;"><br />
exec</span> <span style="color: blue;">master</span><span style="color: grey;">.</span>dbo<span style="color: grey;">.</span><span style="color: maroon;">xp_sqlagent_enum_jobs</span><span style="color: blue;"> </span>1<span style="color: grey;">,</span>garbage</span></div><div style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-autospace: none;"><span style="color: blue; font-family: "Courier New"; font-size: 10pt;">select</span><span style="font-family: "Courier New"; font-size: 10pt;"> R<span style="color: grey;">.</span>[name]<span style="color: grey;"><br />
,</span> R<span style="color: grey;">.</span>last_run_date<span style="color: grey;">,</span>R<span style="color: grey;">.</span>RunningForTime<span style="color: grey;">,</span><br />
<span style="color: magenta;">getdate</span><span style="color: grey;">()</span><span style="color: blue;">as</span> <span style="color: blue;">now</span> <span style="color: blue;"><br />
from</span> #enum_job a</span></div><div style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-autospace: none;"><span style="color: grey; font-family: "Courier New"; font-size: 10pt;">inner</span><span style="font-family: "Courier New"; font-size: 10pt;"> <span style="color: grey;">join</span><span style="color: blue;"> </span><span style="color: grey;">(</span></span></div><div style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-autospace: none;"><br />
</div><div style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-autospace: none;"><span style="color: blue; font-family: "Courier New"; font-size: 10pt;">SELECT</span><span style="font-family: "Courier New"; font-size: 10pt;"> j<span style="color: grey;">.</span>job_id<span style="color: grey;">,</span>j<span style="color: grey;">.</span>name<span style="color: grey;">,</span><br />
ja<span style="color: grey;">.</span>run_requested_date <span style="color: blue;">AS</span><br />
last_run_date<span style="color: grey;">,(</span><span style="color: magenta;">DATEDIFF</span><span style="color: grey;">(</span>mi<span style="color: grey;">,</span>ja<span style="color: grey;">.</span>run_requested_date<span style="color: grey;">,</span><span style="color: magenta;">getdate</span><span style="color: grey;">()))</span><br />
<span style="color: blue;">AS</span> RunningFor<span style="color: grey;">,</span></span></div><div style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-autospace: none;"><br />
</div><div style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-autospace: none;"><span style="color: blue; font-family: "Courier New"; font-size: 10pt;">Case</span><span style="font-family: "Courier New"; font-size: 10pt;"> <span style="color: magenta;"><br />
Len</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>5<span style="color: grey;">),</span><span style="color: magenta;">DATEDIFF</span><span style="color: grey;">(</span>ss<span style="color: grey;">,</span>ja<span style="color: grey;">.</span>run_requested_date<span style="color: grey;">,</span><span style="color: magenta;">getdate</span><span style="color: grey;">())/</span>3600<span style="color: grey;">))</span></span></div><div style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-autospace: none;"><span style="color: blue; font-family: "Courier New"; font-size: 10pt;">when</span><span style="font-family: "Courier New"; font-size: 10pt;"> 1 <span style="color: blue;">then</span><br />
<span style="color: red;">'0'</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>5<span style="color: grey;">),</span><span style="color: magenta;">DATEDIFF</span><span style="color: grey;">(</span>ss<span style="color: grey;">,</span>ja<span style="color: grey;">.</span>run_requested_date<span style="color: grey;">,</span><span style="color: magenta;">getdate</span><span style="color: grey;">())/</span>3600<span style="color: grey;">)</span></span></div><div style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-autospace: none;"><span style="color: blue; font-family: "Courier New"; font-size: 10pt;">else</span><span style="font-family: "Courier New"; font-size: 10pt;"> <span style="color: magenta;"><br />
Convert</span><span style="color: grey;">(</span><span style="color: blue;">varchar</span><span style="color: grey;">(</span>5<span style="color: grey;">),</span><span style="color: magenta;">DATEDIFF</span><span style="color: grey;">(</span>ss<span style="color: grey;">,</span>ja<span style="color: grey;">.</span>run_requested_date<span style="color: grey;">,</span><span style="color: magenta;">getdate</span><span style="color: grey;">())/</span>3600<span style="color: grey;">)</span></span></div><div style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-autospace: none;"><span style="color: blue; font-family: "Courier New"; font-size: 10pt;">end</span><span style="font-family: "Courier New"; font-size: 10pt;"> <span style="color: grey;">+</span><br />
<span style="color: red;">':'</span> <span style="color: grey;">+</span> <span style="color: blue;"><br />
Case</span> <span style="color: magenta;">Len</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>5<span style="color: grey;">),((</span><span style="color: magenta;">DATEDIFF</span><span style="color: grey;">(</span>ss<span style="color: grey;">,</span>ja<span style="color: grey;">.</span>run_requested_date<span style="color: grey;">,</span><span style="color: magenta;">getdate</span><span style="color: grey;">())/</span>60<span style="color: grey;">)%</span>60<span style="color: grey;">)))</span></span></div><div style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-autospace: none;"><span style="color: blue; font-family: "Courier New"; font-size: 10pt;">when</span><span style="font-family: "Courier New"; font-size: 10pt;"> 1 <span style="color: blue;">then</span><br />
<span style="color: red;">'0'</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>5<span style="color: grey;">),((</span><span style="color: magenta;">DATEDIFF</span><span style="color: grey;">(</span>ss<span style="color: grey;">,</span>ja<span style="color: grey;">.</span>run_requested_date<span style="color: grey;">,</span><span style="color: magenta;">getdate</span><span style="color: grey;">())/</span>60<span style="color: grey;">)%</span>60<span style="color: grey;">))</span></span></div><div style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-autospace: none;"><span style="color: blue; font-family: "Courier New"; font-size: 10pt;">else</span><span style="font-family: "Courier New"; font-size: 10pt;"> <span style="color: magenta;"><br />
Convert</span><span style="color: grey;">(</span><span style="color: blue;">varchar</span><span style="color: grey;">(</span>5<span style="color: grey;">),((</span><span style="color: magenta;">DATEDIFF</span><span style="color: grey;">(</span>ss<span style="color: grey;">,</span>ja<span style="color: grey;">.</span>run_requested_date<span style="color: grey;">,</span><span style="color: magenta;">getdate</span><span style="color: grey;">())/</span>60<span style="color: grey;">)%</span>60<span style="color: grey;">))</span><br />
<span style="color: blue;">end</span> <span style="color: grey;">+</span> <span style="color: red;"><br />
':'</span> <span style="color: grey;">+</span> <span style="color: blue;">Case</span><br />
<span style="color: magenta;">Len</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>5<span style="color: grey;">),(</span><span style="color: magenta;">DATEDIFF</span><span style="color: grey;">(</span>ss<span style="color: grey;">,</span>ja<span style="color: grey;">.</span>run_requested_date<span style="color: grey;">,</span><span style="color: magenta;">getdate</span><span style="color: grey;">())%</span>60<span style="color: grey;">)))</span></span></div><div style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-autospace: none;"><span style="color: blue; font-family: "Courier New"; font-size: 10pt;">when</span><span style="font-family: "Courier New"; font-size: 10pt;"> 1 <span style="color: blue;">then</span><br />
<span style="color: red;">'0'</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>5<span style="color: grey;">),(</span><span style="color: magenta;">DATEDIFF</span><span style="color: grey;">(</span>ss<span style="color: grey;">,</span>ja<span style="color: grey;">.</span>run_requested_date<span style="color: grey;">,</span><span style="color: magenta;">getdate</span><span style="color: grey;">())%</span>60<span style="color: grey;">))</span></span></div><div style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-autospace: none;"><span style="color: blue; font-family: "Courier New"; font-size: 10pt;">else</span><span style="font-family: "Courier New"; font-size: 10pt;"> <span style="color: magenta;"><br />
Convert</span><span style="color: grey;">(</span><span style="color: blue;">varchar</span><span style="color: grey;">(</span>5<span style="color: grey;">),(</span><span style="color: magenta;">DATEDIFF</span><span style="color: grey;">(</span>ss<span style="color: grey;">,</span>ja<span style="color: grey;">.</span>run_requested_date<span style="color: grey;">,</span><span style="color: magenta;">getdate</span><span style="color: grey;">())%</span>60<span style="color: grey;">))</span></span></div><div style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-autospace: none;"><span style="color: blue; font-family: "Courier New"; font-size: 10pt;">end</span><span style="font-family: "Courier New"; font-size: 10pt;"> <span style="color: blue;">AS</span><br />
RunningForTime </span></div><div style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-autospace: none;"><span style="color: blue; font-family: "Courier New"; font-size: 10pt;">FROM</span><span style="font-family: "Courier New"; font-size: 10pt;"> msdb<span style="color: grey;">.</span>dbo<span style="color: grey;">.</span>sysjobactivity <span style="color: blue;">AS</span><br />
ja <span style="color: grey;">LEFT</span> <span style="color: grey;">OUTER</span><br />
<span style="color: grey;">JOIN</span></span></div><div style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-autospace: none;"><span style="font-family: "Courier New"; font-size: 10pt;">msdb<span style="color: grey;">.</span>dbo<span style="color: grey;">.</span>sysjobhistory <span style="color: blue;">AS</span> jh<br />
<span style="color: blue;">ON</span> ja<span style="color: grey;">.</span>job_history_id<br />
<span style="color: grey;">=</span> jh<span style="color: grey;">.</span>instance_id<br />
<span style="color: grey;">INNER</span> <span style="color: grey;">JOIN</span></span></div><div style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-autospace: none;"><span style="font-family: "Courier New"; font-size: 10pt;">msdb<span style="color: grey;">.</span>dbo<span style="color: grey;">.</span><span style="color: green;">sysjobs_view</span> <span style="color: blue;">AS</span> j <span style="color: blue;">ON</span> ja<span style="color: grey;">.</span>job_id<br />
<span style="color: grey;">=</span> j<span style="color: grey;">.</span>job_id</span></div><div style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-autospace: none;"><span style="color: blue; font-family: "Courier New"; font-size: 10pt;">WHERE </span><br />
<span style="color: grey; font-family: "Courier New"; font-size: 10pt;">(</span><span style="font-family: "Courier New"; font-size: 10pt;">ja<span style="color: grey;">.</span>session_id<br />
<span style="color: grey;">=</span></span> <span style="color: grey; font-family: "Courier New"; font-size: 10pt;">(</span><span style="color: blue; font-family: "Courier New"; font-size: 10pt;">SELECT</span><span style="font-family: "Courier New"; font-size: 10pt;"> <span style="color: magenta;"><br />
MAX</span><span style="color: grey;">(</span>session_id<span style="color: grey;">)</span><br />
<span style="color: blue;">AS</span> EXPR1<span style="color: blue;"><br />
FROM</span> msdb<span style="color: grey;">.</span>dbo<span style="color: grey;">.</span>sysjobactivity<span style="color: grey;">))</span></span></div><div style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-autospace: none;"><span style="color: grey; font-family: "Courier New"; font-size: 10pt;">)</span><span style="font-family: "Courier New"; font-size: 10pt;"> R</span></div><div style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-autospace: none;"><span style="color: blue; font-family: "Courier New"; font-size: 10pt;">on</span><span style="font-family: "Courier New"; font-size: 10pt;"> R<span style="color: grey;">.</span>job_id<span style="color: grey;">=</span> a<span style="color: grey;">.</span>Job_Id <span style="color: grey;">and</span> a<span style="color: grey;">.</span>Running <span style="color: grey;">=</span> 1</span></div><div style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-autospace: none;"><br />
</div><div style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><span style="color: blue; font-family: "Courier New"; font-size: 10pt;">drop</span><span style="font-family: "Courier New"; font-size: 10pt;"> <span style="color: blue;">table </span>#enum_job</span></div></td> </tr>
</tbody></table><br />
<br />
</div>Surender Singh Bhadauriahttp://www.blogger.com/profile/17542565963836942496noreply@blogger.com0tag:blogger.com,1999:blog-7970283394092537253.post-14802512182002843312011-10-16T16:59:00.000+05:302011-10-16T22:49:12.973+05:30SSIS : Redirecting Error Rows<div dir="ltr" style="text-align: left;" trbidi="on"><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjVXnTNAVT3YbTdzboPlQEAT7QY6s1pAcrw_aHle3Ig6UzEF7s72B4mrd8fEZUSQL1QHk17faoF_K9HGXEtg_-j1s-nGoS-SKPj_2MUYxyw6CuG4idhPj_vnjC-5bq990ss7epOXuoBqNU/s1600/Over+View.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="250" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjVXnTNAVT3YbTdzboPlQEAT7QY6s1pAcrw_aHle3Ig6UzEF7s72B4mrd8fEZUSQL1QHk17faoF_K9HGXEtg_-j1s-nGoS-SKPj_2MUYxyw6CuG4idhPj_vnjC-5bq990ss7epOXuoBqNU/s400/Over+View.png" width="400" /></a></div><br />
<div class="separator" style="clear: both; text-align: center;"></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgsEeHZBZ5Pcdh4nNmC5IC_AwSpzFO3ByWEnuK_gZEuFgRrpQ59vG_M-A4YubWS8ZM0Bc6VSbbU4LhBGPrYq6WB0L-ol3vtdB7DmvpUgm1R-cM1qUIt34tKJFmrua7-HRG6gb4TI7IAoGw/s1600/Error.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"></a></div><br />
<ol style="text-align: left;"><li>Set the Connection manager for the Data Source </li>
<li>Set the Option of redirection in the Error Output Page of the Source Editor<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiZ1rnObgKVIvNMV1JQYSHOJSDUBamoWIXxsUDd5jIG0bYf7Uth3YBx7RX0DiDIOG7GVa-0B3qL83HFN641FPYwbr1elnDdKnebEHFrTFrE9O_G-PXkFdKwbbqhmB4u0e9Ga2TaaRrQPTc/s1600/Over+View.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="337" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiZ1rnObgKVIvNMV1JQYSHOJSDUBamoWIXxsUDd5jIG0bYf7Uth3YBx7RX0DiDIOG7GVa-0B3qL83HFN641FPYwbr1elnDdKnebEHFrTFrE9O_G-PXkFdKwbbqhmB4u0e9Ga2TaaRrQPTc/s400/Over+View.png" width="400" /></a> </li>
<li> Create a table for the Error Rows (Not necessarily SQL)<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhaQmzkORKzjhw9jEaHL2IMuE3HFP1zZzfzksBi0WP1kNI_lCpMqAfGs9esBm0USYKMvnwbdS3Ye_SXFtbP6RYff7PuZ4zRGY7zJ5CC7E_dCqrHTzdpgWGtIrqg8Lf8XwjIMZfMkdv5_Xg/s1600/Error.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="167" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhaQmzkORKzjhw9jEaHL2IMuE3HFP1zZzfzksBi0WP1kNI_lCpMqAfGs9esBm0USYKMvnwbdS3Ye_SXFtbP6RYff7PuZ4zRGY7zJ5CC7E_dCqrHTzdpgWGtIrqg8Lf8XwjIMZfMkdv5_Xg/s400/Error.png" width="400" /> </a></li>
<li> Configure a Data Destination for the error rows <img border="0" height="345" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgsEeHZBZ5Pcdh4nNmC5IC_AwSpzFO3ByWEnuK_gZEuFgRrpQ59vG_M-A4YubWS8ZM0Bc6VSbbU4LhBGPrYq6WB0L-ol3vtdB7DmvpUgm1R-cM1qUIt34tKJFmrua7-HRG6gb4TI7IAoGw/s400/Error.png" width="400" /> </li>
<li>Done Execute the package and the data with error goes to the Error Destination <br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj7eU9Icxz1XzM1EO97jCYqhNrgHxxCjDc-u2WRN7NZK_oFhv5NTcIetXi8CTuekZ5O2euql8qC3FRl_FQiUQry0q8GsHrfRTwqfi4deCikQlMktOYH0IUIypc6EOgHSBsd7xisnlf19GI/s1600/Error.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="211" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj7eU9Icxz1XzM1EO97jCYqhNrgHxxCjDc-u2WRN7NZK_oFhv5NTcIetXi8CTuekZ5O2euql8qC3FRl_FQiUQry0q8GsHrfRTwqfi4deCikQlMktOYH0IUIypc6EOgHSBsd7xisnlf19GI/s400/Error.png" width="400" /></a></div> </li>
</ol></div>Surender Singh Bhadauriahttp://www.blogger.com/profile/17542565963836942496noreply@blogger.com0