November 20, 2009 at 9:54 am
Hi,
I'm new to SSIS, We are in the process of converting DTS-SSIS.
We have a machine A from which we execute all the sql script(using ISQL) and DTS file (using DTSRUN) using .bat file which is unavoidable. We are not able to do the same after upgrading to SQL 2005 due to licensing issue ("Integration service" being not available wirth client installation).
Please suggest us a work around.
Approaches considered so far:
1. Schedule SQL agent job - Not possible since DTS is part of set task performed in .bat file.
2. Invoking sp_start_job - client does not want to store the SSIS on database
3. Web service - DBA is not ok with providing server machine credential
4. invoking sp_start_job, having one SSIS on SQL agent job to invoke all the other SSIS dynamically - not able to find an option to pass the parameter (dynamic package name).
Thanks in advance
MLP
SQL server recides in different machine B. We are not planning to install 'Integration service' component on machine A due to licensing issue. But we want execute all the SSIS files from machine A due to some limitation which is unavoidable.
I'm looking at executing SSIS on
November 20, 2009 at 10:09 am
Not sure this answers your question but replacement for isql would be osql and for dtsrun use dtexec in 2005.
Hope this helps,
Greg
November 20, 2009 at 11:26 am
I'm not fully understanding.. Please correct me..
You have 2 machines, one is basically a SQL server and the other is just the client tools?
You (client) doesn't want to store packges IN the SQL server, would they be ok with the file system?
On machine A is the client ?
On Machine B is SQL ?
Thoughts
1. In the end the machine with SSIS installed MUST run the SSIS package, you can't really do it any other way and still be licensed.
2. You CAN install SSIS on another machine BUT you get dinged for licensing a whole SQL Server. It is available only on Std and Enterprise. So basically at least $800 to do it this way..
3. Now if you want to start it from A ON B there are several ways..
4. Why don't you explain some of the limitations that are being imposed on you because we are working largely in the dark here..
CEWII
November 22, 2009 at 3:39 pm
Thanks for the response!
Machine B is used for SQL server and machine A is used for CRM application(Siebel) and we execute scripts from machine A since we need to refer to one of the application component of CRM application. That is the constraint for us to execute the .bat file from Machine B.
About storing SSIS package, we would like to continue to store all the SSIS as storage file (.dtsx).
I'm aware of the fact that i need to use SQLCMD in place of isql and DTEXEC in place of dtsrun going forward.
I'm looking for some help in executing some command in machine A to execute the ssis in machine B (where SSIS is installed). I wont be able to use commands like psexec or rexec since these these commands require admin login details of machine B.
I have gone through http://msdn.microsoft.com/en-us/library/ms403355.aspx. Its not really helping me
- Approach1 requires me to deploy all the package in database
- Approach2 requires login credentials of machine B
Hope my reply helps in understanding the actual issue.
MLP
November 23, 2009 at 10:20 am
Assuming machine B is SQL 2005 or higher the easiest way is to create a SQL Agent job on machine B that uses a job step that is of the Operating System type to call the .bat file. Then machine A using a linked server TO machine B could execute a command like:
EXEC machineB.msdb.dbo.sp_start_job @Jobname = 'Your job name'
No, looking back on the posts you mention needing to be able to pass parameters. This is not directly possible with the method I just referenced.
You could do something like an remote xp_cmdhsell like this:
EXEC machineB.master.dbo.xp_cmdshell 'D:\wk\YouBatFile.bat someparameterhere'
But I have to say that that is a fairly severe security issue to allow arbitrary xp_cmdshell commands over a linked server.
You could set your pacakge to read a parameter value from the database, that is not terribly difficult to seup but you would still need to use one of the previous methods to get it started running.
You could build a dtsConfig file at runtime to use but you would still need to use one of the previous methods to get it started running.
In all of these methods the location of the pacakge (SQL or file system) is not important, they work the same in these cases.
The first starting method is probably the best, and if you need to pass variables to it the database method is probably the easiest.
CEWII
November 23, 2009 at 2:31 pm
I liked your first approach. It does work in my scenario.
But I found out that DBA team is not ready to give us rights to execute the SQL agent jobs.
I guess any approach related to remote execution needs some kind of permission from DBA. So I'm left with no option.Since our DTS loads are simple txt file to table and table to file, we are planning to use bcp utility to perform the same.
If you think I still have any option left please let me know. I'm a big fan of SSIS so I would love to go for SSIS.
Thanks a lot for your timely input.
MLP
November 23, 2009 at 2:43 pm
What is their hang-up with the job? Can it be scheduled?
I have one other thought and it is kind of overkill for this but..
You could do it with service broker. You could generate a message and then their side could run the job in response to the message. that way you re not running the job directly AND the code could be limited so that it can only run that single job. It aint pretty but it will give the DBAs some control and not grant you rights to run the job directly.
CEWII
November 23, 2009 at 3:00 pm
I guess thats like an asynchronous command i.e. it runs in background.
In my case i need to run the DTS/ssis and upon successful completion execute the next step n so on..
correct me if i'm wrong ?!
November 23, 2009 at 3:07 pm
Based on what you have described of your process you need to call a bat file and wait for it to complete.. The discussion seems to be about how you call the bat file...
CEWII
November 23, 2009 at 3:36 pm
thats right. My concern is about how to execute the ssis remotely on server machine.
In my previous reply my question was, in the process of "generate a message and then on SQL server side run the job in response to the message" will there be a big delay in SQL server responding to the message? will it send a response back stating whether job was successful or not ? Do I have an option to wait for the response and execute nxt process x ?
pls let me know if my question is confusing.
November 23, 2009 at 4:00 pm
In the context of Service Broker you can do quite a bit.
Once you send the message it will likely very quickly be responded to. As far as what you get back that is entirely up to you. You can have the remote process start the job and then sit there an poll its history until it completes and then send a message back saying it completed sucessfully or failed. Service Broker allows for a great deal of latitude in this..
CEWII
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply