how to split excelspread sheets in ssis

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SSIS and Import/Export (2008)

how to split excelspread sheets in ssis

Author

Topic

srinu522426
Starting Member

3 Posts

Posted - 2012-08-25 : 03:16:04

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-25 : 13:33:00

if they're all in same sheet then you dont need to split them onto separate sheets. just use data flow task with excel source to connect to excel, then use conditional split transform to split based on city. then link each output to a oldb destination to populate required tables

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

srinu522426
Starting Member

3 Posts

Posted - 2012-08-25 : 14:20:23

u r understanding wrong way. actualy source data comes database table.
based one that table what ever based on locaton we load data separatesheets

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-25 : 17:07:14

quote:
Originally posted by srinu522426

u r understanding wrong way. actualy source data comes database table.
based one that table what ever based on locaton we load data separatesheets

ok. even in that case

as i told you can add data flow task with OLEDB source to connect to table then a conditional split to split rows based on city. And in final step connect the various outputs to different excel destinations to create different excel sheets with city data

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

srinu522426
Starting Member

3 Posts

Posted - 2012-08-26 : 01:10:26

what ever u told logic that way i do already i given very clearly while posting the form.that time is not configure the destination excel sheets dynamicaly.plz read what ever i post the form.and tell me how to soislve this issu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-26 : 12:45:50

quote:
Originally posted by srinu522426

what ever u told logic that way i do already i given very clearly while posting the form.that time is not configure the destination excel sheets dynamicaly.plz read what ever i post the form.and tell me how to soislve this issu

sorry i didnt get you

can you illustrate your issue with some sample data?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources