Showing posts with label job. Show all posts
Showing posts with label job. Show all posts

Wednesday, March 7, 2012

Another Date Question

There is no "Last business day of the month" option in SQL
Server job schedular. What is the best way to achive
this ?. Is there any code out there to get this ?
Thanks.
Todd wrote:
> There is no "Last business day of the month" option in SQL
> Server job schedular. What is the best way to achive
> this ?. Is there any code out there to get this ?
> Thanks.
Calculate the first day of the next month by adding a month to the
current month, using Day 1, and using the year as it returned from the
add of the month. Then dateadd a -1 day and you are on the last day of
the current month. In a loop, check the day of the week using
datepart(dw,...) and if Sat or Sun keep subtracting one day until you
find the correct day.
A calendar table would also be useful here as it could be used to hold
holidays as well.
David Gugick
Imceda Software
www.imceda.com
|||Are holidays considered a business day?
Jeff
"Todd" <anonymous@.discussions.microsoft.com> wrote in message
news:0ed701c4bab8$33015290$a501280a@.phx.gbl...
> There is no "Last business day of the month" option in SQL
> Server job schedular. What is the best way to achive
> this ?. Is there any code out there to get this ?
> Thanks.
|||http://www.aspfaq.com/show.asp?id=2519
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Todd" <anonymous@.discussions.microsoft.com> wrote in message
news:0ed701c4bab8$33015290$a501280a@.phx.gbl...
> There is no "Last business day of the month" option in SQL
> Server job schedular. What is the best way to achive
> this ?. Is there any code out there to get this ?
> Thanks.

Another Date Question

There is no "Last business day of the month" option in SQL
Server job schedular. What is the best way to achive
this '. Is there any code out there to get this '
Thanks.Todd wrote:
> There is no "Last business day of the month" option in SQL
> Server job schedular. What is the best way to achive
> this '. Is there any code out there to get this '
> Thanks.
Calculate the first day of the next month by adding a month to the
current month, using Day 1, and using the year as it returned from the
add of the month. Then dateadd a -1 day and you are on the last day of
the current month. In a loop, check the day of the week using
datepart(dw,...) and if Sat or Sun keep subtracting one day until you
find the correct day.
A calendar table would also be useful here as it could be used to hold
holidays as well.
David Gugick
Imceda Software
www.imceda.com|||Are holidays considered a business day?
Jeff
"Todd" <anonymous@.discussions.microsoft.com> wrote in message
news:0ed701c4bab8$33015290$a501280a@.phx.gbl...
> There is no "Last business day of the month" option in SQL
> Server job schedular. What is the best way to achive
> this '. Is there any code out there to get this '
> Thanks.|||http://www.aspfaq.com/show.asp?id=2519
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Todd" <anonymous@.discussions.microsoft.com> wrote in message
news:0ed701c4bab8$33015290$a501280a@.phx.gbl...
> There is no "Last business day of the month" option in SQL
> Server job schedular. What is the best way to achive
> this '. Is there any code out there to get this '
> Thanks.

Another Date Question

There is no "Last business day of the month" option in SQL
Server job schedular. What is the best way to achive
this '. Is there any code out there to get this '
Thanks.Todd wrote:
> There is no "Last business day of the month" option in SQL
> Server job schedular. What is the best way to achive
> this '. Is there any code out there to get this '
> Thanks.
Calculate the first day of the next month by adding a month to the
current month, using Day 1, and using the year as it returned from the
add of the month. Then dateadd a -1 day and you are on the last day of
the current month. In a loop, check the day of the week using
datepart(dw,...) and if Sat or Sun keep subtracting one day until you
find the correct day.
A calendar table would also be useful here as it could be used to hold
holidays as well.
David Gugick
Imceda Software
www.imceda.com|||Are holidays considered a business day?
Jeff
"Todd" <anonymous@.discussions.microsoft.com> wrote in message
news:0ed701c4bab8$33015290$a501280a@.phx.gbl...
> There is no "Last business day of the month" option in SQL
> Server job schedular. What is the best way to achive
> this '. Is there any code out there to get this '
> Thanks.|||http://www.aspfaq.com/show.asp?id=2519
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Todd" <anonymous@.discussions.microsoft.com> wrote in message
news:0ed701c4bab8$33015290$a501280a@.phx.gbl...
> There is no "Last business day of the month" option in SQL
> Server job schedular. What is the best way to achive
> this '. Is there any code out there to get this '
> Thanks.

Monday, February 13, 2012

Analysis Services Processing task: logging and error handling

I have an Analysis Services Processing Task in my SSIS package. I run the SSIS package using SQL Server job, the running of the package is a job step.

When I process manually the analysis services objects (in practise cubes) using dtexec utility I get a lot of log. In case the processing fails I get error messages that quite well describe the error. But when I run the job the only information I get in the job log is that the job step failed. I know the failure happens in the Analysis Services Processing Task.

Is there any way in SSIS to get a) the log of the Analysis Services processing or b) the error messages of the Analysis Services processing? Or should the processing be done some other way than I've been doing?

JM_F wrote:

I have an Analysis Services Processing Task in my SSIS package. I run the SSIS package using SQL Server job, the running of the package is a job step.

When I process manually the analysis services objects (in practise cubes) using dtexec utility I get a lot of log. In case the processing fails I get error messages that quite well describe the error. But when I run the job the only information I get in the job log is that the job step failed. I know the failure happens in the Analysis Services Processing Task.

Is there any way in SSIS to get a) the log of the Analysis Services processing or b) the error messages of the Analysis Services processing? Or should the processing be done some other way than I've been doing?

I recommend you take a read of this:

Scheduled packages
http://wiki.sqlis.com/default.aspx/SQLISWiki/ScheduledPackages.html

-Jamie

|||

I have the Analysis Services Processing Task in the middle of the SISS package, like this:

Task A: execute SQL
Task B: script task
Task C: Analysis Services Processing task
Task D: script task

And the job I have only contains the SSIS package.

By following the idea in the link you sent, I'd have three job steps like this:

1st step: a new SSIS package consisting of tasks A and B
2nd step: analysis services processing using CmdExec
3nd step: a new SSIS package consisting of task D

This doesn't seem to be a clean solution, since I end having three physical SSIS packages to perform a logical work of one package.

What came to my mind was to use Script Task and Analysis Management Objects (AMO) to process the Analysis Services database objects. I guess AMO library should give better means for error handling and logging than Analysis Services Processing Task.

|||

I think we may be misunderstanding each other here. The point of my earlier post was to alert you that the output when running the job using SSIS subsystem isn't very good so you should use cmdexec instead.

On another note, what sort of logging are you doing from within your package?

-Jamie

|||

Jamie Thomson wrote:

On another note, what sort of logging are you doing from within your package?

Actually I'm doing logging to an application specifig log table and currently not using SSIS package logging at all.

Sunday, February 12, 2012

Analysis Services for DBA's.

A lot of job postings these days for SQL DBA's list the requirement of
Analysis Services. Not having any experience with it I'm wondering what
exactly is meant? Of course its going to vary from company to company, but I
really have no clue what they mean in general? Is it cube design? MDX query
writing? Simply backing up? To be clear, Im asking in regards specificically
for a DBA, not a Developer.
TIA, ChrisR
Chris - not that I'm an expert at all, but I have done 2 short contracts in
AS/SQL and might share these experiences. In both I had to design the cubes
and staging database structure and manage the population of the staging
database (DTS). The determination of fact granularity and measures takes
ages and I was expected to do that. All in all this was the main part of my
work. We created some sample reports using Excel PivotTables and ProClarity
and once they were up and running they wanted to take time to design the
rest of the queries. I showed them a little MDX because it was of interest
to me and the tools seem slightly limited out of the box ("Generate" was one
particularly useful function which ProClarity couldn't do at the time) but
in my 2 roles MDX wasn't a priority and unless you are using it every day
you're unlikely to become good at it anyway IMHO. Since then I've met a guy
at a large multinational insurance firm who does all their data warehousing
using AS and he has never touched MDX at all. My guess therefore is that
cube design and population are the main skills, and for report-writing it is
important to have familiarity with the main front-end tools - (as far as I
know Cognos and ProClarity).
Cheers,
Paul Ibison

Analysis Services for DBA's.

A lot of job postings these days for SQL DBA's list the requirement of
Analysis Services. Not having any experience with it I'm wondering what
exactly is meant? Of course its going to vary from company to company, but I
really have no clue what they mean in general? Is it cube design? MDX query
writing? Simply backing up? To be clear, Im asking in regards specificically
for a DBA, not a Developer.
TIA, ChrisRChris - not that I'm an expert at all, but I have done 2 short contracts in
AS/SQL and might share these experiences. In both I had to design the cubes
and staging database structure and manage the population of the staging
database (DTS). The determination of fact granularity and measures takes
ages and I was expected to do that. All in all this was the main part of my
work. We created some sample reports using Excel PivotTables and ProClarity
and once they were up and running they wanted to take time to design the
rest of the queries. I showed them a little MDX because it was of interest
to me and the tools seem slightly limited out of the box ("Generate" was one
particularly useful function which ProClarity couldn't do at the time) but
in my 2 roles MDX wasn't a priority and unless you are using it every day
you're unlikely to become good at it anyway IMHO. Since then I've met a guy
at a large multinational insurance firm who does all their data warehousing
using AS and he has never touched MDX at all. My guess therefore is that
cube design and population are the main skills, and for report-writing it is
important to have familiarity with the main front-end tools - (as far as I
know Cognos and ProClarity).
Cheers,
Paul Ibison

Analysis Services for DBA's.

A lot of job postings these days for SQL DBA's list the requirement of
Analysis Services. Not having any experience with it I'm wondering what
exactly is meant? Of course its going to vary from company to company, but I
really have no clue what they mean in general? Is it cube design? MDX query
writing? Simply backing up? To be clear, Im asking in regards specificically
for a DBA, not a Developer.
TIA, ChrisRChris - not that I'm an expert at all, but I have done 2 short contracts in
AS/SQL and might share these experiences. In both I had to design the cubes
and staging database structure and manage the population of the staging
database (DTS). The determination of fact granularity and measures takes
ages and I was expected to do that. All in all this was the main part of my
work. We created some sample reports using Excel PivotTables and ProClarity
and once they were up and running they wanted to take time to design the
rest of the queries. I showed them a little MDX because it was of interest
to me and the tools seem slightly limited out of the box ("Generate" was one
particularly useful function which ProClarity couldn't do at the time) but
in my 2 roles MDX wasn't a priority and unless you are using it every day
you're unlikely to become good at it anyway IMHO. Since then I've met a guy
at a large multinational insurance firm who does all their data warehousing
using AS and he has never touched MDX at all. My guess therefore is that
cube design and population are the main skills, and for report-writing it is
important to have familiarity with the main front-end tools - (as far as I
know Cognos and ProClarity).
Cheers,
Paul Ibison

Analysis Services for DBAs.

A lot of job postings these days for SQL DBA's list the requirement of Analysis Services. Not having any experience with it I'm wondering what exactly is meant? Of course its going to vary from company to company, but I really have no clue what they mean in general? Is it cube design? MDX query writing? Simply backing up? To be clear, Im asking in regards specificically for a DBA, not a Developer.

TIA, ChrisRit really annoys me how microsoft seems to change the name for this every year or 2. data warehousing, OLAP, analysis services, business intelligence etc... It really throws the recruiters off and makes me have to put what I think are redundant statements on my resume. do you know how to implement a denormalized copy of your data for reporting purposes in such a such version of the product?|||Yes. But I don't know much about AS, hence the question.|||it really annoys me how microsoft seems to change the name for this every year or 2.

how else would you know it's time to upgrade?|||ANALYSIS SERVICES IS NOT DATA WAREHOUSING!

(Just thought that would pair well with me sig...)|||does that mean AS is a star schema? ;)|||Logically, no.

For instance:

COW PIES ARE NOT DATA WAREHOUSES!

This does not imply that cow pies are star schemas. Neither does it assert that data warehouses are not cow pies, which, in fact, many of the ones based upon star schemas are...|||heh, i was just kidding. a dumb joke.|||the blindman is being funny too. his tone can just be misconstrued some times.

I am glad I am not the only one breaking the super bowl sabbath. Damn last minute requirements changes. My numbers are not balancing. Missing about $375 out of 500K in my testing environment. this friggin' thing was working before I got the new requirements Friday after lunch. Going nuts. At least I am at home on the couch working on it...|||that's nothing - one of my ex-girlfriends used to be a bank teller. she once told me the tellers there used to be up or down up to 1k after a *single* day. :)|||the blindman is being funny too. his tone can just be misconstrued some times.They have yet to invent an emoticon to represent the nuances of my posts.