Understand SSIS Package Execute

SSIS and Import/Export
1

Hi All,

I want to understand something and need your advice,

Here is my question, Let say Two Users Users1 & Users2

User1 Call SSIS Package from C# code and SSIS Package ( 1st Step Truncate Staging Table, 2nd Step read data from Excel and insert into Staging SQL Table, 3rd Step Staging to Production)

Now

User1 Call Same SSIS package from C# code and SSIS Package (1st Step Truncate Staging Table, 2nd Step read data from Excel and insert into Staging SQL Table, 3rd Step Staging to Production)

My question is, Is there any conflict between User1 & User2 to calling same SSIS Package at the same time?

My Understanding was whoever the call first, second will execute when first finished.

Please explain to me how this will work and how can I handle this kind of situation.

Thanks in advance.

2

Actually no your understanding is not correct. Both packages will run concurrently and will interfere with each other. How do you handle this situation? Well you need to make sure that they aren't called while it's already running (use a SQL Agent job for instance) or rewrite the package so that it can handle this (staging table would be user-specific for instance).

3

Thank You for your reply. I am kinda confused here. SSIS Package is calling from Front End (.Net). Any user(s) can call this SSIS Package. Could you please advise or comment how can I solve this kind of situation?

Thanks in Advance.

4

I'd solve it by having the SSIS package be executed inside a SQL Agent job. The application would simply start the job. SQL Agent doesn't allow the job to be started when it's already running. The application could query if it's already running or not.

Otherwise, I am sure there are SSIS logs in a table somewhere that would show if it's currently executing. The application would just query for it.

1 Like