Another Time Dimension question

I want to track sales vs. both date scheduled to be shipped and date actually shipped. Both are fields in my OLTP. I can set the SSIS program to extract data any why I choose.

Would it be best to create a fact table and 2 different dimension tables or combine the time fields in one dimension table or leave them in the fact table and let SQL Server extract the time dimension?

Thanks.

Hello! In SSAS2005(Analysis services 2005) you have something called 'Role-playing' dimensions that solves this problem. You will only need one time dimension in the starschema that you join to all dates in the fact table.

If you have these relations designed in the source system/star schema or in the data source view SSAS2005 will detect this relation and create separate time dimensions automatically when you build the cube.

HTH

Thomas Ivarsson

|||I don't want to apply one time dimension to many fact tables. I want to apply many time dimensions to one fact table. Right now my date fields are in the Fact Table.|||

I do not think that I have said that but my explanation, perhaps, was not good enough.

You use one time dimension and join the fact tables different date dimensions to the same date key in the time dimension table.

Connect the key for order date in the fact table to the date key in the time dimension. Connect the date key for invoice date in the fact table to the date key in the time dimension.

HTH

Thomas Ivarsson

|||

Ahh...that's better....I think I need to take my dates out of my fact table and create a dimTime table and then I can create the multiple relationships.

Thanks.

|||

That seems to be working but I'm a little fuzzy on the theory.

Either or both date fields may be null in my OLTP table, so I can't use either for the key field. My OLTP table has an integer key that is just a counting number.

I created the dimTime table with the same type key field. When I did my SSIS run I just copied the Fact table key field to the dimTime table key field along with the date fields.

The tables are linked but not throught a date field. SSAS is smart enough to build its aggregates on the date fields and pretty much ignore the key field? It only cares that the tables are linked somehow?

Thanks!!!!

|||

You can add a theoretical time member to the time dimension like '2099-12-31" and point fact records without time members to that time member.

There will be many more SQL Server releases until we reach that date.

HTH

/Thomas Ivarsson

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 Mysterious issue in SQL 2005 & SSIS

Explain this:

Package runs successfully from BIDS
It runs successfully in SSIS store
It runs successfully when I manually execute the Job

JOB FAILS EVERY FREAKING NIGHT

It'd be easier to explain if you provided more information. Such as an error message.

I would imagine this is something to do with the user that SQL Agent is running under.

I also can't recommend this article highly enough:

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

-Jamie

|||Well the Error message in SQL 2005 jobs is very generic (thnx MS) - like Job failed at step 1.

And the SQL Agent is running under the MyNetworkDomain/Administrator account.|||

TheViewMaster wrote:

Well the Error message in SQL 2005 jobs is very generic (thnx MS) - like Job failed at step 1.

Exactly. Which is why you should change how you call the package as explained in the article that I linked to above.

-Jamie

|||

Jamie Thomson wrote:

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

Start by changing to the Operating system (CmdExec) sub-system. You should then get the full message output.

How exactly are you suppose to change the Job subsystems?|||

TheViewMaster wrote:

Jamie Thomson wrote:

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

Start by changing to the Operating system (CmdExec) sub-system. You should then get the full message output.

How exactly are you suppose to change the Job subsystems?

When you setup the job step, select "Operating System (Cmdexec)" from the 'Type' dropdown box.

-Jamie

|||

Change the job step type from "SQL Server Integration Services Package" to "Operating System (CmdExec)". Just enter the command to run dtexec ("C:\PROGRAM FILES\Microsoft SQL Server\90\DTS\Binn\dtexec") and then add the switches. You can copy all the switches from the command line tab in your SSIS step before you change it to a cmdexec step.

Are any of your connections in your package using sql authentication by chance?

|||Message
Executed as user: MyDomain\Administrator. The process could not be created for step 1 of job 0x51E59B1986AB454C80F1D7B18F658064 (reason: The system cannot find the path specified). The step failed.

Btw - there is no DTS folder in C:\PROGRAM FILES\Microsoft SQL Server\90 directory
|||

TheViewMaster wrote:

Message
Executed as user: MyDomain\Administrator. The process could not be created for step 1 of job 0x51E59B1986AB454C80F1D7B18F658064 (reason: The system cannot find the path specified). The step failed.

Btw - there is no DTS folder in C:\PROGRAM FILES\Microsoft SQL Server\90 directory

Have you installed SSIS on the server that SQL Server Agent is running on?

-Jamie

|||Have you checked the NT Event log for any signs of an error? I often forget to check this...

If you set it to execute during the day does it run? If it runs then compare the night failure times with your tape backup schedule. I recently had a maintenance plan failure caused by the nightly tape backups.

If it is still failing then it could be a security problem of some type. I have gotten this exact generic error message from security mis-configurations.

Thanks,
Greg Van Mullem
|||

Jamie Thomson wrote:

s

Have you installed SSIS on the server that SQL Server Agent is running on?

Yes - SSIS is installed|||

TheViewMaster wrote:

Jamie Thomson wrote:

s

Have you installed SSIS on the server that SQL Server Agent is running on?

Yes - SSIS is installed

Whereabouts is it installed? Because the C:\Program Files\Microsoft SQL Server\90\DTS folder is where it gets installed to by default.

-Jamie

|||I just dont get it (I used to think myself as a moderately intelligent guy but MS has really made me feel like a complete dumbass whenever I try to start using their new products - talking about that the Vista RC2 installation just hanged on me the other night with no apparent reason) 2 jobs and both have issues running automatically:

1st - runs on BIDS, runs on SSIS, runs as Manual job, Automatic Jobs fail
2nd - runs on BIDS, runs on SSIS - jobs (manual and automatic) always fail

And in do not have that DTEXEC installed on that machine to troubleshoot it according to the article posted

Another day - another load of problems
Thanks BG for making our job so nightmarish that we can excuse the $ spent on software - otherwise normal ppl could do it, rite?|||The management of SSIS is quite non-intuitive in my opinion, there are loads of posts about job failures. They usually have to do with encrypting sensitive information using a user key or the account used to run the job not having sufficient access. You should, however, have dtexec on the server somewhere. Did you do a file search for it?|||I didt search for dtexec - nada
So im copying the DTS/Binn directory from my development pc - let's see if that will help to come closer to solving this mystery

Another Mysterious issue in SQL 2005 & SSIS

Explain this:

Package runs successfully from BIDS
It runs successfully in SSIS store
It runs successfully when I manually execute the Job

JOB FAILS EVERY FREAKING NIGHT

It'd be easier to explain if you provided more information. Such as an error message.

I would imagine this is something to do with the user that SQL Agent is running under.

I also can't recommend this article highly enough:

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

-Jamie

|||Well the Error message in SQL 2005 jobs is very generic (thnx MS) - like Job failed at step 1.

And the SQL Agent is running under the MyNetworkDomain/Administrator account.|||

TheViewMaster wrote:

Well the Error message in SQL 2005 jobs is very generic (thnx MS) - like Job failed at step 1.

Exactly. Which is why you should change how you call the package as explained in the article that I linked to above.

-Jamie

|||

Jamie Thomson wrote:

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

Start by changing to the Operating system (CmdExec) sub-system. You should then get the full message output.

How exactly are you suppose to change the Job subsystems?|||

TheViewMaster wrote:

Jamie Thomson wrote:

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

Start by changing to the Operating system (CmdExec) sub-system. You should then get the full message output.

How exactly are you suppose to change the Job subsystems?

When you setup the job step, select "Operating System (Cmdexec)" from the 'Type' dropdown box.

-Jamie

|||

Change the job step type from "SQL Server Integration Services Package" to "Operating System (CmdExec)". Just enter the command to run dtexec ("C:\PROGRAM FILES\Microsoft SQL Server\90\DTS\Binn\dtexec") and then add the switches. You can copy all the switches from the command line tab in your SSIS step before you change it to a cmdexec step.

Are any of your connections in your package using sql authentication by chance?

|||Message
Executed as user: MyDomain\Administrator. The process could not be created for step 1 of job 0x51E59B1986AB454C80F1D7B18F658064 (reason: The system cannot find the path specified). The step failed.

Btw - there is no DTS folder in C:\PROGRAM FILES\Microsoft SQL Server\90 directory
|||

TheViewMaster wrote:

Message
Executed as user: MyDomain\Administrator. The process could not be created for step 1 of job 0x51E59B1986AB454C80F1D7B18F658064 (reason: The system cannot find the path specified). The step failed.

Btw - there is no DTS folder in C:\PROGRAM FILES\Microsoft SQL Server\90 directory

Have you installed SSIS on the server that SQL Server Agent is running on?

-Jamie

|||Have you checked the NT Event log for any signs of an error? I often forget to check this...

If you set it to execute during the day does it run? If it runs then compare the night failure times with your tape backup schedule. I recently had a maintenance plan failure caused by the nightly tape backups.

If it is still failing then it could be a security problem of some type. I have gotten this exact generic error message from security mis-configurations.

Thanks,
Greg Van Mullem|||

Jamie Thomson wrote:

s

Have you installed SSIS on the server that SQL Server Agent is running on?

Yes - SSIS is installed|||

TheViewMaster wrote:

Jamie Thomson wrote:

s

Have you installed SSIS on the server that SQL Server Agent is running on?

Yes - SSIS is installed

Whereabouts is it installed? Because the C:\Program Files\Microsoft SQL Server\90\DTS folder is where it gets installed to by default.

-Jamie

|||I just dont get it (I used to think myself as a moderately intelligent guy but MS has really made me feel like a complete dumbass whenever I try to start using their new products - talking about that the Vista RC2 installation just hanged on me the other night with no apparent reason) 2 jobs and both have issues running automatically:

1st - runs on BIDS, runs on SSIS, runs as Manual job, Automatic Jobs fail
2nd - runs on BIDS, runs on SSIS - jobs (manual and automatic) always fail

And in do not have that DTEXEC installed on that machine to troubleshoot it according to the article posted

Another day - another load of problems
Thanks BG for making our job so nightmarish that we can excuse the $ spent on software - otherwise normal ppl could do it, rite?|||The management of SSIS is quite non-intuitive in my opinion, there are loads of posts about job failures. They usually have to do with encrypting sensitive information using a user key or the account used to run the job not having sufficient access. You should, however, have dtexec on the server somewhere. Did you do a file search for it?|||I didt search for dtexec - nada
So im copying the DTS/Binn directory from my development pc - let's see if that will help to come closer to solving this mystery

Another cryptic message from SQL 2005 SSIS

It reminds me of the "opearation was successful the patient died" scenario.

Compare this to the SSIS Error message:

The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

SSIS package "myPubs.dtsx" finished: Failure.

Why can't they write meaningful messages?

YOu need enable the logging for SSIS packages in order to see what was that error : http://www.databasejournal.com/features/mssql/article.php/3562406 fyi.|||Did they fix this bug finally?|||

I forgot to indicate my blog entry for this:

http://hodentek.blogspot.com/2007/03/ssis-migration-fiasco.html

|||

I was actaully trying to find an answer for the question I posted:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1393552&SiteID=1

Please forgive.

|||Moved this thread from SQL Server Tools General to the SSIS forum.

You should know that your error likely has to do with the ProtectionLevel property of the package. Right click on the control flow and select properties. What is it set to?|||

Jayaram Krishnaswamy wrote:

Did they fix this bug finally?

What bug? I don't believe there is a bug here, rather I think there is a lack of understanding of how differently SSIS operates compared to DTS.|||

Reply was not helpful.

Tried all options (somewhat unprofessional, fortunately fewer options). The same result.

Details follow:

Protection Level of package:

DontSaveSensitive: failed

Warning: 0x80019002 at myPubs: The Execution method succeeded, but the number of errors raised (4) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "myPubs.dtsx" finished: Failure.

EncryptSensitiveWithPassowrd: failed

Provided a password for the package, myPubs.dtsx.

SSIS package "myPubs.dtsx" starting.
Error: 0xC002F325 at Copy SQL Server Objects, Transfer SQL Server Objects Task: Execution failed with the following error: "Cannot apply value null to property Login: Value cannot be null..".
Task failed: Copy SQL Server Objects
Warning: 0x80019002 at myPubs: The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "myPubs.dtsx" finished: Failure.

EncryptAllwithPassword:failed
failed
SSIS package "myPubs.dtsx" starting.
Error: 0xC002F325 at Copy SQL Server Objects, Transfer SQL Server Objects Task: Execution failed with the following error: "Cannot apply value null to property Login: Value cannot be null..".
Task failed: Copy SQL Server Objects
Warning: 0x80019002 at myPubs: The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "myPubs.dtsx" finished: Failure.

EncryptAllwithUserkey:failed (ola didn't ask for a key or anything, it must have assumed some default)

SSIS package "myPubs.dtsx" starting.
Error: 0xC002F325 at Copy SQL Server Objects, Transfer SQL Server Objects Task: Execution failed with the following error: "Cannot apply value null to property Login: Value cannot be null..".
Task failed: Copy SQL Server Objects
Warning: 0x80019002 at myPubs: The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "myPubs.dtsx" finished: Failure

ServerStorage: failed
TITLE: Microsoft Visual Studio

Failure saving package.

ADDITIONAL INFORMATION:

Failed to apply package protection with error 0xC0014061 "The protection level, ServerStorage, cannot be used when saving to this destination. The system could not verify that the destination supports secure storage capability.". This error occurs when saving to Xml.
(myPubs)

Failed to apply package protection with error 0xC0014061 "The protection level, ServerStorage, cannot be used when saving to this destination. The system could not verify that the destination supports secure storage capability.". This error occurs when saving to Xml.
(myPubs)

BUTTONS:

OK

Open for suggestions

|||Try EncryptSensitiveWithUserKey and then go back in and retype in your credentials for the connections. Make sure you check the save password box.|||

Jayaram Krishnaswamy wrote:

I forgot to indicate my blog entry for this:

http://hodentek.blogspot.com/2007/03/ssis-migration-fiasco.html

You haven't understood the inherent differences between SSIS and DTS. SSIS is a much more secure environment and will not allow you to store passwords with the same freedom as DTS did. Can I politely suggest that you familiarise yourself totally with this new tool before you start to blog about it and thus post inaccurate information. You are not helping the SSIS community by doing so.

-Jamie

|||If the number of people reporting this is large surely somebody should take the responsibility of putting the matter to rest. This is probably one of the problems with proprietary software.|||

Jayaram Krishnaswamy wrote:

If the number of people reporting this is large surely somebody should take the responsibility of putting the matter to rest. This is probably one of the problems with proprietary software.

REPORTING WHAT, *exactly*?

WHAT *IS* THE PROBLEM?|||

Jayaram Krishnaswamy wrote:

If the number of people reporting this is large surely somebody should take the responsibility of putting the matter to rest. This is probably one of the problems with proprietary software.

huh?

If SSIS is proprietary software then show me some software that ISN'T proprietary.

I haven't seen anyone else on this thread reporting any problem, only you. In fact, you haven't actually articulated a problem as far as I can see. In summary, what is the prolem that you speak of?

-Jamie

|||

Phil Brammer wrote:

Try EncryptSensitiveWithUserKey and then go back in and retype in your credentials for the connections. Make sure you check the save password box.

This did not help in my case.

Jayaram, have you resolved this problem finally?

|||

Dan Miniyarov wrote:

Phil Brammer wrote:

Try EncryptSensitiveWithUserKey and then go back in and retype in your credentials for the connections. Make sure you check the save password box.

This did not help in my case.

Jayaram, have you resolved this problem finally?

I don't know what that means. What exactly is your issue? What are you trying to achieve?

Another cryptic message from SQL 2005 SSIS

It reminds me of the "opearation was successful the patient died" scenario.

Compare this to the SSIS Error message:

The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

SSIS package "myPubs.dtsx" finished: Failure.

Why can't they write meaningful messages?

YOu need enable the logging for SSIS packages in order to see what was that error : http://www.databasejournal.com/features/mssql/article.php/3562406 fyi.|||Did they fix this bug finally?|||

I forgot to indicate my blog entry for this:

http://hodentek.blogspot.com/2007/03/ssis-migration-fiasco.html

|||

I was actaully trying to find an answer for the question I posted:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1393552&SiteID=1

Please forgive.

|||Moved this thread from SQL Server Tools General to the SSIS forum.

You should know that your error likely has to do with the ProtectionLevel property of the package. Right click on the control flow and select properties. What is it set to?|||

Jayaram Krishnaswamy wrote:

Did they fix this bug finally?

What bug? I don't believe there is a bug here, rather I think there is a lack of understanding of how differently SSIS operates compared to DTS.|||

Reply was not helpful.

Tried all options (somewhat unprofessional, fortunately fewer options). The same result.

Details follow:

Protection Level of package:

DontSaveSensitive: failed

Warning: 0x80019002 at myPubs: The Execution method succeeded, but the number of errors raised (4) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "myPubs.dtsx" finished: Failure.

EncryptSensitiveWithPassowrd: failed

Provided a password for the package, myPubs.dtsx.

SSIS package "myPubs.dtsx" starting.
Error: 0xC002F325 at Copy SQL Server Objects, Transfer SQL Server Objects Task: Execution failed with the following error: "Cannot apply value null to property Login: Value cannot be null..".
Task failed: Copy SQL Server Objects
Warning: 0x80019002 at myPubs: The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "myPubs.dtsx" finished: Failure.

EncryptAllwithPassword:failed
failed
SSIS package "myPubs.dtsx" starting.
Error: 0xC002F325 at Copy SQL Server Objects, Transfer SQL Server Objects Task: Execution failed with the following error: "Cannot apply value null to property Login: Value cannot be null..".
Task failed: Copy SQL Server Objects
Warning: 0x80019002 at myPubs: The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "myPubs.dtsx" finished: Failure.

EncryptAllwithUserkey:failed (ola didn't ask for a key or anything, it must have assumed some default)

SSIS package "myPubs.dtsx" starting.
Error: 0xC002F325 at Copy SQL Server Objects, Transfer SQL Server Objects Task: Execution failed with the following error: "Cannot apply value null to property Login: Value cannot be null..".
Task failed: Copy SQL Server Objects
Warning: 0x80019002 at myPubs: The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "myPubs.dtsx" finished: Failure

ServerStorage: failed
TITLE: Microsoft Visual Studio

Failure saving package.

ADDITIONAL INFORMATION:

Failed to apply package protection with error 0xC0014061 "The protection level, ServerStorage, cannot be used when saving to this destination. The system could not verify that the destination supports secure storage capability.". This error occurs when saving to Xml.
(myPubs)

Failed to apply package protection with error 0xC0014061 "The protection level, ServerStorage, cannot be used when saving to this destination. The system could not verify that the destination supports secure storage capability.". This error occurs when saving to Xml.
(myPubs)

BUTTONS:

OK

Open for suggestions

|||Try EncryptSensitiveWithUserKey and then go back in and retype in your credentials for the connections. Make sure you check the save password box.|||

Jayaram Krishnaswamy wrote:

I forgot to indicate my blog entry for this:

http://hodentek.blogspot.com/2007/03/ssis-migration-fiasco.html

You haven't understood the inherent differences between SSIS and DTS. SSIS is a much more secure environment and will not allow you to store passwords with the same freedom as DTS did. Can I politely suggest that you familiarise yourself totally with this new tool before you start to blog about it and thus post inaccurate information. You are not helping the SSIS community by doing so.

-Jamie

|||If the number of people reporting this is large surely somebody should take the responsibility of putting the matter to rest. This is probably one of the problems with proprietary software.|||

Jayaram Krishnaswamy wrote:

If the number of people reporting this is large surely somebody should take the responsibility of putting the matter to rest. This is probably one of the problems with proprietary software.

REPORTING WHAT, *exactly*?

WHAT *IS* THE PROBLEM?|||

Jayaram Krishnaswamy wrote:

If the number of people reporting this is large surely somebody should take the responsibility of putting the matter to rest. This is probably one of the problems with proprietary software.

huh?

If SSIS is proprietary software then show me some software that ISN'T proprietary.

I haven't seen anyone else on this thread reporting any problem, only you. In fact, you haven't actually articulated a problem as far as I can see. In summary, what is the prolem that you speak of?

-Jamie

|||

Phil Brammer wrote:

Try EncryptSensitiveWithUserKey and then go back in and retype in your credentials for the connections. Make sure you check the save password box.

This did not help in my case.

Jayaram, have you resolved this problem finally?|||

Dan Miniyarov wrote:

Phil Brammer wrote:

Try EncryptSensitiveWithUserKey and then go back in and retype in your credentials for the connections. Make sure you check the save password box.

This did not help in my case.

Jayaram, have you resolved this problem finally?

I don't know what that means. What exactly is your issue? What are you trying to achieve?

Another cryptic message from SQL 2005 SSIS

It reminds me of the "opearation was successful the patient died" scenario.

Compare this to the SSIS Error message:

The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

SSIS package "myPubs.dtsx" finished: Failure.

Why can't they write meaningful messages?

YOu need enable the logging for SSIS packages in order to see what was that error : http://www.databasejournal.com/features/mssql/article.php/3562406 fyi.|||Did they fix this bug finally?|||

I forgot to indicate my blog entry for this:

http://hodentek.blogspot.com/2007/03/ssis-migration-fiasco.html

|||

I was actaully trying to find an answer for the question I posted:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1393552&SiteID=1

Please forgive.

|||Moved this thread from SQL Server Tools General to the SSIS forum.

You should know that your error likely has to do with the ProtectionLevel property of the package. Right click on the control flow and select properties. What is it set to?|||

Jayaram Krishnaswamy wrote:

Did they fix this bug finally?

What bug? I don't believe there is a bug here, rather I think there is a lack of understanding of how differently SSIS operates compared to DTS.|||

Reply was not helpful.

Tried all options (somewhat unprofessional, fortunately fewer options). The same result.

Details follow:

Protection Level of package:

DontSaveSensitive: failed

Warning: 0x80019002 at myPubs: The Execution method succeeded, but the number of errors raised (4) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "myPubs.dtsx" finished: Failure.

EncryptSensitiveWithPassowrd: failed

Provided a password for the package, myPubs.dtsx.

SSIS package "myPubs.dtsx" starting.
Error: 0xC002F325 at Copy SQL Server Objects, Transfer SQL Server Objects Task: Execution failed with the following error: "Cannot apply value null to property Login: Value cannot be null..".
Task failed: Copy SQL Server Objects
Warning: 0x80019002 at myPubs: The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "myPubs.dtsx" finished: Failure.

EncryptAllwithPassword:failed
failed
SSIS package "myPubs.dtsx" starting.
Error: 0xC002F325 at Copy SQL Server Objects, Transfer SQL Server Objects Task: Execution failed with the following error: "Cannot apply value null to property Login: Value cannot be null..".
Task failed: Copy SQL Server Objects
Warning: 0x80019002 at myPubs: The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "myPubs.dtsx" finished: Failure.

EncryptAllwithUserkey:failed (ola didn't ask for a key or anything, it must have assumed some default)

SSIS package "myPubs.dtsx" starting.
Error: 0xC002F325 at Copy SQL Server Objects, Transfer SQL Server Objects Task: Execution failed with the following error: "Cannot apply value null to property Login: Value cannot be null..".
Task failed: Copy SQL Server Objects
Warning: 0x80019002 at myPubs: The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "myPubs.dtsx" finished: Failure

ServerStorage: failed
TITLE: Microsoft Visual Studio

Failure saving package.

ADDITIONAL INFORMATION:

Failed to apply package protection with error 0xC0014061 "The protection level, ServerStorage, cannot be used when saving to this destination. The system could not verify that the destination supports secure storage capability.". This error occurs when saving to Xml.
(myPubs)

Failed to apply package protection with error 0xC0014061 "The protection level, ServerStorage, cannot be used when saving to this destination. The system could not verify that the destination supports secure storage capability.". This error occurs when saving to Xml.
(myPubs)

BUTTONS:

OK

Open for suggestions

|||Try EncryptSensitiveWithUserKey and then go back in and retype in your credentials for the connections. Make sure you check the save password box.|||

Jayaram Krishnaswamy wrote:

I forgot to indicate my blog entry for this:

http://hodentek.blogspot.com/2007/03/ssis-migration-fiasco.html

You haven't understood the inherent differences between SSIS and DTS. SSIS is a much more secure environment and will not allow you to store passwords with the same freedom as DTS did. Can I politely suggest that you familiarise yourself totally with this new tool before you start to blog about it and thus post inaccurate information. You are not helping the SSIS community by doing so.

-Jamie

|||If the number of people reporting this is large surely somebody should take the responsibility of putting the matter to rest. This is probably one of the problems with proprietary software.|||

Jayaram Krishnaswamy wrote:

If the number of people reporting this is large surely somebody should take the responsibility of putting the matter to rest. This is probably one of the problems with proprietary software.

REPORTING WHAT, *exactly*?

WHAT *IS* THE PROBLEM?|||

Jayaram Krishnaswamy wrote:

If the number of people reporting this is large surely somebody should take the responsibility of putting the matter to rest. This is probably one of the problems with proprietary software.

huh?

If SSIS is proprietary software then show me some software that ISN'T proprietary.

I haven't seen anyone else on this thread reporting any problem, only you. In fact, you haven't actually articulated a problem as far as I can see. In summary, what is the prolem that you speak of?

-Jamie

|||

Phil Brammer wrote:

Try EncryptSensitiveWithUserKey and then go back in and retype in your credentials for the connections. Make sure you check the save password box.

This did not help in my case.

Jayaram, have you resolved this problem finally?

|||

Dan Miniyarov wrote:

Phil Brammer wrote:

Try EncryptSensitiveWithUserKey and then go back in and retype in your credentials for the connections. Make sure you check the save password box.

This did not help in my case.

Jayaram, have you resolved this problem finally?

I don't know what that means. What exactly is your issue? What are you trying to achieve?

Annoying scrolling in SSIS

Hi Jamie, I raised questions about this kind of thing way back in the private beta timeframe. It was perceived as not too much of a practical problem. Either that, or it was relatively low priority compared to other things that needed to be fixed. Another "feature" is that when you lay shapes out nicely on the designer, close and re-open the package they can be moved! Which is a nuisance. Andrew Watt MVP - InfoPath wrote in message news:8cf48857-f836-4a83-b5f3-ea3a5246be4e@.discussions.microsoft.com... >I have a package with 2 and only 2 tasks in it - which obviously easily
> fit onto the control-flow surface at the same time.
> When I open the package though, why-oh-why is there nothing on the
> screen therefore forcing me to scroll all the way over to the left and
> all the way up to the top to see my 2 tasks (at which point the scroll
> bars disappear). >
> Oh and I close the package down, open it straight back up again and the
> same things happens. >
> Very annoying. Anyone concur? >
> -Jamie >
>I have a package with 2 and only 2 tasks in it - which obviously easily fit onto the control-flow surface at the same time.
When I open the package though, why-oh-why is there nothing on the screen therefore forcing me to scroll all the way over to the left and all the way up to the top to see my 2 tasks (at which point the scroll bars disappear).

Oh and I close the package down, open it straight back up again and the same things happens.

OK its nothing major but its very annoying. Anyone concur?

-Jamie

Annoying Inner Join Problem

Greetings SSIS friends,

I have the following problem in SSIS and it's driving me nuts!!!!!

My situation is as follows :

Data Source 1 & Data Source 2are joined using a merge join. This bit works fine.

The output of the above join is then joined to a third data source but this time, I only get 63 rows coming through instead of 77097 even though the join key in the second merge join component is the same as the first one!!!

I thought I was going mad so I decided to see if the same problem occurs if I was to do this with T-SQL. I created 3 temporary tables for each of my data sources.

I did an inner join between tmpTable_Source1 and tmpTable_Source2, I then stored the result in tempTable4

The final inner join was performed between tempTable4 and tempTable3 and the result produced 77097 and not 63 rows.

What the hell is SSIS playing at?! The merge keys I used in T-SQL is the same one I used in my SSIS package!!!!

Are all the inputs of your merge joins actually sorted? The inputs require to be sorted; otherwise you will get unexpected results.|||

Hi Rafael,

Yes my inputs are sorted but anyway, I decided to do away with one of my data sources and combined it as part of the SQL command in my second data source.

Now I have another problem!!!

In my UNION ALL component I get an error for one of my Input fields. It says that the metadata for my input column does not match with the metadata for the associated output column. The trouble is, this was working just fine before and I haven't touched it!!!!

What would cause this kind of problem?!!

Your help would be much appreciated.

|||

Sorry Rafael,

I've solved this little problem. Looks like the data type for one of my input columns was a unicode string.

I will look in to your idea with the other saved package that I've got. Still unsure aboutt the ordering business but will try it and let you know.

Thanks for your help.

|||

Union all transforms are not very friendly when refreshing metadata. I have found faster to delete the faulty column mapping inside of the lookup and added it back. To delete it select the whole row representing the column with the problem and select delete from the right click menu. Then add it back using the drop down lists. Alternative, you could delete the whole Union all and place it back.

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 2005

Hi,

For Reporting I am using reporting services using the Analysis Services Cube and i am using SSIS to process the cube.

My doubt is if the cube is processing at the same time the report is running the report is not running.

Can you please let me know what to do to runt he same process at the same time.

Thanks * Regards

Dinesh

You could always process it on another server and then sync it across if the database is not too big, otherwise backup and restore.

Matt

|||

Thanks for reply.

This is a huge database, i cannot take backup and restore.

In SSAS there is an option for doing this task is selecting Automatic MOLAP in partitions tab in the cube.

Thanks

Dinesh