Title: Microsoft
Visual Studio, Dynamics AX ETL SSIS Package Deployment, SQL Security
Credential, SQL Proxies, and SQL Job implementation:
Author: Toraj
Khavari
Date: December
22, 2015
Objective: Microsoft
Visual Studio (VS) is an integrated solution to create SQL Server Integrated
Services (SSIS) packages. In this case, the SSIS Package Extract Transfer and
Load (ETL) Microsoft Dynamics AX tables to an Enterprise Data Warehouse (EDW). In this article, we outline the following
items.
Ø VS
created SSIS package deployment
Ø Creating
SQL Security Credential
Ø Establishing
SQL Proxies
Ø Implementing
a SQL Job with execution schedule
The
SQL Security Credential and Proxies are needed to meet the integrated mode
requirements. The SQL “Login” enables the administrators to setup SQL users.
However, the SQL “Login” setup has no use when the SSIS package execution requires
multiple databases integration. Henceforth, Microsoft SQL has Security
Credential and Proxies technologies to empower jobs using domain / network
“Login” across different SQL databases and multiple servers.
This
article is based on the assumption that the VS SSIS package/s is created,
tested, and ready for deployment, I recommend you refer to the following blogs
for creating SSIS packages.
Tittles:
1-
Microsoft Dynamics AX to Enterprise Data
Warehouse (EDW) SQL Extract Transform and Load (ETL).
2-
Microsoft Dynamics AX to Enterprise Data
Warehouse (EDW) SQL Extract Transform and Load (ETL) for Multiple Tables.
<Side>
During my VS SSIS deployment and SQL job setup, discovered a lot of information
is on-line. However, there is not a comprehensive solution collection. My hope
is, you will find the following helpful and save you some time.
Article Body: VS
SSIS package deployment and SQL job setup using integration mode have multiple
steps. Prerequisites are as follows.
A.
Have
an existing and tested VS SSIS package. Rebuild the solution.
B.
Databases’
connections must have the ”Log on to the server” “Use Windows Authentication”
selected. Also, known as integrated mode.
C.
Have
a Network Login ID and Password, preferably service ID and Password with the
SQL Access rights to all SQL databases involved in your solution.
D.
Delete
an existing SQL Integrated Catalogs Projects, in my case “EDWSSIS_Extract”.
Initializes your project in the SQL Integrated Catalogs Projects. Removes all
the existing Projects’ Packages, connection string, and their components.
1.
VS
created SSIS package deployment
1.1. Identify
and navigate to the VS SSIS Project’s Integrated Services Project Deployment
File. It is in the VS project’s “bin” folder.
In my case the path is as follows.
C:\VSProjects\Solutions\Dev\SSIS\DWETL\EDWSSIS_Extract\EDWSSIS_Extract\bin\Development
1.2. “Open”
your project Integrated Services Project Deployment File. Integrated Services
Deployment Wizard assist you to deploy the project. Follow the wizard, in the “Select
Destination” enter the “Server Name” and “Path”. The “Path” will be the SQL
Integrated Catalogs Projects.
1.3. Navigate
to the SQL Integrated Catalogs Projects. Select the package, right mouse click
“Validate” > Connection Managers tab. Validate the packages
“ConnectionString”.
The
connections must be “Integrated Security” and “UserName” equal to NULL.
Connection String example is as follows.
Data
Source=<YourServerName>;Initial
Catalog=AX2012R2_PROD;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto
Translate=False;
If any of the
connection string are not Integrated Security with user name equal NULL, repeat
prerequisites A and B, and previous steps 1.
2.
Creating
SQL Security Credential
2.1. Prerequisites
“C” outlined having a Network Login ID and Password. In my case, I prefer,
service ID. Because they have a lot of different security roles than employee
login. E.g., Service IDs in our company do not require frequent password
changes.
2.2. Start
the Microsoft SQL Server Management Studio, connect to your database, navigate
to SQL Server, “Security” > ”Credential. Create a new “Credential”. Type
“Credential name”, “Network Service ID”, and “Password”.
3.
Establishing
SQL Proxies
3.1. Navigate
to “SQL Server Agent” > “Proxies” > “SSIS Package Execution”. Select
right mouse click, “New Proxy…” Enter “Proxy Name, “Credential name”, and
select “SQL Server Integrated Services Package”. The “Credential name” must be
the same as step number 2.2.
4.
Implementing
a SQL Job with execution schedule
4.1. Select
“SQL Server Agent” > “Jobs” > right mouse click “New Job…”. In the
“General” tab enter “Name” and “Owner”. The “Owner” should be the Network
Service ID Login from prerequisite “C”.
4.2. In the
“Steps” tab, select “New…”. In the “General” tab, type “Step name”. Select “SQL
Server Integration Services Package” for “Type”. Select in my case
“SSIS_Proxy_AXDWUser” for the “Run as”. The “Run as” must match the step 3.1.
Select “SSIS Catalog” for “Package source”. Type “Server” name. Select “Log on
to the server” “Use Windows Authentication”. For the “Package” navigate to the
desired package.
4.3. In the
“Schedules” tab select the appropriate setup to meet the business requirement.
To
test, select the created job, right mouse click “Start Job at Step…”.
Enable
the new job. You are done.
The
scheduled job uses the created proxy. The proxy uses a credential with correct
network login and password. To me; proxy and credential are delegate / trusted
agent for security.
Hints:
-
The
Network service ID must have a SQL Logins with correct SQL authority.
-
The
job “View History” and “Integrated Services Catalogs” “Packages” reports have
great tools for debugging, performance and traceability.
-
The
“Proxies” “SSIS Package Execution” “References” tab has all the “Jobs step”
listed which utilizes this specific proxy. SQL will do the references for you.
Microsoft
VS, Dynamics AX ETL SSIS Package Deployment, SQL Security Credential, SQL
Proxies, and SQL Job implementation are powerful tools and technologies. Enjoy
using them to meet the business needs.
I
wish you and yours a Merry Christmas, Happy New Year, good health and
prosperous 2016.
References:
-
Configure
a User to Create and Manage SQL Server Agent Jobs - https://msdn.microsoft.com/en-us/library/ms187901.aspx
