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 12»»

Query help for Data pulling Expand / Collapse
Author
Message
Posted Wednesday, April 3, 2013 1:15 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, April 24, 2014 8:19 PM
Points: 56, Visits: 240
I need to pull the info from excel to the Database table through SSIS. I need logic for below scenario.

Excel Look like below:

Server Cost Values
SAB245 DRU 200
SAB246 DRU 2001
SAB247 TAPE 300
SAB248 TAPE 3001
SAB249 DISK 100
SAB250 DISK 1001


Output table should be:

Server DRU TAPE Disk
SAB245 200 Null Null
SAB246 2001 Null Null
SAB247 Null 300 Null
SAB248 Null 3001 Null
SAB249 Null Null 100
SAB250 Null Null 1001

Please let me know if you need more explanation.

thanks
Post #1438554
Posted Wednesday, April 3, 2013 1:42 PM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Yesterday @ 11:30 AM
Points: 1,485, Visits: 1,034
You need to place a derived column transformation between your source and your Destination Containers.

Here is a tutorial on how to use the derived column transformation
http://sqlblog.com/blogs/andy_leonard/archive/2009/02/04/ssis-expression-language-and-the-derived-column-transformation.aspx
Post #1438562
Posted Wednesday, April 3, 2013 2:31 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, April 24, 2014 8:19 PM
Points: 56, Visits: 240
What would be the logic int his scenario.
Post #1438573
Posted Wednesday, April 3, 2013 2:51 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 6:26 PM
Points: 20,732, Visits: 32,496
DBA12345 (4/3/2013)
I need to pull the info from excel to the Database table through SSIS. I need logic for below scenario.

Excel Look like below:

Server Cost Values
SAB245 DRU 200
SAB246 DRU 2001
SAB247 TAPE 300
SAB248 TAPE 3001
SAB249 DISK 100
SAB250 DISK 1001


Output table should be:

Server DRU TAPE Disk
SAB245 200 Null Null
SAB246 2001 Null Null
SAB247 Null 300 Null
SAB248 Null 3001 Null
SAB249 Null Null 100
SAB250 Null Null 1001

Please let me know if you need more explanation.

thanks


Are DRU, TAPE, and DISK the only values you have to worry about here?



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1438582
Posted Wednesday, April 3, 2013 2:52 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, April 24, 2014 8:19 PM
Points: 56, Visits: 240
yes..I need to get those values in seperate columns with respective values
Post #1438583
Posted Wednesday, April 3, 2013 3:07 PM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Yesterday @ 11:30 AM
Points: 1,485, Visits: 1,034
In the Derived column transformation you add 3 rows.
and the expression for DRU would be a very simple one.

Cost == "DRU" ? Values : NULL(DT_I4)

Of course repeat the process for the Disk and Tape by adding 2 more derived columns.

Edit: Added Image


  Post Attachments 
example.jpg (5 views, 228.31 KB)
Post #1438589
Posted Wednesday, April 3, 2013 3:08 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 6:26 PM
Points: 20,732, Visits: 32,496
Does this help you figure out how to query the data into a format you can use? Personally, I'd load the data as is from Excel into a staging table then pivot it from there using SQL.



declare @TestTable table(
ServerName varchar(10),
Cost varchar(4),
Value int);

insert into @TestTable
values
('SAB245','DRU',200),
('SAB246','DRU',2001),
('SAB247','TAPE',300),
('SAB248','TAPE',3001),
('SAB249','DISK',100),
('SAB250','DISK',1001);


select
ServerName,
DRU,
[TAPE],
[DISK]
from
(select
ServerName,
max(case Cost when 'DRU' then Value end) as DRU,
max(case Cost when 'TAPE' then Value end) as [TAPE],
max(case Cost when 'DISK' then Value end) as [DISK]
from
@TestTAble
group by
ServerName
)dt;





Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1438590
Posted Wednesday, April 3, 2013 3:12 PM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Yesterday @ 11:30 AM
Points: 1,485, Visits: 1,034
Lynn Pettis (4/3/2013)
Does this help you figure out how to query the data into a format you can use? Personally, I'd load the data as is from Excel into a staging table then pivot it from there using SQL.



declare @TestTable table(
ServerName varchar(10),
Cost varchar(4),
Value int);

insert into @TestTable
values
('SAB245','DRU',200),
('SAB246','DRU',2001),
('SAB247','TAPE',300),
('SAB248','TAPE',3001),
('SAB249','DISK',100),
('SAB250','DISK',1001);


select
ServerName,
DRU,
[TAPE],
[DISK]
from
(select
ServerName,
max(case Cost when 'DRU' then Value end) as DRU,
max(case Cost when 'TAPE' then Value end) as [TAPE],
max(case Cost when 'DISK' then Value end) as [DISK]
from
@TestTAble
group by
ServerName
)dt;



This would work if you were pulling from sql, however since pulling from excel it would be ineffective.
Post #1438593
Posted Wednesday, April 3, 2013 3:14 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 6:26 PM
Points: 20,732, Visits: 32,496
Ray M (4/3/2013)
Lynn Pettis (4/3/2013)
Does this help you figure out how to query the data into a format you can use? Personally, I'd load the data as is from Excel into a staging table then pivot it from there using SQL.



declare @TestTable table(
ServerName varchar(10),
Cost varchar(4),
Value int);

insert into @TestTable
values
('SAB245','DRU',200),
('SAB246','DRU',2001),
('SAB247','TAPE',300),
('SAB248','TAPE',3001),
('SAB249','DISK',100),
('SAB250','DISK',1001);


select
ServerName,
DRU,
[TAPE],
[DISK]
from
(select
ServerName,
max(case Cost when 'DRU' then Value end) as DRU,
max(case Cost when 'TAPE' then Value end) as [TAPE],
max(case Cost when 'DISK' then Value end) as [DISK]
from
@TestTAble
group by
ServerName
)dt;



This would work if you were pulling from sql, however since pulling from excel it would be ineffective.


Not if you load it into a staging table in SQL first then use SQL to complete the transform to the final table as I suggested.




Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1438594
Posted Wednesday, April 3, 2013 3:35 PM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Yesterday @ 11:30 AM
Points: 1,485, Visits: 1,034
Not if you load it into a staging table in SQL first then use SQL to complete the transform to the final table as I suggested.




Lynn Pettis

Sure it would but missed that part.
Post #1438602
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse