Hi All,First of all want to thank everyone for thesolutions I recieved for my previous question. Now I have a new question:I am using VS 2005 and SQL 2005.My database name: MY_DBI have two tables in my database: TEMP and FINALFields in both Tables as well as excel sheets : CUST_ID, NAME, ORDER_NO.I have to import an excel sheet to FINAL table using the TEMP as temporary destination. The steps are given below: 1. Check if the TEMP table is already present in database (MY_DB) a. If Present then DROP TEMP table and create TEMP table. Go to step 2. b. If Not Present then CREATE TEMP table. Go to step 2. 2. Push data from excel to TEMP (Using Dataflow Task) table.Go to STEP 3. 3. Truncate FINAL table. Push data from TEMP to FINAL (Using Dataflow Task). Go to step 4. 4. DROP TEMP Table. I can do step 2 onwards. But I don't know how to do step 1. Please help.ThanksPartha

Create a Execute SQL Tasks and specify the following in the General tab,1. Connection type:LOLE DB2. Connection: Connection manager of your db(MY_DB)3. SQLSourceType: Direct input4. SQLStatement as below query(Modify as per your requirement) IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TEMP]') AND type in (N'U')) DROP TABLE [dbo].[TEMP ] GO CREATE TABLE [dbo].[TEMP ] ( CUST_ID int, NAME DataType, ORDER_NO int ) The above query will handle both of your scenario's mentioned in STEP 1. If you want to maintain this script with ease, place this script in a proc and invoke this proc from the execute sql task.Post back if any issues. Thanks, Bharani M - Please mark the post as answered if it answers your question. - Please vote the post as Helpful if you find the post as helpful.

A simple Execute SQL task should do the trick, executing a stored procedure similar to the following. This way, you don't have to handle anyextra logic in your SSIS package: CREATE PROC dbo.CreateTempTable AS IF EXISTS (SELECT * FROM sys.objects WHERE name = 'TEMP') BEGIN DROP TABLE dbo.Temp END CREATE TABLE dbo.Temp (CUST_ID INT, NAME VARCHAR(100), ORDER_NO INT) GO every day is a school day

Hi All,Its working PERFECTLY !!!Thanks a lot :-)Just one more question I want to ask... Is there any book available which I can refer to learn SSIS, DTS and all the controls present in it ?Regards,Partha

Partha, For all MS tools MSDN is your tutor. This would be the best link to start with learning SSIS http://msdn.microsoft.com/en-us/library/ms141091.aspx Control flow:http://msdn.microsoft.com/en-us/library/ms141664.aspx Data flow:http://msdn.microsoft.com/en-us/library/ms137612.aspx SSIS:http://msdn.microsoft.com/en-us/library/ms141026.aspxThanks, Bharani M - Please mark the post as answered if it answers your question. - Please vote the post as Helpful if you find the post as helpful.

Hi Bharani,Please refer to my initial Question which is there at the top.In STEP3 : The Truncation of FINAL table is executing.Next, I have a dataflow task in step 3. "Push data from TEMP to FINAL (Using Dataflow Task). ". Here I havetaken an OLEDB source(For TEMP TABLE)-> then a DATA CONVERSION -> and then push to SQL DESTINATION (For FINAL TABLE). But the problem is,in OLEDB Source I am not getting the TEMP table name in the dropdown list in connection manager. Because the TEMP TABLE is being created in runtime.Kindly need your help.....RegardsPartha.

Partha,Any how it is created in the previous step, so you can write query instead of taking table from drop down list. Here you need to selectdata access mode as 'Sql Command' instead of 'Table or view' in theOLEDB Source. Ex : select * from dbo.temp Lakshman

This is the known problem while developing packages which has "on the fly" table creation script.Just for the development purpose while developing the DFT(step 3 in your case)which uses the temp table,create the table upfront and do modification's to your DFT as required. Once the development is over just drop the table.Also dont forgt to set the Delay Validation property to True for all the tasks(DFT and Connection Manager) which uses the temp table, without this property change the package will not be able to build. Thanks, Bharani M - Please mark the post as answered if it answers your question. - Please vote the post as Helpful if you find the post as helpful.

Bharani,EXCELLENT !!!I have only changed the DELAY VALIDATION to TRUE and its working :-)Once again thanks brother.Regards,Partha.

Pleasure! Also mark the post(s) as answered/vote as helpfulwhere i have provided links and other stuff's which you requested.Thanks, Bharani M - Please mark the post as answered if it answers your question. - Please vote the post as Helpful if you find the post as helpful.

Hi Bharani,I have a new problem. Please refer tothe same table structurein my first question.The new problem is... I have Multiple workbooks and each workbooks are having multiple number of sheets. The names of the workbooks and sheets does not have any common types or pattern like sheet1, sheet2, sheet3, and so on. But the structure/data remains the same i.e. CUST_ID, NAME, ORDER_NO. I have found a solution to browse thru multiple workbooks and it is picking data only from the first sheet. I have done it by declaring a Package variable and called it in variable mappings of the FOR EACH LOOP. After that I have given the path of the Folder where the files are kept. And in the data flow task I hav taken an excel source and linked it to SQL Destination. And its working.. But my problem is... isthere any way thru which I can traverse multiple sheets... It would be really great if u can explain the steps coz I am just a beginner in SSIS. And thats why I am asking this kind basic doubts !!!Pls help.Regards,Partha

So you have a folder with multiple excel workbooks and each workbook has different number of worksheets.And the structure of all the sheets are same.1.Create a ADO.NET Connection Select .NET Providers for OLEDB\Microsoft Jet 4.0 OLEDB Provider. Select the excel workbook using Browse Go to All and set Extended Properties to Excel 8.0 2.Create a variable as SheetName with value as a valid sheet (Inof$) 3. Take a Foreach loop and inside it take a DFT Edit the For Each Loop: Collection: Foreach ADO.NET Shema Rowset Enumerator Enumerator Configuration: Select the ADO.NET Connection created above. Select Table as Schema. Variable Mapping: Select a variable (SheetName) that will capture the sheet name and set the index as 2. Take the excel source in DFT and define a excel connection manager (MYExcel) for it. Select the data access mode as TableName/ViewName variable and select the SheetName variable from the drop down box.Now complete the data flow as per your requirement. Note: This will work for one excel work book.For multiple workbooks you need one more foreach loop and add the earlier defined foreach loop inside this.Select the collection as for each file enumeraror. Selec the folder where the workbooks are located.Retrieve file name: fully qualified.Capture the workbook name in a variable (excelfilepath) using Varibale Mapping.Give a valid filepath to this variable while creating. (C:\A.xls)Then go to MYExcel connection manager's Property. Go to Expressions and select the Conenction String property. Under Expressions, click on ellipsis (...) and write"Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " + @[User::excelfilepath] + ";Extended Properties=" +"\"EXCEL 8.0;HDR=YES;;" + "\";"Nitesh Rai- Please mark the post as answered if it answers your question

Partha,I got ya problem! Let me give it a try:)I would suggest you to go through the below link which describes the solution for your problemhttp://bi-polar23.blogspot.com/2007/09/loading-multiple-excel-files-with-ssis.htmlYou may also need to read the below link before starting your implementation.http://bi-polar23.blogspot.com/2007/08/loading-multiple-excel-files-with-ssis.htmlPost back if you have any query. Thanks, Bharani M - Please mark the post as answered if it answers your question. - Please vote the post as Helpful if you find the post as helpful.

Hi Bharani,I already have this link. From the above links I have learnt how to import data from multiple woorkbooks. But I am not able to follow what he has explained for multiple sheets.Please help.Partha

Hi Nitesh,As per what you have explained from step 1 to 3.. I have done that. But it is importing data only from the firstsheet. I want it to traverse for all the sheets present in the workbook.Please help.Partha.

Where you are facing difficulties??Will you have the list of sheets from where you need to load the data? I mean before running the package?Thanks, Bharani M - Please mark the post as answered if it answers your question. - Please vote the post as Helpful if you find the post as helpful.

How you have configured the Excel Source and its connection manager inside the DFT?Nitesh Rai- Please mark the post as answered if it answers your question

Bharani,The actual scenario is... I have a single folder where users will just dump the excel files. The files may have multiple sheets or may not. And the file names or sheet names are not similar. So, at the end of each day I will be running the package and it should pull data. The only common thing for all the files and sheets is that the columns are ordered exactly in the same order i.e. CUST_ID, NAME, ORDER_NO. The number of files will vary for each day.Hope now u hav a clearer picture of my package.Partha

Bharani,The actual scenario is... I have a single folder where users will just dump the excel files. The files may have multiple sheets or may not. And the file names or sheet names are not similar. So, at the end of each day I will be running the package and it should pull data. The only common thing for all the files and sheets is that the columns are ordered exactly in the same order i.e. CUST_ID, NAME, ORDER_NO. The number of files will vary for each day.Hope now u hav a clearer picture of my package.Partha Whatever I have explained in my post is exactly for such a scenario.Nitesh Rai- Please mark the post as answered if it answers your question

Hi Nitesh,First I would like to appreciate your patience in going thru my doubts and then giving a solution :-)I have followed whatever u have explained above. And it is working in a very weird manner !!!SCENARIO I: Excel File inside the folder: Book1.xls -> Sheet1(1record).Book2.xls -> Sheet1(4records), Sheet2(2records), Sheet3(3records), Sheet4(1record).Books3.xls -> Sheet1(4records), Sheet2(2records), Sheet3(1record).After running the package it pulls data from Book1.Sheet1(1)+ Book2.Sheet2(4)+ Books3.Sheet1(4)=9records.SCENARIO II: Excel File inside the folder: Book1.xls -> Sheet1(4records), Sheet2(2records), Sheet3(3records), Sheet4(1record).Book2.xls -> Sheet1(1record).It does not enters the Books3.xlsAfter running the package it pulls data from Book1.Sheet1(4)+Book1.Sheet2(2)+Book1.Sheet3(3)+Book1.Sheet4(1)+ Book2.Sheet1(1) =11records !!!And so on....I am pasting the error code it shows: ######################################ERROR######################################################## SSIS package "Package2.dtsx" starting. Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning. Warning: 0x802092A7 at Data Flow Task, SQL Server Destination [56]: Truncation may occur due to inserting data from data flow column "Copy of name" with a length of 255 to database column "NAME" with a length of 100. Information: 0x40043006 at Data Flow Task, DTS.Pipeline: Prepare for Execute phase is beginning. Information: 0x40043007 at Data Flow Task, DTS.Pipeline: Pre-Execute phase is beginning. Information: 0x4004300C at Data Flow Task, DTS.Pipeline: Execute phase is beginning. Information: 0x40043008 at Data Flow Task, DTS.Pipeline: Post Execute phase is beginning. Information: 0x40043009 at Data Flow Task, DTS.Pipeline: Cleanup phase is beginning. Information: 0x4004300B at Data Flow Task, DTS.Pipeline: "component "SQL Server Destination" (56)" wrote 4 rows. Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning. Warning: 0x802092A7 at Data Flow Task, SQL Server Destination [56]: Truncation may occur due to inserting data from data flow column "Copy of name" with a length of 255 to database column "NAME" with a length of 100. Information: 0x40043006 at Data Flow Task, DTS.Pipeline: Prepare for Execute phase is beginning. Information: 0x40043007 at Data Flow Task, DTS.Pipeline: Pre-Execute phase is beginning. Information: 0x4004300C at Data Flow Task, DTS.Pipeline: Execute phase is beginning. Information: 0x40043008 at Data Flow Task, DTS.Pipeline: Post Execute phase is beginning. Information: 0x40043009 at Data Flow Task, DTS.Pipeline: Cleanup phase is beginning. Information: 0x4004300B at Data Flow Task, DTS.Pipeline: "component "SQL Server Destination" (56)" wrote 2 rows. Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning. Warning: 0x802092A7 at Data Flow Task, SQL Server Destination [56]: Truncation may occur due to inserting data from data flow column "Copy of name" with a length of 255 to database column "NAME" with a length of 100. Information: 0x40043006 at Data Flow Task, DTS.Pipeline: Prepare for Execute phase is beginning. Information: 0x40043007 at Data Flow Task, DTS.Pipeline: Pre-Execute phase is beginning. Information: 0x4004300C at Data Flow Task, DTS.Pipeline: Execute phase is beginning. Information: 0x40043008 at Data Flow Task, DTS.Pipeline: Post Execute phase is beginning. Information: 0x40043009 at Data Flow Task, DTS.Pipeline: Cleanup phase is beginning. Information: 0x4004300B at Data Flow Task, DTS.Pipeline: "component "SQL Server Destination" (56)" wrote 3 rows. Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning. Warning: 0x802092A7 at Data Flow Task, SQL Server Destination [56]: Truncation may occur due to inserting data from data flow column "Copy of name" with a length of 255 to database column "NAME" with a length of 100. Information: 0x40043006 at Data Flow Task, DTS.Pipeline: Prepare for Execute phase is beginning. Information: 0x40043007 at Data Flow Task, DTS.Pipeline: Pre-Execute phase is beginning. Information: 0x4004300C at Data Flow Task, DTS.Pipeline: Execute phase is beginning. Information: 0x40043008 at Data Flow Task, DTS.Pipeline: Post Execute phase is beginning. Information: 0x40043009 at Data Flow Task, DTS.Pipeline: Cleanup phase is beginning. Information: 0x4004300B at Data Flow Task, DTS.Pipeline: "component "SQL Server Destination" (56)" wrote 1 rows. Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning. Warning: 0x802092A7 at Data Flow Task, SQL Server Destination [56]: Truncation may occur due to inserting data from data flow column "Copy of name" with a length of 255 to database column "NAME" with a length of 100. Information: 0x40043006 at Data Flow Task, DTS.Pipeline: Prepare for Execute phase is beginning. Information: 0x40043007 at Data Flow Task, DTS.Pipeline: Pre-Execute phase is beginning. Information: 0x4004300C at Data Flow Task, DTS.Pipeline: Execute phase is beginning. Information: 0x40043008 at Data Flow Task, DTS.Pipeline: Post Execute phase is beginning. Information: 0x40043009 at Data Flow Task, DTS.Pipeline: Cleanup phase is beginning. Information: 0x4004300B at Data Flow Task, DTS.Pipeline: "component "SQL Server Destination" (56)" wrote 1 rows. Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning. Warning: 0x802092A7 at Data Flow Task, SQL Server Destination [56]: Truncation may occur due to inserting data from data flow column "Copy of name" with a length of 255 to database column "NAME" with a length of 100. Error: 0xC0202009 at Data Flow Task, Excel Source [1]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. Error: 0xC02020E8 at Data Flow Task, Excel Source [1]: Opening a rowset for "Sheet2$" failed. Check that the object exists in the database. Error: 0xC004706B at Data Flow Task, DTS.Pipeline: "component "Excel Source" (1)" failed validation and returned validation status "VS_ISBROKEN". Error: 0xC004700C at Data Flow Task, DTS.Pipeline: One or more component failed validation. Error: 0xC0024107 at Data Flow Task: There were errors during task validation. Warning: 0x80019002 at BROWSE FOR MULTIPLE SHEETS INSIDE WORKBOOK: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (6) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. Warning: 0x80019002 at BROWSE FOR MULTIPLE WOORKBOOKS: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (6) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. Warning: 0x80019002 at Package2: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (6) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. SSIS package "Package2.dtsx" finished: Failure. ######################################ERROR################################################Please help

Partha,The data length for NAME column is 100 but in source it seems it is more than that (i.e. 255). Kindly increase the size of the NAME column and reexecute the package. Thanks, Bharani M - Please mark the post as answered if it answers your question. - Please vote the post as Helpful if you find the post as helpful.

Bharani,I tried after increasing the column size. But of no use :-(Moreover it was showing as warning. Please check the ERROR part.Thanks,Partha

My mistake!theerror is "Opening a rowset for "Sheet2$" failed. Check that the object exists in the database". Which means it is trying to open a sheet of name Sheet2$ in an excel which doesnot exists.Do a validation check on a sheet name which is getting passed to the DFT to ensure that it exists in the excel.Just to cross verify do you have 2 foreach loop as suggested by nitesh??Thanks, Bharani M - Please mark the post as answered if it answers your question. - Please vote the post as Helpful if you find the post as helpful.

As you are able to loop through all the workbooks, it means outer for each loop is working properly.I think the problem is with the way you have configured the inner for each loop. Please share the configurtion of inner for each loop.Nitesh Rai- Please mark the post as answered if it answers your question

Bharani,Yes.. I am using 2 ForEach Loops. One inside the other.partha

Nitesh,OK.I llexplain...I have taken a DataFlowTask(DFT) inside the inner ForEeachLoop(FEL). In the DFT I have taken an Excel Source.Connection Manager: OLE DB Connection Manager=Excel Connection Manager.Data Access Mode: Table Name or View Name VariableVariable Name: User::SHEETNAMEIn Connection Manager Expression : "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " + @[User::excelfilepath] + ";Extended Properties=" +"\"EXCEL 8.0;HDR=YES;;" + "\";"Then I have connected the excel source to SQL Table.I have configured the FEL in this way :COLLECTION ->Enumerator: Foreach ADO.NET Schema Rowset Enumerator.Connection: Book1( I have taken this name as my ado.net connection)Schema: TablesVARIABLE MAPPINGS ->Variable ---> User::SHEETNAMEIndex ---> 2Thats all !!!Please let me know if u need any other details....ThanksPartha

Niersh,OK.I llexplain...I have taken a DataFlowTask(DFT) inside the inner ForEeachLoop(FEL). In the DFT I have taken an Excel Source.Connection Manager: OLE DB Connection Manager=Excel Connection Manager.Data Access Mode: Table Name or View Name VariableVariable Name: User::SHEETNAMEIn Connection Manager Expression : "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " + @[User::excelfilepath] + ";Extended Properties=" +"\"EXCEL 8.0;HDR=YES;;" + "\";"Then I have connected the excel source to SQL Table.Thats all !!!Please let me know if u need any other details....ThanksPartha Okay...Is there any default value for variable SheetName?Is variable mapping done for SheetName using Variable Mappings tab in the inner for each loop editor using the Index as 2?Nitesh Rai- Please mark the post as answered if it answers your question

Yes Nitesh....Default value for variable SHEETNAME = Sheet1$And Variable Mapping is already done for the inner FEL using index 2.

Dont give the default value.And make the "Validate External Metadata" property of Excel Source as False and let us know your observationNitesh Rai- Please mark the post as answered if it answers your question

Hi Nitesh,I have made both the changes but still it is not working. Now it is says "Opening a rowset for "Sheet2$" failed".I have pasted the error below :############################################################################################### SSIS package "Package2.dtsx" starting. Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning. Information: 0x40043006 at Data Flow Task, DTS.Pipeline: Prepare for Execute phase is beginning. Information: 0x40043007 at Data Flow Task, DTS.Pipeline: Pre-Execute phase is beginning. Information: 0x4004300C at Data Flow Task, DTS.Pipeline: Execute phase is beginning. Information: 0x40043008 at Data Flow Task, DTS.Pipeline: Post Execute phase is beginning. Information: 0x40043009 at Data Flow Task, DTS.Pipeline: Cleanup phase is beginning. Information: 0x4004300B at Data Flow Task, DTS.Pipeline: "component "SQL Server Destination" (56)" wrote 4 rows. Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning. Information: 0x40043006 at Data Flow Task, DTS.Pipeline: Prepare for Execute phase is beginning. Information: 0x40043007 at Data Flow Task, DTS.Pipeline: Pre-Execute phase is beginning. Information: 0x4004300C at Data Flow Task, DTS.Pipeline: Execute phase is beginning. Information: 0x40043008 at Data Flow Task, DTS.Pipeline: Post Execute phase is beginning. Information: 0x40043009 at Data Flow Task, DTS.Pipeline: Cleanup phase is beginning. Information: 0x4004300B at Data Flow Task, DTS.Pipeline: "component "SQL Server Destination" (56)" wrote 2 rows. Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning. Information: 0x40043006 at Data Flow Task, DTS.Pipeline: Prepare for Execute phase is beginning. Information: 0x40043007 at Data Flow Task, DTS.Pipeline: Pre-Execute phase is beginning. Information: 0x4004300C at Data Flow Task, DTS.Pipeline: Execute phase is beginning. Information: 0x40043008 at Data Flow Task, DTS.Pipeline: Post Execute phase is beginning. Information: 0x40043009 at Data Flow Task, DTS.Pipeline: Cleanup phase is beginning. Information: 0x4004300B at Data Flow Task, DTS.Pipeline: "component "SQL Server Destination" (56)" wrote 3 rows. Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning. Information: 0x40043006 at Data Flow Task, DTS.Pipeline: Prepare for Execute phase is beginning. Information: 0x40043007 at Data Flow Task, DTS.Pipeline: Pre-Execute phase is beginning. Information: 0x4004300C at Data Flow Task, DTS.Pipeline: Execute phase is beginning. Information: 0x40043008 at Data Flow Task, DTS.Pipeline: Post Execute phase is beginning. Information: 0x40043009 at Data Flow Task, DTS.Pipeline: Cleanup phase is beginning. Information: 0x4004300B at Data Flow Task, DTS.Pipeline: "component "SQL Server Destination" (56)" wrote 1 rows. Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning. Information: 0x40043006 at Data Flow Task, DTS.Pipeline: Prepare for Execute phase is beginning. Information: 0x40043007 at Data Flow Task, DTS.Pipeline: Pre-Execute phase is beginning. Information: 0x4004300C at Data Flow Task, DTS.Pipeline: Execute phase is beginning. Information: 0x40043008 at Data Flow Task, DTS.Pipeline: Post Execute phase is beginning. Information: 0x40043009 at Data Flow Task, DTS.Pipeline: Cleanup phase is beginning. Information: 0x4004300B at Data Flow Task, DTS.Pipeline: "component "SQL Server Destination" (56)" wrote 1 rows. Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning. Information: 0x40043006 at Data Flow Task, DTS.Pipeline: Prepare for Execute phase is beginning. Information: 0x40043007 at Data Flow Task, DTS.Pipeline: Pre-Execute phase is beginning. Error: 0xC0202009 at Data Flow Task, Excel Source [1]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. Error: 0xC02020E8 at Data Flow Task, Excel Source [1]: Opening a rowset for "Sheet2$" failed. Check that the object exists in the database. Error: 0xC004701A at Data Flow Task, DTS.Pipeline: component "Excel Source" (1) failed the pre-execute phase and returned error code 0xC02020E8. Information: 0x40043009 at Data Flow Task, DTS.Pipeline: Cleanup phase is beginning. Information: 0x4004300B at Data Flow Task, DTS.Pipeline: "component "SQL Server Destination" (56)" wrote 0 rows. Task failed: Data Flow Task Warning: 0x80019002 at BROWSE FOR MULTIPLE SHEETS INSIDE WORKBOOK: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (3) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. Warning: 0x80019002 at BROWSE FOR MULTIPLE WOORKBOOKS: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (3) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. Warning: 0x80019002 at Package2: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (3) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. SSIS package "Package2.dtsx" finished: Failure. ###############################################################################################

Can you check if SheetName varibale is capturing all the sheet names on each iteration usng a Script Task?Just disable the Data Flow task in inner for each loop and add a script task inside inner for each loop.Make SheetName as Read varibale and write this line :System.Windows.MessageBox.Show(Dts.Variables("SheetName").Value.ToString())Check if all the sheets are being captured or not Nitesh Rai- Please mark the post as answered if it answers your question

I think it is looking for a Sheet2$ in a excel where sheet2 doesnot exists. As i said earlier validate the sheets details as suggested by nitesh.Thanks, Bharani M - Please mark the post as answered if it answers your question. - Please vote the post as Helpful if you find the post as helpful.

The problem is with the way you are setting the connection string property of the excel connection manager.Please visit my blog at http://cr9itesh.blogspot.com/which covers the same requirement as you have.Nitesh Rai- Please mark the post as answered if it answers your question

Hi Nitesh,I tried to modify my project based on whatever u have done in ur above blog. Till Step: 4 I hav done. The problem is withstep 5. In "Excel Source Editor" I have done all the steps and it is fine. But when I go to Excel Connection Manager -> Properties -> Expressions and enter the connection String an error shows in excel connection.... I have pasted the error message....TITLE: Microsoft Visual Studio------------------------------ =================================== Error at Package2 [Connection manager "Excel 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: "Invalid argument.". Error at Data Flow Task [Excel Source [318]]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. (Microsoft Visual Studio) =================================== Exception from HRESULT: 0xC020801C (Microsoft.SqlServer.DTSPipelineWrap) ------------------------------Program Location: at Microsoft.SqlServer.Dts.Pipeline.Wrapper.CManagedComponentWrapperClass.AcquireConnections(Object pTransaction) at Microsoft.DataTransformationServices.Design.PipelineUtils.AcquireConnections(IDTSComponentMetaData90 componentMetadata, Connections connections, IServiceProvider serviceProvider) at Microsoft.DataTransformationServices.DataFlowUI.DataFlowComponentUI.ReinitializeMetadata() at Microsoft.DataTransformationServices.DataFlowUI.DataFlowAdapterUI.connectionPage_SaveConnectionAttributes(Object sender, ConnectionAttributesEventArgs args) Please help brother.Regards,Partha

Is the connection string getting evaluated correctly using "Evaluate Expression" button in Expression Builder while setting the property for excel conneciton manager? Nitesh Rai- Please mark the post as answered if it answers your question

Yes... It is getting evaluated properly. But the error symbol comes next to the Excel Source the moment I clickOK in the "Property Expressions Editor".:-(

Okay..In the excel conection manager you must be using the varibale that is used to capture the excel path (i have used excelpath as the varibale name in the blog.) I have given a proper default value to this variable. Check if you are setting any default value for it. Use a genuine file so that you can do the mappings.Nitesh Rai- Please mark the post as answered if it answers your question

Cheers Nitesh :-)ITS WORKING !!!This is the first time it ran properly. I would like to thank you and also Bharani for the support which you both gave to me.Let me test this with actual data... which is more than 10 lakhs rows.Will get in touch with you if required.Once Again... Thanks a ton.Regards,Partha

My mistake! the error is "Opening a rowset for "Sheet2$" failed. Check that the object exists in the database". Which means it is trying to open a sheet of name Sheet2$ in an excel which doesnot exists. Do a validation check on a sheet name which is getting passed to the DFT to ensure that it exists in the excel. Just to cross verify do you have 2 foreach loop as suggested by nitesh?? Thanks, Bharani M - Please mark the post as answered if it answers your question. - Please vote the post as Helpful if you find the post as helpful. Hi I'm also having same problem... please see my question on link : http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/e1ac5304-37a3-42bd-bd30-7d1cb187340f Ashish Fugat (9960978134) Software Engineer