When you create a data flow with an Excel destination and click ‘Name of Excel Sheet’ I get an “Unspecified Error”. When you look under ‘Show Advanced Editor’ of the destination you will see following erros

Goal is to extract complete active directory information with some commonly used attributes using SSIS

There are other ways achieve this using Linked server , Microsoft OLE DB provider for directory services and native TSQL but the limitation is most of the users face is it can only retrieve 100o rows because of the default limitation set by the active directory and OLEDB provider doesnt allow us to over ride the page size option.

We can also use SQL Server CLR to achieve same goal but here there no limit of how many rows you can return in your result set

Last but not least using SSIS and here also there is no limit

We will be using Script component as source with .Net framework 3.5

set all the data types to string

Here is the code
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.Collections;
using System.Collections.Generic;
using System.DirectoryServices.AccountManagement;
using System.DirectoryServices;
[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{

as a developer and writing querys you offtn encounter in a situation where you want to replace a NULL in your result every RDBMS has its own system function to handle this today we will go through these functions in detail

We will start with SQL Server

In sql Server we have to functions one is ISNULL and other COALESCE

ISNULL is a bult in TSQL function develped by Microsoft , it replaces null value with scpecified value

Where as if we look at COALESCE it takes multiple expressions and returns first not null value

The one big dfference between ISNULL and COALESCE is the return data type,

When using ISNULL if check expression is evaluated to NULL and return expression is NOT NULL and is different data type that of check expression SQL Server implictly converts return expression to the data type of check expressions

Where as COALESCE well it returns the first not null value but the data type it returns depends on the SQL Server data type precedence

Exp1 and Exp2 might not be same datatype just like in sql server but it should be implicitly convertble to the datatype of Exp1

But this implicit conversion is done in a smarter way when compared to isnull

If expr1 is character data, then Oracle Database converts expr2 to the datatype of expr1 before comparing them and returns VARCHAR2 in the character set of expr1.

If expr1 is numeric, then Oracle determines which argument has the highest numeric precedence, implicitly converts the other argument to that datatype, and returns that datatype.

Numeric Precedence

Numeric precedence determines, for operations that support numeric datatypes, the datatype Oracle uses if the arguments to the operation have different datatypes. BINARY_DOUBLE has the highest numeric precedence, followed by BINARY_FLOAT, and finally by NUMBER. Therefore, in any operation on multiple numeric values:

If any of the operands is BINARY_DOUBLE, then Oracle attempts to convert all the operands implicitly to BINARY_DOUBLE before performing the operation.

If none of the operands is BINARY_DOUBLE but any of the operands is BINARY_FLOAT, then Oracle attempts to convert all the operands implicitly to BINARY_FLOAT before performing the operation.

Otherwise, Oracle attempts to convert all the operands to NUMBER before performing the operation.

Table 2-10

Implicit Type Conversion Matrix

Table 2-10 Implicit Type Conversion Matrix

CHAR

VARCHAR2

NCHAR

NVARCHAR2

DATE

DATETIME/INTERVAL

NUMBER

BINARY_FLOAT

BINARY_DOUBLE

LONG

RAW

ROWID

CLOB

BLOB

NCLOB

CHAR

—

X

X

X

X

X

X

X

X

X

X

—

X

X

X

VARCHAR2

X

—

X

X

X

X

X

X

X

X

X

X

X

—

X

NCHAR

X

X

—

X

X

X

X

X

X

X

X

X

X

—

X

NVARCHAR2

X

X

X

—

X

X

X

X

X

X

X

X

X

—

X

DATE

X

X

X

X

—

—

—

—

—

—

—

—

—

—

—

DATETIME/ INTERVAL

X

X

X

X

—

—

—

—

—

X

—

—

—

—

—

NUMBER

X

X

X

X

—

—

—

X

X

—

—

—

—

—

—

BINARY_FLOAT

X

X

X

X

—

—

X

—

X

—

—

—

—

—

—

BINARY_DOUBLE

X

X

X

X

—

—

X

X

—

—

—

—

—

—

—

LONG

X

X

X

X

—

X

—

—

—

—

X

—

X

—

X

RAW

X

X

X

X

—

—

—

—

—

X

—

—

—

X

—

ROWID

—

X

X

X

—

—

—

—

—

—

—

—

—

—

—

CLOB

X

X

X

X

—

—

—

—

—

X

—

—

—

—

X

BLOB

—

—

—

—

—

—

—

—

—

—

X

—

—

—

—

NCLOB

X

X

X

X

—

—

—

—

—

X

—

—

X

—

—

Oracle Implicit Conversion Matrix

IFNULLI() in MY SQL

IFNULL() is equavalent to NVL() in oracle and ISNULL() in Microsoft Sql server