I have to use excel 2007 version as data source . I changed the excel version to 2007 in the excel connection manger, but it when I ran the package it threw error: Error: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.Data.OleDb.OleDbException: External table is not in the expected format. at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection) at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject) at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup) at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.OleDb.OleDbConnection.Open() at ScriptTask_9534937ca8d9411c863c39ba6b6d2252.vbproj.ScriptMain.Main() --- End of inner exception stack trace --- at System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner) at System.RuntimeMethodHandle.InvokeMethodFast(Object target, Object[] arguments, Signature sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner) at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks) at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture) at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams) at System.Type.InvokeMember(String name, BindingFlags invokeAttr, Binder binder, Object target, Object[] args, CultureInfo culture) at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()

I am using Visual studio 2008 version.

You may need to create a new connection manager using the correct format (in this case 2007).Jorge Segarra SQLChicken.com || Follow me on Twitter! || SQL University Please click the Mark as Answer button if a post solves your problem!

You need to use an Excel Connection Manager, not an OLE DB Connection ManagerJeff Wharton MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt) MCT, MCPD, MCITP, MCDBA Blog: Mr. Wharty's Ramblings Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker

Hi ankii, You need latest OLE DB data connection driver to perfom this task. You need “Microsoft.ACE.OLEDB.12.0” drier installed in SQL server 2008 to use it and make sure u have it in SSIS package also. If not you can download it from http://www.microsoft.com/downloads/en/details.aspx?familyid=7554f536-8c28-4598-9b72-ef94e038c891 hope this will help Thanks Ashish

Hi Ashish, I went to the above site mentioned by you but could not able to find : Microsoft.ACE.OLEDB.12.0 Can you Please verify the site again Thanks Anki

Hi Ashish, Sorry I was wrong . I got it downloaded , but now new error is coming when I ran the SSIS package: Error: The connection "{4F76B064-3C45-4F32-93FC-E5ED2D93728F}" is not found. This error is thrown by Connections collection when the specific connection element is not found. I am new to SSIS, Can you please help me out. Thanks Anki

This usually happens when a connection manager for a task/data flow cannot be found. There should be a red circle with a white 'X' on the component reporting the error and you need to set it's connectoin manager property to a valid connection managerJeff Wharton MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt) MCT, MCPD, MCITP, MCDBA Blog: Mr. Wharty's Ramblings Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker

You need to make sure that you are using an Excel Connection Manager setting to Excel 2007. The data in your excel file should well-formed for that format, i.e. making sure when you save you excel file, it is actually saving as Excel 2007.

Hi ankii, 1)Please reconnect the excel connection in the excel source to the connection manager and 2) if it is a 64 bit machine you need to install 32 bit drivers for Excel 2007. 3) Build the solution and run the package again. Happy to help! Thanks. Regards and good Wishes, Deepak.

Hi Jeff, You are right I am getting : a red circle with a white 'X' on the component reporting the error, Can you help me out what exactly properties I do need to set. Thanks Ankita

Why don't you just delete and re-create your connection to Excel from scratch?Arthur My Blog