question

VarunJ1991-7113 avatar image
0 Votes"
VarunJ1991-7113 asked VarunJ1991-7113 commented

SSIS Migration issue 2008 to 2016

I got requirement to convert all my excel report file in SSIS from .xls to .xlsx including migration from 2008 to 2015 DATA Tools(Targer version : SQL Server 2016) - For all the reports we use Data Flow Task to extract reports i have added "WITH Result Sets" along with EXEC SQL command which is working fine. But Execute SQL Task in the project which has OUTPUT variable in Result set from Teamp table is throwing below error.

[Execute SQL Task] Error: Executing the query "EXEC master..merge_file ? ,..." failed with the following error: "Class not registered (Exception from HRESULT: 0x80040154 (REGDB_E_CLASSNOTREG))". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
I understand this is due to its running in 64bits but if we switch it to 32bits its working fine but Excel connection manager is throwing error since .xlsx format works only in 64bits. How to tackle both the issues. ? Appreciate any help to resolve the issue.

sql-server-integration-services
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Don't you have the wrong tag? There is nothing about T-SQL here.

0 Votes 0 ·

Thank u corrected.

0 Votes 0 ·

1 Answer

Monalv-msft avatar image
0 Votes"
Monalv-msft answered VarunJ1991-7113 commented

Hi @VarunJ1991-7113 ,

1.Please check if you download both 32-bit and 64-bit Microsoft Access Database Engine 2016 Redistributable.
Hope the following link will be helpful:
Installing the Microsoft.ACE.OLEDB.12.0 Provider for Both 64-bit and 32-bit Processing

2.Please check if you set the parameter correctly in the Execute SQL Task.
Please refer to Parameters in the Execute SQL Task.

Best regards,
Mona


If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.



· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @VarunJ1991-7113 ,

May I know if you have anything to update?

Best regards,
Mona

0 Votes 0 ·

Yes both the 32 and 64bits installed - As you know we need the reports need to be .xlsx. generated from SSIS package from data flow task.. in the same package we have Execute sql task which have requirement to generate "Full result set" - This is not working when 64BIT Runtime set to "True" - This is the same error i am getting Class not registered (Exception from HRESULT: 0x80040154 (REGDB_E_CLASSNOTREG)). But if it set 64runtime to false its working fine but. .xlsx DFT task is failing. i want both to work..

is it possible ?

0 Votes 0 ·