October 22, 2010 at 2:51 am
Hello
I'm using SSIS 2008 and I'm a bit new at using SSIS (haven't used in 18-20 months)
I got the following scenario (I changed the name of the databases to make it easier to explain)
2 databases:
Machines => Keeps all the information about machines and sensors on these machines.
Machines_DW => Stores data send by sensors (For each machine there is a table, eg M_0001)
A 3 DB I created to keep track of sensor recording of the last month (Sliding window needed)
I got a table MRegistration, it keeps the machine id, the sensor id, the time of the sensor recording and the value its passed.
A table RConfigTable is used to keep track of most recent sensor recordings(So that we can ignore any new sensor recording that are older then the most recent one).
So what does the SSIS package need to do.
1)Delete all rows in MRegistration that are older then 1 month
2)Build a dynamic sql to get the data from the Machines_DW from the past month for each individual Machine and only new records.
3)Insert the new records.
Ok this all works fine, on to the read problem. I need to keep track also on how many records are deleted or added. And I need to custom log these.
I based this on the explanation on following blogpost:
I use rowcounts in dataflow task to get the amount of records affected and store these in user variables.
And it works, but I'm not 100% happy with it.
According to my boss there is an object(or way) to get the amount of records affected by a dataflow task, but I can't seem to find it.
Any help would be appriciated
October 23, 2010 at 12:08 pm
If there is such an object (that can tell you how many rows are affected), I would like to hear about it. It would be very useful.
If you keep audit columns (such as creation date), the number of rows inserted or deleted are pretty easy to determine with simple T-SQL statements.
Rows deleted:
SELECT COUNT(1) FROM myTable before you delete anything. Store the result in a variable. Do the same after you deleted the rows. Substract one from the other and you got the number of rows deleted.
Rows inserted:
Get the latest creation_date of your table and store it in a variable @Max_CreationDate. Insert your rows.
SELECT COUNT(1) FROM myTable WHERE creation_date > @Max_CreationDate
Be careful with datetime variables as SSIS has the crappy habbit of truncating it (SSIS datetime <> SQL Server datetime).
Or, if you use a lot of T-SQL, you can just use the OUTPUT statement.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply