Unspecified Error SSIS Excel connection manager

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

Error at blah [Connection manager ""]: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.

An OLE DB record is available.  Source: “Microsoft JET Database Engine”  Hresult: 0×80004005  Description: “Unspecified error”. Error at Insert blah [Destination - blah [199]]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager “DestinationConnectionExcel” failed with error code 0xC0202009.  There may be error messages posted before this with more information on why the AcquireConnection method call failed. Exception from HRESULT: 0xC020801C (Microsoft.SqlServer.DTSPipelineWrap)

To Fix this three easy steps

First step:

please make sure you have this files

C:\windows\system32\odbcjt32.dll

C:\windows\system32\msjet40.dll

C:\windows\system32\msexcl40.dll

C:\Program Files\Common Files\System\Ole DB\oledb32.dll

C:\Program Files\Common Files\System\ado\msado15.dll

 

Second Step

If they are, please manually register them.  Run each of the following commands from command prompt:

Regsvr32 “C:\Program Files\Common Files\system\Ole DB\oledb32.dll”

Regsvr32 “C:\Program Files\Common Files\system\ado\msado15.dll”

Regsvr32 “C:\windows\system32\msjet40.dll”

Regsvr32 “C:\windows\system32\msexcl40.dll”

 

Out put should look like below do this for all the commands above

Unspecified error ssis excel

 

Step three : Restart Visual studio developer studio

 

 

 

DMV : sys.dm_exec_connections

Returns information about the connections established to this instance of SQL Server and the details of each connection.

Column name Data type Description
session_id int Identifies the session associated with this connection. Is nullable.
connect_time datetime Timestamp when connection was established. Is not nullable.
num_reads int Number of packet reads that have occurred over this connection. Is nullable.
num_writes int Number of data packet writes that have occurred over this connection. Is nullable.
last_read datetime Timestamp when last read occurred over this connection. Is nullable.
last_write datetime Timestamp when last write occurred over this connection. Not Is nullable.
client_net_address varchar(48) Host address of the client connecting to this server. Is nullable.

sys.dm_exec_connections

–Client wich has more than one connection

SELECT  client_net_address ,COUNT(*) number_of_connections

FROMsys.dm_exec_connections

GROUP BY client_net_address

HAVING  COUNT(*)> 1

ORDER  BY number_of_connections desc

–Oldest connection to server

SELECT  session_id,connection_id,connect_time FROMsys.dm_exec_connections

ORDER  BY connect_time asc

–Connection with highest network utilization

SELECT  num_reads,num_writes FROMsys.dm_exec_connections

ORDER  BY num_reads DESC,num_writes DESC

Sql server datacollector issue specified @collector_type_uid is not valid in this data warehouse

agent error collector upload error

Executing the query “{call [core].[sp_create_snapshot](?, ?, ?, ?, ?, ?…” failed with the following error: “The specified @collector_type_uid (14AF3C12-38E6-4155-BD29-F33E7966BA23) is not valid in this data warehouse.”. Possible failure reasons: Problems with the query, “ResultSet” property not set correctly, parameters not set correctly, or connection not established correctly.

if we look sp_create_snapshot procedure in Management_dataware_house  it accepts six parameters  @collection_set_uid   ,@collector_type_uid   ,@machine_name  , @named_instance  ,  @log_id   ,  @snapshot_id

this procedure creates a snapshot in the management datawarehouse for a collection instance one parameter is Collecter_type_id

Note: Sql Server Data collector can collect data from Dynamic management views , SQL trace and Windows performance counters using TSQL and WMI query’s

We can get list of availanle data collector types by querying

SELECT

[collector_type_uid],[name]

,[parameter_schema]

,[parameter_formatter]

,[schema_collection]

,[collection_package_name]

,[collection_package_folderid]

,[upload_package_name]

,[upload_package_folderid]

,[is_system]

FROM [msdb].[dbo].[syscollector_collector_types_internal]

Collector types

insertinto [core].[supported_collector_types_internal](collector_type_uid)

values (’14AF3C12-38E6-4155-BD29-F33E7966BA23′)

 

collector suscces full

Importing entire Active directory information using SSIS and .net framework 3.5 and above

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

source type

Source columns

set all the data types to string

AD code

result

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
{

public override void PreExecute()
{
base.PreExecute();
/*
Add your code here for preprocessing or remove if not needed
*/
}

public override void PostExecute()
{
base.PostExecute();
/*
Add your code here for postprocessing or remove if not needed
You can set read/write variables here, for example:
Variables.MyIntVar = 100
*/
}

public override void CreateNewOutputRows()
{
string[] ACTIVE_DIRECTORY_DOMAINS = new string[] { “WEISCORP”, “WEISSTORE” };

foreach (string s in ACTIVE_DIRECTORY_DOMAINS)
{
using (PrincipalContext pc = new PrincipalContext(ContextType.Domain, s))
{
using (PrincipalSearcher sr = new PrincipalSearcher(new UserPrincipal(pc)))
{

PrincipalSearchResult<Principal> list = sr.FindAll();
foreach (Principal result in list)
{
DirectoryEntry de = (DirectoryEntry)result.GetUnderlyingObject();
Output0Buffer.AddRow();
Output0Buffer.AD = de.Properties["samAccountName"].Value.ToString();
Output0Buffer.objectclass = de.Properties["objectCategory"].Value.ToString();
Output0Buffer.domain = s;

}

}
}

}
}

}

Credits to Rory