Dynamically execute SSIS package.

Question

Post reply

Dynamically execute SSIS package.

ugh3012

SSChasing Mays

Points: 648
More actions
November 14, 2019 at 5:37 pm

#3697748

I am trying to figure out how to dynamically execute SSIS package based on file path that are stored in the table. I found some examples online, it won’t work for me for some reason. The path could be something like this \\ServerName\\SubFolder\Name.DTSX. Most examples I found online has something to do with SQL Server under location. I tried with File System, but it requires connection manager and the connection manager don’t have expression. I am not sure what options I have. Any suggestions is appreciated. I am using VS 2015
Thanks

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

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

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

Call DTExec, with the relevant parameters (full file path is one of them).

Take a look here.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

ugh3012 SSChasing Mays Points: 648 More actions · Answer 2

That is one option we cannot do at this time. Is there a way to have SSIS invoke other SSIS package dynamically?

Thanks.

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

ugh3012 wrote:

That is one option we cannot do at this time. Is there a way to have SSIS invoke other SSIS package dynamically?
Thanks.

Use an Execute Process task to call DTExec?

I'm guessing that you do not have some sort of aversion to DTExec itself, given that this is the executable which runs SSIS packages?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

frederico_fonseca SSCoach Points: 16154 More actions · Answer 4

don't know what you tried but for sure you didn't look where you should.

1 - Add new variable - @package - type string

2 - Add new Execute Package task

2.1 - Select filesystem - create new connection - enter name of an existing package.

3 - go to the properties (e.g. right click - properties which should show up on a window - not a popup window) of the connection created above - you have expressions available - change connection string to be from variable above.

steps 2 to 3 can be executed on a for each loop that populates the variable - or other means if required.

ugh3012 SSChasing Mays Points: 648 More actions · Answer 5

frederico_fonseca wrote:

don't know what you tried but for sure you didn't look where you should.
1 - Add new variable - @package - type string
2 - Add new Execute Package task
2.1 - Select filesystem - create new connection - enter name of an existing package.
3 - go to the properties (e.g. right click - properties which should show up on a window - not a popup window) of the connection created above - you have expressions available - change connection string to be from variable above.
steps 2 to 3 can be executed on a for each loop that populates the variable - or other means if required.

LOL. I feel stupid. I was not expecting the expression to be in that properties section of VS. Many thanks.

Phil Parkin SSC Guru Points: 246960 More actions · Answer 6

And I feel stupid for not having double-checked the obvious solution!

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin