Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

how to send sql data to dynamaic excel sheets in ssis Expand / Collapse
Author
Message
Posted Saturday, September 1, 2012 9:25 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 4:16 AM
Points: 208, Visits: 4,498
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
Post #1353185
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse