SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Restricting Data before using OLE DB Destination


Restricting Data before using OLE DB Destination

Author
Message
Jacob Pressures
Jacob Pressures
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1167 Visits: 410
I just used the unpivot tool. So now I have more columns than I need. I know how to map the columns. If I need to use the SQL command, i don't know how I'd use it. I know the above is an either/or situation.

Since my data has been unpivoted in an effort to normalize it, I need to make sure that the information i'm inputting into the destination is DISTINCT. What tool do i need to get DISTINCT data into my destination?

Thanks!
Evil Kraig F
Evil Kraig F
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21483 Visits: 7660
The aggregate tool. Just group by everything.

Be aware, that's a stream-stopper. It forces all rows into it before it releases any because the data is unsorted. So any following constructs in the stream won't start until the aggregate completes.

Under most circumstances I tend to do aggregations at the db tier as it's better able to handle the workload. If you're dropping this to a staging table immediately after you'll be better off (unless the volume difference is drastic) dumping everything and then SELECT DISTINCT'ing into your real table.

If it's for a flatfile or something it's just price of doing business, just try to make sure it's late in the stream.


- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)

Group: General Forum Members
Points: 53566 Visits: 21207
Jacob Pressures (2/19/2013)
I just used the unpivot tool. So now I have more columns than I need. I know how to map the columns. If I need to use the SQL command, i don't know how I'd use it. I know the above is an either/or situation.

Since my data has been unpivoted in an effort to normalize it, I need to make sure that the information i'm inputting into the destination is DISTINCT. What tool do i need to get DISTINCT data into my destination?

Thanks!


Quick note: if your source is an RDBMS, it would be faster (probably much faster) to do all of this using SQL rather than SSIS.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Jacob Pressures
Jacob Pressures
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1167 Visits: 410
Thanks guys! It sounds like doing the staging table is the easiest. This is just an exercise for my internship to help me understand how to use SSIS. I guess i'll do it both ways for practice.

If i use a staging table, I'm assuming I'd have to end the data flow add another data flow to the Control flow and link the two. I'd then pull the distinct data out of the staging tables and place it in the real destination tables.

This is my understanding. Any alternatives? Once I put something into a destination table can i take it out in the same data flow? This is why i'm thinking I'd need two data flows.

Thanks!
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)

Group: General Forum Members
Points: 53566 Visits: 21207
There are almost always alternatives, and knowing the pros and cons of each is a really useful thing. In this case, once data is in a staging table I would probably run a T-SQL Merge to get the data to its destination, unless it's all INSERTS, in which case your suggested method will work fine.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Jacob Pressures
Jacob Pressures
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1167 Visits: 410
Thanks very much!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search