January 4, 2016 at 7:36 am
I have put this here as I am still learning about SSIS and have assumed I can do this using this.
So, there is a table where we need to do a daily count as the figure changes on this (due to a flag changing the number of items in it).
I would need to insert this figure plus that day's date into this new table.
I have the query to get the count out (simple count plus getdate when flag is Y), but am abit lost on how to insert it into a new table.
Columns would be Date and total only.
I have done some playing around, I have wondered about creating an SP with a temp table in it to collate the value then inserting those values into the new table. However bit confused as to which tasks I would use to achieve this.
All help is greatly appreciated!
January 4, 2016 at 7:53 am
karen.blake (1/4/2016)
I have put this here as I am still learning about SSIS and have assumed I can do this using this.So, there is a table where we need to do a daily count as the figure changes on this (due to a flag changing the number of items in it).
I would need to insert this figure plus that day's date into this new table.
I have the query to get the count out (simple count plus getdate when flag is Y), but am abit lost on how to insert it into a new table.
Columns would be Date and total only.
I have done some playing around, I have wondered about creating an SP with a temp table in it to collate the value then inserting those values into the new table. However bit confused as to which tasks I would use to achieve this.
All help is greatly appreciated!
If the tables are both in the same database, I'd do this:
1) Create a stored proc which does all the work.
2) In SSIS, add an Execute SQL task to execute the proc.
If the tables are in different databases, there are several ways. Here is one:
1) Create an integer variable in your package.
2) Create an Execute SQL task which runs against DB1 and returns a single row (the count). Configure the Execute SQL task to assign the row count to your row count variable.
3) Create another Execute SQL task which runs against your target DB. Use an Expression to build the SQL for the task:
insert tbl(TheDate,TheCount)
select cast(getdate() as date), [row count variable]
Note that there are faster ways of getting row counts than SELECT COUNT(*) from tbl. Eg
select sum(row_count)
from sys.dm_db_partition_stats
where object_id = object_id('tbl')
and ( index_id = 0
or index_id = 1
);
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
January 4, 2016 at 8:05 am
Thanks Phil, I am also wondering now if I should just run a job on the server rather than more in SSIS if that makes sense.
I think I am trying to see SSIs as more complex than it is, am booked on a course so fingers crossed I will make more headway.
Btw love your signature about your wife 😀
January 4, 2016 at 8:19 am
Thanks!
If it's a single-database process, SSIS is rarely a recommended way forward. I'd still put the code in a stored proc though, and use SQL Agent to execute that according to your desired schedule (I try to keep database code within the database, if possible – when the time comes to do any refactoring, you'll find it easier if the code is all easy to locate).
As soon as a process requires the moving of data from one DB to another, or from one platform to another (eg, flat file to DB), SSIS comes into play. Unless your name is Jeff Moden, that is 🙂
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply