August 19, 2009 at 2:38 am
I am an experienced user with DTS in the past and I am due to go on a SSIS 2008 course in the next couple of weeks. I have a requirement and I was wondering if it is possible to do using SSIS.
I need to populate data from the same source columns to the same destination columns except with one difference. I have 50 rules with each rule being calculated from the where clause. Dependent upon this rule a new column in the destination is populated with the rule number.
For example,
If FIRST_COLUMN IS NULL then 'Rule 1'
IF SECOND_COLUMN IS NULL AND THIRD_COLUMN = '' THEN 'Rule2'.
Some of the where clauses are 20 rows long, so a case statement is not going to be much use to me.
I wanted to know if I could create a transformation task that could determine which rule is selected and populate the new column with that value. I thought if I have it in a graphical view it would be easy to add rules in and amend existing rules instead of hacking large amounts of code. I thought of having some sort of splitting task where all the logic could be catered for.
Any help would be appreciated including non SSIS solutions.
August 19, 2009 at 3:01 am
yes
If you take the dtail of what you want with you the tutor will be able to tell you. The Exceute SQL task will do exactly what its name is and execute SQL. That is the obvious choice but there may be a more suitable approach. It will be far easier to design this face to face with the tutor.
Ells.
😎
August 19, 2009 at 10:17 am
It sounds like a calculated column transformation will do the trick.
If that doesn't work, you can certainly do a conditional split into as many outputs as necessary (and hopefully you don't need a separate one for each rule) and then process the outputs individually and then use union all to combine them back together before the final insert.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 20, 2009 at 2:37 am
Thanks for the replies. Unfortunately, the Conditional Split functionality is not much use to me as some of the records will fail multiple rules. I need to have a separate entry per rule broken, so this rules conditional splits out because a row can only fall into a single bucket. The same applies to a case statement.
I need a way of inserting a row into a table for every rule it breaks. I have done quite a bit of research and it looks like the only way I am going to achieve this is through dynamic sql. In that case I will see if I can loop through an SSIS package passing in a dynamic where clause for every rule.
If anyone can help out with that i would appreciate it. If I find anything I will post it back here.
August 20, 2009 at 3:17 am
there is more than one way to skin a cat or in this case design a package.
The for each loop container sounds like a suitable loop for you. I use this a bit to extract a list of months that I want and then loop through the months and do an operation.
You may even decide to write the process in a stored procedure and then execute the stored proc from inside the package.
This is why I said if you are going on a course show what you want to do to a tutor and they will be able to advise. I find them great for things like this if you can easily convey what you want they should have had this sort of issue before.
Ells.
😎
August 20, 2009 at 5:06 am
Thanks, SSC-Enthusiastic. I accept your point and I will be picking the tutor's brain when I attend the course.
I have more of an idea now on how I may base my design, but I still need a couple of more questions answered. I guess what I really want is to make it easy to administer and add new rules, ideally by letting the business user update and maintain their own reference table that I can use for paramaterisation.
August 20, 2009 at 6:29 am
It would be nice if you could show us what you mean to do. Can you give us the create table statement and inserts of data for the table you want to run the rules on. Also a few of the rules that would need to be applied. That way we can really help you.
----------------------------------------------------------------------------------------------
Struggling to find the question?
http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
August 21, 2009 at 3:13 am
This is a quick and crude example. Apologies if I cause offence on the data, its just a quick example I often use.
In this example Cus_Name ACME appears twice
CREATE TABLE Customers
(
Cus_ID int PRIMARY KEY IDENTITY,
Cus_Name varchar(30) NOT NULL,
Cus_City varchar(30) NOT NULL,
Cus_Country varchar(30) NOT NULL
)
CREATE TABLE City
(
Cit_ID int PRIMARY KEY IDENTITY,
Cit_Name varchar(30),
Cit_Small char(1)
)
CREATE TABLE Country
(
Cou_ID int PRIMARY KEY IDENTITY,
Cou_Name varchar(30),
Cou_Small char(1)
)
INSERT Customers VALUES ('Acme','Paris','France')
INSERT Customers VALUES ('Bouvier','Paris','France')
INSERT Customers VALUES ('Oilers','Houston','USA')
INSERT City VALUES ('Paris','Y')
INSERT City VALUES ('Houston','N')
INSERT Country VALUES ('USA','N')
INSERT Country VALUES ('FRANCE',NULL)
SELECT
Customers.Cus_Name,
Customers.Cus_City,
Customers.Cus_Country,
'Rule1 - Small City' as RuleBreaker
FROM Customers
INNER JOIN City
ON Customers.Cus_City = City.Cit_Name
INNER JOIN Country
ON Customers.Cus_Country = Country.Cou_Name
WHERE
City.Cit_Small = 'Y'
UNION ALL
SELECT
Customers.Cus_Name,
Customers.Cus_City,
Customers.Cus_Country,
'Rule2 - Small Country' as RuleBreaker
FROM Customers
INNER JOIN City
ON Customers.Cus_City = City.Cit_Name
INNER JOIN Country
ON Customers.Cus_Country = Country.Cou_Name
WHERE
Country.Cou_Small = 'N'
UNION ALL
SELECT
Customers.Cus_Name,
Customers.Cus_City,
Customers.Cus_Country,
'Rule3 - Unknown city or country size' as RuleBreaker
FROM Customers
INNER JOIN City
ON Customers.Cus_City = City.Cit_Name
INNER JOIN Country
ON Customers.Cus_Country = Country.Cou_Name
WHERE
Country.Cou_Small IS NULL OR City.Cit_Small IS NULL
August 21, 2009 at 4:48 am
You may even decide to write the process in a stored procedure and then execute the stored proc from inside the package.
This is why I said if you are going on a course show what you want to do to a tutor and they will be able to advise. I find them great for things like this if you can easily convey what you want they should have had this sort of issue before.
September 21, 2009 at 2:09 am
I finally have a solution:
1. Create a variable that I fill with a sql script to get all the where clauses from a parameter table
2. Create a ForEach Loop for the container
3. Map the variables from the task 1
4. Create the mapping of data source to destination using a dynamic sql in a stored procedure that expects the where clause clause as a parameter
5. Loop through the variables calling the stored procedure each time.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply