Packaging reports as a product?

I've written an application that generates a couple of stored procs and
views, and installs them into a DB. I also have a series of reports in
Reporting Services that pull data from those views.
Its easy enough for me to distribute the .exe for the app that generates SPs
and views, but how do I distribute the reports without giving the installer
access to them? Basically, I dont want anyone to poke around through the
formulas, etc.
Is there a way to do this?Henry,
Even if you secure the installation process the administrator can always see
the report definition in the Report Manager. So, I would rather gravitate
toward moving the sensitive information in an external .NET assembly and
possibly obfuscating it.
--
Hope this helps.
----
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
----
"Henry Zektser" <Henry Zektser@.discussions.microsoft.com> wrote in message
news:6C861463-3CD3-40BB-80B2-A24D2A443923@.microsoft.com...
> I've written an application that generates a couple of stored procs and
> views, and installs them into a DB. I also have a series of reports in
> Reporting Services that pull data from those views.
> Its easy enough for me to distribute the .exe for the app that generates
SPs
> and views, but how do I distribute the reports without giving the
installer
> access to them? Basically, I dont want anyone to poke around through the
> formulas, etc.
> Is there a way to do this?|||Youre suggesting an assembly that spits out a dataset, and reporting on that?
Are there any examples of this around?
"Teo Lachev [MVP]" wrote:
> Henry,
> Even if you secure the installation process the administrator can always see
> the report definition in the Report Manager. So, I would rather gravitate
> toward moving the sensitive information in an external .NET assembly and
> possibly obfuscating it.
> --
> Hope this helps.
> ----
> Teo Lachev, MVP [SQL Server], MCSD, MCT
> Author: "Microsoft Reporting Services in Action"
> Publisher website: http://www.manning.com/lachev
> Buy it from Amazon.com: http://shrinkster.com/eq
> Home page and blog: http://www.prologika.com/
> ----
> "Henry Zektser" <Henry Zektser@.discussions.microsoft.com> wrote in message
> news:6C861463-3CD3-40BB-80B2-A24D2A443923@.microsoft.com...
> > I've written an application that generates a couple of stored procs and
> > views, and installs them into a DB. I also have a series of reports in
> > Reporting Services that pull data from those views.
> >
> > Its easy enough for me to distribute the .exe for the app that generates
> SPs
> > and views, but how do I distribute the reports without giving the
> installer
> > access to them? Basically, I dont want anyone to poke around through the
> > formulas, etc.
> >
> > Is there a way to do this?
>
>|||Well, depends on what you want to protect. You mentioned that you want to
protect the formulas so I imagined that you have some sensitive information
embedded in your formulas that you don't want to reveal.
What exactly do you want to protect?
--
Hope this helps.
----
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
----
"Henry Zektser" <HenryZektser@.discussions.microsoft.com> wrote in message
news:D95E5500-FB7D-4596-A92B-09E4A6F58981@.microsoft.com...
> Youre suggesting an assembly that spits out a dataset, and reporting on
that?
> Are there any examples of this around?
> "Teo Lachev [MVP]" wrote:
> > Henry,
> >
> > Even if you secure the installation process the administrator can always
see
> > the report definition in the Report Manager. So, I would rather
gravitate
> > toward moving the sensitive information in an external .NET assembly and
> > possibly obfuscating it.
> >
> > --
> > Hope this helps.
> >
> > ----
> > Teo Lachev, MVP [SQL Server], MCSD, MCT
> > Author: "Microsoft Reporting Services in Action"
> > Publisher website: http://www.manning.com/lachev
> > Buy it from Amazon.com: http://shrinkster.com/eq
> > Home page and blog: http://www.prologika.com/
> > ----
> >
> > "Henry Zektser" <Henry Zektser@.discussions.microsoft.com> wrote in
message
> > news:6C861463-3CD3-40BB-80B2-A24D2A443923@.microsoft.com...
> > > I've written an application that generates a couple of stored procs
and
> > > views, and installs them into a DB. I also have a series of reports in
> > > Reporting Services that pull data from those views.
> > >
> > > Its easy enough for me to distribute the .exe for the app that
generates
> > SPs
> > > and views, but how do I distribute the reports without giving the
> > installer
> > > access to them? Basically, I dont want anyone to poke around through
the
> > > formulas, etc.
> > >
> > > Is there a way to do this?
> >
> >
> >

Package question

Hello All,

I am new to the sql 2k5 utilities. My company had to move a couple of their access database over to sql server because they are approaching the 2G size. I have a macro in access that creating a csv file. I have to recreate that macro in sql server. So I have created a view to produce the result set for the file. I create a package that has the view as its source and the csv file as the destination. I run the package to create the file and it runs fine. I then create a job to run the package automatically but the job fails. I am not sure what I am doing wrong. All this is being done directly on the server...do I need to do something extra when I create the step?

In the step I specify step name

Type SSIS...run as 'SQL Agent Service Account'

Package source 'SQL Server"

Then I select the package from the list of SSIS packages. The error message does not give any specifics except to say package failed. The step failed.

Any ideas on what I am doing wrong?

Thanks!

Hi,

you should implement some proper error handling in your SSIS package, seems that if you are importing a csv file, either the SQL Server Agent account has no access to the files, the files are locked, or the Authentication used for loading the data in SQL Server is not setup properly (e.g. the SQL Agent Account has no access to the SQL Serverobjects)

Jens K. Suessmeyer.

http://www.sqlserver2005.de

sql

Package crosstalk?

I'm trying to convince myself that I am not crazy...

I'm having some odd things occur with a couple of child packages being called from a parent package. Inside the master package, I have two different Execute Package tasks that are contained in two different ForEach Loop containers. Both of these containers are downstream of another container. The only Precedence Constraints on either is "Successful".

One of these child packages loads a Household dimension table. The other loads a Customer-to-Account bridge table . I built each child package with a template, so there are many of the same variables, containers, etc.

The first time I ran the master package I noticed my Household package failed because its Flat File Connection Manager was trying to pick up the file my Bridge package needed. I then disabled the container in the master package with the Bridge task so that only the Household package would run. The next time I ran it, I put some PreExecute breakpoints in the Household package. When the breakpoint was hit, the Locals tab showed a mix of Household variable values and Bridge variable values.

So I went to open my Bridge package and the breakpoints I had set in the Household package were also set on the Bridge package. Yet, I had the Bridge package closed when I set the Household breakpoints. If I unset a breakpoint in one, it automatically unset it in the other.

I had seen this behavior before, but it cleared up and I guess I assumed I had dreamt it all up. Anyone else have any experience with this?This may help-->http://sqljunkies.com/WebLog/knight_reign/comments/5366.aspx|||I appreciate the link, R.K.S. Rather than helping me out, though, it's validating that what I'm seeing is bug-like.

I mentioned that the two child packages have like-named variables and that these two packages most likely would be executed at the same time as a result of their parallel position and precedence criteria following an upstream container.

The link states that the variables defined in the child packages themselves are hidden from each other. But with the parallel positioning of their respective Execute Package tasks in my master package, that's not what I'm seeing.

As a test, I moved the position of the Bridge's Execute Package tasks further downstream in my master package. The Household package picks up the correct values in its variables and finishes with no problem.

Curiously, the setting and unsetting of breakpoints across the two packages still occurs.|||Sorry, haven't had much experience with the situation you're describing. I do have a similar problem where I have two child packages that run fine independently but when I run them from a parent the variables in each respective child seem to "step on each other", for lack of a better term. The children were created from the same original package and contain the same variable names. All variables are scoped at the package(child) level.|||

mr_superlove wrote:

Curiously, the setting and unsetting of breakpoints across the two packages still occurs.

This may occur if one of the packages was a copy of another (e.g. you copied one package file and added it as second package). This can confuse the designer, as both packages have the same IDs and the tasks inside them have the same IDs, so the designer can't distinguish between them.

To fix this condition, open one of the packages (or both), make sure the package object is selected in Properties panel, find a property called "ID", click the drop down at the right and select 'Generate new ID'. Save the package. This should fix the problem.

|||Thanks, Michael. This partially works. Certainly at the package level generating a new ID prevents the package-level breakpoints from being confused. I can set the OnPreExecute breakpoint on my Household package and it not propagate to the Bridge package.

I'm still seeing confusion on breakpoints set at the container level, however. The IDs for these objects are grayed out. Any way to generate those?

I assume the like IDs are also the reason for the variable value cross-pollination I was seeing on my copied packages executing at the same time. Any way to just regenerate the whole lot of IDs? Thanks.|||

mr_superlove wrote:

I assume the like IDs are also the reason for the variable value cross-pollination I was seeing on my copied packages executing at the same time. Any way to just regenerate the whole lot of IDs? Thanks.

Here is an KB articule on how to re-generate package IDs

http://support.microsoft.com/kb/906564/

Rafael Salas

|||Thanks, Rafael, but I believe this link merely states an additional command line method of doing what Michael posted.

It doesn't address task IDs within the packages themselves. For instance, here are the IDs for the data flow task in each package:

{BD576B04-E131-479C-B720-75848C51F9E6}
{BD576B04-E131-479C-B720-75848C51F9E6}

These are grayed out and, at least within BIDS, static. I suppose I could modify the IDs manually in a text editor, but I shudder to think the problems that could cause.

It's a great learning experience, though. It certainly speaks to problems I've had in the past with copying data flow components. I'm not crazy after all.|||

I've had a problem with the fact that every "named" object is eventually reduced to a GUID, and only referenced by the GUID during execution (at least, that's how it appears). I believe you're bang-on that the problem you're seeing is caused by the same GUID being used to ID two different objects.

This is precisely why I've pretty much stopped copying and pasting objects -- BIDS does not do a good job of assigning new GUIDs to objects pasted into a project from the clipboard. I've had 'way too many problems because of this issue. (I really wish Microsoft had use a more Visual Studio-like paradigm, where each object was a discrete file like in a VB.NET or C# project. This reliance on XML is just too inefficient.)

I also believe you're going to have to manually edit the DTSX file using a text editor to fix the problem. There are several issues you're going to encounter, the first being that when you search for a GUID in the DTSX, how are you going to tell which object it references? If you manually update the GUID (like, changing the "E6" at the end to "E7", making sure that {BD576B04-E131-479C-B720-75848C51F9E7} doesn't already exist in the DTSX file), you have to be sure to update only the GUID that refers to Data Flow Task 2, and not Data Flow Task 1.

I've actually had good luck directly editing my DTSX, when needed. In many cases it's not to hard to figure out which block of XML referes to which object, based on the context in which it appears (other values around it, etc.). So far I've been able to patch in variables, variable assignments, configuration information, and other stuff. But, nothing quite as ambitious as you need.

My guess is that, other than completely rebuilding one of your data flow tasks, the only fix is going to be to directly edit the DTSX file (after backing it up, of course!).

Good luck!

Package Configuration

Hi All,

I have couple of properties in my package Configuration

(1) Lets me know the Folder path - where I have file processing

(2) Connection String

When working in the Development machine, I am able to play around these values and test the application. I have tried both XML Configuration and SQL Server Configuration

But during deployment in the Test Server, the values in the Configuration are not getting reflected automatically and takes the values assigned during deployment

I tried importing the XML file and it works. But the values are not persistant.After running the package, the value gets resetted to the old value. Every time I run my package, I am reassingning my package variables.

Be it XML Configuration File or SQL Server, I would like to have the variables pulled out from the Configuration I am setting during the deployment. the values should be persistant and should refer the updated values.

Any help will be highly appreciated...thanks

When you move to the test server, do you:
- copy the configuration file to the server
- ensure that SSIS knows to look for the file in the correct place on the new server
- have the configuration file listed in the Package Configurations list? (Right-click Control flow background, select Package Configurations)|||

Phil, Thanks for sharing your thought.

I have configured the XML Package Configuration File during my development and placed the file along with my SSIS Packages in the same folder. But my IS is not picking up the Configuraiton file in my test Environment (new Server.

Any suggestions on how to solve this issue.

Thanks

Evan

|||

Web Explorer wrote:

Phil, Thanks for sharing your thought.

I have configured the XML Package Configuration File during my development and placed the file along with my SSIS Packages in the same folder. But my IS is not picking up the Configuraiton file in my test Environment (new Server.

Any suggestions on how to solve this issue.

Thanks

Evan

Pretty much I have nothing more to add other than the list of things to check above. Is the directory path to the config file exactly the same on the test server compared to the dev server?|||When you open the package on the test server, do you get any warnings? (CTRL-E to open that window if it isn't already)|||

no....I purposely wanted to have the folder structure different in Dev and test Server, because during the actual deployment, we should have the flexibility to change the folder structure accoding to the environment.

I am sure If the directory matches IS is going to pick our files. But I want my setting in the XML Config file to be referred in the test server

Thanks

Evan

|||I dont get any warnings Error. When the package runs, it says that the for each container is empty as its trying to refer a different folder|||

Web Explorer wrote:

no....I purposely wanted to have the folder structure different in Dev and test Server, because during the actual deployment, we should have the flexibility to change the folder structure accoding to the environment.

I am sure If the directory matches IS is going to pick our files. But I want my setting in the XML Config file to be referred in the test server

Thanks

Evan

Right, but you have to understand that SSIS needs to know where to look for the XML configuration file. The path specified cannot change, UNLESS you use an environment variable to define the path to the XML configuration file.|||There are obviously two things going on here.

1 - Can SSIS find the XML configuration file
2 - When it does find the configuration file, is it picking up the values contained within

Assuming SSIS picks up the configuration file in DEV and sets the value accordingly, then it should work in TEST provided it can find the configuration file.|||

Thanks a lot for your useful thoughts.

Finally got this working.

Configured a XML Configuration File and Also configured an Environment Variable.

After deploying the XML configuration file in the test server and configuring the Environment variable, a re-boot is necessary.

I found that after restarting the machine, the test server started to work perfectly.

Thanks a ton for your timely help.

Regards,

Evan

|||Check out this thread of similar problems, for an explanation, perhaps of what was going on with your issue. Specifically my last post.

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

Package Configuration

Hi All,

I have couple of properties in my package Configuration

(1) Lets me know the Folder path - where I have file processing

(2) Connection String

When working in the Development machine, I am able to play around these values and test the application. I have tried both XML Configuration and SQL Server Configuration

But during deployment in the Test Server, the values in the Configuration are not getting reflected automatically and takes the values assigned during deployment

I tried importing the XML file and it works. But the values are not persistant.After running the package, the value gets resetted to the old value. Every time I run my package, I am reassingning my package variables.

Be it XML Configuration File or SQL Server, I would like to have the variables pulled out from the Configuration I am setting during the deployment. the values should be persistant and should refer the updated values.

Any help will be highly appreciated...thanks

When you move to the test server, do you:
- copy the configuration file to the server
- ensure that SSIS knows to look for the file in the correct place on the new server
- have the configuration file listed in the Package Configurations list? (Right-click Control flow background, select Package Configurations)|||

Phil, Thanks for sharing your thought.

I have configured the XML Package Configuration File during my development and placed the file along with my SSIS Packages in the same folder. But my IS is not picking up the Configuraiton file in my test Environment (new Server.

Any suggestions on how to solve this issue.

Thanks

Evan

|||

Web Explorer wrote:

Phil, Thanks for sharing your thought.

I have configured the XML Package Configuration File during my development and placed the file along with my SSIS Packages in the same folder. But my IS is not picking up the Configuraiton file in my test Environment (new Server.

Any suggestions on how to solve this issue.

Thanks

Evan

Pretty much I have nothing more to add other than the list of things to check above. Is the directory path to the config file exactly the same on the test server compared to the dev server?|||When you open the package on the test server, do you get any warnings? (CTRL-E to open that window if it isn't already)|||

no....I purposely wanted to have the folder structure different in Dev and test Server, because during the actual deployment, we should have the flexibility to change the folder structure accoding to the environment.

I am sure If the directory matches IS is going to pick our files. But I want my setting in the XML Config file to be referred in the test server

Thanks

Evan

|||I dont get any warnings Error. When the package runs, it says that the for each container is empty as its trying to refer a different folder|||

Web Explorer wrote:

no....I purposely wanted to have the folder structure different in Dev and test Server, because during the actual deployment, we should have the flexibility to change the folder structure accoding to the environment.

I am sure If the directory matches IS is going to pick our files. But I want my setting in the XML Config file to be referred in the test server

Thanks

Evan

Right, but you have to understand that SSIS needs to know where to look for the XML configuration file. The path specified cannot change, UNLESS you use an environment variable to define the path to the XML configuration file.|||There are obviously two things going on here.

1 - Can SSIS find the XML configuration file
2 - When it does find the configuration file, is it picking up the values contained within

Assuming SSIS picks up the configuration file in DEV and sets the value accordingly, then it should work in TEST provided it can find the configuration file.|||

Thanks a lot for your useful thoughts.

Finally got this working.

Configured a XML Configuration File and Also configured an Environment Variable.

After deploying the XML configuration file in the test server and configuring the Environment variable, a re-boot is necessary.

I found that after restarting the machine, the test server started to work perfectly.

Thanks a ton for your timely help.

Regards,

Evan

|||Check out this thread of similar problems, for an explanation, perhaps of what was going on with your issue. Specifically my last post.

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

OWC, Pivot & "OR" queries

this is probably a very easy question but I can't seem to find an answer...

Is there any visual way of building a query in OWC that includes a couple of conditions included as 'OR'?

basically every dimension I drop becomes a condition but they all get added as 'AND' & I can't seem to have a way to do 'OR' - even cube browser in BI studio doesn't have this option...

You can do OR between members of the same hierarchy (hopefully on the same level). Just select multiple members from the same hierarchy.|||

if it was the same dimension it'd have been easy, but it's a bit complicated since it's a role playing dimension & so even though it's the same hierarchy & same level it becomes 2 different dimensions.

something like this: DateOpened.Year = 2000 or LastRenewalDate.Year = 2000?

|||You cannot do it in OWC. You will have to write your own MDX queries to do it.

overvue

i want to build an xml based, driven web site.......... with sql......... in c#.

However i looked into visual web developer over a couple of weeks and

liked some of the display tools......then when i went back into c# i noticed

some of the display tools werent available and vis versa.

Also in vis web dev there does not seem to be the "under the bonnet"

dataset...person binding source etc.

The question is which way should I go?

Can I build and refine a sql in c# and then drop it into vis web dev or am I

just making a lot of work for no reason.

I just want to start correctly and not end up in some sort of swamp.

I don��t know if I got your ight, but the drag and drop functionality is mainly available through the GUI designer of the webforms / controls. After dragging and dorpping the controls on the form the code behind will automatically produced and can be changed afterwards.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Jens.... thanks again

have included this time the exceptions .....is this any help.

Override constraints

I have to change someones username in my database in a couple of tables. When I try to make any changes, I get an error that there is a constraint. Is there a way to make the changes without removing all the constraints then replacing them?
Thanksheh. sucky. take a look at your constraints to see if they're really necessary. Or follow the correct insertion plan so the contstraints don't block you.

if it weren't 2000, I'd say SET DISABLE_DEF_CNST_CHK, but that's no longer supported.