Execute SSIS package through Batch file

Question

Execute SSIS package through Batch file

parul2j

SSC Veteran

Points: 255
More actions
March 16, 2011 at 5:01 pm

#236996

Hi All,
I have requirement to run the SSIS through batch file and have to do the export from file to database(for 3 diff tables).
-Can i get the sample batch file that could be run on server to execute the package?
- Also wat could be the best way to implement it either single .dtsx package(for 3 diff tables) or 3 different dtsx package that could be run through the batch file?
Please provide your inputs in regards to the same.
Thanks
Parul

Viewing 8 posts - 1 through 7 (of 7 total)

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

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 1

I'm curious about the requirement, why a batch file? Does it have to call it directly or just start a SQL Agent job? What is the real requirement? And exactly what is going to start the package.

As far as can you, yes, look into DTExec. Keep in mind the batch file MUST be executed ON the SQL server not FROM. That distinction is important, some process on the server has to call the batch file.

I would probably do it as a single package with three data-flows, one for each table. This simplifies things a bit..

CEWII

Rich Mechaber SSChampion Points: 11130 More actions · Answer 2

Actually, it is handy to run the occasional SSIS package with a batch file. I have one in particular that is called daily with a SQL Agent, but I sometimes want to run it ad hoc. A tiny batch file on the server desktop does that perfectly.

See BOL: http://msdn.microsoft.com/en-us/library/ms162810%28v=SQL.90%29.aspx

Rich

Phil Parkin SSC Guru Points: 246979 More actions · Answer 3

Or you could execute the SQL Agent job from a batch file instead - something like:

osql -E -Q "msdb.dbo.sp_start_job N'MyJob'"

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 4

What I was really trying to get to was the real requirement. Here on the forums we very often get specific questions about how to do this or that. In many of these cases the developer's inexperience has led to a workable but not desireable solution. By understanding what is really needed we can better give guidance. This guidance may be an entirely seperate solution, but the ultimate goal being to educate and solve the poster's problem.

In most cases I try to standardize execution of the package, often through a job. This also minimizes what the batch file must contain and something like Phil's solution is then used to call it. However, your mileage may vary..

CEWII

manoranjan.kumar Valued Member Points: 53 More actions · Answer 5

dtexec can do it. You need to create package variables for reading the files and also for the connection.

Then in the dtexec you can pass those by passing variables using SET.

mgbp SSC Enthusiast Points: 177 More actions · Answer 6

Hi,

Please help me with the scenarios below:

A-Server contains:

1. SSIS Package (dtsx)

2. textfiles to be imported in Database (of B-Server).

3. SSIS Component installed ONLY (no BIDS, no SQLServer-engine)

B-Server

1. SQL Server -engine (with target database).

2. WITHOUT SSIS Component installed.

--------------------------------------

I need to manually execute the .dtsx in A-Server using DTEXEC.exe in the command line.

This dtsx will import/convert the textfile to the database located in B-Server.

Both servers are in 64-bit.

Is it possible with the given situation above? What are the other requirements/components needed?

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 7

mgbp (4/21/2011)
Hi,
Please help me with the scenarios below:
A-Server contains:
1. SSIS Package (dtsx)
2. textfiles to be imported in Database (of B-Server).
3. SSIS Component installed ONLY (no BIDS, no SQLServer-engine)
B-Server
1. SQL Server -engine (with target database).
2. WITHOUT SSIS Component installed.
--------------------------------------
I need to manually execute the .dtsx in A-Server using DTEXEC.exe in the command line.
This dtsx will import/convert the textfile to the database located in B-Server.
Both servers are in 64-bit.
Is it possible with the given situation above? What are the other requirements/components needed?

Same remark as in this topic:

http://www.sqlservercentral.com/Forums/Topic865414-148-2.aspx

Crossposting shatters replies and wastes peoples time.

mgbp (4/21/2011)
Hi,
Please help me with the scenarios below:
A-Server contains:
1. SSIS Package (dtsx)
2. textfiles to be imported in Database (of B-Server).
3. SSIS Component installed ONLY (no BIDS, no SQLServer-engine)
B-Server
1. SQL Server -engine (with target database).
2. WITHOUT SSIS Component installed.
--------------------------------------
I need to manually execute the .dtsx in A-Server using DTEXEC.exe in the command line.
This dtsx will import/convert the textfile to the database located in B-Server.
Both servers are in 64-bit.
Is it possible with the given situation above? What are the other requirements/components needed?

Same remark as in this topic:

http://www.sqlservercentral.com/Forums/Topic865414-148-2.aspx

Crossposting shatters replies and wastes peoples time.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP