Running SSIS for the first time

Hello,

If my SSIS runs every day, how can I "know" that I'm running it for the first time?

I need an SQL Select1 statement to execute the first day that my flow starts (to get all the old data) and after that SQL Select2 statement should always execute (to get the latest data).

How is this possable?

Thank you.

You could :

1. Run a SQL Task with SQL Statement = conditional expression on a flag value,

2. Set the true Output=Select2 and the false output=Select1

3. Get your SQL select1 to also update a flag to true in the database

HTH

Kar

|||Your are better having a control table where you track the load history. Then the packages will check on that table what is the next batch that requires to be processed.|||

Thanks for your replies.

Is it possable to have a bool flag (fetchall = true/false) that is set in the beginning when SSIS is first started?

Is it possable to do this without having to write to a database?

Thank you.

|||

Sure,

Taht would be easy to implement. You can create a boolean variable in the package and then assign its value via command line when executing the package (/SET option of DTEXEC command). Then you can build/change the where clause of the source components based on the value of that variable.

Don't forget to provide an intial value to the variable as the designer requires it for validation prupose.

|||

"Run a SQL Task with SQL Statement = conditional expression on a flag value"

How is this done? Can you please give me an example?

Thank you.

|||

Even if I use a bool value that is stored in a database, how do I set it back to 0 when the SSIS service is stopped?

Would you recommend keeping the bool value in a database or storing it as a variable in SSIS?

Thank you.

|||

You could put the whole query in a variable, let's call it MyQuery, then use an expression in MyQuery variable to actually build the query at run time based on the other boolean variable.

You would need to figure out how to control and maintain the boolean varaible. I sgugested to build a table to track the load history; but that is up to you.

|||Rafael Salas:

"I suggested to build a table to track the load history"

I'm sorry, but can you please explain this for me how it would solve my problem?

My SSIS is supposed to run every day as a scheduled task.

==Start SSIS process==

1day: Send all the old data (1000 rows)

2day: Send 1 row

3day: Send 1 row

..

.

==Stop SSIS process==

Since I'm new to SSIS, I'm still very confused how to implement this.

What would be the most simple solution?

Thanks a lot!!

|||

Rafael Salas:

"Your are better having a control table where you track the load history. Then the packages will check on that table what is the next batch that requires to be processed. "

Lets say I do this and log everything I do in a table.

==Start SSIS process==

1. 1000 rows

2. 1 row

3. 1 row

4. 1 row

..

.

==Stop SSIS process==

==Start SSIS process==

How can I know now what to do (get 1000 or 1 row) by looking at the table above that I've logged?

Maby Im misunderstanding

Thank you.

|||

I'm guessing you have some sort of WHERE clause on your select to determine what rows to retrieve, perhaps a modified date. Something like "WHERE modified_date >= @.last_date".

You'd store the maximum retrieved value for modified_date in a control table. Each time the package runs, you'd retrieve the last modified_date value from the control table, and use that for the @.last_date parameter.

|||

That is right John.

What I am suggestion is to log the latest date or ID you loaded in the previous execution; then the subsequent execution will use that value to filter the rows (in a where clause) and pick only 'new' rows.

Perhaps, I am not understanding your requirement. What would be the difference between the 'first run' and the following ones?

|||

Thank you for your patience

As I understand it, SSIS can be configured as a scheduled task.

That means that, f.ex. if I want my SSIS flow to be run at 18:00 every day, my flow will be executed at that time day after day.

When the database guys setup my SSIS flow to be run as a schedules task, Im guessing they pick my flow and say "start". When that happens, the first day at 18:00 I want to send all my old data (1000rows).

However, the next day at 18:00 (and the next, and the next...) I only want to send an update (1row).

If the database guy "stops" my scheduled task (SQL went down for some reasons) and then "starts" my flow again, I need to send all my old data the first day at 18:00 (1000rows), then the next day at 18:00 (and the day after that...) only an update (1row) etc...

Im still very puzzled how to do this.

If you have any suggestions, please let me know.

Thanks very much!!

|||

I think that I still really don't understand what you are attempting to accomplish, because this scenario doesn't make much sense to me. If you've already moved your 1000 old rows, why does it matter if the scheduled task is restarted? The rows were already moved.

However, assuming that you really do want to reset everything because of the scheduled task being stopped, I don't see an easy way to handle it in SSIS. SSIS has no way of knowing if the the scheduled task was stopped or not. You'd have to do something on the SQL Server database engine side.

Again, you might want to better explain the reason why you are trying to do this, as that might help us to come up with a better solution.

|||

I agree...Please provide a better explanation of your scenario.

Anyway, you could have am Execute SQL Task at the begining of the dataflow to check whatever you want and then based on that result decide what kind of process it will be run (Incremental, Vs initial).

sql