August 8, 2013 at 2:18 am
I have 3 tables in excel source as shown below.
S.No Name TPA
1 Abc x
2 Bta y
3 Cbo z
S.No Name TPB
4 Abt P
5 Bch Q
6 Cbk R
S.No Name TPA
7 Abl M
8 Bcd N
9 Cbk P
Destination Table:
S.no Name T_value T_type
1 Abc x TPA
2 Bta y TPA
3 Cbo z TPA
4 Abt P TPB
5 Bch Q TPB
6 Cbk R TPB
.
.
.
the header name(TPA,TPB... etc) need to be inserted as a "T_type" for every record of their repective....
August 8, 2013 at 2:30 am
You can use the UNION ALL component to get the results of the three sources together.
After each source, you can use a derived column component to add the T-Type column.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 8, 2013 at 7:58 am
Thanks for your response... Here the no.of rows are dynamic in each table...
August 8, 2013 at 3:53 pm
If you are using openrowset in SQL then UNION ALL is your solution. You posted this on the SSIS forum so I am presuming not.
Create three ole data connections, one for each worksheet in the workbook
Create a dataflow task
Create three ole data sources, one for each of the above connections
add a derived column task in each stream to insert the sheet name
use the merge task to bring the three data streams together
output to whatever destination you need.
If you have 133 worksheets to do, then the solution will need to be a bit more flexible
August 9, 2013 at 12:23 am
chandra.gurrapu (8/8/2013)
Thanks for your response... Here the no.of rows are dynamic in each table...
The number of rows is not a problem.
If the number of columns are dynamic, that's a problem.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 12, 2013 at 7:16 am
Thanks for Response...
here three tables(I have 32 tables in actual source workbook) are in a singe worksheet(Say sheet1).
Here the no of rows in each table is different so I can't go with cell reference for each data flow.
August 12, 2013 at 7:19 am
thanks for response...
In short , I have excel sheet which has 32 tables... each table has header , the column in the header need to be stored as a column value for each table...
August 12, 2013 at 7:28 am
raghavender.2369 (8/12/2013)
thanks for response...In short , I have excel sheet which has 32 tables... each table has header , the column in the header need to be stored as a column value for each table...
Are you saying you want the column names of the destination tables to be the column names from the header rows in the Excel file?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 12, 2013 at 7:56 am
No No ... If you can go to the post there you can find 3 sample tables in a single excel sheet, from each table one specific column is need to be stored in the destination table. here the position of the column is fixed in all the tables. plz look at the post.
thanks for response...
August 12, 2013 at 1:07 pm
OK, why don't you add the header value into a column with a derived column component?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 12, 2013 at 4:01 pm
@Raghavendar
I am confused:-P
The problem was not well explained in your first post. I am thinking that what you have is two different record types: A header record and some lines records and you want to pull a value from the header down to the lines. This can be done with SSIS but it would be better handled with Excel VBA to get the data into a better format
Can you google for 3rd Normal Form and then present your data in 3NF; doing this will make your life MUCH easier in the long run.
As far as I am aware, SSIS will not let you pull data from part of a worksheet unless it is a named range. You really need to make the data in Excel look like the data you want in the database (i.e. one record per worksheet row, One field value per column, no data in the worksheet EXCEPT what you want to import) My experience is the Excel and SSIS do not play nicely, so you need to make the job as simple as possible.
Obiron
August 12, 2013 at 5:57 pm
Thanks a lot for your support...Yes, we can add derived column but how can I hold the column of the each table ? and I need to insert the header name as a column value in the destination , u can better understand this in attached image "capture".
Like this in a single sheet of excel I have 32 tables right now. the tables may increase as per the business or transactions... the records in each table may increase.
August 12, 2013 at 11:49 pm
In my actual source are 32 tables i can't proceed with static values in derived column. as the tables are subject to increase... how to hold the header and insert into destination?? plz look at the post once...
thanks a lot for your support.
August 13, 2013 at 4:00 pm
OK. I get the data, and your diagram is like I thought it would look.
Why can you not put in a formula in Excel to bring the TPA/TPB down onto the row so in cell C3
=IF(A2="Name",B2,C2)
then copy the formula down the page (use VBA if necessary to populate the correct number of rows)
The above formula says, if column A in the row above contains "NAME" then populate Col C with the value from Col B above, otherwise populate with Col C from above so what you would end up with is
A B C
Name TPA
abc x TPA
bca y TPA
....
tup v TPA
Name TPB
acz p TPB
...
etc..
you could then drop all of the header columns, either in Excel (preferred) or in the dataflow using a conditional split.
August 14, 2013 at 12:31 am
Hmm good Idea and that solves my problem If I can change the source(Excel) but here I don't have to change the structure.
Can you plz explain the process how exactly the conditional split useful to do loading ??? Here the column name is not coming for each record then how can I split the records into specific buckets???
I have worked on conditional split for other jobs.
thanks & appreciate for your continuous support...
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply