how to send sql data to dynamaic excel sheets in ssis

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Data Warehousing » Integration Services » how to send sql data to dynamaic excel sheets...

how to send sql data to dynamaic excel sheets in ssis

Rate Topic

Display Mode

Topic Options

Author

Message

asranantha

Posted Saturday, September 01, 2012 9:25 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 8:20 AM
Points: 162, Visits: 3,810

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 »

Permissions