How to use .xlsb file in SSIS 2005

• sqlstud

  SSCrazy

  Points: 2527

  More actions

  August 30, 2012 at 5:40 am

  #145557

  Hi,

  I have .xlsb file. It has been used as a source and to insert the data to staging table using ssis 2005.

  Please let me know how to use .xlsb file in SSIS 2005

  Regards

  SqlStud

• Mordred

  SSCrazy

  Points: 2904

  More actions

  August 30, 2012 at 9:05 am

  #1531962

  Hi sqlstud,

  Right click in the Connection Managers window and select New File Connection. Next, click browse and select the xlsb file. You can then use code like (VB)Public Sub Main()

  Try

  Dim MyCon As ConnectionManager = Dts.Connections("Book1.xlsb")

  'Enter your code here

  Dts.TaskResult = ScriptResults.Success

  Catch ex As Exception

  MsgBox(ex.Message)

  Dts.TaskResult = ScriptResults.Failure

  End Try

  End SubHopefully this helps!

  Regards:
  Mordred
  Keep on Coding in the Free World

• sqlstud

  SSCrazy

  Points: 2527

  More actions

  August 31, 2012 at 4:49 am

  #1532297

  Hi,

  I am using SSIS 2005. IF SSIS 2008, we have MS Office 2007 provider

  So,As usual, created new OLEDB connection by choosing the provider Microsoft.ACE.OLEDB.12.0 and set Extended Properties=Excel 12.0;

  But got the attached Error

  Regards

  SqlStud

• sqlstud

  SSCrazy

  Points: 2527

  More actions

  September 4, 2012 at 4:02 am

  #1533186

  Hi,

  IS there any solution?

• Orlando Colamatteo

  SSC Guru

  Points: 182279

  More actions

  September 21, 2012 at 10:54 am

  #1540861

  Sometimes the error messages can be red herrings, but other times they say exactly what needs to be done. Have you tried doing what the error message suggested?

  There are no special teachers of virtue, because virtue is taught by the whole community.
  --Plato