tag:blogger.com,1999:blog-55343741970674629252024-03-13T12:31:53.905-04:00Plexus SQL<br><i> This blog is home to share my experiences, views, learning and findings on SQL Server 2005 & SQL Server 2008, MS-Business Intelligence - SSIS, SSRS. <br> </i>
<br><b> Feel free to contact me at SNikkhah@Live.ca <br> </b> <i><b> Location: Toronto Canada</b></i>Shahriar Nikkhah (Nik)http://www.blogger.com/profile/17215555681462843010noreply@blogger.comBlogger11125tag:blogger.com,1999:blog-5534374197067462925.post-31828490084191670662010-06-18T13:53:00.023-04:002010-10-05T14:17:20.727-04:00Chronological Order (List files by oldest first)<span style="color:#3366ff;"><strong>Chronological Order (List files by oldest first)</strong><br /></span>Extracting (listing) the files in a chronological order by, (this example “Modified Date”).<br /><br /><span style="color:#3366ff;"><strong>Criteria</strong><br /></span>--<br /><span style="color:#3366ff;"><strong>Over all View</strong></span><br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjXxmqpB7owkGrv1oi50-lUuPyhy-SAuK-xCsMvZDoTQ_DCazFCIxam0NLSOPnNI6nIrrrp625ASSmHN5GYwg7is3kbzwSJXZ0lfCHw3OMJjsDIlu8Qm5vv-1XzUIB0NIUJAhUZ13lAs2Eo/s1600/New+Bitmap+Image+(2).bmp"><img style="WIDTH: 372px; HEIGHT: 400px; CURSOR: hand" id="BLOGGER_PHOTO_ID_5484180552488515506" border="0" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjXxmqpB7owkGrv1oi50-lUuPyhy-SAuK-xCsMvZDoTQ_DCazFCIxam0NLSOPnNI6nIrrrp625ASSmHN5GYwg7is3kbzwSJXZ0lfCHw3OMJjsDIlu8Qm5vv-1XzUIB0NIUJAhUZ13lAs2Eo/s400/New+Bitmap+Image+(2).bmp" /></a><br /><span style="color:#3366ff;"></span><br /><span style="color:#3366ff;"><strong>Defining some variables in the package</strong></span><br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjZen3fgUQ08WtopZkRrtVfL2Kz7vzcbO0HXY0pWNQv5b4HLac3i-KGijyXZGY0fBOBpljyIuV-e7IcCavlLugMWXfoL6HUDgCxjr4FA2ryZ3ZLAy-vUuqEPWefC-citMT0h-IErJlUQCNo/s1600/New+Bitmap+Image+(3).bmp"><img style="WIDTH: 400px; HEIGHT: 268px; CURSOR: hand" id="BLOGGER_PHOTO_ID_5484180318514670834" border="0" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjZen3fgUQ08WtopZkRrtVfL2Kz7vzcbO0HXY0pWNQv5b4HLac3i-KGijyXZGY0fBOBpljyIuV-e7IcCavlLugMWXfoL6HUDgCxjr4FA2ryZ3ZLAy-vUuqEPWefC-citMT0h-IErJlUQCNo/s400/New+Bitmap+Image+(3).bmp" /></a><br /><br />1. “<strong>uVar_ArrayOfFilesProperties</strong>” An Object that will contain each files information.<br />2. “<strong>uVar_CurrentFileName</strong>” Name of the current file<br />3. “<strong>uVar_DateCreated</strong>” File creation date<br />4. “<strong>uVar_DateMofified</strong>” File creation modified<br />5. “<strong>uVar_FileExtention</strong>” i.e. “xls” for excel, “txt” for text.<br />6. “<strong>uVar_FileName</strong>” The file name only without the file extension.<br />7. “<strong>uVar_FirstTime</strong>” A flag for the flow of the package<br />8. “<strong>uVar_FolderNameOfTheFilesToBeProcessed</strong>” The main folder root of the files that need to be in a chronological order.<br />9. “<strong>uVar_Path</strong>” The path of each file<br />10. “<strong>uVar_PathFileName</strong>” The path, filename and extension all together<br />11. “<strong>uVar_SourceFileExtention</strong>” This variable will define the file extension (e.g. *.csv).<br /><br /><span style="color:#3366ff;"><strong>Adding Object (controls) in the Control Flow</strong></span><br />Adding the objects as seen in the “Over all View”<br /><br />1. FELC--Looping through files and folder<br />The first loop will find the file and process them one by one<br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjgE3Yo7M_SVjCYN4aNURA0lNbYBkaJX1b_4UB6lBt0qGxGWQ0-h02mHxgdrQHhCWQVpCDACifsrptM95pp3lLT31TJSffcV1M4e8ODt7JPTU7mwBx2Lg_DfHONTZFf4bE7PFbNzsGxiG_S/s1600/New+Bitmap+Image+(4).bmp"><img style="WIDTH: 400px; HEIGHT: 336px; CURSOR: hand" id="BLOGGER_PHOTO_ID_5484174583903631906" border="0" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjgE3Yo7M_SVjCYN4aNURA0lNbYBkaJX1b_4UB6lBt0qGxGWQ0-h02mHxgdrQHhCWQVpCDACifsrptM95pp3lLT31TJSffcV1M4e8ODt7JPTU7mwBx2Lg_DfHONTZFf4bE7PFbNzsGxiG_S/s400/New+Bitmap+Image+(4).bmp" /></a><br /><br />2. SCR--Getting each files property information<br />The script will retrieve each files property; it’s like right clicking on a file and selecting properties.<br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh_wDeAiKH7DDsfixjwPrhGdDYbRqIhHSoVExWEoHbzVoWlrVlfG-J_EtGU3chfoi8FlRLFpJr2dZtq3j9zRMhiG6p6IExalxxJ6TBjt9GPGJGxOBB8MZ7V-FXxMpmMyAY5lMYDm1a41E1L/s1600/New+Bitmap+Image+(5).bmp"><img style="WIDTH: 400px; HEIGHT: 75px; CURSOR: hand" id="BLOGGER_PHOTO_ID_5484174578738450258" border="0" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh_wDeAiKH7DDsfixjwPrhGdDYbRqIhHSoVExWEoHbzVoWlrVlfG-J_EtGU3chfoi8FlRLFpJr2dZtq3j9zRMhiG6p6IExalxxJ6TBjt9GPGJGxOBB8MZ7V-FXxMpmMyAY5lMYDm1a41E1L/s400/New+Bitmap+Image+(5).bmp" /></a><br /><br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj2U0hS4lDK6CrhPxQ3x7QDNcK-5x1drXg24yAaHBSIotQY4lLs1X5DQjWNBluRC3-iuBJnReKU6dmpBIUXqH7_P5j8E4wNSc2tF6T8hJgX5IoOu_QBu4PGIZBB1CvVb08nv7ksTxt1fgZV/s1600/New+Bitmap+Image+(6).bmp"><img style="WIDTH: 400px; HEIGHT: 340px; CURSOR: hand" id="BLOGGER_PHOTO_ID_5484174569841434834" border="0" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj2U0hS4lDK6CrhPxQ3x7QDNcK-5x1drXg24yAaHBSIotQY4lLs1X5DQjWNBluRC3-iuBJnReKU6dmpBIUXqH7_P5j8E4wNSc2tF6T8hJgX5IoOu_QBu4PGIZBB1CvVb08nv7ksTxt1fgZV/s400/New+Bitmap+Image+(6).bmp" /></a><br />The files properties information will be saved in the variables (See the script or check the variables with a red dot in the variable table).<br /><br />3. DFT--List of sorted files by ModifiedDate<br />Over view of the DFT<br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgvzsX08qAh6-VoPbHJWGABs9ZXz4QH61FNbx13uKGWZ_oR3aKuRBEKpPMF5pEcj3x2Ot42jYWPjfxo_8cWRriZKjMfiQ4yIbjQm-TSyRWQCCkiQAawDKXtyLX1VMb4wCkTW_4SyHNYpOv4/s1600/New+Bitmap+Image+(7).bmp"><img style="WIDTH: 400px; HEIGHT: 205px; CURSOR: hand" id="BLOGGER_PHOTO_ID_5484174572540060818" border="0" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgvzsX08qAh6-VoPbHJWGABs9ZXz4QH61FNbx13uKGWZ_oR3aKuRBEKpPMF5pEcj3x2Ot42jYWPjfxo_8cWRriZKjMfiQ4yIbjQm-TSyRWQCCkiQAawDKXtyLX1VMb4wCkTW_4SyHNYpOv4/s400/New+Bitmap+Image+(7).bmp" /></a><br /><br />In the DFT you will have<br />a. SCR--Appending the records in SSIS object variable with the current file info for the output records<br /><br />You will have to use the ADD COLUMN option and add each Fields; you will have to select the right data type for each field<br /><br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhblPcu3ye0ILMbLHQgHoE44flYMbxx6RbUYsoKC_HvXtbmVfAkCtu1VurrdFbRvShpkqPh-1LEKI1anr0n1u0zaVjbKU0de6ZX1IBXPfgMitouRCMt-MnmCLBwiaDEqp4FFOjOe2lQ7n24/s1600/New+Bitmap+Image+(8).bmp"><img style="WIDTH: 400px; HEIGHT: 150px; CURSOR: hand" id="BLOGGER_PHOTO_ID_5484174568177469474" border="0" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhblPcu3ye0ILMbLHQgHoE44flYMbxx6RbUYsoKC_HvXtbmVfAkCtu1VurrdFbRvShpkqPh-1LEKI1anr0n1u0zaVjbKU0de6ZX1IBXPfgMitouRCMt-MnmCLBwiaDEqp4FFOjOe2lQ7n24/s400/New+Bitmap+Image+(8).bmp" /></a><br /><br />(Example for data type: DateModified = [DT_DBTIMESTAMP] , Path = [DT_WSTR] 3000, FileName = = [DT_WSTR] 200 , etc...)<br /><div><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh4UL4XtrBUEnWKas0IkvdSup5QRnWC3YRMlPfv0Z94MLVsLyOnToJZsDzP6gl6v50zkZWP017DiRcWcbJAbmIX9XCk0fUpytmv-I0F0h2hmlqZXKSwsmZWBme6o3TCpsjP_G82dfT_wVwb/s1600/New+Bitmap+Image+(9).bmp"><img style="WIDTH: 400px; HEIGHT: 67px; CURSOR: hand" id="BLOGGER_PHOTO_ID_5484174251730284770" border="0" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh4UL4XtrBUEnWKas0IkvdSup5QRnWC3YRMlPfv0Z94MLVsLyOnToJZsDzP6gl6v50zkZWP017DiRcWcbJAbmIX9XCk0fUpytmv-I0F0h2hmlqZXKSwsmZWBme6o3TCpsjP_G82dfT_wVwb/s400/New+Bitmap+Image+(9).bmp" /></a><br /><br />All variable that contain the files information must be passed on to the Script component and the “User::uVar_ArrayOfFileProperties” variable.<br />The files properties information will be saved in the variables (See the script or check the variables with a red dot in the variable table).<br />• Scrip<br />The script is divided into 2 section, the first section is before the “IF” and the second section is within the “IF”, the first section is adding the current files information into the Output Buffer of the script component, the second part is looping through all the records in the SSIS object “uVarArrayOfFileProperties” and adding them to the Output Buffer of the script component, so the output result will always be the current files and the previous files information.<br />---------------<br /><span style="color:#3366ff;">Public Overrides Sub</span> CreateNewOutputRows()<br />'<br />'<br /><span style="color:#3366ff;">With</span> MainOutputrecordsBuffer<br />.AddRow()<br />.PathFileName = <span style="color:#3366ff;">Me</span>.Variables.uVarPathFileName<br />.Path = <span style="color:#3366ff;">Me</span>.Variables.uVarPath<br />.FileName = <span style="color:#3366ff;">Me</span>.Variables.uVarFileName<br />.FileExtension = <span style="color:#3366ff;">Me</span>.Variables.uVarFileExtension<br />.DateCreated = <span style="color:#3366ff;">Me</span>.Variables.uVarDateCreated<br />.DateModified = <span style="color:#3366ff;">Me</span>.Variables.uVarDateModified<br /><span style="color:#3366ff;">End With</span><br /><br /><span style="color:#3366ff;">If Me</span>.Variables.uVarFirstTime =<span style="color:#3366ff;"> False Then</span><br /><br /><span style="color:#3366ff;">Dim</span> oleDA <span style="color:#3366ff;">As New</span> Data.OleDb.OleDbDataAdapter<br /><span style="color:#3366ff;">Dim</span> dt <span style="color:#3366ff;">As New</span> Data.DataTable<br /><br /><span style="color:#3366ff;">Dim</span> row <span style="color:#3366ff;">As</span> System.Data.DataRow<br /><br /><span style="color:#3366ff;">Dim</span> strPathFileName <span style="color:#3333ff;">As Integer<br /></span><span style="color:#3366ff;">Dim</span> strPath <span style="color:#3366ff;">As Integer<br /></span><span style="color:#3366ff;">Dim</span> strFileName <span style="color:#3366ff;">As Integer<br /></span><span style="color:#3366ff;">Dim</span> strFileExtension <span style="color:#3366ff;">As Integer<br /></span><span style="color:#3366ff;">Dim</span> strDateCreated <span style="color:#3366ff;">As Integer<br /></span><span style="color:#3366ff;">Dim</span> strDateModified <span style="color:#3366ff;">As Integer<br /></span><br />oleDA.Fill(dt, <span style="color:#3366ff;">Me</span>.Variables.uVarArrayOfFileProperties)<br /><br />strPathFileName = dt.Columns("<span style="color:#993300;">PathFileName</span>").Ordinal<br />strPath = dt.Columns("<span style="color:#993300;">Path</span>").Ordinal<br />strFileName = dt.Columns("<span style="color:#993300;">FileName</span>").Ordinal<br />strFileExtension = dt.Columns("<span style="color:#993300;">FileExtension</span>").Ordinal<br />strDateCreated = dt.Columns("<span style="color:#993300;">DateCreated</span>").Ordinal<br />strDateModified = dt.Columns("<span style="color:#993300;">DateModified</span>").Ordinal<br /><br /><span style="color:#3366ff;">For Each</span> row <span style="color:#3366ff;">In</span> dt.Rows<br /><span style="color:#3366ff;">With</span> MainOutputrecordsBuffer<br />.AddRow()<br />.PathFileName = row(strPathFileName).ToString()<br />.Path = row(strPath).ToString()<br />.FileName = row(strFileName).ToString()<br />.FileExtension = row(strFileExtension).ToString()<br />.DateCreated = CType(row(strDateCreated).ToString(), DateTime)<br />.DateModified = CType(row(strDateModified).ToString(), DateTime)<br /><span style="color:#3366ff;">End With<br /></span><span style="color:#3366ff;">Next<br /></span>MainOutputrecordsBuffer.EndOfRowset()<br /><br /><span style="color:#3366ff;">End If<br />End Sub</span><br />----------------<br /><br />b. SRT--Sorting the list by "DateModified"<br />This is almost where you have to choose your chronological order , I have selected the modified date, why?<br /><div><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjCvtTVacqOILKpPXwlLwWPcqxGFm9GavIkMlN2I3KONfvMQtJShkmQqDHtO_OMN4lkE_lwf7P5l45t3o1aSj-aDVEhADO0BaZ0_pmGwEkH0WM8rNzZmvQjlrh0HdPBBL7EkE0SNfhzPrLi/s1600/New+Bitmap+Image+(10).bmp"><img style="WIDTH: 400px; HEIGHT: 331px; CURSOR: hand" id="BLOGGER_PHOTO_ID_5484174243765790690" border="0" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjCvtTVacqOILKpPXwlLwWPcqxGFm9GavIkMlN2I3KONfvMQtJShkmQqDHtO_OMN4lkE_lwf7P5l45t3o1aSj-aDVEhADO0BaZ0_pmGwEkH0WM8rNzZmvQjlrh0HdPBBL7EkE0SNfhzPrLi/s400/New+Bitmap+Image+(10).bmp" /></a><br /><br />Do you think that you can find a files property that says the Create date is bigger that the modified date (Create = 2010-01-01 and Modified = 2007-01-01)? Yes you can, a file can be made on a computer on 2005, and be modified on the same computer on 2007, so far so good, and the same file can be pushed to a another computer on 2010-01-01 so on the second computer the files properties show that Create Date = 2010-01-01 and Modified Date= 2007-01-01.<br />c. RS_DST--Saving records in the SSIS objects<br />Now we have to save the sorted result to a SSIS Object variable.<br /><div><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhURV-GVtsYUCMzvnHVaEoTTYXa4OaBeDyp_iYAd98TTEQooB7nFEKOOfq_bM9xyfSKYXt6NHVIBP4whGVo7mZyfWRMeTnuNFTaMeeEXxwqkOb6N3zun0zHYiR_8DqfRSxXBP3w6aD10YMU/s1600/New+Bitmap+Image+(11).bmp"><img style="WIDTH: 400px; HEIGHT: 39px; CURSOR: hand" id="BLOGGER_PHOTO_ID_5484174243025161410" border="0" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhURV-GVtsYUCMzvnHVaEoTTYXa4OaBeDyp_iYAd98TTEQooB7nFEKOOfq_bM9xyfSKYXt6NHVIBP4whGVo7mZyfWRMeTnuNFTaMeeEXxwqkOb6N3zun0zHYiR_8DqfRSxXBP3w6aD10YMU/s400/New+Bitmap+Image+(11).bmp" /></a><br /><br />Please remember to select all fields.<br /><div><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgbNEg3CIRPBl6sGclHvVLFJuPxQgLUB0ytvLLNqBWao54ounFD20UzgJEXjK0Di3X-CJWEf6bCYhTUlyDyOn7S6dHvdohwh_ZHohrlwO3H1JlO9Ht-xOJPu9M_9kKr9CLbGB99qruymZbs/s1600/New+Bitmap+Image+(12).bmp"><img style="WIDTH: 400px; HEIGHT: 321px; CURSOR: hand" id="BLOGGER_PHOTO_ID_5484174235401510786" border="0" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgbNEg3CIRPBl6sGclHvVLFJuPxQgLUB0ytvLLNqBWao54ounFD20UzgJEXjK0Di3X-CJWEf6bCYhTUlyDyOn7S6dHvdohwh_ZHohrlwO3H1JlO9Ht-xOJPu9M_9kKr9CLbGB99qruymZbs/s400/New+Bitmap+Image+(12).bmp" /></a><br /><br />4. SCR--Setting a flag<br />Passing on the flag variable to set the flag to FALSE, the reason we do this is that, if you look at the script in the DFT you will see that the flag is been used because the SSIS Object does not have a structure format for the first time so we won’t be able to use the object (See in script “oleDA.Fill(dt, Me.Variables.uVarArrayOfFileProperties)”).<br /><div><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhd4qQgUOoAUDP7H5roebxzyT64UuWpnQnrhtxYw30wJZV_PS-rSVZxKLgOIpA0s08M5RxGMa6hBGtJ3P6NkzZlCbFj82DMzUu4kkVGP-CmuReCAWp1vIR5xvwJ5m4rA3shTYF8LhP6wXG-/s1600/New+Bitmap+Image+(13).bmp"><img style="WIDTH: 400px; HEIGHT: 169px; CURSOR: hand" id="BLOGGER_PHOTO_ID_5484174229355339410" border="0" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhd4qQgUOoAUDP7H5roebxzyT64UuWpnQnrhtxYw30wJZV_PS-rSVZxKLgOIpA0s08M5RxGMa6hBGtJ3P6NkzZlCbFj82DMzUu4kkVGP-CmuReCAWp1vIR5xvwJ5m4rA3shTYF8LhP6wXG-/s400/New+Bitmap+Image+(13).bmp" /></a><br /><br /><div><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhWiEGcZuklkNk7H-0-BaaVvIQCBoh3YQcmgIC-jWdLwiP7oh7ScrhR20q4_RheICDkOBvUV-MhNRTYjsFLg_LdLIdxzeGFEbgyypAn0bx407l00SgM6822iQRfdQ7iwZc5mAc5TgqxWkWS/s1600/New+Bitmap+Image+(14).bmp"><img style="WIDTH: 400px; HEIGHT: 102px; CURSOR: hand" id="BLOGGER_PHOTO_ID_5484173973354013106" border="0" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhWiEGcZuklkNk7H-0-BaaVvIQCBoh3YQcmgIC-jWdLwiP7oh7ScrhR20q4_RheICDkOBvUV-MhNRTYjsFLg_LdLIdxzeGFEbgyypAn0bx407l00SgM6822iQRfdQ7iwZc5mAc5TgqxWkWS/s400/New+Bitmap+Image+(14).bmp" /></a><br /><br />5. FELC--Looping the sorted file list<br />Setting the second file<br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhpSptJthpD5acaX-F4z3tSxzneDkuceAV8hzjrcFfNzeZ4PiLxF6z4gLMHIyYaDlFEVbjYKHR8BWrqCW_3sLTw0bnPZffNjuPJ9HKLn_kqzIY7DtN6D4A1b4569nmAAjeB1-Wc7-6VPw7W/s1600/New+Bitmap+Image+(15).bmp"><img style="WIDTH: 400px; HEIGHT: 253px; CURSOR: hand" id="BLOGGER_PHOTO_ID_5484173974732960642" border="0" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhpSptJthpD5acaX-F4z3tSxzneDkuceAV8hzjrcFfNzeZ4PiLxF6z4gLMHIyYaDlFEVbjYKHR8BWrqCW_3sLTw0bnPZffNjuPJ9HKLn_kqzIY7DtN6D4A1b4569nmAAjeB1-Wc7-6VPw7W/s400/New+Bitmap+Image+(15).bmp" /></a><br /><br />This will loop through each record in the object one by one and save the value in the mentioned variables.<br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh4tdOCEbeP1XRfCNl-EpX74UueMt1_00Iv5YBEZaEHcb8S1uL8DuPHSnnw0n_3W1BZWjEU5dXJD8u3aA52jy1SvZGKOuAyv92cO34G-snmbg1WL985l9F6Rw8ZONdLtWz8ar5PPduMG-4J/s1600/New+Bitmap+Image+(16).bmp"><img style="WIDTH: 400px; HEIGHT: 177px; CURSOR: hand" id="BLOGGER_PHOTO_ID_5484173966777297442" border="0" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh4tdOCEbeP1XRfCNl-EpX74UueMt1_00Iv5YBEZaEHcb8S1uL8DuPHSnnw0n_3W1BZWjEU5dXJD8u3aA52jy1SvZGKOuAyv92cO34G-snmbg1WL985l9F6Rw8ZONdLtWz8ar5PPduMG-4J/s400/New+Bitmap+Image+(16).bmp" /></a><br /><br />6. SCR-- Show the result in a MsgBox (For test only)<br />In the second loop I am sure that you will be having a DFT to do the main process in my case I just want to display the sorted order.<br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgpDMo492nsT3XSIWtH07O5MsWZAMORvU2BXKmdEgcE2yz6dPfAc8pd8V9Eh1n_vDMoZY7VEf72PYeYPUhnYNHzQT0N6FAyxjVijznnHjuyrTnssxp0GsCgwF1TZBQL2iv_f-kzCyDi42Gx/s1600/New+Bitmap+Image+(17).bmp"><img style="WIDTH: 400px; HEIGHT: 133px; CURSOR: hand" id="BLOGGER_PHOTO_ID_5484173960508635330" border="0" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgpDMo492nsT3XSIWtH07O5MsWZAMORvU2BXKmdEgcE2yz6dPfAc8pd8V9Eh1n_vDMoZY7VEf72PYeYPUhnYNHzQT0N6FAyxjVijznnHjuyrTnssxp0GsCgwF1TZBQL2iv_f-kzCyDi42Gx/s400/New+Bitmap+Image+(17).bmp" /></a><br /><br />Note: Step 6 is only for testing only, please delete the object after testing.<br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjKALEkdQs_nSXF3QsdeYsI120b-BU8xT9nM4BFtzE4Em1QSpp_DJuc4Q8E76pe13b_oFbX1-iiW7uOJVCYdA_5r1gcrqcrWjj5C8rMtnq7yJrY3thrAXwpWJ3jXEf9LcEp4z59w_z1RymB/s1600/New+Bitmap+Image+(18).bmp"><img style="WIDTH: 400px; HEIGHT: 152px; CURSOR: hand" id="BLOGGER_PHOTO_ID_5484173953958707330" border="0" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjKALEkdQs_nSXF3QsdeYsI120b-BU8xT9nM4BFtzE4Em1QSpp_DJuc4Q8E76pe13b_oFbX1-iiW7uOJVCYdA_5r1gcrqcrWjj5C8rMtnq7yJrY3thrAXwpWJ3jXEf9LcEp4z59w_z1RymB/s400/New+Bitmap+Image+(18).bmp" /></a><br /><br /><strong><span style="color:#3366ff;">Testing Scenarios</span></strong><br />Before testing make sure that you have set the variables according to the snap shot of the variables that I have and check the “Criteria”.<br /><br /><strong>Scenario 1:<br /></strong>1- Select a folder that contains the files that you need to be sorted<br />2- Cut and past the folder into the SSIS variable “uVar_FolderNameOfFilesToBeProcessed”, this will point to that folder<br />3- Maybe you want to select a certain group of files like *.csv then you need to put “*.csv” in the variable “uVar_SourceFileExtention”<br />4- Run the package and you will see a MsgBox showing the files properties, check the files Modified date with the results of the MsgBox<br /><br /><strong>Note:</strong> Step 6 is only for testing please delete the second SCR after testing. </div></div></div></div></div></div>Shahriar Nikkhah (Nik)http://www.blogger.com/profile/17215555681462843010noreply@blogger.com2tag:blogger.com,1999:blog-5534374197067462925.post-54724843607640348212010-06-15T11:37:00.026-04:002018-04-01T22:49:06.681-04:00Looping Through CSV Files using SSIS<strong><span style="color: #3366ff;"><br /></span></strong>
<strong><span style="color: red;">2018-04-01</span></strong><br />
<strong><span style="color: red;">Hi </span></strong><br />
<strong><span style="color: red;">I have an updated version of this blog (SSIS 2016) at my new blog </span></strong><br />
<h4>
<span style="color: blue;"><b>Click <a href="http://sqldataside.blogspot.ca/2018/04/looping-through-flat-files-using-ssis.html">here</a></b></span></h4>
<strong><span style="color: #3366ff;"><br /></span></strong>
<strong><span style="color: #3366ff;"><br /></span></strong>
<strong><span style="color: #3366ff;">Looping Through CSV Files</span></strong><br />
This program will loop through a folder and its sub folders, find all the *.CSV files and retrieve data from each file and inserts the record to a destination table and finally moving the file to a backup folder after each loop cycle.<br />
<br />
<strong><span style="color: #3366ff;">Criteria</span></strong><br />
1. The file format must be in a *.CSV format (Flat File)<br />
2. A sample source file of the CSV file (Please renamed to SampleCSVFile.csv)<br />
3. A Folder that will be the main folder of the ETL (in this case it will be named => C:\SSIS\LoopingThroughCSVFiles)<br />
4. Two other sub folders 1- “Sample” 2- “ToBeProcessed”<br />
5. Copy the CSV Sample File in the “Sample Folder”<br />
6. All headers must be on the first row of each CSV file<br />
7. No empty/missed rows are accepted, remove them all<br />
<br />
<strong><span style="color: #3366ff;">Over all View </span></strong><br />
<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgMdcr94ssEmtYD8YgGZ_0zF8uAdsmtpiLePLm1bhTr-nt54qHWrqAsI4VVNr61tynsrtr-5a008qm6PsMNfnniJKc7IMhRQGBOzuuLgzA_HicW11VawAWH0FhWatPaZxP21hh64R4g8Opx/s1600/New+Bitmap+Image+(1).bmp"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5483027232619288706" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgMdcr94ssEmtYD8YgGZ_0zF8uAdsmtpiLePLm1bhTr-nt54qHWrqAsI4VVNr61tynsrtr-5a008qm6PsMNfnniJKc7IMhRQGBOzuuLgzA_HicW11VawAWH0FhWatPaZxP21hh64R4g8Opx/s400/New+Bitmap+Image+(1).bmp" style="cursor: hand; height: 298px; width: 400px;" /></a><br />
<br />
<strong><span style="color: #3366ff;">Defining some variables in the package</span></strong>One of the most important part of a SSIS package are the variables. Because some variables will be defined in the Configuration File(s), so that you can use the package dynamically,<br />
Example 1: The server name on your computer is different that the server name on production server so basically you can have the “server name variable” defined in the XML or table configuration so that it can be changes when deploying the package from one server to another.<br />
<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEii74tjsMkBPLQBsFBO_0rf_nqTimsr8uoBgjbKw3IHSXyB8_VXBkmtFZ3sxjprbmAaKqSUodERzNxrWnoMunFLBAay5TxTmaZfIb_zN4TGAWKcWjeqUKiF8K4dh6jtDXaTHm-FS6LOsDNk/s1600/New+Bitmap+Image+(2).bmp"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5483027234811085426" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEii74tjsMkBPLQBsFBO_0rf_nqTimsr8uoBgjbKw3IHSXyB8_VXBkmtFZ3sxjprbmAaKqSUodERzNxrWnoMunFLBAay5TxTmaZfIb_zN4TGAWKcWjeqUKiF8K4dh6jtDXaTHm-FS6LOsDNk/s400/New+Bitmap+Image+(2).bmp" style="cursor: hand; height: 137px; width: 400px;" /></a><br />
<br />
1. “<strong>uVar_BackupActiveFolder</strong>” Each time the package runs it makes a folder in the backup folder this folder will be known as the current backup folder which will be used to save the CSV backup files and/or maybe the Error text file.<br />
2. “<strong>uVar_BackupMainFolderPath</strong>” Some customers prefer to have the backup folder in another server so basically we allow them to have a choice, by adding this variable (See the script section for more details). If this variable is empty it will make a default backup folder name (See backup scripts).<br />
3. “<strong>uVar_DestinationDBName</strong>” The name of the Destination Database.<br />
4. “<strong>uVar_DestinationServerName</strong>” The name of the Destination SQL Server.<br />
5. “<strong>uVar_DestinationTableName</strong>” The name of the Destination Table in SQL Server.<br />
6. “<strong>uVar_ErrorOutPutFile</strong>” This is the path and file name of the Error file, each time this path and file name changes so that we can keep a backup of each error log file.<br />
7. “<strong>uVar_FolderNameOfFilesToBeProcessed</strong>”<br />
8. “<strong>uVar_MainFolderPath</strong>” This is the path where you will be having your main ETL; it can contain the Package, configuration files, backup, sample folder, ToBeProcessed folder and etc... (C:\SSIS\LoopingThroughCSVFiles)<br />
9. “<strong>uVar_NumberOfExtractedRows</strong>” A row counter that won’t be used in this example.<br />
10. “<strong>uVar_SMTPEmailServerName</strong>” The SMTP name or IP address of it<br />
(e.g. 1: Neptune e.g. 2: mars.com e.g. 3: 10.2.10.56)<br />
11. “<strong>uVar_SMTPSendEmailsTo_FromError</strong>” A List of email address to email the error file. (Snikkhah@Live.ca ; YourEmail@YourBox.Com )<br />
12. “<strong>uVar_SourceConStr_DataSource</strong>” A variable that will have the file name and folder (See the loop property).<br />
13. “<strong>uVar_SourceFileExtention</strong>” This variable will define the file extension (e.g. *.csv).<br />
14. “<strong>uVar_SourceFileNameRenamed</strong>” Before moving a file to the backup folder we will have to rename and set the right backup folder in this variable.<br />
<br />
<strong><span style="color: #3366ff;">Defining Connection Manager</span></strong>We need 4 connections, in this example I will not be setting the error and email section you can check my blog for that<br />
1. CSV Flat File Connection Manager<br />
The setting in this section depend on the format of the source CSV file, you may have a semicolon (;) as a row delimiter or a tab as a column delimiter, every format has its own settings. In my case the settings are...<br />
<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjf0FwUjCnoHydAKc7BnMti4jS-OWLkTsetrKOG7zOUASWHYYdIXv6vZTXIW-OTaOq3cWjLpVD8XYLpXYttsGqw72gJYkDsVeqNNC2WYKmGoP5ly1ej7HuvTLtG78XZN49dk_woNA7M31G6/s1600/New+Bitmap+Image+(3).bmp"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5483027246492101122" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjf0FwUjCnoHydAKc7BnMti4jS-OWLkTsetrKOG7zOUASWHYYdIXv6vZTXIW-OTaOq3cWjLpVD8XYLpXYttsGqw72gJYkDsVeqNNC2WYKmGoP5ly1ej7HuvTLtG78XZN49dk_woNA7M31G6/s400/New+Bitmap+Image+(3).bmp" style="cursor: hand; height: 172px; width: 400px;" /></a><br />
<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj6u6g-gMxhlYG42Ow8ajpiPDc1afoSilPp_cfoL4AaRcNoBMHC-JQSa8zsvoXmg6G4dPF_H9K8UGuWCUjqOxciHhOmxMk6G0IWfykhPo9reeSj6RVm832ujvh4xNoyGWWtsNdt5NiSLMR4/s1600/New+Bitmap+Image+(4).bmp"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5483027250525141330" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj6u6g-gMxhlYG42Ow8ajpiPDc1afoSilPp_cfoL4AaRcNoBMHC-JQSa8zsvoXmg6G4dPF_H9K8UGuWCUjqOxciHhOmxMk6G0IWfykhPo9reeSj6RVm832ujvh4xNoyGWWtsNdt5NiSLMR4/s400/New+Bitmap+Image+(4).bmp" style="cursor: hand; height: 97px; width: 400px;" /></a><br />
<br />
2. FF_DST----Error Output file for debugging<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiWyO8sBlSCQfoGGv82HWkR_Kw8wYUQV4Dp_vwyb6DagmjzI1kgC5KvBbug9PiCk0f3-sl4Fu7uqphoz-XsDPJxg6g1zJZLYFBP805LFNd8BKoLVf-OHP59lnLOwDk9fwaeOT4eM8lOy8c3/s1600/New+Bitmap+Image+(5).bmp"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5483027255039244050" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiWyO8sBlSCQfoGGv82HWkR_Kw8wYUQV4Dp_vwyb6DagmjzI1kgC5KvBbug9PiCk0f3-sl4Fu7uqphoz-XsDPJxg6g1zJZLYFBP805LFNd8BKoLVf-OHP59lnLOwDk9fwaeOT4eM8lOy8c3/s400/New+Bitmap+Image+(5).bmp" style="cursor: hand; height: 214px; width: 400px;" /></a><br />
3. SQLServerDestinationConnection<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhQvWouUTAVBe5cB0cXcnONsO88ptdd7gLDDh0Ko3vGPDLArV5aOe-of6i9sb0u_ehOypFZtItWEf-kZMv2A9Iv5PH9tQ0VuxEaUhl1y7i9MPYgGbakPVkbB8wtyaZUTqwk-iNYT-RwrXH3/s1600/New+Bitmap+Image+(6).bmp"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5483029375933660066" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhQvWouUTAVBe5cB0cXcnONsO88ptdd7gLDDh0Ko3vGPDLArV5aOe-of6i9sb0u_ehOypFZtItWEf-kZMv2A9Iv5PH9tQ0VuxEaUhl1y7i9MPYgGbakPVkbB8wtyaZUTqwk-iNYT-RwrXH3/s400/New+Bitmap+Image+(6).bmp" style="cursor: hand; height: 347px; width: 400px;" /></a><br />
<br />
4. SMTP Connection Manager For Error messages<br />
<br />
<strong><span style="color: #3366ff;">Adding Object (controls) in the Control Flow </span></strong><br />
<br />
Now we can add the objects as seen in the “<em>Over all View</em>”<br />
1. SCR---- Making the "ToBeProcessed Folder"<br />
<div>
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh12LGWwUbt5ua1u7XEMuuiq88ACJlvLdSgjnNCrJqV5eAiPtWlT-erhYphilKdv9TblxBVdHEIKdOT8WDLyfIJTL4DRHkJ8je0tUtBD9EbstR0qIp52pVQ5XoIrcRZpDoB8NpesJLrGOfN/s1600/New+Bitmap+Image+(7).bmp"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5483030133613284690" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh12LGWwUbt5ua1u7XEMuuiq88ACJlvLdSgjnNCrJqV5eAiPtWlT-erhYphilKdv9TblxBVdHEIKdOT8WDLyfIJTL4DRHkJ8je0tUtBD9EbstR0qIp52pVQ5XoIrcRZpDoB8NpesJLrGOfN/s400/New+Bitmap+Image+(7).bmp" style="cursor: hand; height: 138px; width: 400px;" /></a><br />
<br />
<div>
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg58qYkKaCYlC2Q1cJxsDXKA4Ytw2PVZx9-jTiolbumpH5Foyuxi5ajzaOiZFjHKwD-qR9_lTLLg3K2uvbkn0AsOyIsYidnPHjdrbl9shi_VkWTL_O9ZtxiuKzQqbNU6c4TJ4K9Cx6OM6Aw/s1600/New+Bitmap+Image+(8).bmp"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5483030130854072882" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg58qYkKaCYlC2Q1cJxsDXKA4Ytw2PVZx9-jTiolbumpH5Foyuxi5ajzaOiZFjHKwD-qR9_lTLLg3K2uvbkn0AsOyIsYidnPHjdrbl9shi_VkWTL_O9ZtxiuKzQqbNU6c4TJ4K9Cx6OM6Aw/s400/New+Bitmap+Image+(8).bmp" style="cursor: hand; height: 264px; width: 400px;" /></a></div>
<div>
</div>
<div>
</div>
<div>
In this section the customer can redirect the source folder by entering the folder full path name in the “uVar_FolderNameOfFilesToBeProcessed” variable, if not the system will pick up the default name “ToBeProcessed”.<br />
<br />
2. SCR---- Making the backup folder string from BackupFolderName Variable (Dynamic Backup folder yyyy-mm-dd-hh-mm-ss) </div>
<div>
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEivhz5_h8KtDocv13cU0WLAIGyCRmpC80a5ilYY-9MWM9_4rv2rjqkF71nme7Noi8xJmvmmsWzPGvBoucAPsKnR7Vn081YKb_IlCowzQUy5_w12aRyo66kCmwBwc97bihVuJA6mFtbn4WMQ/s1600/New+Bitmap+Image+(9).bmp"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5483030352696165394" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEivhz5_h8KtDocv13cU0WLAIGyCRmpC80a5ilYY-9MWM9_4rv2rjqkF71nme7Noi8xJmvmmsWzPGvBoucAPsKnR7Vn081YKb_IlCowzQUy5_w12aRyo66kCmwBwc97bihVuJA6mFtbn4WMQ/s400/New+Bitmap+Image+(9).bmp" style="cursor: hand; height: 121px; width: 400px;" /></a><br />
<br />
<div>
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh1MWFkCYbLJ9AGhCMTboIr3gaNCTVV8BOjLh8CJRAUFB_hgcAEtVRzTfYcCZPjtXcNoQpF9BJ0TLRnAt0KSeWJ42SunKVn3ZMAqDacA4wM40nj2gYjeXQxEAkBlua_CxpoMJjURIm3oYEN/s1600/New+Bitmap+Image+(10).bmp"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5483030349037804898" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh1MWFkCYbLJ9AGhCMTboIr3gaNCTVV8BOjLh8CJRAUFB_hgcAEtVRzTfYcCZPjtXcNoQpF9BJ0TLRnAt0KSeWJ42SunKVn3ZMAqDacA4wM40nj2gYjeXQxEAkBlua_CxpoMJjURIm3oYEN/s400/New+Bitmap+Image+(10).bmp" style="cursor: hand; height: 355px; width: 400px;" /></a><br />
<br />
Same settings again the customer can define the backup folder in the variable “uVar_BackupMainFolderPath”, if not the Package will pick the default folder name “BackUpFolder”. The other thing that happens in this script is that the current backup folder name string is been made, e.g. <em><span style="font-size: 85%;">C:\SSIS\LoopingThroughCSVFiles\BackUp\2010-04\2010-04-10-141000.</span></em><br />
3. FSYS----Creating the backup folder from BackupFolderName<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiHik1Y-iXoyZyeqon6jBUxfRy1_CSpeT8BTq2uSzzC-_9KtcxbJEL1xPapEi9-fn3CPoL0yeYgXP0qvoRy2N7B0LwBYZbIkHqefK21eeOoujHRSVp42Y_ReZpvZphvF0oBAtmXVDezxtcS/s1600/New+Bitmap+Image+(11).bmp"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5483030890915269730" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiHik1Y-iXoyZyeqon6jBUxfRy1_CSpeT8BTq2uSzzC-_9KtcxbJEL1xPapEi9-fn3CPoL0yeYgXP0qvoRy2N7B0LwBYZbIkHqefK21eeOoujHRSVp42Y_ReZpvZphvF0oBAtmXVDezxtcS/s400/New+Bitmap+Image+(11).bmp" style="cursor: hand; height: 162px; width: 400px;" /></a><br />
<br />
This will make the backup folder from the “uVar_BackupActiveFolder” which will be <em><span style="color: black; font-size: 85%;">C:\SSIS\LoopingThroughCSVFiles\BackUp\2010-04\2010-04-10-141000.</span></em><br />
<br />
4. SCR--- Make the correct path and file name for the Error Text File<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgdZxu-TLur7k4b3Cptrp58LqEntZWB2TP7pIc_7EHJWE6Z0JDc80yq8EFea-timN7Xd7H_2ev9OZWMpuHcjr3w_MuIaFwep4gj17NMgD8nzvY5LN19lMhpm0ZrJCXInAJQccudld0f-gAo/s1600/New+Bitmap+Image+(12).bmp"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5483030885518732850" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgdZxu-TLur7k4b3Cptrp58LqEntZWB2TP7pIc_7EHJWE6Z0JDc80yq8EFea-timN7Xd7H_2ev9OZWMpuHcjr3w_MuIaFwep4gj17NMgD8nzvY5LN19lMhpm0ZrJCXInAJQccudld0f-gAo/s400/New+Bitmap+Image+(12).bmp" style="cursor: hand; height: 118px; width: 400px;" /></a><br />
<br />
<div>
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj-nL0DMh_NZIXVTktecqUyvtpCN0S2IzuLi5ocqgqXVjhL9PiEVYWa-oKjaQllHN89pBNzwKWNLNksJp4OLcroc1tX7sE1LjXmKh-6Ks3XqkBkZNZD8V7oXDU05avS6dz1nNtGbZcnAjKA/s1600/New+Bitmap+Image+(13).bmp"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5483030884227963682" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj-nL0DMh_NZIXVTktecqUyvtpCN0S2IzuLi5ocqgqXVjhL9PiEVYWa-oKjaQllHN89pBNzwKWNLNksJp4OLcroc1tX7sE1LjXmKh-6Ks3XqkBkZNZD8V7oXDU05avS6dz1nNtGbZcnAjKA/s400/New+Bitmap+Image+(13).bmp" style="cursor: hand; height: 205px; width: 400px;" /></a><br />
<br />
What I am doing is making a file name string that has, the date and time, ETL Name, etc... This will be needed it an error accurse<br />
<br />
5. SQL----Truncate the destination Table<br />
<div>
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjBIG7EFFMHGu7zwyh9DmGhyPnWlWp1y26wtHscuCtlxKu9o2GpdNh5Jisv5r4F-rvQUMlkdmrv7bjreBzCq6RIx6JiPiHgWhk40YUVNcx0tLg94_Yi344uwtf55CzEtj1VLWJYYHCg5O1Y/s1600/New+Bitmap+Image+(14).bmp"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5483030879507174978" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjBIG7EFFMHGu7zwyh9DmGhyPnWlWp1y26wtHscuCtlxKu9o2GpdNh5Jisv5r4F-rvQUMlkdmrv7bjreBzCq6RIx6JiPiHgWhk40YUVNcx0tLg94_Yi344uwtf55CzEtj1VLWJYYHCg5O1Y/s400/New+Bitmap+Image+(14).bmp" style="cursor: hand; height: 175px; width: 400px;" /></a><br />
<br />
6. FLC - Looping through each CSV file - Finding the Data Source<br />
<div>
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjpssLg3t98GKQgEbgD3YAYZyiGBq0JDbQvysMNIgC0eg8B8ee3l43DMosmY-SOrWrhu9_Znus_fSGXKzSe3Lr-LyeEs4cOHw3oEBplOZAaQYUCfLZfKZjwnxw0TwbTwwoOpNQwM1ncYL2A/s1600/New+Bitmap+Image+(15).bmp"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5483030872413670402" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjpssLg3t98GKQgEbgD3YAYZyiGBq0JDbQvysMNIgC0eg8B8ee3l43DMosmY-SOrWrhu9_Znus_fSGXKzSe3Lr-LyeEs4cOHw3oEBplOZAaQYUCfLZfKZjwnxw0TwbTwwoOpNQwM1ncYL2A/s400/New+Bitmap+Image+(15).bmp" style="cursor: hand; height: 400px; width: 343px;" /></a><br />
<br />
By using the variable “uVar_FolderNameOfFilesToBeProcessed” the loop will be loop through the folder and sub folder to find *.csv files “name and folder” and each file folder and name will be saved in the “uVar_SourceConStr_DataSource” e.g. <em><span style="font-size: 85%;">C:\SSIS\LoopingThroughCSVFiles\ToBeProcessed\AnyCSVFileName.csv</span></em><br />
<br />
7. DFT---- MAIN ETL (E and T Only)<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEji327Ugtm5ezd23i-k_fJfnb8Fqpr-FVobjl5lgSbZNEJXgOWPx08OhPzESgOGCefQuODfvdkTsKsOCjZCcaa-IXH-K2L-NpBaW-aMY-GsTDklOVUqaCQn2axtTMiSkOglQVuo4DllEhhk/s1600/New+Bitmap+Image+(20).bmp"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5483043765632524626" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEji327Ugtm5ezd23i-k_fJfnb8Fqpr-FVobjl5lgSbZNEJXgOWPx08OhPzESgOGCefQuODfvdkTsKsOCjZCcaa-IXH-K2L-NpBaW-aMY-GsTDklOVUqaCQn2axtTMiSkOglQVuo4DllEhhk/s400/New+Bitmap+Image+(20).bmp" style="cursor: hand; height: 179px; width: 400px;" /></a><br />
<br />
8. FF_SRC---- Connecting to CSV file to extract Data<br />
<br />
Setting the right connection object for the Flat File data source object<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEglD_nyNuLlHQTuoGAVQ66_zEDe9MNAGF6e-yzq7A0wnfSaZSKziAmA4ejwMwxwoCLL3x7NZ5MCgXNmnXXBiz9MznHd1VGarddi4u2d_FmfQRkRSa3Vc3Li0qwKu-TzeQV46rqfZbPWA69C/s1600/New+Bitmap+Image+(21).bmp"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5483035155392171570" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEglD_nyNuLlHQTuoGAVQ66_zEDe9MNAGF6e-yzq7A0wnfSaZSKziAmA4ejwMwxwoCLL3x7NZ5MCgXNmnXXBiz9MznHd1VGarddi4u2d_FmfQRkRSa3Vc3Li0qwKu-TzeQV46rqfZbPWA69C/s400/New+Bitmap+Image+(21).bmp" style="cursor: hand; height: 379px; width: 400px;" /></a><br />
<br />
9. OLE_DST----SQL SERVER Destination table<br />
<br />
Setting the destination table in SQL server for the OLE destination object.<br />
<div>
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhIfEm0PFXrH7RHU6GsZH1xEqcsAdjJwm7iMnnhCTJWxHd9zANCd0OFt3MKK_ruD-3Odor7nWiGUFiBUcwqjh14NiRMhOgPjiehrZRqoY1UKWkCGR6TX52qF9L-v_l7Jsb1qQNo1My8xPGa/s1600/New+Bitmap+Image+(22).bmp"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5483035151249751794" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhIfEm0PFXrH7RHU6GsZH1xEqcsAdjJwm7iMnnhCTJWxHd9zANCd0OFt3MKK_ruD-3Odor7nWiGUFiBUcwqjh14NiRMhOgPjiehrZRqoY1UKWkCGR6TX52qF9L-v_l7Jsb1qQNo1My8xPGa/s400/New+Bitmap+Image+(22).bmp" style="cursor: hand; height: 124px; width: 400px;" /></a><br />
<br />
10. SCR--- Rename file names with the format of "yyyy-mm-dd-hhmmssff"<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEinHPVhtrXS4lvqEwWqg0AavHJ18qAz0it21J6yuhwKZidTvTvJEYdTlkLPO-JKvq-syA3LNsXPRJMxiwsF2tNYFwBNkv0V-nsyZj4rmtFmkfUUvDlyXU6yefFyge9A6cpxn76wPIJjj-uc/s1600/New+Bitmap+Image+(23).bmp"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5483035144323722434" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEinHPVhtrXS4lvqEwWqg0AavHJ18qAz0it21J6yuhwKZidTvTvJEYdTlkLPO-JKvq-syA3LNsXPRJMxiwsF2tNYFwBNkv0V-nsyZj4rmtFmkfUUvDlyXU6yefFyge9A6cpxn76wPIJjj-uc/s400/New+Bitmap+Image+(23).bmp" style="cursor: hand; height: 102px; width: 400px;" /></a><br />
<br />
Making a string that will rename the current file that has been processed and save it in the current backup folder<br />
<div>
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi9su0jYhxW3ssXOJxYYzJPr6ndDDIXw44UZYpev9vltVtMO8w-5AbrhjyFjS66gg4EwMlmmWcPDK_TUdUPp5JMTwTiZBgrUGyeqo4niXfRBXF2s5F29KY47hBJJ9ucw-PvP4sLDHN60xst/s1600/New+Bitmap+Image+(24).bmp"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5483035136826767810" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi9su0jYhxW3ssXOJxYYzJPr6ndDDIXw44UZYpev9vltVtMO8w-5AbrhjyFjS66gg4EwMlmmWcPDK_TUdUPp5JMTwTiZBgrUGyeqo4niXfRBXF2s5F29KY47hBJJ9ucw-PvP4sLDHN60xst/s400/New+Bitmap+Image+(24).bmp" style="cursor: hand; height: 400px; width: 338px;" /></a><br />
<br />
11. FSYS---- Moving processed files to the "Backup Active Folder"<br />
<div>
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgrEm1bXRwaDgHF2zhghrWfzppCElT_TuIifuYWgIWRCQtHNKBw0dpNzRA6CBlkMmv0bPX-lybwPKKpvXe9Fqjm8xIKWa65dyyFQs4kc8zBdd6ZUAwcZHjWt-1li9TVEGenxxezed1BdLdE/s1600/New+Bitmap+Image+(25).bmp"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5483035133831610962" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgrEm1bXRwaDgHF2zhghrWfzppCElT_TuIifuYWgIWRCQtHNKBw0dpNzRA6CBlkMmv0bPX-lybwPKKpvXe9Fqjm8xIKWa65dyyFQs4kc8zBdd6ZUAwcZHjWt-1li9TVEGenxxezed1BdLdE/s400/New+Bitmap+Image+(25).bmp" style="cursor: hand; height: 156px; width: 400px;" /></a><br />
Moving the processed file to that final backup folder<br />
<br />
12. End of Loop<br />
<br />
<strong><span style="color: #3366ff;">Testing Scenarios </span></strong><br />
<br />
Before testing make sure that you have set the variables according to the snap shot of the variables that I have and check the “Criteria”.<br />
<br />
Scenario 1:<br />
1- Copy CSV files to the “ToBeProcessed” folder<br />
2- Run the package<br />
3- Check the destination table<br />
4- Check the backup folder and find the current date time folder and see if the file have been moved from the “ToBeProcessed” folder to the new current backup folder </div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
<br />
<br />
<br />
<br /></div>
Shahriar Nikkhah (Nik)http://www.blogger.com/profile/17215555681462843010noreply@blogger.com12tag:blogger.com,1999:blog-5534374197067462925.post-349806593418864672010-04-28T15:09:00.061-04:002010-06-15T11:26:07.341-04:00Emailing work log notification to individual users using SSIS<span style="color:#33ccff;"><span style="color:#3366ff;">Emailing work log notification to individual users by using SSIS</span> </span><br />Another project that I had worked on was to send emails with attached excel work log files to individual users; I used the same package with some modification to send excel Management MIS reports to CEOs & managers, the Excel file contained pie charts and etc...<br /><br /><span style="color:#3366ff;">Criteria<br /></span>1. A sample Excel (97 – 2003) file with one sheet named “Report”<br />2. In the “Report” sheet the field names that need to be reported to the user must be on the first row, e.g.<br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgiKOcg9_DCrl4yYF30jisvkD-dsfrewfcKiP1WJ0lAiGH3r2DjyVOfwQ0BgHhq6nK8azwtqGwifsmdpIchVFRX637pYo8YkghhBP3ENNLBX9UM5G1mSP8bE1UbTYpOSdle83TFr-rANZlU/s1600/New+Bitmap+Image+(2).bmp"><img style="WIDTH: 371px; HEIGHT: 50px; CURSOR: hand" id="BLOGGER_PHOTO_ID_5465274635085595634" border="0" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgiKOcg9_DCrl4yYF30jisvkD-dsfrewfcKiP1WJ0lAiGH3r2DjyVOfwQ0BgHhq6nK8azwtqGwifsmdpIchVFRX637pYo8YkghhBP3ENNLBX9UM5G1mSP8bE1UbTYpOSdle83TFr-rANZlU/s400/New+Bitmap+Image+(2).bmp" /></a><br /><br /><p>3. The list of the fields will be used in the SSIS package; in this post I will be only using one field (UserID).<br />4. The Excel file name must be renamed to “SampleExcelFile.xls”<br />5. Make a main root folder e.g. “C:\SSIS\Email to list with attached Excel File”<br />6. Make two other sub folders 1- “Sample” 2- “ToBeEmailed”<br />7. Copy the Excel Sample File in the “Sample” folder<br /></p><br /><p><span style="color:#3366ff;">Note(s)</span><br />1. The attached file format will be in *.xls format (Excel (97 – 2003))<br /><span style="color:#3366ff;"></span></p><p><span style="color:#3366ff;">Over all View</span></p><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgfPKW-i3QsMVPVD3BKYU-xVDWcdRq6EfJ7LeAcrL4CWRQO_SM_QGOay4YbOPPk-4BQGSfFDTeBCU_AibO3zjv5S0SpOY5Z3CIVwSz2lNDSzZuLjJmx5PLt0zi-t-_81R85URcVh6sw4ryH/s1600/New+Bitmap+Image+(3).bmp"><img style="WIDTH: 400px; HEIGHT: 349px; CURSOR: hand" id="BLOGGER_PHOTO_ID_5465274627488544450" border="0" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgfPKW-i3QsMVPVD3BKYU-xVDWcdRq6EfJ7LeAcrL4CWRQO_SM_QGOay4YbOPPk-4BQGSfFDTeBCU_AibO3zjv5S0SpOY5Z3CIVwSz2lNDSzZuLjJmx5PLt0zi-t-_81R85URcVh6sw4ryH/s400/New+Bitmap+Image+(3).bmp" /></a><br /><br /><span style="color:#3366ff;">Defining some variables in the package<br /></span>One of the most important part of a SSIS package are the variables, because some variables will be defined in the SSIS Configuration File(s) and/or Table(s), so that the package can be used dynamically.<br />Example: The server name on your computer is different that the server name on production server so basically you can have the “server name variable” defined in the SSIS configuration XML file or table so that it can be changes when deploying the package from one server to another.<br /><br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhtUvKQtGbOYkSKZ6LP4RD-1VHReYnKm198j8p3fb6asqvphCf065dZrXfytEBKbkwDSkY8PxW1uF4qT8BYmEFPhh7laRvPVHWv6ytAVd5aTYPVs51yyYuwqmM5KIXJhKA6m3pS5gpeslAz/s1600/New+Bitmap+Image+(4).bmp"><img style="WIDTH: 400px; HEIGHT: 211px; CURSOR: hand" id="BLOGGER_PHOTO_ID_5465274625417253170" border="0" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhtUvKQtGbOYkSKZ6LP4RD-1VHReYnKm198j8p3fb6asqvphCf065dZrXfytEBKbkwDSkY8PxW1uF4qT8BYmEFPhh7laRvPVHWv6ytAVd5aTYPVs51yyYuwqmM5KIXJhKA6m3pS5gpeslAz/s400/New+Bitmap+Image+(4).bmp" /></a><br /><br />1. “<strong>uVar_BackupActiveFolder</strong>” Each time the package runs it makes a backup folder known as the current backup folder which will be used to save the Excel backup files and/or maybe the Error text file.<br />2. “<strong>uVar_BackupMainFolderPath</strong>” Some customers prefer to have the backup folder in another server so basically we allow them to redirect the folders location by using this variable. If this variable is empty it will make a default backup folder name (See backup scripts).<br />3. “<strong>uVar_BackupUserFolder</strong>” once the variable “uVar_BackupActiveFolder” is defined, the package will make subfolders with the name of the users, so that it will be easier to trace what was reported on that day and for whom.<br />4. “<strong>uVar_DestinationFileNameRenamed</strong>” To move the excel file to the backup folder we need to rename it.<br />5. “<strong>uVar_ListOfUsers</strong>” A SSIS variable to save the list of users that needs to be notified.<br />6. “<strong>uVar_MainFolderPath</strong>” This is the path where you will be having your main ETL folder root; it can contain the Package, configuration files, backup, sample folder, ToBeProcessed folder and etc... (C:\SSIS\ETLName)<br />7. “<strong>uVar_Source_SQLQueryString</strong>” A SQL statement that provides the work log of each user, The TOP statement is been used for design time only, the script string will change in the package loop (check object=> “SCR--- Make the WHERE condition of the Source table”).<br />8. “<strong>uVar_SourceSampleExcelFile</strong>” the location of the Excel sample file.<br />9. “<strong>uVar_ToBeEmailedFile</strong>” The file that will be emailed individually<br />10. “<strong>uVar_ToBeEmailedFolder</strong>” The folder that has the file that needs to be emailed.<br />11. “<strong>uVar_UserEmail</strong>” The email of the current user in the Loop.<br />12. “<strong>uVar_UserID</strong>” The current user ID in the Loop.<br />13. “<strong>uVar_UserName</strong>” The current user name in the Loop.<br /><br /><span style="color:#33ffff;"><span style="color:#3366ff;">Defining Connection Manager</span><br /></span>I am going to define only one connection in this example I am sure you can set the rest of them yourself.<br /><br />1. Excel connection manager<br /><br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiRr3tTk4eqCrkxgFcFSm4Rer7QaHM_bAe5kw9_CvrvQVLI4LnXIf5s0QGe7i9gwxudnG0l1HGN9eG11bYbWCEJOQZm-78N7pP6iUIcpAlVCKUGI-YBLn33PGS1qO4XzZ1lGNaYKXuoq6pm/s1600/New+Bitmap+Image+(5).bmp"><img style="WIDTH: 400px; HEIGHT: 374px; CURSOR: hand" id="BLOGGER_PHOTO_ID_5465274620030244114" border="0" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiRr3tTk4eqCrkxgFcFSm4Rer7QaHM_bAe5kw9_CvrvQVLI4LnXIf5s0QGe7i9gwxudnG0l1HGN9eG11bYbWCEJOQZm-78N7pP6iUIcpAlVCKUGI-YBLn33PGS1qO4XzZ1lGNaYKXuoq6pm/s400/New+Bitmap+Image+(5).bmp" /></a><br /><br />Set the “<em>DelayValidation</em>” to TRUE and enter the right the connection string e.g.<br /><em><span style="font-size:85%;">Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\SSIS\Email to list with attached Excel File\Sample\SampleExcelFile.xls;Extended Properties="EXCEL 8.0;HDR=YES";<br /></span></em><br />2. SMTP Connection Manager<br /><br />3. SQLServerSourceConnection<br /><br />You can add the Error handling features in the package.<br /><br /><span style="color:#3366ff;">Adding Object (controls) in the Control Flow</span><br />Now we can add the objects as seen in the “<em>Over all View</em>”<br />1. SCR---- Making the backup folder string from BackupFolderName Variable (Dynamic Backup folder yyyy-mm-dd-hh-mm-ss)<br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhKBUcb1TtLkU6AcML6rh0dmQDrJXilepK_Ob2kJxRLRu730sVw0_EqmZRIYUGJFEmGeBG-JPx878up3WZIfryFKoif78mXHehjh_IxQzPg3e1jkzZQMf1AFZpCEUhfWwRMcRNGBo7y-8J2/s1600/New+Bitmap+Image+(6).bmp"><img style="WIDTH: 400px; HEIGHT: 125px; CURSOR: hand" id="BLOGGER_PHOTO_ID_5465274335483995394" border="0" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhKBUcb1TtLkU6AcML6rh0dmQDrJXilepK_Ob2kJxRLRu730sVw0_EqmZRIYUGJFEmGeBG-JPx878up3WZIfryFKoif78mXHehjh_IxQzPg3e1jkzZQMf1AFZpCEUhfWwRMcRNGBo7y-8J2/s400/New+Bitmap+Image+(6).bmp" /></a><br /><br />Redirect the backup folder in the “uVar_BackupMainFolderPath” variable; if the variable is empty the system will pick up the default folder name “BackUpFolder” in the main folder path of “uVar_MainFolderPath”. E.g.<br /><em><span style="font-size:85%;">C:\SSIS\Email ..................Excel File\BackUpFolder\2010-04\2010-04-27-103108</span></em>.<br /><br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgvocxZXGB5VeL8kTfmvZSJiGKZs7zjG920Sw9ZizCBfgBtLIEM-dKLqx59baYdXihyphenhyphen_Um0_0JxnztJPYFsr0V5hmD1fGg5g4I56Pvji-_SpgIOmSTCv_mrv7VDU3KF5PAuxgRNboBMSpIu/s1600/New+Bitmap+Image+(7).bmp"><img style="WIDTH: 400px; HEIGHT: 276px; CURSOR: hand" id="BLOGGER_PHOTO_ID_5465274326755235602" border="0" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgvocxZXGB5VeL8kTfmvZSJiGKZs7zjG920Sw9ZizCBfgBtLIEM-dKLqx59baYdXihyphenhyphen_Um0_0JxnztJPYFsr0V5hmD1fGg5g4I56Pvji-_SpgIOmSTCv_mrv7VDU3KF5PAuxgRNboBMSpIu/s400/New+Bitmap+Image+(7).bmp" /></a><br /><br />2. FSYS----Creating the backup folder from BackupFolderName<br />Making the backup active folder from the “uVar_BackupActiveFolder” variable. <em><span style="font-size:85%;">C:\SSIS\Email ..................Excel File\BackUpFolder\2010-04\2010-04-27-103108.</span></em><br /><br /><div><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh2Q88NAncYKJeuO-D2tggZXP1629lH91SxE9TBicGmiKwFB0e-uQBiW55Zi8WEt0kt_PeDfJfEhra7AxEGVZu9pojIjoE1_HsPJA8T01KIJ3B3jxYJ0pEorvCh09NhTF0SyQuj_dx35BXv/s1600/New+Bitmap+Image+(8).bmp"><img style="WIDTH: 400px; HEIGHT: 192px; CURSOR: hand" id="BLOGGER_PHOTO_ID_5465274322040751026" border="0" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh2Q88NAncYKJeuO-D2tggZXP1629lH91SxE9TBicGmiKwFB0e-uQBiW55Zi8WEt0kt_PeDfJfEhra7AxEGVZu9pojIjoE1_HsPJA8T01KIJ3B3jxYJ0pEorvCh09NhTF0SyQuj_dx35BXv/s400/New+Bitmap+Image+(8).bmp" /></a><br /><br />3. FSYS----Creating the ToBeEmailed Folder<br />Creating the folder. <em><span style="font-size:85%;">C:\SSIS\Email ..................Excel File\ ToBeEmailed.<br /></span></em><br /><div><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjdkAd4hxc9XnwDyXKQpnFC6Mj92dCG-mv_JCqFeXwLH787DC08pCGPauZ2NoEEqqRwxM-kVtj0UcL4329qe7YYJtntwMvHxnq7gtOALhbW6SWbXGmyXmIW7D82-z97ZSPSaf8yc2oK-grG/s1600/New+Bitmap+Image+(9).bmp"><img style="WIDTH: 400px; HEIGHT: 195px; CURSOR: hand" id="BLOGGER_PHOTO_ID_5465274319208796370" border="0" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjdkAd4hxc9XnwDyXKQpnFC6Mj92dCG-mv_JCqFeXwLH787DC08pCGPauZ2NoEEqqRwxM-kVtj0UcL4329qe7YYJtntwMvHxnq7gtOALhbW6SWbXGmyXmIW7D82-z97ZSPSaf8yc2oK-grG/s400/New+Bitmap+Image+(9).bmp" /></a><br /><br />4. SCR--- Make ToBeEmailedFile String "Folder and File Name"<br />A “variable pointer” that points to the sample excel file.<br /><br /><div><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhSD6rl18AhG8tYbu7GYRnEun7v0Fn2Z96D9E1ZsjBdhEgh-iU4d-7h5R9OVDOzI2m5sXrQG-s2g4CcOpx6Bp76iTHVPK64Eh9xA-lu2ElIwgciUjdd-UmBMmCpWpu8t5Hh7vfGOxjxy7ft/s1600/New+Bitmap+Image+(10).bmp"><img style="WIDTH: 400px; HEIGHT: 152px; CURSOR: hand" id="BLOGGER_PHOTO_ID_5465274316330746706" border="0" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhSD6rl18AhG8tYbu7GYRnEun7v0Fn2Z96D9E1ZsjBdhEgh-iU4d-7h5R9OVDOzI2m5sXrQG-s2g4CcOpx6Bp76iTHVPK64Eh9xA-lu2ElIwgciUjdd-UmBMmCpWpu8t5Hh7vfGOxjxy7ft/s400/New+Bitmap+Image+(10).bmp" /></a><br /><br /><div><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiceA1FhEe7cjoid0Vj2TNlPKJolLjg_vhZ3rz4u-CB6BVVNuKDhm8BD3XYLPYfNSVBlqwqtda7ty6numJTxnkO5obH_-pYWQjtL_OKwoQPJn1IwQoikW0o_tjENdmQucEstnYdk-ZR87tz/s1600/New+Bitmap+Image+(11).bmp"><img style="WIDTH: 400px; HEIGHT: 240px; CURSOR: hand" id="BLOGGER_PHOTO_ID_5465273812287135842" border="0" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiceA1FhEe7cjoid0Vj2TNlPKJolLjg_vhZ3rz4u-CB6BVVNuKDhm8BD3XYLPYfNSVBlqwqtda7ty6numJTxnkO5obH_-pYWQjtL_OKwoQPJn1IwQoikW0o_tjENdmQucEstnYdk-ZR87tz/s400/New+Bitmap+Image+(11).bmp" /></a><br /><br />5. DFT --- Find the list of users to Email to<br />We now need to get the list of users that need to be notified about their daily work list report, mainly we need the fields of the UserID, UserName and email, <u><em>the number of fields</em></u> and <u>the <em>order of the fields</em></u> are very important (See: Foreach Loop Container).<br />The DFT looks like...<br /><br /><div><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEifPsVEG6d1gcINf58TG07PeFoDV0JaaHpROU9Jze2Vl8rpI7fHFX0z50LouxmzTFeQrpK4kDhQOoWm0EMmUBpELhMl3NjLulRB3oXWEQwNB_c-OT8xuBqgSY2L8ioPL5kfoY7SWBHn86pC/s1600/New+Bitmap+Image+(12).bmp"><img style="WIDTH: 400px; HEIGHT: 243px; CURSOR: hand" id="BLOGGER_PHOTO_ID_5465273806018872098" border="0" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEifPsVEG6d1gcINf58TG07PeFoDV0JaaHpROU9Jze2Vl8rpI7fHFX0z50LouxmzTFeQrpK4kDhQOoWm0EMmUBpELhMl3NjLulRB3oXWEQwNB_c-OT8xuBqgSY2L8ioPL5kfoY7SWBHn86pC/s400/New+Bitmap+Image+(12).bmp" /></a><br /><br />Retrieving the list of users from the OLE DB Source object.<br /><br /><div><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjgYLuZT5qwwiFvTtwUKOvHoV3T-I-m9doBHMGXg4HKbIP9FCxIm9tOEI-Kr8o43FgRROJ6k7j_J1xJ6y0yHn9ST0HiTYUeJQGcwqPJlJjfRnr_Ws7iSHNOkyeJsbKGQOjmsySdFT_DXANI/s1600/New+Bitmap+Image+(13).bmp"><img style="WIDTH: 400px; HEIGHT: 239px; CURSOR: hand" id="BLOGGER_PHOTO_ID_5465273797713312034" border="0" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjgYLuZT5qwwiFvTtwUKOvHoV3T-I-m9doBHMGXg4HKbIP9FCxIm9tOEI-Kr8o43FgRROJ6k7j_J1xJ6y0yHn9ST0HiTYUeJQGcwqPJlJjfRnr_Ws7iSHNOkyeJsbKGQOjmsySdFT_DXANI/s400/New+Bitmap+Image+(13).bmp" /></a><br /><br />Mapping fields.<br /><br /><div><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjLFTQhoXmc17o4cP8C899i61DaSxBY1vtZsBC5k_zMERUwp06_JF4FNf9kV8dW7MbZJWZpVHrJRcSmcHPSU1Z4VxgW4ng4AyjqeE6Gk2hC6h_BeOe5z5-aSiv57AWutW0jlmzmlEYVO71G/s1600/New+Bitmap+Image+(14).bmp"><img style="WIDTH: 400px; HEIGHT: 257px; CURSOR: hand" id="BLOGGER_PHOTO_ID_5465273794561757250" border="0" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjLFTQhoXmc17o4cP8C899i61DaSxBY1vtZsBC5k_zMERUwp06_JF4FNf9kV8dW7MbZJWZpVHrJRcSmcHPSU1Z4VxgW4ng4AyjqeE6Gk2hC6h_BeOe5z5-aSiv57AWutW0jlmzmlEYVO71G/s400/New+Bitmap+Image+(14).bmp" /></a><br /><br />Saving the list of users in a SSIS variable object, the loop in the package will be looping through this variable user by user<br /><br /><div><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgd8k3_parBpLSO-nvM6Miprac19iJCeVDkKxi2XAEhOdY4kMtOALcbpeIwlJKPXG-7w7HpOXDyc-BW-6Bymveht3CEcMXKmPT_Puo7hKA8IY-UjDsRH0WdFMIuJYzy9DajTWr5e9gVz_Ue/s1600/New+Bitmap+Image+(15).bmp"><img style="WIDTH: 400px; HEIGHT: 285px; CURSOR: hand" id="BLOGGER_PHOTO_ID_5465273321964399250" border="0" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgd8k3_parBpLSO-nvM6Miprac19iJCeVDkKxi2XAEhOdY4kMtOALcbpeIwlJKPXG-7w7HpOXDyc-BW-6Bymveht3CEcMXKmPT_Puo7hKA8IY-UjDsRH0WdFMIuJYzy9DajTWr5e9gVz_Ue/s400/New+Bitmap+Image+(15).bmp" /></a><br /><br />6. Foreach Loop Container<br />What the <em>Loop</em> section dose, is to loop though the object variable “uVar_ListOfUsers” (that has the list of users that needs to be notified) user by user, make an Excel file for each user, make a backup folders and move the excel file to the right backup folder for each user and finally email to the user with the right excel file attached.<br />Loop settings...<br /><br /><div><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgOuNDT5v7nZRRZesn7norg2Eh9ohHke2xcqVqdim88sPsOQEcY4gDvZXOmEGa8JBpMDKlW-6V1drzV7WTwH4JM6uR5wwrqByuBLnTbur5fh6eMaXHAw9qu48SZHR0zfBXvfFOQKmSVGnJU/s1600/New+Bitmap+Image+(16).bmp"><img style="WIDTH: 400px; HEIGHT: 289px; CURSOR: hand" id="BLOGGER_PHOTO_ID_5465273323933613234" border="0" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgOuNDT5v7nZRRZesn7norg2Eh9ohHke2xcqVqdim88sPsOQEcY4gDvZXOmEGa8JBpMDKlW-6V1drzV7WTwH4JM6uR5wwrqByuBLnTbur5fh6eMaXHAw9qu48SZHR0zfBXvfFOQKmSVGnJU/s400/New+Bitmap+Image+(16).bmp" /></a><br /><br />Now we can see why the order and the number of fields in the SELECT statement are critical because of the fields mapping in the <em>loop</em>.<br /><br /><div><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg_iQJyRhrCGofpNnB4JH2vukCWpg4MO3lcLmiH0_Rs8vo7pq-YpsQJq1Izm-velDfeaaMl6_d7xzRzcprodUZTk0my1Cu657G-XGscZnE3DZYmwmcVTK5vehh-l8_GVH3IBLMGyuiRfebO/s1600/New+Bitmap+Image+(17).bmp"><img style="WIDTH: 400px; HEIGHT: 162px; CURSOR: hand" id="BLOGGER_PHOTO_ID_5465273312854908258" border="0" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg_iQJyRhrCGofpNnB4JH2vukCWpg4MO3lcLmiH0_Rs8vo7pq-YpsQJq1Izm-velDfeaaMl6_d7xzRzcprodUZTk0my1Cu657G-XGscZnE3DZYmwmcVTK5vehh-l8_GVH3IBLMGyuiRfebO/s400/New+Bitmap+Image+(17).bmp" /></a><br /><br />7. FSYS -- Copy a Template Empty Excel file to the ToBeEmailed Folder<br />In each loop the package (for each user in the notification list) copies a sample of the Excel sample file (SampleExcelFile.xls) to the “<em>uVar_ToBeEmailedFolder</em>”, the file must have a sheet named “<em>Report</em>”.<br /><br /><div><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEilU-z8OLwcDAh-X8b97rnhLkAHENl2zAr48R7IDZ0X550y1PvxESKMClAPYrB_VuWckYodgPBpWq-Xhf7TeykrwM_ioDjNKvTlFpVkHYNTK7sUVq9A6fgmFimE6X_-tanq8xJ6-2heSGZf/s1600/New+Bitmap+Image+(18).bmp"><img style="WIDTH: 400px; HEIGHT: 205px; CURSOR: hand" id="BLOGGER_PHOTO_ID_5465273309506557298" border="0" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEilU-z8OLwcDAh-X8b97rnhLkAHENl2zAr48R7IDZ0X550y1PvxESKMClAPYrB_VuWckYodgPBpWq-Xhf7TeykrwM_ioDjNKvTlFpVkHYNTK7sUVq9A6fgmFimE6X_-tanq8xJ6-2heSGZf/s400/New+Bitmap+Image+(18).bmp" /></a><br /><br />8. SCR--- Make the WHERE condition of the Source table<br /><br /><div><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhPBBDqgkhK3TCW_AuiBfZ9j-x3fdgFSyXOBQ8sEznRBdp8QlW3_snrGfdcYJzL64_ehSiGk7kehvyB1dpLlnDryWxG0c6DspG53r1ODIbNVk194pZxBVo97ophJMrIemAHFdxs_k9w7rIm/s1600/New+Bitmap+Image+(19).bmp"><img style="WIDTH: 400px; HEIGHT: 152px; CURSOR: hand" id="BLOGGER_PHOTO_ID_5465272931602058018" border="0" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhPBBDqgkhK3TCW_AuiBfZ9j-x3fdgFSyXOBQ8sEznRBdp8QlW3_snrGfdcYJzL64_ehSiGk7kehvyB1dpLlnDryWxG0c6DspG53r1ODIbNVk194pZxBVo97ophJMrIemAHFdxs_k9w7rIm/s400/New+Bitmap+Image+(19).bmp" /></a><br /><br />Now we have to make a query string that has the current user work log only, in other words the excel file that will be emailed to each user must have his/her work list report only, so we must filter the report table user by user by using the UserID.<br /><br /><strong>Note</strong>: In this select statement you <u>must</u> have all the field names that will be in the excel file as header fields, in my sample I will be only using one field (UserID)<br />The string will be saved in the “<em>uVar_Source_SQLQueryString</em>” variable that will be used by the “<em>OLE DB Source</em>” that is in the “<em>DFT --- Main Task</em>”.<br /><br /><div><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg1GF1FKqrduvct8STOw7novXCjLO7YVbFHIni2DfcArykOtLmR4iId7LwlBDcGYu-6JL5fhwnvE8YTLemlFc4I0fYLjSKQb2oKdDd17V4ujxZeSuHcyDyN9Hu3jsU8VbTKAv9J751q-YnB/s1600/New+Bitmap+Image+(20).bmp"><img style="WIDTH: 400px; HEIGHT: 334px; CURSOR: hand" id="BLOGGER_PHOTO_ID_5465272928973923474" border="0" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg1GF1FKqrduvct8STOw7novXCjLO7YVbFHIni2DfcArykOtLmR4iId7LwlBDcGYu-6JL5fhwnvE8YTLemlFc4I0fYLjSKQb2oKdDd17V4ujxZeSuHcyDyN9Hu3jsU8VbTKAv9J751q-YnB/s400/New+Bitmap+Image+(20).bmp" /></a><br /><br />9. DFT --- Main Task<br />Saving the work log list for the current user into the excel file.<br /><br /><div><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiaixcKjM0GQoqoGJBwyWBOrTcR5OQQ8WVq93kG-KxbuOGsqbdLHFg8jy6eDqFz_G9TGej9RSKxB9D15a94Z-CbA2yLK-yhAxe0N2dXa7fp-hsFCnmtgXlCP8NbKcIGzUbG69h1pfSD244O/s1600/New+Bitmap+Image+(21).bmp"><img style="WIDTH: 320px; HEIGHT: 245px; CURSOR: hand" id="BLOGGER_PHOTO_ID_5465272924651940162" border="0" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiaixcKjM0GQoqoGJBwyWBOrTcR5OQQ8WVq93kG-KxbuOGsqbdLHFg8jy6eDqFz_G9TGej9RSKxB9D15a94Z-CbA2yLK-yhAxe0N2dXa7fp-hsFCnmtgXlCP8NbKcIGzUbG69h1pfSD244O/s400/New+Bitmap+Image+(21).bmp" /></a><br /><br />The OLE Source is pointing to the <em>WorkList</em> table that is been filtered by the current UserID.<br />In the variable “<em>uVar_Source_SQLQueryString</em>” I had defined a query like “<em>SELECT TOP 1 UserID .....</em>” and I am sure that raised few questions like, why using TOP? Why hard coding a UserID? The answers are in the next step, while developing a package you need to define the fields and you need to be able to map them in the OLE DB source editor, and that’s why.<br /><br /><div><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhssOrNg36kWLVduTmmmO4XRl0PVx9r5IaGVuLwPUnOwMD1-bqB8buMI7h5F0CATLCFql4U5ReRI99VCSBpyPnujW33UvqFzh618hksJVyY7dx8P1qysjW5ZbEJc_FcwpJp6-M8hV_mjduf/s1600/New+Bitmap+Image+(22).bmp"><img style="WIDTH: 400px; HEIGHT: 207px; CURSOR: hand" id="BLOGGER_PHOTO_ID_5465272911533691746" border="0" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhssOrNg36kWLVduTmmmO4XRl0PVx9r5IaGVuLwPUnOwMD1-bqB8buMI7h5F0CATLCFql4U5ReRI99VCSBpyPnujW33UvqFzh618hksJVyY7dx8P1qysjW5ZbEJc_FcwpJp6-M8hV_mjduf/s400/New+Bitmap+Image+(22).bmp" /></a><br /><br />Saving the work list to the excel file in the “<em>Report</em>” sheet.<br /><br /><div><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEijdxeBB1OGBCfqVmyxO0vVTiGXnicLTsyjbxF6G8OH80cvAupuvO9_vaKLtpimuzyn-ug5z9g0hwBvRCsu5H6AGSXYUNLb7iMs4RUQHXrVIkVaoRFvfzBptWWP97Bl2RgK8m5ZqOPQk7Eb/s1600/New+Bitmap+Image+(23).bmp"><img style="WIDTH: 400px; HEIGHT: 210px; CURSOR: hand" id="BLOGGER_PHOTO_ID_5465272587243102418" border="0" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEijdxeBB1OGBCfqVmyxO0vVTiGXnicLTsyjbxF6G8OH80cvAupuvO9_vaKLtpimuzyn-ug5z9g0hwBvRCsu5H6AGSXYUNLb7iMs4RUQHXrVIkVaoRFvfzBptWWP97Bl2RgK8m5ZqOPQk7Eb/s400/New+Bitmap+Image+(23).bmp" /></a><br /><br />10. SCR--- Make Folderstring Like "Backup Folder and UserID"<br /><br /><div><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgtFP12jvDLOgWfbQmSgXCXoiWndQfZ1z09bDw6WWTFhrHIxo0N4aKTQYMA53EDaEQ9E35Nl2V7Y10Z70JvD3VIFDlkWYIwYm5oNXy-EuhsAEcEC4DtbjCnzr2QNq0nGaoAnmH8lnJyvC-W/s1600/New+Bitmap+Image+(24).bmp"><img style="WIDTH: 400px; HEIGHT: 135px; CURSOR: hand" id="BLOGGER_PHOTO_ID_5465272588853459602" border="0" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgtFP12jvDLOgWfbQmSgXCXoiWndQfZ1z09bDw6WWTFhrHIxo0N4aKTQYMA53EDaEQ9E35Nl2V7Y10Z70JvD3VIFDlkWYIwYm5oNXy-EuhsAEcEC4DtbjCnzr2QNq0nGaoAnmH8lnJyvC-W/s400/New+Bitmap+Image+(24).bmp" /></a><br /><br /><em>C:\SSIS\Email .........File \BackUpFolder\2010-04\2010-04-27-103108\FName1 LName1<br /></em><br /><div><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgvaaYZGWVytzQ45ombcO_p5EmRjlfRQr40D3jGgO8UVPP9XtcX2RNUzw6YBkhBWN4BJUC0gEueecYPxQq_S5B87Vb3pC7WYoT_ldarLn3fr0y_oUHxirny3imH9Mz_cy9wswQdT7_ij9FN/s1600/New+Bitmap+Image+(25).bmp"><img style="WIDTH: 400px; HEIGHT: 223px; CURSOR: hand" id="BLOGGER_PHOTO_ID_5465272581148591970" border="0" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgvaaYZGWVytzQ45ombcO_p5EmRjlfRQr40D3jGgO8UVPP9XtcX2RNUzw6YBkhBWN4BJUC0gEueecYPxQq_S5B87Vb3pC7WYoT_ldarLn3fr0y_oUHxirny3imH9Mz_cy9wswQdT7_ij9FN/s400/New+Bitmap+Image+(25).bmp" /></a><br /><br />11. FSYS---- Make Backup folder in the name of the User<br /><em><span style="font-size:85%;">C:\SSIS\Email .........File \BackUpFolder\2010-04\2010-04-27-103108\FName1 LName1<br /></span></em><br /><div><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgzPbbpywU9AhLAppLUVGBGI5QKvp5WJ5i4eLniRUNuGBtDF7nfLKZTBlEVsLYuA-3miPOqAj8P_f5TVR6X5nXvSHPr7hOG3CQ4mEXdPC1ndbb1pXzYJJMvPsZPZJNdFgJ_XoqLF0L-N-1i/s1600/New+Bitmap+Image+(26).bmp"><img style="WIDTH: 400px; HEIGHT: 201px; CURSOR: hand" id="BLOGGER_PHOTO_ID_5465272567654308418" border="0" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgzPbbpywU9AhLAppLUVGBGI5QKvp5WJ5i4eLniRUNuGBtDF7nfLKZTBlEVsLYuA-3miPOqAj8P_f5TVR6X5nXvSHPr7hOG3CQ4mEXdPC1ndbb1pXzYJJMvPsZPZJNdFgJ_XoqLF0L-N-1i/s400/New+Bitmap+Image+(26).bmp" /></a><br /><br />12. SCR--- Rename file names with the format of "yyyy-mm-dd-hhmmssff"<br /><br /><div><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiXsn2-trq3a_TUfGlnqeRBnmYZ1LS_LdYT-_LIqULMxQkpk1PCTz8mkfarjlgrpgliya5sWIviYl4xglBhQQPxwbhc8RvNOVKbCG0B78W6QC3sIJXLxGNCoX2sC5Is9PomMrg_3jk6Aw7u/s1600/New+Bitmap+Image+(27).bmp"><img style="WIDTH: 400px; HEIGHT: 107px; CURSOR: hand" id="BLOGGER_PHOTO_ID_5465272160988802034" border="0" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiXsn2-trq3a_TUfGlnqeRBnmYZ1LS_LdYT-_LIqULMxQkpk1PCTz8mkfarjlgrpgliya5sWIviYl4xglBhQQPxwbhc8RvNOVKbCG0B78W6QC3sIJXLxGNCoX2sC5Is9PomMrg_3jk6Aw7u/s400/New+Bitmap+Image+(27).bmp" /></a><br /><br />The string will look like<br /><em><span style="font-size:78%;"><span style="font-size:85%;">C:\SSIS\Email ...... File\BackUpFolder\2010-04\2010-04-27-103108\FName1 LName1\FName1 LName1 2010-04-27-10311137.xls</span><br /></span></em><br /><div><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhcOnJQOpipAlUp7YR_yso4fAzuIZ45lCCbVafwHX9OrD4SWHjT1faoeRl4othifYLoq0A7_SvubqIrzZpqrehVJrbzhDgepVOYS9CdzcyfXL8t8L002hK9MAZCweGpJTazdmqTb3L8Pulm/s1600/New+Bitmap+Image+(28).bmp"><img style="WIDTH: 400px; HEIGHT: 244px; CURSOR: hand" id="BLOGGER_PHOTO_ID_5465272158907742514" border="0" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhcOnJQOpipAlUp7YR_yso4fAzuIZ45lCCbVafwHX9OrD4SWHjT1faoeRl4othifYLoq0A7_SvubqIrzZpqrehVJrbzhDgepVOYS9CdzcyfXL8t8L002hK9MAZCweGpJTazdmqTb3L8Pulm/s400/New+Bitmap+Image+(28).bmp" /></a><br /><br />13. FSYS---- Moving processed files to the "Backup Active Folder" </div><div><br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjTSKuTtOtRaXXkj1Hycocm3cli7w1Idl0A5pg7atmRj3nibTATDw-LBF42gaIUjrEaU6pKCwEuJcHXdYpbEuL12UpricOZ0O7FfurZNLaEmzazVUahEwjtXRUnXM3cFdyshQntjApPMA3O/s1600/New+Bitmap+Image+(29).bmp"><img style="WIDTH: 400px; HEIGHT: 221px; CURSOR: hand" id="BLOGGER_PHOTO_ID_5465272148275875890" border="0" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjTSKuTtOtRaXXkj1Hycocm3cli7w1Idl0A5pg7atmRj3nibTATDw-LBF42gaIUjrEaU6pKCwEuJcHXdYpbEuL12UpricOZ0O7FfurZNLaEmzazVUahEwjtXRUnXM3cFdyshQntjApPMA3O/s400/New+Bitmap+Image+(29).bmp" /></a></div><div><br /></div><div><br />Moving the file from ....<br /><span style="font-size:85%;"><em>C:\SSIS\Email ..........File\ToBeEmailed\ SampleExcelFile.xls</em><br /></span>To .....<br /><em><span style="font-size:85%;">C:\SSIS\Email ...... File\BackUpFolder\2010-04\2010-04-27-103108\FName1 LName1\FName1 LName1 2010-04-27-10311137.xls<br /></span></em><br />14. Send Mail Task </div><div></div><div><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi-EyvjTLIkPfyfpIWXdMq41hyphenhyphenpedbTPHJId4una_pC95Z8SZXx-eIZhR8rVtC3XiETpBQAxT6I-tzcMDa0FlWeCETwSSt3h-GRNY5igNAjvQH9Jo66lxuk07HYt5xYWmYI_jLncyZ6nHwQ/s1600/New+Bitmap+Image+(30).bmp"><img style="WIDTH: 400px; HEIGHT: 382px; CURSOR: hand" id="BLOGGER_PHOTO_ID_5465269988836745586" border="0" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi-EyvjTLIkPfyfpIWXdMq41hyphenhyphenpedbTPHJId4una_pC95Z8SZXx-eIZhR8rVtC3XiETpBQAxT6I-tzcMDa0FlWeCETwSSt3h-GRNY5igNAjvQH9Jo66lxuk07HYt5xYWmYI_jLncyZ6nHwQ/s400/New+Bitmap+Image+(30).bmp" /></a></div><div><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhuIOlOK5txPbASHenm9qggQkJQfBGV8gGV8Sy_NFe9O3vz6_RLNQMOljrMUvw6_mc8PiechMVF88YJqrSSLSVeQqsub7YZnScuJBxFO3ug-sJAQ1eB9memOEKlKXtFNu33hMLslylAFY2P/s1600/New+Bitmap+Image+(31).bmp"><img style="WIDTH: 337px; HEIGHT: 400px; CURSOR: hand" id="BLOGGER_PHOTO_ID_5465269983566908626" border="0" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhuIOlOK5txPbASHenm9qggQkJQfBGV8gGV8Sy_NFe9O3vz6_RLNQMOljrMUvw6_mc8PiechMVF88YJqrSSLSVeQqsub7YZnScuJBxFO3ug-sJAQ1eB9memOEKlKXtFNu33hMLslylAFY2P/s400/New+Bitmap+Image+(31).bmp" /></a><br /><br /><strong><span style="color:#33ccff;"><span style="color:#3366ff;">Testing Scenarios</span><br /></span></strong>Before testing make sure that you have set the variables according to the snap shot of the variables that I have and check the “Criteria”.<br />Once you run the package you should be able to see.<br /><br /><div><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjbigyEAP06uWCORwchRX6phbgIKHXK4BGT_h4w7YxpapeeOPZpJBF2Dph2Z8qRbXk3WCcG5JPV5uY2DPfMJGd9CszLnrjYEMJdfBcT3F0S-c-cQ4zOr63pmQ5LmKQikGiQxY4AUjHpE70p/s1600/New+Bitmap+Image+(32).bmp"><img style="WIDTH: 400px; HEIGHT: 359px; CURSOR: hand" id="BLOGGER_PHOTO_ID_5465269976411456210" border="0" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjbigyEAP06uWCORwchRX6phbgIKHXK4BGT_h4w7YxpapeeOPZpJBF2Dph2Z8qRbXk3WCcG5JPV5uY2DPfMJGd9CszLnrjYEMJdfBcT3F0S-c-cQ4zOr63pmQ5LmKQikGiQxY4AUjHpE70p/s400/New+Bitmap+Image+(32).bmp" /></a><br /><br /><br /><br /><div></div></div></div></div></div></div></div></div></div></div></div></div></div></div></div></div></div></div></div></div></div></div></div>Shahriar Nikkhah (Nik)http://www.blogger.com/profile/17215555681462843010noreply@blogger.com2tag:blogger.com,1999:blog-5534374197067462925.post-76642827275876853522010-04-15T15:44:00.007-04:002010-04-15T15:53:07.301-04:00Delete the oldest file in a folder with SSIS<span style="color:#3366ff;">Delete the oldest file in a folder with SSIS</span><br />Some cases the developer wants to delete the oldest or the latest file, or maybe just save the name in a variable. In this case I am deleting the oldest file<br /><br /><span style="color:#3366ff;">Criteria</span><br />1. Make a folder at “C:\SSIS\DeleteTheOldestFile\FolderToDeleteFrom”<br />2. Copy some text files in it with different “Created Dates”<br /><br /><span style="color:#3366ff;">Over all View</span><br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj1g_1AMOyG2zc5-xU3_pkqIL87fgOjatQAVMmcFAxQqy86K2MTL-xpc3ilWahPU1lv5h4NmQ9Ce_siLEJbsaqoLbkudtJ7QsRkyOg5PlW5cv3VWGQT7-eZPXjSyFedZKXlrZGqdsIS2XQL/s1600/New+Bitmap+Image+(2).bmp"><img id="BLOGGER_PHOTO_ID_5460453639343361762" style="WIDTH: 400px; CURSOR: hand; HEIGHT: 245px" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj1g_1AMOyG2zc5-xU3_pkqIL87fgOjatQAVMmcFAxQqy86K2MTL-xpc3ilWahPU1lv5h4NmQ9Ce_siLEJbsaqoLbkudtJ7QsRkyOg5PlW5cv3VWGQT7-eZPXjSyFedZKXlrZGqdsIS2XQL/s400/New+Bitmap+Image+(2).bmp" border="0" /></a><br /><br /><span style="color:#3366ff;">Defining some variables in the package</span><br />One of the most important part of a SSIS package are the variables, because some variables will be defined in the Configuration File(s), so that you can use the package dynamically.<br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgXrdi8djkExQpC-pHR4pMAv51qlFj2eVbpflKm4MPmndPyagZHh9PYm7RoDZEQsESe-XHqn_myEl5cod7aQcYPapFvPxAu-rUiXOYxGBbarObAz8ZgyZOzP7tILwknQqn9jOrB1wMWnzH2/s1600/New+Bitmap+Image+(3).bmp"><img id="BLOGGER_PHOTO_ID_5460453634570901506" style="WIDTH: 400px; CURSOR: hand; HEIGHT: 111px" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgXrdi8djkExQpC-pHR4pMAv51qlFj2eVbpflKm4MPmndPyagZHh9PYm7RoDZEQsESe-XHqn_myEl5cod7aQcYPapFvPxAu-rUiXOYxGBbarObAz8ZgyZOzP7tILwknQqn9jOrB1wMWnzH2/s400/New+Bitmap+Image+(3).bmp" border="0" /></a><br /><br />1. “uVar_CurrentFileDateTime” a Defined date for the max date.<br />2. “uVar_CurrentFileName” The current file name<br />3. “uVar_FolderNameOfFilesToBeProcessed” the folder that will be looking for the oldest file to be deleted<br />4. “uVar_OldestFileName” The name of the oldest file.<br />5. “uVar_SourceFileExtension” The files extension.<br /><br /><span style="color:#3366ff;">Adding Object (controls) </span><br />Now we can add the objects as seen in the “Over all View”<br />1. FLC -- Loop through a folder and seek selected files one by one<br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgRm5T0Pn2d5mU7J81ub_67HnoYudyOrBQhcCZkl6a3QFy_7UtCyI1HffDwfFFNBqKMZKfZIPjL784f7RRWrku3VgF4WZmiJXSb7GOidJjeYmEA3SSDbaU5Mhy6_G3VZQ6FOp2TeT2WYun1/s1600/New+Bitmap+Image+(4).bmp"><img id="BLOGGER_PHOTO_ID_5460453636540475298" style="WIDTH: 400px; CURSOR: hand; HEIGHT: 288px" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgRm5T0Pn2d5mU7J81ub_67HnoYudyOrBQhcCZkl6a3QFy_7UtCyI1HffDwfFFNBqKMZKfZIPjL784f7RRWrku3VgF4WZmiJXSb7GOidJjeYmEA3SSDbaU5Mhy6_G3VZQ6FOp2TeT2WYun1/s400/New+Bitmap+Image+(4).bmp" border="0" /></a><br />Setting the ForLoop to find the text files by using the “uVar_SourceFileExtention” and looking into the folder that is in “uVar_FolderNameOfFilesToBeProcessed”.<br />By setting the Traverse Subfolders it will look into the sub folders as well.<br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg6LzVkzOypXbwrj_Z4X7deDO-xUUkEZhknHIAlcS8mtqYZxK83p31GXJ4Y2yQr-GpW8UxeDkwdwKCdq562_2K9Ce5UIKU8bsVGi4uap4YkpF_US0IehsSuGY6GXmftRQU5eXwuPOHH5yrM/s1600/New+Bitmap+Image+(5).bmp"><img id="BLOGGER_PHOTO_ID_5460453070764061666" style="WIDTH: 400px; CURSOR: hand; HEIGHT: 122px" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg6LzVkzOypXbwrj_Z4X7deDO-xUUkEZhknHIAlcS8mtqYZxK83p31GXJ4Y2yQr-GpW8UxeDkwdwKCdq562_2K9Ce5UIKU8bsVGi4uap4YkpF_US0IehsSuGY6GXmftRQU5eXwuPOHH5yrM/s400/New+Bitmap+Image+(5).bmp" border="0" /></a><br />While loop through files, save the file name and path into the above variable<br /><br /><span style="color:#3366ff;">2. SCR -- Find the oldest file by date and time<br /></span><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhAeQjYFuRm4gpyhbCButqgXQJ_R6EIaCIiWS9RjR5BchVPr10ok9LLWag02Gzfey2jjD7FcOaKXgV2DF9_c4Z_m8Pscp2bOpsfMIcZ1aCxdZpZeGyAmXJ2_G7owZ_EUf_-1VEix725BGSB/s1600/New+Bitmap+Image+(6).bmp"><img id="BLOGGER_PHOTO_ID_5460453069975123570" style="WIDTH: 400px; CURSOR: hand; HEIGHT: 124px" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhAeQjYFuRm4gpyhbCButqgXQJ_R6EIaCIiWS9RjR5BchVPr10ok9LLWag02Gzfey2jjD7FcOaKXgV2DF9_c4Z_m8Pscp2bOpsfMIcZ1aCxdZpZeGyAmXJ2_G7owZ_EUf_-1VEix725BGSB/s400/New+Bitmap+Image+(6).bmp" border="0" /></a><br /><br />Now we can use the variable and a script to find each file’s “Created date” and compare it with the others, by saving the oldest file name and folder in the “uVar_OldestFileName” variable<br /><br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjocqsHCsySlzLY1ezyB_cI8BT07o58MO3M3U18sLeDxDbozEsg0dKCUhZNDUXxsNmlYkXSH8CfPA9sBbY80OZGIpW8otDz7zWzCMOgaDRWCx-UCiwk9q34qQHfoCSxijr3jIALjPWS7KCY/s1600/New+Bitmap+Image+(7).bmp"><img id="BLOGGER_PHOTO_ID_5460453061662810242" style="WIDTH: 400px; CURSOR: hand; HEIGHT: 282px" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjocqsHCsySlzLY1ezyB_cI8BT07o58MO3M3U18sLeDxDbozEsg0dKCUhZNDUXxsNmlYkXSH8CfPA9sBbY80OZGIpW8otDz7zWzCMOgaDRWCx-UCiwk9q34qQHfoCSxijr3jIALjPWS7KCY/s400/New+Bitmap+Image+(7).bmp" border="0" /></a><br /><br /><span style="color:#3366ff;">3. fx<br /></span>In this section we have to check that is they are any files to be deleted, otherwise the package will fail if no file found.<br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjNyGf9DaKU1dFtWsWnQtGYay_IW63hY8DKR1B_U1ZIruFsJuyK6-_fdbibQsFk9sLWWZqhvX1QePt56FS-bliisP0u3Rej2ExRmNdeYejLypgy-n90BWB2i6zFoWuD8naeqjIgrF8JXJAl/s1600/New+Bitmap+Image+(8).bmp"><img id="BLOGGER_PHOTO_ID_5460453057284852642" style="WIDTH: 400px; CURSOR: hand; HEIGHT: 337px" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjNyGf9DaKU1dFtWsWnQtGYay_IW63hY8DKR1B_U1ZIruFsJuyK6-_fdbibQsFk9sLWWZqhvX1QePt56FS-bliisP0u3Rej2ExRmNdeYejLypgy-n90BWB2i6zFoWuD8naeqjIgrF8JXJAl/s400/New+Bitmap+Image+(8).bmp" border="0" /></a><br /><br /><span style="color:#3366ff;">4. FSYS -- Delete the oldest file<br /></span>Delete the oldest file<br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhxzeCsQFEeyLeZ6K560rOToGD_QVV2Zw6FpKDwyxb-so41bwqBz_N8mg5ElRYMg2MZiR-5lt_2E4biKUHdxB7HqeJKx7g_etPcVjzcsi5t191XIYS8fYh-u8OzSYPhqCIaiwLc9Et6UKTn/s1600/New+Bitmap+Image+(9).bmp"><img id="BLOGGER_PHOTO_ID_5460453045374186354" style="WIDTH: 400px; CURSOR: hand; HEIGHT: 177px" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhxzeCsQFEeyLeZ6K560rOToGD_QVV2Zw6FpKDwyxb-so41bwqBz_N8mg5ElRYMg2MZiR-5lt_2E4biKUHdxB7HqeJKx7g_etPcVjzcsi5t191XIYS8fYh-u8OzSYPhqCIaiwLc9Et6UKTn/s400/New+Bitmap+Image+(9).bmp" border="0" /></a><br /><br /><span style="color:#3366ff;">Testing Scenarios </span><br />Before testing make sure that you have set the variables according to the snap shot of the variables and have the “Criteria” checked.<br /><br /><em>Scenario 1:</em><br />1- Check the folder “C:\SSIS\DeleteTheOldestFile\FolderToDeleteFrom” as make sure that you have few txt files.<br />2- Check the Create date of each file<br />3- Run the package and check the folder again and see what files have been deleted.<br /><br /><br /><em>Scenario 2:</em><br />This it with Excel files<br />1- Make a folder “C:\MySSIS\MainFolder\DelOldest”<br />2- Save the folder in “uVar_FolderNameOfFilesToBeProcessed”<br />3- Set the Variable “uVar_SourceFileExtension” = *.xls<br />4- Copy some excel files in that folder<br />5- Run the package<br />6- See what files have been deleted<br /><br /><em>Note:</em> Try running the package multiple times.Shahriar Nikkhah (Nik)http://www.blogger.com/profile/17215555681462843010noreply@blogger.com4tag:blogger.com,1999:blog-5534374197067462925.post-51623558316567713652010-04-15T11:24:00.013-04:002010-04-15T15:44:46.574-04:00Looping through list of users in SSIS by using SSIS “Object Variables” and “Recordset Destination”<span style="color:#3366ff;">Looping through list of users in SSIS by using SSIS “Object Variables” and “Recordset Destination”<br /></span>One of the question that came up for me was how to get a list of users & emails and email them the work list in Excel, the second part of the question was they wanted to keep a archive of the work list by user and date, a kind of a back-up list, so in this case I will be showing how to make the folders by user by date, and I will be using objects such as “Recordset Destination” and ADO.NET in a Loop object.<br />Criteria<br />1. ---<br /><span style="color:#3366ff;">Over all View</span><br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjQRLFCxGbjx-aAf34OyP0PGEWc3TXFLEt-72mpqG4v8t01owF7DQ0ZaLPO4LEudaIDeczU_QndKhlSCcKBEY9q-Y6YDjp0mQpnth3CQciPrIzet3EUy1gZF3UFXStnzOaZVqCBP5sf_Ylw/s1600/New+Bitmap+Image+(2).bmp"><img id="BLOGGER_PHOTO_ID_5460389318873902530" style="WIDTH: 390px; CURSOR: hand; HEIGHT: 384px" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjQRLFCxGbjx-aAf34OyP0PGEWc3TXFLEt-72mpqG4v8t01owF7DQ0ZaLPO4LEudaIDeczU_QndKhlSCcKBEY9q-Y6YDjp0mQpnth3CQciPrIzet3EUy1gZF3UFXStnzOaZVqCBP5sf_Ylw/s400/New+Bitmap+Image+(2).bmp" border="0" /></a><br /><br /><span style="color:#3366ff;">Defining some variables in the package</span><br />One of the most important part of a SSIS package are the variables, because some variables will be defined in the Configuration File(s), so that you can use the package dynamically.<br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiOTo7-sJh76C85ZpvMsBhdJPsey4u4RGQK54PZBGOv4V9M8ovB7Q6zOjU8biP8bcAXbVFl8qZS3NrluEZMM207XOu_GM3r7Tf4tXLQDGOJcj0DHN5AeqAbKrya9lGWB6VB3eSD1nFKfuA5/s1600/New+Bitmap+Image+(3).bmp"><img id="BLOGGER_PHOTO_ID_5460389315456147410" style="WIDTH: 388px; CURSOR: hand; HEIGHT: 166px" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiOTo7-sJh76C85ZpvMsBhdJPsey4u4RGQK54PZBGOv4V9M8ovB7Q6zOjU8biP8bcAXbVFl8qZS3NrluEZMM207XOu_GM3r7Tf4tXLQDGOJcj0DHN5AeqAbKrya9lGWB6VB3eSD1nFKfuA5/s400/New+Bitmap+Image+(3).bmp" border="0" /></a><br /><br />1.“<strong>uVar_ArchiveFolder</strong>” This is where you want to have the root of the Archive folder.<br />2.“<strong>uVar_ArchiveFolderCurrUser</strong>” a variable that will save the current user name and folder, e.g. C:\SSIS\Archive\Smith\2010-04\2010-04-15.<br />3.“<strong>uVar_CurrentUser</strong>” An object that will contain the list of Users (Record of users).<br />4.“<strong>uVar_CurrentUserEmail</strong>” The name of the current Email.<br />5.“<strong>uVar_CurrentUserName</strong>” The name of the current UserName.<br /><br /><span style="color:#3366ff;">Defining Connection Manager<br /></span>We need 1 connection.<br />1. Excel connection manager<br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhKa4O_vCI874zRJwWy4qYFjSICds5sub9xLlA8CrBUY-TRuA9_X4a3_dR5m7cQ8PtMh0xokKR9zTOFajybP4HM4ohhRFjvGCbXd-F2KxiN8ekL8cZu6aYUS89bOeadWIdPjLaUoUHsk6bE/s1600/New+Bitmap+Image+(4).bmp"><img id="BLOGGER_PHOTO_ID_5460389311489207794" style="WIDTH: 400px; CURSOR: hand; HEIGHT: 338px" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhKa4O_vCI874zRJwWy4qYFjSICds5sub9xLlA8CrBUY-TRuA9_X4a3_dR5m7cQ8PtMh0xokKR9zTOFajybP4HM4ohhRFjvGCbXd-F2KxiN8ekL8cZu6aYUS89bOeadWIdPjLaUoUHsk6bE/s400/New+Bitmap+Image+(4).bmp" border="0" /></a><br /><br /><span style="color:#3366ff;">Adding Object (controls)</span><br />Now we can add the objects as seen in the “Over all View”<br />1. DFT----CreateUserlistToLoopThrough<br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh8VID91cWA5S75_OMnXTaJQHr0LFRDH7SbMa_YX9UaKqV-_B5im-yLXPO2iNB_FC-FPrCJZiV7gmO1S0wPVL61Zp-ZHNJvZSwREP3kbfZQImHVolwjh1HzVDyJHxx_Ho3J-Z7-esfmuyXi/s1600/New+Bitmap+Image+(5).bmp"><img id="BLOGGER_PHOTO_ID_5460387830508769970" style="WIDTH: 315px; CURSOR: hand; HEIGHT: 400px" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh8VID91cWA5S75_OMnXTaJQHr0LFRDH7SbMa_YX9UaKqV-_B5im-yLXPO2iNB_FC-FPrCJZiV7gmO1S0wPVL61Zp-ZHNJvZSwREP3kbfZQImHVolwjh1HzVDyJHxx_Ho3J-Z7-esfmuyXi/s400/New+Bitmap+Image+(5).bmp" border="0" /></a><br /><br />In this section we are retrieving the list of users and saving them into the SSIS Object variable, which is basically playing a role of a recordset.<br />The order of the select statement is very important because it will be used in the ForLoop. You can filter the SELECT statement dynamically.<br /><br />2. FLC----Loop through Users list in SSIS Object using ADO<br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiQSf0fSpZfS8GttqbmvvacxCQNdmbCva0FW1za6n7wtw8Iaju5vhe8yZ9m2-3RG7qIXzR2BIwieMOIjK8ZEvHfSalUpaX8AyF4gkr66UzyYx6XGx-bSIl08685B-_ITPI8wJTj7_sZRH9r/s1600/New+Bitmap+Image+(6).bmp"><img id="BLOGGER_PHOTO_ID_5460387837333422434" style="WIDTH: 372px; CURSOR: hand; HEIGHT: 400px" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiQSf0fSpZfS8GttqbmvvacxCQNdmbCva0FW1za6n7wtw8Iaju5vhe8yZ9m2-3RG7qIXzR2BIwieMOIjK8ZEvHfSalUpaX8AyF4gkr66UzyYx6XGx-bSIl08685B-_ITPI8wJTj7_sZRH9r/s400/New+Bitmap+Image+(6).bmp" border="0" /></a><br /><br />Setting the LOOP section, this is the part that is related to the order of the fields in the select statement.<br />3. SCR----Make Folder name by User as a String<br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgtm57s8t9DRtZhpcTL1KbG-qdGFhpJSI-drtkyH-WTVvL7V4MQlfrpdPICdtu1dtL1V_aPn4_eaY59ciZ5wDEFID75VIGa_WAfPurvXJlcuwHx8PxXKZa9KbxLtwQ87khFunNz_s18it6-/s1600/New+Bitmap+Image+(7).bmp"><img id="BLOGGER_PHOTO_ID_5460387839271452850" style="WIDTH: 400px; CURSOR: hand; HEIGHT: 367px" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgtm57s8t9DRtZhpcTL1KbG-qdGFhpJSI-drtkyH-WTVvL7V4MQlfrpdPICdtu1dtL1V_aPn4_eaY59ciZ5wDEFID75VIGa_WAfPurvXJlcuwHx8PxXKZa9KbxLtwQ87khFunNz_s18it6-/s400/New+Bitmap+Image+(7).bmp" border="0" /></a><br /><br />A script to make the FOLDER string.<br /><br />4. FSYS----Create archive folderSetting a “File System task” to make the folders<br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjkOTg67f2eq-PK-A4O2_HSnR0qWL4U0gTH11soQ2q_2CWrVQmva_mkL0NZWpgOD1zxv0rhiZj0oHLwdi_p0yhjU-Ph0K7E_3uyERSNOUTTmvz8FSvDvTHKUwJZaXWaK99defSUPRxnIPos/s1600/New+Bitmap+Image+(8).bmp"><img id="BLOGGER_PHOTO_ID_5460387843095403490" style="WIDTH: 400px; CURSOR: hand; HEIGHT: 192px" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjkOTg67f2eq-PK-A4O2_HSnR0qWL4U0gTH11soQ2q_2CWrVQmva_mkL0NZWpgOD1zxv0rhiZj0oHLwdi_p0yhjU-Ph0K7E_3uyERSNOUTTmvz8FSvDvTHKUwJZaXWaK99defSUPRxnIPos/s400/New+Bitmap+Image+(8).bmp" border="0" /></a><br /><br /><span style="color:#3366ff;">Testing Scenarios</span><br />Before testing make sure that you have set the variables according to the snap shot of the variables and have the “Criteria” checked.<br /><br /><em>Scenario 1:</em><br />1- Run the package and check the folder “C:\SSIS\ArchiveFolder” and its sub folders<br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiRDynDcauQ-Hv2Wvoxoi7uOJ87_4GIhl0s_HJ6T5k5VEqo8w_XGeG2Z8e241LTl9L8syW7bCp55bgzB_MUVBjDMiMBEO6q44kPWaCe2RzlQNjJXq4qU9JQrrREhPbMzokbmDaT7VNqrmBD/s1600/New+Bitmap+Image+(9).bmp"><img id="BLOGGER_PHOTO_ID_5460387850670063730" style="WIDTH: 400px; CURSOR: hand; HEIGHT: 197px" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiRDynDcauQ-Hv2Wvoxoi7uOJ87_4GIhl0s_HJ6T5k5VEqo8w_XGeG2Z8e241LTl9L8syW7bCp55bgzB_MUVBjDMiMBEO6q44kPWaCe2RzlQNjJXq4qU9JQrrREhPbMzokbmDaT7VNqrmBD/s400/New+Bitmap+Image+(9).bmp" border="0" /></a><br /><br /><em>Scenario 2:</em><br />1- Set the Variable “uVar_ArchiveFolder” = “C:\MyFolder\BackUpFolder”<br />2- Run the package and check the folder “C:\MyFolder\BackUpFolder” and its sub foldersShahriar Nikkhah (Nik)http://www.blogger.com/profile/17215555681462843010noreply@blogger.com1tag:blogger.com,1999:blog-5534374197067462925.post-79806487588741666082010-04-08T16:05:00.050-04:002018-04-08T18:29:32.176-04:00Looping Through Excel Files And Sheets in SSIS<span style="color: #3366ff;"><strong style="background-color: white; color: #333333; font-family: Georgia, Times, serif; font-size: 12.87px;"><span style="color: red;">2018-04-08</span></strong><br style="background-color: white; color: #333333; font-family: Georgia, Times, serif; font-size: 12.87px;" /><strong style="background-color: white; color: #333333; font-family: Georgia, Times, serif; font-size: 12.87px;"><span style="color: red;">Hi </span></strong><br style="background-color: white; color: #333333; font-family: Georgia, Times, serif; font-size: 12.87px;" /><strong style="background-color: white; color: #333333; font-family: Georgia, Times, serif; font-size: 12.87px;"><span style="color: red;">I have an updated version of this blog (SSIS 2016) at my new blog with all the codes</span></strong></span><br />
<h4 style="background-color: white; color: #333333; font-family: Georgia, Times, serif; font-size: 12.87px;">
<span style="color: blue;"><b>Click <a href="http://sqldataside.blogspot.ca/2018/04/looping-through-excel-97-2003-files-and.html" style="color: #999999; text-decoration-line: none;" target="_blank">here</a></b></span></h4>
<div>
<span style="color: blue;"><br /></span></div>
<span style="color: #3366ff;">Looping Through Excel Files and Sheets</span><br />
I decided to write a small part of my SSIS projects about how to retrieve data from an Excel (97 – 2003) files. Basically this program will loop through a folder and its sub folders, find all the Excel files and within each Excel file it will loop though all its sheets, retrieve data from each sheet and inserting them to a destination table and finally moving the file to a backup folder after each process.<br />
<br />
<span style="color: #3366ff;">Criteria</span>1. The file format must be in a *.xls format (Excel (97 – 2003))<br />
2. All sheets in all Excel files must have the same data format (column/Field names, data type, etc..., all meta data must be the same)<br />
3. A copy of one of the excel files that has been renamed to SampleExcelFile.xls<br />
4. In the SampleExcelFile.xls file you must have one sheet with data (Headers included) as a sample and the sheet name must be named “Tab”. This will help us while we are developing and we always have a sample to show the customer what was implemented.<br />
5. A Folder that will be the main folder of the ETL (in this case it will be named => C:\SSIS\LoopingThroughExcelFilesAndSheets)<br />
6. Two other sub folders 1- “Sample” 2- “ToBeProcessed”<br />
7. Copy the Excel Sample File in the “Sample Folder”<br />
8. All headers must be on the first row of each excel sheet<br />
9. No empty sheets are accepted, remove them all<br />
<br />
<span style="color: #3366ff;">Over all View</span><br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhVoIhny4qZzDhvJxl0kNzV9mo07sgAwH9AM6PXKke9U1_A4RRE0zrQie5gLlLEaY-TTl8mjKI03u1b-btj83fa8QuxPnxD8rsoErLRJeqwTuMBil2NHGfV32hp4yRchPRw5G4dxTC-03ga/s1600/New+Bitmap+Image+(2).bmp"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5457869047058193650" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhVoIhny4qZzDhvJxl0kNzV9mo07sgAwH9AM6PXKke9U1_A4RRE0zrQie5gLlLEaY-TTl8mjKI03u1b-btj83fa8QuxPnxD8rsoErLRJeqwTuMBil2NHGfV32hp4yRchPRw5G4dxTC-03ga/s400/New+Bitmap+Image+(2).bmp" style="cursor: hand; height: 356px; width: 400px;" /></a><br />
<br />
<br />
<span style="color: #3366ff;">Defining some variables in the package</span><br />
One of the most important part of a SSIS package are the variables. Because some variables will be defined in the Configuration File(s), so that you can use the package dynamically,<br />
Example 1: The server name on your computer is different that the server name on production server so basically you can have the “server name variable” defined in the XML or table configuration so that it can be changes when deploying the package from one server to another.<br />
<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhn-3at-MYIRre4R3ViBamUoZdeQ3Udfm0xBJ01OCQFwCLC8ktyO6ywtwyrT6396r36xm2SCo5VXmzYLSzkcTWEF9c3QkLbvL4nylwl1ywxIG5j5ktQSta11657bspR6yVDvGd47FpUR_4j/s1600/New+Bitmap+Image+(3).bmp"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5457868828785964850" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhn-3at-MYIRre4R3ViBamUoZdeQ3Udfm0xBJ01OCQFwCLC8ktyO6ywtwyrT6396r36xm2SCo5VXmzYLSzkcTWEF9c3QkLbvL4nylwl1ywxIG5j5ktQSta11657bspR6yVDvGd47FpUR_4j/s400/New+Bitmap+Image+(3).bmp" style="cursor: hand; height: 149px; width: 400px;" /></a><br />
<br />
<br />
1. “<strong>uVar_BackupActiveFolder</strong>” Each time the package runs it makes a folder in the backup folder this folder will be known as the current backup folder which will be used to save the Excel backup files and/or maybe the Error text file.<br />
2. “<strong>uVar_BackupMainFolderPath</strong>” Some customers prefer to have the backup folder in another server so basically we allow them to have a choice, by adding this variable (See the script section for more details). If this variable is empty it will make a default backup folder name (See backup scripts).<br />
3. “<strong>uVar_DestinationDBName</strong>” The name of the Destination Database.<br />
4. “<strong>uVar_DestinationServerName</strong>” The name of the Destination SQL Server.<br />
5. “<strong>uVar_DestinationTableName</strong>” The name of the Destination Table in SQL Server.<br />
6. “<strong>uVar_ErrorOutPutFile</strong>” This is the path and file name of the Error file, each time this path and file name changes so that we can keep a backup of each error log file.<br />
7. “<strong>uVar_ExcelActiveTabName</strong>” The name of the active Excel Sheet name (the default value is “Tab$”) this is more needed in the design mode<br />
8. “<strong>uVar_ExcelTabObjectName</strong>” A variable to keep the list of the sheet names of each Excel file<br />
9. “<strong>uVar_FolderNameOfFilesToBeProcessed</strong>”<br />
10. “<strong>uVar_MainFolderPath</strong>” This is the path where you will be having your main ETL; it can contain the Package, configuration files, backup, sample folder, ToBeProcessed folder and etc... (C:\SSIS\LoopingThroughExcelFilesAndSheets)<br />
11. “<strong>uVar_NumberOfExtractedRows</strong>” A row counter that won’t be used in this example.<br />
12. “<strong>uVar_SMTPEmailServerName</strong>” The SMTP name or IP address of it<br />
(e.g. 1: Neptune e.g. 2: <a href="http://www.mars.com/">http://www.mars.com/</a> e.g. 3: 10.2.10.56)<br />
13. “<strong>uVar_SMTPSendEmailsTo_FromError</strong>” A List of email address to email the error file. (<a href="mailto:Snikkhah@Live.ca">Snikkhah@Live.ca</a> ; <a href="mailto:YourEmail@YourBox.Com">YourEmail@YourBox.Com</a> )<br />
14. “<strong>uVar_SourceConStr_ConnectionString</strong>” The Excel connection string<br />
E.g. Provider=Microsoft.Jet.OLEDB.4.0;Data Source = C:\SSIS\LoopingThroughExcelFilesAndSheets\Sample\SampleExcelFile.xls;Extended Properties="EXCEL 8.0;HDR=Yes; IMEX=1;"<br />
15. “<strong>uVar_SourceConStr_DataSource</strong>” A variable that will have the file name and folder (See the first loop property).<br />
16. “<strong>uVar_SourceConStr_ExtendedProperties</strong>” Variables that have a part of the connection string hard coded (e.g. EXCEL 8.0;HDR=Yes; IMEX=1;)<br />
17. “<strong>uVar_SourceConStr_PersistSecurityInfo</strong>” Variables that have a part of the connection string hard coded<br />
18. “<strong>uVar_SourceConStr_Provider</strong>” Variables that have a part of the connection string hard coded<br />
19. “<strong>uVar_SourceFileExtention</strong>” This variable will define the file extension (e.g. *.xls).<br />
20. “<strong>uVar_SourceFileNameRenamed</strong>” Before moving a file to the backup folder we will have to rename and set the right backup folder in this variable.<br />
21. “<strong>uVar_SourceMultiTabExcel</strong>” Some Excel files have few sheets but only one of them contains the data so I am using this variable to set my package to pickup only on defined sheet name (FALSE = Single sheet , TRUE = Multiple sheet with the same data format). In this example it must be set to “TRUE”<br />
<br />
<br />
<span style="color: #3366ff;">Defining Connection Manager</span><br />
We need 4 connections, in this example I will not be setting the error and email section you can check my blog for that.<br />
<br />
1. Excel connection manager<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhSPLQ5XpWR7g__rXO5yW00rlixrS5UXvD5-SizVV7YQ0bUMj2FwWo0orquAuylAk-1A2UgIbg1Jua0GKtCwfxNygSpLD-L1xdwabzB0Y42GFFfDDWRQ5eM53uxxBjnQ5D3VkN-QiDX9f5K/s1600/New+Bitmap+Image+(4).bmp"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5457868599327984658" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhSPLQ5XpWR7g__rXO5yW00rlixrS5UXvD5-SizVV7YQ0bUMj2FwWo0orquAuylAk-1A2UgIbg1Jua0GKtCwfxNygSpLD-L1xdwabzB0Y42GFFfDDWRQ5eM53uxxBjnQ5D3VkN-QiDX9f5K/s400/New+Bitmap+Image+(4).bmp" style="cursor: hand; height: 334px; width: 400px;" /></a><br />
<br />
2. FF_DST----Error Output file for debugging<br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjPoCchGQs-yy_rLNTntdJLQFCwj7VOAtyzz-lkgaaNZDMmRL5Cu65Y3CId3ywgkssUIuAXJmuoO_zfszSMycc2UhpTkb97PIju0WmcEDrt4RWkXEPf5I2VtMyH16moH-3eHcEIzEqav33A/s1600/New+Bitmap+Image+(5).bmp"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5457868070862720578" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjPoCchGQs-yy_rLNTntdJLQFCwj7VOAtyzz-lkgaaNZDMmRL5Cu65Y3CId3ywgkssUIuAXJmuoO_zfszSMycc2UhpTkb97PIju0WmcEDrt4RWkXEPf5I2VtMyH16moH-3eHcEIzEqav33A/s400/New+Bitmap+Image+(5).bmp" style="cursor: hand; height: 214px; width: 400px;" /></a> <br />
<br />
3. SQLServerDestinationConnection<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjZfN6cNeZbWcNtYQaZnH1F5UKcbl7MMa0-wtFqZTNETn3m0Wfz-yqgxR5VPU8MkjGZtzahb5lncSUFkKDwUgttNdowctCRcXHx8hmSSTOPEVhaJTG0fFqK-7UYM_m1e1DKYKkQEVBtIuaw/s1600/New+Bitmap+Image+(6).bmp"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5457867675649613618" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjZfN6cNeZbWcNtYQaZnH1F5UKcbl7MMa0-wtFqZTNETn3m0Wfz-yqgxR5VPU8MkjGZtzahb5lncSUFkKDwUgttNdowctCRcXHx8hmSSTOPEVhaJTG0fFqK-7UYM_m1e1DKYKkQEVBtIuaw/s400/New+Bitmap+Image+(6).bmp" style="cursor: hand; height: 347px; width: 400px;" /></a><br />
<br />
4. SMTP Connection Manager For Error messages<br />
<br />
<br />
<br />
<br />
<br />
<span style="color: #3366ff;">Adding Object (controls) in the Control Flow</span>Now we can add the objects as seen in the “Over all View”<br />
1. SCR---- Making the "ToBeProcessed Folder"<br />
<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj81fYmjBmNdn7PvJs40DqK0hcQvaSoD1fiXDIT8tnHdls3si5tRFYmSo2JEI0WbUj31H0x6BGy-O1kiCh9axU2WSxTB6Ae_i4JvbTzkTtNQsvR7d7gVntYvqgv7-r3YwX4Nz_x76ZRyU__/s1600/New+Bitmap+Image+(7).bmp"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5457867292648600610" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj81fYmjBmNdn7PvJs40DqK0hcQvaSoD1fiXDIT8tnHdls3si5tRFYmSo2JEI0WbUj31H0x6BGy-O1kiCh9axU2WSxTB6Ae_i4JvbTzkTtNQsvR7d7gVntYvqgv7-r3YwX4Nz_x76ZRyU__/s400/New+Bitmap+Image+(7).bmp" style="cursor: hand; height: 138px; width: 400px;" /></a><br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjhz1hhBsLVI98ZxcCLWGoDixIFKNDaqzS8EnEg6h3g4IEWzKBmaLdMlkxPl51S1QDiGSsJRRp8IAquGiBrDGzfTOLMWW8JLCBkqn95-ohDEfsAfKuJd-V_dwNDhr8b-Jsc5v4Ntf5F8nUQ/s1600/New+Bitmap+Image+(8).bmp"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5457867041023224626" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjhz1hhBsLVI98ZxcCLWGoDixIFKNDaqzS8EnEg6h3g4IEWzKBmaLdMlkxPl51S1QDiGSsJRRp8IAquGiBrDGzfTOLMWW8JLCBkqn95-ohDEfsAfKuJd-V_dwNDhr8b-Jsc5v4Ntf5F8nUQ/s400/New+Bitmap+Image+(8).bmp" style="cursor: hand; height: 264px; width: 400px;" /></a><br />
<br />
In this section the customer can redirect the source folder by entering the folder full path name in the “uVar_FolderNameOfFilesToBeProcessed” variable, if not the system will pick up the default name “ToBeProcessed”.<br />
<br />
2. SCR---- Making the backup folder string from BackupFolderName Variable (Dynamic Backup folder yyyy-mm-dd-hh-mm-ss)<br />
<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh5jNxvxCzG6tYh9340Tc2tvauETWBWgBhARak5Pi3Y0mBmQPT-s7HKJuHFx0DaUWdrGaMeR9kJlnb1zkXAqXmnxdfA08YG23PGaXZyz9Zu9WeSNcmwUT_UnTXsiBNjPq7eNDROyfPtYh4d/s1600/New+Bitmap+Image+(9).bmp"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5457866697280111570" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh5jNxvxCzG6tYh9340Tc2tvauETWBWgBhARak5Pi3Y0mBmQPT-s7HKJuHFx0DaUWdrGaMeR9kJlnb1zkXAqXmnxdfA08YG23PGaXZyz9Zu9WeSNcmwUT_UnTXsiBNjPq7eNDROyfPtYh4d/s400/New+Bitmap+Image+(9).bmp" style="cursor: hand; height: 121px; width: 400px;" /></a><br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh0Gb0yrwLjcYmGKcNIW-SQuTdaCRy1rarWhwkTZ2OuaYrEML6MWI9hxJdvFwHHrXwJ44BjofXJwZFRYLbtv-_ZpnNJP5Pb9gkQNBefi10wpv5pEXULggtuQmxD7tpmaWWkqDvco6PRrEvw/s1600/New+Bitmap+Image+(10).bmp"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5457866498266255714" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh0Gb0yrwLjcYmGKcNIW-SQuTdaCRy1rarWhwkTZ2OuaYrEML6MWI9hxJdvFwHHrXwJ44BjofXJwZFRYLbtv-_ZpnNJP5Pb9gkQNBefi10wpv5pEXULggtuQmxD7tpmaWWkqDvco6PRrEvw/s400/New+Bitmap+Image+(10).bmp" style="cursor: hand; height: 355px; width: 400px;" /></a><br />
<br />
Same settings again the customer can define the backup folder in the variable “uVar_BackupMainFolderPath”, if not the Package will pick the default folder name “BackUpFolder”. The other thing that happens in this script is that the current backup folder name string is been made,<br />
<br />
e.g. C:\SSIS\LoopingThroughExcelFilesAndSheets\BackUp\2010-04\2010-04-10-141000.<br />
<br />
3. FSYS----Creating the backup folder from BackupFolderName<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhOnWq1aSfQrYqW0Cndx3ofVoBKDGmaFIDc3iPEGgn3L7qq05zrFkJgecOMdsACjzQhmDDmOGh6bNcHuuAakMzvgkSCX8T7UVQDcps4TN3Zs2CmjCseub3uJ57LtPDB6-qKTIDvZBYIW-ZT/s1600/New+Bitmap+Image+(11).bmp"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5457866258720315826" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhOnWq1aSfQrYqW0Cndx3ofVoBKDGmaFIDc3iPEGgn3L7qq05zrFkJgecOMdsACjzQhmDDmOGh6bNcHuuAakMzvgkSCX8T7UVQDcps4TN3Zs2CmjCseub3uJ57LtPDB6-qKTIDvZBYIW-ZT/s400/New+Bitmap+Image+(11).bmp" style="cursor: hand; height: 162px; width: 400px;" /></a><br />
<br />
This will make the backup folder from the “uVar_BackupActiveFolder” which will be C:\SSIS\LoopingThroughExcelFilesAndSheets\BackUp\2010-04\2010-04-10-141000.<br />
<br />
4. SCR--- Make the correct path and file name for the Error Text File<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiuSWJDEoTu185Qd7fLZuSe1JqnspWd4-S2WscncBrGd5hwh9UGtRp0j1d4gbPm5x3ObuUj-PIDV9mKDyiC30t2KWdLQHycHLJKcVB96PcbH1cBKDGvn4n_ISvQd8BPOR_g3wPkT1aOOzKb/s1600/New+Bitmap+Image+(12).bmp"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5457866004013171858" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiuSWJDEoTu185Qd7fLZuSe1JqnspWd4-S2WscncBrGd5hwh9UGtRp0j1d4gbPm5x3ObuUj-PIDV9mKDyiC30t2KWdLQHycHLJKcVB96PcbH1cBKDGvn4n_ISvQd8BPOR_g3wPkT1aOOzKb/s400/New+Bitmap+Image+(12).bmp" style="cursor: hand; height: 118px; width: 400px;" /></a><br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhAklf_LY63eOq-E-kTutXP_gSTW2cr9K9tKWIUk9Uc9opMJINC0KllzC3vN0L_aCWVAPLuDFial7YWkJOSPjR4S4lgKE9vvw75crWf89ZG-HSKy1ISct2ZUlUUPf4Qkydjj0A75_sVSI8u/s1600/New+Bitmap+Image+(13).bmp"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5457865847326390066" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhAklf_LY63eOq-E-kTutXP_gSTW2cr9K9tKWIUk9Uc9opMJINC0KllzC3vN0L_aCWVAPLuDFial7YWkJOSPjR4S4lgKE9vvw75crWf89ZG-HSKy1ISct2ZUlUUPf4Qkydjj0A75_sVSI8u/s400/New+Bitmap+Image+(13).bmp" style="cursor: hand; height: 205px; width: 400px;" /></a><br />
<br />
What I am doing is making a file name string that has, the date and time, ETL Name, etc... This will be needed it an error accurse<br />
<br />
5. SQL----Truncate the destination Table<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi3qKfDMwKg-g7mKoEHFcqeF4hyYkAqzrlg2c0lsN80Z4lyoK4JeQXN5M7ixVi5XMAGuQGclG_bjG39vQodCT5E2Qo6tbLabDMWaUnEnSKHUanatXpDqzGi0qrqzqlJSTCZrV_Reca2QVso/s1600/New+Bitmap+Image+(14).bmp"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5457865542645356418" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi3qKfDMwKg-g7mKoEHFcqeF4hyYkAqzrlg2c0lsN80Z4lyoK4JeQXN5M7ixVi5XMAGuQGclG_bjG39vQodCT5E2Qo6tbLabDMWaUnEnSKHUanatXpDqzGi0qrqzqlJSTCZrV_Reca2QVso/s400/New+Bitmap+Image+(14).bmp" style="cursor: hand; height: 175px; width: 400px;" /></a><br />
<br />
<br />
6. FLC - Looping through each Excel file - Finding the Data Source<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhvLxQ7LWTEMS_HeYv_1kDC15QmpJKUVTmq7MIjUW3KAPA27bZzM4RCfL84r1eQsgoEYFEPsYYUpYbyAQ-ra8QBnaErrlRg4jjF06XyNJmvL7OrEntZIBs_w1dy-i37B1vV4GilqO-gDtuz/s1600/New+Bitmap+Image+(15).bmp"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5457865182975264242" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhvLxQ7LWTEMS_HeYv_1kDC15QmpJKUVTmq7MIjUW3KAPA27bZzM4RCfL84r1eQsgoEYFEPsYYUpYbyAQ-ra8QBnaErrlRg4jjF06XyNJmvL7OrEntZIBs_w1dy-i37B1vV4GilqO-gDtuz/s400/New+Bitmap+Image+(15).bmp" style="cursor: hand; height: 351px; width: 400px;" /></a><br />
<br />
By using the variable “uVar_FolderNameOfFilesToBeProcessed” the first loop will be looping through the folder and sub folder to find *.xls files “name and folder” and each file folder and name will be saved in the “uVar_SourceConStr_DataSource” e.g. C:\SSIS\LoopingThroughExcelFilesAndSheets\ToBeProcessed\AnyExcelFileName.xls<br />
<br />
7. SCR - Make the connection string for each file<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjdNu6UOOxewm144qyXHFlsJoG-AX2wozj4KKfRymtJDiUo46C0k3ZejDyViiaoRAcDZzNNBHkOWNOYnDB23y3ipnqgR_HRF8nmkpEtfYRRcWPDovzmaW-BHTP-eO61HUSxsrCRhFsvbboz/s1600/New+Bitmap+Image+(16).bmp"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5457864901258838610" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjdNu6UOOxewm144qyXHFlsJoG-AX2wozj4KKfRymtJDiUo46C0k3ZejDyViiaoRAcDZzNNBHkOWNOYnDB23y3ipnqgR_HRF8nmkpEtfYRRcWPDovzmaW-BHTP-eO61HUSxsrCRhFsvbboz/s400/New+Bitmap+Image+(16).bmp" style="cursor: hand; height: 63px; width: 400px;" /></a><br />
<br />
The settings are...<br />
<strong>ReadOnlyVariable= </strong>uVar_SourceConStr_Provider, uVar_SourceConStr_DataSource, uVar_SourceConStr_PersistSecurityInfo, uVar_SourceConStr_ExtendedProperties, uVar_ExcelTabObjectName<br />
<strong>ReadWriteVariable</strong>= uVar_SourceConStr_ConnectionString<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh0r2KO2kr-wlLdT8dd-OIk6s_f8Sc48UZ6uui1I-TC8KyWM8eQtcyqy7M2ZMTWJU8FsC1rgZ_JILliW09SP-uBk36pyJhNu9gJzufeCLIL7CKhfCH8CPCllV7rYfzU8oYzmG9eiw86xapj/s1600/New+Bitmap+Image+(17).bmp"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5457864377212944946" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh0r2KO2kr-wlLdT8dd-OIk6s_f8Sc48UZ6uui1I-TC8KyWM8eQtcyqy7M2ZMTWJU8FsC1rgZ_JILliW09SP-uBk36pyJhNu9gJzufeCLIL7CKhfCH8CPCllV7rYfzU8oYzmG9eiw86xapj/s400/New+Bitmap+Image+(17).bmp" style="cursor: hand; height: 223px; width: 400px;" /></a><br />
<br />
As you know that each “Excel Connection manager” object can only point to one Excel file, so each time (On each First Loop) we have to set the “Excel Connection manager” to point to the current Excel file (“uVar_SourceConStr_DataSource”).<br />
<br />
8. SCR - Find Tabs in Source file (tabs Only Not "Named Ranged")<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhoqY-TjJL-p-kpOpPfY_HO_U99JoQCDs04CSigl2LBdsWQfyCJifxYaWbh5087DU_wCQCtVBTD3NPCFp7z908VXavpvZ4CQH2LtwUDZC3iDXHQoYlxiDTZET8i8_y7oD-JaChtQHduGzMi/s1600/New+Bitmap+Image+(18).bmp"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5457863921129950994" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhoqY-TjJL-p-kpOpPfY_HO_U99JoQCDs04CSigl2LBdsWQfyCJifxYaWbh5087DU_wCQCtVBTD3NPCFp7z908VXavpvZ4CQH2LtwUDZC3iDXHQoYlxiDTZET8i8_y7oD-JaChtQHduGzMi/s400/New+Bitmap+Image+(18).bmp" style="cursor: hand; height: 91px; width: 400px;" /></a><br />
<br />
Now we have the connection string in the “uVar_SourceConStr_ConnectionString” variable, by using that and this script we will be looping though each Sheet and finding that names of them, and finally saving them in the “uVar_ExcelTabObjectName” variable which is a SSIS Object variable.<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjn2bOvaVJN4ju84LahXAI62N6UpkAY4B4KDh2KizPxs8LfeRrdZTs1q540OR0q2ygATNpzTBU2EUIAVYe9ZlxT28bUU3DrjE4zJX72e5uC5J8kcprS04tHMNHG0Af2DwxrTJ5ohyphenhyphenEegDzi/s1600/New+Bitmap+Image+(19).bmp"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5457863633723457666" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjn2bOvaVJN4ju84LahXAI62N6UpkAY4B4KDh2KizPxs8LfeRrdZTs1q540OR0q2ygATNpzTBU2EUIAVYe9ZlxT28bUU3DrjE4zJX72e5uC5J8kcprS04tHMNHG0Af2DwxrTJ5ohyphenhyphenEegDzi/s400/New+Bitmap+Image+(19).bmp" style="cursor: hand; height: 400px; width: 283px;" /></a><br />
<br />
Remember the variable “uVar_SourceMultiTabExcel” we use it if the Excel file sheet has multiple sheets but only one can be used, if the this is the scenario that you have your customer must not change the sheet name and the sheet name must be hard coded in the variable “uVar_ExcelActiveTabName” (e.g. Ticket Details$)<br />
<br />
9. FLC - Looping through each Excel file TAB<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhWgOkHSwJdE0VqGrQh00SQfsrJtewfiAW4oEwwm9gv7kuPCvJ5IpntJEf7tdbPKLcvxImziQJtsJzrbwBaLvHT9NHcay21JkrprbaHebPC6iGwfD4q6fVgpykE5DtGQ0rHtw6b-wI0WP1n/s1600/New+Bitmap+Image+(20).bmp"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5457863176769491410" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhWgOkHSwJdE0VqGrQh00SQfsrJtewfiAW4oEwwm9gv7kuPCvJ5IpntJEf7tdbPKLcvxImziQJtsJzrbwBaLvHT9NHcay21JkrprbaHebPC6iGwfD4q6fVgpykE5DtGQ0rHtw6b-wI0WP1n/s400/New+Bitmap+Image+(20).bmp" style="cursor: hand; height: 316px; width: 400px;" /></a><br />
<br />
The second loop will loop through the “uVar_ExcelTabObjectName” variable which contains the list of sheet names of the current Excel file.<br />
<br />
10. DFT---- MAIN ETL (E and T Only)<br />
<br />
11. Ex_SRC---- Connecting to Excel file to extract Data<br />
How is this section working? The sheet name is in the “uVar_ExcelActiveTabName” which is set to the this object and its Connection is using the “Excel Connection Manager” which is set to the current Excel file by the variable “uVar_SourceConStr_ConnectionString” (That it’s been set to DelayValidation = TRUE)<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhppoEmZhSo5umNFi59nFX812kTThq8qul9YiA3tQ2ZIuSvZoU4rFWDOSAUp-iNDx_h2FgpESBHEHrBiPkM73V43t6-Enmc6_o-iDR9s3qkKprleKYAYCAaLmhBhC4kPNmLExpbpK0vV9hL/s1600/New+Bitmap+Image+(21).bmp"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5457862779733811778" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhppoEmZhSo5umNFi59nFX812kTThq8qul9YiA3tQ2ZIuSvZoU4rFWDOSAUp-iNDx_h2FgpESBHEHrBiPkM73V43t6-Enmc6_o-iDR9s3qkKprleKYAYCAaLmhBhC4kPNmLExpbpK0vV9hL/s400/New+Bitmap+Image+(21).bmp" style="cursor: hand; height: 303px; width: 400px;" /></a><br />
<br />
<br />
12. OLE_DST----SQL SERVER Destination table<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiGQ9Ed9RbUX457cf4HnQCLxIaszgr6qsQrsvhat_nTdoz-4219sPHeyryf_mtfcbO0hpXEBy3lWI_WEH_fLufiB7iM7kKiOncAU-GIYHIzHq8_9iILt_vCMQ7flprJhul6bqZEwc2cFs1W/s1600/New+Bitmap+Image+(22).bmp"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5457861290429145538" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiGQ9Ed9RbUX457cf4HnQCLxIaszgr6qsQrsvhat_nTdoz-4219sPHeyryf_mtfcbO0hpXEBy3lWI_WEH_fLufiB7iM7kKiOncAU-GIYHIzHq8_9iILt_vCMQ7flprJhul6bqZEwc2cFs1W/s400/New+Bitmap+Image+(22).bmp" style="cursor: hand; height: 124px; width: 400px;" /></a><br />
<br />
Same story as the previous step except it’s been set for the destination table in SQL server<br />
<br />
13. SCR--- Rename file names with the format of "yyyy-mm-dd-hhmmssff"<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjjIWMXa_xmOpXw1LAAEirmFHLojUjkhM5YBygTnplxby4Zy2wFZR9SGdpMSiYB7exYvvTkZIRZLhMNr_sDeTzP7Bz3FC_cDT8Q-292tF9dshuyy-b8Vq75beD7OtAWaaungQG1D9S7-byz/s1600/New+Bitmap+Image+(23).bmp"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5457861135147717266" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjjIWMXa_xmOpXw1LAAEirmFHLojUjkhM5YBygTnplxby4Zy2wFZR9SGdpMSiYB7exYvvTkZIRZLhMNr_sDeTzP7Bz3FC_cDT8Q-292tF9dshuyy-b8Vq75beD7OtAWaaungQG1D9S7-byz/s400/New+Bitmap+Image+(23).bmp" style="cursor: hand; height: 102px; width: 400px;" /></a><br />
<br />
Again making a string that will rename the current file that has been processed and save it in the current backup folder<br />
<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgBLXLNG6KuPC2o0-eT9FfzLldXteAn8mj2qt0cCN1R3dHuUq8nqZUhnbpdbIPVST-EW8jesNKAhrEVzh9jkqz0vnybJh_qwAl6CjpiFQ2LO4kdQ_1Uq3A2UWQou1I1uAxpnNljU3HYsL-k/s1600/New+Bitmap+Image+(24).bmp"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5457860987686946834" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgBLXLNG6KuPC2o0-eT9FfzLldXteAn8mj2qt0cCN1R3dHuUq8nqZUhnbpdbIPVST-EW8jesNKAhrEVzh9jkqz0vnybJh_qwAl6CjpiFQ2LO4kdQ_1Uq3A2UWQou1I1uAxpnNljU3HYsL-k/s400/New+Bitmap+Image+(24).bmp" style="cursor: hand; height: 400px; width: 338px;" /></a><br />
<br />
<br />
14. FSYS---- Moving processed files to the "Backup Active Folder"<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEisspoGvK_XMeZRXJo7uImiSfUDUOgZMhhVLY4SWT2elC_9IL89N866qfY6-nG4XZzIDUa0Mc7oku9vhVbJC40UsHkUkg7HNnovhn_ihrdizt5JuJ6R3P0FIqHL0neYmhEZ05QZtec0iNvk/s1600/New+Bitmap+Image+(25).bmp"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5457860767733048418" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEisspoGvK_XMeZRXJo7uImiSfUDUOgZMhhVLY4SWT2elC_9IL89N866qfY6-nG4XZzIDUa0Mc7oku9vhVbJC40UsHkUkg7HNnovhn_ihrdizt5JuJ6R3P0FIqHL0neYmhEZ05QZtec0iNvk/s400/New+Bitmap+Image+(25).bmp" style="cursor: hand; height: 156px; width: 400px;" /></a><br />
<br />
Moving the processed file to that final backup folder<br />
<br />
15. End of the first Loop, Loop for next file<br />
<br />
<span style="color: #3366ff;">Testing Scenarios </span><br />
<br />
Before testing make sure that you have set the variables according to the snap shot of the variables that I have and check the “Criteria”.<br />
<br />
<em>Scenario 1:</em><br />
Excel files with multiple sheets (all sheet s have the same data format)<br />
1- Set the Variable “uVar_SourceMultiTabExcel” = TRUE<br />
2- Copy Excel files to the ToBeProcessed folder<br />
3- Run the package<br />
4- Check the destination table<br />
5- Check the backup folder and find the current date time folder and see if the file have been moved from the ToBeProcessed folder to the new current backup folder<br />
<br />
<em>Scenario 2:</em><br />
Excel files with single data sheets (the data sheet must be hard coded)<br />
1- In the sample file do not rename the data sheet (do to rename it to Tab$, keep it as it is because this will be the fixed sheet name that will be in the Excel file)<br />
2- Set the Variable “uVar_SourceMultiTabExcel” = False<br />
3- Name the “uVar_ExcelActiveTabName” as the sheet name agreed with the customer e.g. “Sales$”<br />
4- Copy Excel files to the ToBeProcessed folder<br />
5- Run the package<br />
6- Check the destination table<br />
7- Check the backup folder and find the current date time folder and see if the file have been moved from the ToBeProcessed folder to the new current backup folderShahriar Nikkhah (Nik)http://www.blogger.com/profile/17215555681462843010noreply@blogger.com37tag:blogger.com,1999:blog-5534374197067462925.post-66304948389057998152010-03-17T10:10:00.009-04:002010-03-17T10:25:10.766-04:00List of SQL table names and their field namesSome developers may want to get the list of table names and their field names, without scripting the database and at the same time they would like to copy the data to a new database with maybe new names and etc...
<br />The script will provide the list of table and its fields and will insert into a the new db
<br />
<br />Instruction:
<br />· Change à SET @NewDBName = 'NewDB’ and rename to the new database name
<br />· Find “First and second format” for the format that you require (or you can use the other variable that may help you to provide the query that you need such as the “@NewFieldName” , “@FieldName”)
<br />· Run script
<br />· Copy the results in to a new query page
<br />· Run script
<br />
<br />DECLARE @FieldName AS nVarChar(max)
<br />DECLARE @NewDBName AS nVarChar(100)
<br />DECLARE @NewFieldName AS nVarChar(max)
<br />DECLARE @NewFieldString AS nVarChar(max)
<br />
<br />SET @NewDBName = 'NewDB'
<br />
<br />SET @FieldName = ''
<br />SET @NewFieldName = ''
<br />SET @NewFieldString = ''
<br />
<br />DECLARE @tblName AS nVarChar(100)
<br />DECLARE @object_ID AS int
<br />
<br />DECLARE LoopCursor CURSOR FOR
<br />SELECT tbl.Name , tbl.object_ID
<br />FROM sys.objects AS tbl
<br />WHERE tbl.TYPE = 'u' and tbl.name = 'users'
<br />ORDER BY tbl.Name
<br />
<br />OPEN LoopCursor
<br />FETCH NEXT FROM LoopCursor INTO @tblName , @object_ID
<br />
<br />IF @@FETCH_STATUS <> 0
<br />PRINT ' <<none>>'
<br />
<br />WHILE @@FETCH_STATUS = 0
<br />BEGIN
<br />
<br /><blockquote>
<br />SELECT @FieldName = @FieldName + '[' + Cast (Col.Name AS nVarChar) + '], '
<br />, @NewFieldName= @NewFieldName + '[' + Cast (Col.Name AS nVarChar) + '_NEW], '
<br />, @NewFieldString = @NewFieldString
<br />+ '[' + Cast (Col.Name AS nVarChar) + '] AS ' + '[' + Cast (Col.Name AS nVarChar) + '_NEW], '
<br />FROM sys.columns Col
<br />WHERE Col.object_ID = @object_ID
<br />SET @NewFieldString = Left (@NewFieldString , Len(@NewFieldString) - 1 )
<br />----------------------------------------------------------------------------------------------
<br />----------------------------------------------------
<br />-- First format ----------------------------------
<br />-- PRINT ' SELECT ' + @NewFieldString
<br />-- + ' INTO '+ '[' + @NewDBName + '].dbo.[' + @tblName + '_NEW]'
<br />-- + ' FROM [' + @tblName + ']'
<br />----------------------------------------------------
<br />-- Second format ----------------------------------
<br />PRINT ' SELECT ' + @FieldName
<br />+ ' INTO '+ '[' + @NewDBName + '].dbo.[' + @tblName + ']'
<br />+ ' FROM [' + @tblName + ']'
<br />----------------------------------------------------
<br />----------------------------------------------------------------------------------------------
<br />FETCH NEXT FROM LoopCursor INTO @tblName , @object_ID
<br /></blockquote>
<br />;
<br />END
<br />;
<br />CLOSE LoopCursor
<br />;
<br />DEALLOCATE LoopCursor
<br />;Shahriar Nikkhah (Nik)http://www.blogger.com/profile/17215555681462843010noreply@blogger.com0tag:blogger.com,1999:blog-5534374197067462925.post-66651451830180987492010-01-07T15:02:00.025-05:002010-03-17T10:15:21.273-04:00Emailing Error log file as attachments in SSISTo email Error log files as an attachment please check the post “<em>Making Dynamic Backup Folders</em>” first<br /><br /><strong>Over all View</strong><br /><br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEisY6_9uGqTz5i3y6Iu4dSh83_d_jNUr8Yh2Lo61T8KxQM7yUSbTHQ0OlRn-0MseykPw6tJaz36cMf4E4fnzZjVoy8CZcr60WozBzWVdZn93o0icv2-9Fs0Zdjge4p13ZIJzAuFTe2H_sDh/s1600-h/New+Bitmap+Image.bmp"><img id="BLOGGER_PHOTO_ID_5424092162287211858" style="WIDTH: 400px; CURSOR: hand; HEIGHT: 312px" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEisY6_9uGqTz5i3y6Iu4dSh83_d_jNUr8Yh2Lo61T8KxQM7yUSbTHQ0OlRn-0MseykPw6tJaz36cMf4E4fnzZjVoy8CZcr60WozBzWVdZn93o0icv2-9Fs0Zdjge4p13ZIJzAuFTe2H_sDh/s400/New+Bitmap+Image.bmp" border="0" /></a><br /><br />I am sure that you can find other ways to do this; the concept is to give an idea<br /><strong>Step 1 - </strong><br />Define few variables in the system<br /><br /><div><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiGXZAyYr4h3677xN0Y4qL7VULQKLZchE74kmJrwMpQua-beZ8Zf3haeCHH1lW3LXcdajtZExjB23PlNn8o1oSPpo9O27ZQNFrq1M54TbSEX6YkCsQup3kHhL_OtwFc2VgnRwlCiCcpk5G_/s1600-h/New+Bitmap+Image+(2).bmp"><img id="BLOGGER_PHOTO_ID_5424092094490720498" style="WIDTH: 400px; CURSOR: hand; HEIGHT: 129px" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiGXZAyYr4h3677xN0Y4qL7VULQKLZchE74kmJrwMpQua-beZ8Zf3haeCHH1lW3LXcdajtZExjB23PlNn8o1oSPpo9O27ZQNFrq1M54TbSEX6YkCsQup3kHhL_OtwFc2VgnRwlCiCcpk5G_/s400/New+Bitmap+Image+(2).bmp" border="0" /></a><br /><br />1. “uVar_BackupActiveFolder” when the package runs it points to a new folder each time which is the active backup folder and that is where we keep the backup and error files for that transaction for that ETL run.<br />2. “uVar_BackupMainFolderPath” some customers prefer to have the backup folder in another server so basically we allow them to have a choice, by adding this variable (See the script section for more details).<br />3. “uVar_MainFolderPath” This is where your main Folder path is basically it’s the main root of your package and its other folders (e.g. backup, Error folder, sample folder, etc...).<br />4. “uVar_ErrorOutPutFile” this is the path and file name of the Error file, remember each time this path and file names changes so that we can keep a Backup of each error log<br />5. “uVar_SMTPEmailServerName” you can enter a IP number if you like<br />6. “uVar_SMTPEmailTo_FromError” the list of people or email groups, use multi emails</div><br /><div></div><div>Once adding the objects in the control flow...</div><br /><div><strong>Step 2 –<br /></strong>Adding connection Objects first<br />First for the Error file</div><br /><div><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjcx4bOLQdsXPDylDb_tGStCNsb3GcAX5C66kuaHdg6g4TW7k_GhG-cDuwCXoC27N4yTsAkzWsGdvKtjmygb0gyxnZCHI65hnekUWKo9dQvLKiFSdQMsP7kA0iA1h3iEqa5h-kM1I62hk0U/s1600-h/New+Bitmap+Image+(3).bmp"><img id="BLOGGER_PHOTO_ID_5424092034362773906" style="WIDTH: 295px; CURSOR: hand; HEIGHT: 400px" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjcx4bOLQdsXPDylDb_tGStCNsb3GcAX5C66kuaHdg6g4TW7k_GhG-cDuwCXoC27N4yTsAkzWsGdvKtjmygb0gyxnZCHI65hnekUWKo9dQvLKiFSdQMsP7kA0iA1h3iEqa5h-kM1I62hk0U/s400/New+Bitmap+Image+(3).bmp" border="0" /></a></div><br /><div>Second for the SMTP object<br /></div><div><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEggQIgU0ReYhhmveSS_70LQi4-jkkFqCNOxSJpGgh8pV-9Jhbvq9mBNf4v2W4mp-ZCgyqGaAkS5FmJhH3Z8_EjSI4jfcH1_E0Y3cvWdCc-oBQyMRexZkxTD32jN_wRBVQOHqYnGdS7iCEUq/s1600-h/New+Bitmap+Image+(4).bmp"><img id="BLOGGER_PHOTO_ID_5424091963201855314" style="WIDTH: 400px; CURSOR: hand; HEIGHT: 199px" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEggQIgU0ReYhhmveSS_70LQi4-jkkFqCNOxSJpGgh8pV-9Jhbvq9mBNf4v2W4mp-ZCgyqGaAkS5FmJhH3Z8_EjSI4jfcH1_E0Y3cvWdCc-oBQyMRexZkxTD32jN_wRBVQOHqYnGdS7iCEUq/s400/New+Bitmap+Image+(4).bmp" border="0" /></a><br /><br /><strong>Step 3 –</strong><br />Starting from Script Task “SCR--- Make the correct path and file name for the Error Text File”<br /><strong>Note : </strong>The first 2 script have full details and explanation in “<em>Making Dynamic Backup Folders</em>”<br /></div><div><div><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg9Ra8_Ae2lg4JA185TDKp6tDLVMMvEFVsnanrS87caGCUkEuEBHtU5pcMdsmwo5RuWgD7RxVa4cfd53DS5hmg9veRFMDd28KZEetVTAycBawKzVxxN7CBCqPhl8dSpNQ6xPaneYJoXWwXt/s1600-h/New+Bitmap+Image+(5).bmp"><img id="BLOGGER_PHOTO_ID_5424091877400079058" style="WIDTH: 400px; CURSOR: hand; HEIGHT: 157px" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg9Ra8_Ae2lg4JA185TDKp6tDLVMMvEFVsnanrS87caGCUkEuEBHtU5pcMdsmwo5RuWgD7RxVa4cfd53DS5hmg9veRFMDd28KZEetVTAycBawKzVxxN7CBCqPhl8dSpNQ6xPaneYJoXWwXt/s400/New+Bitmap+Image+(5).bmp" border="0" /></a><br /><br />In the <strong>Design Script ...</strong><br /><br /><div><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhvuyNeFcqYX6Iu3Hi02pGlsyxOBXlVyiRXhcwnPxObefOH0ivzePEtQPP9qqoxVSP-ROfHMjaYRfRJm8zgKgBbedZa0EF-tyDzvDmaPC3PLg_VQ1kJirj3rsryoqDy-EaZvTFSpzIeuPcP/s1600-h/New+Bitmap+Image+(6).bmp"><img id="BLOGGER_PHOTO_ID_5424091821263365234" style="WIDTH: 400px; CURSOR: hand; HEIGHT: 194px" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhvuyNeFcqYX6Iu3Hi02pGlsyxOBXlVyiRXhcwnPxObefOH0ivzePEtQPP9qqoxVSP-ROfHMjaYRfRJm8zgKgBbedZa0EF-tyDzvDmaPC3PLg_VQ1kJirj3rsryoqDy-EaZvTFSpzIeuPcP/s400/New+Bitmap+Image+(6).bmp" border="0" /></a><br /><br /><strong>Step 4 –<br /></strong>Now we have to make a object that will fail, “SCR--- Make me an Error”<br /><br /><div><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEge66jn5-5FsnrfhC5esy4-lOFX16kiyHDTRqlZ92plNPBpXeRdPl2d9CtEL-Z_bWly8EYx01tuUqq39_Nsl6kOsjrtFTO9i6cYCqwYA4OkWIF_OdHawgZtZeSvovWh1cID6K0u6584MtHG/s1600-h/New+Bitmap+Image+(7).bmp"><img id="BLOGGER_PHOTO_ID_5424091746472989074" style="WIDTH: 400px; CURSOR: hand; HEIGHT: 165px" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEge66jn5-5FsnrfhC5esy4-lOFX16kiyHDTRqlZ92plNPBpXeRdPl2d9CtEL-Z_bWly8EYx01tuUqq39_Nsl6kOsjrtFTO9i6cYCqwYA4OkWIF_OdHawgZtZeSvovWh1cID6K0u6584MtHG/s400/New+Bitmap+Image+(7).bmp" border="0" /></a> </div><div></div><br /><div><strong>Step 5 –<br /></strong>Now Email settingsIn the executable combo-box (drop down list) select the main root of the package, and then select OnError, drag and drop the Objects needed from the ToolBox. (See next figure)<br /></div><br /><div><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEibGMMpeGKCVWtHXBmLPqHSKQeQ9NPQO10HPV_b3M8yruVTxDFeMe3NFF15G5ihrb3NiJnS_NFwQhqm9UPLmjcYOiiDGmC1GdWDg0Tjtvszb9c4lEnCv0Ht95TyKhOdTWlNP3K2D9mF1G_P/s1600-h/New+Bitmap+Image+(8).bmp"><img id="BLOGGER_PHOTO_ID_5424091674980826290" style="WIDTH: 400px; CURSOR: hand; HEIGHT: 237px" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEibGMMpeGKCVWtHXBmLPqHSKQeQ9NPQO10HPV_b3M8yruVTxDFeMe3NFF15G5ihrb3NiJnS_NFwQhqm9UPLmjcYOiiDGmC1GdWDg0Tjtvszb9c4lEnCv0Ht95TyKhOdTWlNP3K2D9mF1G_P/s400/New+Bitmap+Image+(8).bmp" border="0" /></a><br /></div><br /><div><strong>Step 6 – </strong><br />Open the DFT and make drag and drop the objects from the ToolBox.</div><div><br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiaySRe5bEamJKKzXTTHYHqWr_Bdhs1LAt4BZJN3iO8WJ0q0Tb9r76Y8wGCGzrCrRBSM91JiQowPPA45vGlJm3CCgzB9pRGGGfQHIR6UTxC9_fwM8GC_9lSIgJEt91UudFNRg2ageaRMSN9/s1600-h/New+Bitmap+Image+(9).bmp"><img id="BLOGGER_PHOTO_ID_5424091600235930354" style="WIDTH: 400px; CURSOR: hand; HEIGHT: 203px" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiaySRe5bEamJKKzXTTHYHqWr_Bdhs1LAt4BZJN3iO8WJ0q0Tb9r76Y8wGCGzrCrRBSM91JiQowPPA45vGlJm3CCgzB9pRGGGfQHIR6UTxC9_fwM8GC_9lSIgJEt91UudFNRg2ageaRMSN9/s400/New+Bitmap+Image+(9).bmp" border="0" /></a><br /><br /><strong>Step 7 –</strong></div><div>Select the “SCR---Catch Error Output description” and set...</div><div></div><div><br /><div><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEghtsw-kqai_VuKv_RPdKuUn-dqdG-9ALL58ZGGxsdE2lVAXRLNL867VnMyOT5ylHOOJQ0N9mEscnfepkjUyZNHSR62RQBj6j5Bg6mPsvP9sHcbQ0lJaGONSFYyDOHeDyvRImEBMaQRZz5H/s1600-h/New+Bitmap+Image+(10).bmp"><img id="BLOGGER_PHOTO_ID_5424091531926609986" style="WIDTH: 400px; CURSOR: hand; HEIGHT: 390px" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEghtsw-kqai_VuKv_RPdKuUn-dqdG-9ALL58ZGGxsdE2lVAXRLNL867VnMyOT5ylHOOJQ0N9mEscnfepkjUyZNHSR62RQBj6j5Bg6mPsvP9sHcbQ0lJaGONSFYyDOHeDyvRImEBMaQRZz5H/s400/New+Bitmap+Image+(10).bmp" border="0" /></a><br /><br /><div><br /><br /><div><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi_v3T_qh9XJlbk0Fgc9L1UjP3rtLz9fGsFBu_tJ2P5DxfRNB9m97dvUV8XqirkjM-lZmNA76Nwj9G8K2j9oq4B8GcdVxoi-mK6WPhmfabZvYHnMurJkl3c4NLw7vJsuYJQzTswcoRIWCtc/s1600-h/New+Bitmap+Image+(12).bmp"><img id="BLOGGER_PHOTO_ID_5424091363718197506" style="WIDTH: 400px; CURSOR: hand; HEIGHT: 284px" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi_v3T_qh9XJlbk0Fgc9L1UjP3rtLz9fGsFBu_tJ2P5DxfRNB9m97dvUV8XqirkjM-lZmNA76Nwj9G8K2j9oq4B8GcdVxoi-mK6WPhmfabZvYHnMurJkl3c4NLw7vJsuYJQzTswcoRIWCtc/s400/New+Bitmap+Image+(12).bmp" border="0" /></a><br /><br /><div><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhikuEvVXDfteuQDHM2lQyDbAt6Br-4oHsAzJRCfoYaItpWcmr9iwLvtQbwZgjUrpEGDRsLmdOvoLLB3elGYWYg2ICeJUpT3sUbfOGh2Zk_MtlvxTJhhLvvam-jSKOTXUN2jal4lCFGDa-F/s1600-h/New+Bitmap+Image+(13).bmp"><img id="BLOGGER_PHOTO_ID_5424091283237823506" style="WIDTH: 400px; CURSOR: hand; HEIGHT: 206px" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhikuEvVXDfteuQDHM2lQyDbAt6Br-4oHsAzJRCfoYaItpWcmr9iwLvtQbwZgjUrpEGDRsLmdOvoLLB3elGYWYg2ICeJUpT3sUbfOGh2Zk_MtlvxTJhhLvvam-jSKOTXUN2jal4lCFGDa-F/s400/New+Bitmap+Image+(13).bmp" border="0" /></a> </div><div><br /><strong>Step 8 –<br /></strong>Select the Flat file destination Object and set the connection manger to “FF_DST Error output File for debugging” and ...</div><div><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhaPi_GmqoRGYvpvn57ZEYDjqFOLT4QKDVHuKudZAKDDmZ1tRxLoRa0zd2u0Vwg0xUu1orQgmi_ZckrDPYdS2EXIYkgPbM92VMEbe7nfBe8Sc_sIvXDodqc_odOAsiDqI3T3Nh6bFoOYZPS/s1600-h/New+Bitmap+Image+(11).bmp"><img id="BLOGGER_PHOTO_ID_5424091438578776418" style="WIDTH: 400px; CURSOR: hand; HEIGHT: 219px" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhaPi_GmqoRGYvpvn57ZEYDjqFOLT4QKDVHuKudZAKDDmZ1tRxLoRa0zd2u0Vwg0xUu1orQgmi_ZckrDPYdS2EXIYkgPbM92VMEbe7nfBe8Sc_sIvXDodqc_odOAsiDqI3T3Nh6bFoOYZPS/s400/New+Bitmap+Image+(11).bmp" border="0" /></a><br /></div><div><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgfqv0ckpct0bKCIO6tB0lc8XuiCYxGRJTCjFpdMOXOlvSBhJAQ_Q8dWDMAMYOP6AaWzWIlg6PFwHZapqfsEEzmJx8bHPUQ28jbwrPPfmYECwIlJrxfrk34cgVhhtBccRPkvtZ3Ffrounxy/s1600-h/New+Bitmap+Image+(14).bmp"><img id="BLOGGER_PHOTO_ID_5424091177062011138" style="WIDTH: 400px; CURSOR: hand; HEIGHT: 179px" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgfqv0ckpct0bKCIO6tB0lc8XuiCYxGRJTCjFpdMOXOlvSBhJAQ_Q8dWDMAMYOP6AaWzWIlg6PFwHZapqfsEEzmJx8bHPUQ28jbwrPPfmYECwIlJrxfrk34cgVhhtBccRPkvtZ3Ffrounxy/s400/New+Bitmap+Image+(14).bmp" border="0" /></a><br /></div><div><br /></div><div><strong>Step 9 –<br /></strong>Back to the “SMT----Send E-Mail to Notify that we have an Error in the ET section” and its settings<br /></div><div><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhvHQGHRhvCNdzxNWELbtx5OkhtGTlt6FxL-2RBQUIqOQ2tM-Gbo47PoenNzYSiJ8_9Etl8vKSp_JSCSakdTvhCELnNsDO5KKEQTOeINA38niCBuej_WmnL287-n6NelAU76GdihHsi20K0/s1600-h/New+Bitmap+Image+(15).bmp"><img id="BLOGGER_PHOTO_ID_5424091050636371330" style="WIDTH: 400px; CURSOR: hand; HEIGHT: 97px" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhvHQGHRhvCNdzxNWELbtx5OkhtGTlt6FxL-2RBQUIqOQ2tM-Gbo47PoenNzYSiJ8_9Etl8vKSp_JSCSakdTvhCELnNsDO5KKEQTOeINA38niCBuej_WmnL287-n6NelAU76GdihHsi20K0/s400/New+Bitmap+Image+(15).bmp" border="0" /></a><br /><br /><strong>Testing Scenarios</strong></div><div><br /></div><div>Just run the package. </div></div></div></div></div></div></div></div>Shahriar Nikkhah (Nik)http://www.blogger.com/profile/17215555681462843010noreply@blogger.com1tag:blogger.com,1999:blog-5534374197067462925.post-16055108857882978462010-01-07T12:26:00.012-05:002010-01-07T12:37:01.032-05:00Making Dynamic Backup Folders In SSISTo make dynamic backup folders from SSIS you need to do is to follow the steps.<br />Over all View<br /><br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEioNvYYSr1hjkMtJAPGYtYIhOuegiYYmewgIh2CTvVhcleNkOOxrF22Rv5_oE6jb2kRUSamQswOUDuN1mQ0gsxbUOhYuQ1Oa6Z9qNAbizppWPkwLludW8H6B18KtZnPZg4U_lgP9rm6vNot/s1600-h/New+Bitmap+Image.bmp"><img id="BLOGGER_PHOTO_ID_5424051653999787666" style="WIDTH: 400px; CURSOR: hand; HEIGHT: 224px" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEioNvYYSr1hjkMtJAPGYtYIhOuegiYYmewgIh2CTvVhcleNkOOxrF22Rv5_oE6jb2kRUSamQswOUDuN1mQ0gsxbUOhYuQ1Oa6Z9qNAbizppWPkwLludW8H6B18KtZnPZg4U_lgP9rm6vNot/s400/New+Bitmap+Image.bmp" border="0" /></a><br />I am sure that you can find other ways to do this and maybe much simpler, but the concept of the blog is to give you the idea<br /><br /><em>First step is to define few variables in the system</em><br /><br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEifbEvej_PpPGJ0v3Dxtcb6yIMtfzC1kvySRvZ_HO3-XKlk4zi7bRxOYE5IZ9AGg3Ag4DP4Iff77KCcQIKyyOjduzQBbbNSdL5phyphenhyphenUjTqSVsg7pEjE9k7xPgNxSFgmiLiiStjqFtrlQ0LU7/s1600-h/New+Bitmap+Image+(2).bmp"><img id="BLOGGER_PHOTO_ID_5424051592070920002" style="WIDTH: 400px; CURSOR: hand; HEIGHT: 101px" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEifbEvej_PpPGJ0v3Dxtcb6yIMtfzC1kvySRvZ_HO3-XKlk4zi7bRxOYE5IZ9AGg3Ag4DP4Iff77KCcQIKyyOjduzQBbbNSdL5phyphenhyphenUjTqSVsg7pEjE9k7xPgNxSFgmiLiiStjqFtrlQ0LU7/s400/New+Bitmap+Image+(2).bmp" border="0" /></a><br />1. “uVar_BackupActiveFolder” when the package runs it points to a new folder each time which is the active backup folder and that is where we keep the backup and error files for that transaction for that ETL run.<br />2. “uVar_BackupMainFolderPath” some customers prefer to have the backup folder in another server so basically we allow them to have a choice, by adding this variable (See the script section for more details).<br />3. “uVar_MainFolderPath” This is where your main Folder path is basically it’s the main root of your package and its other folders (e.g. backup, Error folder, sample folder, etc...).Once adding the objects in the control flow...<br /><br /><em>First ---</em><br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiuA6JaOLQHUqr9pAEjTPpqP7rVsAL72L_0gTVcDMUWCS-Il-EoazxXNSstrohNkM3dT1rITRyDkJyHb214v7oKDuKeORqHA1j-L3qmVE0mBSmFCdTIFOtC2rvh4H4pwb0QNYq4Vs50eHE8/s1600-h/New+Bitmap+Image+(3).bmp"><img id="BLOGGER_PHOTO_ID_5424051524667123442" style="WIDTH: 400px; CURSOR: hand; HEIGHT: 333px" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiuA6JaOLQHUqr9pAEjTPpqP7rVsAL72L_0gTVcDMUWCS-Il-EoazxXNSstrohNkM3dT1rITRyDkJyHb214v7oKDuKeORqHA1j-L3qmVE0mBSmFCdTIFOtC2rvh4H4pwb0QNYq4Vs50eHE8/s400/New+Bitmap+Image+(3).bmp" border="0" /></a><br /><br /><em>Second –</em> go to the <strong>Design Scripts</strong> and add the script setting<br /><br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgSP1WtFGdt1cNAzr92x02GuisuS7c8fECzWA1FEFZN3kLllP3Lf8aHXD5w5GY-pFSMvXRUtDIfXoPrTO0JJYRp5isZ4U6t07UZGyfJfkU4W6bSwxJU_PyTZIiFVy39fSw6BNBOLrRXROyd/s1600-h/New+Bitmap+Image+(4).bmp"><img id="BLOGGER_PHOTO_ID_5424051438833570530" style="WIDTH: 400px; CURSOR: hand; HEIGHT: 337px" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgSP1WtFGdt1cNAzr92x02GuisuS7c8fECzWA1FEFZN3kLllP3Lf8aHXD5w5GY-pFSMvXRUtDIfXoPrTO0JJYRp5isZ4U6t07UZGyfJfkU4W6bSwxJU_PyTZIiFVy39fSw6BNBOLrRXROyd/s400/New+Bitmap+Image+(4).bmp" border="0" /></a><br />As you can see, the IF statement checks that if the variable is not empty, change the backup folder from the MainFolderPath to the New BackupMainFolderPath, this is when the customer wants to change the backup folder from the main root of the ETL to somewhere else. Check the Testing Scenarios.<br /><br /><em>Next step the FSYS</em><br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgKlDozGm9VHd2tPlrA5PsEs91AfBRUV_aE3DVmlALfLRt64RyA8n31sCBuMWnLWn1tEOZ5oGqEXQkfyidPEF8-DOjn3pDXIr0WBgkHgHOWJnQ8ZmUkLfQZxSF-OmSBEIILYFLsg_scyVmL/s1600-h/New+Bitmap+Image+(5).bmp"><img id="BLOGGER_PHOTO_ID_5424051294154816194" style="WIDTH: 400px; CURSOR: hand; HEIGHT: 225px" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgKlDozGm9VHd2tPlrA5PsEs91AfBRUV_aE3DVmlALfLRt64RyA8n31sCBuMWnLWn1tEOZ5oGqEXQkfyidPEF8-DOjn3pDXIr0WBgkHgHOWJnQ8ZmUkLfQZxSF-OmSBEIILYFLsg_scyVmL/s400/New+Bitmap+Image+(5).bmp" border="0" /></a><br /><br /><strong>Testing Scenarios<br /></strong>Once finishing the setting run the package, you will see....<br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg9h7SflY-1d0Jeyf3R7hCe45BIoo8ONtqxgZM_Xx_yQN6MR02MzAbxAS7Sighzg4pgkR09Gxfp55VYnHotGVRB1a32jlSRfQ-ZrRagsF-2tv6E94SwgbdykGBFSM6erCLTArYtxUHM6XCH/s1600-h/New+Bitmap+Image+(6).bmp"><img id="BLOGGER_PHOTO_ID_5424051192585110194" style="WIDTH: 400px; CURSOR: hand; HEIGHT: 260px" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg9h7SflY-1d0Jeyf3R7hCe45BIoo8ONtqxgZM_Xx_yQN6MR02MzAbxAS7Sighzg4pgkR09Gxfp55VYnHotGVRB1a32jlSRfQ-ZrRagsF-2tv6E94SwgbdykGBFSM6erCLTArYtxUHM6XCH/s400/New+Bitmap+Image+(6).bmp" border="0" /></a><br />Assuming that the ETL is been running for months and you customer decided to change the backup folder, you won’t need to change anything in the package just simply add the new backup path (e.g. C:\NewBackUp) to the “uVar_BackupMainFolderPath” Variable and run the package.<br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgrrGXp8oFx050sx0BhzXr29iHqBvNnTdXSMsmwhx6XMnCl25vk41rpmWEIlufejipmgwM7F6N5b4cIhiclIwJMfYe3vhzLPuEZqsiyaD8sYLB3RTaXArhTzehAKXoJgpsBE1vA436y8Lhg/s1600-h/New+Bitmap+Image+(7).bmp"><img id="BLOGGER_PHOTO_ID_5424051119170718674" style="WIDTH: 400px; CURSOR: hand; HEIGHT: 216px" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgrrGXp8oFx050sx0BhzXr29iHqBvNnTdXSMsmwhx6XMnCl25vk41rpmWEIlufejipmgwM7F6N5b4cIhiclIwJMfYe3vhzLPuEZqsiyaD8sYLB3RTaXArhTzehAKXoJgpsBE1vA436y8Lhg/s400/New+Bitmap+Image+(7).bmp" border="0" /></a><br /><br />In this case remember to add the package or ETL name in the variable<br />e.g. C:\NewBackup\MyETLnameShahriar Nikkhah (Nik)http://www.blogger.com/profile/17215555681462843010noreply@blogger.com1tag:blogger.com,1999:blog-5534374197067462925.post-9775563891814030472009-11-13T11:03:00.033-05:002009-12-14T16:01:41.331-05:00Coping files in SSIS<p>How to Copy Files in SSIS<br />To copy files in SSIS all you need to do is follow these simple steps.<br /><br />Overall View:<br /><br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi9gskwtC65ZYeSIFWkriaU33d_DGVy-J92K7rW3e6RnhOQ9UVEnYES5zmucwJLBamhkIlWeALbz4RETV7z2tf325kIb06WAcXPG9EIbr8lk1cYjSBYBvJJry_4WEkrx-IF4COHf7qPG_Km/s1600-h/New+Bitmap+Image.bmp"><img id="BLOGGER_PHOTO_ID_5403620794616209826" style="WIDTH: 400px; CURSOR: hand; HEIGHT: 169px" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi9gskwtC65ZYeSIFWkriaU33d_DGVy-J92K7rW3e6RnhOQ9UVEnYES5zmucwJLBamhkIlWeALbz4RETV7z2tf325kIb06WAcXPG9EIbr8lk1cYjSBYBvJJry_4WEkrx-IF4COHf7qPG_Km/s400/New+Bitmap+Image.bmp" border="0" /></a><br /><br /><span style="font-family:georgia;">The first step is to define few variables in the system</span>:<br /><br /><br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjdBTonShgscW-NxEHOMrGMAJ2Fz4hr5VlbECqLvKG9ox7x3bfy6M1wikjA7XLKLC08_dU5hVGW8BXeG33NENW-AM7W-dMjWN72sq55SYW6844WYzoXdar7XyinmsnP4dYENzdjCyC2npa-/s1600-h/New+Bitmap+Image+(2).bmp"><img id="BLOGGER_PHOTO_ID_5403620736837530066" style="WIDTH: 400px; CURSOR: hand; HEIGHT: 103px" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjdBTonShgscW-NxEHOMrGMAJ2Fz4hr5VlbECqLvKG9ox7x3bfy6M1wikjA7XLKLC08_dU5hVGW8BXeG33NENW-AM7W-dMjWN72sq55SYW6844WYzoXdar7XyinmsnP4dYENzdjCyC2npa-/s400/New+Bitmap+Image+(2).bmp" border="0" /></a><br /><br /><span style="font-family:georgia;">1.“uVar_BackupFolder” this is the folder that the files will be copied to, you must create this folder before running the package, this package does not make folder.<br />2.“uVar_FolderNameOfFilesToBeProcessed” Points to the folder that is required to be processed.<br />3.“uVar_SourceConStr_DataSource” File name and folder gets saved in on each loop<br />4.“uVar_SourceFileExtention” Defines the file extension type. </span><br /><br /><span style="font-family:georgia;"><br />Next, we must configure the settings of the Loop: </span></p><p><span style="font-family:georgia;">First ---</span><br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhv7iVVueuY9Iij8iuNHxyyUHBpoIWYX2GyP_dsfD4wdG5yMZvi9q4D76LrQ-XCnWN1GkkYXROtYAEMnZ1jijCXikDIUKjcKt92wYmZzz9EQxE_tt08TkrOABYUnRrJMT3X5ttNz91CYcW2/s1600-h/New+Bitmap+Image+(3).bmp"><img id="BLOGGER_PHOTO_ID_5403620675637009810" style="WIDTH: 400px; CURSOR: hand; HEIGHT: 372px" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhv7iVVueuY9Iij8iuNHxyyUHBpoIWYX2GyP_dsfD4wdG5yMZvi9q4D76LrQ-XCnWN1GkkYXROtYAEMnZ1jijCXikDIUKjcKt92wYmZzz9EQxE_tt08TkrOABYUnRrJMT3X5ttNz91CYcW2/s400/New+Bitmap+Image+(3).bmp" border="0" /></a><br /><br /><br />Second --<br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjcWiVl3Um9QlRE8_NbDsvnTf0qhi4ix1txBGErBEs33mVgUSGTM5YesQdUZHS6MkjEvfWLTd7II1t5XaeckEiufQMGpj8TSH1Tcwq94g6FTnefKxMWaMYylSjZcAB6pgvHx2R6MbtyQOSs/s1600-h/New+Bitmap+Image+(4).bmp"><img id="BLOGGER_PHOTO_ID_5403620568834531474" style="WIDTH: 400px; CURSOR: hand; HEIGHT: 382px" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjcWiVl3Um9QlRE8_NbDsvnTf0qhi4ix1txBGErBEs33mVgUSGTM5YesQdUZHS6MkjEvfWLTd7II1t5XaeckEiufQMGpj8TSH1Tcwq94g6FTnefKxMWaMYylSjZcAB6pgvHx2R6MbtyQOSs/s400/New+Bitmap+Image+(4).bmp" border="0" /></a><br /><br /><br />The final step, setting FSYS<br /><br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiIUJCEIFGjlT-cTvgbDNzkE2iNyw2RWF4PAglHuXAlCs-lafCacf-5HXu14-Df24GswcWXHKErh-m2CIdiLVTyIH3IQlXTC45U7dlggKMHw0_tB12sDbZH0H7wyK7pvXtnx8XIqEgC_khM/s1600-h/New+Bitmap+Image+(5).bmp"><img id="BLOGGER_PHOTO_ID_5403620483558072578" style="WIDTH: 400px; CURSOR: hand; HEIGHT: 84px" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiIUJCEIFGjlT-cTvgbDNzkE2iNyw2RWF4PAglHuXAlCs-lafCacf-5HXu14-Df24GswcWXHKErh-m2CIdiLVTyIH3IQlXTC45U7dlggKMHw0_tB12sDbZH0H7wyK7pvXtnx8XIqEgC_khM/s400/New+Bitmap+Image+(5).bmp" border="0" /></a><br /><br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi2DvgO2aTBXoA-ZcRgKhTb5TYQhr0Gum4RC_bazJ3Fv7UGvZR0Qne4Hjb61mQWxjwCKKhTpKWDXzJUSoGJHZVNwjqpDhVHMoWSAmlZGTQ-zTl3PV4ATYvHaphYGsJ8tEYg72deCVxDTRRc/s1600-h/New+Bitmap+Image+(6).bmp"><img id="BLOGGER_PHOTO_ID_5403620421162144850" style="WIDTH: 400px; CURSOR: hand; HEIGHT: 363px" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi2DvgO2aTBXoA-ZcRgKhTb5TYQhr0Gum4RC_bazJ3Fv7UGvZR0Qne4Hjb61mQWxjwCKKhTpKWDXzJUSoGJHZVNwjqpDhVHMoWSAmlZGTQ-zTl3PV4ATYvHaphYGsJ8tEYg72deCVxDTRRc/s400/New+Bitmap+Image+(6).bmp" border="0" /></a><br /><br /><strong>Testing Scenarios</strong><br />Assuming that we have 2 excel files in 2 different folders...<br /><br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEihpgK43BsIFuwnfQ9uGZHhg3r9qA-5QR9VC0gB4vYYTl86tSyFyrDPad-M41AKYfm5MX-lubQNzEaGIonNvFejh343Ew1YDfCKvzOdiwcixk0WHtwvu_XCdB20GmS3uan0G5zqs8KOAIve/s1600-h/New+Bitmap+Image+(7).bmp"><img id="BLOGGER_PHOTO_ID_5403620309299917570" style="WIDTH: 400px; CURSOR: hand; HEIGHT: 238px" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEihpgK43BsIFuwnfQ9uGZHhg3r9qA-5QR9VC0gB4vYYTl86tSyFyrDPad-M41AKYfm5MX-lubQNzEaGIonNvFejh343Ew1YDfCKvzOdiwcixk0WHtwvu_XCdB20GmS3uan0G5zqs8KOAIve/s400/New+Bitmap+Image+(7).bmp" border="0" /></a><br /><br />Simply run the package from BIDS and check the backup folder and see </p>Shahriar Nikkhah (Nik)http://www.blogger.com/profile/17215555681462843010noreply@blogger.com1tag:blogger.com,1999:blog-5534374197067462925.post-33406821619269843912009-10-23T12:25:00.007-04:002009-12-14T16:01:11.699-05:00How to loop through files in SSISMost of the SSIS developers want to get the list of the files from a folder (and maybe subfolders) and save them in to an object variable or loop though them one by one so that they can use it in an object within SSIS. (e.g. *.xml, *.bmp, *.txt, most of all Excel files *.xls and etc...)<br />This scenario explains how to loop though the files one by one.<br /><br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg1Pfjoc8TTzlUcmIZrGBbHYuDyBlvaVlP3V_xdKEbCljheU4QzmU_foPKdkZeUrPI4eN3DK0oIACNE1-Mqj-yCkWso3-HGPqBJypJ-0kGmO9g2uEs37Dlin1Kzuo_oIBANNo1riJv1YwFB/s1600-h/New+Bitmap+Image.bmp"></a><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg2J8Z6Y_YxdpcQjfgjQ5OLH-VWb_6ZwCMOcMJcDwD0vmvhZC2OLRy08IL7gtnwtPMzDX7_NP-PKrq6169l8uB8b_TELdL4zVcOgzoHsYHUgBa9WH20-ghetUbF0lVoGuFljJSMmXk31J5t/s1600-h/New+Bitmap+Image.bmp"><img id="BLOGGER_PHOTO_ID_5385471716295763618" style="WIDTH: 400px; CURSOR: hand; HEIGHT: 164px" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg2J8Z6Y_YxdpcQjfgjQ5OLH-VWb_6ZwCMOcMJcDwD0vmvhZC2OLRy08IL7gtnwtPMzDX7_NP-PKrq6169l8uB8b_TELdL4zVcOgzoHsYHUgBa9WH20-ghetUbF0lVoGuFljJSMmXk31J5t/s400/New+Bitmap+Image.bmp" border="0" /></a><br /><br /><em><span style="font-family:georgia;">First step is to define few variables in the system<br /></span><br /></em><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiKssPmzQ57gGmFBIriCaD9U1pfoO1ZauwTtGRuS96jh18Re7hckpX5GnfBLJwIMewYuOomNws38kRCUj_HYwMjsu6fGPl6N9R_IwuRNSx0wLBB-cdENg0ilqLIqnXu8Cgb7j7hBEN7QY2B/s1600-h/New+Bitmap+Image+(2.5).bmp"><img id="BLOGGER_PHOTO_ID_5385452832734420242" style="WIDTH: 400px; CURSOR: hand; HEIGHT: 98px" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiKssPmzQ57gGmFBIriCaD9U1pfoO1ZauwTtGRuS96jh18Re7hckpX5GnfBLJwIMewYuOomNws38kRCUj_HYwMjsu6fGPl6N9R_IwuRNSx0wLBB-cdENg0ilqLIqnXu8Cgb7j7hBEN7QY2B/s400/New+Bitmap+Image+(2.5).bmp" border="0" /></a><br /><br /><strong>1. </strong>uVar_FolderNameOfFilesToBeProcessed : Points to the folder that is required to be processed.<br /><strong>2.</strong> uVar_SourceConStr_DataSource : File name and folder gets saved in on each loop<br /><strong>3.</strong> uVar_SourceFileExtention : Defines the file extension type<br /><br /><em>Next is the settings of the Loop<br /></em><br />First ---<br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhSbHpH7Ggi1vyY0YG7nlSMbHvxZnxwIDtH5kcCNHZzuwn1z72PaPt2lTQtgafdtf552bk-VQ74fFSmc-5nxIC8qC0dcIHdYLrxXQokzGvCGbD4gsqT5EcXpYKqA8Qd1aoO98d_5b_F0Rsu/s1600-h/New+Bitmap+Image+(3).bmp"><img id="BLOGGER_PHOTO_ID_5385455685311393218" style="WIDTH: 400px; CURSOR: hand; HEIGHT: 372px" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhSbHpH7Ggi1vyY0YG7nlSMbHvxZnxwIDtH5kcCNHZzuwn1z72PaPt2lTQtgafdtf552bk-VQ74fFSmc-5nxIC8qC0dcIHdYLrxXQokzGvCGbD4gsqT5EcXpYKqA8Qd1aoO98d_5b_F0Rsu/s400/New+Bitmap+Image+(3).bmp" border="0" /></a><br /><br />Second --<br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOLXXzyFPqZcA6J7M7xnR8J6otFog_hzE47nYd3o2oHCGuajdxyHa5zeMkjqNN28vE_n7zPjoyMcCkA6bvAYzEsLWd0BWPWDovYR5IeUFMll3UR9ciubIQPFew2lsvuMgzCvcTOilc8jUh/s1600-h/New+Bitmap+Image+(4).bmp"><img id="BLOGGER_PHOTO_ID_5385456129823220354" style="WIDTH: 400px; CURSOR: hand; HEIGHT: 382px" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOLXXzyFPqZcA6J7M7xnR8J6otFog_hzE47nYd3o2oHCGuajdxyHa5zeMkjqNN28vE_n7zPjoyMcCkA6bvAYzEsLWd0BWPWDovYR5IeUFMll3UR9ciubIQPFew2lsvuMgzCvcTOilc8jUh/s400/New+Bitmap+Image+(4).bmp" border="0" /></a><br /><br /><em>Next step the script setting</em><br /><br />In this section I am going to make it easy, so I am going to use a message box to display the File and folder name.<br /><br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiSo35mr2Wm7xd-u_pJnpD4hXmXNf6G4Tw2pd_kc4MViFZeeYnygeLajNirBiRgvwPxDmRBmZs0UlZtkPk0EQOpbyIQqAEEWY_vQqMaLjd8NadEdltRjcExsYFwV13yb9Lj5GQb6NXfMa7E/s1600-h/New+Bitmap+Image+(5).bmp"><img id="BLOGGER_PHOTO_ID_5385460100771319186" style="WIDTH: 400px; CURSOR: hand; HEIGHT: 382px" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiSo35mr2Wm7xd-u_pJnpD4hXmXNf6G4Tw2pd_kc4MViFZeeYnygeLajNirBiRgvwPxDmRBmZs0UlZtkPk0EQOpbyIQqAEEWY_vQqMaLjd8NadEdltRjcExsYFwV13yb9Lj5GQb6NXfMa7E/s400/New+Bitmap+Image+(5).bmp" border="0" /></a><br /><br /><strong>Please Note</strong>: SSIS is not a front-end software, the message box is just for test purposes.<br />next click on the "Design Script" and enter the code....<br /><br /><span style="color:#3333ff;">Public Sub</span> Main()<br /><span style="color:#33ff33;"></span><span style="color:#009900;">' </span><br /><span style="color:#009900;">' Add your code here </span><br /><span style="color:#009900;">'</span><br />MsgBox(Dts.Variables(<span style="color:#993300;">"uVar_SourceConStr_DataSource").</span>Value.ToString)<br />Dts.TaskResult = Dts.Results.Success<br /><br /><span style="color:#3333ff;">End Sub</span><br /><br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj8YXGOAEdCE-r7HBAjFVmddH1ILasVM909cm9AgJJI2UfcCwNDnjc6Y6N20iLUr8azl1xi3p6NVbphQ5d4Iq8OO2TlfAZnSyUd11iDrNWetxWWNJvWGcb1IixxYDNVpDYl5tlVv1oEH_N5/s1600-h/New+Bitmap+Image+(6).bmp"><img id="BLOGGER_PHOTO_ID_5385460695211463874" style="WIDTH: 400px; CURSOR: hand; HEIGHT: 232px" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj8YXGOAEdCE-r7HBAjFVmddH1ILasVM909cm9AgJJI2UfcCwNDnjc6Y6N20iLUr8azl1xi3p6NVbphQ5d4Iq8OO2TlfAZnSyUd11iDrNWetxWWNJvWGcb1IixxYDNVpDYl5tlVv1oEH_N5/s400/New+Bitmap+Image+(6).bmp" border="0" /></a><br /><br /><strong><em>Testing Scenarios</em></strong><br /><br />Assuming that we have 2 types of file (1- Excel files 2- Text file) .....<br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhhxvFaWK28Ssck0euk54IRpLb6AyFO1LwAgWjRyMlNR1XOJexGllq2ymvJElU80qzQkvrMkgzfMlKdBH8O1d21WvbH1kadW7GWOr64AxITC1CienWZJkYrCuGoVG37nbEhFdPdX5zeCuSN/s1600-h/New+Bitmap+Image+(7).bmp"><img id="BLOGGER_PHOTO_ID_5385462861767743234" style="WIDTH: 400px; CURSOR: hand; HEIGHT: 211px" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhhxvFaWK28Ssck0euk54IRpLb6AyFO1LwAgWjRyMlNR1XOJexGllq2ymvJElU80qzQkvrMkgzfMlKdBH8O1d21WvbH1kadW7GWOr64AxITC1CienWZJkYrCuGoVG37nbEhFdPdX5zeCuSN/s400/New+Bitmap+Image+(7).bmp" border="0" /></a><br /><br /><em>---First test finding *.xls files </em><br />In the variable "uVar_SourceFileExtention" we have "*.xls", and in the folders and subfolders we have 2 excel files, once running the SSIS package from BIDS you'll get 2 message box one message for each loop as...<br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgAZf0I2aXLrRAOLKGJulwb65yd1pEHJdz-K31666bWebjV1U61Cavz6-HRZmEJDmm2pIQFSYjhACci1ssrrxZ9GKgFNkYxdOwpUjruFGX3HFKp8vza5AFFT2HTbOgbDl1dOizCmpQK5eBY/s1600-h/New+Bitmap+Image+(8).bmp"><img id="BLOGGER_PHOTO_ID_5385464338958515042" style="WIDTH: 400px; CURSOR: hand; HEIGHT: 204px" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgAZf0I2aXLrRAOLKGJulwb65yd1pEHJdz-K31666bWebjV1U61Cavz6-HRZmEJDmm2pIQFSYjhACci1ssrrxZ9GKgFNkYxdOwpUjruFGX3HFKp8vza5AFFT2HTbOgbDl1dOizCmpQK5eBY/s400/New+Bitmap+Image+(8).bmp" border="0" /></a><br /><em>---Second test finding *.txt files</em><br />Change the variable "uVar_SourceFileExtension" to "*.txt", and run the package again in BIDS, you will have to get only one message.<br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjmMW7uHCfwA7yq5OCjK16R-KInv0J_wI55JHMmq_smojPi2GhRysScPuSfWzC-AhuDir1jpDzxPeFwSmgJ9VaXTXH53fLFt52hbyMv4s_rl_3SokbYbnLrTaL9fvdMJ3EQpZtX37LAsJEn/s1600-h/New+Bitmap+Image+(9).bmp"><img id="BLOGGER_PHOTO_ID_5385466578841944866" style="WIDTH: 400px; CURSOR: hand; HEIGHT: 188px" alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjmMW7uHCfwA7yq5OCjK16R-KInv0J_wI55JHMmq_smojPi2GhRysScPuSfWzC-AhuDir1jpDzxPeFwSmgJ9VaXTXH53fLFt52hbyMv4s_rl_3SokbYbnLrTaL9fvdMJ3EQpZtX37LAsJEn/s400/New+Bitmap+Image+(9).bmp" border="0" /></a><br /><em>---Third test finding files with a special extension <strong>*30.xls</strong> or <strong>File*.xls</strong></em><br />In this case you will get the same message box as the first scenario except you will only get the first message, file “File20070930.xls”Shahriar Nikkhah (Nik)http://www.blogger.com/profile/17215555681462843010noreply@blogger.com1