SurendraThotaMSBIProfessional

Problem : I have got an error when I upgrade from Sql server 2008R2 to 2014Standard version as below: In current, I have a .NET application(32bit) which is executing 2008R2 SSIS packages and the application are running on win server 2008r2 (64bit). These packages were built in BIDS 2008. After that, I upgraded my database server from SQL2008R2 to SQL2014 Standard (running on Win server 2012R2) and deployed .NET app to new environment. I'm encountering the issues with calling package from .NET application(32bit). The message error is ERROR: Could not load file or assembly "Microsoft.SqlServer.Dts.DTSRuntimeWrap.dll, Version=10.0.0.0, Culture=neutral, Public Key=...". In .net application, I already included some DLL (version 10.0.0.0) of SSIS. I don't want to upgrade my .net application and SISS packages because I have around 50 environments (50 servers) and just one of them need to upgrade to SQL server 2014. We are planing to upgrade all but not now. Having two s...

Problem :  I have 4.6 million records  in my sql db  and i want to copy this into a  excel file. for this i went to the db    and rt click and export to command and started the ssis package running.  but after few minutes, it throws me  an error that "error in transferring data into excel file." Copying to `excel1_Wbook` (Error) Messages Error 0xc0202009: Data Flow Task 1: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x8007000E.  (SQL Server Import and Export Wizard) Error 0xc0047022: Data Flow Task 1: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "Destination - excel1_Wbook" (217) failed with error code 0xC0202009 while processing input "Destination Input" (228). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to...

You will get the fallowing error  'Microsoft.SqlServer.Dts.Runtime.DtsRuntimeException'   when you use the SSIS parameters as read write . SSIS Parameters are always read only . It is not for write purpose . Please see my blog  Parameters Vs Variables for more info http://surendrathotamsbiproffesional.blogspot.be/2014/06/ssis-ssis-2012-new-feature-parameters.html

CTE is not allowed in SSAS DSV . it will give the error Incorrect syntax near ')' To avoid this error Please use Named Query instead of CTE. Example : WITH mycte AS (SELECT TOP (200) object_id, name, column_id,         system_type_id         FROM sys.columns)         select [CTETest].*         from         (         SELECT object_id, name, column_id, system_type_id         FROM mycte as mycte_1         ) AS [CTETest] You can use Select Object_id , column_id , system_type_id FROM (SELECT TOP (200) object_id, name, column_id,         system_type_id         FROM sys.columns) myCTE

when we are trying get the result  where 2 measures are not equal . For example :  generally number of files recived should be equal to number of files processed . if there are any difference then there is an issue happened. To identify those records in Tsql we use WHERE cluase to Filter those records . In MDX we need to use Filter ()  function . SELECT NON EMPTY { [Measures].[Number Of Files Recieved] , [Measures].[Number of Files Processed]} ON COLUMNS ,   Filter (     [Date].[Hierarchy].[Date]. ALLMEMBERS ,     ([Measures].[Number of Files Processed]<> [Measures].[Number Of Files Recieved] )         ) ON ROWS FROM [M2M] ;

when ever we got the this error  Code: 0xC0047012 : A buffer failed while allocating 10484760 bytes  it is due to memory size full . All the transactions in SSIS is handled by buffers only The setting you need to be aware of is "Max Server Memory" and if there is physical memory available on the server then you can increase the memory size using the command below. I am linking to a post where it shows suggested Max memory settings but you shouldn't depend on it completely and use your judgement to find the right balance. SSIS packages use memory that is outside of the Max memory setting  and bump your memory reserved for OS as needed. Note that you increase the size of memory for the server and not for a database. http://sqlserverperformance.wordpress.com/2008/08/06/suggested-max-memory-settings-for-sql-server-20052008-2/ http://troubleshootingsql.com/2010/11/28/common-sql-server-myths-%E2%80%93-series-ii-%E2%80%93-sql-memory/ --Sample code only -- Turn on adva...

Problem : have a 64bit system and installed ssis on my system. How do I changed the ssis project to 32 bit. I have this error: Information: 0x4004300A at Data Flow Task, SSIS.Pipeline: Validation phase is beginning. Error: 0xC00F9304 at Package, Connection manager "Excel Connection Manager": SSIS Error Code DTS_E_OLEDB_EXCEL_NOT_SUPPORTED: The Excel Connection Manager is not supported in the 64-bit version of SSIS, as no OLE DB provider is available. Error: 0xC020801C at Data Flow Task, Excel Source [1]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC00F9304. There may be error messages posted before this with more information on why the AcquireConnection method call failed. Error: 0xC0047017 at Data Flow Task, SSIS.Pipeline: component "Excel Source" (1) failed validation and returned error code 0xC020801C. So...

Hi... I am trying to take data from a inspection/timeclock software and create useable reports for our payroll department. My problem is that the data is in a difficult form to sum and I am not entirely sure how I can do this. The employees perform inspections on their vehicle with a handheld unit.  For each row of data there is a starttime and an endtime which shows the beginning and ending times just for the inspection itself.  An inspection might take anywhere from 30 seconds to 15 minutes. Before an employee starts a route, they must perform an inspection.  They will choose a category at the beginning of the inspection that will specify what type of route they are about to run (there could be any number of different route types).  They then perform the inspection (this is their IN time punch) and cradle the handheld unit, which will send the data up to the system.  The employee then begins his/her route.  When the employee is finsihed with his/her rout...

Problem : I've been running into this issue quite a few times, and have been unable to solve it through reading various posts/forums.  Here is the issue Software Details: Datawarehouse Database - SQL Server 2008 R2 Reporting Services Version - SSRS 2008 R2 Development Environment - Visual Studio 2008 Problem Details: I have been writing reports based of a Relational Datamart with dimensions and facts.  The report in question uses dimensions for parameters and facts for content.  Multi valued parameters are enabled in the report query by using the 'IN(@Paramenter)' statement within the report query.  It has a total of 6 multi-value parameters.  I assign the same available values from dimension datasets for each parameter to it's default parameter. Theoretically all the values in the option lists should be selected after first render.  This is not always the case. Example: SELECT * FROM dbo.MyTable WHERE TableColumn0 IN (@Parameter0) AND Table...

Problem : I'm very new new at this.  I'm try to deploy a report model and got this message.  I have no idea what its going on about. ------------------------------ System.Web.Services.Protocols.SoapException: The report server cannot decrypt the symmetric key used to access sensitive or encrypted data in a report server database. You must either restore a backup key or delete all encrypted content. Check the documentation for more information. (rsReportServerDisabled) ---> Microsoft.ReportingServices.Diagnostics.Utilities.RPCException: The report server cannot decrypt the symmetric key used to access sensitive or encrypted data in a report server database. You must either restore a backup key or delete all encrypted content. Check the documentation for more information. (rsReportServerDisabled) ---> System.Exception: The report server cannot decrypt the symmetric key used to access sensitive or encrypted data in a report server database. You must either restore a...

Problem : I have a On_hand_Quantity report which has a parameter drop down to select about 200 stores. When i run this report in BI studio , selecting all the 200 stores for the parameter it works fine. But when i upload the same report to report manager and try selecting all the store_numbers it errors out saying 'operation cannot run due to current state of the object ' This report was running fine since the past 2 years, on 30th dec we did some .NET updates (KB2656356 and KB2656351) after which this report stopped working. Solution : You don't have to uninstall the patch; the default value of MaxHttpCollectionKeys is set to 1000 by the security patch. You can override the value by adding the following key to the appsetttings section in your SSRS report manager web.config file (usually in Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\ReportManager) <add key="aspnet:MaxHttpCollectionKeys" value="10000" /> 1000...

Problem : have a Report with 2 data sources & 2 Data sets 1. SQL (ProjectWebApp) 2. SharePoint List The lookup() function works as expected, however I want the multilookup() to work.  I have many rows in the SharePoint List I want to return. When I use the multilookup() SSRS returns the following and I am not sure how to resolve this: Warning 1 [rsLookupOfInvalidExpressionDataType] The Value expression for the textrun ‘Textbox20.Paragraphs[0].TextRuns[0]’ uses a lookup function with an expression that returned a data type that is not valid for the lookup function. The data type must be an RDL Variant type. Here is the Lookup() expression that works =lookup(Fields!ECP.Value, Fields!Project_Code.Value, Fields!Daily_Update.Value, "List") Solution : If you want to returns all matching values for the Daily_Update field from List dataset, I suggest you should use LookupSet Function. The following expression is for your reference: =Join(LookupSet(Fiel...

Problem: I have a web service task that worked fine prior to upgrading from SQL Server 2005, but in 2012 fails with an error saying the "wsdl file is invalid".  I am using the same wsdl file as I was using in 2005.  Connectivity tests for the web service connection manager in 2012 worked fine. Any ideas on why I would be getting a failed wsdl file when it worked ok on the 2005 platform?  Below is the specific error: [Web Service Task] Error: An error occurred with the following error message: "Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebserviceTaskException: Could not read the Web Services Description Language (WSDL) file. The input WSDL file is not valid. The following error occurred while reading the file. There is an error in XML document (3, 2)..    at Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebServiceTaskUtil..ctor(Object connection, String downloadedWSDL)    at Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebServiceTask.executeThrea...

Problem : I have a container with around 10 tasks in it and when I am trying to move the lowest task I am getting the error: "Cannot move task with precedence constraint out of the container" - although I am not moving it out of the container. It seems that the boundary of the container is not recognized correctly. How can it be solved - I need all these tasks to be inside one container - as there are variables with its scope. Solution : I get this error as soon as I try to resize the task inside the container. Try moving the task control with the cursor keys Up-down-Left-Right, while holding down the CTRL key. I have experienced unexpected behaviour whenever I tried to drag with the mouse. And trying to change the size of the sequence container or the tasks is an exercise in self-control. If you right-click on the task control (or any other control) there is an Autosize command that helps a little.

Problem :   I'm upgrading SSIS 2005 packages to SQL 2008 R2, and I'm running into a problem with VB.Net the Scripting Task.  In SQL 2005 SSIS VB.Net Scripting task the DTS object is instantiated implicitly at runtime and has global scope.  In SSIS 2008 VB.Net scripting task you need to add the reference: Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase Unfortunately this creates an error message that I don't understand: 'ScriptMain' is not CLS-compliant because it derives from 'VSTARTScriptObjectModelBase', which is not CLS-compliant. The module won't build the binary with this error.  Solution : Please refer to the following code in this case: <System.AddIn.AddIn ( "ScriptMain" , Version : = "1.0" , Publisher : = "" , Description : = "" ) > _ <System.CLSCompliantAttribute ( False ) > _ Partial Public Class ScriptMain Inherits Microsoft.SqlServer.Dts.Task...

Problem : I have two script tasks in my SSIS package. The first one saves a string array as a package variable: // In 1st script task: String[] astrCustNames = new String[cust_count]; /* ... * Some code to add strings to array ... */ // Add array to a package variable Dts.Variables["CustomerNames"].Value = astrCustNames; The second task should then extract the strings from the variable. In the past, I've done this with a table variable. The same approach won't work for the string array: // In 2nd script task: OleDbDataAdapter ole_da = new OleDbDataAdapter(); // Fill the customer names data table DataTable dtCustNames = new DataTable(); ole_da.Fill(dtCustNames, Dts.Variables["User::CustomerNames"].Value); The call that fills the data adapter will result in the error "Object is not an ADODB.RecordSet or an ADODB.Record." The .dtsx package lists the data type as DTS:DataType="13" The data type of the variable as it is defined in...

I am trying to calculate the datediff logic using derived column. ( DT_DBDATE ) "2014-01-31" < (( DT_DBDATE ) [ maturityDate ]) ? NULL ( DT_DBDATE )   :   DATEDIFF ( "d" , "2014-01-31" ,(( DT_DBDATE ) [ maturityDate ]) ) but this is giving me error, maturity date is also datetime datatype.i am getting the following error. Solution :  @[User::DataDate] > maturityDate || ISNULL(maturityDate) ? NULL(DT_NUMERIC,10,3) : DATEDIFF("dd",(DT_DBTIMESTAMP)@[User::DataDate],(DT_DBTIMESTAMP)maturityDate) / 365.25

am trying to export a package from 2008r2 to 2012 that is stored in the package store and I keep getting the "class not registered." Any help would be appreciated. TITLE: Import Package ------------------------------ Connecting to the Integration Services service on the computer "" failed with the following error: "Class not registered". This error can occur when you try to connect to a SQL Server 2005 Integration Services service from the current version of the SQL Server tools. Instead, add folders to the service configuration file to let the local Integration Services service manage packages on the SQL Server 2005 instance. For help, click: http://go.microsoft.com/fwlink/?LinkId=220763 ------------------------------ ADDITIONAL INFORMATION: Connecting to the Integration Services service on the computer "s3" failed with the following error: "Class not registered". This error can occur when you try to connect to a SQL Serve...

SSIS Package : While Extracting Sharepoint Lookup column, getting error working on one project and there is need to extract Sharepoint list data and import them to SQL Server table. I have few lookup columns in the list. Steps in my Data Flow : Sharepoint List Source Derived Column its formula : SUBSTRING([BusinessUnit],FINDSTRING([BusinessUnit],"#",1)+1,LEN([BusinessUnit])-FINDSTRING([BusinessUnit],"#",1)) Data Conversion OLE DB Destination But I am getting the error of not converting between unicode and non-unicode string data types. Solution :  You have a data conversion transformation.  Now, in the destination are you assigning the results of the derived column transformation or the data conversion transformation.  To avoid this error you need use the data conversion output. You can eliminate the need for the data conversion with the following in the derived column (creating a new column): ( DT_STR , 100 , 1252 )( SUBSTRING ([ BusinessUnit...

when run the SSIS package if you get the fallowing error Error: 2014-06-12 11:30:28.63    Code: 0xC000F427    Source: xxxxxxxxxxxxxxxxx   Description: To run a SSIS package outside of SQL Server Data Tools you must install LKP Get EventID for FileName of Integration Services or higher. End Error Error: 2014-06-12 11:30:28.63    Code: 0xC000F427    Source: xxxxxxxxxxxxxxxxx    Description: To run a SSIS package outside of SQL Server Data Tools you must install Multicast of Integration Services or higher. End Error Error: 2014-06-12 11:30:28.63    Code: 0xC000F427    Source: xxxxxxxxxxxxxxxxx    Description: To run a SSIS package outside of SQL Server Data Tools you must install ODC Insert Into EventReviewStatusHistory of Integration Services or hig er. End Error Error: 2014-06-12 11:30:28.64    Code: 0xC000F427    Source: xxxxxxxxxxxxxxxxx    Description: To run a SS...

Problem : I got following error in msmdsrv.log. (01/04/2012 12:46:22) Message: OLE DB error: OLE DB or ODBC error: Unspecified error. (Source: \\?\E:\Program Files\Microsoft SQL Server\MSAS10_50\OLAP\Log\msmdsrv.log, Type: 3, Category: 289, Event ID: 0xC1210003) (01/04/2012 12:46:25) Message: OLE DB error: OLE DB or ODBC error: Unspecified error. (Source: \\?\E:\Program Files\Microsoft SQL Server\MSAS10_50\OLAP\Log\msmdsrv.log, Type: 3, Category: 289, Event ID: 0xC1210003) and Application Log: OLE DB error: OLE DB or ODBC error: Unspecified error. EentID=3 and category=289. How do i resolve this error? This server has Ssas and reporting services installed... Solution : Is anything relating to database processing or querying failing?  Or is anything else not working (e.g. you setup querylog but it is not writing entries to the SQL database?). This type of error is often generated when the SSAS instance is trying to use ODBC/OLE DB to connect to a remote database, e....

Problem :  After processing a cube and when attempting to add a heirarchy to the cube I receive the following error : The query could not be processed: o Memory error: While attempting to store a string, a string was found that was larger than the page size selected. The operation cannot be completed. I've seen reference to a hotfix for this issue, Solution : Please update the client PTS9 drivers with the RTM version at: http://www.microsoft.com/downloads/details.aspx?familyid=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&displaylang=en You need to update the client bits along with upgrading the server to RTM.

Columnstore indexes in SQL Server 2014 brought a dramatic boost to data warehouse performance, but with a hitch: They couldn't be updated. With SQL Server 2014, now they can. This means you no longer have to drop and re-create columnstore indexes every time you need to load your warehouse tables. Not only that, but updateability also means you may be able to look at columnstore indexes for certain OLTP applications. The caveat is that you must have a clustered columnstore index on the table. Non-clustered columnstores aren't supported.

For OLTP, the most exciting new feature in SQL Server 2014 is In-Memory OLTP (aka "Hekaton"), which allows you to move individual tables to special in-memory structures. The performance boost can be as huge as 30x. There are a number of limitations and special requirements for these tables, so they won't work under every circumstance. But when they do, your OLTP performance will go through the roof. This is better than other in-memory solutions that require the entire database to be placed in memory. You can get more performance by converting existing stored procedures into in-memory procedures, too. You'll need to test to make sure your tables are compatible, but if they are, you'll love this feature. To convert to Tables in in memory OLTP we need to have these below features for the Tables .

some time back we got the fallowing error when we try to access the SSRS report . An error occurred within the report server database. This may be due to a connection failure, timeout or low disk condition within the database. (rsReportServerDatabaseError) The EXECUTE permission was denied on the object 'sp_verify_job_identifiers', database 'msdb', schema 'dbo'. The SELECT permission was denied on the object 'sysjobs', database 'msdb', schema 'dbo'. The EXECUTE permission was denied on the object 'xp_sqlagent_enum_jobs', database 'mssqlsystemresource', schema 'sys'. The solution for this RS_exec role was granted required access. Open "Reporting Services Configuration Manager" --> Database, verify the login under [Current Report Server Database Credential] is one of users in RSExecRole or not, if not, click "Change Credentials" button to change to an user in RSExecRole.   /****...