DTS_E_OLEDB_EXCEL_NOT_SUPPORTED

The current majority opinion is that the current Office OLEDB provider (Microsoft.ACE.OLEDB.12.0) and 64-Bit environment can’t work together. In fact there are some problems using the provider in a 64Bit environment. If you want to know how to use it in SSIS the right way read this article.

Some facts about the Office OLEDB provider and SSIS:

Currently the Office OLEDB provider is 32 bit only!

That is wrong! Microsoft provides a 64 bit version since December 2010. You can download it here: Microsoft Access Database Engine 2010 Redistributable

Developing SSIS packages with the 64 bit Version of the provider does not work!

Unfortunately Microsoft provides Visual Studio as 32 bit application only (Why?). Therefore it can’t access/use 64 bit OLEDB providers. As a consequence of that fact you cannot select the Office OLEDB provider. In an development environment you have to use the 32 bit provider.

Excuting SSIS packages in an 64 bit environment with 64 bit OLE DB driver does not work!

Yes and no! Using the original Excel-Source does not work. It seems that Microsoft implemented an hard if-statement which throws an exception (DTS_E_OLEDB_EXCEL_NOT_SUPPORTED: The Excel Connection Manager is not supported in the 64-bit version of SSIS, as no OLE DB provider is available.) if these conditions are met:

·         DTS-Engine runs in 64 bit environment

·         Connection string is “Microsoft.ACE.OLEDB.12.0″

One solution for that is to use the standard OLEDB Source. Select Microsoft.ACE.OLEDB.12.0 as OLEDB provider and set theextended properties to “Excel 14.0;HDR=YES”. Now you can use the normal OLEDB source to retrieve data from an Excel file. I found no restrictions so far.

Excel has to be installed where the SSIS package is to be executed!

That is wrong. You only need the Microsoft Access Database Engine 2010 Redistributable

The lesson is clear: Using Office OLEDB in a 64 bit environment with SSIS works (At least at the command line with DTEXEC)!

Description of Error in SSIS

I have come across a open source data flow component which we can add in data flow transformation Tool Box .SSIS inherently does not provide column name which caused the error.

http://eod.codeplex.com/

All we just need to do is drag and drop this component in data flow task and map all the information that need to capture like  column name,error description and many more things.

The steps that need to follow are as follows:

1.Go to above link , click download link ,extract files and run the install.bat file which will install the component in GAC.

2. Go to Your SSIS package and Open your Data Flow Task and Go To ToolBox.

3.Right click the mouse in Data Flow Transformation section and choose Items which Will Open up Window then select SSIS data flow items TAB.You can see the list of component available and checked.Scroll down and find Error description  and mark it checked and click the OK button.

4.Now Error Description will appear in toolbox ,just drag and drop it.Now map the error mapping path  of source to Error description.You also need to select name of columns describing error ,which you want to log in destination.

Please let me know if you have any issue in implementing this.

SSIS : Excel Header Issue

Problem:
Want to upload Excel file into SQL Server database. Excel file contains header and logo in the start. The Data with column header starts after some lines in excel. dont need these header data in my destination.
Solution:
SOL 1: There could be multiple solutions for this using script task and coding but there is very easy step in which you need to set hdr=yes in excel connection string.
This properties you can find in connection manager properties by hovering on connection string or copying it in notepad. by defualt you will see hdr=no.
SOL 2: There is one more solution where you can provide the range for excel columns which can be used for data in source. For this go to excel source , right click and click on Advance editor.
There you can select data access mode as openrowset and provide database as excel range from where you wanna select data leaving the header rows.

Aggregate Transformations

Aggregate Transformation:

Aggregate transformation is used for basic aggregation as in SQL such as count distinct, group by.

 Advance Window

Providing the number of distinct values, exact or approximate, optimizes performance, because the transformation can preallocate an appropriate amount of memory to do its work.

Reading Data from XML Source Adapter

Reading Data from XML Source Adapter

XML source adapter does not support mixed content model on complex types.

I was getting the above error while try to read my XML file from XML source adapter in data flow task. I have tried one other XML file which I able to read but not the other file.

I saved it check it and tried many times bit nothing happened. This file was created in xml spy.

I finally checked thoroughly the tags in XML files and then find out one tag had 2 closings but there is no difference in file saving. I removed it and then check the same process again from XML source adapter and it was working fine.

It seems the problem was only because wrong closing of tag but XML file doesn’t showed any error at the time saving.

Issue while reading data from XML in SSIS data flow task

Issue while reading data from XML

Error Message

[XML Source [1]] Error: The "component "XML Source" (1)" failed because truncation occurred, and the truncation row disposition on "output column "Query" (7382)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.

This error occurs when you read XML files through XML Source in data flow task. This issue occurs when because of some column length in XML data source.

To resolve this issue right click on the XML source and click on show advance properties. In advance properties check the length of output column is sufficient or not.

Issue in reading object variable in Script Task in for loop in SSIS.

While working with SSIS object variable I have faced some issue when I used it in loop and not able to get any proper solution on web. I find out some work around.

Problem:

 I have created a package in which I was getting the result set from database into an object variable using Execute SQL task. While iterating it in a loop the object was showing null values after first iteration. Script task was throwing an error after second iteration.

Analysis:

It seems the issue with the object reading of the .net. While filling data in to data table through object for first time it traverse entire result set. For second times it doesn’t retrieves the value from object in script task. This behavior is the know problem in SSIS for object variable.

Solution

For this we have added the data fill task for object in loop itself so it fills the data in the object every time in beginning of the loop.