April 3, 2013 at 1:15 pm
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
April 3, 2013 at 1:42 pm
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
April 3, 2013 at 2:31 pm
What would be the logic int his scenario.
April 3, 2013 at 2:51 pm
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?
April 3, 2013 at 2:52 pm
yes..I need to get those values in seperate columns with respective values
April 3, 2013 at 3:07 pm
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
April 3, 2013 at 3:08 pm
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;
April 3, 2013 at 3:12 pm
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.
April 3, 2013 at 3:14 pm
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.
April 3, 2013 at 3:35 pm
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.
:w00t:
April 3, 2013 at 4:13 pm
Hi the below query
Cost == "DRU" ? Values : NULL(DT_I4)
giving null values..could you please help me
April 7, 2013 at 10:14 am
Don't know if you have this figured out yet or not.
Lets break down the expression
Cost == "DRU" ? Values : NULL(DT_I4)
Cost=="DRU" is the expression or if statement if you will.
So if the Cost value for the row your on = "DRU"
? = use the value if True,
: = Use the Value if Fales
So here's what I see when running your sample data.
Server DRU
SAB245 200
SAB246 2001
SAB247 NULL
SAB248 NULL
SAB249 NULL
SAB250 NULL
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply