tag:blogger.com,1999:blog-77138846382134032152024-03-05T09:31:31.431-08:00Quest for KnowledgeManish Sharmahttp://www.blogger.com/profile/01694083809722708790noreply@blogger.comBlogger19125tag:blogger.com,1999:blog-7713884638213403215.post-37104371309498995682012-10-16T00:11:00.000-07:002012-10-16T00:13:33.497-07:00SSIS TUTORIALS: Important links<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
<h3 style="background-color: white; border-bottom-color: rgb(170, 170, 170); border-bottom-style: solid; border-bottom-width: 1px; font-family: Verdana, Geneva, Arial, Helvetica, sans-serif;">
<span style="font-size: small;">
You can expect:</span></h3>
<span style="background-color: white;"><span style="font-family: Verdana, Geneva, Arial, Helvetica, sans-serif;">This is a low intensity post (you should be able to do this in your sleep). I'm trying to collect all important links which will be useful for all new SSIS ( SQL Server Integration Services ) learners.</span></span><br />
<span style="font-family: Verdana, Geneva, Arial, Helvetica, sans-serif;">I can see many of the SSIS MSDN forum post for the same and very usual.</span><br />
<span style="font-family: Verdana, Geneva, Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Verdana, Geneva, Arial, Helvetica, sans-serif;"><br /></span>
<br />
<br />
<h3 style="background-color: white; border-bottom-color: rgb(170, 170, 170); border-bottom-style: solid; border-bottom-width: 1px; font-family: Verdana, Geneva, Arial, Helvetica, sans-serif;">
<span style="font-size: small;">
SSIS TUTORIALS: Important links:</span></h3>
<span style="font-family: Verdana, Geneva, Arial, Helvetica, sans-serif;">here you can find the important links for your learning which may help you.</span><br />
<div>
<span style="font-family: Verdana, Geneva, Arial, Helvetica, sans-serif;"><br /></span></div>
<div>
<span style="font-family: Verdana, Geneva, Arial, Helvetica, sans-serif;"><b>ONLINE</b></span></div>
<div>
<span style="font-family: Verdana, Geneva, Arial, Helvetica, sans-serif;">this is MSDN link which guide you to enhance your knowledge</span></div>
<div>
<ul style="text-align: left;">
<li><a href="http://msdn.microsoft.com/en-us/sqlserver/cc511477.aspx">http://msdn.microsoft.com/en-us/sqlserver/cc511477.aspx</a><span style="font-family: Verdana, Geneva, Arial, Helvetica, sans-serif;"> </span></li>
<li><span style="font-family: Verdana, Geneva, Arial, Helvetica, sans-serif;"><a href="http://msdn.microsoft.com/en-us/library/ms141026(v=sql.105).aspx">http://msdn.microsoft.com/en-us/library/ms141026(v=sql.105).aspx</a></span></li>
<li><a href="http://msdn.microsoft.com/en-us/library/bb522537(v=sql.105).aspx">http://msdn.microsoft.com/en-us/library/bb522537(v=sql.105).aspx</a></li>
</ul>
</div>
<div>
<span style="font-family: Verdana, Geneva, Arial, Helvetica, sans-serif;"><br /></span></div>
<div>
<span style="font-family: Verdana, Geneva, Arial, Helvetica, sans-serif;"><br /></span></div>
<div>
<span style="font-family: Verdana, Geneva, Arial, Helvetica, sans-serif;"></span><br />
<div>
<span style="font-family: Verdana, Geneva, Arial, Helvetica, sans-serif;"><b>LOCAL SYSTEM</b></span></div>
<span style="font-family: Verdana, Geneva, Arial, Helvetica, sans-serif;">
<div>
when you have SQL Server installed go to </div>
<div>
<i>Start>All Programs>Microsoft SQL Server 2008 R2>Documentation and Tutorials>SQL Server Tutorials</i>.</div>
<div>
<br /></div>
<div>
<br /></div>
<div>
<b>BOOKS</b></div>
<div>
these books are paid services, you can read these books online but membership is required. here you can search for others relevant books also.</div>
<div>
<br /></div>
<div>
free E-Book</div>
<div>
<ul style="text-align: left;">
<li><a href="http://karthikeyananbarasan.files.wordpress.com/2011/10/free-ebook-sql-server-integration-services-ssis-step-by-step-version-2-0.pdf">http://karthikeyananbarasan.files.wordpress.com/2011/10/free-ebook-sql-server-integration-services-ssis-step-by-step-version-2-0.pdf</a></li>
</ul>
</div>
<div>
<br /></div>
<div>
Safari Books </div>
<div>
<ul style="text-align: left;">
<li><a href="http://www.safaribooksonline.com/search/site/SSIS">http://www.safaribooksonline.com/search/site/SSIS</a></li>
</ul>
</div>
<div>
<br /></div>
<div>
wrox.com</div>
<div>
<ul style="text-align: left;">
<li><a href="http://www.wrox.com/WileyCDA/WroxTitle/Professional-Microsoft-SQL-Server-2008-Integration-Services.productCd-0470247959.html">http://www.wrox.com/WileyCDA/WroxTitle/Professional-Microsoft-SQL-Server-2008-Integration-Services.productCd-0470247959.html</a></li>
</ul>
</div>
<div>
amazon.com</div>
<div>
<ul style="text-align: left;">
<li><a href="http://www.amazon.com/Professional-Microsoft-Integration-Services-Programmer/dp/0470247959">http://www.amazon.com/Professional-Microsoft-Integration-Services-Programmer/dp/0470247959</a></li>
</ul>
</div>
<div>
<br /></div>
<div>
Integration Services Videos</div>
<div>
<br /></div>
<div>
<ul style="text-align: left;">
<li><a href="http://msdn.microsoft.com/en-us/library/dd299421.aspx">http://msdn.microsoft.com/en-us/library/dd299421.aspx</a></li>
</ul>
<ul style="text-align: left;">
<li><a href="http://www.ssistutorial.com/video.php">http://www.ssistutorial.com/video.php</a></li>
</ul>
</div>
<div>
<br /></div>
<div>
Other Important Links</div>
<div>
<br /></div>
<div>
<ul style="text-align: left;">
<li><a href="http://www.mssqltips.com/sqlservertutorial/200/sql-server-integration-services-ssis/">http://www.mssqltips.com/sqlservertutorial/200/sql-server-integration-services-ssis/</a></li>
</ul>
<ul style="text-align: left;">
<li><a href="http://www.accelebrate.com/sql_training/ssis_tutorial.htm">http://www.accelebrate.com/sql_training/ssis_tutorial.htm</a></li>
</ul>
<div>
<br /></div>
</div>
<div>
<h3 style="background-color: white; border-bottom-color: rgb(170, 170, 170); border-bottom-style: solid; border-bottom-width: 1px;">
<span style="font-size: small;">
Request:</span></h3>
As learning never ends so I have a humble request to all readers. If you guys find out some other helpful links for SSIS learning, Please share with me or comment.</div>
</span></div>
</div>
Manish Sharmahttp://www.blogger.com/profile/01694083809722708790noreply@blogger.com0tag:blogger.com,1999:blog-7713884638213403215.post-30887591018537782622011-08-18T00:27:00.000-07:002011-08-18T05:34:54.063-07:00Delete Empty Excel work sheets dynamically through SSIS<div dir="ltr" style="text-align: left;" trbidi="on">Yesterday I visited the SSIS forum and come across this requirement (delete empty excel sheets dynamically from a excel workbook). In SSIS we have to write the Script in Script Task. We've to use COM API to do this.<br />
<span class="Apple-style-span" style="color: #333333; font-family: 'lucida grande', tahoma, verdana, arial, sans-serif;"><span class="Apple-style-span" style="font-size: 11px; line-height: 14px;"><br />
</span></span><br />
Follow below steps to solve this problem.<br />
<br />
<b><span class="Apple-style-span" style="color: #660000;">Step1:</span></b> Open your BIDS and Use a Script Task under the control flow.<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh-iU9a4873RSRVFypI9VRFZ7W2C05RVk1-ew330E5QzD_V0K5W327ICZCSXW-dPPxjO-aQbGfD7ySe4YSYZm0jtDk0PaaWpDbWsSes8KdO44uqeQVt2-MsYTj_1mhvFknUi1DLZmhIkaQ/s1600/ScriptTask.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="217" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh-iU9a4873RSRVFypI9VRFZ7W2C05RVk1-ew330E5QzD_V0K5W327ICZCSXW-dPPxjO-aQbGfD7ySe4YSYZm0jtDk0PaaWpDbWsSes8KdO44uqeQVt2-MsYTj_1mhvFknUi1DLZmhIkaQ/s320/ScriptTask.JPG" width="320" /></a></div><br />
<span class="Apple-style-span" style="color: #660000;"><b>Step 2:</b></span> Select the Script language in which you are comfortable. Here I'm using the VB Script and than click on the Edit Script button.<br />
<br />
<span class="Apple-style-span" style="color: #660000;"><b>Step3:</b></span> In the Script Editor you need to add the <b style="background-color: #6aa84f;">"Microsoft.Office.Interop.Excel.dll"</b> in your reference.<br />
<br />
if you want to use this DLL, following these steps:<br />
<br />
<ul style="text-align: left;"><li>On the Project menu, click Add Reference.</li>
<li>On the COM tab, locate Microsoft Excel Object Library, and then click Select. In Visual Studio 2008, locate Microsoft Excel 12.0 Object Library on the COM tab.</li>
<li>Click OK in the Add References dialog box to accept your selections. If you are prompted to generate wrappers for the libraries that you selected, click “Yes”.</li>
</ul><br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg9P8evG_a4UORhWd85G1ZK3z_SMExDpFWQ8okT2c9SAQh67fzzr4rqvUBQuqZ-vrG2q6vGehxnO4w6YxseobGmD6HMTNrwpRNRlTqKAmu4GHWb3ZxXTmQaM6zaFvOijG9no38zxh9m0cY/s1600/AddRefernce.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="356" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg9P8evG_a4UORhWd85G1ZK3z_SMExDpFWQ8okT2c9SAQh67fzzr4rqvUBQuqZ-vrG2q6vGehxnO4w6YxseobGmD6HMTNrwpRNRlTqKAmu4GHWb3ZxXTmQaM6zaFvOijG9no38zxh9m0cY/s640/AddRefernce.JPG" width="640" /></a></div><div><br />
</div><div><br />
</div><div><b><span class="Apple-style-span" style="color: #660000;">Step 4:</span></b> you need to import the following reference in your code.</div><div>Imports Microsoft.Office.Interop.Excel</div><div><br />
</div><div><br />
</div><div><b><span class="Apple-style-span" style="color: #660000;">Step 5:</span></b> Write the following code and change the appropriate path for your excel file ( you can use the SSIS variable for passing the file name)</div><br />
<br />
<br />
<span class="Apple-style-span" style="color: blue;">Public Sub Main()</span><br />
<span class="Apple-style-span" style="color: blue;"> Dim Wexcel As Microsoft.Office.Interop.Excel.Application = New Microsoft.Office.Interop.Excel.Application()</span><br />
<span class="Apple-style-span" style="color: blue;"> Dim Wb As Workbook = Wexcel.Workbooks.Open("C:\emptyExcelSheet.xls")</span><br />
<span class="Apple-style-span" style="color: blue;"> Dim wf As WorksheetFunction = Wexcel.WorksheetFunction</span><br />
<span class="Apple-style-span" style="color: blue;"><br />
</span><br />
<span class="Apple-style-span" style="color: blue;"> For Each sheet In Wexcel.Worksheets </span><br />
<span class="Apple-style-span" style="color: blue;"> If wf.CountA(sheet.Cells) = 0 Then</span><br />
<span class="Apple-style-span" style="color: blue;"> Wexcel.DisplayAlerts = False</span><br />
<span class="Apple-style-span" style="color: blue;"> sheet.Delete()</span><br />
<span class="Apple-style-span" style="color: blue;"> Wexcel.DisplayAlerts = True</span><br />
<span class="Apple-style-span" style="color: blue;"><br />
</span><br />
<span class="Apple-style-span" style="color: blue;"> End If</span><br />
<span class="Apple-style-span" style="color: blue;"> Next sheet</span><br />
<span class="Apple-style-span" style="color: blue;"> Dts.TaskResult = ScriptResults.Success</span><br />
<span class="Apple-style-span" style="color: blue;"> End Sub</span><br />
<br />
<b><span class="Apple-style-span" style="color: #660000;">Step 6:</span></b> Build the code first and then close the script editor. Now you can execute your package and test the results.<br />
<br />
Hope this helps you..<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh9dleBVMVrwkWq7O4zFq5uvouhOhhWBttUazfQrMbF8gZ2jUDZG7UadDe8u4yYGfVK7bNaDqcwPFvqCK0LuoYbLhbcRYJ6oiyygZ-4x5n4te2dUxwV_MzYEIq449zGl0Lp4q68HeI4DJk/s1600/s.bmp" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh9dleBVMVrwkWq7O4zFq5uvouhOhhWBttUazfQrMbF8gZ2jUDZG7UadDe8u4yYGfVK7bNaDqcwPFvqCK0LuoYbLhbcRYJ6oiyygZ-4x5n4te2dUxwV_MzYEIq449zGl0Lp4q68HeI4DJk/s1600/s.bmp" /></a></div><br />
</div>Manish Sharmahttp://www.blogger.com/profile/01694083809722708790noreply@blogger.com0tag:blogger.com,1999:blog-7713884638213403215.post-7305530458961081542011-08-10T11:30:00.000-07:002011-08-11T02:18:46.338-07:00SSIS - Denali vs 2008 - Control Flow<div dir="ltr" style="text-align: left;" trbidi="on">Hurry !!! Denali (SQL Server 2011) is available for all Techic's now. In the last post I've explained about the installation steps for denali. After the installation, I opened the SSIS and I found a single new member(Task) in control flow where as missing below 2 tasks:<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi4vb2XUXcVe6OzN-WD-rUbLeyx-y4qoK5WAULG07wHUIz1RFgZUwacuIshKjR10V6lkL8PPvZyj2cSEU-KPtN4LbT0KBCQ2JBEC7LNPWoILJeMuuXXnSFFDXC9mJ4FzM9toGbPWdYwZ1g/s1600/listoftask.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="156" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi4vb2XUXcVe6OzN-WD-rUbLeyx-y4qoK5WAULG07wHUIz1RFgZUwacuIshKjR10V6lkL8PPvZyj2cSEU-KPtN4LbT0KBCQ2JBEC7LNPWoILJeMuuXXnSFFDXC9mJ4FzM9toGbPWdYwZ1g/s400/listoftask.JPG" width="400" /></a></div><br />
<br />
<div class="separator" style="clear: both; text-align: center;"></div><div class="separator" style="clear: both; text-align: center;"></div>Below image is showing the SSIS Denali vs SSIS 2008 Control Flow tool list. There is NO big change in the SSIS again(after a first look) ...<br />
<br />
<strong> SSIS 2008 SSIS Denali</strong><br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg-8ek49ZNmDGbTJgC5rP4m7cIuGNj_2bIcrJjy-UArW71k2maqEExC2fcyQk-dipm1lxK_iVe_6hb9m8t4uUEY5WgxEPsSqoo2-o1kTI7x-ZH2sHqJU8jP8mjRoY6YC-3_Z4ds18WHTCI/s1600/SSIS_Denali_vs_2008.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="640" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg-8ek49ZNmDGbTJgC5rP4m7cIuGNj_2bIcrJjy-UArW71k2maqEExC2fcyQk-dipm1lxK_iVe_6hb9m8t4uUEY5WgxEPsSqoo2-o1kTI7x-ZH2sHqJU8jP8mjRoY6YC-3_Z4ds18WHTCI/s640/SSIS_Denali_vs_2008.png" width="596" /></a></div><div class="separator" style="clear: both; text-align: center;"><br />
</div><div class="separator" style="clear: both; text-align: center;"><br />
</div></div>Manish Sharmahttp://www.blogger.com/profile/01694083809722708790noreply@blogger.com0tag:blogger.com,1999:blog-7713884638213403215.post-49387797457109014332011-08-10T08:08:00.000-07:002012-10-18T20:36:39.732-07:00SQL SERVER :: Denali (2012) :: Step By Step Installation Guide<div dir="ltr" style="text-align: left;" trbidi="on">
<div class="separator" style="clear: both; text-align: center;">
</div>
New era is available in the database and BI market and its name is "SQL Server Denali". Believe me, you'll definitely love to work on it. Yesterday night I installed this on my laptop and here I'm sharing my experience with you.<br />
<br />
<span class="Apple-style-span" style="background-color: white; color: #333333; font-family: verdana; font-size: 12px; line-height: 21px;">Installation is quite straight-forward, but you would find additional tab pages while installation. In the below screen you can see new features for selection like Data Quality Services, Data Quality Client, Distributed Replay Controller, and Distributed Replay Client.</span><br />
<br />
You can download it from following link<br />
<br />
<a href="http://www.microsoft.com/sqlserver/en/us/future-editions.aspx">http://www.microsoft.com/sqlserver/en/us/future-editions.aspx</a><br />
<a href="http://www.softpedia.com/get/Internet/Servers/Database-Utils/Microsoft-SQL-Server.shtml">http://www.softpedia.com/get/Internet/Servers/Database-Utils/Microsoft-SQL-Server.shtml</a><br />
(This is free till today... Not sure if you need to pay tomorrow<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgYuZf9Koc_UKvfqmBfgFAq9bB4CVRq26lG0XW9QGaKJB6mV9qUb7frnFYl8huSrrVo22RfdHy3aN_TOMOs5EiPXOF1HSmiNsCYy_AXvmoEnsKZ2sjkqUsi0PZfhONCq6OB66tWOJzRkjE/s1600/s.bmp" imageanchor="1" style="clear: left; display: inline !important; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgYuZf9Koc_UKvfqmBfgFAq9bB4CVRq26lG0XW9QGaKJB6mV9qUb7frnFYl8huSrrVo22RfdHy3aN_TOMOs5EiPXOF1HSmiNsCYy_AXvmoEnsKZ2sjkqUsi0PZfhONCq6OB66tWOJzRkjE/s1600/s.bmp" /></a>)<br />
<br />
<span class="Apple-style-span" style="color: blue;"><b><br />
</b></span><br />
After download you will get 3 files in your folder and once you click on the installation file you will get the last file (SQLFULL_x86_ENU_Lang.box)<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgPFgrJOhL4oxU0uI-qRtejn5BWQRNMMUvS96S2xTiR5qVtolcgfrtMxv_uh0MorPqqFTD3nQBsyshX9xQpLdQKzNLE57pkePAXsA4kfcEZU_yXd1FOCYpnEIa42N2cOn9MMXWgCDWWr0o/s1600/FileNames.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="99" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgPFgrJOhL4oxU0uI-qRtejn5BWQRNMMUvS96S2xTiR5qVtolcgfrtMxv_uh0MorPqqFTD3nQBsyshX9xQpLdQKzNLE57pkePAXsA4kfcEZU_yXd1FOCYpnEIa42N2cOn9MMXWgCDWWr0o/s640/FileNames.JPG" width="640" /></a></div>
<br />
<br />
Once you click on application file it'll extract the Set-up file and all supporting file in the folder named "SQLFULL_x86_ENU".<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjZnWmwd6ampLV0VQ2WyKR6nB2oieDUAj0Kyt1XVawgUIPueEDD1o4gakXaYeqDNTiMjVZYLZn1ofc3vO8kXsDkEKv7GSXzRtvb-j05xlqeRlPYZktwPp1rWzG8XHlMxUgGpMPDlh0RPnA/s1600/extract.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="125" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjZnWmwd6ampLV0VQ2WyKR6nB2oieDUAj0Kyt1XVawgUIPueEDD1o4gakXaYeqDNTiMjVZYLZn1ofc3vO8kXsDkEKv7GSXzRtvb-j05xlqeRlPYZktwPp1rWzG8XHlMxUgGpMPDlh0RPnA/s400/extract.JPG" width="400" /></a></div>
<br />
You'll find the following file and folders in the "SQLFULL_x86_ENU" folder.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiONJ0Gyx3wyed5aePCb8iSGtu5L8OYaX6x1-dUf2tjtURCjvlnhz376ycH7Q2EeCya_IqnRfyDoEI3c2MjINYJLWlJR5NChCUJFfzE1au7vUXIS0Go1drK-sBgnABJVxC7-a2M9OawAi8/s1600/extractedFiles.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="211" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiONJ0Gyx3wyed5aePCb8iSGtu5L8OYaX6x1-dUf2tjtURCjvlnhz376ycH7Q2EeCya_IqnRfyDoEI3c2MjINYJLWlJR5NChCUJFfzE1au7vUXIS0Go1drK-sBgnABJVxC7-a2M9OawAi8/s640/extractedFiles.JPG" width="640" /></a></div>
<br />
<span class="Apple-style-span" style="background-color: yellow; font-family: Verdana, sans-serif; font-size: large;"><span class="Apple-style-span" style="color: red;">Important:</span><span class="Apple-style-span" style="color: blue;"> While Installation you must be connected to internet for the updates (Optional). It'll download approximately 500MB data for installation.</span></span><br />
<b><span class="Apple-style-span" style="color: blue;"><br />
</span></b><br />
Now you need to double click on Setup.exe file in this folder and get ready for some more configuration setting which is important while for the installation. This installation quit similar to SQL Server - 2008.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjYxEn6TFYHqXQsXr1gvyYbNQvyltzggC4LWDoR6HOrBm1xpY2F08793aOMr3yZjeHnilN1YesoLY2A4enEP7ElzgNvZm0wIi1YNou7Pc9I4NeFs1NR0WSrY8LNbcloUxg_xNcr5mjM8Lw/s1600/DenaliSetup.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="156" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjYxEn6TFYHqXQsXr1gvyYbNQvyltzggC4LWDoR6HOrBm1xpY2F08793aOMr3yZjeHnilN1YesoLY2A4enEP7ElzgNvZm0wIi1YNou7Pc9I4NeFs1NR0WSrY8LNbcloUxg_xNcr5mjM8Lw/s640/DenaliSetup.JPG" width="640" /></a></div>
<br />
Now you can see the default window on your screen. It is showing the Planning tab.. as follow.<br />
Here you can analyze your requirement and do the planning accordingly .<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
</div>
<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/AVvXsEhTz15XMV_bP1dep48n4-XTZloQn9sDqv5Eu1GCHQmZ7V-XBvoaGxLmU5dJliinhROYGAw1qZXyIWmhVXFeHXP-gtr8QLLipr_2hvv78_SLe0qiMuXyaeigtGv97E9Tmzru-mmuExFEowg/s1600/DefaultScreen.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="480" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhTz15XMV_bP1dep48n4-XTZloQn9sDqv5Eu1GCHQmZ7V-XBvoaGxLmU5dJliinhROYGAw1qZXyIWmhVXFeHXP-gtr8QLLipr_2hvv78_SLe0qiMuXyaeigtGv97E9Tmzru-mmuExFEowg/s640/DefaultScreen.JPG" width="640" /></a></div>
<br />
Next is Installation tab, where you can select the appropriate option for the installation. Here I'm selecting the first option "New SQL Server Stand-alone installation"<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOyNfUsj30dDMxDhW5gw2VZ6BDLS5deqwNJntCMY1JKeG1mof8D9KGR_P2ymYlVVpcfv78S1prP2JTsDACo_NRrqYCSmS0ZrdxhXyFiw8y1bpn_DYKIDzgtMpxHCpMByzKxir5uBLQX2A/s1600/Installation.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="480" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOyNfUsj30dDMxDhW5gw2VZ6BDLS5deqwNJntCMY1JKeG1mof8D9KGR_P2ymYlVVpcfv78S1prP2JTsDACo_NRrqYCSmS0ZrdxhXyFiw8y1bpn_DYKIDzgtMpxHCpMByzKxir5uBLQX2A/s640/Installation.JPG" width="640" /></a></div>
<br />
Now I'm just adding few Images which will come across your installation process where you need not to do more. Simply click to next. :)<br />
<br />
Setup Support Rules, simply check the following operation on your system. If its fail you need to rectify that and then you need to start again. After this you just go with the flow.<br />
<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhAQhSaBn4xaLOYWRjdU_4qr6uZlovp2r-V0g97GyHYus803VtzwtzBAq4A9JJWKW4Zyj22Mt3EH0fa_mvZksSiF5mmAeOwdcYfQHKOHNSqfTmZgo8ieXzRsQkp6F9ezplIZbXJO1hnAeM/s1600/setupSupportRules.bmp" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="480" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhAQhSaBn4xaLOYWRjdU_4qr6uZlovp2r-V0g97GyHYus803VtzwtzBAq4A9JJWKW4Zyj22Mt3EH0fa_mvZksSiF5mmAeOwdcYfQHKOHNSqfTmZgo8ieXzRsQkp6F9ezplIZbXJO1hnAeM/s640/setupSupportRules.bmp" width="640" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhr3onbM4lnCxcSVgF5uplsHnfT2Z_9BemaMFohs6UVUz671MJ1inWj50sQ1NvD68dcXcPNCf5sigxE4AtyLxp264AGPrcdIGytWqPIbl0Zhtwy2lm1vDPC_CD2qWqujHlGiyAwgxMBXVQ/s1600/SQLServerEdition.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="480" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhr3onbM4lnCxcSVgF5uplsHnfT2Z_9BemaMFohs6UVUz671MJ1inWj50sQ1NvD68dcXcPNCf5sigxE4AtyLxp264AGPrcdIGytWqPIbl0Zhtwy2lm1vDPC_CD2qWqujHlGiyAwgxMBXVQ/s640/SQLServerEdition.JPG" width="640" /></a></div>
<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh6I7otNcEQGAiPSmGo4RsmiBCB9TyXVpz2CNDcxMl6P6f_KXsbj4aja1kuBKowKflC4RrxwNtvL8ePC3SQhWFr-EgkaRL9i5JFkLtIgmu_WGbZbTIAARw-mCDQLt1Tnes4jQ7cQmWMM1g/s1600/licenseTerms.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="480" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh6I7otNcEQGAiPSmGo4RsmiBCB9TyXVpz2CNDcxMl6P6f_KXsbj4aja1kuBKowKflC4RrxwNtvL8ePC3SQhWFr-EgkaRL9i5JFkLtIgmu_WGbZbTIAARw-mCDQLt1Tnes4jQ7cQmWMM1g/s640/licenseTerms.JPG" width="640" /></a></div>
<br />
<br />
Product updated are optional here... if you need to install these updates you need to connect to internet.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh40UlmujIogHuCBASnGwiTVLjSTyyd-FcEd98NvNVXw5Cyae5R-KVDNQEjUcLo0rc796Y-ef6t2JDsJ-lEA0yY02i78WNtwCSSK4P7_clEefCxzl9iyuz3V3VLYa2rL9gsDL_TiZ4PVdA/s1600/Productupdates.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="480" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh40UlmujIogHuCBASnGwiTVLjSTyyd-FcEd98NvNVXw5Cyae5R-KVDNQEjUcLo0rc796Y-ef6t2JDsJ-lEA0yY02i78WNtwCSSK4P7_clEefCxzl9iyuz3V3VLYa2rL9gsDL_TiZ4PVdA/s640/Productupdates.JPG" width="640" /></a></div>
<br />
As mention in the above screen-shot that "Setup Updates" will be installed immediately.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiRngQXmY6dEcCh_sJJYm_MsmbLVqdssNOKyS0MLUFbKrR6FM-wouAiuWFIo5kj5G88G3nHnaqPvtQ-fWKjXxGsHM5KMy0rOLr-52APHIGEVrCVNmnIUL3fQwbQyuj0IpCQMkY6voEWopg/s1600/InstallSetupFile.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="480" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiRngQXmY6dEcCh_sJJYm_MsmbLVqdssNOKyS0MLUFbKrR6FM-wouAiuWFIo5kj5G88G3nHnaqPvtQ-fWKjXxGsHM5KMy0rOLr-52APHIGEVrCVNmnIUL3fQwbQyuj0IpCQMkY6voEWopg/s640/InstallSetupFile.JPG" width="640" /></a></div>
<br />
<br />
Setup Support Rules again check the set related requirement before starting the installation.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgpn-TI_b4OdI3WkHvr9mHXJLRH4w3TZhq7U1XvFJW9-XYHFN6urmRy55MCYD40VjNFzjecir23iBtJgc2e_XI9iq1gwzVfoYn1aw-GwjvXYI75VGGkuW-eUjlwNnNV0EOytiE6EkRfhfI/s1600/SetupSupportRules2.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="480" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgpn-TI_b4OdI3WkHvr9mHXJLRH4w3TZhq7U1XvFJW9-XYHFN6urmRy55MCYD40VjNFzjecir23iBtJgc2e_XI9iq1gwzVfoYn1aw-GwjvXYI75VGGkuW-eUjlwNnNV0EOytiE6EkRfhfI/s640/SetupSupportRules2.JPG" width="640" /></a></div>
<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjAz9RdlDmwi4gg8mU2pFjwEWSrI9vg_7cjpEV-ax6xW879Pf16Q5yXNd_DnZivWQkDwrVpBUje37Wuo0XrxUF1w5trrEk5NTBF1VGz9kGhyphenhypheni9VY9w_RS7Zgq5wNZ1M6c6GFSON4xKjFcg/s1600/SetupRole.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="480" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjAz9RdlDmwi4gg8mU2pFjwEWSrI9vg_7cjpEV-ax6xW879Pf16Q5yXNd_DnZivWQkDwrVpBUje37Wuo0XrxUF1w5trrEk5NTBF1VGz9kGhyphenhypheni9VY9w_RS7Zgq5wNZ1M6c6GFSON4xKjFcg/s640/SetupRole.JPG" width="640" /></a></div>
<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjQwOSCiNkstcrOJeFVVS-U-_c9xj_xr0XuCrgTon56nQ69ZpRqSgg_xyvLcE83CsGe0Ma8DW174wS0X-tYOzW1VAWrhDwoPzeslVN36DNyIcc_0nw5FybQUdg3ARNhx-LR0NBh8su5cvM/s1600/FeatureSelection.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="380" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjQwOSCiNkstcrOJeFVVS-U-_c9xj_xr0XuCrgTon56nQ69ZpRqSgg_xyvLcE83CsGe0Ma8DW174wS0X-tYOzW1VAWrhDwoPzeslVN36DNyIcc_0nw5FybQUdg3ARNhx-LR0NBh8su5cvM/s640/FeatureSelection.JPG" width="640" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiH5cYi3h4lrajE9Zk1sju_VgfnNvIjvfRsJKl9-rcKMBZqc1Dd5wZNqCPk-AaaHfBERssuCgApx_RC7dJFPVYW0Dh6adP-pbddq0FeooVDWJ0iKWcqK4X4aP4hmxq8LDHKRYIVD9Hn62k/s1600/InstallationRules.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="380" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiH5cYi3h4lrajE9Zk1sju_VgfnNvIjvfRsJKl9-rcKMBZqc1Dd5wZNqCPk-AaaHfBERssuCgApx_RC7dJFPVYW0Dh6adP-pbddq0FeooVDWJ0iKWcqK4X4aP4hmxq8LDHKRYIVD9Hn62k/s640/InstallationRules.JPG" width="640" /></a></div>
<br />
Here I'm creating the another instance (named) of SQL Server Denali with the "Denali" name.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiCk6ERzp_WczcW9lcAPw7DH6W_YaolHHMkNe-bWE0bQZOQaHIrxHnOox7sX1g2jYCCtn0KTrg9146R1U7ZDhUDP3-5tcSTPH4y3o6Qg6K7ESPAwHSa5FdnQTJk75B8iWYHgG9kkbCY24c/s1600/InstanceConfig.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="380" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiCk6ERzp_WczcW9lcAPw7DH6W_YaolHHMkNe-bWE0bQZOQaHIrxHnOox7sX1g2jYCCtn0KTrg9146R1U7ZDhUDP3-5tcSTPH4y3o6Qg6K7ESPAwHSa5FdnQTJk75B8iWYHgG9kkbCY24c/s640/InstanceConfig.JPG" width="640" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEikhm2gs7qAVahQyYNa317yYjqg8Jwn4az7KYp3ET3s2A_gcruPovyHQXlKqXO0UQ-JqAT_v60V-x00GyqC38-Uqp6fQSU-U1Si7JNstaUOuW9w8aJBmIk01FwuuAd5tUFS90zsWo-01X4/s1600/DiskSpace.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="380" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEikhm2gs7qAVahQyYNa317yYjqg8Jwn4az7KYp3ET3s2A_gcruPovyHQXlKqXO0UQ-JqAT_v60V-x00GyqC38-Uqp6fQSU-U1Si7JNstaUOuW9w8aJBmIk01FwuuAd5tUFS90zsWo-01X4/s640/DiskSpace.JPG" width="640" /></a></div>
<br />
Installation Wizard uses the some default services accounts for running the services. you can change these services to your name.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgPRbbu2T-mxh6mYLbl7G6QQqzJt9rWTpbOmgQ8IiQLjrwGn5O5jVrybaMalkRqKUHt0Q2UvdQvFFmElUk9v9XJSpqgwPq2UFwCsixVKeOBuUt_wcuSL6oLGG5saKYiksMbdH4nkmbO1ms/s1600/ServiceAccounts.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="332" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgPRbbu2T-mxh6mYLbl7G6QQqzJt9rWTpbOmgQ8IiQLjrwGn5O5jVrybaMalkRqKUHt0Q2UvdQvFFmElUk9v9XJSpqgwPq2UFwCsixVKeOBuUt_wcuSL6oLGG5saKYiksMbdH4nkmbO1ms/s640/ServiceAccounts.JPG" width="640" /></a></div>
<br />
You can change these service Accounts to your account.. I did it (This is optional)<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiZVvBmhAUgBSQax-VodFwlLmMQDenf_cDJZGNeV8RTjzwIn2EiGekMbLEfJGrG-vObsTpj1KRQD9ADHQE6oYs-yMI8Y8GGCNv9RaX4hyCDiY9LSGrx4tpveAHGT-rnODyg9lEXvV0LUrg/s1600/ServiceAccounts2.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="380" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiZVvBmhAUgBSQax-VodFwlLmMQDenf_cDJZGNeV8RTjzwIn2EiGekMbLEfJGrG-vObsTpj1KRQD9ADHQE6oYs-yMI8Y8GGCNv9RaX4hyCDiY9LSGrx4tpveAHGT-rnODyg9lEXvV0LUrg/s640/ServiceAccounts2.JPG" width="640" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjGCKEB8XLqQWqGLCya8J2SbbuCIEs66tmKHRhDV4Sp-zslpbJgqs5rw3Tl3hjA036Z24a1GWfLzgAHfBhJvuVUiJMwT5tgpocuLhdp8T5w07KvxG_fS-MCQZIT2v41r0yfzfiByIweqFY/s1600/DB.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="380" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjGCKEB8XLqQWqGLCya8J2SbbuCIEs66tmKHRhDV4Sp-zslpbJgqs5rw3Tl3hjA036Z24a1GWfLzgAHfBhJvuVUiJMwT5tgpocuLhdp8T5w07KvxG_fS-MCQZIT2v41r0yfzfiByIweqFY/s640/DB.JPG" width="640" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh-z8qmipuBdW14Mp3KaX4iJHhopG2FYQxtSs_flkWsWgrXmkVMLUyvAZ9r3GzNVQovtPjGUv_muDUZvuqqvZFlYpiSm3DOSq0GzpxedochaGL-gmmOz1wQ9MVK3abBnVsiQLoNPjl7ooc/s1600/SSAS.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="380" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh-z8qmipuBdW14Mp3KaX4iJHhopG2FYQxtSs_flkWsWgrXmkVMLUyvAZ9r3GzNVQovtPjGUv_muDUZvuqqvZFlYpiSm3DOSq0GzpxedochaGL-gmmOz1wQ9MVK3abBnVsiQLoNPjl7ooc/s640/SSAS.JPG" width="640" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgsl0gw0BEM2d8WsTjfCzKQBwbBDioV-MflOOA7oNcnSD-PFTD2rQv3cx9U7Mh9HLnC5WpqPqtyXUEHNpqeAOaf2qjtWDb_inukVV6010nCMi_5P5TtHOed4WBVOGYJuC3Y_RGetwoKbi8/s1600/SSRS.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="380" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgsl0gw0BEM2d8WsTjfCzKQBwbBDioV-MflOOA7oNcnSD-PFTD2rQv3cx9U7Mh9HLnC5WpqPqtyXUEHNpqeAOaf2qjtWDb_inukVV6010nCMi_5P5TtHOed4WBVOGYJuC3Y_RGetwoKbi8/s640/SSRS.JPG" width="640" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhffbhzT9b2WyiPPX4wTEYBQNYQTZwfVjpepJOZ-y5ghWCpvwPqAmqOCxCWrD4xZvRHD7GTheq7yB4Cyj5gujn1uTUZJO4r4SdPkczXI6zOLCCRuNdumRCMcXxnAr_u39B4XV5P6dAufEg/s1600/DRC.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="380" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhffbhzT9b2WyiPPX4wTEYBQNYQTZwfVjpepJOZ-y5ghWCpvwPqAmqOCxCWrD4xZvRHD7GTheq7yB4Cyj5gujn1uTUZJO4r4SdPkczXI6zOLCCRuNdumRCMcXxnAr_u39B4XV5P6dAufEg/s640/DRC.JPG" width="640" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi6Z9x7-jOAA73UBqfmyfaOg1hwSbQ1IGVNUKalGnHBDVxFkwuhIC0OArF8bq0ic1lJawdJsH42rsh9Xl0UYVC6TdQYM8UMiyy7b5QvIdj7uMTJ38QHyaC-EspDhDtx4kIXHvPdl-fPdL8/s1600/DRC2.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="380" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi6Z9x7-jOAA73UBqfmyfaOg1hwSbQ1IGVNUKalGnHBDVxFkwuhIC0OArF8bq0ic1lJawdJsH42rsh9Xl0UYVC6TdQYM8UMiyy7b5QvIdj7uMTJ38QHyaC-EspDhDtx4kIXHvPdl-fPdL8/s640/DRC2.JPG" width="640" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjJwZbQng0lFXBzlV2uP_vqubqTDo1X-smUEJuls9wENtjVTQACrFyjypnQD4JLP6Izh3MBq6A4hI444S0XgIfPtieYhNI2k3W6PCgNhdFIx0OQgPR_4K-hfKzDsSuVoSWrolxLi0ZQikQ/s1600/ErrorReporting.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="380" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjJwZbQng0lFXBzlV2uP_vqubqTDo1X-smUEJuls9wENtjVTQACrFyjypnQD4JLP6Izh3MBq6A4hI444S0XgIfPtieYhNI2k3W6PCgNhdFIx0OQgPR_4K-hfKzDsSuVoSWrolxLi0ZQikQ/s640/ErrorReporting.JPG" width="640" /></a></div>
<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhdewI9JOE6iGUti1QKDxGHCzYpGikDd4PfSB4SgKOajkVn7B2rq342B6wmtyjsb17lJR1TrfoxYTDfZZPR5KamWn5n-lWcaZlxnIWUVTIWp-oxEWnwlL3wds5JR-om_7M4VYDkG0UB3P4/s1600/ICR.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="380" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhdewI9JOE6iGUti1QKDxGHCzYpGikDd4PfSB4SgKOajkVn7B2rq342B6wmtyjsb17lJR1TrfoxYTDfZZPR5KamWn5n-lWcaZlxnIWUVTIWp-oxEWnwlL3wds5JR-om_7M4VYDkG0UB3P4/s640/ICR.JPG" width="640" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgCxtEwu37yHBvvEIm3ntf4JcSzsO-WzAFU2wZH8hdnzSFzO4XLYlNJN8caXFB6C789wCot4s44t-py84EGgA3VAIfZwBpX6pQNw0FrAtqrnHzC953jCzWMLGsZCg8pYZglwxddj5gHU8w/s1600/Summary.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="380" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgCxtEwu37yHBvvEIm3ntf4JcSzsO-WzAFU2wZH8hdnzSFzO4XLYlNJN8caXFB6C789wCot4s44t-py84EGgA3VAIfZwBpX6pQNw0FrAtqrnHzC953jCzWMLGsZCg8pYZglwxddj5gHU8w/s640/Summary.JPG" width="640" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg-xhG6F3f33nAP6alrMlyEHdG4A4lmkFpMtttY2SO3OGN_a2f80tWjZt1guuDuXXqqwDn-wmJhagwfJ8zBwtxzQLxvUVFQh4hi57MEP-SKAh80YARFx45_AKYOnn6UGeQ9grlJWKLqT6w/s1600/Complete.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="380" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg-xhG6F3f33nAP6alrMlyEHdG4A4lmkFpMtttY2SO3OGN_a2f80tWjZt1guuDuXXqqwDn-wmJhagwfJ8zBwtxzQLxvUVFQh4hi57MEP-SKAh80YARFx45_AKYOnn6UGeQ9grlJWKLqT6w/s640/Complete.JPG" width="640" /></a></div>
<br />
<br />
And SQL Server Denali is ready to server you.. Let me know if you are facing any problem during the installation.<br />
<br />
<br />
<br />
<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh9dleBVMVrwkWq7O4zFq5uvouhOhhWBttUazfQrMbF8gZ2jUDZG7UadDe8u4yYGfVK7bNaDqcwPFvqCK0LuoYbLhbcRYJ6oiyygZ-4x5n4te2dUxwV_MzYEIq449zGl0Lp4q68HeI4DJk/s1600/s.bmp" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh9dleBVMVrwkWq7O4zFq5uvouhOhhWBttUazfQrMbF8gZ2jUDZG7UadDe8u4yYGfVK7bNaDqcwPFvqCK0LuoYbLhbcRYJ6oiyygZ-4x5n4te2dUxwV_MzYEIq449zGl0Lp4q68HeI4DJk/s1600/s.bmp" /></a></div>
<br />
......</div>
Manish Sharmahttp://www.blogger.com/profile/01694083809722708790noreply@blogger.com0tag:blogger.com,1999:blog-7713884638213403215.post-25150408334282306052011-03-25T07:06:00.000-07:002011-08-11T01:59:25.881-07:00Handling extra/missing columns in Delimited Flat file<div dir="ltr" style="text-align: left;" trbidi="on">Delimited Flat files (CSV) are one of the important non-relational source for SSIS ETL but if we get robust file all the time then we can use inbuild flat file source in SSIS. Surprisingly we are not that much lucky and get the data in which columns are missing (or we can say extra columns) like as follow...<br />
<a href="http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/8267a610-c694-41d8-af73-2140bd5627f6" style="background-color: #f3f3f3;">Problem Reference(Click here) </a><br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEga7GiPtR51za86q78jy6hTqLwMKQpwdOTNYNHJuVtCjmtm5uZ0H5AaARxx4teb93tloIBTfitU9JV6SUIwGfjVUVGJyAoNZFNW5JrQxsUaWigbdrzR9YszgKQ35xMJHSrl4q2tbLkfHcY/s1600/IN_OUT.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="228" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEga7GiPtR51za86q78jy6hTqLwMKQpwdOTNYNHJuVtCjmtm5uZ0H5AaARxx4teb93tloIBTfitU9JV6SUIwGfjVUVGJyAoNZFNW5JrQxsUaWigbdrzR9YszgKQ35xMJHSrl4q2tbLkfHcY/s640/IN_OUT.JPG" width="640" /></a></div><br />
<br />
So handling such a file is very problematic for a SSIS ETL developer.Here I'm giving you some work around for such cases<br />
<br />
<span class="Apple-style-span" style="color: #333333; font-family: Arial, Helvetica; font-size: 13px;"></span><br />
<ol><li><span class="Apple-style-span" style="background-color: #f3f3f3;"><a href="http://ssisdfs.codeplex.com/">SSIS Delimited File Source available at Codeplex (Click here)</a></span></li>
<li><span class="Apple-style-span" style="background-color: #f3f3f3;"><a href="http://beyondrelational.com/blogs/sudeep/archive/2011/03/09/ssis-script-component-add-missing-column-or-ignore-extra-columns.aspx">Script Component--- Check Sudeep's Blog (Click here)</a></span></li>
<li>Flat file source which reads all data into one column and then splits them based on index of the comma in a derived column.</li>
</ol><div>Here I'm giving you the demonstration to handle such file with the help of derived column.We have to write expression (sometime complex) to get the proper data in column format. In the above example I am getting all the data as String and after splitting we are saving it as string only (If you want to change the data type then you can use Data Conversion task). If number of columns are more then this approach is not recommended, In that case you should go with the first 2 approach.</div><div><br />
</div><div><b>Step1</b>: Inside the Data Flow Task (DFT), Select a Flat File Source and Derived column.<br />
<br />
</div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhwfYrdFlVIXfJjLCf6tza6Q8mmYe_ToQEAcyG90g4xN27FejUELj1sPiLt65mFjE9AUnFKAjX-4S9L2Xlyg-Z13ANO-FHv2HQfesxzzWVD_plH7A-4OvZu6FCvpj3xOBhNNikjzy_ZFjA/s1600/DFT.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhwfYrdFlVIXfJjLCf6tza6Q8mmYe_ToQEAcyG90g4xN27FejUELj1sPiLt65mFjE9AUnFKAjX-4S9L2Xlyg-Z13ANO-FHv2HQfesxzzWVD_plH7A-4OvZu6FCvpj3xOBhNNikjzy_ZFjA/s1600/DFT.JPG" /></a></div><div class="separator" style="clear: both; text-align: center;"><br />
</div><div class="separator" style="clear: both; text-align: left;"><b>Step2</b>: Configure the FlatFile Source in such a way that you will get all input data in single column.</div><div class="separator" style="clear: both; text-align: left;"><br />
</div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiUGWqoSsy6_55jELv2H45jCo6JuVlBoexVMarlxh3O5jriE4URTn2-SFQO24UUI7CW4LZgpVAcL-K8vZOhMJ43-79w52Ad3EtIwRq4WfXICGCN6m0NSt0t1c8sIqSgV8VpNiYACIBDwuQ/s1600/FF_Source.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="320" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiUGWqoSsy6_55jELv2H45jCo6JuVlBoexVMarlxh3O5jriE4URTn2-SFQO24UUI7CW4LZgpVAcL-K8vZOhMJ43-79w52Ad3EtIwRq4WfXICGCN6m0NSt0t1c8sIqSgV8VpNiYACIBDwuQ/s320/FF_Source.JPG" width="302" /></a></div><div class="separator" style="clear: both; text-align: left;"><br />
</div><div class="separator" style="clear: both; text-align: left;"><b>Note</b>: We are keeping the Column name as "C" in the example.</div><div class="separator" style="clear: both; text-align: left;"><br />
</div><div class="separator" style="clear: both; text-align: left;"><b>Step3</b>: Double click the derived column and use the following logic.</div><div class="separator" style="clear: both; text-align: left;"><br />
</div><div class="separator" style="clear: both; text-align: left;"></div><ul style="text-align: left;"><li>For <b>separating the column "C1" </b>from the column "C", we need to fetch the data from position 1 to first occurrence of delimiter " <b>, </b>" so finding the first delimiter we have to use FINDSTRING SSIS function (Returns the location of the specified occurrence of a string within a character expression).</li>
</ul><ul style="text-align: left;"><li><b style="background-color: yellow;">SUBSTRING(C,1,FINDSTRING(C,",",1) - 1)</b></li>
</ul><ul style="text-align: left;"><li>Here we have to do "-1" because we have to exclude delimiter from the column value.</li>
</ul><ul style="text-align: left;"><li>For <b>separating the column "C2" </b>from the column "C", we have to fetch the data after the first delimiter to 2nd delimiter but before that we have to check whether we have 2nd delimiter in place</li>
</ul><br />
<div><ul style="text-align: left;"><li><span class="Apple-style-span" style="background-color: yellow;"><b>FINDSTRING(C,",",2) != 0 ? SUBSTRING(C,FINDSTRING(C,",",1) + 1,FINDSTRING(C,",",2) - FINDSTRING(C,",",1) - 1) : SUBSTRING(C,FINDSTRING(C,",",1) + 1,LEN(C) - FINDSTRING(C,",",2))</b> </span></li>
</ul><ul style="text-align: left;"><li>For <b>separating the column "C3" </b>from the column "C"</li>
<li><span class="Apple-style-span" style="background-color: yellow;">FINDSTRING(C,",",2) == 0 ? "NULL" : (FINDSTRING(C,",",3) != 0 ? SUBSTRING(C,FINDSTRING(C,",",2) + 1,FINDSTRING(C,",",3) - FINDSTRING(C,",",2) - 1) : "NULL")</span></li>
</ul><div><br />
</div><ul style="text-align: left;"><li>For <b>separating the Last column "C4" </b>from the column "C",we have to fetch the data after the third delimiter to end of the row. and for calculating the last position of the row, we have to use <a href="http://msdn.microsoft.com/en-us/library/ms141797.aspx">LEN SSIS</a> function</li>
<li><span class="Apple-style-span" style="background-color: yellow;">FINDSTRING(C,",",3) == 0 ? "NULL" : SUBSTRING(C,FINDSTRING(C,",",3) + 1,LEN(C) - FINDSTRING(C,",",2))</span></li>
</ul><div><b>Step4</b>: And result would be as expected..<br />
<br />
</div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjRFnXYCqm60eMwBpBrkvveQqTyrJvxnOl6S9BRsgetHdtwNFniNOFUjuYX7PzZBoj0KZjPAl-6XZHvLTPBbIYiE1kg6n7APAEVBmLnRW9Oq7NYulBpbWhJp-semSK9Zg9bjBPKGTusLmM/s1600/Result.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="228" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjRFnXYCqm60eMwBpBrkvveQqTyrJvxnOl6S9BRsgetHdtwNFniNOFUjuYX7PzZBoj0KZjPAl-6XZHvLTPBbIYiE1kg6n7APAEVBmLnRW9Oq7NYulBpbWhJp-semSK9Zg9bjBPKGTusLmM/s640/Result.JPG" width="640" /></a></div><div></div></div><div><br />
</div><div><b>Note: Let me know your valuable comments to make this post more robust and helpful</b>..<br />
<br />
</div><div></div></div>Manish Sharmahttp://www.blogger.com/profile/01694083809722708790noreply@blogger.com2tag:blogger.com,1999:blog-7713884638213403215.post-63698042859218188252010-08-19T01:55:00.000-07:002011-08-11T01:59:44.026-07:00Write data from Multiple tables to single flat file<div dir="ltr" style="text-align: left;" trbidi="on"><span class="Apple-style-span" style="font-family: Arial; font-size: small;"><span class="Apple-style-span" style="font-size: 13px;"></span></span><br />
<span class="Apple-style-span" style="font-family: Arial; font-size: small;"></span><br />
<span class="Apple-style-span" style="font-family: Arial; font-size: small;"></span><br />
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><span class="Apple-style-span" style="font-family: Arial; font-size: small;"><span style="border-bottom-color: windowtext; border-bottom-style: none; border-bottom-width: 1pt; border-left-color: windowtext; border-left-style: none; border-left-width: 1pt; border-right-color: windowtext; border-right-style: none; border-right-width: 1pt; border-top-color: windowtext; border-top-style: none; border-top-width: 1pt; color: #333333; font-family: 'Segoe UI', sans-serif; font-size: 11.5pt; padding-bottom: 0in; padding-left: 0in; padding-right: 0in; padding-top: 0in;">It's simple if you are transferring your single table data to a single file means its one-to-one mapping. Many threads I observed at MSDN forum where user want different set of data (Header, Detail, Trailer) in a single file or data from the multiple tables to single flat file. It's not difficult in SSIS but it is tricky.<o:p></o:p></span></span></div><span class="Apple-style-span" style="font-family: Arial; font-size: small;"><u1:p></u1:p> <br />
</span><br />
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><span class="Apple-style-span" style="font-family: Arial; font-size: small;"><br />
</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><span class="Apple-style-span" style="font-family: Arial; font-size: small;"><span style="border-bottom-color: windowtext; border-bottom-style: none; border-bottom-width: 1pt; border-left-color: windowtext; border-left-style: none; border-left-width: 1pt; border-right-color: windowtext; border-right-style: none; border-right-width: 1pt; border-top-color: windowtext; border-top-style: none; border-top-width: 1pt; color: #333333; font-family: 'Segoe UI', sans-serif; padding-bottom: 0in; padding-left: 0in; padding-right: 0in; padding-top: 0in;"><span class="Apple-style-span" style="font-size: x-large;">Solution:</span><o:p></o:p></span><u1:p></u1:p></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><span class="Apple-style-span" style="font-family: Arial; font-size: small;"><span style="border-bottom-color: windowtext; border-bottom-style: none; border-bottom-width: 1pt; border-left-color: windowtext; border-left-style: none; border-left-width: 1pt; border-right-color: windowtext; border-right-style: none; border-right-width: 1pt; border-top-color: windowtext; border-top-style: none; border-top-width: 1pt; color: #333333; font-family: 'Segoe UI', sans-serif; font-size: 11.5pt; padding-bottom: 0in; padding-left: 0in; padding-right: 0in; padding-top: 0in;">USE 3 dataflow tasks one for each table with precedence constraints to have them execute in the correct order.<o:p></o:p></span></span></div><span class="Apple-style-span" style="font-family: Arial; font-size: small;"><u1:p></u1:p> <br />
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><span style="border-bottom-color: windowtext; border-bottom-style: none; border-bottom-width: 1pt; border-left-color: windowtext; border-left-style: none; border-left-width: 1pt; border-right-color: windowtext; border-right-style: none; border-right-width: 1pt; border-top-color: windowtext; border-top-style: none; border-top-width: 1pt; color: #333333; font-family: 'Segoe UI', sans-serif; font-size: 11.5pt; padding-bottom: 0in; padding-left: 0in; padding-right: 0in; padding-top: 0in;"><br />
</span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><span style="border-bottom-color: windowtext; border-bottom-style: none; border-bottom-width: 1pt; border-left-color: windowtext; border-left-style: none; border-left-width: 1pt; border-right-color: windowtext; border-right-style: none; border-right-width: 1pt; border-top-color: windowtext; border-top-style: none; border-top-width: 1pt; color: #333333; font-family: 'Segoe UI', sans-serif; font-size: 11.5pt; padding-bottom: 0in; padding-left: 0in; padding-right: 0in; padding-top: 0in;"><b>Important:</b> </span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><span style="border-bottom-color: windowtext; border-bottom-style: none; border-bottom-width: 1pt; border-left-color: windowtext; border-left-style: none; border-left-width: 1pt; border-right-color: windowtext; border-right-style: none; border-right-width: 1pt; border-top-color: windowtext; border-top-style: none; border-top-width: 1pt; color: #333333; font-family: 'Segoe UI', sans-serif; font-size: 11.5pt; padding-bottom: 0in; padding-left: 0in; padding-right: 0in; padding-top: 0in;">Each destination would use its own connection manager but they would all point to the same flat file. The only thing you would need to do is in the flat file destination property grid (or advanced editor) make sure you set the overwrite flag to false for the second and third dataflows so that the file will be appended to. <o:p></o:p></span></div></span><br />
<span class="Apple-style-span" style="font-family: Arial; font-size: small;"></span><br />
<span class="Apple-style-span" style="font-family: Arial; font-size: small;"></span><br />
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><span class="Apple-style-span" style="font-family: Arial; font-size: small;"><span style="border-bottom-color: windowtext; border-bottom-style: none; border-bottom-width: 1pt; border-left-color: windowtext; border-left-style: none; border-left-width: 1pt; border-right-color: windowtext; border-right-style: none; border-right-width: 1pt; border-top-color: windowtext; border-top-style: none; border-top-width: 1pt; color: #333333; font-size: 12pt; padding-bottom: 0in; padding-left: 0in; padding-right: 0in; padding-top: 0in;"></span></span></div><div style="text-align: justify;"><span class="Apple-style-span" style="font-family: Arial; font-size: small;"><br />
</span></div><div style="text-align: justify;"><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><span class="Apple-style-span" style="font-family: Arial; font-size: small;"><span style="border-bottom-color: windowtext; border-bottom-style: none; border-bottom-width: 1pt; border-left-color: windowtext; border-left-style: none; border-left-width: 1pt; border-right-color: windowtext; border-right-style: none; border-right-width: 1pt; border-top-color: windowtext; border-top-style: none; border-top-width: 1pt; color: #333333; font-family: 'Segoe UI', sans-serif; font-size: 11.5pt; padding-bottom: 0in; padding-left: 0in; padding-right: 0in; padding-top: 0in;">DFT 1:<br />
OLEDB Src (header table) --> FlatFile Dest<o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><span class="Apple-style-span" style="font-family: Arial; font-size: small;"><span style="border-bottom-color: windowtext; border-bottom-style: none; border-bottom-width: 1pt; border-left-color: windowtext; border-left-style: none; border-left-width: 1pt; border-right-color: windowtext; border-right-style: none; border-right-width: 1pt; border-top-color: windowtext; border-top-style: none; border-top-width: 1pt; color: #333333; font-family: 'Segoe UI', sans-serif; font-size: 11.5pt; padding-bottom: 0in; padding-left: 0in; padding-right: 0in; padding-top: 0in;">(using flat file conn mgr 1 (filename = OutputFile.csv), <b>overwrite set to true</b>)<o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><span class="Apple-style-span" style="font-family: Arial; font-size: small;"><span style="border-bottom-color: windowtext; border-bottom-style: none; border-bottom-width: 1pt; border-left-color: windowtext; border-left-style: none; border-left-width: 1pt; border-right-color: windowtext; border-right-style: none; border-right-width: 1pt; border-top-color: windowtext; border-top-style: none; border-top-width: 1pt; color: #333333; font-family: 'Segoe UI', sans-serif; font-size: 11.5pt; padding-bottom: 0in; padding-left: 0in; padding-right: 0in; padding-top: 0in;"><br />
</span></span></div></div><span class="Apple-style-span" style="font-family: Arial; font-size: small;"><br />
<br />
<br />
</span><br />
<div class="MsoNormal" style="line-height: normal; margin-bottom: 0.0001pt; text-align: justify;"><span class="Apple-style-span" style="font-family: Arial; font-size: small;"><span style="border-bottom-color: windowtext; border-bottom-style: none; border-bottom-width: 1pt; border-left-color: windowtext; border-left-style: none; border-left-width: 1pt; border-right-color: windowtext; border-right-style: none; border-right-width: 1pt; border-top-color: windowtext; border-top-style: none; border-top-width: 1pt; color: #333333; font-size: 12pt; padding-bottom: 0in; padding-left: 0in; padding-right: 0in; padding-top: 0in;"></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><span class="Apple-style-span" style="font-family: Arial; font-size: small;"><span style="border-bottom-color: windowtext; border-bottom-style: none; border-bottom-width: 1pt; border-left-color: windowtext; border-left-style: none; border-left-width: 1pt; border-right-color: windowtext; border-right-style: none; border-right-width: 1pt; border-top-color: windowtext; border-top-style: none; border-top-width: 1pt; color: #333333; font-family: 'Segoe UI', sans-serif; font-size: 11.5pt; padding-bottom: 0in; padding-left: 0in; padding-right: 0in; padding-top: 0in;">DFT 2:<br />
OLEDB Src (details table) --> FlatFile Dest</span><span style="color: black; font-size: 13.5pt;"><o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><span class="Apple-style-span" style="font-family: Arial; font-size: small;"><span style="border-bottom-color: windowtext; border-bottom-style: none; border-bottom-width: 1pt; border-left-color: windowtext; border-left-style: none; border-left-width: 1pt; border-right-color: windowtext; border-right-style: none; border-right-width: 1pt; border-top-color: windowtext; border-top-style: none; border-top-width: 1pt; color: #333333; font-family: 'Segoe UI', sans-serif; font-size: 11.5pt; padding-bottom: 0in; padding-left: 0in; padding-right: 0in; padding-top: 0in;">(Using flat file conn mgr 2 (filename = OutputFile.csv), <b>overwrite set to false</b>)<o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><span class="Apple-style-span" style="font-family: Arial; font-size: small;"><span style="border-bottom-color: windowtext; border-bottom-style: none; border-bottom-width: 1pt; border-left-color: windowtext; border-left-style: none; border-left-width: 1pt; border-right-color: windowtext; border-right-style: none; border-right-width: 1pt; border-top-color: windowtext; border-top-style: none; border-top-width: 1pt; color: #333333; font-family: 'Segoe UI', sans-serif; font-size: 11.5pt; padding-bottom: 0in; padding-left: 0in; padding-right: 0in; padding-top: 0in;"><br />
</span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><span class="Apple-style-span" style="font-family: Arial; font-size: small;"><span style="border-bottom-color: windowtext; border-bottom-style: none; border-bottom-width: 1pt; border-left-color: windowtext; border-left-style: none; border-left-width: 1pt; border-right-color: windowtext; border-right-style: none; border-right-width: 1pt; border-top-color: windowtext; border-top-style: none; border-top-width: 1pt; color: #333333; font-family: 'Segoe UI', sans-serif; font-size: 11.5pt; padding-bottom: 0in; padding-left: 0in; padding-right: 0in; padding-top: 0in;"></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><span class="Apple-style-span" style="font-family: Arial; font-size: small;"><span style="border-bottom-color: windowtext; border-bottom-style: none; border-bottom-width: 1pt; border-left-color: windowtext; border-left-style: none; border-left-width: 1pt; border-right-color: windowtext; border-right-style: none; border-right-width: 1pt; border-top-color: windowtext; border-top-style: none; border-top-width: 1pt; color: #333333; font-family: 'Segoe UI', sans-serif; font-size: 11.5pt; padding-bottom: 0in; padding-left: 0in; padding-right: 0in; padding-top: 0in;">DFT 3:<br />
OLEDB Src (trailer table) --> FlatFile Dest</span><span style="color: black; font-size: 13.5pt;"><o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><span class="Apple-style-span" style="font-family: Arial; font-size: small;"><span style="border-bottom-color: windowtext; border-bottom-style: none; border-bottom-width: 1pt; border-left-color: windowtext; border-left-style: none; border-left-width: 1pt; border-right-color: windowtext; border-right-style: none; border-right-width: 1pt; border-top-color: windowtext; border-top-style: none; border-top-width: 1pt; color: #333333; font-family: 'Segoe UI', sans-serif; font-size: 11.5pt; padding-bottom: 0in; padding-left: 0in; padding-right: 0in; padding-top: 0in;">(Using flat file conn mgr 3 (filename = OutputFile.csv), <b>overwrite set to false</b>)</span><span style="color: black; font-size: 13.5pt;"><o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><span class="Apple-style-span" style="font-family: Arial; font-size: small;"><span style="border-bottom-color: windowtext; border-bottom-style: none; border-bottom-width: 1pt; border-left-color: windowtext; border-left-style: none; border-left-width: 1pt; border-right-color: windowtext; border-right-style: none; border-right-width: 1pt; border-top-color: windowtext; border-top-style: none; border-top-width: 1pt; color: #333333; font-family: 'Segoe UI', sans-serif; font-size: 11.5pt; padding-bottom: 0in; padding-left: 0in; padding-right: 0in; padding-top: 0in;"><br />
</span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><span class="Apple-style-span" style="font-family: Arial; font-size: small;"><span style="border-bottom-color: windowtext; border-bottom-style: none; border-bottom-width: 1pt; border-left-color: windowtext; border-left-style: none; border-left-width: 1pt; border-right-color: windowtext; border-right-style: none; border-right-width: 1pt; border-top-color: windowtext; border-top-style: none; border-top-width: 1pt; color: #333333; font-family: 'Segoe UI', sans-serif; font-size: 11.5pt; padding-bottom: 0in; padding-left: 0in; padding-right: 0in; padding-top: 0in;">----------------------------------------------------------------------------------------------------</span></span><br />
<span class="Apple-style-span" style="font-family: Arial; font-size: small;"><span style="border-bottom-color: windowtext; border-bottom-style: none; border-bottom-width: 1pt; border-left-color: windowtext; border-left-style: none; border-left-width: 1pt; border-right-color: windowtext; border-right-style: none; border-right-width: 1pt; border-top-color: windowtext; border-top-style: none; border-top-width: 1pt; color: #333333; font-family: 'Segoe UI', sans-serif; font-size: 11.5pt; padding-bottom: 0in; padding-left: 0in; padding-right: 0in; padding-top: 0in;">Get the sample package..</span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><span class="Apple-style-span" style="font-family: Arial; font-size: small;"><span style="border-bottom-color: windowtext; border-bottom-style: none; border-bottom-width: 1pt; border-left-color: windowtext; border-left-style: none; border-left-width: 1pt; border-right-color: windowtext; border-right-style: none; border-right-width: 1pt; border-top-color: windowtext; border-top-style: none; border-top-width: 1pt; color: #333333; font-family: 'Segoe UI', sans-serif; font-size: 11.5pt; padding-bottom: 0in; padding-left: 0in; padding-right: 0in; padding-top: 0in;"><a href="http://cid-5b21421d068ff953.office.live.com/browse.aspx/.Public">Package.dtsx</a></span></span></div><div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in;"><span class="Apple-style-span" style="font-family: Arial; font-size: small;"><span style="border-bottom-color: windowtext; border-bottom-style: none; border-bottom-width: 1pt; border-left-color: windowtext; border-left-style: none; border-left-width: 1pt; border-right-color: windowtext; border-right-style: none; border-right-width: 1pt; border-top-color: windowtext; border-top-style: none; border-top-width: 1pt; color: #333333; font-family: 'Segoe UI', sans-serif; font-size: 11.5pt; padding-bottom: 0in; padding-left: 0in; padding-right: 0in; padding-top: 0in;">For step-by-step explanation, please check the following document.</span></span></div><div class="MsoNormal" style="margin-bottom: 0.0001pt;"><span class="Apple-style-span" style="font-family: Arial; font-size: small;">PPT: <a href="http://cid-5b21421d068ff953.office.live.com/browse.aspx/.Public">OneOutputFile.ppsx</a></span></div><div class="MsoNormal" style="margin-bottom: 0.0001pt;"><span class="Apple-style-span" style="font-family: Arial; font-size: small;">OR</span></div><div class="MsoNormal" style="margin-bottom: 0.0001pt;"><span class="Apple-style-span" style="font-family: Arial; font-size: small;">Doc: <a href="http://cid-5b21421d068ff953.office.live.com/browse.aspx/.Public">OneOutputFile.doc</a></span></div><span class="Apple-style-span" style="font-family: Arial; font-size: small;"><span class="Apple-style-span" style="color: #333333; font-family: 'Segoe UI', sans-serif; font-size: 15px;">----------------------------------------------------------------------------------------------------</span> </span><br />
<span class="Apple-style-span" style="font-family: Arial; font-size: small;"><span class="Apple-style-span" style="color: #333333; font-family: 'Segoe UI', sans-serif; font-size: 15px;"><br />
</span></span><br />
<span class="Apple-style-span" style="font-family: Arial; font-size: small;"><span class="Apple-style-span" style="color: #333333; font-family: 'Segoe UI', sans-serif; font-size: 15px;">There are lots of other ways to do this. but in the above method, you don't need any specific programming skill. Do let me know if you have any doubt..</span></span><br />
<span class="Apple-style-span" style="font-family: Arial; font-size: small;"><span class="Apple-style-span" style="color: #333333; font-family: 'Segoe UI', sans-serif; font-size: 15px;"><br />
</span></span></div>Manish Sharmahttp://www.blogger.com/profile/01694083809722708790noreply@blogger.com1tag:blogger.com,1999:blog-7713884638213403215.post-51551953315003203912010-06-30T07:21:00.000-07:002011-08-11T02:00:00.920-07:00Convert Number to IP address<div dir="ltr" style="text-align: left;" trbidi="on"><span class="Apple-style-span" style="font-size: x-large;"><span class="Apple-style-span" style="color: #073763;">Convert Number to IP address.</span></span><br />
<br />
Use the following query to convert the number into an IP Address..<br />
<br />
<br />
<span class="Apple-style-span" style="font-size: small;">DECLARE @IP_NUM AS INTEGER</span><br />
<span class="Apple-style-span" style="font-size: small;">SET @IP_NUM=1135863234 </span><br />
<br />
<span class="Apple-style-span" style="font-size: small;">SELECT</span><span class="Apple-tab-span" style="white-space: pre;"><span class="Apple-style-span" style="font-size: small;"> </span></span><span class="Apple-style-span" style="font-size: small;">CAST(@IP_NUM/16777216 AS VARCHAR(3)) +'.'+ </span><br />
<span class="Apple-tab-span" style="white-space: pre;"><span class="Apple-style-span" style="font-size: small;"> </span></span><span class="Apple-style-span" style="font-size: small;">CAST((@IP_NUM%16777216)/65536 AS VARCHAR(3)) +'.'+ </span><br />
<span class="Apple-tab-span" style="white-space: pre;"><span class="Apple-style-span" style="font-size: small;"> </span></span><span class="Apple-style-span" style="font-size: small;">CAST( ((@IP_NUM%16777216)%65536)/256 AS VARCHAR(3)) +'.'+ </span><br />
<span class="Apple-tab-span" style="white-space: pre;"><span class="Apple-style-span" style="font-size: small;"> </span></span><span class="Apple-style-span" style="font-size: small;">CAST(((@IP_NUM%16777216)%65536)%256 AS VARCHAR(3))</span><br />
<br />
<span class="Apple-style-span" style="color: #073763;"><b>Result:</b></span><br />
<span class="Apple-style-span" style="color: #073763;"><b>67.179.229.194</b></span><br />
<br />
Please give some suggestion to make it better.</div>Manish Sharmahttp://www.blogger.com/profile/01694083809722708790noreply@blogger.com0tag:blogger.com,1999:blog-7713884638213403215.post-69169847760119823692010-05-05T22:50:00.000-07:002012-02-10T03:31:17.185-08:00Load CUBE data to SQL table using Script task<div dir="ltr" style="text-align: left;" trbidi="on">Recently I faced an issue while working with SSAS 2008 cube. I was using a data flow task and inside that I have OLEDB source (<strong>The Analysis Services 9.0 OLE DB Provider (msolap90.dll)</strong>) for fetching data using MDX query. I was running this package in BIDS, its working fine. But as I was trying to run it with DTEXEC command, it’s throwing following error:<br />
<br />
<br />
• ERROR message:<br />
<br />
<em>SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. <strong>Error code: 0x80040E05</strong>. An OLE DB record is available. Source: "<strong>Microsoft OLE DB Provider for Analysis Services 2008</strong>." Hresult: 0x00000001 Description: "Error Code = 0x80040E05, External Code = 0x00000000:.". </em><br />
<em><br />
</em><br />
<em>Component "OLE DB Source" (2297) failed the pre-execute phase and returned error code 0xC0202009. </em><br />
<br />
<strong>Environment: </strong><br />
SSIS package on SQL Server 2005. BIDS 2005<br />
SSAS cube on SQL server 2008.<br />
<br />
<br />
<span style="font-size: large;">Solution: </span><br />
I have tried many possible solutions but no success. Finally I wrote a <strong>script task code</strong> which is connecting to CUBE and dumping the cube data in flat file with comma delimited format. Later on I used this flat file to laod data in SQL table.<br />
<br />
I would like to share this code, may this helps other. <br />
<br />
<strong>Important: Please add the "Microsoft.AnalysisServices.AdomdClient.dll" dll to your reference.</strong><br />
<span class="Apple-style-span" style="color: #333333; font-family: 'Segoe UI', 'Lucida Grande', Verdana, Arial, Helvetica, sans-serif; font-size: 13px; line-height: 16px;"><br />
</span><br />
<span class="Apple-style-span" style="color: #333333; font-family: 'Segoe UI', 'Lucida Grande', Verdana, Arial, Helvetica, sans-serif; line-height: 16px;"><span class="Apple-style-span" style="font-size: small;">Microsoft.AnalysisServices.AdomdClient.dll is available under the link below,</span><a href="http://www.microsoft.com/downloads/en/details.aspx?FamilyId=228DE03F-3B5A-428A-923F-58A033D316E1&displaylang=en" style="border-bottom-style: none; border-color: initial; border-color: initial; border-left-style: none; border-right-style: none; border-style: initial; border-top-style: none; border-width: initial; color: #0066dd; cursor: pointer; font-family: inherit; font-weight: inherit; list-style-type: none; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; outline-color: initial; outline-style: initial; outline-width: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-decoration: none;"><span class="Apple-style-span" style="font-size: small;">http://www.microsoft.com/downloads/en/details.aspx?FamilyId=228DE03F-3B5A-428A-923F-58A033D316E1&displaylang=en</span></a><span class="Apple-style-span" style="font-size: small;"><br />
</span><span style="border-bottom-width: 0px; border-color: initial; border-left-width: 0px; border-right-width: 0px; border-style: initial; border-top-width: 0px; font-family: inherit; font-weight: inherit; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; outline-color: initial; outline-style: initial; outline-width: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;"><span class="Apple-style-span" style="font-size: small;">Download</span><strong style="border-bottom-width: 0px; border-color: initial; border-left-width: 0px; border-right-width: 0px; border-style: initial; border-top-width: 0px; font-family: inherit; font-weight: bold; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; outline-color: initial; outline-style: initial; outline-width: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px;"><span class="Apple-style-span" style="font-size: small;"> "Microsoft ADOMD.NET</span></strong><span class="Apple-style-span" style="font-size: small;"> </span></span><span class="Apple-style-span" style="font-size: small;">" from the link above and after running msi file you will get<br />
Microsoft.AnalysisServices.AdomdClient.dll under folder.<br />
C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.AnalysisServices.</span></span><br />
<br />
**********************************************************<br />
Public Sub Main()<br />
Dim <strong>serverName</strong> As String = "Manish"<br />
Dim <strong>databaseName</strong> As String = "CRM"<br />
Dim databaseID As String = databaseName<br />
Dim cn As String = "Provider=MSOLAP;Data Source=" & serverName & ";Initial Catalog=" & databaseName<br />
Dim connex As New <strong>AdomdConnection</strong>(cn)<br />
<br />
connex.Open()<br />
<br />
Dim commandText As String<br />
<br />
commandText = "SELECT [Country].[Country Code].MEMBERS ON 0, [Time].[Gregorian Calendar].[Year] ON 1 FROM [CUBE2008];"<br />
<br />
Dim cmd As AdomdCommand = New AdomdCommand(commandText, connex)<br />
Dim dr As AdomdDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)<br />
<br />
' output the rows in the DataReader<br />
Using writer As StreamWriter = New StreamWriter("c:\myfile.txt")<br />
While dr.Read()<br />
For i As Integer = 0 To dr.FieldCount - 1<br />
If (dr(i) Is Nothing) Then<br />
writer.Write(" , ")<br />
Continue For<br />
Else<br />
writer.Write(dr(i).ToString() + " , ")<br />
End If<br />
Next<br />
writer.WriteLine()<br />
End While<br />
End Using<br />
dr.Close()<br />
<br />
Dts.TaskResult = Dts.Results.Success<br />
<br />
End Sub<br />
<br />
***************************************************************<br />
<br />
Let me know your valuable comment to make this post better.</div>Manish Sharmahttp://www.blogger.com/profile/01694083809722708790noreply@blogger.com1tag:blogger.com,1999:blog-7713884638213403215.post-21563499619375340072010-04-17T14:31:00.000-07:002011-08-11T02:00:45.946-07:00Processing Options for SSAS Objects<div dir="ltr" style="text-align: left;" trbidi="on"><span style="font-size: large;"><a href="http://technet.microsoft.com/en-us/library/ms174774.aspx"><span style="font-family: 'Courier New', Courier, monospace;"><span style="background-color: #0b5394; color: white;">PROCESSING OPTION SSAS2008</span></span></a></span><br />
<div></div><br />
<div></div><span style="color: #741b47;"><strong><u>Process Default: (Applicable for **All objects)</u></strong></span><br />
Performs the minimum number of tasks required to fully initialize the object. The server converts this option to one of the other options based on the object state.<br />
<br />
<div></div><strong><u><span style="color: #4c1130;">Process Full</span><span style="color: #741b47;"> (Applicable for All objects)</span></u></strong><br />
Drops the object stores and rebuilds the object. Metadata changes, such as adding a new attribute to a dimension, require Process Full.<br />
<br />
<div></div><strong><u><span style="color: #741b47;">Process Update (Only for Dimensions)</span> </u></strong><br />
Applies member inserts, deletes, and updates without invalidating the affected cubes. <br />
<br />
<div></div><br />
<div></div><strong><u><span style="color: #741b47;">Process Add (Only for Dimension & Partition)</span></u></strong><br />
Adds only new data. <br />
<br />
<div></div><strong><u><span style="color: #741b47;">Process Data (Dimension, cube, measure group, partition)</span></u></strong><br />
<br />
<div></div>Loads the object with data without building indexes and aggregations. <br />
<br />
<div></div><span style="color: #741b47;"><strong><u>Process Index(Dimension, cube, measure group, partition)</u></strong></span> <br />
Retains data and builds only indexes and aggregations. <br />
<br />
<div></div><strong><u><span style="color: #741b47;">Unprocess (All Objects) </span></u></strong><br />
Deletes the object data or the data in the containing objects. <br />
<br />
<div></div><span style="color: #741b47;"><strong><u>Process Structure ( Only CUBE)</u></strong></span><br />
Deletes the partition data and applies Process Default to the cube dimensions.<br />
<br />
------------------------------------------------------------<br />
<br />
**SSAS objects list<br />
<ul><li>Database</li>
<li>Dimension</li>
<li>Cube</li>
<li>Measure group</li>
<li>Partition</li>
<li>Mining structure</li>
<li>Mining model</li>
</ul></div>Manish Sharmahttp://www.blogger.com/profile/01694083809722708790noreply@blogger.com1tag:blogger.com,1999:blog-7713884638213403215.post-87717102434813130882010-03-12T08:52:00.000-08:002010-03-12T21:26:32.588-08:00Script task recompilation ErrorA package that has a script task runs fine in visual studio, but the package fails as a job with following error on DEV Server. <br />
<br />
"<em>Script could not be recompiled or run: Attempted to read or write protected memory. This is often an indication that other memory is corrupt</em>"<br />
<br />
OR<br />
<br />
“<em>Error 1 Validation error. Script Task : The task is configured to pre-compile the script, but binary code is not found. Please visit the IDE in Script Task Editor by clicking Design Script button to cause binary code to be generated</em>.”<br />
<br />
<div><span class="Apple-style-span" style="font-size: x-large;">Answer:</span></div><br />
<ul><li>Check the pre-complied property of script task.</li>
<li>Change it to TRUE</li>
<li>Add the following code in your script, before imports statement.</li>
</ul><br />
<div> Option Strict Off</div> Option Explicit On<br />
Imports System<br />
<div> .</div><div> . </div><br />
<div></div><br />
<div><br />
</div>Manish Sharmahttp://www.blogger.com/profile/01694083809722708790noreply@blogger.com0tag:blogger.com,1999:blog-7713884638213403215.post-31369346687807811072010-03-09T08:29:00.000-08:002010-03-09T08:32:37.225-08:00Checking for the file?<span class="Apple-style-span" style="font-family: Arial; font-size: small;"><span class="Apple-style-span" style="font-size: 13px;"></span></span><br />
<span class="Apple-style-span" style="font-family: Arial; font-size: small;"><span class="Apple-style-span" style="font-size: 13px;"><div style="margin-bottom: .0001pt; margin: 0cm;"><span style="font-size: 13.5pt;"><span class="Apple-style-span" style="background-color: white;"><span class="Apple-style-span" style="color: purple;">Question:</span></span></span></div><div class="MsoNormal" style="line-height: 12.0pt;"><span class="apple-style-span"><span style="color: #333333; font-family: 'Segoe UI'; font-size: 10pt;">Many times I observed on MSDN SSIS forum that developers are facing problem for checking the file in a particular folder. There are 2 types of checking:<o:p></o:p></span></span></div><div style="margin-bottom: .0001pt; margin: 0cm;"><br />
</div><div class="MsoNormal" style="line-height: 12.0pt;"><span class="apple-style-span"><span style="color: #333333; font-family: 'Segoe UI'; font-size: 10pt;">1> Is any file exist in the folder?<o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: 12.0pt;"><span class="apple-style-span"><span style="color: #333333; font-family: 'Segoe UI'; font-size: 10pt;">OR<o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: 12.0pt;"><span class="apple-style-span"><span style="color: #333333; font-family: 'Segoe UI'; font-size: 10pt;">Count number of files in a directory through SSIS<o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: 12.0pt;"><br />
</div><div class="MsoNormal" style="line-height: 12.0pt;"><span class="apple-style-span"><span style="color: #333333; font-family: 'Segoe UI'; font-size: 10pt;">2> A particular file is exist [for example c:\Manish\demo.txt]<o:p></o:p></span></span></div><div style="margin-bottom: .0001pt; margin: 0cm;"><br />
</div><div style="margin-bottom: .0001pt; margin: 0cm;"><span class="Apple-style-span" style="color: purple; font-size: 18px;">Answer:</span></div><div style="margin-bottom: .0001pt; margin: 0cm;"><br />
</div><div class="MsoNormal" style="line-height: 12.0pt;"><span class="apple-style-span"><span style="color: #333333; font-family: 'Segoe UI'; font-size: 10pt;"><span class="Apple-style-span" style="background-color: white;"><b>1> Is any file exist in the folder? OR Count number of files in a directory through SSIS?</b></span><o:p></o:p></span></span></div><div class="MsoNormal"><br />
</div><div class="MsoNormal"><br />
</div><div class="MsoNormal" style="line-height: 12.0pt;"><span class="apple-style-span"><span style="color: #333333; font-family: 'Segoe UI'; font-size: 10pt;">For checking the file existence in a directory, there are 2 ways<o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: 12.0pt;"><br />
</div><div class="MsoNormal" style="line-height: 12.0pt;"><span class="apple-style-span"><b><span style="font-family: 'Segoe UI'; font-size: 10pt;"><span class="Apple-style-span" style="color: #20124d;"><span class="Apple-style-span" style="background-color: #6aa84f;">1: File Property task</span></span><o:p></o:p></span></b></span></div><div class="MsoNormal" style="line-height: 12.0pt;"><span class="apple-style-span"><b style="mso-bidi-font-weight: normal;"><span style="color: #333333; font-family: 'Segoe UI'; font-size: 10pt;"><a href="http://filepropertiestask.codeplex.com/"><span style="border-bottom-color: windowtext; border-bottom-style: none; border-bottom-width: 1pt; border-left-color: windowtext; border-left-style: none; border-left-width: 1pt; border-right-color: windowtext; border-right-style: none; border-right-width: 1pt; border-top-color: windowtext; border-top-style: none; border-top-width: 1pt; color: #0066dd; font-family: inherit; font-weight: normal; padding-bottom: 0cm; padding-left: 0cm; padding-right: 0cm; padding-top: 0cm;">File Properties Task</span></a></span></b></span><span class="apple-converted-space"><span style="color: #333333; font-family: 'Segoe UI'; font-size: 10pt;"> </span></span><span class="apple-style-span"><span style="color: #333333; font-family: 'Segoe UI'; font-size: 10pt;">. It will detect file existence.</span> Check the link.<o:p></o:p></span></div><div class="MsoNormal" style="line-height: 12.0pt;"><br />
</div><div class="MsoNormal" style="line-height: 12.0pt;"><span class="apple-style-span"><b><span style="font-family: 'Segoe UI'; font-size: 10pt;"><span class="Apple-style-span" style="color: #20124d;"><span class="Apple-style-span" style="background-color: #6aa84f;">2: Script task</span></span><o:p></o:p></span></b></span></div><div class="MsoNormal" style="line-height: 12.0pt;"><br />
</div><div class="MsoNormal" style="line-height: 12.0pt;"><span class="apple-style-span"><span style="color: #333333; font-family: 'Segoe UI'; font-size: 10pt;"><b>Step1:</b> Take a ForEachLoop container. <o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: 12.0pt;"><span class="apple-style-span"><span style="color: #333333; font-family: 'Segoe UI'; font-size: 10pt;"><b>Step2:</b> Map to the particular directory.</span></span><span style="color: #333333; font-family: 'Segoe UI'; font-size: 10pt;"><br />
<br />
<b> Step3:</b> <span class="apple-style-span">Inside that you have to use a script task and there you can write following code to count the no of files.</span><br />
<br />
<b> Step4:</b> D<span class="apple-style-span">efine the following variable @ package level</span><span class="apple-converted-space"> </span><span class="apple-style-span"><o:p></o:p></span></span></div><pre style="background: white; border-color: initial; border-color: initial; border-style: initial; border-width: initial; font-style: inherit; font-weight: inherit; line-height: 12.0pt; outline-color: initial; outline-style: initial; outline-width: 0px; word-wrap: break-word;"><span style="color: black; font-family: inherit;"><o:p> </o:p></span></pre><pre style="background: white; line-height: 12.0pt;"><span style="color: black; font-family: inherit;">User::</span><span style="border-color: initial; border-style: initial; font-style: inherit; font-weight: inherit; outline-color: initial; outline-style: initial; outline-width: 0px;"><span style="border-bottom-color: windowtext; border-bottom-style: none; border-bottom-width: 1pt; border-left-color: windowtext; border-left-style: none; border-left-width: 1pt; border-right-color: windowtext; border-right-style: none; border-right-width: 1pt; border-top-color: windowtext; border-top-style: none; border-top-width: 1pt; color: blue; font-family: inherit; padding-bottom: 0cm; padding-left: 0cm; padding-right: 0cm; padding-top: 0cm;">Count</span></span><span style="color: #333333; font-family: 'Segoe UI';">
<o:p></o:p></span></pre><pre style="background: white; line-height: 12.0pt;"><span style="color: #333333; font-family: 'Segoe UI';"><o:p> </o:p></span></pre><pre style="background: white; line-height: 12.0pt;"><span class="apple-style-span"><span style="color: #333333; font-family: 'Segoe UI';">---------------------------</span></span><span style="color: #333333; font-family: 'Segoe UI';">
| <span class="apple-style-span">ForEachLoop |</span>
<span class="apple-style-span">---------------------------</span>
<span class="apple-style-span">|</span>
<span class="apple-style-span">| --------------</span>
<span class="apple-style-span">| |Script Task |</span>
<span class="apple-style-span">| --------------</span>
<span class="apple-style-span">|</span>
---------------------------</span></pre><pre style="background: white; line-height: 12.0pt;"><span style="color: #333333; font-family: 'Segoe UI';">
<b>Step5:</b></span><span style="color: black; font-family: inherit;"><o:p></o:p></span></pre><pre style="background: white; border-color: initial; border-color: initial; border-style: initial; border-width: initial; font-style: inherit; font-weight: inherit; line-height: 12.0pt; outline-color: initial; outline-style: initial; outline-width: 0px; word-wrap: break-word;"><span style="border-bottom-color: windowtext; border-bottom-style: none; border-bottom-width: 1pt; border-left-color: windowtext; border-left-style: none; border-left-width: 1pt; border-right-color: windowtext; border-right-style: none; border-right-width: 1pt; border-top-color: windowtext; border-top-style: none; border-top-width: 1pt; color: blue; font-family: inherit; padding-bottom: 0cm; padding-left: 0cm; padding-right: 0cm; padding-top: 0cm;">
</span></pre><pre style="background: white; border-color: initial; border-color: initial; border-style: initial; border-width: initial; font-style: inherit; font-weight: inherit; line-height: 12.0pt; outline-color: initial; outline-style: initial; outline-width: 0px; word-wrap: break-word;"><span style="border-bottom-color: windowtext; border-bottom-style: none; border-bottom-width: 1pt; border-left-color: windowtext; border-left-style: none; border-left-width: 1pt; border-right-color: windowtext; border-right-style: none; border-right-width: 1pt; border-top-color: windowtext; border-top-style: none; border-top-width: 1pt; color: blue; font-family: inherit; padding-bottom: 0cm; padding-left: 0cm; padding-right: 0cm; padding-top: 0cm;">Public</span><span style="color: black; font-family: inherit;"> </span><span style="border-color: initial; border-style: initial; font-style: inherit; font-weight: inherit; outline-color: initial; outline-style: initial; outline-width: 0px;"><span style="border-bottom-color: windowtext; border-bottom-style: none; border-bottom-width: 1pt; border-left-color: windowtext; border-left-style: none; border-left-width: 1pt; border-right-color: windowtext; border-right-style: none; border-right-width: 1pt; border-top-color: windowtext; border-top-style: none; border-top-width: 1pt; color: blue; font-family: inherit; padding-bottom: 0cm; padding-left: 0cm; padding-right: 0cm; padding-top: 0cm;">Sub</span></span><span style="color: black; font-family: inherit;"> <st1:place w:st="on">Main</st1:place>()<o:p></o:p></span></pre><pre style="background: white; line-height: 12.0pt;"><span style="color: black; font-family: inherit;"> Dts.Variables(</span><span style="border-color: initial; border-style: initial; font-style: inherit; font-weight: inherit; outline-color: initial; outline-style: initial; outline-width: 0px;"><span style="border-bottom-color: windowtext; border-bottom-style: none; border-bottom-width: 1pt; border-left-color: windowtext; border-left-style: none; border-left-width: 1pt; border-right-color: windowtext; border-right-style: none; border-right-width: 1pt; border-top-color: windowtext; border-top-style: none; border-top-width: 1pt; color: #a31515; font-family: inherit; padding-bottom: 0cm; padding-left: 0cm; padding-right: 0cm; padding-top: 0cm;">"User::Count"</span></span><span style="color: black; font-family: inherit;">).Value = Dts.Variables(</span><span style="border-color: initial; border-style: initial; font-style: inherit; font-weight: inherit; outline-color: initial; outline-style: initial; outline-width: 0px;"><span style="border-bottom-color: windowtext; border-bottom-style: none; border-bottom-width: 1pt; border-left-color: windowtext; border-left-style: none; border-left-width: 1pt; border-right-color: windowtext; border-right-style: none; border-right-width: 1pt; border-top-color: windowtext; border-top-style: none; border-top-width: 1pt; color: #a31515; font-family: inherit; padding-bottom: 0cm; padding-left: 0cm; padding-right: 0cm; padding-top: 0cm;">"User::Count"</span></span><span style="color: black; font-family: inherit;">).Value + 1<o:p></o:p></span></pre><pre style="background: white; line-height: 12.0pt;"><span style="color: black; font-family: inherit;"> MsgBox(Dts.Variables(</span><span style="border-color: initial; border-style: initial; font-style: inherit; font-weight: inherit; outline-color: initial; outline-style: initial; outline-width: 0px;"><span style="border-bottom-color: windowtext; border-bottom-style: none; border-bottom-width: 1pt; border-left-color: windowtext; border-left-style: none; border-left-width: 1pt; border-right-color: windowtext; border-right-style: none; border-right-width: 1pt; border-top-color: windowtext; border-top-style: none; border-top-width: 1pt; color: #a31515; font-family: inherit; padding-bottom: 0cm; padding-left: 0cm; padding-right: 0cm; padding-top: 0cm;">"User::Count"</span></span><span style="color: black; font-family: inherit;">).Value.ToString())<o:p></o:p></span></pre><pre style="background: white; line-height: 12.0pt;"><span style="color: black; font-family: inherit;"> Dts.TaskResult = ScriptResults.Success<o:p></o:p></span></pre><pre style="background: white; line-height: 12.0pt;"><span style="border-bottom-color: windowtext; border-bottom-style: none; border-bottom-width: 1pt; border-left-color: windowtext; border-left-style: none; border-left-width: 1pt; border-right-color: windowtext; border-right-style: none; border-right-width: 1pt; border-top-color: windowtext; border-top-style: none; border-top-width: 1pt; color: blue; font-family: inherit; padding-bottom: 0cm; padding-left: 0cm; padding-right: 0cm; padding-top: 0cm;">End</span><span style="color: black; font-family: inherit;"> </span><span style="border-color: initial; border-style: initial; font-style: inherit; font-weight: inherit; outline-color: initial; outline-style: initial; outline-width: 0px;"><span style="border-bottom-color: windowtext; border-bottom-style: none; border-bottom-width: 1pt; border-left-color: windowtext; border-left-style: none; border-left-width: 1pt; border-right-color: windowtext; border-right-style: none; border-right-width: 1pt; border-top-color: windowtext; border-top-style: none; border-top-width: 1pt; color: blue; font-family: inherit; padding-bottom: 0cm; padding-left: 0cm; padding-right: 0cm; padding-top: 0cm;">Sub</span></span><span style="color: black; font-family: inherit;"><o:p></o:p></span></pre><div class="MsoNormal"><span style="color: #333333; font-family: 'Segoe UI'; font-size: 10pt;"><span class="apple-style-span"> </span><span class="apple-converted-space"> </span><br />
<span class="apple-style-span">Now you can access the count variable</span><o:p></o:p></span></div><div class="MsoNormal"><br />
</div><div class="MsoNormal"><span class="apple-style-span"><span style="color: #333333; font-family: 'Segoe UI'; font-size: 10pt;"><o:p><br />
</o:p></span></span></div><div class="MsoNormal"><span class="apple-style-span"><span style="color: #333333; font-family: 'Segoe UI'; font-size: 10pt;"><o:p><br />
</o:p></span></span></div><div class="MsoNormal"><span class="apple-style-span"><span style="color: #333333; font-family: 'Segoe UI'; font-size: 10pt;"><o:p><span class="Apple-style-span" style="line-height: 16px;"><b>2> A particular file is exist [for example c:\Manish\demo.txt]</b></span></o:p></span></span></div><div class="MsoNormal"><span class="apple-style-span"><span style="color: #333333; font-family: 'Segoe UI'; font-size: 10pt;"><o:p><br />
</o:p></span></span></div><div class="MsoNormal"><span class="apple-style-span"><span style="color: #333333; font-family: 'Segoe UI'; font-size: 10pt;"><o:p></o:p></span></span></div><div class="MsoNormal" style="line-height: 12.0pt;"><span class="apple-style-span"><span style="color: #333333; font-family: 'Segoe UI'; font-size: 10pt;">You can use a script task to check if the file exists or not. Suppose I have demo.txt in C:\Manish then I will use a script task with code as:<o:p></o:p></span></span></div><pre style="background: white; line-height: 12.0pt;"><span style="border-bottom-color: windowtext; border-bottom-style: none; border-bottom-width: 1pt; border-left-color: windowtext; border-left-style: none; border-left-width: 1pt; border-right-color: windowtext; border-right-style: none; border-right-width: 1pt; border-top-color: windowtext; border-top-style: none; border-top-width: 1pt; color: blue; font-family: inherit; padding-bottom: 0cm; padding-left: 0cm; padding-right: 0cm; padding-top: 0cm;"><o:p> </o:p></span></pre><pre style="background: white; line-height: 12.0pt;"><span style="border-bottom-color: windowtext; border-bottom-style: none; border-bottom-width: 1pt; border-left-color: windowtext; border-left-style: none; border-left-width: 1pt; border-right-color: windowtext; border-right-style: none; border-right-width: 1pt; border-top-color: windowtext; border-top-style: none; border-top-width: 1pt; color: blue; font-family: inherit; padding-bottom: 0cm; padding-left: 0cm; padding-right: 0cm; padding-top: 0cm;">Public</span><span style="color: black; font-family: inherit;"> </span><span style="border-color: initial; border-style: initial; font-style: inherit; font-weight: inherit; outline-color: initial; outline-style: initial; outline-width: 0px;"><span style="border-bottom-color: windowtext; border-bottom-style: none; border-bottom-width: 1pt; border-left-color: windowtext; border-left-style: none; border-left-width: 1pt; border-right-color: windowtext; border-right-style: none; border-right-width: 1pt; border-top-color: windowtext; border-top-style: none; border-top-width: 1pt; color: blue; font-family: inherit; padding-bottom: 0cm; padding-left: 0cm; padding-right: 0cm; padding-top: 0cm;">Class</span></span><span style="color: black; font-family: inherit;"> ScriptMain<o:p></o:p></span></pre><pre style="background: white; line-height: 12.0pt;"><span style="color: black; font-family: inherit;"> </span><span style="border-color: initial; border-style: initial; font-style: inherit; font-weight: inherit; outline-color: initial; outline-style: initial; outline-width: 0px;"><span style="border-bottom-color: windowtext; border-bottom-style: none; border-bottom-width: 1pt; border-left-color: windowtext; border-left-style: none; border-left-width: 1pt; border-right-color: windowtext; border-right-style: none; border-right-width: 1pt; border-top-color: windowtext; border-top-style: none; border-top-width: 1pt; color: blue; font-family: inherit; padding-bottom: 0cm; padding-left: 0cm; padding-right: 0cm; padding-top: 0cm;">Dim</span></span><span style="color: black; font-family: inherit;"> flag </span><span style="border-color: initial; border-style: initial; font-style: inherit; font-weight: inherit; outline-color: initial; outline-style: initial; outline-width: 0px;"><span style="border-bottom-color: windowtext; border-bottom-style: none; border-bottom-width: 1pt; border-left-color: windowtext; border-left-style: none; border-left-width: 1pt; border-right-color: windowtext; border-right-style: none; border-right-width: 1pt; border-top-color: windowtext; border-top-style: none; border-top-width: 1pt; color: blue; font-family: inherit; padding-bottom: 0cm; padding-left: 0cm; padding-right: 0cm; padding-top: 0cm;">As</span></span><span style="color: black; font-family: inherit;"> </span><span style="border-color: initial; border-style: initial; font-style: inherit; font-weight: inherit; outline-color: initial; outline-style: initial; outline-width: 0px;"><span style="border-bottom-color: windowtext; border-bottom-style: none; border-bottom-width: 1pt; border-left-color: windowtext; border-left-style: none; border-left-width: 1pt; border-right-color: windowtext; border-right-style: none; border-right-width: 1pt; border-top-color: windowtext; border-top-style: none; border-top-width: 1pt; color: blue; font-family: inherit; padding-bottom: 0cm; padding-left: 0cm; padding-right: 0cm; padding-top: 0cm;">Boolean</span></span><span style="color: black; font-family: inherit;"><o:p></o:p></span></pre><pre style="background: white; line-height: 12.0pt;"><span style="border-bottom-color: windowtext; border-bottom-style: none; border-bottom-width: 1pt; border-left-color: windowtext; border-left-style: none; border-left-width: 1pt; border-right-color: windowtext; border-right-style: none; border-right-width: 1pt; border-top-color: windowtext; border-top-style: none; border-top-width: 1pt; color: blue; font-family: inherit; padding-bottom: 0cm; padding-left: 0cm; padding-right: 0cm; padding-top: 0cm;"> Public</span><span style="color: black; font-family: inherit;"> </span><span style="border-color: initial; border-style: initial; font-style: inherit; font-weight: inherit; outline-color: initial; outline-style: initial; outline-width: 0px;"><span style="border-bottom-color: windowtext; border-bottom-style: none; border-bottom-width: 1pt; border-left-color: windowtext; border-left-style: none; border-left-width: 1pt; border-right-color: windowtext; border-right-style: none; border-right-width: 1pt; border-top-color: windowtext; border-top-style: none; border-top-width: 1pt; color: blue; font-family: inherit; padding-bottom: 0cm; padding-left: 0cm; padding-right: 0cm; padding-top: 0cm;">Sub</span></span><span style="color: black; font-family: inherit;"> <st1:place w:st="on">Main</st1:place>()<o:p></o:p></span></pre><pre style="background: white; line-height: 12.0pt;"><span style="color: black; font-family: inherit;"> flag = File.Exists(</span><span style="border-color: initial; border-style: initial; font-style: inherit; font-weight: inherit; outline-color: initial; outline-style: initial; outline-width: 0px;"><span style="border-bottom-color: windowtext; border-bottom-style: none; border-bottom-width: 1pt; border-left-color: windowtext; border-left-style: none; border-left-width: 1pt; border-right-color: windowtext; border-right-style: none; border-right-width: 1pt; border-top-color: windowtext; border-top-style: none; border-top-width: 1pt; color: #a31515; font-family: inherit; padding-bottom: 0cm; padding-left: 0cm; padding-right: 0cm; padding-top: 0cm;">"C:\MyFolder\demo.txt"</span></span><span style="color: black; font-family: inherit;">)<o:p></o:p></span></pre><pre style="background: white; line-height: 12.0pt;"><span style="color: black; font-family: inherit;"> </span><span style="border-color: initial; border-style: initial; font-style: inherit; font-weight: inherit; outline-color: initial; outline-style: initial; outline-width: 0px;"><span style="border-bottom-color: windowtext; border-bottom-style: none; border-bottom-width: 1pt; border-left-color: windowtext; border-left-style: none; border-left-width: 1pt; border-right-color: windowtext; border-right-style: none; border-right-width: 1pt; border-top-color: windowtext; border-top-style: none; border-top-width: 1pt; color: blue; font-family: inherit; padding-bottom: 0cm; padding-left: 0cm; padding-right: 0cm; padding-top: 0cm;">If</span></span><span style="color: black; font-family: inherit;"> flag = </span><span style="border-color: initial; border-style: initial; font-style: inherit; font-weight: inherit; outline-color: initial; outline-style: initial; outline-width: 0px;"><span style="border-bottom-color: windowtext; border-bottom-style: none; border-bottom-width: 1pt; border-left-color: windowtext; border-left-style: none; border-left-width: 1pt; border-right-color: windowtext; border-right-style: none; border-right-width: 1pt; border-top-color: windowtext; border-top-style: none; border-top-width: 1pt; color: blue; font-family: inherit; padding-bottom: 0cm; padding-left: 0cm; padding-right: 0cm; padding-top: 0cm;">True</span></span><span style="color: black; font-family: inherit;"> </span><span style="border-color: initial; border-style: initial; font-style: inherit; font-weight: inherit; outline-color: initial; outline-style: initial; outline-width: 0px;"><span style="border-bottom-color: windowtext; border-bottom-style: none; border-bottom-width: 1pt; border-left-color: windowtext; border-left-style: none; border-left-width: 1pt; border-right-color: windowtext; border-right-style: none; border-right-width: 1pt; border-top-color: windowtext; border-top-style: none; border-top-width: 1pt; color: blue; font-family: inherit; padding-bottom: 0cm; padding-left: 0cm; padding-right: 0cm; padding-top: 0cm;">Then</span></span><span style="color: black; font-family: inherit;"><o:p></o:p></span></pre><pre style="background: white; line-height: 12.0pt;"><span style="color: black; font-family: inherit;"> System.Windows.Forms.MessageBox.Show(</span><span style="border-color: initial; border-style: initial; font-style: inherit; font-weight: inherit; outline-color: initial; outline-style: initial; outline-width: 0px;"><span style="border-bottom-color: windowtext; border-bottom-style: none; border-bottom-width: 1pt; border-left-color: windowtext; border-left-style: none; border-left-width: 1pt; border-right-color: windowtext; border-right-style: none; border-right-width: 1pt; border-top-color: windowtext; border-top-style: none; border-top-width: 1pt; color: #a31515; font-family: inherit; padding-bottom: 0cm; padding-left: 0cm; padding-right: 0cm; padding-top: 0cm;">"File Exists"</span></span><span style="color: black; font-family: inherit;">)<o:p></o:p></span></pre><pre style="background: white; line-height: 12.0pt;"><span style="color: black; font-family: inherit;"> </span><span style="border-color: initial; border-style: initial; font-style: inherit; font-weight: inherit; outline-color: initial; outline-style: initial; outline-width: 0px;"><span style="border-bottom-color: windowtext; border-bottom-style: none; border-bottom-width: 1pt; border-left-color: windowtext; border-left-style: none; border-left-width: 1pt; border-right-color: windowtext; border-right-style: none; border-right-width: 1pt; border-top-color: windowtext; border-top-style: none; border-top-width: 1pt; color: blue; font-family: inherit; padding-bottom: 0cm; padding-left: 0cm; padding-right: 0cm; padding-top: 0cm;">End</span></span><span style="color: black; font-family: inherit;"> </span><span style="border-color: initial; border-style: initial; font-style: inherit; font-weight: inherit; outline-color: initial; outline-style: initial; outline-width: 0px;"><span style="border-bottom-color: windowtext; border-bottom-style: none; border-bottom-width: 1pt; border-left-color: windowtext; border-left-style: none; border-left-width: 1pt; border-right-color: windowtext; border-right-style: none; border-right-width: 1pt; border-top-color: windowtext; border-top-style: none; border-top-width: 1pt; color: blue; font-family: inherit; padding-bottom: 0cm; padding-left: 0cm; padding-right: 0cm; padding-top: 0cm;">If</span></span><span style="color: black; font-family: inherit;"><o:p></o:p></span></pre><pre style="background: white; line-height: 12.0pt;"><span style="color: black; font-family: inherit;"> Dts.TaskResult = Dts.Results.Success<o:p></o:p></span></pre><pre style="background: white; line-height: 12.0pt;"><span style="color: black; font-family: inherit;"> </span><span style="border-color: initial; border-style: initial; font-style: inherit; font-weight: inherit; outline-color: initial; outline-style: initial; outline-width: 0px;"><span style="border-bottom-color: windowtext; border-bottom-style: none; border-bottom-width: 1pt; border-left-color: windowtext; border-left-style: none; border-left-width: 1pt; border-right-color: windowtext; border-right-style: none; border-right-width: 1pt; border-top-color: windowtext; border-top-style: none; border-top-width: 1pt; color: blue; font-family: inherit; padding-bottom: 0cm; padding-left: 0cm; padding-right: 0cm; padding-top: 0cm;">End</span></span><span style="color: black; font-family: inherit;"> </span><span style="border-color: initial; border-style: initial; font-style: inherit; font-weight: inherit; outline-color: initial; outline-style: initial; outline-width: 0px;"><span style="border-bottom-color: windowtext; border-bottom-style: none; border-bottom-width: 1pt; border-left-color: windowtext; border-left-style: none; border-left-width: 1pt; border-right-color: windowtext; border-right-style: none; border-right-width: 1pt; border-top-color: windowtext; border-top-style: none; border-top-width: 1pt; color: blue; font-family: inherit; padding-bottom: 0cm; padding-left: 0cm; padding-right: 0cm; padding-top: 0cm;">Sub</span></span><span style="color: black; font-family: inherit;"><o:p></o:p></span></pre><pre style="background: white; line-height: 12.0pt;"><span style="border-bottom-color: windowtext; border-bottom-style: none; border-bottom-width: 1pt; border-left-color: windowtext; border-left-style: none; border-left-width: 1pt; border-right-color: windowtext; border-right-style: none; border-right-width: 1pt; border-top-color: windowtext; border-top-style: none; border-top-width: 1pt; color: blue; font-family: inherit; padding-bottom: 0cm; padding-left: 0cm; padding-right: 0cm; padding-top: 0cm;"><span style="border-color: initial; border-style: initial; font-style: inherit; font-weight: inherit; outline-color: initial; outline-style: initial; outline-width: 0px;">End</span></span><span style="color: black; font-family: inherit;"> </span><span style="border-color: initial; border-style: initial; font-style: inherit; font-weight: inherit; outline-color: initial; outline-style: initial; outline-width: 0px;"><span style="border-bottom-color: windowtext; border-bottom-style: none; border-bottom-width: 1pt; border-left-color: windowtext; border-left-style: none; border-left-width: 1pt; border-right-color: windowtext; border-right-style: none; border-right-width: 1pt; border-top-color: windowtext; border-top-style: none; border-top-width: 1pt; color: blue; font-family: inherit; padding-bottom: 0cm; padding-left: 0cm; padding-right: 0cm; padding-top: 0cm;">Clas</span></span><span style="color: black; font-family: inherit;"><o:p></o:p></span></pre><div class="MsoNormal"><br />
</div></span><br />
<br />
<div class="MsoNormal"><span class="apple-style-span"><span style="color: #333333; font-family: 'Segoe UI'; font-size: 10pt;"><o:p>Hope this will help you. Give your suggestion to improve this post. </o:p></span></span></div><div class="MsoNormal"><br />
</div></span>Manish Sharmahttp://www.blogger.com/profile/01694083809722708790noreply@blogger.com0tag:blogger.com,1999:blog-7713884638213403215.post-28226106617406512792010-02-10T12:44:00.000-08:002010-02-10T13:02:15.224-08:00Divide Single column to multiple columns<span id="goog_1265833740791"></span><span id="goog_1265833740792"></span><span style="color: purple; font-size: large;">Question:</span><br />
<span class="Apple-style-span" style="color: #333333; font-family: 'Segoe UI', 'Lucida Grande', Verdana, Arial, Helvetica, sans-serif; font-size: 13px; line-height: 16px;">I have a column as : act=TUNING; svid=629; VCN=180; TI=949290689; TO=949291183; TT=494; FPID=(586653033-494)<br />
Now i want to take the values of svid,VCN,TI,TO,TT,FPID into seperate columns.<br />
How can i do it in ssis.<br />
The column sizes are not standard all the time.<br />
In one of my table in the DB , i have the token strings like token1 => svid= and token2 => ;<br />
So now how can i use these tokens to pull the data of 629. </span><br />
<br />
Please check the above question @<a href="http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/284e9619-9906-4e1a-be61-5f58e872ee81">msdn forum</a><br />
<br />
<span style="color: purple; font-size: large;">Answer:</span><br />
<br />
Please write the following expression in your derived column (C2 is column name)<br />
(click on the image for larger view)<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgpq-VLDIXqWigXYd-U6zzYKq88xJvHbmAR-N510VBcSGK03jw1x1tCYgDWtlZWSvyZA8hf9GjI5UWUqV_Mc2BqxJXxITCHUxeiiaNK0lz2lp6ewRrrYsKnkOzxqSFPkGLzLwqgaebT68I/s1600-h/11.jpg" imageanchor="1" style="clear: left; cssfloat: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" kt="true" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgpq-VLDIXqWigXYd-U6zzYKq88xJvHbmAR-N510VBcSGK03jw1x1tCYgDWtlZWSvyZA8hf9GjI5UWUqV_Mc2BqxJXxITCHUxeiiaNK0lz2lp6ewRrrYsKnkOzxqSFPkGLzLwqgaebT68I/s320/11.jpg" /></a></div><div class="separator" style="clear: both; text-align: center;"><br />
</div>After this you just need to replace the following with the blank string<br />
act = <br />
svid = <br />
TT=<br />
TI=<br />
TO=<br />
<br />
Do let me know if you need more help on it.<br />
<div class="separator" style="clear: both; text-align: auto;"><br />
</div>Manish Sharmahttp://www.blogger.com/profile/01694083809722708790noreply@blogger.com0tag:blogger.com,1999:blog-7713884638213403215.post-36338786921260466132009-12-09T03:25:00.000-08:002009-12-09T03:47:40.430-08:00Load MS Access data to SQL server<span style="background-color: white;"><span style="color: purple;"><b>Question</b></span></span><span style="color: purple;"><b>:</b> </span><span style="color: #351c75;">How to transfer MS Access data to Sql Server 2005?</span><br />
<br />
<span style="color: purple;"><b>Answer:</b></span><br />
There are 2 ways for achieving this<br />
1) SSMS: Import & Export Wizard<br />
2) BIDS: Create a New SSIS package<br />
<br />
<span style="color: #cc0000;"><b>First approach</b></span> is easy and gives you flexibility to modify the SSIS package(If required)<br />
I hope you can try this your self .<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEihK6pYj2demPx9yKSnqFXSmZmzuV_tBaWFybP47Nj-x6SHpOZxVr51GF_-OHCILcJ-bbhUJwVaEOU7kaoYGPlf_eyX2aFSn0S5DTcX19V7H5ItisxZNuS3CVEppXPn03A2Ngzlc4Y_WLM/s1600-h/s.bmp" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEihK6pYj2demPx9yKSnqFXSmZmzuV_tBaWFybP47Nj-x6SHpOZxVr51GF_-OHCILcJ-bbhUJwVaEOU7kaoYGPlf_eyX2aFSn0S5DTcX19V7H5ItisxZNuS3CVEppXPn03A2Ngzlc4Y_WLM/s200/s.bmp" /></a><br />
<br />
Let me know if you need my help for this.<br />
<br />
<span style="color: #cc0000;"><b>Second approach: </b></span><br />
Open the BIDS and create a new package<br />
<br />
<b>Step1:</b><br />
a) Create a new <b>OLE DB</b> Source connection.<br />
b) Select the provider as "<span style="background-color: orange;">Native OLE DB\ Microsoft Jet 4.0 OLE DB Provider</span>".<br />
c) Select the Database file name. [Check the Image Below]<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh6Jx5BJNKNpXQgxDUcCdnlREpgGV2ILeVs3edQy7Vvh3-WluNf794Rj8JL6QWt9IxVaxPYGqj0uVkpgv5RWrB8dYqeoA2EFksiXZDq_VVq-fJvR7BmXnWE_F8vJ36HDGUGWBusgCma89s/s1600-h/Access.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh6Jx5BJNKNpXQgxDUcCdnlREpgGV2ILeVs3edQy7Vvh3-WluNf794Rj8JL6QWt9IxVaxPYGqj0uVkpgv5RWrB8dYqeoA2EFksiXZDq_VVq-fJvR7BmXnWE_F8vJ36HDGUGWBusgCma89s/s400/Access.jpg" /></a><br />
</div><br />
<b><br />
</b><br />
<span style="font-weight: bold;">Step2:</span><br />
a) Take a DFT.<br />
b) In DFT drag "OLE DB Source", "OLE DB Destination" [or take any destination as per your requirement]<br />
[Check the Image below]<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiPfr1peFNHT_XI8hdqGSkkElH-_zW8nJtqCE9gQXN4QEX4QUs-8QVzOLtD-sGYaf3Ks7kD29sxFeEHTYoYw8UNYJWsFwGmXabSg2C1Y3gyf6jA2tYN9szIw2ET3CfRxbhf1s-b8iDoKHM/s1600-h/DFT1.bmp" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiPfr1peFNHT_XI8hdqGSkkElH-_zW8nJtqCE9gQXN4QEX4QUs-8QVzOLtD-sGYaf3Ks7kD29sxFeEHTYoYw8UNYJWsFwGmXabSg2C1Y3gyf6jA2tYN9szIw2ET3CfRxbhf1s-b8iDoKHM/s320/DFT1.bmp" /></a><br />
</div><b>Step3:</b><br />
Before executing the package please makes sure that destination table should have the correct data type. [if not use data conversion task]<br />
<br />
<br />
I hope this will help you. If you need more assistance or clarification, please leave the comment.Manish Sharmahttp://www.blogger.com/profile/01694083809722708790noreply@blogger.com1tag:blogger.com,1999:blog-7713884638213403215.post-26420120306028214432009-11-23T04:15:00.000-08:002009-11-29T03:23:56.354-08:00What is Factless Fact table?<span style="font-family: Arial; font-size: small;"><span style="font-size: 13px;"></span></span><br />
<span style="font-family: Arial; font-size: small;"><span style="font-size: 13px;"></span></span><br />
<span style="font-family: Arial; font-size: small;"><span style="font-size: 13px;"><div style="margin-bottom: .0001pt; margin: 0cm;"><span style="color: #333333; font-family: Verdana; font-size: 10pt;">Today, I faced an interview for MSBI Developer. The guy asked me many questions and most of them i answered perfectly. The Guy asked me a question on factless fact table. I like to share it with you, if you are not already familiar with it.<o:p></o:p></span><br />
</div><div style="margin-bottom: .0001pt; margin: 0cm;"><br />
</div><div style="margin-bottom: .0001pt; margin: 0cm;"><b style="mso-bidi-font-weight: normal;"><span style="color: #993366; font-family: Verdana; font-size: 10pt;">Question:</span></b><span style="color: #333333; font-family: Verdana; font-size: 10pt;"> What is FACTLESS Fact table?</span><span style="color: black; font-size: 13.5pt;"><o:p></o:p></span><br />
</div><div style="margin-bottom: .0001pt; margin: 0cm;"><br />
</div><div style="margin-bottom: .0001pt; margin: 0cm;"><b style="mso-bidi-font-weight: normal;"><span style="color: #993366; font-family: Verdana; font-size: 10pt;">Answer:</span></b><b style="mso-bidi-font-weight: normal;"><span style="color: #993366; font-size: 13.5pt;"><o:p></o:p></span></b><br />
</div><div style="margin-bottom: .0001pt; margin: 0cm;"><span style="color: #333333; font-family: Verdana; font-size: 10pt;">The “Factless Fact Table” is a table which is similar to Fact Table except for having any measure; I mean that this table just has the links to the dimensions. These tables enable you to track events; indeed they are for recording events.</span><span style="color: black; font-size: 13.5pt;"><o:p></o:p></span><br />
<span style="color: #333333; font-family: Verdana;"><br />
</span><br />
<span style="color: #333333; font-family: Verdana;"><span class="Apple-style-span" style="color: black; font-family: Verdana, sans-serif; font-size: 12px; line-height: 16px;">Factless fact tables are used for tracking a process or collecting stats. They are called so because, the fact table does not have aggregatable numeric values or information. They are mere key values with reference to the dimensions from which the stats can be collected</span></span><br />
</div><div style="margin-bottom: .0001pt; margin: 0cm;"><span style="color: #333333; font-family: Verdana;"><br />
</span><br />
</div><div style="margin-bottom: .0001pt; margin: 0cm;"><span style="color: #333333; font-family: Verdana;"><br />
</span><br />
</div></span></span>Manish Sharmahttp://www.blogger.com/profile/01694083809722708790noreply@blogger.com1tag:blogger.com,1999:blog-7713884638213403215.post-42764386537044478372009-10-06T10:53:00.000-07:002009-10-06T11:25:38.967-07:00Delete bunch of dynamic files<span style="font-size:130%;color:#cc0000;">Question:</span> <span style="color:#339999;">How to delete all files from a directory?<br /></span><br /><br /><span style="font-size:130%;color:#cc0000;">Answer:</span> <span style="color:#339999;">Please follow the following steps:</span><br /><br /><span style="color:#330099;">Step1:</span> <span style="color:#339999;">Add a For each loop container. & Inside it add a File system task</span>.<br /><br /><p align="center"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhsgwaciqJFkX6f2GR6Mo0zQ0QbGbikwqmSpkcokfMlrj-lmOuXb8Ln4p7T-v_JAHfZrORnMqUQqqSFbNiKwt3Z2Z0_v96u4N-iNUhPqN49jO__dR-cECjTsTWIpC5b6oCcc8UxN0fhpvI/s1600-h/package.bmp"><img style="WIDTH: 208px; HEIGHT: 266px; CURSOR: hand" id="BLOGGER_PHOTO_ID_5389550212329828354" border="0" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhsgwaciqJFkX6f2GR6Mo0zQ0QbGbikwqmSpkcokfMlrj-lmOuXb8Ln4p7T-v_JAHfZrORnMqUQqqSFbNiKwt3Z2Z0_v96u4N-iNUhPqN49jO__dR-cECjTsTWIpC5b6oCcc8UxN0fhpvI/s320/package.bmp" /></a></p><span style="color:#330099;">Step2:</span> <span style="color:#339999;">Add 2 variables (Scope: Package level)<br /></span><ol><li><span style="color:#339999;">Var_FilePath</span></li><li><span style="color:#339999;">Var_InputFolder<br /></span><br /><p align="left"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjZQcAPgfn53TyvL2636-BCCd6GGXHBToee4HWq7riSJ0_lMqnsi1cO48hc5qqUPltKp7ebQ0piYUDVhiFqCz_LTKizFpAu1c-tCUAL-zx67_7iVfD2RtSUSwu0BgVQe8CD31YUiEgkJEg/s1600-h/variables.bmp"><img style="WIDTH: 320px; HEIGHT: 82px; CURSOR: hand" id="BLOGGER_PHOTO_ID_5389549504200034530" border="0" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjZQcAPgfn53TyvL2636-BCCd6GGXHBToee4HWq7riSJ0_lMqnsi1cO48hc5qqUPltKp7ebQ0piYUDVhiFqCz_LTKizFpAu1c-tCUAL-zx67_7iVfD2RtSUSwu0BgVQe8CD31YUiEgkJEg/s320/variables.bmp" /></a></p></li></ol><p align="left"><span style="color:#330099;">Step3;</span> <span style="color:#339999;">Double click on Foreach loop container and in expression map the "Directory" property to Var-InputFolder.</span> (<span style="color:#ff0000;">Click on the image for larger view</span>)</p><p align="left"><span style="color:#ff0000;">Please select the "Reterive file name" property to fully qualified.<br /></span></p><p align="center"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiEjBEyR90rx8n0EnenIs4ZcP-ElyoHSxqXzqfIXmOa78JXTmaaP3axk4687Dfma8BRZ75cqpgfvf6gAuywAqr9XFY2fedXu891emaUqNbQT2YojhBWmkgh4EIPcNtXPIIo7LI_h1zQHAA/s1600-h/FELC.bmp"><img style="WIDTH: 320px; HEIGHT: 180px; CURSOR: hand" id="BLOGGER_PHOTO_ID_5389549498492239282" border="0" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiEjBEyR90rx8n0EnenIs4ZcP-ElyoHSxqXzqfIXmOa78JXTmaaP3axk4687Dfma8BRZ75cqpgfvf6gAuywAqr9XFY2fedXu891emaUqNbQT2YojhBWmkgh4EIPcNtXPIIo7LI_h1zQHAA/s320/FELC.bmp" /></a></p><p align="left"><br /><span style="color:#330099;">Step4;</span> <span style="color:#339999;">Map the Var_FilePath in variable mapping tab.</span></p><p align="center"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgWbBYCAFJ7ONGlQEDNHI8s4Ehsp4ktCiQUmr89I0tXXU7WRQdiplHvcAh5eMPkGghfFxdrhmvbk-G7ZWLM7bdg3FMEiPk9SJdAoelCl99BiWR7JJ0uoMkdwIYjgaB9qQKgm6RLG8d5yDE/s1600-h/variablemapping.bmp"><img style="WIDTH: 320px; HEIGHT: 115px; CURSOR: hand" id="BLOGGER_PHOTO_ID_5389549480570338690" border="0" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgWbBYCAFJ7ONGlQEDNHI8s4Ehsp4ktCiQUmr89I0tXXU7WRQdiplHvcAh5eMPkGghfFxdrhmvbk-G7ZWLM7bdg3FMEiPk9SJdAoelCl99BiWR7JJ0uoMkdwIYjgaB9qQKgm6RLG8d5yDE/s320/variablemapping.bmp" /></a></p><p align="left"><span style="color:#330099;">Step5:</span> <span style="color:#339999;">Double click on File system task and change the operation to "Delete file" and Set IsSourcePathVariable property to TRUE. Then map your Var_FilePath to it</span>. (<span style="color:#ff0000;">Click Image</span>)<br /></p><p align="center"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi7EeZzqJwoqgWLAV3fOYZdxdjS-zTvLySY5sWAQbQxBjorJzV_59vky0ZE-LRjNhbZKo4OBt-Tojj0eiofALudp0VkbGQjKsu2uPn-RJd_fcoJBk4RJg69jV13jVu7fOaHkFQnHgLmzVY/s1600-h/FST.bmp"><img style="WIDTH: 320px; HEIGHT: 124px; CURSOR: hand" id="BLOGGER_PHOTO_ID_5389549476611875794" border="0" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi7EeZzqJwoqgWLAV3fOYZdxdjS-zTvLySY5sWAQbQxBjorJzV_59vky0ZE-LRjNhbZKo4OBt-Tojj0eiofALudp0VkbGQjKsu2uPn-RJd_fcoJBk4RJg69jV13jVu7fOaHkFQnHgLmzVY/s320/FST.bmp" /></a></p><p align="left"><br /><span style="color:#330099;">Step6:</span> <span style="color:#339999;">Now select the File System Task and press F4 for property window. Set the "Delay validation" to </span><span style="color:#ff0000;">True</span>.<br /></p><p align="center"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgGVsqJnEn4xZ03cFhi6ywFf_niLNBlM3dESuRV4I2w24jgYK5-6M8dehJkIrCtvxAVsdpeJI_lt8kHZLBoiWriDkIPEaOS7Hr3HMhpscjsa_r9C1kw4JO4iLqWi03TD7wKTzhoqoMesyI/s1600-h/delay.bmp"><img style="WIDTH: 320px; HEIGHT: 101px; CURSOR: hand" id="BLOGGER_PHOTO_ID_5389549468235469938" border="0" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgGVsqJnEn4xZ03cFhi6ywFf_niLNBlM3dESuRV4I2w24jgYK5-6M8dehJkIrCtvxAVsdpeJI_lt8kHZLBoiWriDkIPEaOS7Hr3HMhpscjsa_r9C1kw4JO4iLqWi03TD7wKTzhoqoMesyI/s320/delay.bmp" /></a></p><p align="left"><br /><span style="color:#330099;">Step7:</span> <span style="color:#339999;">Execute your package</span>. </p><p align="left">Hope this will help you.</p><p align="left">:)<br /><br /><br /><br /><br /></p>Manish Sharmahttp://www.blogger.com/profile/01694083809722708790noreply@blogger.com0tag:blogger.com,1999:blog-7713884638213403215.post-69521560403360774512009-10-06T07:23:00.000-07:002009-10-06T09:27:53.285-07:00Allocating a 'Set ID' & Extracting data in groups<p align="left"><span style="font-size:130%;color:#990000;">Question:</span><br /><span style="color:#336666;">Allocate a Set ID for a Batch of data and make a group.<br />OR<br /><a href="http://ewisdahl.spaces.live.com/blog/cns!23AC9944C8FA112A!501.entry">Retain Values from Previous Rows. </a><br /></span></p><br /><br /><p align="left"><span style="color:#999900;">Following is the input data and desired output for that</span><br /></p><p align="center"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhyhbGb2QRLRwFNqbygYrJi4sZj-0LOSP8St2I6XapeIEyAurBjLsrbK7kANhT6uscF9BJRGrnhNrtgsdJUeZQ-7KWHCvQXjgMX5HGMJjOEiAzaw_YKK0KqGz2w8stNZlC-DzB10fWlsOo/s1600-h/Input.bmp"><img style="WIDTH: 298px; HEIGHT: 162px; CURSOR: hand" id="BLOGGER_PHOTO_ID_5389496443338255218" border="0" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhyhbGb2QRLRwFNqbygYrJi4sZj-0LOSP8St2I6XapeIEyAurBjLsrbK7kANhT6uscF9BJRGrnhNrtgsdJUeZQ-7KWHCvQXjgMX5HGMJjOEiAzaw_YKK0KqGz2w8stNZlC-DzB10fWlsOo/s320/Input.bmp" /></a></p><p align="left"><br /></p><br /><br /><p><span style="font-size:180%;color:#990000;">Answer:</span></p><span style="color:#339999;">Drag a DFT in the control flow and add the following components.</span><br /><br /><br /><p align="center"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhDIpHhV3uGDQ4ShYPlz5qpra6egpM7pW9hybsgqNsuGrXnyy5jjQ4ZGnCtzzQFPPJOubbXSvYDbqTjXcjJAoojeDPaBm5OyNhCG1N30mx6Bt2LEolxmeQwkuQxOe3yyq-4truou_mtSt0/s1600-h/components.bmp"><img style="WIDTH: 292px; HEIGHT: 316px; CURSOR: hand" id="BLOGGER_PHOTO_ID_5389520437091947058" border="0" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhDIpHhV3uGDQ4ShYPlz5qpra6egpM7pW9hybsgqNsuGrXnyy5jjQ4ZGnCtzzQFPPJOubbXSvYDbqTjXcjJAoojeDPaBm5OyNhCG1N30mx6Bt2LEolxmeQwkuQxOe3yyq-4truou_mtSt0/s320/components.bmp" /></a></p><br /><span style="color:#339999;">For achieving this you need to use Script Component in DFT. and the following below steps: Before that you need to select Header as input column</span>.<br /><br /><p align="center"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgxfxVYqKr0XQSD22ffFTfIkfFB1NtvAPLCre4a31ilaeN9UgBqBZRykxpe6HXZ5Mcr0cMyuMFoH_PNRHDQHj7sWGGwyBblz2-79w4ZlP4dpR2gWxYvbqyQx-6v0ge9yH47XfPMojuwwfc/s1600-h/sc_Inputcolumn.bmp"><img style="WIDTH: 320px; HEIGHT: 206px; CURSOR: hand" id="BLOGGER_PHOTO_ID_5389518565020455522" border="0" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgxfxVYqKr0XQSD22ffFTfIkfFB1NtvAPLCre4a31ilaeN9UgBqBZRykxpe6HXZ5Mcr0cMyuMFoH_PNRHDQHj7sWGGwyBblz2-79w4ZlP4dpR2gWxYvbqyQx-6v0ge9yH47XfPMojuwwfc/s320/sc_Inputcolumn.bmp" /></a></p><br /><span style="color:#339999;">Create an output column in script component <sno><br /></span><br /><br /><p align="center"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhqBzSQx36xDW_82ZDK175_g417s-pAbbwcCn45KDz8udZTQJC2SSKU-3oweWdLTSNN7OYUiKqq6n0kHmGu_CTTJD0mYJQTG8Y6AlyPVfJR6jCxvQnekYFb33MPPIDsZfEPsfVe4m28Un4/s1600-h/sc.bmp"><img style="WIDTH: 320px; HEIGHT: 276px; CURSOR: hand" id="BLOGGER_PHOTO_ID_5389518878123456098" border="0" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhqBzSQx36xDW_82ZDK175_g417s-pAbbwcCn45KDz8udZTQJC2SSKU-3oweWdLTSNN7OYUiKqq6n0kHmGu_CTTJD0mYJQTG8Y6AlyPVfJR6jCxvQnekYFb33MPPIDsZfEPsfVe4m28Un4/s320/sc.bmp" /></a></p><span style="color:#339999;">Add the following code in the Script component</span>.<br />'*******************************************************************************<br /><span style="color:#000099;">Imports System</span><br /><span style="color:#000099;">Imports System.Data</span><br /><span style="color:#000099;">Imports Microsoft.SqlServer.Dts.Runtime</span><br /><span style="color:#000099;">Public Class ScriptMain </span><br /><span style="color:#000099;">Inherits UserComponent </span><br /><span style="color:#000099;">Dim int_Sno as integer ' // this variable should be global for the script (class level) </span><br /><span style="color:#000099;">Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer) </span><br /><span style="color:#000099;">Try </span><br /><span style="color:#000099;">If Row.RecType = "A" Then </span><br /><span style="color:#000099;">int_Sno = int_Sno + 1 </span><br /><span style="color:#000099;">End If </span><br /><span style="color:#000099;">Row.Sno = int_Sno </span><br /><span style="color:#000099;"></span><br /><span style="color:#000099;">Catch ex As Exception </span><br /><span style="color:#000099;">MsgBox(ex.Message) </span><br /><span style="color:#000099;">End Try </span><br /><span style="color:#000099;">End Sub</span><br /><span style="color:#000099;">End Class</span><br />******************************************************************<br /><br /><span style="color:#339999;">Now execute your package and you will get the desired output (Given below)</span><br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgbbwbH6US8GmU2FrCyJdg3nkCvxL67paVj3BGrml_W887ZkESZU0KJ3LJh2lvarc3rsuD_zDB_yvPSmo4afgs6JcEgVqBH4R4lAPbTyRkLmI77dY3H2k6Zvv0JaCy1tFB5gXBgFB25Cvs/s1600-h/in_out.bmp"><img style="WIDTH: 320px; HEIGHT: 211px; CURSOR: hand" id="BLOGGER_PHOTO_ID_5389509424854698754" border="0" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgbbwbH6US8GmU2FrCyJdg3nkCvxL67paVj3BGrml_W887ZkESZU0KJ3LJh2lvarc3rsuD_zDB_yvPSmo4afgs6JcEgVqBH4R4lAPbTyRkLmI77dY3H2k6Zvv0JaCy1tFB5gXBgFB25Cvs/s320/in_out.bmp" /></a><br /><br /><br /><span style="color:#339999;">Hope this will help you.</span><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhew1iPZztX8TklTKH98uaWqUKMu4MfUlPY_PhaBtGxUKiyI-pvvCGAHHAaZU-wH74BBWXW2Owpavz57itlko0-5oqc5wtiXLilYBEDi13tDGoIXsAMcCMySiVaOdSJXGC4Z5TzvTMFUbM/s1600-h/script.bmp"></a>Manish Sharmahttp://www.blogger.com/profile/01694083809722708790noreply@blogger.com1tag:blogger.com,1999:blog-7713884638213403215.post-46492966947729719672009-08-14T08:33:00.000-07:002009-08-14T08:48:23.782-07:00Excel destination - cannot convert between unicode and non-unicodeWhile using the Excel Destination we used to get following error:<br />Error1 Validation error. Data Flow Task: Excel Destination [1311]: Column "Order Date" cannot convert between Unicode and non-Unicode string data types.<br /><br />OLE DB source query:<br />where order_date data type is datetime in DB. Here we are converting to varchar<br /><br /><div><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi5XEPkGClEC4EZthQ5UaLhzQYzoVBIrjrdO-0FoYNOdfCLOfLptDJPLYw-NNB6cK91BrkljtsqisO9KQHhElk0tsIzn9jPePfP7P2a8zhI1pty6C85-dBCnfI17qxPU_Z9YTW8U-cRhMc/s1600-h/1.bmp"><img style="WIDTH: 320px; HEIGHT: 128px; CURSOR: hand" id="BLOGGER_PHOTO_ID_5369844626910191186" border="0" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi5XEPkGClEC4EZthQ5UaLhzQYzoVBIrjrdO-0FoYNOdfCLOfLptDJPLYw-NNB6cK91BrkljtsqisO9KQHhElk0tsIzn9jPePfP7P2a8zhI1pty6C85-dBCnfI17qxPU_Z9YTW8U-cRhMc/s320/1.bmp" /></a><br /><br />DFT (OLE DB Source and Excel destination)<br /><br /><div><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhX5seDHhcCaiDa-ZqEvJr4_lEs_9mPp_eo3v48w727FXQwv6dS67eTwGGfwMV58j1Tf9jBsSOixItzrz0b1I-zFU1ceI7RuzOJa7rie9jfsCGKszZnDJfggqDpaJ5IZ8zMmv4XeUGiMh4/s1600-h/2.bmp"><img style="WIDTH: 410px; HEIGHT: 173px; CURSOR: hand" id="BLOGGER_PHOTO_ID_5369844636092984274" border="0" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhX5seDHhcCaiDa-ZqEvJr4_lEs_9mPp_eo3v48w727FXQwv6dS67eTwGGfwMV58j1Tf9jBsSOixItzrz0b1I-zFU1ceI7RuzOJa7rie9jfsCGKszZnDJfggqDpaJ5IZ8zMmv4XeUGiMh4/s320/2.bmp" /></a><br /><br />Table structure:<br /><div><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjqHCPejZ5NOmFF7uNtpifnAJoRLFjx9B6om88jN7J2CxSRjJhuWrwR7Mzzk4tFdtPuqIEHmWxtMHkL5pyqYHMS9H_ruflSMcZxtWVLrWWJbV7zu7dmHYlvyZ_RxUMdhiM22sDz-JT5B7g/s1600-h/3.bmp"><img style="WIDTH: 320px; HEIGHT: 68px; CURSOR: hand" id="BLOGGER_PHOTO_ID_5369844644706734722" border="0" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjqHCPejZ5NOmFF7uNtpifnAJoRLFjx9B6om88jN7J2CxSRjJhuWrwR7Mzzk4tFdtPuqIEHmWxtMHkL5pyqYHMS9H_ruflSMcZxtWVLrWWJbV7zu7dmHYlvyZ_RxUMdhiM22sDz-JT5B7g/s320/3.bmp" /></a><br /></div><div>Answer:<br />I checked on MSDN for excel destination. Here is some content written about moving data for your reference: Data types. The Excel driver uses only six data types, which Integration Services maps as follows: · Numeric – double-precision float (DT_R8) · Currency – currency (DT_CY) · Boolean – Boolean (DT_BOOL) · Date/time – date (DT_DATE) · String – Unicode string, length 255 (DT_WSTR) · Memo – Unicode text stream (DT_NTEXT)<br /><br />Integration Services does not implicitly convert data types. for resolving this problem please follow these steps.<br /><br />Step1: Add Data Conversion task between Source and Excel destination<br /></div><div><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEipKqDYLfqZYeJoocUsQzUIYvIhE1fCru5W_0tpv3VrvR4Aa17s6IukGctz2W4WYXEFqYVtuowtkPoswtx9CzC7V7FXSbVe3w-CB_4buv1zoU3o7zmpk-jrg39EQOqTnjKB9iY85jqZK7s/s1600-h/4.jpg"><img style="WIDTH: 320px; HEIGHT: 317px; CURSOR: hand" id="BLOGGER_PHOTO_ID_5369844656791628370" border="0" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEipKqDYLfqZYeJoocUsQzUIYvIhE1fCru5W_0tpv3VrvR4Aa17s6IukGctz2W4WYXEFqYVtuowtkPoswtx9CzC7V7FXSbVe3w-CB_4buv1zoU3o7zmpk-jrg39EQOqTnjKB9iY85jqZK7s/s320/4.jpg" /></a><br /></div><div>Step2: Edit the data conversion task and change the data type to [DT_WSTR], change the length if required.<br /></div><div><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi2qt7DDewoX-eMWQCGhjO0UakFNh-W_HNnCJmr6h3iONMFyGLdvecrwa_chGdYEImxH3pUViBzoNGGqAaaCxXTKNdLBd4jlKQqF4aVRrX-qcR672Si51VTAIUy7uD4QqWZbD0WKBbsFkI/s1600-h/4.bmp"><img style="WIDTH: 382px; HEIGHT: 180px; CURSOR: hand" id="BLOGGER_PHOTO_ID_5369844650757845458" border="0" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi2qt7DDewoX-eMWQCGhjO0UakFNh-W_HNnCJmr6h3iONMFyGLdvecrwa_chGdYEImxH3pUViBzoNGGqAaaCxXTKNdLBd4jlKQqF4aVRrX-qcR672Si51VTAIUy7uD4QqWZbD0WKBbsFkI/s320/4.bmp" /></a><br /><br />Step3: After adding the data conversion, the error message will go. Execute your package.<br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgJkBHt2vMUC9__bCh9bfF49aogbFpN9A8OSVOh1Ktv7Ndtpn6_5FcdUVsdHnAk-HACcLLrquEZ9jjOE8TdPzus_GzSePN8DIfd7vZLJnsJiZWmOpfcs18NBQ4rMJ7BmhX8mSBk8YXASuI/s1600-h/6.jpg"><img style="WIDTH: 320px; HEIGHT: 317px; CURSOR: hand" id="BLOGGER_PHOTO_ID_5369844828948757874" border="0" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgJkBHt2vMUC9__bCh9bfF49aogbFpN9A8OSVOh1Ktv7Ndtpn6_5FcdUVsdHnAk-HACcLLrquEZ9jjOE8TdPzus_GzSePN8DIfd7vZLJnsJiZWmOpfcs18NBQ4rMJ7BmhX8mSBk8YXASuI/s320/6.jpg" /></a><br />It will create an Excel file and load the data.<br /><br /><br /><br /><div></div></div></div></div>Manish Sharmahttp://www.blogger.com/profile/01694083809722708790noreply@blogger.com0tag:blogger.com,1999:blog-7713884638213403215.post-91367640541216787072009-07-20T21:49:00.000-07:002009-07-20T22:29:02.389-07:00How to include Primary ID column using SSIS package?<span style="font-weight: bold; color: rgb(102, 0, 0);">Question:</span><br /><span style="font-size:85%;">I have target table where columns are ID, Name & DateofBirth where ID is PK and should be increment of 1(eg. 1, 2, 3, 4... and so on) also NOTE that ID column is <strong>NOT</strong> AN IDENTITY column<br /><br />Now I have flat file which has data with only Names & Dateofbirth.<br /></span><a href="http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/db36004b-30a8-4404-8f95-301cc4a628f1"><br /><span style="font-size:78%;">http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/db36004b-30a8-4404-8f95-301cc4a628f1</span></a><span style="font-size:78%;"><br /></span><span style="font-weight: bold; color: rgb(102, 0, 0);"><br />Answer:</span><br /><span style="font-size:85%;">Hi, There are two ways.<br /><br /></span><div style="text-align: justify;"><span style="color: rgb(153, 153, 0);font-size:85%;" ><span style="color: rgb(102, 0, 0);">First::</span> </span><span style="font-size:85%;"> Download the "<a href="http://sqlis.com/post/Row-Number-Transformation.aspx">Row Number Transformation</a>" and use it in your SSIS package.<br /><br />The Row Number Transformation calculates a row number for each row, and adds this as a new output column to the data flow. The column number is a sequential number, based on a seed value. Each row receives the next number in the sequence, based on the defined increment value.<br /><br /></span><span style="color: rgb(102, 0, 0);font-size:85%;" >Second::</span><span style="font-size:85%;"> Add a "Script Component transformation" and write a script there for adding the row numbers. Please check the "<a id="viewpost_ascx_TitleUrl" title="Title of this entry." href="http://weblogs.sqlteam.com/jamesn/archive/2008/02/13/60509.aspx">Row Numbers in a DataFlow</a>"for step-by-step explanation.<br /></span><br /><br /></div>Manish Sharmahttp://www.blogger.com/profile/01694083809722708790noreply@blogger.com0tag:blogger.com,1999:blog-7713884638213403215.post-35883388373626833142009-07-20T09:52:00.000-07:002009-07-20T10:02:52.425-07:00AboutThisBlogThe main motto of this blog is to share my knowledge and experience with you.Manish Sharmahttp://www.blogger.com/profile/01694083809722708790noreply@blogger.com0