Another SSIS Package Validation Question

We are going to be running a package repeatedly 24/7. The same package against the same data store, filtered using a "stageFlag" so as not to read rows previously processed. We have various timing statistics and have yet to fine tune; but on the surface it appears that it takes approximately three minutes to validate and another three minutes to run. If we have no additional data on the second run it still takes three minutes to complete - to do nothing but skip rows already processed.

Is it possible to set this up to run repeatedly without the validation on each iteration?

Any ideas as to how this would be accomplished would be greatly appreciated.

David,

Try setting DelayValidation=TRUE.

-Jamie

|||I thought this only delayed validation until later - that it still had to be validated before execution. I want it to validate once and then run forever. I will check it out again; but if you've understood my request and could respond that would be great.|||

No this is not possible. We always validate before an execution because the package doesn't "know" that nothing has been changed externally.

Matt

|||

Matt,

A small question. If DelayValidation=FALSE will a task get validated twice. i.e. once at package startup and once immediately prior to the task firing? Or is it one or the other?

I only ask because I'm sure I've seen situations where setting DelayValidation=TRUE on a task causes the whole package to run quicker. Perhaps I was mistaken...

Thanks

Jamie

|||

Hi Jamie,

Setting DelayValidation to false does cause an extra validation to occur because we always validate right before we execute but if delay validation is false then we also validate it up front. You might wonder why we do this since it does slow package execution. The answer is so we catch problems before the package runs for a long time. If you had a package that took 8 hours to run then it would be very useful to catch the error in the first validation pass than to wait until 7 hours had passed before getting to the task that would fail validation, but we give the option to turn it off as needed.

Matt

|||

Dear Matt,

I also have a problem about Pre-Execution Phase that it takes very long time. (Some packages have been taken around 2 minutes, but some is less, and some package could not start execute phase, so I disable it)

Could I have any solution to solve this?

Thank you very much for your help

Nop

Another SSIS Package Validation Question

We are going to be running a package repeatedly 24/7. The same package against the same data store, filtered using a "stageFlag" so as not to read rows previously processed. We have various timing statistics and have yet to fine tune; but on the surface it appears that it takes approximately three minutes to validate and another three minutes to run. If we have no additional data on the second run it still takes three minutes to complete - to do nothing but skip rows already processed.

Is it possible to set this up to run repeatedly without the validation on each iteration?

Any ideas as to how this would be accomplished would be greatly appreciated.

David,

Try setting DelayValidation=TRUE.

-Jamie

|||I thought this only delayed validation until later - that it still had to be validated before execution. I want it to validate once and then run forever. I will check it out again; but if you've understood my request and could respond that would be great.|||

No this is not possible. We always validate before an execution because the package doesn't "know" that nothing has been changed externally.

Matt

|||

Matt,

A small question. If DelayValidation=FALSE will a task get validated twice. i.e. once at package startup and once immediately prior to the task firing? Or is it one or the other?

I only ask because I'm sure I've seen situations where setting DelayValidation=TRUE on a task causes the whole package to run quicker. Perhaps I was mistaken...

Thanks

Jamie

|||

Hi Jamie,

Setting DelayValidation to false does cause an extra validation to occur because we always validate right before we execute but if delay validation is false then we also validate it up front. You might wonder why we do this since it does slow package execution. The answer is so we catch problems before the package runs for a long time. If you had a package that took 8 hours to run then it would be very useful to catch the error in the first validation pass than to wait until 7 hours had passed before getting to the task that would fail validation, but we give the option to turn it off as needed.

Matt

|||

Dear Matt,

I also have a problem about Pre-Execution Phase that it takes very long time. (Some packages have been taken around 2 minutes, but some is less, and some package could not start execute phase, so I disable it)

Could I have any solution to solve this?

Thank you very much for your help

Nop

Another Sql 2005 Express Installation Problem

I am receiving the following message when trying to install Sql Server 2005 Express: "An installation package for the product Microsoft Sql Server Native Client cannot be found. Try the installation again using a valid copy of the installation package 'sqlncli.msi'. I have verified that the sqlncli.mis file is included in the file being extracted by the installer and I have redownloaded the installation program 3 times from the MSDN site in case the file was corrupt.

Any ideas?I was able to solve my own problem by copying the 'sqlncli.msi' file to another folder during the failed installation.

Then after the failed installation I ran the sqlncli.msi by itself and it installed the Sql Native Client on its own just fine.

Then I ran the installation again and it made it past this point fine and completed the rest of the installation successfully.

This was on a Win2000 Professional machine. I am leaving this post here just in case anyone else runs across this.|||

Thanks a lot man "Johansec".

Your solution worked for me.

Another Sql 2005 Express Installation Problem

I am receiving the following message when trying to install Sql Server 2005 Express: "An installation package for the product Microsoft Sql Server Native Client cannot be found. Try the installation again using a valid copy of the installation package 'sqlncli.msi'. I have verified that the sqlncli.mis file is included in the file being extracted by the installer and I have redownloaded the installation program 3 times from the MSDN site in case the file was corrupt.

Any ideas?I was able to solve my own problem by copying the 'sqlncli.msi' file to another folder during the failed installation.

Then after the failed installation I ran the sqlncli.msi by itself and it installed the Sql Native Client on its own just fine.

Then I ran the installation again and it made it past this point fine and completed the rest of the installation successfully.

This was on a Win2000 Professional machine. I am leaving this post here just in case anyone else runs across this.|||

Thanks a lot man "Johansec".

Your solution worked for me.

Another Date time question

I have a datetime column. This column has an index
(Primary key). I need to insert only the date part(not the
time) so that when I run my DTS package it only inserts
one date (TODAY's DATE) without the time.
How can I insert today's date with only date part ?
Thanks.This is a multi-part message in MIME format.
--=_NextPart_000_030E_01C37B89.95A72100
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Try:
convert (char (8), getdate(), 112)
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Charlie" <ckerns@.hotmail.com> wrote in message =news:446c01c37baa$8807dfa0$a601280a@.phx.gbl...
I have a datetime column. This column has an index (Primary key). I need to insert only the date part(not the time) so that when I run my DTS package it only inserts one date (TODAY's DATE) without the time.
How can I insert today's date with only date part ?
Thanks.
--=_NextPart_000_030E_01C37B89.95A72100
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Try:
convert (char (8), getdate(), 112)
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Charlie" wrote in =message news:446c01c37baa$88=07dfa0$a601280a@.phx.gbl...I have a datetime column. This column has an index (Primary key). I =need to insert only the date part(not the time) so that when I run my DTS =package it only inserts one date (TODAY's DATE) without the time. How =can I insert today's date with only date part ?Thanks.

---

--=_NextPart_000_030E_01C37B89.95A72100--|||This will do it:
SELECT CAST(CONVERT(char, CURRENT_TIMESTAMP, 112) AS datetime)
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
What hardware is your SQL Server running on?
http://vyaskn.tripod.com/poll.htm
"Charlie" <ckerns@.hotmail.com> wrote in message
news:446c01c37baa$8807dfa0$a601280a@.phx.gbl...
I have a datetime column. This column has an index
(Primary key). I need to insert only the date part(not the
time) so that when I run my DTS package it only inserts
one date (TODAY's DATE) without the time.
How can I insert today's date with only date part ?
Thanks.

Analysis services query timeout from SSIS

Hi,

I've just developed a simple integration services package which picks up a list of MDX queries from a table then runs them against our Cube using a .NET OleDb connection and saves the results to a csv file.

Unfortunately the queries frequently timeout, despite the fact that the Timeout property on the Connection Manager is set to 100000 and the Connect Timeout property is set to 600.

I have some logging on the packages and this revels that the queries will often timeout after less than 60 seconds with the error below:
<<Query>> failed with the following error: "XML for Analysis parser: The XML for Analysis request timed out before it was completed.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Looking at the properties of the connection manager, I can see that the ConnectionString which has been generated is:
Data Source=Server;Initial Catalog=CubeName;Provider=MSOLAP.3;Integrated Security=SSPI;Connect Timeout=600;Auto Synch Period=1000;Timeout=100000;

Subsequent re-running of the package will often succeed, I'm guessing this is because the Cube has cached the query result and is able to return it fast enough.

Is there an additional timeout property that I'm missing somewhere?

Your help is much appreciated!

-Stuart

Perhaps try ExternalCommandTimeout? (http://www.microsoft.com/technet/prodtechnol/sql/2005/ssasproperties.mspx)|||Chris Webb built a "cache warming" prototype using SSIS and I think he found a way around this issue, you can find the post on his blog about this at http://cwebbbi.spaces.live.com/Blog/cns!7B84B0F2C239489A!1062.entry|||

Hi,

Sorry for the delay in replying and thanks for your suggestions. It seems that Chris Webb's solution was to ignore the timeout, which was fine given that he was building a cache warmer and didn't actually need to get the results. Given that I did need the results I decided to try and build some retry logic into my SSIS package.

I had a very clear and simple idea in my mind of how this would work. I would simply add an additional flow connector to the Execute SQL task with a Failure & Expression constraint where @.RetryCount < @.MaxRetries, then I would flow into a script task to increment @.RetryCount then back into the same Execute SQL task. Unfortunately though SSIS will not allow this as this would result in looping dependency.

The somewhat contrived solution I have now come up with is to extract my MDX Query list into a .NET Dataset pointed to by an SSIS Object variable, onto which I add an additional column to keep track of the number of retires. I then loop over the dataset and in the event of a query failing, I copy the row in the dataset and add it to the bottom, incrementing RetryCount.

It seems like a crazy solution to the problem of a query timing out. Is anyone at Microsoft aware that the Execute SQL task will timeout after 30 seconds when using an ADO .NET OleDb Connection with the Analysis Services provider, regardless of what you set the timeouts to be?

-Stuart

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.

Analysis Services package in SQL2K

I'm new to Analysis Services package in SQL2K.

As data analyst I wanted to create a short presentation that would explore most advantages of using Analysis Services (Cube, datamart).

Questions:
1.Does this tool (Analysis Services) come with SQL2K?
2. Is there any light version (or client version)of Analysis Services?
3. How to start (what module I have to install , set up)
Any relevant info would be really appreciated.Thank you.

Analysis Services is the best in the market at present time. MS has the biggest market share in OLAP world. Please see the the link below for assurance. This is an independent report.

http://www.olapreport.com/market.htm

Yes Analysis Services (AS) comes free with SQL2K. If you / your organisation want to invest your money and effort into SQL Server at this point and time, I would recommend you to have a look at SQL 2005. The features and improvements to SQL2K are paramout.

You would use AS to build OLAP cubes on the server. Clients would use 3rd party products such as Pro Clarity / IA to access these cubes (to view this data, slice & dice). You can build local cubes on client if you really wanted to. Not recommended for various reasons, but nothing stopping you doing that.

Installation is very simple. Just put the CD and click on "SQL Server 2000 Components" and then select "Install Analysis Services". Please dont forget to install service pack (4 is the latest)

http://www.olapreport.com/Comment_Microsoft.htm
http://www.microsoft.com/sql/evaluation/bi/bianalysis.mspx
Thanks
Sutha