SSRS report that uses an SSIS package as input is launching an old and deleted version of the package from the MSDB SQL Server store. Rebooting the server makes the problem go away: correct package is found after the reboot.
Symptoms:
1) Package is first deleted from SQL Server MSDB package store.
2) Report that uses this package is run and expected package not found error occurs.
3) Revised package is imported into the SQL Server MSDB package store.
4) Package is run directly from SQL Server Management Studio. Revised package runs.
5) Report that uses package is run and the old package that was deleted in step 1 above runs.
Is there some reporting service caching of SSIS packages going on here? Is there a way to get around this without having to reboot the server?
Hello everybody, I'm new to SQL,I have a interview tomor,so please i need a website from where i can get questions related to SSIS & SSRS.Please help me. Thanks in advance
I would like to loop through a SQL Server table that contains the paths to all the reports(SSRS) we need to run and then execute the reports via SSIS. What task should I be doing to do this? Will the For Loop work for something like this? Anyone Please Explain how to do it . Or either Explain me how to run a Report(SSRS) from SSIS
I've a question regarding VS 2005 and SSIS (BIDS -- Business Intelligence Development Studio)
I will be moving the existing databases from MS-Access 2003 to Sql Server 2005. My question is I've Visual Studio 2005 Professional Edition version 80.0.50727.42 installed on my system. How do I access Sql Server 2005 , SSIS and SSRS. I mean BIDS using VS 2005?
Does Sql Server Express edition which comes with VS 2005 give me access to work with SSRS and SSIS?
I'm trying to call a SSIS package as my data source for SQL reporting service but I keep getting the error "Cannot create a connection to data source 'DsSSIS'.
My DsSSIS consists of the following in the connection string.
I am developing an SSRS report over a cube. When I drag and drop fields, it works fine. it runs in a few minutes. I am selectinng only from a single day - about 10,000 records. However, when I add some calculated fields it takes much longer. It's been running for 7 hours. The calculated fields fields are pretty simple. Some are selection of one field over another depending upon the value of a 3rd field. One is two fields multiplied together. One is a constant times a field. Something's obviously wrong here. Anybody seen this or have a solution?
Hi All,I have an application that reads data from a very slow database link(like 10 seconds per call) though what I am looking for would be ofgeneric use for anyone who has long-running queries that arefrequently repeated.I would like to be able to cache the results of a query so that I donot have to re-execute that query if it is reissued. Ideally Ibelieve that this could be implemented by hiding the query inside aUDF and exposing the UDF through a view. The UDF could then "Checkthe cache" and only run the slow query if there wasn't a match (or ifthe match was too old). From what I understand the best way to dothis would be for the cache to be an extended stored procedure.Has anyone done or seen this? Has someone written a copy that Icould purchase? Does anyone care to offer their opinnion of how or ifthis could work?Thanks in Advance,Steven
I have a SSISpackage which have 10 execute sql tasks, which loads data into the 10 tabales. Using these 10 tables, I load data into 2 tables. These 2 tables are using to generate reports using SSRS. So we have creatad SSRS package which will generate report. So here what we are doing is we are loading data into those (10+2) tables. then running that report manually and sending that created excel report manully. Can we achieve this using SSIS only, so once data loaded into those 2 reporting table it will start generating reports. How do we achieve using SSRS?
I have an SSIS package. It updates a table in SQL Server 2005 database. There is another SSRS report that presents my final table and I run it and export it into Excel.Is it any way after SQL Query task and Store procedures are being finished in my SSIS package, I add another object to run my SSRS report and export it to Excel format?
SSRS report uses an SSIS package as input and is launching an old and deleted version of the package from the MSDB SQL Server store.
Rebooting the server makes the problem go away: correct package is found after the reboot.
Symptoms: 1) Package is first deleted from SQL Server MSDB package store. 2) Report that uses this package is run and expected package not found error occurs. 3) Revised package is imported into the SQL Server MSDB package store. 4) Package is run directly from SQL Server Management Studio. Revised package runs. 5) Report that uses package is run and the old package that was deleted in step 1 above runs.
Is there some reporting service caching of SSIS packages going on here? Is there a way to get around this without having to reboot the server?
I am trying to write a SSIS package to auto generate a SSRS PDF. I have tried several threads on the net but all seem to require a web service.I am using VS2012 and SQL2014..The report accepts one parameter @Licence
In one of my SSIS Project tasks I insert some records into a table. I want to somehow fire off an SSRS report and send this report to some staff members. how would I add a component to reference or run an SSRS report off the table I just inserted to all from SSIS? I assume I'd h ave to create the report in SSRS first, then somehow reference it from SSIS and then figure out a way to run the report, maybe export it into Excel format and send to the users....not sure how to go about all this in my SSIS package.
I am currently trying to use a SSIS datareader destination as a datasource in Reporting Services (RS). I have sucessfully developed the report and I am able to execute and see the results from it in the RS preview pane. I am also able to deploy it (same server as RS development and report is on) without errors. However, once deployed, I am unable to get the report to work in Report Manager. I get the following error message:
"An error has occurred during report processing. Query execution failed for data set Dynamic_POS_BO_xRef'. The package failed to execute."
I have read everything in BOL regarding configuration of the RS Execution Account and have configured it with a domain member that has admin rights and tryed it with one that has limited rights (best practice recommendation).
Hello All, I want to use SQLDataSource as a base of all Gridview in my application. I have read that SQLDataSource support caching. I want to know as SQLDataSource is a child control of page class. It will get unloaded when page is unloaded. So how caching works. Like I am using SQLDataSource as base of my GridView control. When I perform paging or sorting,In theory It says that It will not hit the Datbase again. It will keep that Data in memory as DataSet, but this will stay alive upto life of page, which starts again and again when a page is requested, So as its child controls. Can anybody give me more intrinsics on this. Any Help from Microsoft is appreciated !!!! Thanks !!
I know I can use the SqlDataSource object and cache the results by setting it's enableCache property to true, but what if I am using essentially the same SqlDataSource control across multiple pages? For instance I have a States SqlDataSource on many pages in my site, if I enable caching on all of these objects won't they all be cached separately? Is the solution to not use the SqlDataSource and instead cache the datatable of results and bind the dropdownlists to that? Thanks in advance.
Hi Expert, Am I doing it right? Can you give me any suggestions please? Thank you!!I followed the way for setting up the sql cache dependency from http://quickstarts.asp.net/QuickStartv20/aspnet/doc/caching/SQLInvalidation.aspx.However, I still have the following error: When using SqlDependency without providing an options value, SqlDependency.Start() must be called prior to execution of a command added to the SqlDependency instance. IssueSummary.aspx<%@ Page Language="VB" MasterPageFile="~/MasterPage.master" AutoEventWireup="true" CodeFile="IssueSummary.aspx.vb" Inherits="IssueSummary" title="Issue Summary" %><%@ Register Assembly="AtlasControlToolkit" Namespace="AtlasControlToolkit" TagPrefix="cc2" %><%@ Register Assembly="Microsoft.Web.Atlas" Namespace="Microsoft.Web.UI" TagPrefix="cc1" %><%@ OutputCache Duration="999999" VaryByParam="None" SqlDependency="CommandNotification" %> Global.asaxSub Application_Start(ByVal sender As Object, ByVal e As EventArgs)Dim connectionString As String = ConfigurationManager.ConnectionStrings("ICTConnectionString").ConnectionStringDim needToInstall As Boolean = TrueTryDim tables() As Stringtables = SqlCacheDependencyAdmin.GetTablesEnabledForNotifications(connectionString)If (Not tables Is Nothing) ThenDim tbl As StringFor Each tbl In tablesIf (tbl.ToLower().Equals("IssueDetails")) ThenneedToInstall = FalseEnd IfNextEnd IfCatch ex As ExceptionneedToInstall = TrueEnd TryIf (needToInstall) ThenSqlCacheDependencyAdmin.EnableNotifications(connectionString)SqlCacheDependencyAdmin.EnableTableForNotifications(connectionString, "IssueDetails")End IfEnd Sub Web.config<connectionStrings><add name="ICTConnectionString" connectionString="Data Source=jamesle-3;Initial Catalog=IssueCommunicationTool;Integrated Security=True" providerName="System.Data.SqlClient"/></connectionStrings><system.web><caching><sqlCacheDependency enabled="true" pollTime="1000" ><databases><add name="ICTDB" connectionStringName="ICTConnectionString" /></databases></sqlCacheDependency></caching> SQL Server 2005 - Database - IssueCommunicationToolSELECT is_broker_enabled FROM sys.databases WHERE name = 'IssueCommunicationTool' Return = 1, it means the broker service is enabled. Also, the database has created the table ASPNET_SQLCacheTableForChangeNotification, the trigger in IssueDetails table, and some other store procedures.
Hello, I have caching enabled in my application and I am using SqlCacheDependency to cache my tables. Well, the cache works fine but when the table is updated the information in my cache does not update. I DO have the broker service started and running but not sure what else i am missing. I am using sql server 2005.
I have a control that shows the top 5 best selling products. Thes list does not need to be up to date at all times. Ideally I would like to cache the data for say 24hr before pulling it again if someone accesses the control. I thought there was an easy way to do this, but I can't seem to find anything. Can anyone point me in the right direction.
Hi, I'm trying to set up a SqlCacheDependency using the Query notifications of SQL Server 2005. I haven't even got to the point of testing the notifications part. My problem is that my DataTable is not even getting stored in the cache when I insert it. The cache seems to be getting invalided as soon as I add the DataTable. Here is my code: (am trying to get a simple example working first)protected void Page_Load(object sender, EventArgs e) { DataTable results = (DataTable)HttpRuntime.Cache.Get("supplyFunctions"); if (results == null) { Response.Write("Cache Invalidated, hitting DB. TIME: " + DateTime.Now.ToString()); results = getSupplyFunctions(); } else { Response.Write("got from Cache, TIME: " + DateTime.Now); } GridView1.DataSource = results; GridView1.DataBind(); }private DataTable getSupplyFunctions() { DataTable results = new DataTable(); using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ToString()) ) { using (SqlCommand command = connection.CreateCommand()) { command.CommandText = "schema.myStoredProc"; command.CommandType = CommandType.StoredProcedure; SqlCacheDependency dependency = new SqlCacheDependency(command); SqlDataAdapter adapter = new SqlDataAdapter(); adapter.SelectCommand = command; DataSet dataset = new DataSet(); adapter.Fill(dataset); results = dataset.Tables[0]; HttpRuntime.Cache.Insert("supplyFunctions", results, dependency); }
} return results; } Also, whats weird is that when I keep refreshing the page, after like 5 to 10 minutes it works, and starts caching the data. This disturbs me-- I would like to know what is going on. I am pretty sure my stored procedure doesn't break the rules of the query notifications. Can someone help me out?!??! Thanks!
I have a page in my website that pulls content for a newsletter from a SQL Server 2000 database using a SqlDataSource and a FormView. I add a new newsletter every week. Other than that weekly addition, no changes are really ever needed or made to the data. Scott Gu says every application can and should make use of caching and I would like to add some kind of caching to improve the performance of this page. I have read about SqlCacheDependency with SQL 2000, but if I understand it correctly, it will poll the database for changes every x seconds, an expensive operation that I just don’t need; the data only changes once a week. I’ve seen some code examples using DataSets and the Cache API but I’m really just looking for a simple, mostly declarative solution using the SqlDataSource. Theoretically, I would like the data cached forever until I insert a new item. I have set the following cache-related properties on my SqlDataSource as follows:
Setting these properties seems to work great for caching the data but how do I programmatically invalidate the cache when a new item is inserted? I have a separate admin page with a FormView that I use to insert the newsletter each week. Is there some kind of code that I could put in the ItemInserted event that would invalidate the cache so that the new item will be displayed and then cached? I don't have any experience with caching; where am I going wrong? Are my expectations unrealistic? Thank you in advance to anyone who can shed some light on this issue.
I've recently deployed an ASP.Net application, with admittedly not a lot of experience with SQL Server 2005 caching.A problem that I ran into is a user searching for a case number very soon after entering it, and it not showing up. However, after a few minutes and re-running the search it appeared.I'm guessing that this is a SQL Server caching issue, but I'm not sure what to do about it.On my search screen, I have a listview for the results, a textbox for the case # input, a button and a sqldatasource. The SqlDataSource uses a select statement, and two parameters for user(ensure the case belongs to that user) and casenumber. The SqlDataSource has enablecaching set to false. Any ideas what I can do about this issue? Also, any good places I can read about sql server caching? Thanks
I'm trying to performance tune a procedure and am sort of being thwarted by caching.
When I first run the procedure, it takes a few seconds which is too long in this case. Subsequent executions in Management Studio are nearly instantaneous, though, which I imagine is due to caching and does not reflect the behavior of the procedure in production.
Is there a way to disable caching so that each execution of the procedure in Management Studio will be consistent and reflect the "first run" performance?
Is there a way to manage the cache when a file is being FTP'd from a server? It writes the file to the d: drive but caches it to c: first. Can I add a command line switch to tell it not to cache the file first?
Is it possible expire a report cache after less than one minute? I'm looking for a way to only have a report hit the database once every 10 seconds, no matter how many people are hitting it. Thanks.
If i use the caching feature of the report. is there a way i can find or set in the report itself if the user if renedered from cache or actually connected to the database.
i mean if ON the Caching, for the 1st user the data will come from DB and the report manager stores the report in cache. next user if he tries to connect should get a message that the report was rendered from cache - taken 5 min back.
