InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 02:04:07 GMT20http://www.sqlservercentral.com/Forums/Topic1353185-364-1.aspxhi friend i have small doubt in ssis plz how to solve this.i have a table that table contains data likeid ,name ,location1 ,abc ,hyd2 ,ravi ,bang3 ,venu ,chen4 ,jaidu ,hyd5 ,venu ,bang6 ,fan ,chen7 ,fabi ,hyd8 ,rheu ,bangso this data load into one excel sheet in that excel sheets load data separatel based on locataion data load separte sheets likehydsheets that sheet contain hyd loacation information thats likeid,name,location1,abc,hyd4,jaidu,hyd 7,fabi,hyd .like this way simalarly load chen and bang location. all are load into one excel shheet in that excel sheet based on location it load separte sheets.and i do like this way i taken 4 variable that are name scope datatype valuerecords excelsplit object system.objectlocation excelsplit string hydexceltable excelsplit string create table 'abc_tab'('id' nvarchar(255),'name' nvarchar(255),'location' nvarchar(255))excel_tab excelsplit string hyd_tabthen in controal flow level i taken executesql tarsk in that task i takenresultset value fullresultset and connection to the database and in sql statement i write query like select distinct location from tablename and in resultset i maping variable likeresultname , variablename0 user::recordsthen i taken foreach loop container in that i taken foreach ado enumeratori select adoobjectsource variable .....user::recirdsand variable mapping variable indexlocation 0in foreach loop i taken one more executea sql task in that task i take connectin type excel and i chose sql source type ..variable and and i seelctsource variable ..user::exceltableand i taken dataflow task in foreach loopcontainer and edit dft task.in data flow level i configure the sourec table data.and inthat transformation bottom i taken conditionalsplit transformationin that i write one condition like location==user::locationand taken dataconversion transformation i change all are nonunicode datalenth 255 all. and configur to excel sheet and in cofigure i select excel data accessmoad tablename or viewname variable i chose variable ..user::execeltable.that time it doesnot configure that time it show errore likeTITLE: Microsoft Visual Studio------------------------------Error at Data Flow Task [Excel Destination [75]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.Error at Data Flow Task [Excel Destination [75]]: Opening a rowset for "CREATE TABLE `hyd_Tab` ( `Id` NVARCHAR( 255 ) , `Name` NVARCHAR( 255 ) , `location` NVARCHAR( 255 ) )" failed. Check that the object exists in the database.------------------------------ADDITIONAL INFORMATION:Exception from HRESULT: 0xC02020E8 (Microsoft.SqlServer.DTSPipelineWrap)-in case i chose execl access mode varible user::excel_tabit show errore likeTITLE: Microsoft Visual Studio------------------------------Error at Data Flow Task [Excel Destination [75]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E37.Error at Data Flow Task [Excel Destination [75]]: Opening a rowset for "hyd_tab" failed. Check that the object exists in the database.ADDITIONAL INFORMATION:Exception from HRESULT: 0xC02020E8 (Microsoft.SqlServer.DTSPipelineWrap)and i try another way to wirte expersion in dataflow level excelconfigration destination that time i write one expersion like"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+ @[User::filelocation]+"Extended Properties=\"Excel 8.0;HDR=YES\";"but it not working. plz tell where i did mistick in this task .plz tell me how to solve this issuseSat, 01 Sep 2012 21:25:37 GMTasranantha