SSIS Automate Process

Question

SSIS Automate Process

rocky_498

SSCertifiable

Points: 6532
More actions
May 28, 2017 at 11:25 pm

#326205

Hi Gurus,
I need advice.I am working on Project to Automate some manual project.
Here are the steps
1) Attach Oracle database.
2) Then Load the Data (almost 20 tables) from Oracle to SQL Staging Environment
3) Last step Load data from SQL Staging To SQL Prod
I kind of have an idea and plan to automate 2nd and 3rd steps.
My question is Is it possible to create SSIS Project to accomplish above steps? I am going
to use SSIS for 2nd and 3rd step.
Is it possible to create a batch file? when the user doubles click the batch file message or new
window pop up
Oracle Database Name =?
SQL Server =?
SQL Database =?
Then the user will enter above info. Once it's done hit SUBMIT and behind the seen.
Create Oracle DB/Load data from Oracle To SQL Staging/SQL Staging To SQL Prod.
Please, any advice would be great appreciated.
Thank You.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Andrew P SSCarpal Tunnel Points: 4649 More actions · Answer 1

Yes, it's possible to create a batch file that starts an SSIS package via the DTEXEC executable, using the command option "/Parameter" to pass the relevant information as package parameters.

You could also use the standard "Execute Package" option from SSMS (or start the DTEXECUI executable) to prompt for that information and start the package - that would look something like the below.

Andrew P.

frederico_fonseca SSCoach Points: 16154 More actions · Answer 2

By Using SSIS I presume you are aware that it is a licensed SQL Server tool that requires the computer where it is being executed to be fully licensed? meaning you need to have either Enterprise or Standard edition installed. (see https://docs.microsoft.com/en-us/sql/integration-services/integration-services-features-supported-by-the-editions-of-sql-server

Unless it is licensed and for the purpose of allowing users to freely transfer data like this it would probably be better for you to do a small C# application that asks for those details and then extracts/loads the data using normal data access components for source, and bulkloading component for the destination.

rocky_498 SSCertifiable Points: 6532 More actions · Answer 3

Guys Appreciate your thoughts.

I am not sure how can I accomplish to transfer dumps file into Oracle through SSIS and execute package through batchIs file or DTEXECUI. Is anyone know or guide me to where can I find the example that would be great.

Thank You.

tychopotts Say Hey Kid Points: 692 More actions · Answer 4

If you deploy your SSIS package to a SQL Server environment and then set up a SQL job to execute the package, you can create a batch file to run the job:

osql -S "SERVER INSTANCE" -E -Q"exec msdb.dbo.sp_start_job ' BACKUPTEST ' "

I got the code snippet from this article: http://www.sqlservercentral.com/articles/Agent+jobs/66421/