SurendraThotaMSBIProfessional

To connect Terdata database from SSRS we need to install  .Net Data provider for Terdata  on our machine  Then we are able to see Teradata data source type in SSRS. Once we complete the above step then start the report creation process as always do , first create datasource with datasource type  Terdata. In step 2 we need to create dataset and the process of creation of dataset is also same like SQL server/Oracle  datasets. The difference we can see in datasets where we use parameters. The report parameters can be named or unnamed. Teradata only supports unnamed parameters. An unnamed parameter is denoted by a ‘?’, and is merely a placeholder for data that is going to be entered at report processing time. For example, the following is a dataset with a single-value unnamed query parameter: Select * from Table where column = ?; For a multi-value parameter use an IN clause with ‘(?)’, the end-user has the option to select from a list of available values. The following query

Error : [Teradata Database] 3939   There is a mismatch between the number of parameters specified and the number of parameters required. When we get this Error : when we are creating SSRS reports on Teradata database and in which when we are using parameters in the dataset in wrong style\format. Just for Info :  to connect Terdata we need to have  .Net Data provider for Terdata  when we installed this on our machine  Then we are able to see Teradata data source type in SSRS. How to Solve this issue :  This error will come if we are not using correct syntax to create parameterized data set .  The correct way is we need to ?  for parameter The report parameters can be named or unnamed. Teradata only supports unnamed parameters. An unnamed parameter is denoted by a ‘?’, and is merely a placeholder for data that is going to be entered at report processing time. For example, the following is a dataset with a single-value unnamed query parameter: Select * from Table where column

Our SSAS cubes connect to Oracle database . we faced the fallowing error when we are processing the cube  we are getting the fallowing error OLE DB error: OLE DB or ODBC error: ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA. The Actual problem is Some OracleConfig  files correpted in Analaysis server . Solution is Copy the fallowing files  idap.Ora AND  sqlnet.ora files in target directory ie “D:\Oracle\ora1120\network\admin”

..." failed with the following error: "The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. Description: The reason for this error is source contains duplicate rows.so for a row in target matches to all the duplicates in source. Its better to avoid duplicates in source and target. Solution: The solution for this problem is to make sure source doesn’t contain any duplicate rows. Note: it doesn’t fail if you have duplicate rows in target

we can use Script task in SSIS to download the file from HTTP location. we need to pass 3 variables to script task   1. FileURL   2. FileName   3.Destination   --where the file needs to be placed. Here is the complete script '' Microsoft SQL Server Integration Services Script Task '' Write scripts using Microsoft Visual Basic 2008. '' The ScriptMain is the entry point class of the script.*/ Imports System Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Runtime Imports System.Net Imports System.IO <System.AddIn.AddIn( "ScriptMain" , Version:= "1.0" , Publisher:= "" , Description:= "" )> _ <System.CLSCompliantAttribute( False )> _ Partial Public Class ScriptMain Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase Enum ScriptResults Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success Failure = Microsoft.SqlServer.Dts.Runtim

Scenario : In HTTP location the files are placed everyday with Date in the filename  and we need to get the files which are not loaded. If the filename looks like http://XXXXXXXX/html/kpn_services/kpn_services_20140522.csv Implementation  : Step 1 : Create the Execute SQL task to get the list of all the file names which needs from HTTP location.      1,1 you can get the dates from the Destination table where you store the data   ie get all the dates from today to  max(date)  in the destination table.           declare  @FromDate datetime declare @Todate datetime DECLARE @HttpTable TABLE ( fullpath varchar(100), filename varchar(50) ) select @FromDate = max(date) from ODS_Schema.Unicast_level2 select @Todate = getdate()-1 while (@Todate > @FromDate) begin insert into @HttpTable (fullpath,filename) select 'http://XXXXXXXX/html/kpn_services/kpn_services_'+ max(convert(varchar(10),@FromDate,112))+'.csv'  as fullpath, 'kpn_services_'+ max(conver

Step 1) select Send Mail task from the Tool box on Event Handlers  Tab  for OnError Event Handler . Step 2) double Click  On Send Mail Task which will open Send mail Task Editor , Under Mail Section   click on smtpConnection part to create new connection   Give the Name and SMTP Server Details as shown below and click ok 3. Fill the remaining values in Send Mail task Editor as shown below 4. To configure the To Email address or Message source  you can use Expressions .

when i am planning to redeploy the Cube . I took the latest cube from Production and want to deploy in Dev server . i am getting the fallowing error . Error 33 XML parsing failed at line 1, column 0: A document must contain exactly one root element. .  0 0 Error 34 Errors in the metadata manager. An error occurred when instantiating a metadata object from the file, '\\?\E:\MSSQL\Data\DbR4.194.db\MSC ID.112677.dim.xml'.  0 0 Solution : Go the path specified the in the Error on the deploying server not on you local server . and there you will find that file with 0 KB  . Delete that file and redeploy the project .

If we want to check the Datatype of the columnname in each table of the entire Database then we can use this query select OWNER,TABLE_NAME,COLUMN_NAME,DATA_TYPE,DATA_LENGTH from all_tab_columns where COLUMN_NAME = 'MSC_ID' This query will give the list of all tables which are having the given column and datatype of that column in those tables .

when i am trying to open the SSIS package in BIDS  iam getting this error and closing the BIDS Package Loading error ,Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. Actual Issue : the value of the package property "ProtectionLevel" is some sensitive setting instead of DontSaveSensitive then you will face this issue . How to solve this issue : check the package's protectionLevel property  ... unless you're using passwords and security with your packages and their deployment, it's probably best/easier to set your package ProtectionLevel to DontSaveSensitve , which removes passwords from the packages, which should probably solve your problem

DECLARE @epoch bigint SET @epoch = 1399518300002 select DATEADD ( minute , DATEDIFF ( minute , getutcdate (), getdate ()), DATEADD ( s , @epoch / 1000 , '19700101 00:00:00:000' ))

When we are trying to select  different columns which includes bigint column also and insert that result into SSIS Object datatype variable  Using Execute SQL task . In the next Step when we are using that same Object varible  in ForEachLoop then we are facing this Error . Error: The type of the value being assigned to variable "User::MaxEpoch" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object. Why this error is coming :  In ForEachloop the Bigint value from the Object Variable is mapped to Int64 datatype variable in SSIS . Here the problem is occuring that convertion is not possible in ForEach Loop when dealing with Object variable as enumerator . When we are trying to insert BigInt column value directly into Int64 variable using ExcuteSQL task is working fine .The problem is with ForEachloop only . Work around :  we are converting that BigInt Column into Varchar before

when we try to use table name as parameter is Execute sql task in SSIS Then we got this error [Execute SQL Task] Error: Executing the query "delete ? where [group] ='HTTP' " failed with the following error: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. After many trails we came to know that it i snot possible to pass table name as input parameter for the Execute Sql Task . Work Around to solve this issue : Create a Variable to build dynamic query . In the Expression of the Neqvarible pass the Tablename variable . "Delete "+ @[User::DestRateTableName]+ "  where [Group] ='HTTP'" In Execute Sql task select the SQLSourceType as Variable and  select the new variable in Sourcevariable co

when we try to write the expression on a variable  and we made some mistake in the expression then we will get this parsing issue . Error : =================================== Attempt to parse the expression "@[User::DestFilePath]+"\"" failed.  The token """ at line number "1", character number "23" was not recognized. The expression cannot be parsed because it contains invalid elements at the location specified.  (Microsoft.DataTransformationServices.Controls) ------------------------------ Program Location:    at Microsoft.DataTransformationServices.Controls.ExpressionEvaluator.Validate(IDTSVariableDispenser100 vars)    at Microsoft.DataTransformationServices.Controls.ExpressionBuilder.EvaluateExpression() When we will get this Error :  When we are trying to Write an Expression to add "\"  for example to create a complete file path  ie FolderPath+"\"+FileName C:\Users\Public\Documents\SurendraData\

Error : OLE DB error: OLE DB or ODBC error: ORA-01652: unable to extend temp segment by 128 in tablespace TEMP2 When This error will Occur :  When SSAS Cube processing the partition which is contected to Oracle database . Why it will Occur : When we are Quering the Oracle databse the temp table space of the DB got Full . Solution : 1GB of space has been added to TEMP2 tablespace

SSIS Error :  Error at DFT - Loading Data 1 [Script Component [1]]: Microsoft.SqlServer.Dts.Pipeline.CannotCreateUserComponentException: Cannot create user component class. Make sure there is one class marked with SSISScriptComponentEntryPointAttribute in your script.    at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.CreateUserScriptInstance() Error at DFT - Loading Data 1 [Script Component [1]]: Microsoft.SqlServer.Dts.Pipeline.CannotCreateUserComponentException: Cannot create user component class. Make sure there is one class marked with SSISScriptComponentEntryPointAttribute in your script.    at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.CreateUserScriptInstance() Error at DFT - Loading Data 1 [Script Component [1]]: The binary code for the script is not found. Please open the script in the designer by clicking Edit Script button and make sure it builds successfully. Error at DFT - Loading Data 1 [SSIS.Pipeline]: "component "Script Component" (1

SELECT t . NAME AS TableName , i . name AS indexName , ds . name AS filegroup_name , SUM ( p . rows ) AS RowCounts , SUM ( a . total_pages ) AS TotalPages , SUM ( a . used_pages ) AS UsedPages , SUM ( a . data_pages ) AS DataPages , ( SUM ( a . total_pages ) * 8 ) / 1024 AS TotalSpaceMB , ( SUM ( a . used_pages ) * 8 ) / 1024 AS UsedSpaceMB , ( SUM ( a . data_pages ) * 8 ) / 1024 AS DataSpaceMB FROM sys . tables t INNER JOIN sys . indexes i ON t . OBJECT_ID = i . object_id INNER JOIN sys . filegroups ds ON i . data_space_id = ds . data_space_id INNER JOIN sys . partitions p ON i . object_id = p . OBJECT_ID AND i . index_id = p . index_id INNER JOIN sys . allocation_units a ON p . partition_id = a . container_id WHERE t . NAME NOT LIKE 'dt%' AND i . OBJECT_ID > 255 AND i . index_id <= 1 GROUP BY t . NAME , i . object_id , i . index_id , i . name , ds . name ORDER BY OBJECT_NAME ( i . o

In the Table partitioning process the first step we need to create is Partition function . A partition function is used to map the rows of a table into specific partitions ( now have up to 15 , 000 partitions per table in SQL Server 2012 , versus 1 , 000 per table in SQL 2005 & 2008 ). Step 1  : Partition Function =============== CREATE PARTITION FUNCTION M2MPartitionFunction ( varchar ( 6 )) AS RANGE RIGHT FOR VALUES ( '201403' , '201404' , '201405' , '201406' , '201407' , '201408' , '201409' , '201410' , '201411' , '201412' ); we can achieve this by a dynamic script , we can make SP with the below script  Declare @Startdate datetime2 Declare @Enddate datetime2 set @Startdate = '20140301' set @Enddate = '20141201' DECLARE @DatePartitionFunction nvarchar ( max ) = N'CREATE PARTITION FUNCTION