Category: SSIS

QuickTiP: Setup TNS less connection to Oracle in SSIS

Leave a comment

 

Add connection :

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Connection String: (DESCRIPTION=(CID=GTU_APP)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=myHost)(PORT=myPort)))(CONNECT_DATA=(SID=MyOracleSID)(SERVER=DEDICATED)))

 

 

 

Advertisement

Unspecified Error SSIS Excel connection manager

1 Comment

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: 0x80004005  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

 

 

 

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

1 Comment

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

How to achieve Initcap functionality in SSIS , and how to use FINDSTRING(),

2 Comments

How to use FINDSTRING() SSIS

FINDSTRING() works as the same as CHARINDEX() in TSQL

it returns the position of character or the string in side the string that we want to search

Syntax : FINDSTRING( “search string”,”searching term”,occurrence)

to better understand we will use a simple example in validating a email address, so for the email to be valid it needs to have at least one @ character

in the email address , if not its considered as not a valid address

As we learned how to use FINDSTRING() now we will use this functionality to get the initcap functionality in SSIS , Initcap is a cool feature in oracle when this function used,

The first letter of each word into uppercase, all other letters in lowercase. Words are delimited by white space or characters that are not alphanumeric

This is how it is used in Oracle example

The following example capitalizes each word in the string:

\\***** PL\SQl****\\\
SELECT INITCAP(‘the soap’) “Capitals” FROM DUAL;

Capitals
———
The Soap
\\*** PL/SQl*****\\

Now we will achieve this in SSIS using FINDSTRING, TRIM, UPPER, and LOWER functions in derived columns

In the derived column we are using

(UPPER(SUBSTRING(TRIM(Name),1,1)) + TRIM(LOWER(SUBSTRING(TRIM(Name),2,FINDSTRING(TRIM(Name),” “,1) – 1 < 0 ? LEN(TRIM(Name)) : FINDSTRING(TRIM(Name)," ",1) – 1)))) + " " + (FINDSTRING(TRIM(Name)," ",1) – 1 < 0 ? " " : UPPER(SUBSTRING(TRIM(SUBSTRING(TRIM(Name),FINDSTRING(TRIM(Name)," ",1),LEN(TRIM(Name)))),1,1)) + TRIM(LOWER(SUBSTRING(TRIM(Name),FINDSTRING(TRIM(Name)," ",1) + 2,LEN(TRIM(Name))))))

The above expression looks big but it is very simple

In this we are taking the first letter of the word and making it upper case , if there is a empty in the string we are considering as a second word and then we are selecting the

first letter of the world to upper case and all other characters are mapped to lower case

How to create and map Excel destination dynamically in SSIS

17 Comments

In this post, you will learn

  1. How to create and map Excel destination dynamically in SSIS
  2. How burst the out put into multiple files using For loop container
  3. how to dynamically create sheet names

In this post we will use a OLEDB source and a Excel destination , for the OLEDB source we will use the Adventure works database [Production].[ProductListPriceHistory] table

This table has a history of price changes for each product, so our goal is to create a history Excel file for each product id














Create a variable type object to hold the distinct productID’s











Drag and drop a Data flow task add a oledb source with SQL query as data access mode and modify the query to get the distinct product ids and
Use a record set destination to load all the distinct product ids into the Object variable

drag and drop a For each loop enumerator and configure it as shown below

create a variable with string type and evaluate the variable as expression and open the expression editor modify the sheet create syntax to create the exact sheet name for the excel file, in our case we will use this ‘product_’+ productid

go back to the for loop drag and drop excel with connection type as excel and source input will be variable and select the sheet creation variable that we just created

Go back into the data flow task and edit the expression of excel connection manager and select the connection property as excel file path if you choose connection string then you have to use the full length connection string like this or go for the excel file as shown in the below picture
“Provider=Microsoft.Jet.OLEDB.4.0;Data Source=”+@excelfilename+”;Extended Properties=/”Excel 8.0;HDR=YES/”;”
you will get error like this
TITLE: Microsoft Visual Studio
——————————

Error at Exceldestination [Connection manager “Excel Connection Manager”]: The connection string format is not valid. It must consist of one or more components of the form X=Y, separated by semicolons. This error occurs when a connection string with zero components is set on database connection manager.

Error at Exceldestination: The result of the expression “@[User::Excelfilename]” on property “ConnectionString” cannot be written to the property. The expression was evaluated, but cannot be set on the property.

Error at Create and load Excel [Excel Destination [57]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E37.

Error at Create and load Excel [Excel Destination [57]]: Opening a rowset for “productid_0” failed. Check that the object exists in the database.

——————————
ADDITIONAL INFORMATION:

Exception from HRESULT: 0xC02020E8 (Microsoft.SqlServer.DTSPipelineWrap)

——————————
BUTTONS:

OK
——————————


TITLE: Microsoft Visual Studio
——————————

Error at Create and load Excel [Excel Destination [57]]: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E37.

Error at Create and load Excel [Excel Destination [57]]: Opening a rowset for “productid_0” failed. Check that the object exists in the database.

——————————
ADDITIONAL INFORMATION:

Exception from HRESULT: 0xC02020E8 (Microsoft.SqlServer.DTSPipelineWrap)

——————————
BUTTONS:

OK
——————————
to fix this error create the excel file and the sheet during the design time

after clearing the errors, you can now go ahead and delete the excel file we created during the design time





























If you have any problems please use the comments section

Thank You