Pre-execute Hangs at 50%

Hello,

I did a search and found no answers. I have a simple project; one data flow task reading from one DB2 tabel and writing to another. My OLEDB source is a sql command that returns 97,000 rows and my OLEDB destination's data access mode is "table or view".

For my OLEDB source SQL command, I run it through the "build query" panel and I get all 97,000 rows back within 90 seconds everytime.

I don't know what is so different since I currently do SQL commands that bring back 2.5 million rows from DB2 with no effort in SSIS. I even took my data flow task and placed into a known good solution - but still hangs at 50% forever. I tried attaching to SSIS server and executing through there, again no luck. Still hung!

Any ideas? Thanks.

On your OLE DB Source component in the data flow, set its property, "ValidateExternalMetadata" to false and see if that helps.

One other thing to try is to set the property of the DB2 connection manager object, "DelayValidation" to true.|||

Thanks for your reply Phil.

I went ahead to set both properties accordingly and reran. After 16 minutes of running it was still on the pre-execute phase stuck at 50%.

Just to test I changed my DB2 SQL to FETCH ROW FIRST ONLY and it still hangs at the pre-execute 50% level. So obviously it's not the number of rows causing the problem.

What I don't understand is using the "preview" panel in the OLEDB source, I get the first row in about 6-7 seconds.

Thanks.

|||Do you have any other connection managers defined in the package? Validation will attempt to validate all connections.

What DB2 driver are you using?|||

No, that is the only connection I have in my package/solution.

I am using "Native OLE DB\IBM OLE DB Provider for DB2".

|||

Is there any way to see a log of where it's getting hung? Maybe its my OLEDB Desitnation...since everything is perfect with my source.

Any ideas on how to verify my idea? Thanks.

|||

I went ahead and changed my destination to a temperary SQL 2005 table and now it works fine...so It is my destination. It must be failing on something (connection/rights?) and not telling me why?|||

It seems MS really dropped the ball on this one.

I'm almost certain that it's failing on a unique_index insert error. Why is it hiding the error message and letting SSIS hang FOREVER? Why does it do the SELECT and INSERT before the "real" execution of my package? This really defeats the purpose!!!

oh well - thanks for your help.

|||

Zach84 wrote:

It seems MS really dropped the ball on this one.

I'm almost certain that it's failing on a unique_index insert error. Why is it hiding the error message and letting SSIS hang FOREVER? Why does it do the SELECT and INSERT before the "real" execution of my package? This really defeats the purpose!!!

oh well - thanks for your help.

Be careful... You aren't using a Microsoft driver. It isn't doing any selects or inserts -- it's just trying to validate metadata and such.

You could always try the Microsoft OLE DB for DB2 driver, which I've had better luck with.

Predictions

Hello all,
I was handed a new project and I'm not quite sure how to
do it or even what technology to use. Now, I think that
SQL Server's Analysis Services could help (or even do the
entire thing) but I'm not sure. So, basically, I'm
looking for recommendations and ressources on the subject.
Here's my scenario:
We have a Data-Warehouse that stores information about
the production of our company. Around this fact table, we
have multiple dimensions including one about the origin
of the production (who) and a time dimension (when).
We are building cubes and linking Excel to them for
future analysis. Everything is done. Now, the all-mighty
supervisors would like to have some predictions built-in
to the system for the rest of the year based on the
previous years. Basically, we would be adding production
values for every possible time (the step is every hour,
so the sum of the production comming from a certain
origin is set as the fact and linked to a time dimension
row for that hour).
As you can see, it's not very difficult and it seems to
me like Analysis Services might already have that
functionnality although I couldn't find it (seems like
predictions can only be made by looking at relations
found by the data mining algorithms and these predictions
need to be made by a human because they are not really
registered as "new" facts or rows but more like new
columns).
So, in short, I'd like to have a tool calculate future
productions for the rest of the year and set a new column
(or dimension) to a certain value indicating that this
fact row has been predicted and is not certain. Of
course, the refreshing of data would not create duplicate
rows for predictions, newer predictions (probably based
on more facts) would replace the last equivalent
prediction.
I hope that's clear.
What do you recommend I use? Is it a case where I will
need to build my own separate program (that updates the
data-warehouse) or is it possible to achieve this using
data mining?
Thanks a lot,
Skip.You could use the datamining prediction task in DTS. I have used this before
and it seems to work well. The accuracy of your model (and suitability of y
our data) can be tested directly by applying the prediction task to the sour
ce data. This task as far a
s I used it is suitable for Y/N type questions - eg given a male living in L
ondon in a certain wage-bracket, would they be likely to join our gym. I can
't see how this type of prediction tool can be used for numerical data apart
from bracketed ranges. If
this IS suitable for you, then it's a simple matter to take the predicted da
ta and load it into the fact table and then do an incremental update of the
cube - eg in DTS.
Regards,
Paul Ibison

Prblem with MSDN Article on forms authentication for rs2005

Hi All
i wanted to implement foms authentication to RS2005.i am
using the MSDN article to implement the forms authentication.when build
the project i am getting this error.
'Microsoft.Samples.ReportingServices.CustomSecurity.Authorization' does
not implement interface member
'Microsoft.ReportingServices.Interfaces.IAuthorizationExtension.CheckAccess(string,
System.IntPtr, byte[],
Microsoft.ReportingServices.Interfaces.ModelOperation)' C:\Program
Files\Microsoft SQL Server\MSSQL\Reporting
Services\Samples\Extensions\FormsAuthenticationSample\Authorization.cs
can any one help me with this problem.
ThanksTry opening your reference and see any problems otherwise remove the
reference and search for the reference and add it again..
Amarnath
"vamsi" wrote:
> Hi All
> i wanted to implement foms authentication to RS2005.i am
> using the MSDN article to implement the forms authentication.when build
> the project i am getting this error.
>
> 'Microsoft.Samples.ReportingServices.CustomSecurity.Authorization' does
> not implement interface member
> 'Microsoft.ReportingServices.Interfaces.IAuthorizationExtension.CheckAccess(string,
> System.IntPtr, byte[],
> Microsoft.ReportingServices.Interfaces.ModelOperation)' C:\Program
> Files\Microsoft SQL Server\MSSQL\Reporting
> Services\Samples\Extensions\FormsAuthenticationSample\Authorization.cs
>
> can any one help me with this problem.
> Thanks
>|||Ok Thanks.i will try it.
Thanks
Vamsi
Amarnath wrote:
> Try opening your reference and see any problems otherwise remove the
> reference and search for the reference and add it again..
> Amarnath
> "vamsi" wrote:
> > Hi All
> > i wanted to implement foms authentication to RS2005.i am
> > using the MSDN article to implement the forms authentication.when build
> > the project i am getting this error.
> >
> >
> > 'Microsoft.Samples.ReportingServices.CustomSecurity.Authorization' does
> > not implement interface member
> > 'Microsoft.ReportingServices.Interfaces.IAuthorizationExtension.CheckAccess(string,
> > System.IntPtr, byte[],
> > Microsoft.ReportingServices.Interfaces.ModelOperation)' C:\Program
> > Files\Microsoft SQL Server\MSSQL\Reporting
> > Services\Samples\Extensions\FormsAuthenticationSample\Authorization.cs
> >
> >
> >
> > can any one help me with this problem.
> >
> > Thanks
> >
> >|||everything looks good.i added microsoft.reporting.interfaces and web
services and named as my machine name.still getting the error at the
authorization .
Thanks
vamsi wrote:
> Ok Thanks.i will try it.
> Thanks
> Vamsi
> Amarnath wrote:
> > Try opening your reference and see any problems otherwise remove the
> > reference and search for the reference and add it again..
> >
> > Amarnath
> >
> > "vamsi" wrote:
> >
> > > Hi All
> > > i wanted to implement foms authentication to RS2005.i am
> > > using the MSDN article to implement the forms authentication.when build
> > > the project i am getting this error.
> > >
> > >
> > > 'Microsoft.Samples.ReportingServices.CustomSecurity.Authorization' does
> > > not implement interface member
> > > 'Microsoft.ReportingServices.Interfaces.IAuthorizationExtension.CheckAccess(string,
> > > System.IntPtr, byte[],
> > > Microsoft.ReportingServices.Interfaces.ModelOperation)' C:\Program
> > > Files\Microsoft SQL Server\MSSQL\Reporting
> > > Services\Samples\Extensions\FormsAuthenticationSample\Authorization.cs
> > >
> > >
> > >
> > > can any one help me with this problem.
> > >
> > > Thanks
> > >
> > >|||Hi,
apparently the samplel code is not complete for the current version, I faced
the same problem.
The issue is that the code supplied does not include 2 overloads of the
CheckAccess method.
To resolve include the following in Authorisation.cs:
// Overload for a Model operation
public bool CheckAccess(
string userName,
IntPtr userToken,
byte[] secDesc,
ModelOperation modelOperation)
{
// If the user is the administrator, allow unrestricted access.
if (0 == String.Compare(userName, m_adminUserName, true,
CultureInfo.CurrentCulture))
return true;
if (!CheckAccess(userName, userToken, secDesc, modelOperation))
return false;
else
return true;
}
// Overload for a ModelItem operation
public bool CheckAccess(
string userName,
IntPtr userToken,
byte[] secDesc,
ModelItemOperation modelItemOperation)
{
// If the user is the administrator, allow unrestricted access.
if (0 == String.Compare(userName, m_adminUserName, true,
CultureInfo.CurrentCulture))
return true;
if (!CheckAccess(userName, userToken, secDesc, modelItemOperation))
return false;
else
return true;
}
This will allow the code to compile.
Now if only it didnt give me an 'Invalid Namespace' Error when I try to run
it... :(
Please lt me know if it works for you!
"vamsi" wrote:
> everything looks good.i added microsoft.reporting.interfaces and web
> services and named as my machine name.still getting the error at the
> authorization .
> Thanks
> vamsi wrote:
> > Ok Thanks.i will try it.
> >
> > Thanks
> > Vamsi
> > Amarnath wrote:
> > > Try opening your reference and see any problems otherwise remove the
> > > reference and search for the reference and add it again..
> > >
> > > Amarnath
> > >
> > > "vamsi" wrote:
> > >
> > > > Hi All
> > > > i wanted to implement foms authentication to RS2005.i am
> > > > using the MSDN article to implement the forms authentication.when build
> > > > the project i am getting this error.
> > > >
> > > >
> > > > 'Microsoft.Samples.ReportingServices.CustomSecurity.Authorization' does
> > > > not implement interface member
> > > > 'Microsoft.ReportingServices.Interfaces.IAuthorizationExtension.CheckAccess(string,
> > > > System.IntPtr, byte[],
> > > > Microsoft.ReportingServices.Interfaces.ModelOperation)' C:\Program
> > > > Files\Microsoft SQL Server\MSSQL\Reporting
> > > > Services\Samples\Extensions\FormsAuthenticationSample\Authorization.cs
> > > >
> > > >
> > > >
> > > > can any one help me with this problem.
> > > >
> > > > Thanks
> > > >
> > > >
>|||Update:
The problem is that you are using the Report Services 2000 sample on a 2005
server. You should download the 2005 sample from here:
http://www.microsoft.com/downloads/details.aspx?familyid=E719ECF7-9F46-4312-AF89-6AD8702E4E6E&displaylang=en
hope this helps :-)
"vamsi" wrote:
> Hi All
> i wanted to implement foms authentication to RS2005.i am
> using the MSDN article to implement the forms authentication.when build
> the project i am getting this error.
>
> 'Microsoft.Samples.ReportingServices.CustomSecurity.Authorization' does
> not implement interface member
> 'Microsoft.ReportingServices.Interfaces.IAuthorizationExtension.CheckAccess(string,
> System.IntPtr, byte[],
> Microsoft.ReportingServices.Interfaces.ModelOperation)' C:\Program
> Files\Microsoft SQL Server\MSSQL\Reporting
> Services\Samples\Extensions\FormsAuthenticationSample\Authorization.cs
>
> can any one help me with this problem.
> Thanks
>|||Hi i downloaded new article from the same page just a week back and it
worked.it is able to authenticate.But i am having little bit doubt on
the member ship role authorization.
where we have to design all the tables for the roles that each
registered user supposed to get.Like i want some users to get the
subscription screated and i dont want for some of the users to do this
and they can just view the reports.
Thanks
Vamsi
> Update:
> The problem is that you are using the Report Services 2000 sample on a 2005
> server. You should download the 2005 sample from here:
> http://www.microsoft.com/downloads/details.aspx?familyid=E719ECF7-9F46-4312-AF89-6AD8702E4E6E&displaylang=en
> hope this helps :-)
> "vamsi" wrote:
> > Hi All
> > i wanted to implement foms authentication to RS2005.i am
> > using the MSDN article to implement the forms authentication.when build
> > the project i am getting this error.
> >
> >
> > 'Microsoft.Samples.ReportingServices.CustomSecurity.Authorization' does
> > not implement interface member
> > 'Microsoft.ReportingServices.Interfaces.IAuthorizationExtension.CheckAccess(string,
> > System.IntPtr, byte[],
> > Microsoft.ReportingServices.Interfaces.ModelOperation)' C:\Program
> > Files\Microsoft SQL Server\MSSQL\Reporting
> > Services\Samples\Extensions\FormsAuthenticationSample\Authorization.cs
> >
> >
> >
> > can any one help me with this problem.
> >
> > Thanks
> >
> >sql

Possible to encrypt database assembly?

Hello.

I've built a simple Visual Basic .NET project containing the following code...

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server

Partial Public Class StoredProcedures
<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Shared Sub WhoAmI()
Using connection As New SqlConnection("context connection=true")
connection.Open()
Dim command As New SqlCommand("SELECT SUSER_SNAME()", connection)
SqlContext.Pipe.ExecuteAndSend(command)
End Using
End Sub
End Class

From Visual Studio, I want to encrypt the contents of this assembly, as a proof-of-concept.
Even though assembly contents are stored as varbinary(MAX) in the database, converting to varchar(MAX) will expose the code.

However, the Dotfuscator Community Edition reports the following error: "Dotfuscator Community Edition cannot operate on SQL Server applications.... please try Standard or Professional Edition."

Has anyone tried encrypting a database assembly and deploying to the database?

A good test would be to issue the following TSQL script against the database holding the assembly...

-- Does the sample code above run?
EXEC dbo.WhoAmI
GO
-- Is the code readable?
SELECT * , Convert(varchar(MAX), content) FROM sys.assembly_files

Hi,

There is no explicit support for encrypted assemblies in the database. You can use a code obfuscation utility to make reverse-engineering difficult, as you suggest.

Perhaps someone else has a favorite obfuscator they can recommendI don't know much about them.

Cheers,

-Isaac

|||

Note that the code you're reading from sys.assembly_files isn't related to encrypting the assembly - VS deploys your full source code and PDB files to the database to assist with debugging. If you don't want VS to do this, then you can turn it off under the project properties / Deploy / Deploy Code option. This still won't protect your assembly though as anyone can use a reflection tool such as http://www.denisbauer.com/NETTools/SQL2005Browser.aspx. Obfuscation can help here, but the real solution is to control who has access to the system views containing the assembly using metadata permissions.

Steven

|||A great answer... You've given lots of new leads. Thanks.

Possible to add a Flash Player control to a report?

I'm working on a project where the client wants us to create custom charts which have features not found in the standard Reporting Services charts. I would like to use flash to build this and have found an existing flash charting package for which I have the code and which I plan to expand.

Here's the problem:

The client wants to include these charts in his Reports. Is there some way to add a flash control to a report so that it will appear on the web page as a fully-functional flash presentation, which can then be included when printing or exporting?

The alternative, would be to figure out some means of generating the chart using flash outside of the report, exporting a bitmap of the chart and then saving the bitmap to a known location on the file system so It can be referenced by a report. This has some serious complications, and may not be feasible.

Any ideas?

Embedding flash into reports is not supported. While there are several third party advanced charting packages available which specifically integrate with SSRS2005 Standard, Enterprise, or Developer editions (based on the CustomReportItem extensibility feature), they also cannot use flash because it is currently not supported.

-- Robert