Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Exotic Joins in T-SQL and How They Translate in SSIS


Exotic Joins in T-SQL and How They Translate in SSIS

Author
Message
Jamie Thomson
Jamie Thomson
SSC Eights!
SSC Eights! (885 reputation)SSC Eights! (885 reputation)SSC Eights! (885 reputation)SSC Eights! (885 reputation)SSC Eights! (885 reputation)SSC Eights! (885 reputation)SSC Eights! (885 reputation)SSC Eights! (885 reputation)

Group: General Forum Members
Points: 885 Visits: 188
djedgar (2/4/2008)
I didn’t write that it’s missing and I don’t see fault in SSIS only
Let’s evaluate

-Forward changes to test environment If this means its possible to promote code to different
environments then SSIS has this too.

Promote code to different environments should freeze projects but when you want
test package you must write\reconfigure connection – potential errors !


Not if you're following best practices (i.e. use configurations or apply environmental-specific settings from the command-line). For anyone reading this, be aware that you should NEVER EVER be required to edit packages when moving them through environments.



-Multi user development SSIS has this

It’s for me freezing code for some task . We need to implement a little complex data flow task
we have two programmers how we could divide jobs for them ?

Fair point. Although I can't envisage any situation where 2 developers have to work on a single data-flow. That's akin to 2 developers working on a stored procedure concurrently and I wouldn't generally allow that. I'm not saying that's correct though, if you have a different approach then I have no issue with that.
But I strongly believe that not allowing 2 people to edit a data-flow simultaneously is a GOOD thing. Having said that, not allowing 2 people to work on a package simultaneously is a BAD thing.




-Debug and error handling SSIS has this

Yes but I prefer try catch statements with nested functionality

Fair enough. Each to their own.




-Versioning SSIS has this (versioning of packages). And unless there's some sort of new feature
that I don't know about there is no versioning of stored procs within the database engine

But if you can use not commercial concurrent versioning system it’s maybe problem delegate
code because xml include some binary data.

Errr OK. Not really sure I understand this one. Are you saying you want to version each task? If so then yes, I think that's a very fair criticism. SSIS doesn't handle that very well at all.



-Logic for concurrent loading/ incidental loading Not quite sure what this means but with SSIS
its possible to load multiple tables concurrently.

In real life ETL is very complex and has very dependent task. When something
is going wrong it’s needed quick find the errors and reprocess flow.
In some situations the best choice will be ignore error data, how can admin do it without modification
packages ? Why to the @#!#!! when you run package in agent job you can only see it as one atomic job. I want to say that SSIS package without more flexibly control loading tool (based on metadata) it’s condemned only on run in dubug mode Wink


[/quote]

You say "how can admin do it without modification packages " That's completely true but exactly the same applies to using stored procedures so I don't really understand this one. Sorry.

Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson
vivek_s902003
vivek_s902003
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 23
Hi Nick,

I had some doubts on this range lookup. I had once a reqt for range look up.
Initially i had implemented the similar logic of joining 1 col from source to one of the cols in the lookup table. it had given me wrong values.

Later i had to have 2 columns in the source linked to the lowerlimit col and upper limit col. The 2nd col in the source is a copy of the first. And in the advanced tab, i had written the query similar to yours. it had worked for me.
Can u confirm mapping 1 col from source to lookup table will definitely work.

Vvek
Nick Maroudas
Nick Maroudas
SSC Eights!
SSC Eights! (821 reputation)SSC Eights! (821 reputation)SSC Eights! (821 reputation)SSC Eights! (821 reputation)SSC Eights! (821 reputation)SSC Eights! (821 reputation)SSC Eights! (821 reputation)SSC Eights! (821 reputation)

Group: General Forum Members
Points: 821 Visits: 101
Hi Vvek,

You do have to have the upper and lower limits within the lookup table for the query in the advanced tab to work. I have now used this range lookup within a few of my packages and the values returned are correct.

As you are closer to your own data it is hard for me to say what is being returned is correct. I found that by using the lookup against the lower limit, the data returned for me was correct.

I hope it gives you confidence with your project.

Nick.
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