SurendraThotaMSBIProfessional

In SSIS 2012 when we have Project connection manger  and package connection manager with the same name then we will get this error . Error   21   System . ArgumentException : An item with the same key has already been added .      at System . ThrowHelper . ThrowArgumentException ( ExceptionResource resource )       at System . Collections . Generic . Dictionary ` 2. Insert ( TKey key , TValue value , Boolean add )      at Microsoft . SqlServer . Dts . Runtime . ReferenceId . ReferenceMap . Add ( String key , String value , Boolean isExternal )      at Microsoft . SqlServer . Dts . Runtime . ReferenceId . RefIdProducer . AddRefIdAttribute ( XmlElement element , String objectName , String objectId , ReferenceParserState state )       at Microsoft . SqlServer . Dts . Runtime . ReferenceId . RefIdProducer . GenerateRefId ( XmlElement element , ReferenceParserState state )        at Microsoft . SqlServer . Dts . Runtime . ReferenceId . RefIdProducer . ProcessElement ( XmlEleme

Problem : have installed Analysis service on my server machine through remote login from my service account. I am able to open Analysis services from there. When I open SSMS from the same service account on my local machine, I am not able to connect to that Analysis service. It is giving me error by saying : "A connection cannot be made to redirector. Ensure that 'SQL Browser' service is running. (Microsoft.AnalysisServices.AdomdClient). A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connection host has failed to respond 10.245.192.111:2382(System)." Solution : Based on my research, the issue was caused by the Browser Service account was changed to the domain account. In your scenario, you can try to connect to the Analysis Service using <Servername>:2383 instead of instance name. Or you can change the Browser Service account to "Local System".

Problem : We have a fresh install of WS 2012 and SQL Server 2012 Standard. When connecting to Analysis Services with Management Studio, we get the following error: TITLE: Microsoft SQL Server Management Studio ------------------------------ Error connecting to 'SERVERNAME'. ------------------------------ ADDITIONAL INFORMATION: Method not found: 'Void Microsoft.AnalysisServices.Server.Connect(System.String, Boolean)'. (ObjectExplorer) ------------------------------ BUTTONS: OK ------------------------------ Advanced Information shows: =================================== Error connecting to 'SERVERNAME'. =================================== Method not found: 'Void Microsoft.AnalysisServices.Server.Connect(System.String, Boolean)'. (ObjectExplorer) ------------------------------ Program Location:    at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.SupportedServers.IsIMBIServer(SqlOlapConnectionInfoBase connectionInfo)    at Microsof

when we deployed the ssis package which is developed in sql 2008 r2  the connection string of  Sql server OLEDB provider looks like Data Source=Sever\xxxx;Initial Catalog=DbOdsPMT;Provider=SQLNCLI10.1;Integrated Security=SSPI;Auto Translate=False;Packet Size=32000;Application Name=SSIS-Package-{866F41EB-2082-4344-B650-4BDDB0CFEC95}Sever\xxxx; But when we call the package in SQL agent the connection string changed  to Data Source=Sever\xxxx;Initial Catalog=DbOdsPMT;Provider= SQLNCLI11 ;Integrated Security=SSPI;Auto Translate=False;Packet Size=32000;Application Name=SSIS-Package-{866F41EB-2082-4344-B650-4BDDB0CFEC95}Sever\xxxx; Due to  this the sql Job is Failing  Source: DbOdsPMT-DRA Connection manager "DbOdsPMT"     Description: SSIS Error Code DTS_E_OLEDB_NOPROVIDER_ERROR.  The requested OLE DB provider SQLNCL10 is not registered. Error code: 0x00000000.  An OLE DB record is available.  Source: "Microsoft OLE DB Service Components"  Hresult: 0x80040154 

Here i will let you know how to use the SSRS report from the Front end . i will tell you how to prepare URL part with parameter value . To Preapre SSRS report URL to redirect from the Front end application. First go to Reporting server Reports and click on the Report . Take the URL from the Explorer   ( Note we need to consider Reporting Server URL) Once we got the URL http://ReportServerName/Reportserver_Instance/Pages/Report.aspx?ItemPath=%2fUNICA%2fProject+-+Subproject+Details+Concept+Meeting+View&ViewMode=Detail Then append the Parameter Name and Its value &rs: Command=Render&PR_ShowProject_=false&PR_ShowSubProject_=true&PR_ProjectId_=7661&PR_SubProjectId_=7707 The Final URL looks like http://ReportServerName/Reportserver_Instance/Pages/Report.aspx?ItemPath=%2fUNICA%2fProject+-+Subproject+Details+Concept+Meeting+View &rs:Command=Render&PR_ShowProject_=false&PR_ShowSubProject_=true&PR_ProjectId_=7661&PR_SubProjectId_=770

Problem : when we try to load a SSIS package from the server then we are facing the fallowing error . TITLE: Microsoft Visual Studio ------------------------------ Failed to save package file "C:\Documents and Settings\id826821a\My Documents\Visual Studio 2008\Projects\Integration Services Project1\Integration Services Project1\DbOdsPMT-DRA.dtsx" with error 0xC0010026 "The task has failed to load. The contact information for this task is "%1".". ------------------------------ ADDITIONAL INFORMATION: Failed to save package file "C:\Documents and Settings\id826821a\My Documents\Visual Studio 2008\Projects\Integration Services Project1\Integration Services Project1\DbOdsPMT-DRA.dtsx" with error 0xC0010026 "The task has failed to load. The contact information for this task is "%1".". ------------------------------ BUTTONS: OK ------------------------------ Solution : This issue will come

we have a requirement to create a Cube where Fact data avialable in Oracle and Dimensional Data available in Teradata which Datawarehouse .  The got the issue when we try to keep Other Relational data source as secondary Data source in SSAS . Please check the actual issue what we are facing when we have multiple datasources apart from Sql server . http://surendrathotamsbiproffesional.blogspot.be/2014/10/ssas-teradata-error-errors-in-high.html we performed the Fallowing tests to confirm . Here are the tests Test 1 : Single Data source   è   Teradata ( .net data provider for Teradata )   è   PASS Test 2 : Double Data Source è Teradata ( .net data provider for Teradata )    as Primary and Sql server as Secondary è PASS Test 3 : Double Data Source è Teradata ( .net data provider for Teradata )    as Primary and Oracle  as Secondary ( Tried with all possible oracle connectors) è Fail                  NOTE : Oracle ( Oledb provider for Oracle ) is not even open in

Problem :  we want to use multiple datasources in our Cube . it is working fine when we use Oracle but it is failing when we use Teradata. with the fallowing error . Errors in the high-level relational engine. The 'DS Name' secondary data source is not a relational data source, or does not use an OLE DB provider. we are using  .Net provider for Teradata . Solution :  Teradata Or Oracle can only be Primary DataSource . So Make Teradata as Primary and Sql server as Secondary . we performed  some tests and come to conclusion . please see the below tests. Here are the tests Test 1 : Single Data source   è   Teradata ( .net data provider for Teradata )   è   PASS Test 2 : Double Data Source è Teradata ( .net data provider for Teradata )    as Primary and Sql server as Secondary è PASS Test 3 : Double Data Source è Teradata ( .net data provider for Teradata )    as Primary and Oracle  as Secondary ( Tried with all possible oracle connectors) è Fail    

When we try to load data into Excel we are getting the comment on each cell like the number in this cell is formatted as text or preceded by an apostrophe ssis to avoid these we did this work around 1. take a template with sample data 2.load new data(it takes same datat ypes as in sample data) 3.remove sample data from target excel Brief decription for the above Steps. 1. Take a Destination Excel format as a Template  with some sample data ( with the format we required ) 2. copy that template to destination location and load the other data into that Excel 3. Then remove the sample data from the excel  using script task .   Please see my previous post on how to delete 2nd row from the excel using script task

we want to Delete the 2nd row from the Excel file where 1st row Contains Headings . Here is the script task component code to delete 2nd row from the Excel . Major Steps : Create Excel.Application Object                      Open work of the Given Excel file                      Open sheet                      Delete the Entire row  of Cell A2   using Entirerow,Delete                      Save The workbook                      Quit the excel Dim filename As String Dim appExcel As Object Dim newBook As Object Dim oSheet1 As Object appExcel = CreateObject ( "Excel.Application" ) filename = "C:\test.xls" appExcel . DisplayAlerts = False newBook = appExcel . Workbooks . Open ( filename ) oSheet1 = newBook . worksheets ( "Sheet1" ) oSheet1 . Range ( "A2" ). Entirerow . Delete () newBook . SaveAs ( filename , FileFormat := 56 ) appExcel . Workbooks . Close () appExcel . Quit ()

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 stop running.  There may be error messages posted bef

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 route, they are required to run a