amazon web services - How do I call a stored procedure in SQL Server with Data Pipeline in ShellCommandActivity (AWS Data Pipeline)

Question

I know you can call a MySQL procedure with the script below, but is the same possible for SQL Server?

mysql --host host_url --port port_number --user username --password password --execute="CALL stored_proc_name;

I have SQL Server Express, and need to setup a procedure to be run daily. It's on RDS, and SQL Server Express doesn't have a task scheduler..

Answer 1

The following should work:

• Download the SQL Server JDBC Driver. Choose to download the tar.gz file and unzip it. Among the extracted files should be file named sqljdbc.jar. Upload this to S3. For licensing reasons, AWS is not able to distribute this file.
• Create a Data Pipeline JdbcDatabase object to refer to your SQL database. Set the jdbcDriverJarUri to your S3 location. Set the driver class name to "com.microsoft.sqlserver.jdbc.SQLServerDriver".
• Invoke the EXEC command from a Data Pipeline SqlActivity. The SqlActivity will refer to the JdbcDatabase object that you've defined.