Execute packages in Scale Out

Case
How do I execute a package with the new Scale Out function? I don't see any options when executing a package.

Find the Scale Out options

Solution
The new Scale Out execution is not (yet) integrated in the standard package execution window. And the Package Execution Task has not changed. Therefore it will always execute the package on the same worker as the parent package. Both will probably change within a couple CTP releases.

Catalog
If you right click on SSISDB within the catalog then you will see the new context menu item "Execute in Scale Out..."

Execute in Scale Out...

Next you can choose which packages to execute and on which worker servers.

Execute in Scale Out

After hitting the OK button no reports are shown like in the regular execution, but you can find the reports in the context menu of the Catalog.

No open report option

And in the report you can see which 'random' worker executed the particular package.

The Machine property show which worker was used

Conclusions
Nice first version of the Scale Out. Hopefully the next CTP contains a new version of the Execute Package Task and an integration of the regular execution and scale out execution. Please try it out and let me (or Microsoft) know what you think about it.

Some considerations, because the worker services uses a local system account you might want to consider changing that to a domain account or use other options like a proxy or a database user. Other concerns are the firewall if you're using a local database on the master and local paths (d:\myfiles\) on the master won't work either.

NT Service\SSISScaleOutWorker140

Deployment bug SSIS VNEXT missing reference

Case
When deploying a project from SSDT VNEXT (SQL Server Data Tools 17.0 RC1) I get an error.

Could not load file or assembly

'Microsoft.SqlServer.Management.IntegrationServicesEnum, Culture=neutral,

PublicKeyToken=89845dcd8080cc91' or one of its dependencies.

The system cannot find the file specified. (mscorlib)

Solution
SSDT 17.0 RC1 still got some bugs, for real projects you should use SSDT 16.5 But if you want to discover for example the Scale Out function or Support for Microsoft Dynamics Online Resources of SQL VNEXT then you should/could use this version.

Three solutions (in order of recommendation):

1. Since this bug only occurs in SSDT, you could deploy outside SSDT with PowerShell of by just double clicking the ISPAC file. Then this error won't occur.
2. It's a known issue. Just wait for the next release of SSDT (early 2017).
3. Or add the missing reference to <drive>:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\devenv.exe.config and restart SSDT (at your own risk of course)

Add missing reference

<dependentAssembly>
    <assemblyIdentity name="Microsoft.SqlServer.Management.IntegrationServicesEnum" publicKeyToken="89845dcd8080cc91" culture="neutral"/>
    <bindingRedirect oldVersion="13.0.0.0-14.100.0.0" newVersion="13.0.0.0"/>
</dependentAssembly>

Solved

Setup SSIS Scale Out

Case
SQL VNext has a new Scale Out function. How does it work and how do you install and configure that?

Solution
The new Scale Out option in SSIS VNEXT gives you the ability to execute multiple packages distributed to multiple worker machines. You can select multiple packages on the master that will be executed in parallel by one or more worker machines.

Machine setup
To make sense of a Scale Out you of course need multiple machines. We need a master machine and one or more worker machines. Because the master distributes the executions and doesn't execute packages it self, you may want to consider installing a worker on the same machine as the master to make use of its resources. The worker machines only have a worker installed. A SQL Server engine installation is not necessary on a worker.

Option 1: Master only distributes executions

Option 2: Master also executes packages itself

For this example I will use option 1 with a master and two separate workers on three HyperV machines. All machines are identical with Windows Server 2012 R2 with all updates installed.

HyperV Machines

Download

Before installing your first you need to download SQL Server VNEXT and the associated SSMS and SSDT.

Installation steps:

1. Install SQL Server VNEXT on master
2. Configure firewall on master
3. Copy master certificate to workers
4. Install SQL Server VNEXT on workers
5. Copy worker certificates to master
6. Install SSMS VNEXT to add Catalog on master
7. Install worker certificates on master
8. Enable scale out workers on master

Installation step 1: install SQL Server VNEXT on master
Install SQL Server VNEXT on the 'Master' machine called "SQLVNEXT_M". Below the most important steps of the installation. At the bottom all screens are shown in a movie.

We need the Database Engine to store the SSISDB

We need SSIS and the Scale Out Master

SQL Server Authentication mode is required on the SSISDB

Choose port 8391 and create a new SSL certificate

All steps

Installation step 2: configure firewall on master
Open a firewall port on the Scale Out Master. We need at least an inbound rule for port 8391 supplied as EndPoint in the previous step, but a complete list of all SQL Server ports can be found here.

Installation step 3: copy master certificate to workers
We created a new certificate during installation of the Scale Out Master. You can find it in <drive>:\Program Files\Microsoft SQL Server\140\DTS\Binn. We need that certificate during installation of the Scale Out Workers. So copy it to the worker machines.

Copy SSISScaleOutMaster.cer to worker

Copy SSISScaleOutMaster.cer to worker

Installation step 4: install SQL Server VNEXT on workers
Install SQL Server VNEXT on the 'Worker' machines called "SQLVNEXT_W1" and "SQLVNEXT_W2". Below the most important steps of the installation. At the bottom all screens are shown in a movie.

Only select SSIS and Scale Out Worker (no engine needed)

Add an EndPoint like https://SQLVNEXT_M:8391. This is the name of the
Scale Out Master machine and the port chosen during the installation of the
Scale Out Master.
The certificate is the one you copied from the Scale Out Master
in one of the previous steps.

All steps

Installation step 5: copy worker certificates to master
During installation of the Scale Out Worker machines, certificates where created which we need register on the machine with the Scale Out Master. With these certificates the Scale Out Master can authenticate the Scale Out Workers. You can find SSISScaleOutWorker.cer in <drive>:\Program Files\Microsoft SQL Server\140\DTS\Binn (repeat this for all workers).

Copy SSISScaleOutWorker.cer to master

Copy SSISScaleOutWorker.cer to master

Installation step 6: install SSMS VNEXT to add Catalog on master
Now we need to add a catalog to the master. To do this you need to install SSMS VNEXT first. For this demo situation I installed SSMS VNEXT on the master machine.

Install SSMS VNEXT

Add catalog as you normally do, but notice the extra option:

Enable this server as SSIS scale out master

Add catalog

Installation step 7: install worker certificates on master
Now we need to install all Scale Out Worker Certificates on the Scale Out Master machine. They should be stored in the Trusted Root Certification Authorities. Repeat the steps below for all Worker Certificates.

Store certificates in Trusted Root Certification Authorities

All Steps

Installation step 8: enable scale out workers on master
Make sure the services SSISScaleOutWorker140 on the Worker machines are started and SSISScaleOutMaster140 on the master. Then start SSMS and connect to the SQL Server instance on the master and execute the following query:

-- Get Worker info
SELECT * FROM [SSISDB].[catalog].[worker_agents]

It could take a few minutes before the worker machines are registered. Once that happens the query should return records. Use the values from the WorkerIdAgentId column in the next Stored Procedure Call to enable the Scale Out Workers

-- Enable Workers
EXEC [SSISDB].[catalog].[enable_worker_agent] 'F5BA7B83-D8FC-49D2-8896-95C0F0725562' -- SQLVNEXT_W1
EXEC [SSISDB].[catalog].[enable_worker_agent] 'FC0B9E86-8BB3-4A3D-B3EB-5A29DE1CE9BE' -- SQLVNEXT_W2

Enable Scale Out Workers

Now you're ready to deploy your first project and execute packages with the new Scale Out function. Also see the Microsoft walkthrough for the scale out setup.

Conclusion
The Master Worker setup is a great way to distribute package executions over multiple servers. When you will choose for upgrading your existing SSIS server with more memory and more cores above the new master-worker setup, probably depends on the licensing model. But when you already maxed out the hardware of your current SSIS server, then this new master-worker setup is an easy way to upgrade.
And what about a future Scale Out to Azure? If a weekly, monthly or quarterly run is taking to much time or one of your worker servers is down (for maintenance?).

SSIS Naming conventions

In 2006 Jamie Thomson came up with naming conventions for SSIS tasks and data flow components. These naming conventions make your packages and logs more readable. Five SQL Server versions and a decade later a couple of tasks and components were deprecated, but there were also a lot of new tasks and components introduced by Microsoft.

Together with Koen Verbeeck (B|T) and André Kamman (B|T) we extended the existing list with almost 40 tasks/components and created a PowerShell Script that should make it easier to check/force the naming conventions. This PowerShell script will soon be published at GitHub as a PowerShell module. But for now you can download and test the fully working proof of concept script. Download both ps1 files and the CSV file. Then open "naming conventions v4.ps1" and change the parameters before executing it. The script works with local packages because you can't read individual package from the catalog, but you can use a powershell script to download your packages from the catalog.

PowerShell Naming Conventions Checker

Task name	Prefix	Type	New
For Loop Container	FLC	Container
Foreach Loop Container	FELC	Container
Sequence Container	SEQC	Container
ActiveX Script	AXS	Task
Analysis Services Execute DDL Task	ASE	Task
Analysis Services Processing Task	ASP	Task
Azure Blob Download Task	ADT	Task	*
Azure Blob Upload Task	AUT	Task	*
Azure HDInsight Create Cluster Task	ACCT	Task	*
Azure HDInsight Delete Cluster Task	ACDT	Task	*
Azure HDInsight Hive Task	AHT	Task	*
Azure HDInsight Pig Task	APT	Task	*
Back Up Database Task	BACKUP	Task	*
Bulk Insert Task	BLK	Task
CDC Control Task	CDC	Task	*
Check Database Integrity Task	CHECKDB	Task	*
Data Flow Task	DFT	Task
Data Mining Query Task	DMQ	Task
Data Profiling Task	DPT	Task	*
Execute Package Task	EPT	Task
Execute Process Task	EPR	Task
Execute SQL Server Agent Job Task	AGENT	Task	*
Execute SQL Task	SQL	Task
Execute T-SQL Statement Task	TSQL	Task	*
Expression Task	EXPR	Task
File System Task	FSYS	Task
FTP Task	FTP	Task
Hadoop File System Task	HFSYS	Task	*
Hadoop Hive Task	HIVE	Task	*
Hadoop Pig Task	PIG	Task	*
History Cleanup Task	HISTCT	Task	*
Maintenance Cleanup Task	MAINCT	Task	*
Message Queue Task	MSMQ	Task
Notify Operator Task	NOT	Task	*
Rebuild Index Task	REBIT	Task	*
Reorganize Index Task	REOIT	Task	*
Script Task	SCR	Task
Send Mail Task	SMT	Task
Shrink Database Task	SHRINKDB	Task	*
Transfer Database Task	TDB	Task
Transfer Error Messages Task	TEM	Task
Transfer Jobs Task	TJT	Task
Transfer Logins Task	TLT	Task
Transfer Master Stored Procedures Task	TSP	Task
Transfer SQL Server Objects Task	TSO	Task
Update Statistics Task	STAT	Task	*
Web Service Task	WST	Task
WMI Data Reader Task	WMID	Task
WMI Event Watcher Task	WMIE	Task
XML Task	XML	Task
Transformation name	Prefix	Type	New
ADO NET Source	ADO_SRC	Source	*
Azure Blob Source	AB_SRC	Source	*
CDC Source	CDC_SRC	Source	*
DataReader Source	DR_SRC	Source
Excel Source	EX_SRC	Source
Flat File Source	FF_SRC	Source
HDFS File Source	HDFS_SRC	Source	*
OData Source	ODATA_SRC	Source	*
ODBC Source	ODBC_SRC	Source	*
OLE DB Source	OLE_SRC	Source
Raw File Source	RF_SRC	Source
SharePoint List Source	SPL_SRC	Source
XML Source	XML_SRC	Source
Aggregate	AGG	Transformation
Audit	AUD	Transformation
Balanced Data Distributor	BDD	Transformation	*
Cache Transform	CCH	Transformation	*
CDC Splitter	CDCS	Transformation	*
Character Map	CHM	Transformation
Conditional Split	CSPL	Transformation
Copy Column	CPYC	Transformation
Data Conversion	DCNV	Transformation
Data Mining Query	DMQ	Transformation
Derived Column	DER	Transformation
DQS Cleansing	DQSC	Transformation	*
Export Column	EXPC	Transformation
Fuzzy Grouping	FZG	Transformation
Fuzzy Lookup	FZL	Transformation
Import Column	IMPC	Transformation
Lookup	LKP	Transformation
Merge	MRG	Transformation
Merge Join	MRGJ	Transformation
Multicast	MLT	Transformation
OLE DB Command	CMD	Transformation
Percentage Sampling	PSMP	Transformation
Pivot	PVT	Transformation
Row Count	CNT	Transformation
Row Sampling	RSMP	Transformation
Script Component	SCR	Transformation
Slowly Changing Dimension	SCD	Transformation
Sort	SRT	Transformation
Term Extraction	TEX	Transformation
Term Lookup	TEL	Transformation
Union All	ALL	Transformation
Unpivot	UPVT	Transformation
ADO NET Destination	ADO_DST	Destination	*
Azure Blob Destination	AB_DST	Destination	*
Data Mining Model Training	DMMT_DST	Destination
Data Streaming Destination	DS_DST	Destination	*
DataReaderDest	DR_DST	Destination
Dimension Processing	DP_DST	Destination
Excel Destination	EX_DST	Destination
Flat File Destination	FF_DST	Destination
HDFS File Destination	HDFS_DST	Destination	*
ODBC Destination	ODBC_DST	Destination	*
OLE DB Destination	OLE_DST	Destination
Partition Processing	PP_DST	Destination
Raw File Destination	RF_DST	Destination
Recordset Destination	RS_DST	Destination
SharePoint List Destination	SPL_DST	Destination
SQL Server Compact Destination	SSC_DST	Destination	*
SQL Server Destination	SS_DST	Destination

Example of the prefixes