Difference between revisions of "SSIS:Best Practices"

From Melissa Data Wiki
Jump to navigation Jump to search
 
(2 intermediate revisions by the same user not shown)
Line 1: Line 1:
[[SSIS:Data Quality Components|← SSIS:Data Quality Components]]
[[SSIS:Data Quality Components|← SSIS:Data Quality Components]]
{{CustomTOC}}
{{CustomTOC}}
==Expired License and Community Edition Mode==
When a license string is expired, SSIS will enable Community Edition mode unless a valid license is entered
[[File:SSIS_BP_CommunityEdition.png|link=]]
The Community Edition of SSIS has limited functionality. Providing a few basic abilites for you to sample our products. Some options may be greyed out. <span style="color:red;">If you continue to run the project with a community license, the component will attempt to remove mappings not supported by the license in order to run. Please make sure to backup your configuration in order to avoid data mapping loss.</span>
For more information on the Community Edition features, see the [[SSIS:Subscribe|SSIS Subscribe wiki page]].
<h3 class="h3grey">Adding a Valid License</h3>
:To add a valid license, follow the steps below.
:1. Click the Apply License button.
::SSIS will now prompt the Advanced Configuration screen
::[[File:SSIS_BP_CommunityEdition_AdvConfig1.png|link=]]
:2. Select the Processing Mode from the drop down menu - On-Premise, Melissa Data Cloud or Dedicated Cloud.
::[[File:SSIS_BP_CommunityEdition_AdvConfig2.png|link=]]
:3. Once the Processing Mode has been selected, insert the valid license into the License Key box.
:4. Click Test Configuration.
::[[File:SSIS_BP_CommunityEdition_AdvConfig3.png|link=]]
:5. Click Close, then OK. The valid license string is now loaded into SSIS
==Saving Configuration File==
<h3 class="h3grey">Field Mappings</h3>
:We recommend backing up your configuration files for each project made in SSIS. The configuration file holds all the field mapping information made in the project.
:The following steps will show how to save the configuration file.
:1. Open the project that has all the field mappings set
:2. Double click the component to access the interface
:3. Click File, then choose Save as .cfg file.
::[[File:SSIS_BP_SaveConfig_Save.png|link=]]
:4. Choose a name and directory to save the configuration file.
::[[File:SSIS_BP_SaveConfig_SaveMessage.png|link=]]
<h3 class="h3grey">Loading Configuration File</h3>
:The following instructions will display how to load a saved configuration file.
:1. Open project and double click the component to access the interface
:2. Click File, then choose Open .cfg file.
::[[File:SSIS_BP_SaveConfig_Open.png|link=]]
:3. Locate the previously saved configuration file and click Open.
::[[File:SSIS_BP_SaveConfig_Reload.png|link=]]
:4. A message will appear stating the configuration was successfully loaded.
::[[File:SSIS_BP_SaveConfig_Load.png|link=]]
:SSIS will load the field mappings contained within the configuration file into your project.
==Running SSIS packages from the Command Line==
==Running SSIS packages from the Command Line==
BP_XXSS_001 <br>
BP_XXSS_001 <br>
Line 61: Line 128:
Although our components are tested and function on older hardware configurations, we "officially" support a recommended hardware configuration and will "unofficially" support configurations in which the components will run. Hardware configurations below the minimum requirements are not supported, in which we will advise hardware upgrades. <br>
Although our components are tested and function on older hardware configurations, we "officially" support a recommended hardware configuration and will "unofficially" support configurations in which the components will run. Hardware configurations below the minimum requirements are not supported, in which we will advise hardware upgrades. <br>


[http://www.melissadata.com/webhelp/ssis/updated/Getting_Started/Quick_Start/Quick_Start.htm#System_Requirements SSIS Hardware Requirements]
[[SSIS:System Requirements|SSIS System Requirements]]

Latest revision as of 23:19, 18 November 2019

← SSIS:Data Quality Components


Expired License and Community Edition Mode

When a license string is expired, SSIS will enable Community Edition mode unless a valid license is entered

SSIS BP CommunityEdition.png

The Community Edition of SSIS has limited functionality. Providing a few basic abilites for you to sample our products. Some options may be greyed out. If you continue to run the project with a community license, the component will attempt to remove mappings not supported by the license in order to run. Please make sure to backup your configuration in order to avoid data mapping loss.

For more information on the Community Edition features, see the SSIS Subscribe wiki page.


Adding a Valid License

To add a valid license, follow the steps below.
1. Click the Apply License button.
SSIS will now prompt the Advanced Configuration screen
SSIS BP CommunityEdition AdvConfig1.png
2. Select the Processing Mode from the drop down menu - On-Premise, Melissa Data Cloud or Dedicated Cloud.
SSIS BP CommunityEdition AdvConfig2.png
3. Once the Processing Mode has been selected, insert the valid license into the License Key box.
4. Click Test Configuration.
SSIS BP CommunityEdition AdvConfig3.png
5. Click Close, then OK. The valid license string is now loaded into SSIS


Saving Configuration File

Field Mappings

We recommend backing up your configuration files for each project made in SSIS. The configuration file holds all the field mapping information made in the project.
The following steps will show how to save the configuration file.
1. Open the project that has all the field mappings set
2. Double click the component to access the interface
3. Click File, then choose Save as .cfg file.
SSIS BP SaveConfig Save.png
4. Choose a name and directory to save the configuration file.
SSIS BP SaveConfig SaveMessage.png


Loading Configuration File

The following instructions will display how to load a saved configuration file.
1. Open project and double click the component to access the interface
2. Click File, then choose Open .cfg file.
SSIS BP SaveConfig Open.png
3. Locate the previously saved configuration file and click Open.
SSIS BP SaveConfig Reload.png
4. A message will appear stating the configuration was successfully loaded.
SSIS BP SaveConfig Load.png
SSIS will load the field mappings contained within the configuration file into your project.


Running SSIS packages from the Command Line

BP_XXSS_001
For more efficient memory usage, run your saved SSIS package from the command line instead of running directly from visual studio. Example: dtexec.exe /F "c:\fullpath\Package.dtsx" /Rep EWPD > "OptionalCreateLog.log" tests have shown memory usage to be up to 66% less. Running from visual studio will start devenv and DtsDebugHost processes in your Task Manager, whereas command line processing will start DTEXEC.


Update to the latest version.

BP_XXSS_002
We always recommend users use the most current build (version), which may contain new or improved features, code fixes, or updated functionality. Melissa data provides many ways to ensure you keep your components to the latest version.


MatchUp: Optimize Your Matchcode

BP_MUXX_002
Applying certain optimizations, or configuring the matchcode settings, can greatly reduce processing time without sacrificing accuracy. For a complete discussion see the Matchcodes > Optimization section of the MatchUp SSIS WebHelp or


MatchUp: Keep Work File Location Local

BP_MUXX_004
Although the component's interface lets you change the location of the MatchUp work files, you should only override this setting after thorough testing for operating systems and speed.


MatchUp: Back up your Matchcode database

BP_MUXX_005


Leverage SSIS

BP_XXSS_003
One of the nice things about SSIS is that you can use a canned component (like Conditional split) to separate source records by zipcode into different streams and run the jobs separately. If you have the hardware, this may allow you to take advantage of multi-threading of the processor and multi-instance of the components.


Limit Pass Through Fields

BP_XXSS_004
The greater the number of Pass-through fields required will slow down the process. You should pass a unique record identifier as a passthru, then using the source record identifier, join the results with the source fields not used outside the component.


Run on a local instance

BP_XXSS_005
VERY IMPORTANT - We are not convinced that VM memory management can be replicated as well as a physical machine - at least not without advanced configuration on your part and high performance hardware hosting the VM - which is beyond the scope of our supporting the performance of the component. Below are some links to related concerns with VM performance...

http://www.sqlsolutions.com/articles/articles/SQL_Server_and_VMware-A_Potentially_Fatal_Combination.htm
http://stackoverflow.com/questions/149318/virtualized-sql-server-why-not
http://virtualization.info/en/news/2008/12/running-sql-server-in-virtual-machine.html
http://www.hanselman.com/blog/VirtualMachineCPUPerformance.aspx
http://stackoverflow.com/questions/2678119/is-there-a-reason-why-ssis-significantly-slows-down-after-a-few-minutes

These are not meant as a deterrent, only to point out the potential issue.

Minimum Hardware Requirements

BP_XXSS_006
Although our components are tested and function on older hardware configurations, we "officially" support a recommended hardware configuration and will "unofficially" support configurations in which the components will run. Hardware configurations below the minimum requirements are not supported, in which we will advise hardware upgrades.

SSIS System Requirements