September 16, 2008 at 1:33 pm
Hello Friends,
I have an Excel Workbook (Newest Excel Version, which is specified as an OLE DB Source in SSIS) in which there is a sheet consisting of two columns:
Num1 and Num2.
Num1 Num2
1 0
2 1
3 -1
4 Null
Null Null
Null 5
8 6
-1 2
Now, I have a SQL Server Destination. In which I have one column as Numbers. In that column, I want both the numbers from both the above mentioned columns in a single destination column i.e., In Numbers.
I do not want to add or subtract those. I just want them with a comma (,).
Means,
It should be in the following manner in the destination.
Numbers
1, 0
2, 1
3, -1
4
Null
5
and so on..............
Is there anyone who can help me achieve this? I believe Derived Column transformation can help me out here, but I am not sure which function to use and how. I am trying still, but any help will really be appreciated. Please ask me further questions, if it is not clear from what I have mentioned.
Thank you,
notes4we
September 16, 2008 at 1:59 pm
I'm slightly concerned with your approach here as it seems what you are doing flies in the face of all things relational. Any chance you could give us some of the business logic behind this? Additionally what datatype are your attempting to insert this into? It would almost have to be a varchar. If it was you should be able to build your select statement selecting the data as
select cast(num1 as varchar) + ', ' + Cast(num2 as varchar)
-Luke.
September 16, 2008 at 2:00 pm
[edit]Luke, you got in first! That'll teach me not to refresh the page before replying!!![edit]
I think you might be able to use the SQL Command in the source drop down and convert those columns before the transformation.
SELECT CAST(num1AS VARCHAR) + ',' + CAST(num2 AS VARCHAR)
FROM [whicheverSheet$]
HTH,
September 16, 2008 at 2:04 pm
yup that's what I was meaning, but again i wanted to reiterate, that these seem to be values that depend on something else and should probably be in separate columns anyhow. IF you don't normalize your design now you may find yourself having more headaches down the road.
Hence the request for the business reason behind the combination of the two columns.
-Luke.
September 16, 2008 at 2:23 pm
Hi notes4we,
Try using this expresion in your derived column calculation
ISNULL(col1) && ISNULL(col2) ? NULL(DT_WSTR,10) : ISNULL(col1) && (!ISNULL(col2)) ? (DT_WSTR,20)col2 : ISNULL(col2) && (!ISNULL(col1)) ? (DT_WSTR,20)col1 : (DT_WSTR,20)col1 + "," + (DT_WSTR,20)col2
Note: Replace col1 with your Column1 and col2 with Column2 in your package
HTH
~Mukti
September 17, 2008 at 9:12 am
Thank you so much for all your replies.
The business logic was that there are two units associated with a single machine. So, the number fields were units that I wanted together in one.
If there are two columns UnitID1 and UnitID2, both having integers in it and if we wish to get the UnitID1 and UnitID2 as “UnitID1, UnitID2” in our destination Stations, then we use the following derived column expression. UnitID1 has no null values, but UnitID2 might have null values also.
[Copy of UnitID1] + (ISNULL([Copy of UnitID2]) ? " " : " , " + [Copy of UnitID2])
'Copy' as I have used Data Conversion transformation to change the datatypes before using derived column. Thank you once again all of you.
September 18, 2008 at 7:00 am
So why not just have a lookup table that includes the Machine and a unit value.
If there's 1 unit just have 1 row, if there's 3 then use three rows. Your data will be normalized and you'll have more flexibility moving forward, rather than having to parse that combined column if you ever need to know all of the machines that are used by UnitID 4 for instance. Instead of doing a WHERE UnitID like '%4%' you could use WHERE unitID = 4 and actually make use of indexes and such. The first one won't.
-Luke.
September 18, 2008 at 7:08 am
I think Luke's definitely got a point there. If you leave the two fields separate then you can create a UDF to display as necessary (#, #). That leaves the options open for the future (if you don't plan for it, you know it's going to happen!). :crazy:
September 19, 2008 at 10:35 am
Right and it's not just about leaving the two columns separate, but in the same row (this breaks the first normal form, see the section on repeating groups http://en.wikipedia.org/wiki/First_normal_form), but it's about using 2 rows to store the same information making your schema much more flexible and robust because at the very least it adheres to 1NF so long as you use a unique key that is... It's not just normalization for normalization's sake, it actually will help you down the road.
-Luke.
September 19, 2008 at 5:35 pm
Tongue in cheek when I say this:
Luke L .. for shame, for shame. Just
imagine what would happen to the Forums if they
followed your recommendation. If memory servers
me correctly I think in the last 3 weeks I have read
at least 5 posting requesting help on how to
get a single value from data stored in a table
column as "1,10,5,999,AB" and so on and so forth.
Just think following your advice what would you do
with your free time not having to peruse all those
postings. Why you might even be able to concentrate
on helping those with real problems.
September 20, 2008 at 2:12 am
Luke L (9/16/2008)
I'm slightly concerned with your approach here as it seems what you are doing flies in the face of all things relational. Any chance you could give us some of the business logic behind this? Additionally what datatype are your attempting to insert this into? It would almost have to be a varchar. If it was you should be able to build your select statement selecting the data asselect cast(num1 as varchar) + ', ' + Cast(num2 as varchar)
-Luke.
Always specify length for character datatypes
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/12/04/column-length-and-data-length.aspx
Failing to plan is Planning to fail
September 21, 2008 at 7:28 am
yes obviously, however I didn't want to incorrectly size the varchar column as the OP had mentioned about both being numeric so no clue as to the actual length of their possible incoming values. That's why I left it out.
Again normalizing your columns is the way to go, but if you only need a few numbers use a tinyint(0-255) or small int (+-32767).
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply