Running bat files in SSIS

Folks -

Did you have any success when running bat files (Execute Process Task) from SSIS through SQL Server Agent jobs? My package will succeed when I run it from my machine, when I ask the DBA to run it manually from the Server but not when we run from a job.

The job will hang and the bat file does not seem to be executed. The executable property does evaluate to the right path and the package owner does have write/execute permissions in the folder where the bat file is located.

Everything I could find close to that is under <http://support.microsoft.com/kb/918760>. Would you shed any light on this? When I run the same bat file from a DTS through SQL Server Agent job, it will work with no issues. Any help would be very much appreciated.

Thanks.

Does the batch file have any prompts that require user input?|||

What is SQL Agent Service account running with?

Is it the same account that you are your DBA use - when running the package manually?

Because when you run the job - it runs with the credential of SQL Agent account.

|||Phil, thxs 4 your reply. No prompts that will require user input. The bat file will do a ftp. Thanks again.|||

How do I check what is SQL Agent Service account running my job? I know when I run the pkg manually it will run under my ID.

I do own the SQL Agent job in the Development environment so the DBA told me that my ID should be trying to execute the bat file (as I said before my ID has the permissions so there is something very strange happening). She said when I move to MO it will be owned by SA.

I can't go to MO if it is not working in DEV

Thanks for your reply.

|||If you are using the SSIS job step to run the package, it runs under the service account that the Agent is set up to use, not the job owner's account. Try setting up a proxy account - check books online for details.|||

Hi,

It's simple. Use an Execute Process Task.

Double Click the Task, Under Process Tab, set the values for the following properties:

1) Executable = cmd.exe

2) Arguments = /c C:\BatchFolder\MyFile.bat.

Make Sure when you run the job, The Service account that runs your job should have access to this batch file.

Thanks

Subhash Subramanyam

|||

Jwelch -

There is a proxy account set by the DBA already... When we add a step to a job, for type we do "Operating system (CmdExec)" and for run as we do "SQLAgentJobProxy".

So... Do you believe this proxy account is the one with no access to run the bat file?

Thanks.

|||

That would be my guess.

Also, is the bat file located on a local drive, network share with UNC path, or a mapped drive?

|||Network share with UNC path.|||Might try copying it locally and see if that makes a difference.|||

I don't have Integration Services installed locally. I will check with the DBA on the permissions mentioned here and I will let you guys know.

Thanks.

|||

Folks,

The feedback I had from my DBA was that the SQLAgentProxyAccount does have access to execute the bat file in question... This is proved when I run a dts through the same job and the same bat file is fired successfully. She also agreed that the Executable Property in the Execute Process Task step was evaluating correctly.

So, our work-around was to remove the bat file execution from the SSIS pkg and add it to the second step of the job. Dirty and scary if you think that the ftp might not be the last step of a whole process. In my case, thanks to God it is... But since we have lack of time right now, this is the solution we put in place.

I would say the issue is still not clarified but I will mark this as closed and if somebody in the future runs into the same problem a new thread could be opened.

Thanks to all.

Running bat files in SSIS

Folks -

Did you have any success when running bat files (Execute Process Task) from SSIS through SQL Server Agent jobs? My package will succeed when I run it from my machine, when I ask the DBA to run it manually from the Server but not when we run from a job.

The job will hang and the bat file does not seem to be executed. The executable property does evaluate to the right path and the package owner does have write/execute permissions in the folder where the bat file is located.

Everything I could find close to that is under <http://support.microsoft.com/kb/918760>. Would you shed any light on this? When I run the same bat file from a DTS through SQL Server Agent job, it will work with no issues. Any help would be very much appreciated.

Thanks.

Does the batch file have any prompts that require user input?|||

What is SQL Agent Service account running with?

Is it the same account that you are your DBA use - when running the package manually?

Because when you run the job - it runs with the credential of SQL Agent account.

|||Phil, thxs 4 your reply. No prompts that will require user input. The bat file will do a ftp. Thanks again.|||

How do I check what is SQL Agent Service account running my job? I know when I run the pkg manually it will run under my ID.

I do own the SQL Agent job in the Development environment so the DBA told me that my ID should be trying to execute the bat file (as I said before my ID has the permissions so there is something very strange happening). She said when I move to MO it will be owned by SA.

I can't go to MO if it is not working in DEV

Thanks for your reply.

|||If you are using the SSIS job step to run the package, it runs under the service account that the Agent is set up to use, not the job owner's account. Try setting up a proxy account - check books online for details.|||

Hi,

It's simple. Use an Execute Process Task.

Double Click the Task, Under Process Tab, set the values for the following properties:

1) Executable = cmd.exe

2) Arguments = /c C:\BatchFolder\MyFile.bat.

Make Sure when you run the job, The Service account that runs your job should have access to this batch file.

Thanks

Subhash Subramanyam

|||

Jwelch -

There is a proxy account set by the DBA already... When we add a step to a job, for type we do "Operating system (CmdExec)" and for run as we do "SQLAgentJobProxy".

So... Do you believe this proxy account is the one with no access to run the bat file?

Thanks.

|||

That would be my guess.

Also, is the bat file located on a local drive, network share with UNC path, or a mapped drive?

|||Network share with UNC path.|||Might try copying it locally and see if that makes a difference.|||

I don't have Integration Services installed locally. I will check with the DBA on the permissions mentioned here and I will let you guys know.

Thanks.

|||

Folks,

The feedback I had from my DBA was that the SQLAgentProxyAccount does have access to execute the bat file in question... This is proved when I run a dts through the same job and the same bat file is fired successfully. She also agreed that the Executable Property in the Execute Process Task step was evaluating correctly.

So, our work-around was to remove the bat file execution from the SSIS pkg and add it to the second step of the job. Dirty and scary if you think that the ftp might not be the last step of a whole process. In my case, thanks to God it is... But since we have lack of time right now, this is the solution we put in place.

I would say the issue is still not clarified but I will mark this as closed and if somebody in the future runs into the same problem a new thread could be opened.

Thanks to all.

Running Access XP Macro with script task

I found this and have done everything it says to do, but I can't get the script to compile. Any ideas on how to run a access macro in SSIS?

Baiscally to execute an Access Macros in SSIS package we need to Download

Microsoft.Office.Interop.Access DLL from Office XP PIAs.

Download site

http://www.microsoft.com/downloads/details.aspx?FamilyId=C41BD61E-3060-4F71-A6B4-01FEBA508E52&displaylang=en

1) Extract the Microsoft.Office.Interop.Access DLL from Oxppia.exe

2) Drag and Drop Microsoft.Office.Interop.Access DLL to Global Assembley Directory(GAC) ie: C:\WINNT\assembly for Windows 2000 -- C:\WINDOWS\assembly for ( Win Xp and Win 2003)

3) Copy paste Microsoft.Office.Interop.Access to C:\WINNT\Microsoft.NET\Framework\v2.0.50727 for Windows 2000 -- C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727 ( Win Xp and Win 2003)

4) Add DLL reference in the Script Task

5) Add the below Code

1) Create a New Project in SSIS

2) Drag and Drop Script Task

3) Copy Paste the code in script task editor

Imports Microsoft.Office.Interop.Access

Try

Dim objAccess As New Access.Application

objAccess.OpenCurrentDatabase("D:\TestMacro.mdb", False) ' Add the Access File Path

objAccess.DoCmd.RunMacro("Macro1") ' Replace Macro1 with the name of your macro

objAccess.CloseCurrentDatabase()

objAccess.Quit(Access.AcQuitOption.acQuitSaveNone)

objAccess = Nothing

Catch ex As Exception

System.Windows.Forms.MessageBox.Show(ex.ToString())

End Try

Dts.TaskResult = Dts.Results.Success

I actually figured this out. Had to reference alot more than just the access dll. Below is the final code to get it to work if anyone else wants to do something like this.

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Imports Microsoft.Office.Interop.Access

Imports ADODB

Imports dao

Imports mscomctl

Imports msdatasrc

Imports stdole

Imports Microsoft.Office.Interop

Imports Microsoft.Office.Interop.OWC

PublicClass ScriptMain

' The execution engine calls this method when the task executes.

' To access the object model, use the Dts object. Connections, variables, events,

' and logging features are available as static members of the Dts class.

' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.

'

' To open Code and Text Editor Help, press F1.

' To open Object Browser, press Ctrl+Alt+J.

PublicSub Main()

'

Try

Dim objAccess AsNew Access.Application

objAccess.OpenCurrentDatabase("D:MyDB.mdb", False) ' Add the Access File Path

objAccess.DoCmd.RunMacro("Macro1") 'Add your Macro name

objAccess.CloseCurrentDatabase()

objAccess.Quit(Access.AcQuitOption.acQuitSaveNone)

objAccess = Nothing

Catch ex As Exception

System.Windows.Forms.MessageBox.Show(ex.ToString())

EndTry

Dts.TaskResult = Dts.Results.Success

EndSub

EndClass

Running a SSIS package remotely on the SQL Server

We have a package (which uses SQL Server Destination Task) imported in the SQL Server. Is it possible to invoke this SSIS package from a remote App Server through C# .NET.

We would like to execute the SSIS package in the SQL Server itself and pass on the results of execution to the calling C#.NET application via the output variables declared in the SSIS package.

Thanks,
Loonysan

You can do this with relatively few lines of code from either a remote component or a Web service, by using one of the Load... methods of the Application class, then calling the Execute method of the package.

-Doug

|||

If i have to write a remote component (may be a Windows Service) - then I need to host that in SQL Server. Is there any workaround without hosting any such remote compoenents in the SQL Server.

Also in some other posts - Someone has suggested the use of SQL Jobs to run SSIS packages remotely. I cannot use that approach also because I need to get some output values from the SSIS package variables. Also I am planning to run multiple instances of the same SSIS package in parallel (with different input parameters) - With the help of SQL Jobs - Running multiple instances in parallel isn't possible.

Thanks,
Loonysan

|||

I'm not sure what you mean by hosting a remote component IN SQL Server. If you mean a CLR stored procedure, unfortunately it's not possible to create a CLR stored procedure (or UDF, etc.) that uses the Integration Services API.

If you mean host a component ON the server where SQL Server is running, then you could configure that component as a remote component or a Web service that can be invoked remotely.

Running a Script in DTS

I'm new to DTS.
The task that I want to do is that I want to run a SQL Script from DTS. I know that there is Execute SQL Task for the same purpose.

But my problem is that the Script that i have to run will be in a file and I have to first read the file in DTS.then execute the contents of the file.

The script file may contain :
create database SOMEDB

create table Table1 ( Col1 varchar(10), col2 int)

I tried to use the DTS file handling but when i try to create a FileSystemObject it gives me the error that it cannot create FileSystemObject. And even if i am able to read the File using the FileSystemObject how would I run what I have read.

Can you help me out.

If this is a DTS question (which I think it is) then you are in the wrong place. Hop over to microsoft.public.sqlserver.dts

This is an SSIS forum. SSIS is the successor to DTS.

If this is a question about SSIS then what you want can easily be achieved using the Execute SQL Task which is able to take the statement/script that is going to execute from a file. Just change the SQLSourceType property to "File connection".

-Jamie

Run package performance

i made a packge with sql task

this task execute stored procedure contains cursor

it take alot of time (arround 1 houre ), want increase the performance

note: sql2005 and sp2

please help

You might want to ask your question in the Transact-SQL forum and while there, provide the details of your stored procedure to them. I don't believe this to be an SSIS issue -- not yet anyway.|||The performance is not related to SSIS at all. Well, probably not.

How long does it take to run if you execute it through MSSMS?
if you have a cursor in the proc, this is most probably your problem and the code contain within it. SQL is not a row based language.

Use the SQL group for help on performance of the procedure.

Run IN PROCESS

Sorry but how do you configure to run IN PROCESS. My task runs like this in a BAT file

dtexec /f "\\ssssagemet\xteurs\Admin\FC\Test\DTSX\Test_Alex\Rename_file_alex.dtsx"

Thanks,

aL.

What is the issue, exactly?

Run DDL Task failed when I try to BeginTransaction in it

Just try this:

1.Create a AS Connection Manager in a package, put the RetainSameConnection property into True.

2.Drag a Run DDL Task, and use the Connection in step1.

3.write the DDL query as this:

<Command>
<BeginTransaction />
</Command>

4.Run the package.

Result: It said a mistake like this:

Error: the element Command at 7 row, 38 column(Namespace urn:schemas-microsoft-com:xml-analysis) can not be displayed under Envelope/Body/Execute/Command.

Please help me, what's the hell of this?

Thanks.

Help,Help!

Thanks!

|||

Help, need help!

|||Help! Really need your help!!!|||

Can you provide some more information? what have you tried to debug.

did you try here?

http://www.topxml.com/MS-XML-Analysis/rn-219188_How-to-format-a-NotifyTableChange-command.aspx

what does your XML tag and attribute look like at 7 row, 38 column? can you post it?

|||

Thanks, but I dount that it's no business about the XML tag and attribute.

I think perhaps we just CANNOT BeginTransaction in a DDL task.

Please have a try like what I did, you will get the same result, I belive.

Thanks!

|||

Please save me!

Thanks, I am knocked down by this problem.

Run DDL Task failed when I try to BeginTransaction in it

Just try this:

1.Create a AS Connection Manager in a package, put the RetainSameConnection property into True.

2.Drag a Run DDL Task, and use the Connection in step1.

3.write the DDL query as this:

<Command>
<BeginTransaction />
</Command>

4.Run the package.

Result: It said a mistake like this:

Error: the element Command at 7 row, 38 column(Namespace urn:schemas-microsoft-com:xml-analysis) can not be displayed under Envelope/Body/Execute/Command.

Please help me, what's the hell of this?

Thanks.

Help,Help!

Thanks!

|||

Help, need help!

|||Help! Really need your help!!!|||

Can you provide some more information? what have you tried to debug.

did you try here?

http://www.topxml.com/MS-XML-Analysis/rn-219188_How-to-format-a-NotifyTableChange-command.aspx

what does your XML tag and attribute look like at 7 row, 38 column? can you post it?

|||

Thanks, but I dount that it's no business about the XML tag and attribute.

I think perhaps we just CANNOT BeginTransaction in a DDL task.

Please have a try like what I did, you will get the same result, I belive.

Thanks!

|||

Please save me!

Thanks, I am knocked down by this problem.

Run a task multiple times

I have a situation where I run the same taks multiple times during the execution. I would like to have one task which runs every time, instead of duplicating the task over and over 14 times in my script.

Basically, it is an audit log, which I set variables and then insert into a SQL table the variables.

I would like to do this:

Task1 Success--> Set Vars --Success--> Log
|
Task2 Success--> Set Vars --Success-| (do the Log task again)
|
Task3 -Success--> Set Vars --Success-| (do the Log task again)
|
etc

This works, however, I have to duplicate Log over and over and over. No OR does not work, because it still only executes the Log task once.

Another option I thought of, but cannot find a way to implement is: Make the Log task "disabled" with no dependencies, then in the Set Vars script, enable and execute Log and disable again.

Any ideas?I take it these are variables in SetVars are a custom variable set, since the default SSIS logging can handle many logging situations.

This looks like a case for event handlers.

Create three package scoped event handlers

OnPreExecute:
Set a package scoped variable IsFailed to false

OnTaskFailed:

Set a package scoped variable IsFailed to true provided the task is

one of the (Task1,Task2,Task3 or SetVars tasks)

OnPostExecute:
Check the package scope IsFailed variable
and log as needed (only one log implementation and instance needed)

There are scenarios were this would not work, involving parallelism of tasks, but the depiction you have would appear to be task1 followed by task2...|||It seems that you may want to look into SSIS logging capabilities and custom logging component http://www.thejoyofcode.com/SSIS_Custom_Logging_Task_for_Event_Handlers.aspx. There is also Microsoft example for it I reckon (html and smtp log provider samples)- http://msdn2.microsoft.com/en-us/library/ms365179.aspx

In case you have requirement doing it in task - I would recommend creating a custom task - http://msdn2.microsoft.com/en-us/library/ms345156.aspx. In this case of custom "Log" task - the code would not be duplicated - although you would still need to drag and drop it 14 times to designer and link it|||Ok. LOG was a bad wording. I was trying to get is how to run the same task multiple times.

Lets just say for argument the "LOG" task above is a "Send Email Task", it doesn't matter what the task does. I don't want 14 send email tasks which are exactly the same. I want ONE task and have it execute 14 times.

The best thing, would be if there was a "shortcut" mechanisim, which doesn't exist, to allow this. So I could "reference" the orginal task, even if I had to have 13 shortcuts to the original task that would be ok with me.|||

Tom Phillips wrote:

Ok. LOG was a bad wording. I was trying to get is how to run the same task multiple times.

Lets just say for argument the "LOG" task above is a "Send Email Task", it doesn't matter what the task does. I don't want 14 send email tasks which are exactly the same. I want ONE task and have it execute 14 times.

The best thing, would be if there was a "shortcut" mechanisim, which doesn't exist, to allow this. So I could "reference" the orginal task, even if I had to have 13 shortcuts to the original task that would be ok with me.

The smallest unit of reusability in SSIS (currently) is a package. hence if you want to reuse some functionality anywhere you want you should split it out into a seperate package and call it. This works quite well.

its possible that eventhandlers (as mentioned previously) could solve the problem also although without knowing more about your particular situation I can't make a call on that.

-Jamie

|||

Jamie Thomson wrote:

The smallest unit of reusability in SSIS (currently) is a package. hence if you want to reuse some functionality anywhere you want you should split it out into a seperate package and call it. This works quite well.

its possible that eventhandlers (as mentioned previously) could solve the problem also although without knowing more about your particular situation I can't make a call on that.

-Jamie

Jamie,

"The smallest unit of reusability in SSIS (currently) is a package" is the key.

Under any circimstance, I have to duplicate something, either the execute package or send email or in the above event handler example 3 TIMES 14 tasks, over and over and over.

I would like to suggest to MS they add a "shortcut task" which "references" another task. So only one ocurance of the actual task exists. Even though I would have to duplicate the "shortcut" over and over, if I make a change to the original task it would propagate to all the "shortcuts". There are some techincal details to work out on this, for example, what about expressions in the original, do they run or not, can you override them in the "shortcut"? But I think it would be doable.

In my case I have 14 tasks which are identical, and I had to change one thing in every single one of them. What happens when there are 30, or 100 of them? The shortcut would have eliminated that problem.

Thank you|||

Tom Phillips wrote:

Jamie Thomson wrote:

The smallest unit of reusability in SSIS (currently) is a package. hence if you want to reuse some functionality anywhere you want you should split it out into a seperate package and call it. This works quite well.

its possible that eventhandlers (as mentioned previously) could solve the problem also although without knowing more about your particular situation I can't make a call on that.

-Jamie

Jamie,

"The smallest unit of reusability in SSIS (currently) is a package" is the key.

Under any circimstance, I have to duplicate something, either the execute package or send email or in the above event handler example 3 TIMES 14 tasks, over and over and over.

I would like to suggest to MS they add a "shortcut task" which "references" another task. So only one ocurance of the actual task exists. Even though I would have to duplicate the "shortcut" over and over, if I make a change to the original task it would propagate to all the "shortcuts". There are some techincal details to work out on this, for example, what about expressions in the original, do they run or not, can you override them in the "shortcut"? But I think it would be doable.

In my case I have 14 tasks which are identical, and I had to change one thing in every single one of them. What happens when there are 30, or 100 of them? The shortcut would have eliminated that problem.

Thank you

Tom,

What you're talking about is reusability of tasks. This is my number one request (in a list of literally hundreds) for the next version of SSIS. Unfortunately I don't think we'll see it in SQL Server 2007/8 which is intended to be a small release after the masses in SQL Server 2005, but hopefully in the one after that.

Until then, lump all your reusable stuff into a dedicated package.

-Jamie

|||I would hope to see something before 2022.

I "think" the shortcut method would be realtively easy to do. I wonder if I could create a custom task which allowed referencing another task, basically copy the functionallity at runtime? I will have to look into it.

Thanks

Run a SSIS Package including Windows Tasks Scheduler?

Hello

As a beginner I have created some Packages to import Data to SQL Server.

The Packages are running well.

I have created a Task to run the Package on a SQL SERVER 2005 Express. The Task starts.

Then a Execute Package Wizard Picture appears on the Screen.

I have mannually to select the Connection Manager and the Connection String.

After the selection the Task runs without any Problem.

Now I like to get the Task to run without any manual intervention.

I was looking in Internet but could not find a solution.

Can someone give me a hint how to get the Task working without intervention?

Thanks for an early answer.

Regards

Chaepp

Specify dtexec.exe as executable to run in the Job definition.
See Books Online for dtexec command line parameters reference. In simplest case: "dtexec.exe /f c:\package.dtsx"|||

Hello Michael

I'ts working now. Thank you very much for your help!

Regards

Chaepp

run a Package from package

I have a DTS package that executes a procedure returns a global variable.

In the next ActiveX task (vb) I'd like to examine the variable and based on its value run other DTS packages but I do not know how to set up the code to run a DTS package from within an ActiveX script.

Any help appreciated .
SteveGo to Start > Run and type DTSRUNUI
Type in the SQL Server connection info at the bottom
Click the [...] button at the to choose the package name. Click [Advanced]
Click the [Generate] button
To the left of the [Generate] button, you will see the command to run the DTS from command line.

Run a global variable dynamically

I have a SSIS pkg with several task. I need to run a global variable dynamically.

the global variable's value is basically a bat file with two arguments (\\.....\..bat \\......\.xls \\.....\...xls)

bat file arg1 arg2

This global variable gets set from its previous task at run time. I did put in this global variable as an argument in expression and for executable i am calling the cmd.exe file. But for some reason it does not work. the command prompt comes up wait for an action from user which is wrong. Please help

Thanks

Make sure that you have a full path to the batch file.

Also, make sure you are using the "/C" flag before the path to the batch file.

Command should look like:

cmd.exe /C \\server\mybatchfile.bat arg1 arg2......|||

Thanks Phil. That worked.

Run a DTS Win32 Process Task hidden

Anyone?
I have a picky client who does not want to see the command window open when a batch file is called from DTS.
Can anyone help me in running it hidden?If you are using a batch file, then the dos window will always open up, at least for a short instant, and there is no way that you can avoid that.

To avoid lookup of commands used then use @.ECHO OFF at the beginning of the batch file.|||That's what I was afrain of.

Originally posted by Satya
If you are using a batch file, then the dos window will always open up, at least for a short instant, and there is no way that you can avoid that.

To avoid lookup of commands used then use @.ECHO OFF at the beginning of the batch file.