The output column "" on output "" and component "XML Source" is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance.

In SSIS when we will use data source as XML Source and transmit to any destination source, we may get warning message like:

The output column ""  on output "" and component "XML Source" is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance.

Cause: We are getting a data from a column of XML source but we are not using this. So it would be better to delete or remove that column from XML source to increase the performance.

  

Solution:

Step 1: Right click on your Component "XML Source" and click one edit option.

Step 2: Click on Column tab and choose your Output from combo box.

Step 3: Unchecked the columns name in list which you don't want to use and click on OK button.

Note: It is necessary to select at least one column in each Output.

Note: in same way we can remove the unused columns for other component.

No maximum length was specified for the output column "" with external data type System.String. The SSIS Data Flow Task data type "DT_WSTR" with a length of 255 will be used.

In SSIS when we will use data source as XML Source and if will generate a XSD file and click on Mapping tab or OK button we may get warning message like:

No maximum length was specified for the output column "" with external data type System.String. The SSIS Data Flow Task data type "DT_WSTR" with a length of 255 will be used.

Cause: In your XSD file either generated by SSIS or created by you, doesn't specify the max length of XML tag or attribute of type string. So SSIS use default value that is 255.

Solution:

1. If max length of string i.e. 255 is sufficient for you then click on OK button.

2. If you want increase or decrease the max of string then you have to edit your .xsd file as follow:

Replace the each element and specify minLenght and maxLength according to your requirement:

Initially:

<xs:element minOccurs="0" name="phone" type="xs:string" />

Now:

<xs:element minOccurs="0" name="phone" >

     <xs:simpleType>

          <xs:restriction base="xs:string">

              <xs:minLength value="0"/>

              <xs:maxLength value="4000"/>

          </xs:restriction>

     </xs:simpleType>

</xs:element>

Note: Change the name = "phone" attribute to your element name.

The external column "" of "component "ADO NET Destination" has a data type that cannot be mapped to a Data Flow task data type. The Data Flow task data type DT_NTEXT will be used instead.

In SSIS when we will create task where data destination is ADO NET Destination. Suppose it loads data into the table tblTarget:

CREATE TABLE tblTarget (

    ID INT,

    Remarks SQL_VARIANT

)

We will get error message like:

The external column "Remarks" of "component "ADO NET Destination" has a data type that cannot be mapped to a Data Flow task data type. The Data Flow task data type DT_NTEXT will be used instead.

Cause: SSIS doesn't support a column of data type SQL_VARIANT. So SSIS package will type case data of Remarks column into the DT_NEXT.

Solution:

Since it is warning message so if you want to continue it then, click on OK button.

Or you can also change the data type of Remarks column like VARCHAR(MAX), NVARCHAR(MAX) etc.

The references an external data type that cannot be mapped to a Data Flow task data type. The Data Flow task data type DT_WSTR will be used instead.

In SSIS when we will create task where data source is OLE DB source. Suppose it loads data from table tblSource:

CREATE TABLE tblSource(

    ID INT,

    Remarks SQL_VARIANT

)

We will get error message like:

The output "OLE DB Source Output" references an external data type that cannot be mapped to a Data Flow task data type. The Data Flow task data type DT_WSTR will be used instead.

The output column "" references an external data type that cannot be mapped to a Data Flow task data type. The Data Flow task data type DT_WSTR will be used instead.

Or will use OLE DB destination into the table tblSource, we will get error message like:

The input "OLE DB Destination Input" references an external data type that cannot be mapped to a Data Flow task data type. The Data Flow task data type DT_WSTR will be used instead.

Cause: SSIS doesn't support a column of data type SQL_VARIANT. So SSIS package will type case data of Remarks column into the DT_NEXT.

Solution:

Since it is warning message so if you want to continue it then, click on OK button.

Or you can also change the data type of Remarks column like VARCHAR(MAX), NVARCHAR(MAX) etc.

There was an error setting up the mapping. DTD is prohibited in this XML document.

In SSIS when we will create task where data source is XML source and we are using inline schema  

We may get an error message like:

There was an error setting up the mapping. DTD is prohibited in this XML document.

Cause: Your XML file also contains DTD (Document Type Definition).  

Solution: SSIS doesn't support DTD in XML file. So remove the all DTD from your XML file. 

For security reasons DTD is prohibited in this XML document. To enable DTD processing set the ProhibitDtd property on XmlReaderSettings to false and pass the settings into XmlReader.Create method.

In SSIS when we will create task where data source is XML source.

We may get a warning message like:

For security reasons DTD is prohibited in this XML document. To enable DTD processing set the ProhibitDtd property on XmlReaderSettings to false and pass the settings into XmlReader.Create method.

Cause: Your XML file also contains DTD (Document Type Definition).  

Solution: SSIS doesn't support DTD in XML file. So remove the all DTD from your XML file.

The data type "" found on column "" is not supported for the . This column will be converted to DT_NTEXT.

In SSIS when we will create task where data source is ADO NET Source or OLE DB source. Suppose it loads data from table tblSource:

CREATE TABLE tblSource(

    ID INT,

    Remarks SQL_VARIANT

)

We may get a warning message like:

The data type "System.Object" found on column "" is not supported for the component "ADO NET Source". This column will be converted to DT_NTEXT.

Cause: SSIS doesn't support a column of data type SQL_VARIANT. So SSIS package will type case data of Remarks column into the DT_NEXT.

Solution:

Since it is warning message so if you want to continue it then, click on OK button.

Or you can also change the data type Remarks column like VARCHAR(MAX), NVARCHAR(MAX) etc.

Overwriting Stored Procedure "" at destination

In SSIS when we will execute a Transfer Master Stored Procedures Task. There is option IfObjectExists, if we will choose Overwrite option

We may get warning message like:

Overwriting Stored Procedure "" at destination

It is only warning that SSIS package is overwriting the existing stored procedure. So don't worry about this.

Cannot perform alter on '' because it is an incompatible object type.

Suppose in any database there is table tblTargent

CREATE TABLE tblTarget(

    ID INT IDENTITY(1,1) PRIMARY KEY,

    vcData VARCHAR(500)

)

Now if try to Alter table tblTargert into VIEW like this :

ALTER VIEW tblTarget

AS

    SELECT * FROM tblOrder

We will get error message like :

Cannot perform alter on '' because it is an incompatible object type.

Cause: In sql server, we cannot alter one object to another object type likes:

1. Table to view or vice versa

2. Stored procedure to function or vice versa

3. Scalar function to tabled valued function or vice versa

Solution:

1. Use different object name. For example:

CREATE VIEW vewTarget

AS

    SELECT * FROM tblOrder

2. First drop the existing object then create it. For example:

DROP TABLE tblTarget

CREATE VIEW tblTarget

AS

    SELECT * FROM tblOrder

RETURN statements in scalar valued functions must include an argument

If we create a scalar function:

CREATE FUNCTION uspData()

RETURNS INT

AS

BEGIN

    RETURN SELECT TOP(1) Roll_No FROM Student

END

Or

CREATE FUNCTION uspData(

    @Value AS INT

)

RETURNS INT

AS

BEGIN

    IF @Value = 0

         RETURN

    ELSE

         SET @Value = @Value + 10

        

    RETURN @Value

END

We will get error message :

RETURN statements in scalar valued functions must include an argument.

Cause: In sql server, scalar function must return a single value. So we have to pass value in RETURN statement as an argument not a result of any SELECT statement.

  
In first function returning a result set while in second function, RETURN statement in IF clause not returning any value. 

Solution:

We can write above function like this:

CREATE FUNCTION uspData()

RETURNS INT

AS

BEGIN

    RETURN (SELECT TOP(1) Roll_No FROM Student)

END

Or We can use inline tabled value funcion:

CREATE FUNCTION uspData()

RETURNS TABLE

AS

RETURN(

     SELECT * FROM Student

)

Cannot use a BREAK statement outside the scope of a WHILE statement.

If we execute following sql query:

DECLARE @Num AS INT = 10

   

IF @Num < 5

    BREAK

ELSE

    SELECT @Num

We will get error message :

Cannot use a BREAK statement outside the scope of a WHILE statement.

Cause: In sql server, keyword BREAK is part of WHILE loop. We must have to use BREAK keyword inside a WHILE loop. For example:

DECLARE @Num AS INT = 10

WHILE @Num  > 0 BEGIN

   

    IF @Num < 5

         BREAK

    ELSE

         SELECT @Num

        

    SET @Num = @Num - 1

END

Cannot use a CONTINUE statement outside the scope of a WHILE statement.

If we execute following sql query:

DECLARE @Num AS INT = 5

IF @Num < 5

    CONTINUE

SELECT 1

We will get error message :

Cannot use a CONTINUE statement outside the scope of a WHILE statement.

Cause: In sql server, keyword CONTINUE is part of WHILE loop. We must have to use CONTINUE keyword inside a WHILE loop. For example:

DECLARE @Num AS INT = 10

WHILE @Num  > 0 BEGIN

    SET @Num = @Num - 1

   

    IF @Num % 2 = 0 BEGIN

         CONTINUE

    END ELSE

         SELECT @Num

END

Copy or Move databases from one server to another server using ssms in sql server 2012

Using Sql sever management studio 2012 (SSMS 2012) we can move one or more databases from one server to another or same server.

Steps for this are:

Step 1: Right click on your database and choose Copy Database...

Step 2: We may get welcome window. Click on Next button.

Step 3: In Select a Source Server window, choose your source database server name from where you want to move or copy databases and its authentication. Click on Next button.

Step 4: In Select a Destination Server window, choose your destination database server where you want to move or copy your databases and its authentication. Click on Next button.

Note: It is necessary to enable sql sever agent job at destination server. If it is not enabled you may get a pop up window to enable or run sql server agent job. Click on Yes button.

Step 5: There are two transfer methods.

1. Detach and attach method

2. Sql management object method

Choose any one according to your requirement. Click on Next button.

Step 6: Select databases which you want to move (delete from source sever) or copy. We cannot move or copy databases like system databases (master, tempdb, model and msdb), databases marked as replication etc. Click on Next button.

Step 7: In Configure Destination database window:

Destination database name: You can change the destination database name.

Also choose the option if destination database already present at destination server. Click on Next button.   

Step 8: In Configure the Package window if you want change the Package name etc. you can change here, otherwise click on Next button.

Step 9: We can run only one time or schedule the copy or move database process after a regular interval.

To run only one time choose Run immediately.

To schedule it choose Schedule.

Step 10: If you will choose schedule option you will get New Job Schedule window. Fill the scheduling information and click on OK button.

Step 11: In Complete wizard click on Finish button.

'' is an invalid event type. Sql server

Suppose we have created tblTarget using following sql script in sql server:

CREATE TABLE tblTarget(

    ID int IDENTITY(1,1) PRIMARY KEY,

    vcData varchar(500) NULL,

)

Now if create trigger by executing following sql statement:

CREATE TRIGGER trgTarger ON tblTarget

FOR DELETED

AS

SELECT 1

We will get error message:

'DELETED' is an invalid event type.

Cause: DELETE is invalid trigger event types. Valid trigger events are:

1. INSERT

2. UPDATE

3. DELETE

Solution:

Correct syntax for creating trigger is:

CREATE TRIGGER trgTarger ON tblTarget

FOR DELETE

AS

SELECT 1

The table '' is ambiguous. Sql server

Suppose we have created tblTarget using following sql script in sql server:

CREATE TABLE tblTarget(

    ID int IDENTITY(1,1) PRIMARY KEY,

    vcData varchar(500) NULL,

)

Now if will execute a sql query like this:

UPDATE tblTarget

    SET vcData = s.vcData

FROM  tblTarget t INNER JOIN tblTarget s

ON t.ID = s.ID

We will get error message:

The table 'tblTarget' is ambiguous.

Cause: We cannot use table which is in update clause more than one times in from clause.

Solution: Use table alias in update clause. For example:

UPDATE t

    SET vcData = s.vcData

FROM  tblTarget t INNER JOIN tblTarget s

ON t.ID = s.ID

Query not allowed in Waitfor sql server

If we will execute following sql query in sql server:

WAITFOR (SELECT GETDATE())

We will get error message:

Query not allowed in Waitfor Sql server.

Cause: We cannot write query in WAITFOR. It is syntax error

Solution:

Valid syntax is:

1. WAITFOR DELAY '2:40'

2. WAITFOR TIME '5:30'