It's good practice to add tags to all AWS resources being created so that the cost of the solution can be tracked.
- In VPC service, create a VPC with “VPC and more” option.
- Enter
ssis-demo
in Name tag auto-generation field. - Enter
10.201.0.0/22
in IPv4 CIDR block field. - Keep all other settings as default and click on “Create VPC” button at the bottom.
- Create a bucket named
dewey-ssis-demo
. - In IAM service, create a policy named
ssis-demo-s3-access-policy
with the following permission that allows access to the bucket.
{
"Version": "2012-10-17",
"Statement": [
{
"Effect": "Allow",
"Action": "s3:ListAllMyBuckets",
"Resource": "*"
},
{
"Effect": "Allow",
"Action": [
"s3:ListBucket",
"s3:GetBucketACL",
"s3:GetBucketLocation"
],
"Resource": "arn:aws:s3:::dewey-ssis-demo"
},
{
"Effect": "Allow",
"Action": [
"s3:GetObject",
"s3:PutObject",
"s3:ListMultipartUploadParts",
"s3:AbortMultipartUpload"
],
"Resource": "arn:aws:s3:::dewey-ssis-demo/*"
}
]
}
- Navigate to IAM -> Roles and click "Create role".
- Keep "AWS service" as Trusted entity type.
- Select
RDS
from the Service or use case dropdown. - Select
RDS - Add Role to Database
and click "Next". - In the Add permissions page, search and select
ssis-demo-s3-access-policy
and click "Next". - Enter
ssis-demo-s3-access-role
in Role name field and click "Create role".
- Navigate to Directory Service -> Active Directory -> Directories and click "Set up directory".
- Keep Directory types as
AWS Managed Microsoft AD
and click "Next". - Select
Standard Edition
. - Enter
ssisdemo.com
in Directory DNS name field. - Leave Directory NetBIOS name blank.
- Enter
<AD admin password>
in Admin password and Confirm password field and click "Next". - In VPC and subnets page, select the VPC created in previous step.
- Choose the two private subnets in the selected VPC in Subnets field and click "Next".
- Confirm all the details in the next page and click "Create directory".
- Wait for 45 minutes.
- Navigate to EC2 -> Instances and click "Launch instance".
- Enter
ssis-demo-visual-studio
in Name field. - Select
Microsoft Windows Server 2019
Base as AMI. - Select appropriate instance size. Minimum
t3.large
recommended. - Create a new key pair named
ssis-demo-key-pair
and save the downloaded key. - Edit Network settings and choose
ssis-demo-vpc
in the VPC dropdown. - In the subnet dropdown, select one of the public subnets.
- Set Auto-assign public IP to
Enable
. - Enter
ssis-demo-ec2-sg
in Security group name field. - Expand Advanced details section.
- Select
ssisdemo.com
in Domain join directory field. - Click
Create new IAM profile
link to create a IAM role to allow the EC2 to join the domain. - Click
Create role
in the Roles page. - Select
EC2
as use case and click "Next". - Select both permission
AmazonSSMManagedInstanceCore
andAmazonSSMDirectoryServiceAccess
and click "Next". - Enter
ssis-demo-ec2-instance-profile
in Role name field. - Click "Create role" and return to EC2 Launch Instance page.
- Refresh IAM instance profile options and select the created role.
- Click "Launch instance" and wait for 10 minutes.
- RDP into the instance using username
ssisdemo.com\Admin
and<AD admin password>
. - Install Active Directory administration tools using the following PowerShell command.
Install-WindowsFeature RSAT-ADDS
- Once the installation finishes, launch "Active Directory Users and Computers" from Windows Start menu.
- Navigate to the Users page under the created domain.
- Install SQL Server Management Studio 19.2.
- Install Visual Studio Community 2022 with SQL Server Data Tool and SSIS extension.
- Navigate to RDS -> Subnet groups page and click on "Create DB subnet".
- Enter
ssis-demo-subnet-group
in the Name field. - Enter a description for the subnet group.
- Choose
ssis-demo-vpc
in the VPC dropdown. - Select two availability zones that match the created subnets in previous step.
- Select the two private subnets created before (refer to the subnet page in VPC service to get subnet IDs).
- Click on "Create".
- Navigate to RDS -> Parameter groups and click "Create parameter group".
- Select
sqlserver-se-15.0
from Parameter group family dropdown. - Enter
ssis-demo-sql-param-group
in the Group Name field. - Enter any description in the Description field and click "Create".
- Click on the parameter group just created and click "Edit".
- Search for
clr
in the search box. - Change the value for "clr enabled" to
1
and save changes.
- Navigate to RDS -> Option groups and click "Create group".
- Enter
ssis-demo-sql-option-group
in the Name field. - Enter any description in the Description field.
- Choose
sqlserver-se
from the Engine dropdown. - Choose
15.00
from the Major Engine Version dropdown and click "Create". - Select the option group just created and click "Add option".
- Select
SSIS
from Option name dropdown. - Select
Immediately
in Scheduling dropdown and click "Add option".
- Navigate to RDS -> Databases and click on "Create database".
- Keep creation method as "Standard create".
- Select
Microsoft SQL Server
in Engine options. - Change Edition to
SQL Server Standard Edition
. - Keep "SQL Server 2019 15.00.4345.5.v1" as Engine Version.
- Choose
Dev/Test
in Templates (use "Production" if it's for production environment). - Enter
ssis-demo-database
in DB instance identifier field. - Enter
<password>
in Master password and Confirm master password fields. - Choose appropriate DB instance class (e.g.
db.m5.large
) for the designated workload. - Enter
appropriate size
in the Allocated storage field. - Select
Connect to an EC2 compute resource
in Compute resource field. - Make sure the EC2 instance created in previous step is on before continuing.
- Choose the EC2 instance created in previous step.
- Choose the VPC created in previous step in the VPC dropdown.
- In DB subnet group, select
Choose existing
and select the subnet group created in previous step. - In VPC security group (firewall), select
Create new
. - Enter
ssis-demo-vpc-rds-sg
in New VPC security group name field. - Tick the checkbox "Enable Microsoft SQL Server Windows authentication".
- Leave Windows authentication type as "AWS Managed Microsoft Active Directory".
- Click "Browse Directory" and choose the domain created in previous step.
- Expand Additional configuration.
- Select the parameter group created in previous step in the Parameter group dropdown.
- Select the option group created in previous step in the Option group dropdown.
- Note: for existing RDS instance, modify the instance to change the parameter group and option group.
- Click "Create database" and wait for 15 minutes.
- Click on the RDS instance just created and scroll down to Manage IAM roles section.
- Select the S3 access role created before in Add IAM roles to this instance field.
- Select
S3_INTEGRATION
in the Feature dropdown and click "Add role". - Wait for 30 seconds or until the status of the RDS instance becomes Available again.
- RDP into the EC2 instance created in previous step and launch SQL Server Management Studio.
- Connect to the RDS using master account via SQL Authentication.
- Execute the following SQL script to create SQL login for the domain admin account.
CREATE DATABASE SSISDemo
GO
USE [master]
GO
CREATE LOGIN [SSISDEMO\Admin] FROM WINDOWS WITH DEFAULT_DATABASE=[SSISDemo]
GO
USE [SSISDemo]
GO
CREATE USER [SSISDEMO\Admin] FOR LOGIN [SSISDEMO\Admin]
GO
ALTER ROLE [db_owner] ADD MEMBER [SSISDEMO\Admin]
GO
USE [SSISDB]
GO
CREATE USER [SSISDEMO\Admin] FOR LOGIN [SSISDEMO\Admin]
GO
ALTER ROLE [ssis_admin] ADD MEMBER [SSISDEMO\Admin]
GO
ALTER ROLE [ssis_logreader] ADD MEMBER [SSISDEMO\Admin]
GO
- Execute the following SQL scripts to set up permissions for SQL Server Agent.
USE [msdb]
GO
CREATE USER [ssisdemo\Admin] FOR LOGIN [ssisdemo\Admin]
GRANT EXEC ON msdb.dbo.rds_msbi_task TO [ssisdemo\Admin] with grant option
GRANT SELECT ON msdb.dbo.rds_fn_task_status TO [ssisdemo\Admin] with grant option
GRANT EXEC ON msdb.dbo.rds_cancel_task TO [ssisdemo\Admin] with grant option
GRANT EXEC ON msdb.dbo.rds_download_from_s3 TO [ssisdemo\Admin] with grant option
GRANT EXEC ON msdb.dbo.rds_upload_to_s3 TO [ssisdemo\Admin] with grant option
GRANT EXEC ON msdb.dbo.rds_delete_from_filesystem TO [ssisdemo\Admin] with grant option
GRANT EXEC ON msdb.dbo.rds_gather_file_details TO [ssisdemo\Admin] with grant option
GRANT EXEC ON msdb.dbo.sp_add_proxy TO [ssisdemo\Admin] with grant option
GRANT EXEC ON msdb.dbo.sp_update_proxy TO [ssisdemo\Admin] with grant option
GRANT EXEC ON msdb.dbo.sp_grant_login_to_proxy TO [ssisdemo\Admin] with grant option
GRANT EXEC ON msdb.dbo.sp_revoke_login_from_proxy TO [ssisdemo\Admin] with grant option
GRANT EXEC ON msdb.dbo.sp_delete_proxy TO [ssisdemo\Admin] with grant option
GRANT EXEC ON msdb.dbo.sp_enum_login_for_proxy to [ssisdemo\Admin] with grant option
GRANT EXEC ON msdb.dbo.sp_enum_proxy_for_subsystem TO [ssisdemo\Admin] with grant option
GRANT EXEC ON msdb.dbo.rds_sqlagent_proxy TO [ssisdemo\Admin] WITH GRANT OPTION
ALTER ROLE [SQLAgentUserRole] ADD MEMBER [ssisdemo\Admin]
GO
USE [master]
GO
GRANT ALTER ANY CREDENTIAL TO [ssisdemo\Admin]
GO
- Create a test table in
SSISDemo
database.
USE SSISDemo
GO
CREATE TABLE Test (TestDate DATETIME)
GO
- RDP into the EC2 instance and create a simple SSIS project that writes
SELECT GETDATE()
into the test table. - Use
Microsoft OLD DB Provider for SQL Server
as Provider for the DB connection in SSIS and usessis-demo-databsae.ssisdemo.com
as server name. Note that the format of the server name is very important. - For the Data Flow Task, change the location for BLOBTempStoragePath and BufferTempStoragePath to a file inside the D:\S3\ folder. Note that the backslash at the end of the S3 is very important.
- Set ProtectionLevel to
DontSaveSensitive
, target version toSQL Server 2019
and build the project. - Go to the SSIS project folder and locate the .ispac file. Example path:
C:\Users\Admin\source\repos\SSISDemo\bin\Development
- Upload the file into the S3 bucket created before.
- In SQL Server Management Studio, connect to the RDS instance using admin account via SQL authentication and execute the following SQL script.
exec msdb.dbo.rds_download_from_s3
@s3_arn_of_file='arn:aws:s3:::dewey-ssis-demo/SSISDemo.ispac' ,
@rds_file_path='D:\S3\SSISDemo.ispac' ,
@overwrite_file=1
- Execute the following SQL script to monitor the status of the download task until it finishes.
-- Get task id from the previous SQL result.
SELECT * FROM msdb.dbo.rds_fn_task_status(NULL,<task_id>)
10. Execute the following SQL script to deploy the SSIS project to SSIS catalog.
exec msdb.dbo.rds_msbi_task
@task_type='SSIS_DEPLOY_PROJECT',
@file_path='d:\S3\SSISDemo.ispac',
@folder_name='Test',
@project_name='SSISDemo';
- Use the same SQL in step 9 to monitor the status of the deployment and wait until it finishes.
- Verify that the SSIS project is deployed successfully by going to SSIS catalog in SSMS.
- Right click on the package and manually execute it.
- Delete the downloaded file.
exec msdb.dbo.rds_delete_from_filesystem
@rds_file_path='D:\S3\SSISDemo.ispac';
- Execute the following SQL script to set up SQL Server Agent permissions.
USE [master]
GO
CREATE CREDENTIAL [SSIS_Credential] WITH IDENTITY = N'ssisdemo\Admin', SECRET = N'<domain admin password>'
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_add_proxy @proxy_name=N'SSIS_Demo_Proxy',@credential_name=N'SSIS_Credential',@description=N''
EXEC msdb.dbo.sp_grant_login_to_proxy @proxy_name=N'SSIS_Demo_Proxy',@login_name=N'ssisdemo\Admin'
EXEC msdb.dbo.rds_sqlagent_proxy @task_type='GRANT_SUBSYSTEM_ACCESS',@proxy_name='SSIS_Demo_Proxy',@proxy_subsystem='SSIS'
GO