hmmm .... What is the data type of variable SiteName. furthermore, how many columns are there in derived column transformation beside SiteName?
you just need an additional INT data type Variable something like rowcnt and on the row count transformation assign the result to that variable.
but as you are having problem with this you can do the following
1) Remove the Derived Column transformation from the package.
2) Link the data flow arrow the destination. SiteName column would be null in you destination make sure the table allow null for that column
remain steps are the same. hope it helps