how to send sql data to dynamaic excel sheets in ssis

• asranantha

  SSCarpal Tunnel

  Points: 4246

  More actions

  September 1, 2012 at 9:25 pm

  #147465

  hi friend i have small doubt in ssis plz how to solve this.

  i have a table that table contains data like

  id ,name ,location

  1 ,abc ,hyd

  2 ,ravi ,bang

  3 ,venu ,chen

  4 ,jaidu ,hyd

  5 ,venu ,bang

  6 ,fan ,chen

  7 ,fabi ,hyd

  8 ,rheu ,bang

  so this data load into one excel sheet in that excel sheets load data separatel based on locataion data load separte sheets like

  hydsheets that sheet contain hyd loacation information thats like

  id,name,location

  1,abc,hyd

  4,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 value

  records excelsplit object system.object

  location excelsplit string hyd

  exceltable excelsplit string create table 'abc_tab'('id' nvarchar(255),'name' nvarchar(255),'location' nvarchar(255))

  excel_tab excelsplit string hyd_tab

  then in controal flow level i taken executesql tarsk in that task i taken

  resultset 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 like

  resultname , variablename

  0 user::records

  then i taken foreach loop container in that i taken foreach ado enumerator

  i select adoobjectsource variable .....user::recirds

  and variable mapping variable index

  location 0

  in 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 seelct

  source variable ..user::exceltable

  and 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 transformation

  in that i write one condition like location==user::location

  and 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 like

  TITLE: 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_tab

  it show errore like

  TITLE: 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 issuse