bidshelper Wiki & Documentation Rss Feedhttp://www.codeplex.com/bidshelper/Wiki/View.aspx?title=Homebidshelper Wiki Rss DescriptionUpdated Wiki: SSIS Performance Visualizationhttps://bidshelper.codeplex.com/wikipage?title=SSIS Performance Visualization&version=17<div class="wikidoc"><h3>SSIS Performance Visualization</h3>
Based on the theory that a picture is worth a thousand words, the SSIS Performance Visualization feature of BIDS Helper shows you a graphical gantt chart view of the execution durations and dependencies for your package to help you visualize performance.<br /><br /><img src="http://download-codeplex.sec.s-msft.com/Download?ProjectName=bidshelper&DownloadId=30951" alt="SSISPerformanceTab.png" title="SSISPerformanceTab.png" /><br /><br />The feature can be launched by right-clicking on the package in Solution Explorer and choosing &quot;Execute and Visualize Performance&quot;:<br /><br /><img src="http://download-codeplex.sec.s-msft.com/Download?ProjectName=bidshelper&DownloadId=30952" alt="SSISPerformanceMenu.png" title="SSISPerformanceMenu.png" /><br /><br /><b>Performance Tab:</b> A new Performance tab is then added to the package designer window, then the package is executed out of process using dtexec. Depending on whether you are on a 64-bit server and depending on whether you have Run64BitRuntime set to true in the project properties dialog, the 64-bit version of dtexec is used.<br /><br /><b>Gantt Bars:</b> The bars on the gantt view represent the duration and time period during which that portion of the package was being executed.<br /><br /><b>Buffer Diamonds:</b> For individual data paths inside execution trees of the data flow, the small blue diamonds represent one buffer of data flowing. Note that SSIS logging only reports to the granularity of a second, so if more than one buffer flows on a particular path in the same second, the diamonds will be on top of one another. In this case, a superscript number (2-9 or &quot;+&quot; if greater than 9) indicates the number of buffers that flowed during that second: <img src="http://download-codeplex.sec.s-msft.com/Download?ProjectName=bidshelper&DownloadId=31235" alt="SSISPerformanceSuperscriptDiamonds.png" title="SSISPerformanceSuperscriptDiamonds.png" /><br /><br /><b>Custom Logging:</b> A temporary copy of the package is made and certain SSIS logging settings are enabled to allow BIDS Helper to monitor the package execution events it needs to visualize performance. (Existing SSIS logging you have setup in that package will be disabled in the temporary copy of the package, so do not be confused when you see no log events show up at your logging destination.)<br /><br /><b>Developing Simultaneously:</b> During the execution of the package by the SSIS Performance Visualization feature, you can flip back to the other tabs and continue making changes to the package without impacting the Performance Visualization execution (since it&#39;s executing from a temporary copy of the package).<br /><br /><b>Errors:</b> If any errors occur in package execution, the gantt bar (and any parent to which the error bubbles) are highlighted red. The actual error message can be seen in the Output window.<br /><br />
<h4>Alternate Views</h4>
Alternate views of performance can be seen by clicking the tree icon.<br /><br /><b>Statistics Grid:</b> The Statistics Grid view shows various performance measurements centered mostly around data flow performance:
<ul><li><i>Duration</i> - The duration of this portion of the package in seconds. SSIS logging only reports to the granularity of a second, so any portions of the package that execute in a second or less may show as zero duration, and subsequent columns may be erratic.</li>
<li><i>Inbound Rows/Sec</i> - The count of rows coming from sources. For the data flow task node itself, this is a sum of rows from any source component. For the execution tree node, this is the sum of the rows from the pipeline components that start the buffer for that execution tree.</li>
<li><i>Outbound Rows/Sec</i> - The count of rows going to destinations. For the data flow task node itself, this is a sum of rows going to any destination component. For the execution tree node, this is the sum of the rows going to any endpoints of the execution tree.</li>
<li><i>Rows Per Buffer</i> - Represents the rowcount of the buffer type used for this execution tree. This number is reported in the BufferSizeTuning log event, and the mapping between buffer types and execution trees is reported in the PipelineExecutionPlan log event. For SQL2008, the Rows Per Buffer column will be blank because the BufferSizeTuning event cannot be <a href="http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=3207359&amp;SiteID=1">tied</a> to an execution tree like in SQL2005.</li>
<li><i>Est. Bytes Per Row</i> - This is a very rough and inexact estimate of the width (in bytes) of a row in the buffer type used by an execution tree.</li>
<li><i>Inbound Kb/Sec</i> - Generally measures velocity of data coming from sources. Calculation is <i>Inbound Rows/Sec</i> * <i>Est. Bytes Per Row</i> / 1024.</li>
<li><i>Outbound Kb/Sec</i> - Generally measures velocity of data coming from sources. Calculation is <i>Inbound Rows/Sec</i> * <i>Est. Bytes Per Row</i> / 1024.</li></ul>
<img src="http://download-codeplex.sec.s-msft.com/Download?ProjectName=bidshelper&DownloadId=30953" alt="SSISPerformanceGrid.png" title="SSISPerformanceGrid.png" /><br /><br /><br /><b>Statistics Trend:</b> The Statistics Trend compares performance from one execution to another. It adds a new column for each execution and highlights whether the duration of that piece of the package was faster or slower than before.<br /><br /><img src="http://download-codeplex.sec.s-msft.com/Download?ProjectName=bidshelper&DownloadId=30954" alt="SSISPerformanceTrendGrid.png" title="SSISPerformanceTrendGrid.png" /><br /><br /><b>Exporting:</b> The copy button in the toolbar will place the contents of the current grid into the clipboard. You can then paste this data into Excel.<br /><br /><b>Execution Tree Durations:</b> Do not assume that the an Execution Tree is actually working for the entire duration of it&#39;s gantt bar as much of that bar may represent time it spent waiting on other execution trees. For a more accurate picture of the actual duration spent with each component, right click on the data flow and choose the &quot;Component Performance Breakdown&quot; menu option which executes the <a href="https://bidshelper.codeplex.com/wikipage?title=Pipeline%20Component%20Performance%20Breakdown&referringTitle=SSIS%20Performance%20Visualization">Pipeline Component Performance Breakdown</a> feature:<br /><br /><img src="http://download-codeplex.sec.s-msft.com/Download?ProjectName=bidshelper&DownloadId=32746" alt="SSISPerformanceRightClickDataFlow.png" title="SSISPerformanceRightClickDataFlow.png" /><br /><br /><br />
<h4>Limitations</h4>
This feature may not work if you only install SSDT-BI for VS2012 or VS2013 without installing SSIS from the SQL install media. This is a limitation of the SSDT-BI install. For more information, see <br /><a href="https://connect.microsoft.com/SQLServer/feedback/details/850467/dtutil-installed-with-ssdtbi-for-vs2013-requires-other-installations">https://connect.microsoft.com/SQLServer/feedback/details/850467/dtutil-installed-with-ssdtbi-for-vs2013-requires-other-installations</a>.<br /><br />
<h4>Feedback</h4>
This is the initial version of the SSIS Performance Visualization feature, so feedback is requested. Please post suggestions in <a href="http://www.codeplex.com/bidshelper/WorkItem/List.aspx">Issue Tracker</a>. And please post screenshots of the gantt view of your packages to the <a href="http://www.codeplex.com/bidshelper/Thread/List.aspx">Discussions</a> area if this tool is helpful (or if it is misleading).<br /></div><div class="ClearBoth"></div>furmanggSat, 28 Mar 2015 04:03:29 GMTUpdated Wiki: SSIS Performance Visualization 20150328040329AUpdated Wiki: SSIS Performance Visualizationhttps://bidshelper.codeplex.com/wikipage?title=SSIS Performance Visualization&version=16<div class="wikidoc"><h3>SSIS Performance Visualization</h3>
Based on the theory that a picture is worth a thousand words, the SSIS Performance Visualization feature of BIDS Helper shows you a graphical gantt chart view of the execution durations and dependencies for your package to help you visualize performance.<br /><br /><img src="http://download-codeplex.sec.s-msft.com/Download?ProjectName=bidshelper&DownloadId=30951" alt="SSISPerformanceTab.png" title="SSISPerformanceTab.png" /><br /><br />The feature can be launched by right-clicking on the package in Solution Explorer and choosing &quot;Execute and Visualize Performance&quot;:<br /><br /><img src="http://download-codeplex.sec.s-msft.com/Download?ProjectName=bidshelper&DownloadId=30952" alt="SSISPerformanceMenu.png" title="SSISPerformanceMenu.png" /><br /><br /><b>Performance Tab:</b> A new Performance tab is then added to the package designer window, then the package is executed out of process using dtexec. Depending on whether you are on a 64-bit server and depending on whether you have Run64BitRuntime set to true in the project properties dialog, the 64-bit version of dtexec is used.<br /><br /><b>Gantt Bars:</b> The bars on the gantt view represent the duration and time period during which that portion of the package was being executed.<br /><br /><b>Buffer Diamonds:</b> For individual data paths inside execution trees of the data flow, the small blue diamonds represent one buffer of data flowing. Note that SSIS logging only reports to the granularity of a second, so if more than one buffer flows on a particular path in the same second, the diamonds will be on top of one another. In this case, a superscript number (2-9 or &quot;+&quot; if greater than 9) indicates the number of buffers that flowed during that second: <img src="http://download-codeplex.sec.s-msft.com/Download?ProjectName=bidshelper&DownloadId=31235" alt="SSISPerformanceSuperscriptDiamonds.png" title="SSISPerformanceSuperscriptDiamonds.png" /><br /><br /><b>Custom Logging:</b> A temporary copy of the package is made and certain SSIS logging settings are enabled to allow BIDS Helper to monitor the package execution events it needs to visualize performance. (Existing SSIS logging you have setup in that package will be disabled in the temporary copy of the package, so do not be confused when you see no log events show up at your logging destination.)<br /><br /><b>Developing Simultaneously:</b> During the execution of the package by the SSIS Performance Visualization feature, you can flip back to the other tabs and continue making changes to the package without impacting the Performance Visualization execution (since it&#39;s executing from a temporary copy of the package).<br /><br /><b>Errors:</b> If any errors occur in package execution, the gantt bar (and any parent to which the error bubbles) are highlighted red. The actual error message can be seen in the Output window.<br /><br />
<h4>Alternate Views</h4>
Alternate views of performance can be seen by clicking the tree icon.<br /><br /><b>Statistics Grid:</b> The Statistics Grid view shows various performance measurements centered mostly around data flow performance:
<ul><li><i>Duration</i> - The duration of this portion of the package in seconds. SSIS logging only reports to the granularity of a second, so any portions of the package that execute in a second or less may show as zero duration, and subsequent columns may be erratic.</li>
<li><i>Inbound Rows/Sec</i> - The count of rows coming from sources. For the data flow task node itself, this is a sum of rows from any source component. For the execution tree node, this is the sum of the rows from the pipeline components that start the buffer for that execution tree.</li>
<li><i>Outbound Rows/Sec</i> - The count of rows going to destinations. For the data flow task node itself, this is a sum of rows going to any destination component. For the execution tree node, this is the sum of the rows going to any endpoints of the execution tree.</li>
<li><i>Rows Per Buffer</i> - Represents the rowcount of the buffer type used for this execution tree. This number is reported in the BufferSizeTuning log event, and the mapping between buffer types and execution trees is reported in the PipelineExecutionPlan log event. For SQL2008, the Rows Per Buffer column will be blank because the BufferSizeTuning event cannot be <a href="http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=3207359&amp;SiteID=1">tied</a> to an execution tree like in SQL2005.</li>
<li><i>Est. Bytes Per Row</i> - This is a very rough and inexact estimate of the width (in bytes) of a row in the buffer type used by an execution tree.</li>
<li><i>Inbound Kb/Sec</i> - Generally measures velocity of data coming from sources. Calculation is <i>Inbound Rows/Sec</i> * <i>Est. Bytes Per Row</i> / 1024.</li>
<li><i>Outbound Kb/Sec</i> - Generally measures velocity of data coming from sources. Calculation is <i>Inbound Rows/Sec</i> * <i>Est. Bytes Per Row</i> / 1024.</li></ul>
<img src="http://download-codeplex.sec.s-msft.com/Download?ProjectName=bidshelper&DownloadId=30953" alt="SSISPerformanceGrid.png" title="SSISPerformanceGrid.png" /><br /><br /><br /><b>Statistics Trend:</b> The Statistics Trend compares performance from one execution to another. It adds a new column for each execution and highlights whether the duration of that piece of the package was faster or slower than before.<br /><br /><img src="http://download-codeplex.sec.s-msft.com/Download?ProjectName=bidshelper&DownloadId=30954" alt="SSISPerformanceTrendGrid.png" title="SSISPerformanceTrendGrid.png" /><br /><br /><b>Exporting:</b> The copy button in the toolbar will place the contents of the current grid into the clipboard. You can then paste this data into Excel.<br /><br /><b>Execution Tree Durations:</b> Do not assume that the an Execution Tree is actually working for the entire duration of it&#39;s gantt bar as much of that bar may represent time it spent waiting on other execution trees. For a more accurate picture of the actual duration spent with each component, right click on the data flow and choose the &quot;Component Performance Breakdown&quot; menu option which executes the <a href="https://bidshelper.codeplex.com/wikipage?title=Pipeline%20Component%20Performance%20Breakdown&referringTitle=SSIS%20Performance%20Visualization">Pipeline Component Performance Breakdown</a> feature:<br /><br /><img src="http://download-codeplex.sec.s-msft.com/Download?ProjectName=bidshelper&DownloadId=32746" alt="SSISPerformanceRightClickDataFlow.png" title="SSISPerformanceRightClickDataFlow.png" /><br /><br /><br />
<h4>Limitations</h4>
This feature may not work if you only install SSDT-BI for VS2012 or VS2013 without installing SSIS from the SQL install media. This is a limitation of the SSDT-BI install. For more information, see <a href="http://tlelimo001, we got a disappointing answer from Microsoft that they won&#39;t fix this issue with SSDTBI for VS2012 or VS2013. Basically installing only SSDTBI isn&#39;t enough to run dtexec so this feature won&#39;t work. You will have to install SSIS using the SQL install media for dtexec to function. For more information see [url:tlelimo001, we got a disappointing answer from Microsoft that they won&#39;t fix this issue with SSDTBI for VS2012 or VS2013. Basically installing only SSDTBI isn&#39;t enough to run dtexec so this feature won&#39;t work. You will have to install SSIS using the SQL install media for dtexec to function. See the following for more information:<br />[url:https://connect.microsoft.com/SQLServer/feedback/details/850467/dtutil-installed-with-ssdtbi-for-vs2013-requires-other-installations">this Connect bug report</a>.<br /><br />
<h4>Feedback</h4>
This is the initial version of the SSIS Performance Visualization feature, so feedback is requested. Please post suggestions in <a href="http://www.codeplex.com/bidshelper/WorkItem/List.aspx">Issue Tracker</a>. And please post screenshots of the gantt view of your packages to the <a href="http://www.codeplex.com/bidshelper/Thread/List.aspx">Discussions</a> area if this tool is helpful (or if it is misleading).<br /></div><div class="ClearBoth"></div>furmanggSat, 28 Mar 2015 04:01:37 GMTUpdated Wiki: SSIS Performance Visualization 20150328040137AUpdated Wiki: SSIS Performance Visualizationhttps://bidshelper.codeplex.com/wikipage?title=SSIS Performance Visualization&version=15<div class="wikidoc"><h3>SSIS Performance Visualization</h3>
Based on the theory that a picture is worth a thousand words, the SSIS Performance Visualization feature of BIDS Helper shows you a graphical gantt chart view of the execution durations and dependencies for your package to help you visualize performance.<br /><br /><img src="http://download-codeplex.sec.s-msft.com/Download?ProjectName=bidshelper&DownloadId=30951" alt="SSISPerformanceTab.png" title="SSISPerformanceTab.png" /><br /><br />The feature can be launched by right-clicking on the package in Solution Explorer and choosing &quot;Execute and Visualize Performance&quot;:<br /><br /><img src="http://download-codeplex.sec.s-msft.com/Download?ProjectName=bidshelper&DownloadId=30952" alt="SSISPerformanceMenu.png" title="SSISPerformanceMenu.png" /><br /><br /><b>Performance Tab:</b> A new Performance tab is then added to the package designer window, then the package is executed out of process using dtexec. Depending on whether you are on a 64-bit server and depending on whether you have Run64BitRuntime set to true in the project properties dialog, the 64-bit version of dtexec is used.<br /><br /><b>Gantt Bars:</b> The bars on the gantt view represent the duration and time period during which that portion of the package was being executed.<br /><br /><b>Buffer Diamonds:</b> For individual data paths inside execution trees of the data flow, the small blue diamonds represent one buffer of data flowing. Note that SSIS logging only reports to the granularity of a second, so if more than one buffer flows on a particular path in the same second, the diamonds will be on top of one another. In this case, a superscript number (2-9 or &quot;+&quot; if greater than 9) indicates the number of buffers that flowed during that second: <img src="http://download-codeplex.sec.s-msft.com/Download?ProjectName=bidshelper&DownloadId=31235" alt="SSISPerformanceSuperscriptDiamonds.png" title="SSISPerformanceSuperscriptDiamonds.png" /><br /><br /><b>Custom Logging:</b> A temporary copy of the package is made and certain SSIS logging settings are enabled to allow BIDS Helper to monitor the package execution events it needs to visualize performance. (Existing SSIS logging you have setup in that package will be disabled in the temporary copy of the package, so do not be confused when you see no log events show up at your logging destination.)<br /><br /><b>Developing Simultaneously:</b> During the execution of the package by the SSIS Performance Visualization feature, you can flip back to the other tabs and continue making changes to the package without impacting the Performance Visualization execution (since it&#39;s executing from a temporary copy of the package).<br /><br /><b>Errors:</b> If any errors occur in package execution, the gantt bar (and any parent to which the error bubbles) are highlighted red. The actual error message can be seen in the Output window.<br /><br />
<h4>Alternate Views</h4>
Alternate views of performance can be seen by clicking the tree icon.<br /><br /><b>Statistics Grid:</b> The Statistics Grid view shows various performance measurements centered mostly around data flow performance:
<ul><li><i>Duration</i> - The duration of this portion of the package in seconds. SSIS logging only reports to the granularity of a second, so any portions of the package that execute in a second or less may show as zero duration, and subsequent columns may be erratic.</li>
<li><i>Inbound Rows/Sec</i> - The count of rows coming from sources. For the data flow task node itself, this is a sum of rows from any source component. For the execution tree node, this is the sum of the rows from the pipeline components that start the buffer for that execution tree.</li>
<li><i>Outbound Rows/Sec</i> - The count of rows going to destinations. For the data flow task node itself, this is a sum of rows going to any destination component. For the execution tree node, this is the sum of the rows going to any endpoints of the execution tree.</li>
<li><i>Rows Per Buffer</i> - Represents the rowcount of the buffer type used for this execution tree. This number is reported in the BufferSizeTuning log event, and the mapping between buffer types and execution trees is reported in the PipelineExecutionPlan log event. For SQL2008, the Rows Per Buffer column will be blank because the BufferSizeTuning event cannot be <a href="http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=3207359&amp;SiteID=1">tied</a> to an execution tree like in SQL2005.</li>
<li><i>Est. Bytes Per Row</i> - This is a very rough and inexact estimate of the width (in bytes) of a row in the buffer type used by an execution tree.</li>
<li><i>Inbound Kb/Sec</i> - Generally measures velocity of data coming from sources. Calculation is <i>Inbound Rows/Sec</i> * <i>Est. Bytes Per Row</i> / 1024.</li>
<li><i>Outbound Kb/Sec</i> - Generally measures velocity of data coming from sources. Calculation is <i>Inbound Rows/Sec</i> * <i>Est. Bytes Per Row</i> / 1024.</li></ul>
<img src="http://download-codeplex.sec.s-msft.com/Download?ProjectName=bidshelper&DownloadId=30953" alt="SSISPerformanceGrid.png" title="SSISPerformanceGrid.png" /><br /><br /><br /><b>Statistics Trend:</b> The Statistics Trend compares performance from one execution to another. It adds a new column for each execution and highlights whether the duration of that piece of the package was faster or slower than before.<br /><br /><img src="http://download-codeplex.sec.s-msft.com/Download?ProjectName=bidshelper&DownloadId=30954" alt="SSISPerformanceTrendGrid.png" title="SSISPerformanceTrendGrid.png" /><br /><br /><b>Exporting:</b> The copy button in the toolbar will place the contents of the current grid into the clipboard. You can then paste this data into Excel.<br /><br /><b>Execution Tree Durations:</b> Do not assume that the an Execution Tree is actually working for the entire duration of it&#39;s gantt bar as much of that bar may represent time it spent waiting on other execution trees. For a more accurate picture of the actual duration spent with each component, right click on the data flow and choose the &quot;Component Performance Breakdown&quot; menu option which executes the <a href="https://bidshelper.codeplex.com/wikipage?title=Pipeline%20Component%20Performance%20Breakdown&referringTitle=SSIS%20Performance%20Visualization">Pipeline Component Performance Breakdown</a> feature:<br /><br /><img src="http://download-codeplex.sec.s-msft.com/Download?ProjectName=bidshelper&DownloadId=32746" alt="SSISPerformanceRightClickDataFlow.png" title="SSISPerformanceRightClickDataFlow.png" /><br /><br /><br />
<h4>Limitations</h4>
This feature may not work if you only install SSDT-BI for VS2012 or VS2013 without installing SSIS from the SQL install media. This is a limitation of the SSDT-BI install. For more information, see <a href="http://tlelimo001, we got a disappointing answer from Microsoft that they won&#39;t fix this issue with SSDTBI for VS2012 or VS2013. Basically installing only SSDTBI isn&#39;t enough to run dtexec so this feature won&#39;t work. You will have to install SSIS using the SQL install media for dtexec to function. For more information see [url:tlelimo001, we got a disappointing answer from Microsoft that they won&#39;t fix this issue with SSDTBI for VS2012 or VS2013. Basically installing only SSDTBI isn&#39;t enough to run dtexec so this feature won&#39;t work. You will have to install SSIS using the SQL install media for dtexec to function. See the following for more information:<br />[url:https://connect.microsoft.com/SQLServer/feedback/details/850467/dtutil-installed-with-ssdtbi-for-vs2013-requires-other-installations">this Connect bug report</a>.<br /><br />
<h4>Feedback</h4>
This is the initial version of the SSIS Performance Visualization feature, so feedback is requested. Please post suggestions in <a href="http://www.codeplex.com/bidshelper/WorkItem/List.aspx">Issue Tracker</a>. And please post screenshots of the gantt view of your packages to the <a href="http://www.codeplex.com/bidshelper/Thread/List.aspx">Discussions</a> area if this tool is helpful (or if it is misleading).<br /></div><div class="ClearBoth"></div>furmanggSat, 28 Mar 2015 04:00:19 GMTUpdated Wiki: SSIS Performance Visualization 20150328040019AUpdated Wiki: SSIS Performance Visualizationhttps://bidshelper.codeplex.com/wikipage?title=SSIS Performance Visualization&version=14<div class="wikidoc"><h3>SSIS Performance Visualization</h3>
Based on the theory that a picture is worth a thousand words, the SSIS Performance Visualization feature of BIDS Helper shows you a graphical gantt chart view of the execution durations and dependencies for your package to help you visualize performance.<br /><br /><img src="http://download-codeplex.sec.s-msft.com/Download?ProjectName=bidshelper&DownloadId=30951" alt="SSISPerformanceTab.png" title="SSISPerformanceTab.png" /><br /><br />The feature can be launched by right-clicking on the package in Solution Explorer and choosing &quot;Execute and Visualize Performance&quot;:<br /><br /><img src="http://download-codeplex.sec.s-msft.com/Download?ProjectName=bidshelper&DownloadId=30952" alt="SSISPerformanceMenu.png" title="SSISPerformanceMenu.png" /><br /><br /><b>Performance Tab:</b> A new Performance tab is then added to the package designer window, then the package is executed out of process using dtexec. Depending on whether you are on a 64-bit server and depending on whether you have Run64BitRuntime set to true in the project properties dialog, the 64-bit version of dtexec is used.<br /><br /><b>Gantt Bars:</b> The bars on the gantt view represent the duration and time period during which that portion of the package was being executed.<br /><br /><b>Buffer Diamonds:</b> For individual data paths inside execution trees of the data flow, the small blue diamonds represent one buffer of data flowing. Note that SSIS logging only reports to the granularity of a second, so if more than one buffer flows on a particular path in the same second, the diamonds will be on top of one another. In this case, a superscript number (2-9 or &quot;+&quot; if greater than 9) indicates the number of buffers that flowed during that second: <img src="http://download-codeplex.sec.s-msft.com/Download?ProjectName=bidshelper&DownloadId=31235" alt="SSISPerformanceSuperscriptDiamonds.png" title="SSISPerformanceSuperscriptDiamonds.png" /><br /><br /><b>Custom Logging:</b> A temporary copy of the package is made and certain SSIS logging settings are enabled to allow BIDS Helper to monitor the package execution events it needs to visualize performance. (Existing SSIS logging you have setup in that package will be disabled in the temporary copy of the package, so do not be confused when you see no log events show up at your logging destination.)<br /><br /><b>Developing Simultaneously:</b> During the execution of the package by the SSIS Performance Visualization feature, you can flip back to the other tabs and continue making changes to the package without impacting the Performance Visualization execution (since it&#39;s executing from a temporary copy of the package).<br /><br /><b>Errors:</b> If any errors occur in package execution, the gantt bar (and any parent to which the error bubbles) are highlighted red. The actual error message can be seen in the Output window.<br /><br />
<h4>Alternate Views</h4>
Alternate views of performance can be seen by clicking the tree icon.<br /><br /><b>Statistics Grid:</b> The Statistics Grid view shows various performance measurements centered mostly around data flow performance:
<ul><li><i>Duration</i> - The duration of this portion of the package in seconds. SSIS logging only reports to the granularity of a second, so any portions of the package that execute in a second or less may show as zero duration, and subsequent columns may be erratic.</li>
<li><i>Inbound Rows/Sec</i> - The count of rows coming from sources. For the data flow task node itself, this is a sum of rows from any source component. For the execution tree node, this is the sum of the rows from the pipeline components that start the buffer for that execution tree.</li>
<li><i>Outbound Rows/Sec</i> - The count of rows going to destinations. For the data flow task node itself, this is a sum of rows going to any destination component. For the execution tree node, this is the sum of the rows going to any endpoints of the execution tree.</li>
<li><i>Rows Per Buffer</i> - Represents the rowcount of the buffer type used for this execution tree. This number is reported in the BufferSizeTuning log event, and the mapping between buffer types and execution trees is reported in the PipelineExecutionPlan log event. For SQL2008, the Rows Per Buffer column will be blank because the BufferSizeTuning event cannot be <a href="http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=3207359&amp;SiteID=1">tied</a> to an execution tree like in SQL2005.</li>
<li><i>Est. Bytes Per Row</i> - This is a very rough and inexact estimate of the width (in bytes) of a row in the buffer type used by an execution tree.</li>
<li><i>Inbound Kb/Sec</i> - Generally measures velocity of data coming from sources. Calculation is <i>Inbound Rows/Sec</i> * <i>Est. Bytes Per Row</i> / 1024.</li>
<li><i>Outbound Kb/Sec</i> - Generally measures velocity of data coming from sources. Calculation is <i>Inbound Rows/Sec</i> * <i>Est. Bytes Per Row</i> / 1024.</li></ul>
<img src="http://download-codeplex.sec.s-msft.com/Download?ProjectName=bidshelper&DownloadId=30953" alt="SSISPerformanceGrid.png" title="SSISPerformanceGrid.png" /><br /><br /><br /><b>Statistics Trend:</b> The Statistics Trend compares performance from one execution to another. It adds a new column for each execution and highlights whether the duration of that piece of the package was faster or slower than before.<br /><br /><img src="http://download-codeplex.sec.s-msft.com/Download?ProjectName=bidshelper&DownloadId=30954" alt="SSISPerformanceTrendGrid.png" title="SSISPerformanceTrendGrid.png" /><br /><br /><b>Exporting:</b> The copy button in the toolbar will place the contents of the current grid into the clipboard. You can then paste this data into Excel.<br /><br /><b>Execution Tree Durations:</b> Do not assume that the an Execution Tree is actually working for the entire duration of it&#39;s gantt bar as much of that bar may represent time it spent waiting on other execution trees. For a more accurate picture of the actual duration spent with each component, right click on the data flow and choose the &quot;Component Performance Breakdown&quot; menu option which executes the <a href="https://bidshelper.codeplex.com/wikipage?title=Pipeline%20Component%20Performance%20Breakdown&referringTitle=SSIS%20Performance%20Visualization">Pipeline Component Performance Breakdown</a> feature:<br /><br /><img src="http://download-codeplex.sec.s-msft.com/Download?ProjectName=bidshelper&DownloadId=32746" alt="SSISPerformanceRightClickDataFlow.png" title="SSISPerformanceRightClickDataFlow.png" /><br /><br /><br />
<h4>Limitations</h4>
This feature may not work if you only install SSDT-BI for VS2012 or VS2013 without installing SSIS from the SQL install media. This is a limitation of the SSDT-BI install. For more information, see <a href="http://tlelimo001, we got a disappointing answer from Microsoft that they won&#39;t fix this issue with SSDTBI for VS2012 or VS2013. Basically installing only SSDTBI isn&#39;t enough to run dtexec so this feature won&#39;t work. You will have to install SSIS using the SQL install media for dtexec to function. For more information see [url:tlelimo001, we got a disappointing answer from Microsoft that they won&#39;t fix this issue with SSDTBI for VS2012 or VS2013. Basically installing only SSDTBI isn&#39;t enough to run dtexec so this feature won&#39;t work. You will have to install SSIS using the SQL install media for dtexec to function. See the following for more information:<br />[url:https://connect.microsoft.com/SQLServer/feedback/details/850467/dtutil-installed-with-ssdtbi-for-vs2013-requires-other-installations">this Connect bug report</a>.<br /><br />
<h4>Feedback</h4>
This is the initial version of the SSIS Performance Visualization feature, so feedback is requested. Please post suggestions in <a href="http://www.codeplex.com/bidshelper/WorkItem/List.aspx">Issue Tracker</a>. And please post screenshots of the gantt view of your packages to the <a href="http://www.codeplex.com/bidshelper/Thread/List.aspx">Discussions</a> area if this tool is helpful (or if it is misleading).<br /></div><div class="ClearBoth"></div>furmanggSat, 28 Mar 2015 03:58:04 GMTUpdated Wiki: SSIS Performance Visualization 20150328035804AUpdated Wiki: SSIS Performance Visualizationhttps://bidshelper.codeplex.com/wikipage?title=SSIS Performance Visualization&version=13<div class="wikidoc"><h3>SSIS Performance Visualization</h3>
Based on the theory that a picture is worth a thousand words, the SSIS Performance Visualization feature of BIDS Helper shows you a graphical gantt chart view of the execution durations and dependencies for your package to help you visualize performance.<br /><br /><img src="http://download-codeplex.sec.s-msft.com/Download?ProjectName=bidshelper&DownloadId=30951" alt="SSISPerformanceTab.png" title="SSISPerformanceTab.png" /><br /><br />The feature can be launched by right-clicking on the package in Solution Explorer and choosing &quot;Execute and Visualize Performance&quot;:<br /><br /><img src="http://download-codeplex.sec.s-msft.com/Download?ProjectName=bidshelper&DownloadId=30952" alt="SSISPerformanceMenu.png" title="SSISPerformanceMenu.png" /><br /><br /><b>Performance Tab:</b> A new Performance tab is then added to the package designer window, then the package is executed out of process using dtexec. Depending on whether you are on a 64-bit server and depending on whether you have Run64BitRuntime set to true in the project properties dialog, the 64-bit version of dtexec is used.<br /><br /><b>Gantt Bars:</b> The bars on the gantt view represent the duration and time period during which that portion of the package was being executed.<br /><br /><b>Buffer Diamonds:</b> For individual data paths inside execution trees of the data flow, the small blue diamonds represent one buffer of data flowing. Note that SSIS logging only reports to the granularity of a second, so if more than one buffer flows on a particular path in the same second, the diamonds will be on top of one another. In this case, a superscript number (2-9 or &quot;+&quot; if greater than 9) indicates the number of buffers that flowed during that second: <img src="http://download-codeplex.sec.s-msft.com/Download?ProjectName=bidshelper&DownloadId=31235" alt="SSISPerformanceSuperscriptDiamonds.png" title="SSISPerformanceSuperscriptDiamonds.png" /><br /><br /><b>Custom Logging:</b> A temporary copy of the package is made and certain SSIS logging settings are enabled to allow BIDS Helper to monitor the package execution events it needs to visualize performance. (Existing SSIS logging you have setup in that package will be disabled in the temporary copy of the package, so do not be confused when you see no log events show up at your logging destination.)<br /><br /><b>Developing Simultaneously:</b> During the execution of the package by the SSIS Performance Visualization feature, you can flip back to the other tabs and continue making changes to the package without impacting the Performance Visualization execution (since it&#39;s executing from a temporary copy of the package).<br /><br /><b>Errors:</b> If any errors occur in package execution, the gantt bar (and any parent to which the error bubbles) are highlighted red. The actual error message can be seen in the Output window.<br /><br />
<h4>Alternate Views</h4>
Alternate views of performance can be seen by clicking the tree icon.<br /><br /><b>Statistics Grid:</b> The Statistics Grid view shows various performance measurements centered mostly around data flow performance:
<ul><li><i>Duration</i> - The duration of this portion of the package in seconds. SSIS logging only reports to the granularity of a second, so any portions of the package that execute in a second or less may show as zero duration, and subsequent columns may be erratic.</li>
<li><i>Inbound Rows/Sec</i> - The count of rows coming from sources. For the data flow task node itself, this is a sum of rows from any source component. For the execution tree node, this is the sum of the rows from the pipeline components that start the buffer for that execution tree.</li>
<li><i>Outbound Rows/Sec</i> - The count of rows going to destinations. For the data flow task node itself, this is a sum of rows going to any destination component. For the execution tree node, this is the sum of the rows going to any endpoints of the execution tree.</li>
<li><i>Rows Per Buffer</i> - Represents the rowcount of the buffer type used for this execution tree. This number is reported in the BufferSizeTuning log event, and the mapping between buffer types and execution trees is reported in the PipelineExecutionPlan log event. For SQL2008, the Rows Per Buffer column will be blank because the BufferSizeTuning event cannot be <a href="http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=3207359&amp;SiteID=1">tied</a> to an execution tree like in SQL2005.</li>
<li><i>Est. Bytes Per Row</i> - This is a very rough and inexact estimate of the width (in bytes) of a row in the buffer type used by an execution tree.</li>
<li><i>Inbound Kb/Sec</i> - Generally measures velocity of data coming from sources. Calculation is <i>Inbound Rows/Sec</i> * <i>Est. Bytes Per Row</i> / 1024.</li>
<li><i>Outbound Kb/Sec</i> - Generally measures velocity of data coming from sources. Calculation is <i>Inbound Rows/Sec</i> * <i>Est. Bytes Per Row</i> / 1024.</li></ul>
<img src="http://download-codeplex.sec.s-msft.com/Download?ProjectName=bidshelper&DownloadId=30953" alt="SSISPerformanceGrid.png" title="SSISPerformanceGrid.png" /><br /><br /><br /><b>Statistics Trend:</b> The Statistics Trend compares performance from one execution to another. It adds a new column for each execution and highlights whether the duration of that piece of the package was faster or slower than before.<br /><br /><img src="http://download-codeplex.sec.s-msft.com/Download?ProjectName=bidshelper&DownloadId=30954" alt="SSISPerformanceTrendGrid.png" title="SSISPerformanceTrendGrid.png" /><br /><br /><b>Exporting:</b> The copy button in the toolbar will place the contents of the current grid into the clipboard. You can then paste this data into Excel.<br /><br /><b>Execution Tree Durations:</b> Do not assume that the an Execution Tree is actually working for the entire duration of it&#39;s gantt bar as much of that bar may represent time it spent waiting on other execution trees. For a more accurate picture of the actual duration spent with each component, right click on the data flow and choose the &quot;Component Performance Breakdown&quot; menu option which executes the <a href="https://bidshelper.codeplex.com/wikipage?title=Pipeline%20Component%20Performance%20Breakdown&referringTitle=SSIS%20Performance%20Visualization">Pipeline Component Performance Breakdown</a> feature:<br /><br /><img src="http://download-codeplex.sec.s-msft.com/Download?ProjectName=bidshelper&DownloadId=32746" alt="SSISPerformanceRightClickDataFlow.png" title="SSISPerformanceRightClickDataFlow.png" /><br /><br />
<h4>Feedback</h4>
This is the initial version of the SSIS Performance Visualization feature, so feedback is requested. Please post suggestions in <a href="http://www.codeplex.com/bidshelper/WorkItem/List.aspx">Issue Tracker</a>. And please post screenshots of the gantt view of your packages to the <a href="http://www.codeplex.com/bidshelper/Thread/List.aspx">Discussions</a> area if this tool is helpful (or if it is misleading).<br />
<h4>Limitations</h4>
This feature may not work if you only install SSDT-BI for VS2012 or VS2013 without installing SSIS from the SQL install media. This is a limitation of the SSDT-BI install. For more information, see <a href="http://tlelimo001, we got a disappointing answer from Microsoft that they won&#39;t fix this issue with SSDTBI for VS2012 or VS2013. Basically installing only SSDTBI isn&#39;t enough to run dtexec so this feature won&#39;t work. You will have to install SSIS using the SQL install media for dtexec to function. For more information see [url:tlelimo001, we got a disappointing answer from Microsoft that they won&#39;t fix this issue with SSDTBI for VS2012 or VS2013. Basically installing only SSDTBI isn&#39;t enough to run dtexec so this feature won&#39;t work. You will have to install SSIS using the SQL install media for dtexec to function. See:<br />https://connect.microsoft.com/SQLServer/feedback/details/850467/dtutil-installed-with-ssdtbi-for-vs2013-requires-other-installations">this Connect bug report</a></div><div class="ClearBoth"></div>furmanggSat, 28 Mar 2015 03:56:42 GMTUpdated Wiki: SSIS Performance Visualization 20150328035642AUpdated Wiki: SSIS Performance Visualizationhttps://bidshelper.codeplex.com/wikipage?title=SSIS Performance Visualization&version=12<div class="wikidoc"><h3>SSIS Performance Visualization</h3>
Based on the theory that a picture is worth a thousand words, the SSIS Performance Visualization feature of BIDS Helper shows you a graphical gantt chart view of the execution durations and dependencies for your package to help you visualize performance.<br /><br /><img src="http://download-codeplex.sec.s-msft.com/Download?ProjectName=bidshelper&DownloadId=30951" alt="SSISPerformanceTab.png" title="SSISPerformanceTab.png" /><br /><br />The feature can be launched by right-clicking on the package in Solution Explorer and choosing &quot;Execute and Visualize Performance&quot;:<br /><br /><img src="http://download-codeplex.sec.s-msft.com/Download?ProjectName=bidshelper&DownloadId=30952" alt="SSISPerformanceMenu.png" title="SSISPerformanceMenu.png" /><br /><br /><b>Performance Tab:</b> A new Performance tab is then added to the package designer window, then the package is executed out of process using dtexec. Depending on whether you are on a 64-bit server and depending on whether you have Run64BitRuntime set to true in the project properties dialog, the 64-bit version of dtexec is used.<br /><br /><b>Gantt Bars:</b> The bars on the gantt view represent the duration and time period during which that portion of the package was being executed.<br /><br /><b>Buffer Diamonds:</b> For individual data paths inside execution trees of the data flow, the small blue diamonds represent one buffer of data flowing. Note that SSIS logging only reports to the granularity of a second, so if more than one buffer flows on a particular path in the same second, the diamonds will be on top of one another. In this case, a superscript number (2-9 or &quot;+&quot; if greater than 9) indicates the number of buffers that flowed during that second: <img src="http://download-codeplex.sec.s-msft.com/Download?ProjectName=bidshelper&DownloadId=31235" alt="SSISPerformanceSuperscriptDiamonds.png" title="SSISPerformanceSuperscriptDiamonds.png" /><br /><br /><b>Custom Logging:</b> A temporary copy of the package is made and certain SSIS logging settings are enabled to allow BIDS Helper to monitor the package execution events it needs to visualize performance. (Existing SSIS logging you have setup in that package will be disabled in the temporary copy of the package, so do not be confused when you see no log events show up at your logging destination.)<br /><br /><b>Developing Simultaneously:</b> During the execution of the package by the SSIS Performance Visualization feature, you can flip back to the other tabs and continue making changes to the package without impacting the Performance Visualization execution (since it&#39;s executing from a temporary copy of the package).<br /><br /><b>Errors:</b> If any errors occur in package execution, the gantt bar (and any parent to which the error bubbles) are highlighted red. The actual error message can be seen in the Output window.<br /><br />
<h4>Alternate Views</h4>
Alternate views of performance can be seen by clicking the tree icon.<br /><br /><b>Statistics Grid:</b> The Statistics Grid view shows various performance measurements centered mostly around data flow performance:
<ul><li><i>Duration</i> - The duration of this portion of the package in seconds. SSIS logging only reports to the granularity of a second, so any portions of the package that execute in a second or less may show as zero duration, and subsequent columns may be erratic.</li>
<li><i>Inbound Rows/Sec</i> - The count of rows coming from sources. For the data flow task node itself, this is a sum of rows from any source component. For the execution tree node, this is the sum of the rows from the pipeline components that start the buffer for that execution tree.</li>
<li><i>Outbound Rows/Sec</i> - The count of rows going to destinations. For the data flow task node itself, this is a sum of rows going to any destination component. For the execution tree node, this is the sum of the rows going to any endpoints of the execution tree.</li>
<li><i>Rows Per Buffer</i> - Represents the rowcount of the buffer type used for this execution tree. This number is reported in the BufferSizeTuning log event, and the mapping between buffer types and execution trees is reported in the PipelineExecutionPlan log event. For SQL2008, the Rows Per Buffer column will be blank because the BufferSizeTuning event cannot be <a href="http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=3207359&amp;SiteID=1">tied</a> to an execution tree like in SQL2005.</li>
<li><i>Est. Bytes Per Row</i> - This is a very rough and inexact estimate of the width (in bytes) of a row in the buffer type used by an execution tree.</li>
<li><i>Inbound Kb/Sec</i> - Generally measures velocity of data coming from sources. Calculation is <i>Inbound Rows/Sec</i> * <i>Est. Bytes Per Row</i> / 1024.</li>
<li><i>Outbound Kb/Sec</i> - Generally measures velocity of data coming from sources. Calculation is <i>Inbound Rows/Sec</i> * <i>Est. Bytes Per Row</i> / 1024.</li></ul>
<img src="http://download-codeplex.sec.s-msft.com/Download?ProjectName=bidshelper&DownloadId=30953" alt="SSISPerformanceGrid.png" title="SSISPerformanceGrid.png" /><br /><br /><br /><b>Statistics Trend:</b> The Statistics Trend compares performance from one execution to another. It adds a new column for each execution and highlights whether the duration of that piece of the package was faster or slower than before.<br /><br /><img src="http://download-codeplex.sec.s-msft.com/Download?ProjectName=bidshelper&DownloadId=30954" alt="SSISPerformanceTrendGrid.png" title="SSISPerformanceTrendGrid.png" /><br /><br /><b>Exporting:</b> The copy button in the toolbar will place the contents of the current grid into the clipboard. You can then paste this data into Excel.<br /><br /><b>Execution Tree Durations:</b> Do not assume that the an Execution Tree is actually working for the entire duration of it&#39;s gantt bar as much of that bar may represent time it spent waiting on other execution trees. For a more accurate picture of the actual duration spent with each component, right click on the data flow and choose the &quot;Component Performance Breakdown&quot; menu option which executes the <a href="https://bidshelper.codeplex.com/wikipage?title=Pipeline%20Component%20Performance%20Breakdown&referringTitle=SSIS%20Performance%20Visualization">Pipeline Component Performance Breakdown</a> feature:<br /><br /><img src="http://download-codeplex.sec.s-msft.com/Download?ProjectName=bidshelper&DownloadId=32746" alt="SSISPerformanceRightClickDataFlow.png" title="SSISPerformanceRightClickDataFlow.png" /><br /><br />
<h4>Feedback</h4>
This is the initial version of the SSIS Performance Visualization feature, so feedback is requested. Please post suggestions in <a href="http://www.codeplex.com/bidshelper/WorkItem/List.aspx">Issue Tracker</a>. And please post screenshots of the gantt view of your packages to the <a href="http://www.codeplex.com/bidshelper/Thread/List.aspx">Discussions</a> area if this tool is helpful (or if it is misleading).<br />
<h4>Limitations</h4>
This feature may not work if you only install SSDT-BI for VS2012 or VS2013 without installing SSIS from the SQL install media. This is a limitation of the SSDT-BI install. For more information, see <a href="http://tlelimo001, we got a disappointing answer from Microsoft that they won&#39;t fix this issue with SSDTBI for VS2012 or VS2013. Basically installing only SSDTBI isn&#39;t enough to run dtexec so this feature won&#39;t work. You will have to install SSIS using the SQL install media for dtexec to function. See <br />[url:https://connect.microsoft.com/SQLServer/feedback/details/850467/dtutil-installed-with-ssdtbi-for-vs2013-requires-other-installations">this Connect bug report</a> for more info.</div><div class="ClearBoth"></div>furmanggSat, 28 Mar 2015 03:56:03 GMTUpdated Wiki: SSIS Performance Visualization 20150328035603AUpdated Wiki: SSIS Performance Visualizationhttps://bidshelper.codeplex.com/wikipage?title=SSIS Performance Visualization&version=11<div class="wikidoc"><h3>SSIS Performance Visualization</h3>
Based on the theory that a picture is worth a thousand words, the SSIS Performance Visualization feature of BIDS Helper shows you a graphical gantt chart view of the execution durations and dependencies for your package to help you visualize performance.<br /><br /><img src="http://download-codeplex.sec.s-msft.com/Download?ProjectName=bidshelper&DownloadId=30951" alt="SSISPerformanceTab.png" title="SSISPerformanceTab.png" /><br /><br />The feature can be launched by right-clicking on the package in Solution Explorer and choosing &quot;Execute and Visualize Performance&quot;:<br /><br /><img src="http://download-codeplex.sec.s-msft.com/Download?ProjectName=bidshelper&DownloadId=30952" alt="SSISPerformanceMenu.png" title="SSISPerformanceMenu.png" /><br /><br /><b>Performance Tab:</b> A new Performance tab is then added to the package designer window, then the package is executed out of process using dtexec. Depending on whether you are on a 64-bit server and depending on whether you have Run64BitRuntime set to true in the project properties dialog, the 64-bit version of dtexec is used.<br /><br /><b>Gantt Bars:</b> The bars on the gantt view represent the duration and time period during which that portion of the package was being executed.<br /><br /><b>Buffer Diamonds:</b> For individual data paths inside execution trees of the data flow, the small blue diamonds represent one buffer of data flowing. Note that SSIS logging only reports to the granularity of a second, so if more than one buffer flows on a particular path in the same second, the diamonds will be on top of one another. In this case, a superscript number (2-9 or &quot;+&quot; if greater than 9) indicates the number of buffers that flowed during that second: <img src="http://download-codeplex.sec.s-msft.com/Download?ProjectName=bidshelper&DownloadId=31235" alt="SSISPerformanceSuperscriptDiamonds.png" title="SSISPerformanceSuperscriptDiamonds.png" /><br /><br /><b>Custom Logging:</b> A temporary copy of the package is made and certain SSIS logging settings are enabled to allow BIDS Helper to monitor the package execution events it needs to visualize performance. (Existing SSIS logging you have setup in that package will be disabled in the temporary copy of the package, so do not be confused when you see no log events show up at your logging destination.)<br /><br /><b>Developing Simultaneously:</b> During the execution of the package by the SSIS Performance Visualization feature, you can flip back to the other tabs and continue making changes to the package without impacting the Performance Visualization execution (since it&#39;s executing from a temporary copy of the package).<br /><br /><b>Errors:</b> If any errors occur in package execution, the gantt bar (and any parent to which the error bubbles) are highlighted red. The actual error message can be seen in the Output window.<br /><br />
<h4>Alternate Views</h4>
Alternate views of performance can be seen by clicking the tree icon.<br /><br /><b>Statistics Grid:</b> The Statistics Grid view shows various performance measurements centered mostly around data flow performance:
<ul><li><i>Duration</i> - The duration of this portion of the package in seconds. SSIS logging only reports to the granularity of a second, so any portions of the package that execute in a second or less may show as zero duration, and subsequent columns may be erratic.</li>
<li><i>Inbound Rows/Sec</i> - The count of rows coming from sources. For the data flow task node itself, this is a sum of rows from any source component. For the execution tree node, this is the sum of the rows from the pipeline components that start the buffer for that execution tree.</li>
<li><i>Outbound Rows/Sec</i> - The count of rows going to destinations. For the data flow task node itself, this is a sum of rows going to any destination component. For the execution tree node, this is the sum of the rows going to any endpoints of the execution tree.</li>
<li><i>Rows Per Buffer</i> - Represents the rowcount of the buffer type used for this execution tree. This number is reported in the BufferSizeTuning log event, and the mapping between buffer types and execution trees is reported in the PipelineExecutionPlan log event. For SQL2008, the Rows Per Buffer column will be blank because the BufferSizeTuning event cannot be <a href="http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=3207359&amp;SiteID=1">tied</a> to an execution tree like in SQL2005.</li>
<li><i>Est. Bytes Per Row</i> - This is a very rough and inexact estimate of the width (in bytes) of a row in the buffer type used by an execution tree.</li>
<li><i>Inbound Kb/Sec</i> - Generally measures velocity of data coming from sources. Calculation is <i>Inbound Rows/Sec</i> * <i>Est. Bytes Per Row</i> / 1024.</li>
<li><i>Outbound Kb/Sec</i> - Generally measures velocity of data coming from sources. Calculation is <i>Inbound Rows/Sec</i> * <i>Est. Bytes Per Row</i> / 1024.</li></ul>
<img src="http://download-codeplex.sec.s-msft.com/Download?ProjectName=bidshelper&DownloadId=30953" alt="SSISPerformanceGrid.png" title="SSISPerformanceGrid.png" /><br /><br /><br /><b>Statistics Trend:</b> The Statistics Trend compares performance from one execution to another. It adds a new column for each execution and highlights whether the duration of that piece of the package was faster or slower than before.<br /><br /><img src="http://download-codeplex.sec.s-msft.com/Download?ProjectName=bidshelper&DownloadId=30954" alt="SSISPerformanceTrendGrid.png" title="SSISPerformanceTrendGrid.png" /><br /><br /><b>Exporting:</b> The copy button in the toolbar will place the contents of the current grid into the clipboard. You can then paste this data into Excel.<br /><br /><b>Execution Tree Durations:</b> Do not assume that the an Execution Tree is actually working for the entire duration of it&#39;s gantt bar as much of that bar may represent time it spent waiting on other execution trees. For a more accurate picture of the actual duration spent with each component, right click on the data flow and choose the &quot;Component Performance Breakdown&quot; menu option which executes the <a href="https://bidshelper.codeplex.com/wikipage?title=Pipeline%20Component%20Performance%20Breakdown&referringTitle=SSIS%20Performance%20Visualization">Pipeline Component Performance Breakdown</a> feature:<br /><br /><img src="http://download-codeplex.sec.s-msft.com/Download?ProjectName=bidshelper&DownloadId=32746" alt="SSISPerformanceRightClickDataFlow.png" title="SSISPerformanceRightClickDataFlow.png" /><br /><br />
<h4>Feedback</h4>
This is the initial version of the SSIS Performance Visualization feature, so feedback is requested. Please post suggestions in <a href="http://www.codeplex.com/bidshelper/WorkItem/List.aspx">Issue Tracker</a>. And please post screenshots of the gantt view of your packages to the <a href="http://www.codeplex.com/bidshelper/Thread/List.aspx">Discussions</a> area if this tool is helpful (or if it is misleading).<br />
<h4>Limitations</h4>
This feature may not work if you only install SSDT-BI for VS2012 or VS2013 without installing SSIS from the SQL install media. This is a limitation of the SSDT-BI install. For more information, see <span class="unresolved">Cannot resolve link macro, invalid number of parameters.</span> for more info.</div><div class="ClearBoth"></div>furmanggSat, 28 Mar 2015 03:55:28 GMTUpdated Wiki: SSIS Performance Visualization 20150328035528AUpdated Wiki: SSIS Performance Visualizationhttps://bidshelper.codeplex.com/wikipage?title=SSIS Performance Visualization&version=10<div class="wikidoc"><h3>SSIS Performance Visualization</h3>
Based on the theory that a picture is worth a thousand words, the SSIS Performance Visualization feature of BIDS Helper shows you a graphical gantt chart view of the execution durations and dependencies for your package to help you visualize performance.<br /><br /><img src="http://download-codeplex.sec.s-msft.com/Download?ProjectName=bidshelper&DownloadId=30951" alt="SSISPerformanceTab.png" title="SSISPerformanceTab.png" /><br /><br />The feature can be launched by right-clicking on the package in Solution Explorer and choosing &quot;Execute and Visualize Performance&quot;:<br /><br /><img src="http://download-codeplex.sec.s-msft.com/Download?ProjectName=bidshelper&DownloadId=30952" alt="SSISPerformanceMenu.png" title="SSISPerformanceMenu.png" /><br /><br /><b>Performance Tab:</b> A new Performance tab is then added to the package designer window, then the package is executed out of process using dtexec. Depending on whether you are on a 64-bit server and depending on whether you have Run64BitRuntime set to true in the project properties dialog, the 64-bit version of dtexec is used.<br /><br /><b>Gantt Bars:</b> The bars on the gantt view represent the duration and time period during which that portion of the package was being executed.<br /><br /><b>Buffer Diamonds:</b> For individual data paths inside execution trees of the data flow, the small blue diamonds represent one buffer of data flowing. Note that SSIS logging only reports to the granularity of a second, so if more than one buffer flows on a particular path in the same second, the diamonds will be on top of one another. In this case, a superscript number (2-9 or &quot;+&quot; if greater than 9) indicates the number of buffers that flowed during that second: <img src="http://download-codeplex.sec.s-msft.com/Download?ProjectName=bidshelper&DownloadId=31235" alt="SSISPerformanceSuperscriptDiamonds.png" title="SSISPerformanceSuperscriptDiamonds.png" /><br /><br /><b>Custom Logging:</b> A temporary copy of the package is made and certain SSIS logging settings are enabled to allow BIDS Helper to monitor the package execution events it needs to visualize performance. (Existing SSIS logging you have setup in that package will be disabled in the temporary copy of the package, so do not be confused when you see no log events show up at your logging destination.)<br /><br /><b>Developing Simultaneously:</b> During the execution of the package by the SSIS Performance Visualization feature, you can flip back to the other tabs and continue making changes to the package without impacting the Performance Visualization execution (since it&#39;s executing from a temporary copy of the package).<br /><br /><b>Errors:</b> If any errors occur in package execution, the gantt bar (and any parent to which the error bubbles) are highlighted red. The actual error message can be seen in the Output window.<br /><br />
<h4>Alternate Views</h4>
Alternate views of performance can be seen by clicking the tree icon.<br /><br /><b>Statistics Grid:</b> The Statistics Grid view shows various performance measurements centered mostly around data flow performance:
<ul><li><i>Duration</i> - The duration of this portion of the package in seconds. SSIS logging only reports to the granularity of a second, so any portions of the package that execute in a second or less may show as zero duration, and subsequent columns may be erratic.</li>
<li><i>Inbound Rows/Sec</i> - The count of rows coming from sources. For the data flow task node itself, this is a sum of rows from any source component. For the execution tree node, this is the sum of the rows from the pipeline components that start the buffer for that execution tree.</li>
<li><i>Outbound Rows/Sec</i> - The count of rows going to destinations. For the data flow task node itself, this is a sum of rows going to any destination component. For the execution tree node, this is the sum of the rows going to any endpoints of the execution tree.</li>
<li><i>Rows Per Buffer</i> - Represents the rowcount of the buffer type used for this execution tree. This number is reported in the BufferSizeTuning log event, and the mapping between buffer types and execution trees is reported in the PipelineExecutionPlan log event. For SQL2008, the Rows Per Buffer column will be blank because the BufferSizeTuning event cannot be <a href="http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=3207359&amp;SiteID=1">tied</a> to an execution tree like in SQL2005.</li>
<li><i>Est. Bytes Per Row</i> - This is a very rough and inexact estimate of the width (in bytes) of a row in the buffer type used by an execution tree.</li>
<li><i>Inbound Kb/Sec</i> - Generally measures velocity of data coming from sources. Calculation is <i>Inbound Rows/Sec</i> * <i>Est. Bytes Per Row</i> / 1024.</li>
<li><i>Outbound Kb/Sec</i> - Generally measures velocity of data coming from sources. Calculation is <i>Inbound Rows/Sec</i> * <i>Est. Bytes Per Row</i> / 1024.</li></ul>
<img src="http://download-codeplex.sec.s-msft.com/Download?ProjectName=bidshelper&DownloadId=30953" alt="SSISPerformanceGrid.png" title="SSISPerformanceGrid.png" /><br /><br /><br /><b>Statistics Trend:</b> The Statistics Trend compares performance from one execution to another. It adds a new column for each execution and highlights whether the duration of that piece of the package was faster or slower than before.<br /><br /><img src="http://download-codeplex.sec.s-msft.com/Download?ProjectName=bidshelper&DownloadId=30954" alt="SSISPerformanceTrendGrid.png" title="SSISPerformanceTrendGrid.png" /><br /><br /><b>Exporting:</b> The copy button in the toolbar will place the contents of the current grid into the clipboard. You can then paste this data into Excel.<br /><br /><b>Execution Tree Durations:</b> Do not assume that the an Execution Tree is actually working for the entire duration of it&#39;s gantt bar as much of that bar may represent time it spent waiting on other execution trees. For a more accurate picture of the actual duration spent with each component, right click on the data flow and choose the &quot;Component Performance Breakdown&quot; menu option which executes the <a href="https://bidshelper.codeplex.com/wikipage?title=Pipeline%20Component%20Performance%20Breakdown&referringTitle=SSIS%20Performance%20Visualization">Pipeline Component Performance Breakdown</a> feature:<br /><br /><img src="http://download-codeplex.sec.s-msft.com/Download?ProjectName=bidshelper&DownloadId=32746" alt="SSISPerformanceRightClickDataFlow.png" title="SSISPerformanceRightClickDataFlow.png" /><br /><br />
<h4>Feedback</h4>
This is the initial version of the SSIS Performance Visualization feature, so feedback is requested. Please post suggestions in <a href="http://www.codeplex.com/bidshelper/WorkItem/List.aspx">Issue Tracker</a>. And please post screenshots of the gantt view of your packages to the <a href="http://www.codeplex.com/bidshelper/Thread/List.aspx">Discussions</a> area if this tool is helpful (or if it is misleading).<br />
<h4>Limitations</h4>
This feature may not work if you only install SSDT-BI for VS2012 or VS2013 without installing SSIS from the SQL install media. This is a limitation of the SSDT-BI install. For more information, see <span class="unresolved">Cannot resolve link macro, invalid number of parameters.</span> for more info.</div><div class="ClearBoth"></div>furmanggSat, 28 Mar 2015 03:54:40 GMTUpdated Wiki: SSIS Performance Visualization 20150328035440AUpdated Wiki: SSIS Performance Visualizationhttps://bidshelper.codeplex.com/wikipage?title=SSIS Performance Visualization&version=9<div class="wikidoc"><h3>SSIS Performance Visualization</h3>
Based on the theory that a picture is worth a thousand words, the SSIS Performance Visualization feature of BIDS Helper shows you a graphical gantt chart view of the execution durations and dependencies for your package to help you visualize performance.<br /><br /><img src="http://download-codeplex.sec.s-msft.com/Download?ProjectName=bidshelper&DownloadId=30951" alt="SSISPerformanceTab.png" title="SSISPerformanceTab.png" /><br /><br />The feature can be launched by right-clicking on the package in Solution Explorer and choosing &quot;Execute and Visualize Performance&quot;:<br /><br /><img src="http://download-codeplex.sec.s-msft.com/Download?ProjectName=bidshelper&DownloadId=30952" alt="SSISPerformanceMenu.png" title="SSISPerformanceMenu.png" /><br /><br /><b>Performance Tab:</b> A new Performance tab is then added to the package designer window, then the package is executed out of process using dtexec. Depending on whether you are on a 64-bit server and depending on whether you have Run64BitRuntime set to true in the project properties dialog, the 64-bit version of dtexec is used.<br /><br /><b>Gantt Bars:</b> The bars on the gantt view represent the duration and time period during which that portion of the package was being executed.<br /><br /><b>Buffer Diamonds:</b> For individual data paths inside execution trees of the data flow, the small blue diamonds represent one buffer of data flowing. Note that SSIS logging only reports to the granularity of a second, so if more than one buffer flows on a particular path in the same second, the diamonds will be on top of one another. In this case, a superscript number (2-9 or &quot;+&quot; if greater than 9) indicates the number of buffers that flowed during that second: <img src="http://download-codeplex.sec.s-msft.com/Download?ProjectName=bidshelper&DownloadId=31235" alt="SSISPerformanceSuperscriptDiamonds.png" title="SSISPerformanceSuperscriptDiamonds.png" /><br /><br /><b>Custom Logging:</b> A temporary copy of the package is made and certain SSIS logging settings are enabled to allow BIDS Helper to monitor the package execution events it needs to visualize performance. (Existing SSIS logging you have setup in that package will be disabled in the temporary copy of the package, so do not be confused when you see no log events show up at your logging destination.)<br /><br /><b>Developing Simultaneously:</b> During the execution of the package by the SSIS Performance Visualization feature, you can flip back to the other tabs and continue making changes to the package without impacting the Performance Visualization execution (since it&#39;s executing from a temporary copy of the package).<br /><br /><b>Errors:</b> If any errors occur in package execution, the gantt bar (and any parent to which the error bubbles) are highlighted red. The actual error message can be seen in the Output window.<br /><br />
<h4>Alternate Views</h4>
Alternate views of performance can be seen by clicking the tree icon.<br /><br /><b>Statistics Grid:</b> The Statistics Grid view shows various performance measurements centered mostly around data flow performance:
<ul><li><i>Duration</i> - The duration of this portion of the package in seconds. SSIS logging only reports to the granularity of a second, so any portions of the package that execute in a second or less may show as zero duration, and subsequent columns may be erratic.</li>
<li><i>Inbound Rows/Sec</i> - The count of rows coming from sources. For the data flow task node itself, this is a sum of rows from any source component. For the execution tree node, this is the sum of the rows from the pipeline components that start the buffer for that execution tree.</li>
<li><i>Outbound Rows/Sec</i> - The count of rows going to destinations. For the data flow task node itself, this is a sum of rows going to any destination component. For the execution tree node, this is the sum of the rows going to any endpoints of the execution tree.</li>
<li><i>Rows Per Buffer</i> - Represents the rowcount of the buffer type used for this execution tree. This number is reported in the BufferSizeTuning log event, and the mapping between buffer types and execution trees is reported in the PipelineExecutionPlan log event. For SQL2008, the Rows Per Buffer column will be blank because the BufferSizeTuning event cannot be <a href="http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=3207359&amp;SiteID=1">tied</a> to an execution tree like in SQL2005.</li>
<li><i>Est. Bytes Per Row</i> - This is a very rough and inexact estimate of the width (in bytes) of a row in the buffer type used by an execution tree.</li>
<li><i>Inbound Kb/Sec</i> - Generally measures velocity of data coming from sources. Calculation is <i>Inbound Rows/Sec</i> * <i>Est. Bytes Per Row</i> / 1024.</li>
<li><i>Outbound Kb/Sec</i> - Generally measures velocity of data coming from sources. Calculation is <i>Inbound Rows/Sec</i> * <i>Est. Bytes Per Row</i> / 1024.</li></ul>
<img src="http://download-codeplex.sec.s-msft.com/Download?ProjectName=bidshelper&DownloadId=30953" alt="SSISPerformanceGrid.png" title="SSISPerformanceGrid.png" /><br /><br /><br /><b>Statistics Trend:</b> The Statistics Trend compares performance from one execution to another. It adds a new column for each execution and highlights whether the duration of that piece of the package was faster or slower than before.<br /><br /><img src="http://download-codeplex.sec.s-msft.com/Download?ProjectName=bidshelper&DownloadId=30954" alt="SSISPerformanceTrendGrid.png" title="SSISPerformanceTrendGrid.png" /><br /><br /><b>Exporting:</b> The copy button in the toolbar will place the contents of the current grid into the clipboard. You can then paste this data into Excel.<br /><br /><b>Execution Tree Durations:</b> Do not assume that the an Execution Tree is actually working for the entire duration of it&#39;s gantt bar as much of that bar may represent time it spent waiting on other execution trees. For a more accurate picture of the actual duration spent with each component, right click on the data flow and choose the &quot;Component Performance Breakdown&quot; menu option which executes the <a href="https://bidshelper.codeplex.com/wikipage?title=Pipeline%20Component%20Performance%20Breakdown&referringTitle=SSIS%20Performance%20Visualization">Pipeline Component Performance Breakdown</a> feature:<br /><br /><img src="http://download-codeplex.sec.s-msft.com/Download?ProjectName=bidshelper&DownloadId=32746" alt="SSISPerformanceRightClickDataFlow.png" title="SSISPerformanceRightClickDataFlow.png" /><br /><br />
<h4>Feedback</h4>
This is the initial version of the SSIS Performance Visualization feature, so feedback is requested. Please post suggestions in <a href="http://www.codeplex.com/bidshelper/WorkItem/List.aspx">Issue Tracker</a>. And please post screenshots of the gantt view of your packages to the <a href="http://www.codeplex.com/bidshelper/Thread/List.aspx">Discussions</a> area if this tool is helpful (or if it is misleading).<br />
<h4>Limitations</h4>
This feature may not work if you only install SSDT-BI for VS2012 or VS2013 without installing SSIS from the SQL install media. This is a limitation of the SSDT-BI install. For more information, see <a href="http://tlelimo001, we got a disappointing answer from Microsoft that they won&#39;t fix this issue with SSDTBI for VS2012 or VS2013. Basically installing only SSDTBI isn&#39;t enough to run dtexec so this feature won&#39;t work. You will have to install SSIS using the SQL install media for dtexec to function. See:<br />https://connect.microsoft.com/SQLServer/feedback/details/850467/dtutil-installed-with-ssdtbi-for-vs2013-requires-other-installations">this Connect bug report</a>.</div><div class="ClearBoth"></div>furmanggSat, 28 Mar 2015 03:53:47 GMTUpdated Wiki: SSIS Performance Visualization 20150328035347ANew Comment on "SSIS Performance Visualization"https://bidshelper.codeplex.com/wikipage?title=SSIS Performance Visualization&ANCHOR#C31509tlelimo001, we got a disappointing answer from Microsoft that they won&#39;t fix this issue with SSDTBI for VS2012 or VS2013. Basically installing only SSDTBI isn&#39;t enough to run dtexec so this feature won&#39;t work. You will have to install SSIS using the SQL install media for dtexec to function. See&#58;&#10;https&#58;&#47;&#47;connect.microsoft.com&#47;SQLServer&#47;feedback&#47;details&#47;850467&#47;dtutil-installed-with-ssdtbi-for-vs2013-requires-other-installationsfurmanggSat, 28 Mar 2015 03:51:49 GMTNew Comment on "SSIS Performance Visualization" 20150328035149ANew Comment on "SSIS Performance Visualization"https://bidshelper.codeplex.com/wikipage?title=SSIS Performance Visualization&ANCHOR#C31504Looks REALLY cool. I am however having a problem using it. I get an error saying &#34;To run a SSIS package outside of SQL Server Data Tools you must install Derived Column of Integration Services or higher.&#34; I have installed SSDTBI for VS2013 and Sql server 2014. Any suggestions&#63;tlelimo001Fri, 27 Mar 2015 11:52:07 GMTNew Comment on "SSIS Performance Visualization" 20150327115207AUpdated Wiki: Manually Configuring Biml Package Generatorhttps://bidshelper.codeplex.com/wikipage?title=Manually Configuring Biml Package Generator&version=8<div class="wikidoc">If you are doing an xcopy deployment of BIDS Helper, or experience any issues with the Biml Package Generator, please review the items on this page to make sure your environment is configured for the best experience. None of these are required to use the Biml Package Generator, but they make it more enjoyable.<br /><br />The simplest option is to make sure that Biml.xsd has been copied to the Visual Studio registered schemas folder. This will enable Intellisense when working with Biml files in BIDS. The following tables shows the location of schemas folder for each version of Visual Studio.<br /><br /><table><tr><th>Version</th><th>Folder</th></tr>
<tr><td>2005</td><td>C:\Program Files (x86)\Microsoft Visual Studio 8\Xml\Schemas</td></tr>
<tr><td>2008</td><td>C:\Program Files (x86)\Microsoft Visual Studio 9.0\Xml\Schemas</td></tr>
<tr><td>2010</td><td>C:\Program Files (x86)\Microsoft Visual Studio 10.0\Xml\Schemas</td></tr>
<tr><td>2012</td><td>C:\Program Files (x86)\Microsoft Visual Studio 11.0\Xml\Schemas</td></tr>
<tr><td>2013</td><td>C:\Program Files (x86)\Microsoft Visual Studio 12.0\Xml\Schemas</td></tr></table><br /><br />If you are running on a 32-bit operating system, your folders will start <i>C:\Program Files\</i> instead of <i>C:\Program Files (x86)\</i>.<br /><br />Alternatively if you don&#39;t want to copy Bml.xsd to the schemas directory, you can manually associate it with a Biml file in the Visual Studio environment. To do this, open the Biml file in BIDS, and then open the Properties window (F4 is the default shortcut). Select the Schemas property, and click the ellipsis button to browse to your location for Biml.xsd. If you take this approach, you will have to re-associate the .xsd each time you open a Biml file.</div><div class="ClearBoth"></div>DarrenSQLISWed, 18 Mar 2015 10:51:32 GMTUpdated Wiki: Manually Configuring Biml Package Generator 20150318105132AUpdated Wiki: Manually Configuring Biml Package Generatorhttps://bidshelper.codeplex.com/wikipage?title=Manually Configuring Biml Package Generator&version=7<div class="wikidoc">If you are doing an xcopy deployment of BIDS Helper, or experience any issues with the Biml Package Generator, please review the items on this page to make sure your environment is configured for the best experience. None of these are required to use the Biml Package Generator, but they make it more enjoyable.<br />
<ul><li>Make sure that Biml.xsd has been copied to the Visual Studio registered schemas folder. This will enable Intellisense when working with Biml files in BIDS. The following tables shows the location of schemas folder for each version of Visual Studio.</li></ul>
<br /><table><tr><th>Version</th><th>Folder</th></tr>
<tr><td>2005</td><td>C:\Program Files (x86)\Microsoft Visual Studio 8\Xml\Schemas</td></tr>
<tr><td>2008</td><td>C:\Program Files (x86)\Microsoft Visual Studio 9.0\Xml\Schemas</td></tr>
<tr><td>2010</td><td>C:\Program Files (x86)\Microsoft Visual Studio 10.0\Xml\Schemas</td></tr>
<tr><td>2012</td><td>C:\Program Files (x86)\Microsoft Visual Studio 11.0\Xml\Schemas</td></tr>
<tr><td>2013</td><td>C:\Program Files (x86)\Microsoft Visual Studio 12.0\Xml\Schemas</td></tr></table><br /><br />If you are running on a 32-bit operating system, your folders will start <i>C:\Program Files\</i> instead of <i>C:\Program Files (x86)\</i>.<br />
<ul><li>If you don&#39;t want to copy Bml.xsd to the schemas directory, you can associate it with a Biml file in the Visual Studio environment. To do this, open the Biml file in BIDS, and then open the Properties window (F4 is the default shortcut). Select the Schemas property, and click the ellipsis button to browse to your location for Biml.xsd. If you take this approach, you will have to re-associate the .xsd each time you open a Biml file.</li></ul></div><div class="ClearBoth"></div>DarrenSQLISWed, 18 Mar 2015 10:50:00 GMTUpdated Wiki: Manually Configuring Biml Package Generator 20150318105000AUpdated Wiki: Biml FAQhttps://bidshelper.codeplex.com/wikipage?title=Biml FAQ&version=9<div class="wikidoc"><h2>Biml FAQ</h2>
<b>Q: Why would I want to use Biml?</b><br /><b>A:</b> Creating many similar Integration Services packages is a common task in ETL. Manually creating SSIS packages can consume a significant amount of time. And after you are done, if you decided you need to tweak your &quot;template&quot; to add in a new logging step or correct a bug, you will have to manually edit all the SSIS packages to manually add that change. A simple Biml script, on the other hand, can generate your SSIS packages for you and allow you to regenerate all your SSIS packages if you need to make a small change at a later date. For example, see <a href="http://Copy Data Dynamically with BimlScript">this example</a> as an example of how a simple SQL metadata query and a loop can generate many SSIS packages dynamically.<br /><br /><b>Q: How can I learn Biml?</b><br /><b>A:</b> The <a href="https://bidshelper.codeplex.com/wikipage?title=Samples%20and%20Tutorials&referringTitle=Biml%20FAQ">Samples and Tutorials</a> page has many basic Biml examples to get you started. Additionally, the <a href="http://BimlScript.com">BimlScript.com</a> website is another great resource.<br /><br /><b>Q: Is Biml inside BIDS Helper free?</b><br /><b>A:</b> Yes, the version Biml inside BIDS Helper has been provided free of charge for BIDS Helper users. Any sources and destinations that come with the SSIS install itself are supported for free in BIDS Helper Biml. Other sources and destinations like the Attunity Oracle source and destination or the PDW destination which are installed &quot;after market&quot; may require the paid version of <a href="http://www.varigence.com/Products/Mist/Capabilities">Mist</a>. For example, using the convenient <a href="http://bimlscript.com/Snippet/Details/1124">SqlServerPdwDestination tag</a> requires Mist (as you will get a &quot;No translator was found for the component &lt;your component&gt; of type AstSqlServerPdwDestinationNode in Dataflow &lt;your data flow&gt;&quot; error in BIDS Helper), however you may be able to accomplish the same thing with the <a href="http://bimlscript.com/Walkthrough/Details/67">much more complex CustomComponent tag</a> using the free version of Biml in BIDS Helper.<br /><br /><b>Q: Is there a way to have Biml generate packages unattended during an automated nightly build?</b><br /><b>A:</b> Biml inside BIDS Helper requires a user to start the Biml Package Generator. To automate SSIS package generation from Biml scripts unattended, you must purchase the paid version of <a href="http://www.varigence.com/Products/Mist/Capabilities">Mist</a> and use a command line tool called <a href="http://www.varigence.com/Documentation/mist/Article/Hadron+Compiler+Command+Line+Options">Hadron.exe</a>. Note Hadron is being renamed bimlc.exe as part of Mist 4.0.<br /><br /><b>Q: I see some scripts on the internet mentioning Hadron but I don&#39;t see that in the Biml schema. What gives?</b><br /><b>A:</b> The term Hadron is being renamed to Biml in BIDS Helper 1.7. For example, any code which reads:<br />&lt;#@ import namespace=&quot;Varigence.Hadron.CoreLowerer.SchemaManagement&quot; #&gt;<br />Should be changed to:<br />&lt;#@ import namespace=&quot;Varigence.Biml.CoreLowerer.SchemaManagement&quot; #&gt;<br /><br /><b>Q: Can I reverse engineer a working SSIS package to see the corresponding Biml?</b><br /><b>A:</b> The paid version of <a href="http://www.varigence.com/Products/Mist/Capabilities">Mist</a> supports reverse engineering an SSIS package (.dtsx file) into a Biml script.<br /><br /><b>Q: My BimlScript is not behaving like I expect. How can I debug it?</b><br /><b>A:</b> Besides adding a code block to write to a log file, you can also add popups to your BimlScript expansion as described in this <a href="http://www.bimlgeek.com/blog/popup-a-messagebox-within-bimlscript">blog post</a>.<br /><br /><b>Q: When I copy and paste into a .biml file in Visual Studio the script doesn&#39;t work and the indenting is all wrong. How can I fix this?</b><br /><b>A:</b> The easiest way is to press Ctrl-V (to paste) and then Ctrl-Z (to undo indenting and formatting). This trick is further described and explained <a href="http://bimlscript.com/Walkthrough/Details/45">here</a>.</div><div class="ClearBoth"></div>furmanggWed, 18 Mar 2015 02:17:33 GMTUpdated Wiki: Biml FAQ 20150318021733AUpdated Wiki: Documentationhttps://bidshelper.codeplex.com/documentation?version=8<div class="wikidoc"><h3>Installation</h3>
To install BIDS Helper, download the installer from the <a href="http://bidshelper.codeplex.com/Release/ProjectReleases.aspx">Releases</a> tab.<br />If for some reason you cannot use the installer the latest release includes an <a href="https://bidshelper.codeplex.com/wikipage?title=xcopy%20deploy&referringTitle=Documentation">xcopy deploy</a> option.<br />
<h3>Suggestions</h3>
We are always looking for potential new features, and we maintain a list of items in the <a href="http://bidshelper.codeplex.com/WorkItem/List.aspx">Issue Tracker</a>. If you see an item you would like addressed in the Issue Tracker please vote for it. If you have a feature you would like to see added, drop us a line on the discussion forum.<br />
<h3>Features</h3>
<ul><li><b>Analysis Services Multidimensional</b>
<ul><li><a href="https://bidshelper.codeplex.com/wikipage?title=Aggregation%20Manager&referringTitle=Documentation">Aggregation Manager</a></li>
<li><a href="https://bidshelper.codeplex.com/wikipage?title=Calculation%20Helpers&referringTitle=Documentation">Calculation Helpers</a></li>
<li><a href="https://bidshelper.codeplex.com/wikipage?title=Column%20Usage%20Reports&referringTitle=Documentation">Column Usage Reports</a></li>
<li><a href="https://bidshelper.codeplex.com/wikipage?title=Delete%20Unused%20Aggregations&referringTitle=Documentation">Delete Unused Aggregations</a></li>
<li><a href="https://bidshelper.codeplex.com/wikipage?title=Delete%20Unused%20Indexes&referringTitle=Documentation">Delete Unused Indexes</a></li>
<li><a href="https://bidshelper.codeplex.com/wikipage?title=Deploy%20Aggregation%20Designs&referringTitle=Documentation">Deploy Aggregation Designs</a></li>
<li><a href="https://bidshelper.codeplex.com/wikipage?title=Deploy%20MDX%20Script&referringTitle=Documentation">Deploy MDX Script</a></li>
<li><a href="https://bidshelper.codeplex.com/wikipage?title=Deploy%20Perspectives&referringTitle=Documentation">Deploy Perspectives</a></li>
<li><a href="https://bidshelper.codeplex.com/wikipage?title=Dimension%20Data%20Type%20Discrepancy%20Check&referringTitle=Documentation">Dimension Data Type Discrepancy Check</a></li>
<li><a href="https://bidshelper.codeplex.com/wikipage?title=Dimension%20Health%20Check&referringTitle=Documentation">Dimension Health Check</a></li>
<li><a href="https://bidshelper.codeplex.com/wikipage?title=Dimension%20Optimization%20Report&referringTitle=Documentation">Dimension Optimization Report</a></li>
<li><a href="https://bidshelper.codeplex.com/wikipage?title=Duplicate%20Role&referringTitle=Documentation">Duplicate Role</a></li>
<li><a href="https://bidshelper.codeplex.com/wikipage?title=Many-to-Many%20Matrix%20Compression&referringTitle=Documentation">Many-to-Many Matrix Compression</a></li>
<li><a href="https://bidshelper.codeplex.com/wikipage?title=Measure%20Group%20Health%20Check&referringTitle=Documentation">Measure Group Health Check</a></li>
<li><a href="https://bidshelper.codeplex.com/wikipage?title=Non-Default%20Properties%20Report&referringTitle=Documentation">Non-Default Properties Report</a></li>
<li><a href="https://bidshelper.codeplex.com/wikipage?title=Parent-Child%20Dimension%20Naturalizer&referringTitle=Documentation">Parent-Child Dimension Naturalizer</a></li>
<li><a href="https://bidshelper.codeplex.com/wikipage?title=Printer%20Friendly%20Aggregations&referringTitle=Documentation">Printer Friendly Aggregations</a></li>
<li><a href="https://bidshelper.codeplex.com/wikipage?title=Printer%20Friendly%20Dimension%20Usage&referringTitle=Documentation">Printer Friendly Dimension Usage</a></li>
<li><a href="https://bidshelper.codeplex.com/wikipage?title=Roles%20Report&referringTitle=Documentation">Roles Report</a></li>
<li><a href="https://bidshelper.codeplex.com/wikipage?title=Similar%20Aggregations&referringTitle=Documentation">Similar Aggregations</a></li>
<li><a href="https://bidshelper.codeplex.com/wikipage?title=Smart%20Diff&referringTitle=Documentation">Smart Diff</a></li>
<li><a href="https://bidshelper.codeplex.com/wikipage?title=Show%20Extra%20Properties&referringTitle=Documentation">Show Extra Properties</a></li>
<li><a href="https://bidshelper.codeplex.com/wikipage?title=Sync%20Descriptions&referringTitle=Documentation">Sync Descriptions</a></li>
<li><a href="https://bidshelper.codeplex.com/wikipage?title=Test%20Aggregation%20Performance&referringTitle=Documentation">Test Aggregation Performance</a></li>
<li><a href="https://bidshelper.codeplex.com/wikipage?title=Tri-State%20Perspectives&referringTitle=Documentation">Tri-State Perspectives</a></li>
<li><a href="https://bidshelper.codeplex.com/wikipage?title=Update%20Estimated%20Counts&referringTitle=Documentation">Update Estimated Counts</a></li>
<li><a href="https://bidshelper.codeplex.com/wikipage?title=Validate%20Aggregations&referringTitle=Documentation">Validate Aggregations</a></li>
<li><a href="https://bidshelper.codeplex.com/wikipage?title=Visualize%20Attribute%20Lattice&referringTitle=Documentation">Visualize Attribute Lattice</a></li></ul></li>
<li><b>Analysis Services Tabular</b>
<ul><li><a href="https://bidshelper.codeplex.com/wikipage?title=Printer%20Friendly%20Dimension%20Usage&referringTitle=Documentation">Printer Friendly Dimension Usage</a></li>
<li><a href="https://bidshelper.codeplex.com/wikipage?title=Roles%20Report&referringTitle=Documentation">Roles Report</a></li>
<li><a href="https://bidshelper.codeplex.com/wikipage?title=Smart%20Diff&referringTitle=Documentation">Smart Diff</a></li>
<li><a href="https://bidshelper.codeplex.com/wikipage?title=Tabular%20Actions%20Editor&referringTitle=Documentation">Tabular Actions Editor</a></li>
<li><a href="https://bidshelper.codeplex.com/wikipage?title=Tabular%20Display%20Folders&referringTitle=Documentation">Tabular Display Folders</a></li>
<li><a href="https://bidshelper.codeplex.com/wikipage?title=Tabular%20HideMemberIf&referringTitle=Documentation">Tabular HideMemberIf</a></li>
<li><a href="https://bidshelper.codeplex.com/wikipage?title=Tabular%20Pre-Build&referringTitle=Documentation">Tabular Pre-Build</a></li>
<li><a href="https://bidshelper.codeplex.com/wikipage?title=Tabular%20Sync%20Descriptions&referringTitle=Documentation">Tabular Sync Descriptions</a></li>
<li><a href="https://bidshelper.codeplex.com/wikipage?title=Tabular%20Translations%20Editor&referringTitle=Documentation">Tabular Translations Editor</a></li>
<li><a href="https://bidshelper.codeplex.com/wikipage?title=Tabular%20Annotation%20Workaround&referringTitle=Documentation">Tabular Annotation Workaround</a></li>
<li><a href="https://bidshelper.codeplex.com/wikipage?title=Column%20Usage%20Reports&referringTitle=Documentation">Unused Columns Report</a></li></ul></li>
<li><b>Integration Services</b>
<ul><li><a href="https://bidshelper.codeplex.com/wikipage?title=Batch%20Property%20Update&referringTitle=Documentation">Batch Property Update</a></li>
<li><a href="https://bidshelper.codeplex.com/wikipage?title=Biml%20Package%20Generator&referringTitle=Documentation">Biml Package Generator</a></li>
<li><a href="https://bidshelper.codeplex.com/wikipage?title=Create%20Fixed%20Width%20Columns&referringTitle=Documentation">Create Fixed Width Columns</a></li>
<li><a href="https://bidshelper.codeplex.com/wikipage?title=Deploy%20SSIS%20Packages&referringTitle=Documentation">Deploy SSIS Packages</a></li>
<li><a href="https://bidshelper.codeplex.com/wikipage?title=Design%20Warnings&referringTitle=Documentation">Design Warnings</a></li>
<li><a href="https://bidshelper.codeplex.com/wikipage?title=dtsConfigFormatter&referringTitle=Documentation">dtsConfig File Formatter</a></li>
<li><a href="https://bidshelper.codeplex.com/wikipage?title=Expression%20and%20Configuration%20Highlighter&referringTitle=Documentation">Expression and Configuration Highlighter</a></li>
<li><a href="https://bidshelper.codeplex.com/wikipage?title=Expression%20List&referringTitle=Documentation">Expression List</a></li>
<li><a href="https://bidshelper.codeplex.com/wikipage?title=Fix%20Relative%20Paths&referringTitle=Documentation">Fix Relative Paths</a></li>
<li><a href="https://bidshelper.codeplex.com/wikipage?title=Non-Default%20Properties%20Report&referringTitle=Documentation">Non-Default Properties Report</a></li>
<li><a href="https://bidshelper.codeplex.com/wikipage?title=Pipeline%20Component%20Performance%20Breakdown&referringTitle=Documentation">Pipeline Component Performance Breakdown</a></li>
<li><a href="https://bidshelper.codeplex.com/wikipage?title=Reset%20GUIDs&referringTitle=Documentation">Reset GUIDs</a></li>
<li><a href="https://bidshelper.codeplex.com/wikipage?title=Smart%20Diff&referringTitle=Documentation">Smart Diff</a></li>
<li><a href="https://bidshelper.codeplex.com/wikipage?title=Sort%20Project%20Files&referringTitle=Documentation">Sort Project Files</a></li>
<li><a href="https://bidshelper.codeplex.com/wikipage?title=Sortable%20Package%20Properties%20Report&referringTitle=Documentation">Sortable Package Properties Report</a></li>
<li><a href="https://bidshelper.codeplex.com/wikipage?title=SSIS%20Performance%20Visualization&referringTitle=Documentation">SSIS Performance Visualization</a></li>
<li><a href="https://bidshelper.codeplex.com/wikipage?title=Variables%20Window%20Extensions&referringTitle=Documentation">Variables Window Extensions</a></li></ul></li>
<li><b>Reporting Services</b>
<ul><li><a href="https://bidshelper.codeplex.com/wikipage?title=Dataset%20Usage%20Reports&referringTitle=Documentation">Dataset Usage Reports</a></li>
<li><a href="https://bidshelper.codeplex.com/wikipage?title=Delete%20Dataset%20Cache%20Files&referringTitle=Documentation">Delete Dataset Cache Files</a></li>
<li><a href="https://bidshelper.codeplex.com/wikipage?title=Smart%20Diff&referringTitle=Documentation">Smart Diff</a></li></ul></li>
<li><b>General</b>
<ul><li><a href="https://bidshelper.codeplex.com/wikipage?title=Enable%2fDisable%20features&referringTitle=Documentation">Enable&#47;Disable features</a></li>
<li><a href="https://bidshelper.codeplex.com/wikipage?title=Preferences&referringTitle=Documentation">Preferences</a></li>
<li><a href="https://bidshelper.codeplex.com/wikipage?title=Version%20Notification&referringTitle=Documentation">Version Notification</a></li></ul></li></ul>
<h3>Developers</h3>
For information about compiling the latest source code yourself, please see this <a href="http://bidshelper.codeplex.com/Thread/View.aspx?ThreadId=22344">discussion</a><br /><br /></div><div class="ClearBoth"></div>furmanggSun, 15 Mar 2015 22:00:19 GMTUpdated Wiki: Documentation 20150315100019PUpdated Wiki: Printer Friendly Dimension Usagehttps://bidshelper.codeplex.com/wikipage?title=Printer Friendly Dimension Usage&version=4<div class="wikidoc"><h3>Printer Friendly Dimension Usage</h3>
The Dimension Usage tab allows you to define the relationships between dimensions and measure groups. The Printer Friendly Dimension Usage feature allows you to view and print a report encompassing all the information from that Dimension Usage tab. To use this feature, right click on the cube in Solution Explorer:<br /><br /><img src="http://download-codeplex.sec.s-msft.com/Download?ProjectName=bidshelper&DownloadId=10961" alt="PrinterFriendlyDimensionUsageTeaser.gif" title="PrinterFriendlyDimensionUsageTeaser.gif" /><br /><br />Starting with BIDS Helper 1.7, for Tabular models, right click on the .bim file to launch this report.<br /><br />Then a report similar to the following will open:<br /><br /><img src="http://download-codeplex.sec.s-msft.com/Download?ProjectName=bidshelper&DownloadId=10962" alt="PrinterFriendlyDimensionUsageReport.gif" title="PrinterFriendlyDimensionUsageReport.gif" /><br /><br />You can download a <a href="https://www.codeplex.com/Download?ProjectName=bidshelper&DownloadId=10963">sample copy</a> of this report run against the Adventure Works cube.<br /><br />Starting with release 1.6.4, when you run this feature, it first prompts you to ask if you want the detailed view (the report seen above) or a Bus Matrix view which looks like the following:<br /><br /><img src="http://download-codeplex.sec.s-msft.com/Download?ProjectName=bidshelper&DownloadId=736613" alt="BusMatrix.png" title="BusMatrix.png" /><br /><br />We recommend you export this report to Excel if you desire to do further formatting such as rotating the column labels. You can download a <a href="https://www.codeplex.com/Download?ProjectName=bidshelper&DownloadId=736614">sample copy</a> Excel Bus Matrix report which was run against the Adventure Works cube.</div><div class="ClearBoth"></div>furmanggSun, 15 Mar 2015 21:59:31 GMTUpdated Wiki: Printer Friendly Dimension Usage 20150315095931PNew Comment on "Smart Diff"https://bidshelper.codeplex.com/wikipage?title=Smart Diff&ANCHOR#C31374I use winmerge for my compare. Can you set it up to do folder compare&#63; I can see it would take longer to generate all the tmp files, but that would be an invaluable time saver in the end.argrithmagMon, 23 Feb 2015 17:08:35 GMTNew Comment on "Smart Diff" 20150223050835PUpdated Wiki: Copy Data Dynamically with BimlScripthttps://bidshelper.codeplex.com/wikipage?title=Copy Data Dynamically with BimlScript&version=5<div class="wikidoc">
<p><em>This post is </em><a href="http://agilebi.com/jwelch/2011/05/13/biml-functionality-in-bids-helper/">part 3 of a series</a><em> on using
<a href="http://www.varigence.com/documentation/biml/">Biml</a> in <a href="http://bidshelper.codeplex.com/">
BIDS Helper</a>. This post builds on some of the information and the sample from the previous posts.</em></p>
<p>BimlScript enables some interesting scenarios for generating large numbers of SSIS packages automatically. This can come in handy when you need to copy most or all of the data in one database to a different one. In this case, you could use something like
the <a href="http://msdn.microsoft.com/en-us/library/ms142159.aspx">Transfer SQL Server Objects</a> task, but
<a href="http://blogs.msdn.com/b/mattm/archive/2007/04/18/roll-your-own-transfer-sql-server-objects-task.aspx">
it has a few problems</a>. You can roll your own, but that might mean a fair amount of custom scripting. Or you could use the Import / Export Wizard. But in all these cases, you don&rsquo;t have complete control of how the packages are produced. You could create
all the packages by hand, which does give you full control, but then you are stuck doing a lot of repetitive work in SSIS.</p>
<p>BimlScript provides an alternative that lets you fully control the output, while automating the rote work of producing lots of packages that use the same pattern. Let&rsquo;s take a look at a sample of this, using the scenario above (copying the data from
one database to another).</p>
<pre>&lt;#@ template language=&quot;C#&quot; hostspecific=&quot;true&quot;#&gt;
&lt;#@ import namespace=&quot;System.Data&quot; #&gt;
&lt;Biml xmlns=&quot;http://schemas.varigence.com/biml.xsd&quot;&gt;
&lt;Connections&gt;
&lt;OleDbConnection Name=&quot;Source&quot; ConnectionString=&quot;Provider=SQLNCLI10;Server=.;Initial Catalog=AdventureWorksDW2008R2;Integrated Security=SSPI;&quot;/&gt;
&lt;OleDbConnection Name=&quot;Target&quot; ConnectionString=&quot;Provider=SQLNCLI10;Server=.;Initial Catalog=Target;Integrated Security=SSPI;&quot;/&gt;
&lt;/Connections&gt;
&lt;Packages&gt;
&lt;#
string metadataConnectionString = &quot;Provider=SQLNCLI10;Server=.;Initial Catalog=AdventureWorksDW2008R2;Integrated Security=SSPI;&quot;;
DataTable tables = ExternalDataAccess.GetDataTable(metadataConnectionString,
&quot;SELECT '[' &#43; s.name &#43; '].[' &#43; t.name &#43; ']' FROM sys.tables t INNER JOIN sys.schemas s on t.schema_id = s.schema_id&quot;);
foreach (DataRow row in tables.Rows)
{ #&gt;
&lt;Package Name=&quot;Extract &lt;#=row[0]#&gt;&quot; ConstraintMode=&quot;Linear&quot; AutoCreateConfigurationsType=&quot;None&quot;&gt;
&lt;Tasks&gt;
&lt;Dataflow Name=&quot;Copy Data&quot;&gt;
&lt;Transformations&gt;
&lt;OleDbSource Name=&quot;Retrieve Data&quot; ConnectionName=&quot;Source&quot;&gt;
&lt;DirectInput&gt;SELECT * FROM &lt;#=row[0]#&gt;&lt;/DirectInput&gt;
&lt;/OleDbSource&gt;
&lt;OleDbDestination Name=&quot;Insert Data&quot; ConnectionName=&quot;Target&quot;&gt;
&lt;ExternalTableOutput Table=&quot;&lt;#=row[0]#&gt;&quot;/&gt;
&lt;/OleDbDestination&gt;
&lt;/Transformations&gt;
&lt;/Dataflow&gt;
&lt;/Tasks&gt;
&lt;/Package&gt;
&lt;# } #&gt;
&lt;/Packages&gt;
&lt;/Biml&gt;</pre>
<p>&nbsp;</p>
<p>Note: When you paste this into Visual Studio, the formatting and indenting will be wrong and the code will not work.&nbsp;After pasting, press Ctrl-Z to undo formatting and indenting and the paste will be successful. See&nbsp;<a href="http://bimlscript.com/Walkthrough/Details/45">this
tip</a> for more information.</p>
<p>This script is set up to copy all the data in the AdventureWorksDW2008R2 database to a second database named Target (very inventive, I know). One note &ndash; the script is not creating the tables in the target database. We could actually automate that portion
as well, but it&rsquo;s beyond the scope of this post. To ensure you are set up properly to run this script, you should create an exact structural copy of your source database under a different name. You can use the
<a href="http://msdn.microsoft.com/en-us/library/ms178078(v=SQL.105)">Generate Scripts Wizard</a> to do this. Just script the entire database, and then update the generated script to use a different database name (don&rsquo;t forget to change the USE statement
to the new name).</p>
<p>The script will produce a package per table, with a simple data flow that copies all the data using an OLE DB Source and OLE DB Destination. The script leverages the metadata already contained in the database, in the sys.tables view, to drive the loop that
creates the packages.</p>
<p>What if you don&rsquo;t want to select all the rows from each table? Instead, perhaps you want to specify a WHERE clause to use to filter some of the tables. To handle this, we can create a table in the target database that holds our WHERE information.</p>
<pre>&lt;Biml xmlns=&quot;http://schemas.varigence.com/biml.xsd&quot;&gt;
&lt;Connections&gt;
&lt;OleDbConnection Name=&quot;Target&quot; ConnectionString=&quot;Provider=SQLNCLI10;Server=.;Initial Catalog=Target;Integrated Security=SSPI;&quot;/&gt;
&lt;/Connections&gt;
&lt;Tables&gt;
&lt;Table Name=&quot;WhereClause&quot; ConnectionName=&quot;Target&quot;&gt;
&lt;Columns&gt;
&lt;Column Name=&quot;TableName&quot; DataType=&quot;String&quot; Length=&quot;255&quot;/&gt;
&lt;Column Name=&quot;WhereSql&quot; DataType=&quot;String&quot; Length=&quot;4000&quot;/&gt;
&lt;/Columns&gt;
&lt;/Table&gt;
&lt;/Tables&gt;
&lt;/Biml&gt;</pre>
<p>You can use the <a href="http://agilebi.com/jwelch/2011/05/26/creating-tables-using-biml-and-bimlscript/">
steps shown in Part 2 of this series</a> to create this table in the Target database. Once it&rsquo;s been created, populate it with some data. Note that since we are using the schema-qualified name of the table, you&rsquo;ll need to specify that in the table.
There&rsquo;s an example of data for this table that will work with AdventureWorksDW2008R2 below. This will filter the rows down to only sales where the amount is greater than 1000.</p>
<table border="1" width="610" cellspacing="0" cellpadding="2">
<tbody>
<tr>
<td valign="top" width="200"><strong><span style="font-size:small">TableName</span></strong></td>
<td valign="top" width="408"><strong><span style="font-size:small">SelectSql</span></strong></td>
</tr>
<tr>
<td valign="top" width="200">[dbo].[FactInternetSales]</td>
<td valign="top" width="408">
<pre>WHERE [SalesAmount] &gt;= 1000</pre>
</td>
</tr>
<tr>
<td valign="top" width="200">[dbo].[FactResellerSales]</td>
<td valign="top" width="408">
<pre>WHERE [SalesAmount] &gt;= 1000</pre>
</td>
</tr>
</tbody>
</table>
<p>&nbsp;</p>
<p>Now we need to alter the script to use the new information in this table. At the beginning of the block of script after the &lt;Packages&gt; element, add the following code:</p>
<pre>string targetConnectionString = &quot;Provider=SQLNCLI10;Server=.;Initial Catalog=Target;Integrated Security=SSPI;&quot;;
DataTable whereClauses = ExternalDataAccess.GetDataTable(targetConnectionString, &quot;SELECT TableName, WhereSql FROM WhereClause&quot;);</pre>
<p>This retrieves the WHERE clauses from the WhereClause table, and stores them in the whereClauses variable.</p>
<p>Next, replace the &lt;Direct Input&gt; line in the OleDbSource with this:</p>
<pre>&lt;#
var dataRow = whereClauses.Select(string.Format(&quot;TableName = '{0}'&quot;, row[0]));
string whereSql = dataRow.Length == 0 ? string.Empty : dataRow[0][1].ToString();
string sql = string.Format(&quot;SELECT * FROM {0} {1}&quot;, row[0], whereSql);
#&gt;
&lt;DirectInput&gt;&lt;#=sql#&gt;&lt;/DirectInput&gt;</pre>
<p>This code determines whether the whereClauses table has a row for the current table. If it does, it appends it to the end of the SELECT statement. The complete, final script looks like this:</p>
<pre>&lt;#@ template language=&quot;C#&quot; hostspecific=&quot;true&quot;#&gt;
&lt;#@ import namespace=&quot;System.Data&quot; #&gt;
&lt;Biml xmlns=&quot;http://schemas.varigence.com/biml.xsd&quot;&gt;
&lt;Connections&gt;
&lt;OleDbConnection Name=&quot;Source&quot; ConnectionString=&quot;Provider=SQLNCLI10;Server=.;Initial Catalog=AdventureWorksDW2008R2;Integrated Security=SSPI;&quot;/&gt;
&lt;OleDbConnection Name=&quot;Target&quot; ConnectionString=&quot;Provider=SQLNCLI10;Server=.;Initial Catalog=Target;Integrated Security=SSPI;&quot;/&gt;
&lt;/Connections&gt;
&lt;Packages&gt;
&lt;#
string targetConnectionString = &quot;Provider=SQLNCLI10;Server=.;Initial Catalog=Target;Integrated Security=SSPI;&quot;;
DataTable whereClauses = ExternalDataAccess.GetDataTable(targetConnectionString, &quot;SELECT TableName, WhereSql FROM WhereClause&quot;);
string metadataConnectionString = &quot;Provider=SQLNCLI10;Server=.;Initial Catalog=AdventureWorksDW2008R2;Integrated Security=SSPI;&quot;;
DataTable tables = ExternalDataAccess.GetDataTable(metadataConnectionString,
&quot;SELECT '[' &#43; s.name &#43; '].[' &#43; t.name &#43; ']' FROM sys.tables t INNER JOIN sys.schemas s on t.schema_id = s.schema_id&quot;);
foreach (DataRow row in tables.Rows)
{ #&gt;
&lt;Package Name=&quot;Extract &lt;#=row[0]#&gt;&quot; ConstraintMode=&quot;Linear&quot; AutoCreateConfigurationsType=&quot;None&quot;&gt;
&lt;Tasks&gt;
&lt;Dataflow Name=&quot;Copy Data&quot;&gt;
&lt;Transformations&gt;
&lt;OleDbSource Name=&quot;Retrieve Data&quot; ConnectionName=&quot;Source&quot;&gt;
&lt;#
var dataRow = whereClauses.Select(string.Format(&quot;TableName = '{0}'&quot;, row[0]));
string whereSql = dataRow.Length == 0 ? string.Empty : dataRow[0][1].ToString();
string sql = string.Format(&quot;SELECT * FROM {0} {1}&quot;, row[0], whereSql);
#&gt;
&lt;DirectInput&gt;&lt;#=sql#&gt;&lt;/DirectInput&gt;
&lt;/OleDbSource&gt;
&lt;OleDbDestination Name=&quot;Insert Data&quot; ConnectionName=&quot;Target&quot;&gt;
&lt;ExternalTableOutput Table=&quot;&lt;#=row[0]#&gt;&quot;/&gt;
&lt;/OleDbDestination&gt;
&lt;/Transformations&gt;
&lt;/Dataflow&gt;
&lt;/Tasks&gt;
&lt;/Package&gt;
&lt;# } #&gt;
&lt;/Packages&gt;
&lt;/Biml&gt;</pre>
<p>You can see the results of this script by <a href="http://agilebi.com/jwelch/2011/05/13/creating-a-basic-package-using-biml/">
right-clicking on the Biml file, and choosing Expand</a>. It may take a minute or two to process, but when it finishes, you should see a package for each table in your source database. The data flows will copy the data from Source to Target, and any WHERE clauses
you add to the WhereClause table will be used.</p>
<p>There&rsquo;s a lot more that could be done with this script (automating the recreation of the tables in the destination, or deleting existing data, for example), but it&rsquo;s still a good example of what BimlScript can do. Instead of spending your time
writing 10s or 100s of repetitive packages, automate it with BimlScript.</p>
<p>[cross-posted from <a title="http://agilebi.com/jwelch/2011/05/31/copy-data-dynamically-with-bimlscript/" href="http://agilebi.com/jwelch/2011/05/31/copy-data-dynamically-with-bimlscript/">
http://agilebi.com/jwelch/2011/05/31/copy-data-dynamically-with-bimlscript/</a>]</p>
</div><div class="ClearBoth"></div>furmanggSun, 15 Feb 2015 07:42:53 GMTUpdated Wiki: Copy Data Dynamically with BimlScript 20150215074253AUpdated Wiki: Copy Data Dynamically with BimlScripthttps://bidshelper.codeplex.com/wikipage?title=Copy Data Dynamically with BimlScript&version=4<div class="wikidoc">
<p><em>This post is </em><a href="http://agilebi.com/jwelch/2011/05/13/biml-functionality-in-bids-helper/">part 3 of a series</a><em> on using
<a href="http://www.varigence.com/documentation/biml/">Biml</a> in <a href="http://bidshelper.codeplex.com/">
BIDS Helper</a>. This post builds on some of the information and the sample from the previous posts.</em></p>
<p>BimlScript enables some interesting scenarios for generating large numbers of SSIS packages automatically. This can come in handy when you need to copy most or all of the data in one database to a different one. In this case, you could use something like
the <a href="http://msdn.microsoft.com/en-us/library/ms142159.aspx">Transfer SQL Server Objects</a> task, but
<a href="http://blogs.msdn.com/b/mattm/archive/2007/04/18/roll-your-own-transfer-sql-server-objects-task.aspx">
it has a few problems</a>. You can roll your own, but that might mean a fair amount of custom scripting. Or you could use the Import / Export Wizard. But in all these cases, you don&rsquo;t have complete control of how the packages are produced. You could create
all the packages by hand, which does give you full control, but then you are stuck doing a lot of repetitive work in SSIS.</p>
<p>BimlScript provides an alternative that lets you fully control the output, while automating the rote work of producing lots of packages that use the same pattern. Let&rsquo;s take a look at a sample of this, using the scenario above (copying the data from
one database to another).</p>
<pre>&lt;#@ template language=&quot;C#&quot; hostspecific=&quot;true&quot;#&gt;
&lt;#@ import namespace=&quot;System.Data&quot; #&gt;
&lt;Biml xmlns=&quot;http://schemas.varigence.com/biml.xsd&quot;&gt;
&lt;Connections&gt;
&lt;OleDbConnection Name=&quot;Source&quot; ConnectionString=&quot;Provider=SQLNCLI10;Server=.;Initial Catalog=AdventureWorksDW2008R2;Integrated Security=SSPI;&quot;/&gt;
&lt;OleDbConnection Name=&quot;Target&quot; ConnectionString=&quot;Provider=SQLNCLI10;Server=.;Initial Catalog=Target;Integrated Security=SSPI;&quot;/&gt;
&lt;/Connections&gt;
&lt;Packages&gt;
&lt;#
string metadataConnectionString = &quot;Provider=SQLNCLI10;Server=.;Initial Catalog=AdventureWorksDW2008R2;Integrated Security=SSPI;&quot;;
DataTable tables = ExternalDataAccess.GetDataTable(metadataConnectionString,
&quot;SELECT '[' &#43; s.name &#43; '].[' &#43; t.name &#43; ']' FROM sys.tables t INNER JOIN sys.schemas s on t.schema_id = s.schema_id&quot;);
foreach (DataRow row in tables.Rows)
{ #&gt;
&lt;Package Name=&quot;Extract &lt;#=row[0]#&gt;&quot; ConstraintMode=&quot;Linear&quot; AutoCreateConfigurationsType=&quot;None&quot;&gt;
&lt;Tasks&gt;
&lt;Dataflow Name=&quot;Copy Data&quot;&gt;
&lt;Transformations&gt;
&lt;OleDbSource Name=&quot;Retrieve Data&quot; ConnectionName=&quot;Source&quot;&gt;
&lt;DirectInput&gt;SELECT * FROM &lt;#=row[0]#&gt;&lt;/DirectInput&gt;
&lt;/OleDbSource&gt;
&lt;OleDbDestination Name=&quot;Insert Data&quot; ConnectionName=&quot;Target&quot;&gt;
&lt;ExternalTableOutput Table=&quot;&lt;#=row[0]#&gt;&quot;/&gt;
&lt;/OleDbDestination&gt;
&lt;/Transformations&gt;
&lt;/Dataflow&gt;
&lt;/Tasks&gt;
&lt;/Package&gt;
&lt;# } #&gt;
&lt;/Packages&gt;
&lt;/Biml&gt;</pre>
<p>This script is set up to copy all the data in the AdventureWorksDW2008R2 database to a second database named Target (very inventive, I know). One note &ndash; the script is not creating the tables in the target database. We could actually automate that portion
as well, but it&rsquo;s beyond the scope of this post. To ensure you are set up properly to run this script, you should create an exact structural copy of your source database under a different name. You can use the
<a href="http://msdn.microsoft.com/en-us/library/ms178078(v=SQL.105)">Generate Scripts Wizard</a> to do this. Just script the entire database, and then update the generated script to use a different database name (don&rsquo;t forget to change the USE statement
to the new name).</p>
<p>The script will produce a package per table, with a simple data flow that copies all the data using an OLE DB Source and OLE DB Destination. The script leverages the metadata already contained in the database, in the sys.tables view, to drive the loop that
creates the packages.</p>
<p>What if you don&rsquo;t want to select all the rows from each table? Instead, perhaps you want to specify a WHERE clause to use to filter some of the tables. To handle this, we can create a table in the target database that holds our WHERE information.</p>
<pre>&lt;Biml xmlns=&quot;http://schemas.varigence.com/biml.xsd&quot;&gt;
&lt;Connections&gt;
&lt;OleDbConnection Name=&quot;Target&quot; ConnectionString=&quot;Provider=SQLNCLI10;Server=.;Initial Catalog=Target;Integrated Security=SSPI;&quot;/&gt;
&lt;/Connections&gt;
&lt;Tables&gt;
&lt;Table Name=&quot;WhereClause&quot; ConnectionName=&quot;Target&quot;&gt;
&lt;Columns&gt;
&lt;Column Name=&quot;TableName&quot; DataType=&quot;String&quot; Length=&quot;255&quot;/&gt;
&lt;Column Name=&quot;WhereSql&quot; DataType=&quot;String&quot; Length=&quot;4000&quot;/&gt;
&lt;/Columns&gt;
&lt;/Table&gt;
&lt;/Tables&gt;
&lt;/Biml&gt;</pre>
<p>You can use the <a href="http://agilebi.com/jwelch/2011/05/26/creating-tables-using-biml-and-bimlscript/">
steps shown in Part 2 of this series</a> to create this table in the Target database. Once it&rsquo;s been created, populate it with some data. Note that since we are using the schema-qualified name of the table, you&rsquo;ll need to specify that in the table.
There&rsquo;s an example of data for this table that will work with AdventureWorksDW2008R2 below. This will filter the rows down to only sales where the amount is greater than 1000.</p>
<table border="1" width="610" cellspacing="0" cellpadding="2">
<tbody>
<tr>
<td valign="top" width="200"><strong><span style="font-size:small">TableName</span></strong></td>
<td valign="top" width="408"><strong><span style="font-size:small">SelectSql</span></strong></td>
</tr>
<tr>
<td valign="top" width="200">[dbo].[FactInternetSales]</td>
<td valign="top" width="408">
<pre>WHERE [SalesAmount] &gt;= 1000</pre>
</td>
</tr>
<tr>
<td valign="top" width="200">[dbo].[FactResellerSales]</td>
<td valign="top" width="408">
<pre>WHERE [SalesAmount] &gt;= 1000</pre>
</td>
</tr>
</tbody>
</table>
<p>&nbsp;</p>
<p>Now we need to alter the script to use the new information in this table. At the beginning of the block of script after the &lt;Packages&gt; element, add the following code:</p>
<pre>string targetConnectionString = &quot;Provider=SQLNCLI10;Server=.;Initial Catalog=Target;Integrated Security=SSPI;&quot;;
DataTable whereClauses = ExternalDataAccess.GetDataTable(targetConnectionString, &quot;SELECT TableName, WhereSql FROM WhereClause&quot;);</pre>
<p>This retrieves the WHERE clauses from the WhereClause table, and stores them in the whereClauses variable.</p>
<p>Next, replace the &lt;Direct Input&gt; line in the OleDbSource with this:</p>
<pre>&lt;#
var dataRow = whereClauses.Select(string.Format(&quot;TableName = '{0}'&quot;, row[0]));
string whereSql = dataRow.Length == 0 ? string.Empty : dataRow[0][1].ToString();
string sql = string.Format(&quot;SELECT * FROM {0} {1}&quot;, row[0], whereSql);
#&gt;
&lt;DirectInput&gt;&lt;#=sql#&gt;&lt;/DirectInput&gt;</pre>
<p>This code determines whether the whereClauses table has a row for the current table. If it does, it appends it to the end of the SELECT statement. The complete, final script looks like this:</p>
<pre>&lt;#@ template language=&quot;C#&quot; hostspecific=&quot;true&quot;#&gt;
&lt;#@ import namespace=&quot;System.Data&quot; #&gt;
&lt;Biml xmlns=&quot;http://schemas.varigence.com/biml.xsd&quot;&gt;
&lt;Connections&gt;
&lt;OleDbConnection Name=&quot;Source&quot; ConnectionString=&quot;Provider=SQLNCLI10;Server=.;Initial Catalog=AdventureWorksDW2008R2;Integrated Security=SSPI;&quot;/&gt;
&lt;OleDbConnection Name=&quot;Target&quot; ConnectionString=&quot;Provider=SQLNCLI10;Server=.;Initial Catalog=Target;Integrated Security=SSPI;&quot;/&gt;
&lt;/Connections&gt;
&lt;Packages&gt;
&lt;#
string targetConnectionString = &quot;Provider=SQLNCLI10;Server=.;Initial Catalog=Target;Integrated Security=SSPI;&quot;;
DataTable whereClauses = ExternalDataAccess.GetDataTable(targetConnectionString, &quot;SELECT TableName, WhereSql FROM WhereClause&quot;);
string metadataConnectionString = &quot;Provider=SQLNCLI10;Server=.;Initial Catalog=AdventureWorksDW2008R2;Integrated Security=SSPI;&quot;;
DataTable tables = ExternalDataAccess.GetDataTable(metadataConnectionString,
&quot;SELECT '[' &#43; s.name &#43; '].[' &#43; t.name &#43; ']' FROM sys.tables t INNER JOIN sys.schemas s on t.schema_id = s.schema_id&quot;);
foreach (DataRow row in tables.Rows)
{ #&gt;
&lt;Package Name=&quot;Extract &lt;#=row[0]#&gt;&quot; ConstraintMode=&quot;Linear&quot; AutoCreateConfigurationsType=&quot;None&quot;&gt;
&lt;Tasks&gt;
&lt;Dataflow Name=&quot;Copy Data&quot;&gt;
&lt;Transformations&gt;
&lt;OleDbSource Name=&quot;Retrieve Data&quot; ConnectionName=&quot;Source&quot;&gt;
&lt;#
var dataRow = whereClauses.Select(string.Format(&quot;TableName = '{0}'&quot;, row[0]));
string whereSql = dataRow.Length == 0 ? string.Empty : dataRow[0][1].ToString();
string sql = string.Format(&quot;SELECT * FROM {0} {1}&quot;, row[0], whereSql);
#&gt;
&lt;DirectInput&gt;&lt;#=sql#&gt;&lt;/DirectInput&gt;
&lt;/OleDbSource&gt;
&lt;OleDbDestination Name=&quot;Insert Data&quot; ConnectionName=&quot;Target&quot;&gt;
&lt;ExternalTableOutput Table=&quot;&lt;#=row[0]#&gt;&quot;/&gt;
&lt;/OleDbDestination&gt;
&lt;/Transformations&gt;
&lt;/Dataflow&gt;
&lt;/Tasks&gt;
&lt;/Package&gt;
&lt;# } #&gt;
&lt;/Packages&gt;
&lt;/Biml&gt;</pre>
<p>Note: When you paste this into Visual Studio, the formatting and indenting will be wrong and the code will not work.&nbsp;After pasting, press Ctrl-Z to undo formatting and indenting and the paste will be successful. See&nbsp;<a href="http://bimlscript.com/Walkthrough/Details/45">this
tip</a> for more information.</p>
<p>You can see the results of this script by <a href="http://agilebi.com/jwelch/2011/05/13/creating-a-basic-package-using-biml/">
right-clicking on the Biml file, and choosing Expand</a>. It may take a minute or two to process, but when it finishes, you should see a package for each table in your source database. The data flows will copy the data from Source to Target, and any WHERE clauses
you add to the WhereClause table will be used.</p>
<p>There&rsquo;s a lot more that could be done with this script (automating the recreation of the tables in the destination, or deleting existing data, for example), but it&rsquo;s still a good example of what BimlScript can do. Instead of spending your time
writing 10s or 100s of repetitive packages, automate it with BimlScript.</p>
<p>[cross-posted from <a title="http://agilebi.com/jwelch/2011/05/31/copy-data-dynamically-with-bimlscript/" href="http://agilebi.com/jwelch/2011/05/31/copy-data-dynamically-with-bimlscript/">
http://agilebi.com/jwelch/2011/05/31/copy-data-dynamically-with-bimlscript/</a>]</p>
</div><div class="ClearBoth"></div>furmanggSun, 15 Feb 2015 07:42:03 GMTUpdated Wiki: Copy Data Dynamically with BimlScript 20150215074203AUpdated Wiki: Creating Tables using Biml and BimlScripthttps://bidshelper.codeplex.com/wikipage?title=Creating Tables using Biml and BimlScript&version=5<div class="wikidoc">
<p><em>This post is </em><a href="http://agilebi.com/jwelch/2011/05/13/biml-functionality-in-bids-helper/"><em>part 2 of a series</em></a><em> on using
<a href="http://www.varigence.com/Documentation/Language/Index">Biml</a> in <a href="http://bidshelper.codeplex.com/">
BIDS Helper</a>. This post builds on some of the information and the sample from the previous posts.</em></p>
<p>When I&rsquo;m creating samples for SSIS, I often find it necessary to create supporting tables to go along with the package sample. One of the things I like about
<a href="http://www.varigence.com/Documentation/Language/Index">Biml</a> is that you can define both your tables and packages in the language. Here&rsquo;s an example of defining an OrderHeader and OrderDetail table in Biml:</p>
<div><span style="font-size:9.5pt; color:blue">&lt;</span><span style="font-size:9.5pt; color:#a31515">Biml</span>
<span style="font-size:9.5pt; color:red">xmlns</span><span style="font-size:9.5pt; color:blue">=</span><span style="font-size:9.5pt">&quot;<span style="color:blue">http://schemas.varigence.com/biml.xsd</span>&quot;<span style="color:blue">&gt;</span></span></div>
<div><span style="font-size:9.5pt; color:blue">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;</span><span style="font-size:9.5pt; color:#a31515">Connections</span><span style="font-size:9.5pt; color:blue">&gt;</span></div>
<div><span style="font-size:9.5pt; color:blue">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;</span><span style="font-size:9.5pt; color:#a31515">OleDbConnection</span>
<span style="font-size:9.5pt; color:red">Name</span><span style="font-size:9.5pt; color:blue">=</span><span style="font-size:9.5pt">&quot;<span style="color:blue">DbConnection</span>&quot;
<span style="color:red">ConnectionString</span><span style="color:blue">=</span>&quot;<span style="color:blue">Server=.;Initial Catalog=tempdb;Provider=SQLNCLI10.1;Integrated Security=SSPI;</span>&quot;<span style="color:blue">/&gt;</span></span></div>
<div><span style="font-size:9.5pt; color:blue">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;/</span><span style="font-size:9.5pt; color:#a31515">Connections</span><span style="font-size:9.5pt; color:blue">&gt;</span></div>
<div><span style="font-size:9.5pt; color:blue">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;</span><span style="font-size:9.5pt; color:#a31515">Databases</span><span style="font-size:9.5pt; color:blue">&gt;</span></div>
<div><span style="font-size:9.5pt; color:blue">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;</span><span style="font-size:9.5pt; color:#a31515">Database</span>
<span style="font-size:9.5pt; color:red">ConnectionName</span><span style="font-size:9.5pt; color:blue">=</span><span style="font-size:9.5pt">&quot;<span style="color:blue">DbConnection</span>&quot;
<span style="color:red">Name</span><span style="color:blue">=</span>&quot;<span style="color:blue">tempdb</span>&quot;<span style="color:blue">/&gt;</span></span></div>
<div><span style="font-size:9.5pt; color:blue">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;/</span><span style="font-size:9.5pt; color:#a31515">Databases</span><span style="font-size:9.5pt; color:blue">&gt;</span></div>
<div><span style="font-size:9.5pt; color:blue">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;</span><span style="font-size:9.5pt; color:#a31515">Schemas</span><span style="font-size:9.5pt; color:blue">&gt;</span></div>
<div><span style="font-size:9.5pt; color:blue">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;</span><span style="font-size:9.5pt; color:#a31515">Schema</span>
<span style="font-size:9.5pt; color:red">Name</span><span style="font-size:9.5pt; color:blue">=</span><span style="font-size:9.5pt">&quot;<span style="color:blue">dbo</span>&quot;
<span style="color:red">DatabaseName</span><span style="color:blue">=</span>&quot;<span style="color:blue">tempdb</span>&quot;
<span style="color:red">Owner</span><span style="color:blue">=</span>&quot;<span style="color:blue">dbo</span>&quot;<span style="color:blue">/&gt;</span></span></div>
<div><span style="font-size:9.5pt; color:blue">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;/</span><span style="font-size:9.5pt; color:#a31515">Schemas</span><span style="font-size:9.5pt; color:blue">&gt;</span></div>
<div><span style="font-size:9.5pt; color:blue">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;</span><span style="font-size:9.5pt; color:#a31515">Tables</span><span style="font-size:9.5pt; color:blue">&gt;</span></div>
<div><span style="font-size:9.5pt; color:blue">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;</span><span style="font-size:9.5pt; color:#a31515">Table</span>
<span style="font-size:9.5pt; color:red">Name</span><span style="font-size:9.5pt; color:blue">=</span><span style="font-size:9.5pt">&quot;<span style="color:blue">OrderHeader</span>&quot;
<span style="color:red">SchemaName</span><span style="color:blue">=</span>&quot;<span style="color:blue">tempdb.dbo</span>&quot;<span style="color:blue">&gt;</span></span></div>
<div><span style="font-size:9.5pt; color:blue">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;</span><span style="font-size:9.5pt; color:#a31515">Columns</span><span style="font-size:9.5pt; color:blue">&gt;</span></div>
<div><span style="font-size:9.5pt; color:blue">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;</span><span style="font-size:9.5pt; color:#a31515">Column</span>
<span style="font-size:9.5pt; color:red">Name</span><span style="font-size:9.5pt; color:blue">=</span><span style="font-size:9.5pt">&quot;<span style="color:blue">OrderId</span>&quot;
<span style="color:red">DataType</span><span style="color:blue">=</span>&quot;<span style="color:blue">Int32</span>&quot;
<span style="color:red">IdentityIncrement</span><span style="color:blue">=</span>&quot;<span style="color:blue">1</span>&quot;
<span style="color:red">IdentitySeed</span><span style="color:blue">=</span>&quot;<span style="color:blue">1</span>&quot;<span style="color:blue">/&gt;</span></span></div>
<div><span style="font-size:9.5pt; color:blue">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;</span><span style="font-size:9.5pt; color:#a31515">Column</span>
<span style="font-size:9.5pt; color:red">Name</span><span style="font-size:9.5pt; color:blue">=</span><span style="font-size:9.5pt">&quot;<span style="color:blue">SalesDate</span>&quot;
<span style="color:red">DataType</span><span style="color:blue">=</span>&quot;<span style="color:blue">DateTime</span>&quot;<span style="color:blue">/&gt;</span></span></div>
<div><span style="font-size:9.5pt; color:blue">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;</span><span style="font-size:9.5pt; color:#a31515">Column</span>
<span style="font-size:9.5pt; color:red">Name</span><span style="font-size:9.5pt; color:blue">=</span><span style="font-size:9.5pt">&quot;<span style="color:blue">CustomerName</span>&quot;
<span style="color:red">DataType</span><span style="color:blue">=</span>&quot;<span style="color:blue">String</span>&quot;
<span style="color:red">Length</span><span style="color:blue">=</span>&quot;<span style="color:blue">50</span>&quot;<span style="color:blue">/&gt;</span></span></div>
<div><span style="font-size:9.5pt; color:blue">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;/</span><span style="font-size:9.5pt; color:#a31515">Columns</span><span style="font-size:9.5pt; color:blue">&gt;</span></div>
<div><span style="font-size:9.5pt; color:blue">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;</span><span style="font-size:9.5pt; color:#a31515">Keys</span><span style="font-size:9.5pt; color:blue">&gt;</span></div>
<div><span style="font-size:9.5pt; color:blue">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;</span><span style="font-size:9.5pt; color:#a31515">PrimaryKey</span>
<span style="font-size:9.5pt; color:red">Name</span><span style="font-size:9.5pt; color:blue">=</span><span style="font-size:9.5pt">&quot;<span style="color:blue">OrderHeaderPK</span>&quot;<span style="color:blue">&gt;</span></span></div>
<div><span style="font-size:9.5pt; color:blue">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;</span><span style="font-size:9.5pt; color:#a31515">Columns</span><span style="font-size:9.5pt; color:blue">&gt;</span></div>
<div><span style="font-size:9.5pt; color:blue">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&lt;</span><span style="font-size:9.5pt; color:#a31515">Column</span> <span style="font-size:9.5pt; color:red">
ColumnName</span><span style="font-size:9.5pt; color:blue">=</span><span style="font-size:9.5pt">&quot;<span style="color:blue">OrderId</span>&quot;<span style="color:blue">/&gt;</span></span></div>
<div><span style="font-size:9.5pt; color:blue">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;/</span><span style="font-size:9.5pt; color:#a31515">Columns</span><span style="font-size:9.5pt; color:blue">&gt;</span></div>
<div><span style="font-size:9.5pt; color:blue">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;/</span><span style="font-size:9.5pt; color:#a31515">PrimaryKey</span><span style="font-size:9.5pt; color:blue">&gt;</span></div>
<div><span style="font-size:9.5pt; color:blue">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;/</span><span style="font-size:9.5pt; color:#a31515">Keys</span><span style="font-size:9.5pt; color:blue">&gt;</span></div>
<div><span style="font-size:9.5pt; color:blue">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;/</span><span style="font-size:9.5pt; color:#a31515">Table</span><span style="font-size:9.5pt; color:blue">&gt;</span></div>
<div><span style="font-size:9.5pt; color:blue">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;</span><span style="font-size:9.5pt; color:#a31515">Table</span>
<span style="font-size:9.5pt; color:red">Name</span><span style="font-size:9.5pt; color:blue">=</span><span style="font-size:9.5pt">&quot;<span style="color:blue">OrderDetail</span>&quot;
<span style="color:red">SchemaName</span><span style="color:blue">=</span>&quot;<span style="color:blue">tempdb.dbo</span>&quot;<span style="color:blue">&gt;</span></span></div>
<div><span style="font-size:9.5pt; color:blue">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;</span><span style="font-size:9.5pt; color:#a31515">Columns</span><span style="font-size:9.5pt; color:blue">&gt;</span></div>
<div><span style="font-size:9.5pt; color:blue">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;</span><span style="font-size:9.5pt; color:#a31515">Column</span>
<span style="font-size:9.5pt; color:red">Name</span><span style="font-size:9.5pt; color:blue">=</span><span style="font-size:9.5pt">&quot;<span style="color:blue">OrderDetailId</span>&quot;
<span style="color:red">DataType</span><span style="color:blue">=</span>&quot;<span style="color:blue">Int32</span>&quot;
<span style="color:red">IdentityIncrement</span><span style="color:blue">=</span>&quot;<span style="color:blue">1</span>&quot;
<span style="color:red">IdentitySeed</span><span style="color:blue">=</span>&quot;<span style="color:blue">1</span>&quot;<span style="color:blue">/&gt;</span></span></div>
<div><span style="font-size:9.5pt; color:blue">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;</span><span style="font-size:9.5pt; color:#a31515">TableReference</span>
<span style="font-size:9.5pt; color:red">Name</span><span style="font-size:9.5pt; color:blue">=</span><span style="font-size:9.5pt">&quot;<span style="color:blue">OrderId</span>&quot;
<span style="color:red">TableName</span><span style="color:blue">=</span>&quot;<span style="color:blue">OrderHeader</span>&quot;<span style="color:blue">/&gt;</span></span></div>
<div><span style="font-size:9.5pt; color:blue">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;</span><span style="font-size:9.5pt; color:#a31515">Column</span>
<span style="font-size:9.5pt; color:red">Name</span><span style="font-size:9.5pt; color:blue">=</span><span style="font-size:9.5pt">&quot;<span style="color:blue">ProductName</span>&quot;
<span style="color:red">DataType</span><span style="color:blue">=</span>&quot;<span style="color:blue">String</span>&quot;
<span style="color:red">Length</span><span style="color:blue">=</span>&quot;<span style="color:blue">50</span>&quot;<span style="color:blue">/&gt;</span></span></div>
<div><span style="font-size:9.5pt; color:blue">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;</span><span style="font-size:9.5pt; color:#a31515">Column</span>
<span style="font-size:9.5pt; color:red">Name</span><span style="font-size:9.5pt; color:blue">=</span><span style="font-size:9.5pt">&quot;<span style="color:blue">Qty</span>&quot;
<span style="color:red">DataType</span><span style="color:blue">=</span>&quot;<span style="color:blue">Int16</span>&quot;<span style="color:blue">/&gt;</span></span></div>
<div><span style="font-size:9.5pt; color:blue">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;</span><span style="font-size:9.5pt; color:#a31515">Column</span>
<span style="font-size:9.5pt; color:red">Name</span><span style="font-size:9.5pt; color:blue">=</span><span style="font-size:9.5pt">&quot;<span style="color:blue">UnitPrice</span>&quot;
<span style="color:red">DataType</span><span style="color:blue">=</span>&quot;<span style="color:blue">Currency</span>&quot;<span style="color:blue">/&gt;</span></span></div>
<div><span style="font-size:9.5pt; color:blue">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;/</span><span style="font-size:9.5pt; color:#a31515">Columns</span><span style="font-size:9.5pt; color:blue">&gt;</span></div>
<div><span style="font-size:9.5pt; color:blue">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;</span><span style="font-size:9.5pt; color:#a31515">Keys</span><span style="font-size:9.5pt; color:blue">&gt;</span></div>
<div><span style="font-size:9.5pt; color:blue">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;</span><span style="font-size:9.5pt; color:#a31515">PrimaryKey</span>
<span style="font-size:9.5pt; color:red">Name</span><span style="font-size:9.5pt; color:blue">=</span><span style="font-size:9.5pt">&quot;<span style="color:blue">OrderDetailPK</span>&quot;<span style="color:blue">&gt;</span></span></div>
<div><span style="font-size:9.5pt; color:blue">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;</span><span style="font-size:9.5pt; color:#a31515">Columns</span><span style="font-size:9.5pt; color:blue">&gt;</span></div>
<div><span style="font-size:9.5pt; color:blue">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
&lt;</span><span style="font-size:9.5pt; color:#a31515">Column</span> <span style="font-size:9.5pt; color:red">
ColumnName</span><span style="font-size:9.5pt; color:blue">=</span><span style="font-size:9.5pt">&quot;<span style="color:blue">OrderDetailId</span>&quot;<span style="color:blue">/&gt;</span></span></div>
<div><span style="font-size:9.5pt; color:blue">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;/</span><span style="font-size:9.5pt; color:#a31515">Columns</span><span style="font-size:9.5pt; color:blue">&gt;</span></div>
<div><span style="font-size:9.5pt; color:blue">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;/</span><span style="font-size:9.5pt; color:#a31515">PrimaryKey</span><span style="font-size:9.5pt; color:blue">&gt;</span></div>
<div><span style="font-size:9.5pt; color:blue">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;/</span><span style="font-size:9.5pt; color:#a31515">Keys</span><span style="font-size:9.5pt; color:blue">&gt;</span></div>
<div><span style="font-size:9.5pt; color:blue">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;/</span><span style="font-size:9.5pt; color:#a31515">Table</span><span style="font-size:9.5pt; color:blue">&gt;</span></div>
<div><span style="font-size:9.5pt; color:blue">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;/</span><span style="font-size:9.5pt; color:#a31515">Tables</span><span style="font-size:9.5pt; color:blue">&gt;</span></div>
<div><span style="font-size:9.5pt; color:blue">&lt;/</span><span style="font-size:9.5pt; color:#a31515">Biml</span><span style="font-size:9.5pt; color:blue">&gt;</span></div>
<div>&nbsp;</div>
<p>Tables are defined in a &lt;<a href="http://www.varigence.com/documentation/biml/biml_Varigence.Languages.Biml.Table.AstTableNode.html">Table</a>&gt; tag. They can have columns defined, as well as keys, and even indexes (not shown in the example above).
Notice that the OrderId column doesn&rsquo;t have a DataType attribute. Many of the attributes in Biml have default values, and data type is one of them. If it&rsquo;s not specified, the column data type will default to Int32. The primary key for the table
is defined with a &lt;<a href="http://www.varigence.com/documentation/biml/biml_Varigence.Languages.Biml.Table.AstTablePrimaryKeyNode.html">PrimaryKey</a>&gt; element.</p>
<p>The OrderDetail table includes a &lt;<a href="http://www.varigence.com/documentation/biml/biml_Varigence.Languages.Biml.Table.AstTableColumnTableReferenceNode.html">TableReference</a>&gt; column. TableReference columns are a special class of columns, that
define that this column should have a foreign key reference to another table. This one is referencing back to the OrderHeader table. It&rsquo;s not shown, but you can also use a
<a href="http://www.varigence.com/documentation/biml/biml_Varigence.Languages.Biml.Table.AstMultipleColumnTableReferenceNode.html">
MultipleColumnTableReference</a>, if your foreign key needs to span multiple columns.</p>
<p>Great &ndash; now you have your tables defined in Biml, but how do you make use of that? If only there were some way to run this against your database to create the tables&hellip; Well, fortunately, there is &ndash; by using BimlScript. BimlScript is a scripting
layer that automates the production of Biml (similar in concept to the way ASP.NET produces HTML). To set this up, you need to add two Biml files to your project &ndash; one to hold the table definitions above, and one to hold the BimlScript.</p>
<p>First, add a new Biml file to the SSIS project (see <a href="http://agilebi.com/jwelch/2011/05/13/creating-a-basic-package-using-biml/">
Part 1</a> if you need a refresher on this). Copy the Biml above to this file, and rename the file to TableDefinitions.biml.</p>
<p><a href="http://download.codeplex.com/download?ProjectName=bidshelper&DownloadId=243657"><img title="image" src="http://download.codeplex.com/download?ProjectName=bidshelper&DownloadId=243658" alt="image" width="242" height="245" border="0" style="padding-left:0px; padding-right:0px; display:inline; padding-top:0px; border-width:0px"></a></p>
<p>Second, add an additional Biml file. Name this one CreateTables.biml.</p>
<p><a href="http://download.codeplex.com/download?ProjectName=bidshelper&DownloadId=243659"><img title="image" src="http://download.codeplex.com/download?ProjectName=bidshelper&DownloadId=243660" alt="image" width="220" height="259" border="0" style="padding-left:0px; padding-right:0px; display:inline; padding-top:0px; border-width:0px"></a></p>
<p>Open the CreateTables.biml file, and replace the contents with the following code:</p>
<div><code>&lt;#@ template language=&quot;C#&quot; hostspecific=&quot;True&quot; #&gt;</code><br>
<br>
<br>
<code>&lt;Biml xmlns=&quot;http://schemas.varigence.com/biml.xsd&quot;&gt;</code><br>
<br>
<code>&nbsp;&nbsp;&nbsp; &lt;Packages&gt;</code><br>
<br>
<code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;Package Name=&quot;Create Tables&quot; AutoCreateConfigurationsType=&quot;None&quot; ConstraintMode=&quot;Linear&quot;&gt;</code><br>
<br>
<code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;Tasks&gt;</code><br>
<br>
<code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;# foreach(var table in RootNode.Tables) {#&gt;</code><br>
<br>
<code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;ExecuteSQL Name=&quot;Create &lt;#=table.Name#&gt;&quot; ConnectionName=&quot;&lt;#=table.Connection.Name#&gt;&quot;&gt;</code><br>
<br>
<code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;DirectInput&gt;</code><br>
<br>
<code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;#=table.GetTableSql()#&gt;&nbsp;&nbsp;&nbsp;
</code><br>
<br>
<code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;/DirectInput&gt;</code><br>
<br>
<code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;/ExecuteSQL&gt;</code><br>
<br>
<code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;# } #&gt;</code><br>
<br>
<code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;/Tasks&gt;</code><br>
<br>
<code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;/Package&gt;</code><br>
<br>
<code>&nbsp;&nbsp;&nbsp; &lt;/Packages&gt;</code><br>
<br>
<code>&lt;/Biml&gt;</code><br>
<br>
&nbsp;<br>
Note: When you paste this into Visual Studio, the formatting and indenting will be wrong and the code will not work.&nbsp;After pasting, press Ctrl-Z to undo formatting and indenting and the paste will be successful. See&nbsp;<a href="http://bimlscript.com/Walkthrough/Details/45">this
tip</a> for more information.<br>
&nbsp;</div>
<p>This file has a header at the beginning that indicates the script will use C#. The next section defines a package named &ldquo;Create Tables&rdquo;. The RootNode.Tables section inside the Tasks element is the interesting part.&nbsp;This code iterates over
the tables that are part of the current model. For each table it finds, it creates an ExecuteSQL task, and embeds the SQL to create the table in the package. The code could be repeated to iterate over
<a href="http://www.varigence.com/documentation/biml/biml_Varigence.Languages.Biml.Dimension.AstDimensionNode.html">
Dimensions</a> and <a href="http://www.varigence.com/documentation/biml/biml_Varigence.Languages.Biml.Fact.AstFactNode.html">
Facts</a>, which are special classes of tables.</p>
<p>Notice that there are no tables defined in the BimlScript file. The BimlScript can&rsquo;t operate against objects defined in the same file, which is why we created the TableDefinitions.biml file separately. To produce the package, multi-select both TableDefinitions.biml,
and CreateTables.biml, right-click, and choose Expand Biml File.</p>
<p><a href="http://download.codeplex.com/download?ProjectName=bidshelper&DownloadId=243661"><img title="image" src="http://download.codeplex.com/download?ProjectName=bidshelper&DownloadId=243662" alt="image" width="296" height="348" border="0" style="padding-left:0px; padding-right:0px; display:inline; padding-top:0px; border-width:0px"></a></p>
<p>This will produce a new SSIS package in the project named Create Tables.dtsx. It contains two Execute SQL tasks, one for each table.</p>
<p><a href="http://download.codeplex.com/download?ProjectName=bidshelper&DownloadId=243663"><img title="image" src="http://download.codeplex.com/download?ProjectName=bidshelper&DownloadId=243664" alt="image" width="175" height="152" border="0" style="padding-left:0px; padding-right:0px; display:inline; padding-top:0px; border-width:0px"></a></p>
<p>Each task includes the appropriate SQL to create the tables. As an example, here&rsquo;s the OrderHeader SQL from the Execute SQL task.</p>
<pre>SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
-------------------------------------------------------------------
IF EXISTS (SELECT * from sys.objects WHERE object_id = OBJECT_ID(N'[OrderHeader]') AND type IN (N'U'))
DROP TABLE [OrderHeader]
GO
CREATE TABLE [OrderHeader]
(
-- Columns Definition
[OrderId] int IDENTITY(1,1) NOT NULL
, [SalesDate] datetime NOT NULL
, [CustomerName] nvarchar(50) NOT NULL
-- Constraints
,CONSTRAINT [OrderHeaderPK] PRIMARY KEY CLUSTERED
(
[OrderId] Asc) WITH(PAD_INDEX = OFF,IGNORE_DUP_KEY = OFF) ON [PRIMARY]
)
ON [PRIMARY]
WITH (DATA_COMPRESSION = NONE)
GO
-------------------------------------------------------------------</pre>
<p>Note that the tables are ordered in the package in the same order they are defined in the Biml file. If you have tables with dependencies, make sure to order them correctly.</p>
<p>In the next post, we&rsquo;ll look at some ways to copy data dynamically using BimlScript.</p>
<p>[cross-posted with updates from <a href="http://agilebi.com/jwelch/2011/05/26/creating-tables-using-biml-and-bimlscript/">
http://agilebi.com/jwelch/2011/05/26/creating-tables-using-biml-and-bimlscript/</a>]</p>
</div><div class="ClearBoth"></div>furmanggSun, 15 Feb 2015 07:41:15 GMTUpdated Wiki: Creating Tables using Biml and BimlScript 20150215074115A