Launching and monitoring SSIS packages from a web app

Has anybody developed a ASP.Net app that interfaces to SSIS? If so, what was your experience? Any pitfalls, tips, etc? We have a requirement to launch and monitor SSIS packages via a web interface.I've got the same question...

launch report builder from C# app

Hi friends
how can i launcg report builder from a C# app ?
i know i can use following url but need to know how to execute that url to bring up the report builder
http://<localhost>/reportserver/reportbuilder/reportbuilder.application

Thanks for your help
finally i found how to do that

WebBrowser wb = new WebBrowser();
wb.Navigate("http://<localhost>/reportserver/reportbuilder/reportbuilder.application");

that works nicely.
one more question on this one ,does anybody know how to select report model also by default instead of user choosing after launching report builder ?|||

Please see this blog post:

http://blogs.msdn.com/bobmeyers/archive/2006/01/24/517083.aspx

Note the issue mentioned in the comments.

|||

Thank you very much Bob.

I've been searching for a solution for this one. Thanks again.

Last year comparable Business Day

/**
DESCRIPTION
Okay, I have an app that I need to compare information from two dates
in. The two dates are whatever date is passed to it, and its equivalent
business day from last year. For instance, today is January 10th,
2006. January 10th is the tuesday of the second w in 2006. I need to
be able to compare it the tuesday of the second FULL w in 2005 (Full
w being the catch).
So, if you give it 1/6/06, it will output 1/7/05
1/2/06 => 1/3/05
12/31/06 => 1/1/06
I have a function below that is almost there, but it runs into some
hiccups towards the end of the year. Try inputing 12/31/05, and you
will see what I mean.
That being said, here is my function. You wil have to doctor a few
lines due to word wrap.
FUNCTION
**/
CREATE FUNCTION fnGetLastYearComparableDate(@.datetime datetime)
RETURNS DATETIME
AS
BEGIN
DECLARE @.DATELY datetime,
@.WEEKTY int,
@.WEEKLY int,
@.WEEKDAYTY int,
@.WEEKDAYLY int,
@.WDSUNDAYLY datetime,
@.WDSATURDAYLY datetime,
@.BOY datetime,
@.BOLY datetime
SET @.DATELY = DATEADD("yyyy",-1,@.datetime)
SET @.BOY = CONVERT(datetime,'1/1/' +
CONVERT(varchar(4),datepart("yyyy",@.datetime)))
SET @.BOLY = CONVERT(datetime,'1/1/' +
CONVERT(varchar(4),datepart("yyyy",@.DATELY)))
SET @.WEEKTY = DATEPART("wk", @.datetime)
SET @.WEEKLY = DATEPART("wk", @.DATELY)
BEGIN
IF @.WEEKTY <> @.WEEKLY
IF @.WEEKTY > @.WEEKLY
SET @.DATELY = DATEADD("wk", (@.WEEKTY - @.WEEKLY), @.DATELY)
ELSE IF @.WEEKTY < @.WEEKLY
SET @.DATELY = DATEADD("wk", ((@.WEEKLY - @.WEEKTY) * -1), @.DATELY)
END
SET @.WDSUNDAYLY = DATEADD("dw",(DATEPART("dw",@.BOLY) - 1) * -1,@.BOLY)
SET @.WDSATURDAYLY = DATEADD("dw",((DATEPART("dw",@.BOLY) - 1) * -1 +
6),@.BOLY)
BEGIN
IF (DATEPART("yyyy",@.WDSUNDAYLY) < DATEPART("yyyy",@.WDSATURDAYLY)) OR
(@.WEEKTY <> @.WEEKLY)
BEGIN
SET @.DATELY = DATEADD("d",7,@.DATELY)
END
END
SET @.WEEKDAYTY = DATEPART("dw", @.datetime)
SET @.WEEKDAYLY = DATEPART("dw", @.DATELY)
BEGIN
IF @.WEEKDAYTY <> @.WEEKDAYLY
IF @.WEEKDAYTY > @.WEEKDAYLY
SET @.DATELY = DATEADD("dw", (@.WEEKDAYTY - @.WEEKDAYLY), @.DATELY)
ELSE IF @.WEEKDAYTY < @.WEEKDAYLY
SET @.DATELY = DATEADD("dw", ((@.WEEKDAYLY - @.WEEKDAYTY) * -1),
@.DATELY)
END
RETURN @.DATELY
ENDOn 10 Jan 2006 14:11:54 -0800, Brian Baumann wrote:

>/**
>DESCRIPTION
>Okay, I have an app that I need to compare information from two dates
>in. The two dates are whatever date is passed to it, and its equivalent
>business day from last year. For instance, today is January 10th,
>2006. January 10th is the tuesday of the second w in 2006. I need to
>be able to compare it the tuesday of the second FULL w in 2005 (Full
>w being the catch).
(snip)
Hi Brian,
I didn't take the time to disect your code. Instead of going over the
top with complex date calculations, why not simply create a Calendar
table? (See www.aspfaq.com/2519). Your problem can then be solved by a
simple join on same w, same wday, and year = year - 1.
(snip)
>I have a function below that is almost there, but it runs into some
>hiccups towards the end of the year. Try inputing 12/31/05, and you
>will see what I mean.
I didn't test it, but might this not be related to an omission in the
specification? After all, some years have a 53rd w and others don't.
Or they do, but with different wdays in it. Your spec doesn't say how
to handle that case.
Hugo Kornelis, SQL Server MVP|||Hugo Kornelis (hugo@.perFact.REMOVETHIS.info) writes:
> I didn't take the time to disect your code. Instead of going over the
> top with complex date calculations, why not simply create a Calendar
> table? (See www.aspfaq.com/2519). Your problem can then be solved by a
> simple join on same w, same wday, and year = year - 1.
I don't think so. Brian said first full w. I interpret this as
that the corresponding day to 2007-01-08 will be 2006-01-09, but
the corresponding day to 2008-01-08, will be 2007-01-01, because
in 2008 New Years Day is on a Tuesday, so the first full w starts
on 2008-01-07. (Assuming here that you ws starts on Monday.)
In the two w-numbering system, w 1 is eiher the w that
Jan 1st falls on (this is what SQL Server uses) or the w that
Jan 4th falls on (this is the ISO standard for w numbering).
Of course Brian could number the ws in his calendar table after
his own liking, but then he still left with the problem that to number
them. There are also funny things, with New Years Eve mapping back
to New Years Day the same year. It would be a very strange calendar.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Tue, 10 Jan 2006 23:30:58 +0000 (UTC), Erland Sommarskog wrote:

>Hugo Kornelis (hugo@.perFact.REMOVETHIS.info) writes:
>I don't think so.
(snip)
>Of course Brian could number the ws in his calendar table after
>his own liking,
Hi Erland,
That's what I meant. I wanted to add this to my message, but forgot
about it and clicked Send.
Thanks for the addition!
Hugo Kornelis, SQL Server MVP|||So if it is not in the first full w (starting Monday?) then it is the
Monday of the next w?
So if Tues is the day in year x, and w 1 in year x-1 is Tues, then the
day you want is the next Monday?
What about holidays that land on business days?
William Stacey [MVP]
"Brian Baumann" <ferretworks@.gmail.com> wrote in message
news:1136931114.105675.287240@.g44g2000cwa.googlegroups.com...
> /**
> DESCRIPTION
> Okay, I have an app that I need to compare information from two dates
> in. The two dates are whatever date is passed to it, and its equivalent
> business day from last year. For instance, today is January 10th,
> 2006. January 10th is the tuesday of the second w in 2006. I need to
> be able to compare it the tuesday of the second FULL w in 2005 (Full
> w being the catch).
> So, if you give it 1/6/06, it will output 1/7/05
> 1/2/06 => 1/3/05
> 12/31/06 => 1/1/06
> I have a function below that is almost there, but it runs into some
> hiccups towards the end of the year. Try inputing 12/31/05, and you
> will see what I mean.
> That being said, here is my function. You wil have to doctor a few
> lines due to word wrap.
> FUNCTION
> **/
> CREATE FUNCTION fnGetLastYearComparableDate(@.datetime datetime)
> RETURNS DATETIME
> AS
> BEGIN
> DECLARE @.DATELY datetime,
> @.WEEKTY int,
> @.WEEKLY int,
> @.WEEKDAYTY int,
> @.WEEKDAYLY int,
> @.WDSUNDAYLY datetime,
> @.WDSATURDAYLY datetime,
> @.BOY datetime,
> @.BOLY datetime
>
> SET @.DATELY = DATEADD("yyyy",-1,@.datetime)
> SET @.BOY = CONVERT(datetime,'1/1/' +
> CONVERT(varchar(4),datepart("yyyy",@.datetime)))
> SET @.BOLY = CONVERT(datetime,'1/1/' +
> CONVERT(varchar(4),datepart("yyyy",@.DATELY)))
> SET @.WEEKTY = DATEPART("wk", @.datetime)
> SET @.WEEKLY = DATEPART("wk", @.DATELY)
> BEGIN
> IF @.WEEKTY <> @.WEEKLY
> IF @.WEEKTY > @.WEEKLY
> SET @.DATELY = DATEADD("wk", (@.WEEKTY - @.WEEKLY), @.DATELY)
> ELSE IF @.WEEKTY < @.WEEKLY
> SET @.DATELY = DATEADD("wk", ((@.WEEKLY - @.WEEKTY) * -1), @.DATELY)
> END
> SET @.WDSUNDAYLY = DATEADD("dw",(DATEPART("dw",@.BOLY) - 1) * -1,@.BOLY)
> SET @.WDSATURDAYLY = DATEADD("dw",((DATEPART("dw",@.BOLY) - 1) * -1 +
> 6),@.BOLY)
>
> BEGIN
> IF (DATEPART("yyyy",@.WDSUNDAYLY) < DATEPART("yyyy",@.WDSATURDAYLY)) OR
> (@.WEEKTY <> @.WEEKLY)
> BEGIN
> SET @.DATELY = DATEADD("d",7,@.DATELY)
> END
> END
>
> SET @.WEEKDAYTY = DATEPART("dw", @.datetime)
> SET @.WEEKDAYLY = DATEPART("dw", @.DATELY)
> BEGIN
> IF @.WEEKDAYTY <> @.WEEKDAYLY
> IF @.WEEKDAYTY > @.WEEKDAYLY
> SET @.DATELY = DATEADD("dw", (@.WEEKDAYTY - @.WEEKDAYLY), @.DATELY)
> ELSE IF @.WEEKDAYTY < @.WEEKDAYLY
> SET @.DATELY = DATEADD("dw", ((@.WEEKDAYLY - @.WEEKDAYTY) * -1),
> @.DATELY)
> END
>
> RETURN @.DATELY
> END
>|||This whole approach will fail when you get to lunar/solar holidays,
like Easter. Want to see the code for Easter after the Y2K
non-leapyear? Want to see the code for Easter before the Y2K
non-leapyear?
The best way is to build a Calendar table with a coumn for "equivalent
business day from last year" values. Build a decade or two with a
spreadsheet after talking to the accounting department.|||Not sure who to respond to on this, so I will respond to my original
post and hopefully it will get to everyone.
I figured out what to do, and might I add, it is way simpler than I had
ever imagined. Kinda makes me wanna kick myself in butt.
DATEADD("wk",-52,@.DATE)
Works like a champ and hasn't failed. Always gets me day for day of a
comparable look last year.|||Brian,
One thing you will want to take a look at is what happens in the last two
days of the year. Dec 31st will return Jan 1st of the current year in a
normal year, and Jan 2nd in a leap year. Dec 30th will return Jan 1st
during leap year.
Also, no matter what day it is you will never get back Jan 1st of the
previous year. Following a leap year you wont be able to get back Jan 2nd
either.
This may be perfectly acceptable for your business need. It is something
that needs to be understood before using this approach, however.
Jusat
"Brian Baumann" <ferretworks@.gmail.com> wrote in message
news:1137162876.033640.264260@.z14g2000cwz.googlegroups.com...
> Not sure who to respond to on this, so I will respond to my original
> post and hopefully it will get to everyone.
> I figured out what to do, and might I add, it is way simpler than I had
> ever imagined. Kinda makes me wanna kick myself in butt.
> DATEADD("wk",-52,@.DATE)
> Works like a champ and hasn't failed. Always gets me day for day of a
> comparable look last year.
>

Last & First SQL aggregate functions

I'm trying to migrate an app. from Access to SQL Server, and find that Transact-SQL does not support LAST/FIRST functions. Is there any alternative to these?

Below is the Access SQL statement:

SELECT Last(tblZoneNameString.Val) AS strZoneName, tblZoneNameString.TagIndex
FROM tblZoneNameString
GROUP BY tblZoneNameString.TagIndex
HAVING (((tblZoneNameString.TagIndex)>0));

Use MIN and MAX

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

In SQL Server 2005, you can do something like this: select tblZoneNameString.Val as strZoneName, tblZoneNameString.TagIndex from ( select tblZoneNameString.Val as strZoneName, tblZoneNameString.TagIndex, rank() over (partition by tblZoneNameString.TagIndex order by ?) as rk from tblZoneNameString ) as T where rk = 1 Where I've written ? you will need to put whatever column or columns answer the question "last in order of what?". Perhaps this is something like someDateTime DESC. Steve Kass Drew University JimNolandCBI@.discussions.microsoft.com wrote:
> I'm trying to migrate an app. from Access to SQL Server, and find that
> Transact-SQL does not support LAST/FIRST functions. Is there any
> alternative to these?
>
> Below is the Access SQL statement:
>
> SELECT Last(tblZoneNameString.Val) AS strZoneName,
> tblZoneNameString.TagIndex
> FROM tblZoneNameString
> GROUP BY tblZoneNameString.TagIndex
> HAVING (((tblZoneNameString.TagIndex)>0));
>
>

Large OLTP DB design ponderings

I am in the middle of designing a large OLTP DB app that will also be expected to provide complex reporting capabilities with reasonable response times. There will be tens of thousands of entries a day.

I am torn between minimally indexing my DB and trying to achieve a balance between write speed and read speed or leaving the DB in a heap and creating a linked DB that would mirror the original DB but would be fully indexed with partitions and indexed views, etc. I will probably have a third DB that would serve only as a mirrored backup to the primary with a witness.

Does this seem a reasonable approach? Or am I going over the "overkill" edge?

Fast-response OLTP apps and complex reports don't usually mix particularly well. As a test, why don't you try loading your database with a predicted couple of years' worth of test data and seeing how your design performs?

If you have the ability to add a server dedicated to reporting at this early stage then it would be a lot easier to factor the second server into the design now than it would be to incorporate it later on.

Chris

|||

Chris,

Thanks. That is what I am beginning to do. We are about half way through the project and I was brought on board to "tune" the system.

I guess I was asking if there ids anything inherently wrong with leaving the "entry" DB in a heap and creating a "reporting" DB indexed out the wazoo. I am assuming both would run faster but there would be a lag as indexes were applied to the transaction logs as they were shipped. I can live with some latency in the data if it will make both processes (read & write) "pop".

|||

There's nothing wrong with doing that at all - you're simply optimising each copy of the database for its intended purpose.

I would keep your options open in terms of indexing on the entry table - don't forget that a Primary Key is actually an index. I don't know if your OLTP application needs to read real-time data from the entry table, if it does then you might find that a strategically placed index (or two) will actually help without significantly degrading inserts.

How are you planning to copy data from the primary OLTP database to the secondary database? If you are using Database Mirroring then you will not be able to read data from the mirrored copy of the database during normal operation (due to the database being in a recovering state), however you would be able to create a database snapshot on the mirrored copy which would allow you to read the data, but frozen at a point in time. For this reason you might want to consider using transational replication instead to maintain the report server's copy of the data.

Chris

|||

Actually I was thinking of a system that combined a primary-witness-mirror/quorum system for availability with a 2nd instance on each server having a read only copy that would be updated via transaction log shipping at short intervals.

Currently every table has a clustered index/primary key field, usually an identity field. It will probably be best to leave that in place, give an ample fill factor and rebuild the DB as part of routine (nightly) maintenance.

|||

Personally, unless you are going to be performing a lot of updates to the data, I'd leave the fill factor set at 0 (or 100) on tables where the clustered index is on an identity column - any other setting will result in wasted disk space. If you are expecting updates then a value of 80-90 should be OK. Again, it would be good to test this to find the optimal setting.

If you are going to copy the data over by either shipping the transaction logs or by mirroring, then your reporting indexes would have to exist in the primary database for them to be available on the secondary server. With replication you can choose to execute a 'post-snapshot script' that could be used to apply the indexes to the subscription (secondary) database once the initial snapshot has been applied.

Chris

|||

I just converted a client from a single database system to a 2 database system, and they were able to reap other benefits as well.

They had their reports pulling from the OLTP system, but that would interfere with the business users (slow the system down) and cause the reports to be slow.

I installed a new server, put SQL Server 2005 on it, and I import the OLTP daily. I have setup a data warehouse for their reporting needs, and now the reports are very fast, and I dont interfere with the OLTP system.

BobP

|||

Thanks Bob.

That is probably the type of system I am working toward. I think I have to have data more current than daily, but that detail can be worked out. Do you import a snapshot and then run a script to index the data or is it indexed in the OLTP DB?

|||

There are some indexes in the OLTP, but what I do is using SSIS, I transform the data to make it more report friendly.

Then I indexed the heck out of it :-D

If you need more than daily, my suggestion, and I have done this in the past, is to setup a transactional replication from the OLTP server to a staging server. Then import as many times as you want from the staging server. (The staging server can even be your reporting server, if you have enough disk space)

BobP

|||

Thanks again.....I think that is my solution as eventually we are building a BI system for them.

I'll try transactional replication to a staging server and set the frequency to allow enough time for indexes to be applied. I can run daily reports from the staging DB (heck, you can get a terabyte for $500 these days) and ship the data off to the warehouse to do all the BI stuff - that I'm still learning :-)

Thanks again Chris and Bob (I love this board!)