MSBI Tutor

Error: Cannot convert "Column" between a unicode and a non-unicode string data types in SSIS Solution 1: 1. Right click on source task. 2. click on "Show Advanced editor". -- advanced edit option for source task in ssis 3. Go to "Input and Output Properties" tab. 4. select the output column for which you are getting the error. 5. Its data type will be "String[DT_STR]". 6. Change that data type to "Unicode String[DT_WSTR]". --Changing the data type to unicode string 7. save and close. Solution 2: Add Data Conversion transformations to convert string columns from non-Unicode (DT_STR) to Unicode (DT_WSTR) strings. You need to do this for all the string columns

Error: Variable used as a source or destination and is empty SSIS Solution : Step 1: Click on that variable and press F4, that will take you to properties. Set EvaluteAsExpression=True Step 2: Set the DelayValidation property for the File System Task to True

Error Message: [Excel Source [2]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009.  There may be error messages posted before this with more information on why the AcquireConnection method call failed. Solution 1: 1. Navigate to Project-> project Properties. 2. Navigate to “Debugging” option from left panel and from Right panel, change Run64BitRuntime value to false. Solution 2: 1. Navigate to Project-> project Properties. 2. noticed that the package has "DelayValidation" property set to false in the package. So, we changed the DelayValidation property of package to true and re-ran the package and it succeeded. 

Error: TITLE: Microsoft Visual Studio ------------------------------ Failed to execute the package or element.   Build errors were encountered. For more information, see the Output window. ------------------------------ BUTTONS: OK ------------------------------   Solution: We tried to close SSDT and run it again but, we still got the same error while running SSIS package. Then, we need to follow bellow solution: Step 1: Go to Task Manager–> Details Tab. Step 2: Locate the process “ DtsDebugHost.exe “. Kill this process. There might be multiple instances of this process. Kill all of them. Step 3: Rerun SSIS package

TITLE: Microsoft Visual Studio ------------------------------ Error at Load_Invoice [Merge Join [103]]: The column with the SortKeyPosition value of 1 is not valid. It should be 0. ------------------------------ ADDITIONAL INFORMATION: Exception from HRESULT: 0xC02020B5 (Microsoft.SqlServer.DTSPipelineWrap) ------------------------------ BUTTONS: OK ------------------------------ Solution: It seems to you need to refresh the metadata 1. Delete existing Merge Join transformation 2. Drag and drop new Merge Join transformation. 3. Provide proper connections.

Feature availability  Not supported     Fully supported    `

 

TITLE: Microsoft Visual Studio ------------------------------ Unable to cast COM object of type 'System.__ComObject' to interface type 'Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSObject100'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{203E24BD-5203-4C76-AFC7-7C8545EBA1A0}' failed due to the following error: The application called an interface that was marshalled for a different thread. (Exception from HRESULT: 0x8001010E (RPC_E_WRONG_THREAD)). ------------------------------ BUTTONS: OK ------------------------------ Solution: 1. Before starting, make sure you close any open Visual Studio instance. 2. Open command prompt and run as a administrator. 3. Navigate to bellow path        C:\Program Files\Microsoft SQL Server\ 120 \DTS\Binn\ where  120  is you SQL Server version number.   4. Reregister the dts.dll by executing the command:   regsvr32 dts.dll    

TITLE: Microsoft Visual Studio ------------------------------ Failed to connect to server . ------------------------------ ADDITIONAL INFORMATION: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 2) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=2&LinkId=20476 ------------------------------ The system cannot find the file specified ------------------------------ BUTTONS: OK ------------------------------ Solution: 1.Check for working fine SQL Server Services running or not. 2.Also check for working in good condition SQL Server (MSSQLSERVER). 3.Also check for working fine SQL Server B

1. You can check all the sqlcmd commands using this command: Sqlcmd -?   2. check sql server instance is running or not sc query mssql$instancename    3. check sql server browser is running or not sc query mssqlbrowser    

Step 1: Open report server web URL like : http//:localhost/reports Step 2: Open data sources folder Step 3: Click on (…) and  select manage Step 4: Go to credentials session à select using the following credentials option à provide proper user name and password  à click on apply

Procedure 1: 1.Start Management Studio and connect to Report Server Instance. 2.Right click on the ReportServer Instance and Select Properties 3.Click Advanced 4.In EnableRemoteErrors, select True. 5.Click OK. Procedure 2: 1. Create a text file and copy the following script into the file. Public Sub Main()    Dim P As New [Property]()    P.Name = "EnableRemoteErrors"    P.Value = True    Dim Properties(0) As [Property]    Properties(0) = P    Try      rs.SetSystemProperties(Properties)      Console.WriteLine("Remote errors enabled.")    Catch SE As SoapException      Console.WriteLine(SE.Detail.OuterXml)    End Try  End Sub 2. Save the file as EnableRemoteErrors.rss. 3. Click Start, point to Run, type cmd, and click OK to open a command prompt window. 4. Navigate to the directory that contains the .rss file you just created. 5. Type the following command line, replacing servername with the actual name of your server: rs -i EnableRemoteErrors.rss -s h

TITLE: Microsoft SQL Server Management Studio ------------------------------ Restore of database 'DataBase_Name' failed. (Microsoft.SqlServer.Management.RelationalEngineTasks) ------------------------------ ADDITIONAL INFORMATION: System.Data.SqlClient.SqlError: Exclusive access could not be obtained because the database is in use. (Microsoft.SqlServer.SmoExtended) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=12.0.2000.8+((SQL14_RTM).140220-1752)&LinkId=20476 ------------------------------ BUTTONS: OK ------------------------------ Procedure 1: 1. Right click Databases 2. Restore Database... 3. [General] Source from Device, select the bak file 4. [General] Change the Destination database name to a new name 5. [Files] Change mdf and ldf Restore As paths to a different, new path 6. [Options] (unnecessary on the other computer) Check Overwrite the existing database (WITH_REPLACE) 7. [Options] (unnecessary on the other computer)

USE [msdb] ; GO SELECT DISTINCT        j . name 'job_name' ,        count ( h . run_date ) 'failed_runs_last_7' --h.run_duration FROM        sysjobs j             JOIN sysjobhistory h on j . job_id = h . job_id WHERE        j . enabled = '1'        and h . run_status = '0'        and h . step_id = '0'        and h . run_date >= CONVERT ( VARCHAR ( 8 ), GETDATE (), 112 ) - 7 -- past 7 days GROUP BY        j . name ORDER BY        j . name

STORED PROCEDURES Ownership a)      The developer writing the Stored Procedure must provide his / her Full Name, Create Date, and brief Description of the Stored Procedure. This commented information should be the first part of the Stored Procedure. -- Author          : Author Name           -- Create date     : Date       -- Description     : Brief Description       SET Options b)      The Following options should be set in Each Stored Procedure, set ANSI_NULLS ON set QUOTED_IDENTIFIER ON SET NOCOUNT ON   Naming Conventions a)      Stored Procedure name should start with ‘usp’. The next alphabet after ‘usp’ should be CAPITAL and each recognizable word in the name of Stored Procedure should start with CAPITAL letter. For example ‘uspInsertProcedure’   b)      INPUT parameters of Stored Procedure should start with ‘@p’ and end with ‘_IN’. For Example, @pInputVariable_IN.   The next alphabet after ‘@p’ should be