March 7, 2008 at 4:49 pm
We recently upgraded from SQL 2000 to SQL 2005. Gone is the SQL 2000 DTS and I am still trying to learn SSIS. I've been reading alot and I think I need alittle pointers to help me go over this bump.
For example:
I have a table with a column COMPANY, all the rows have COMPANY = 1 value.
How do I...
1) COPY the whole table,
2) change the value of COMPANY = 1 to COMPANY = 10
3) then append the new COMPANY 10 table to the existing COMPANY 1 table?
I figured out how to copy the table thru SSIS import/export wizard but the transformation part has me stumped. Thanks in advance.
March 7, 2008 at 5:27 pm
Use a dataflow task. Make your table the source table and then use a derived column to replace the value of the company. Then use the same table as the destination.
Make sure to use the derived column in the destination mapping.
March 8, 2008 at 9:24 am
Thanks, it worked out good. I am running into another problem however if anyone can point me to the right direction. That would be much appreciated.
Now I have a table with COMPANY 1 and COMPANY 10.
How do I select only COMPANY 1 and reuse the derived column procedure to create COMPANY 20 and append it to the same table? Thanks in advance.
March 8, 2008 at 9:46 am
You can use the same datasource but use a conditional split to grab only the records where company is company1. The you attach the derived column to the conditional split based on the parameters. Then attach the derived column to the destination.
Another option is to create a view where you query only the rows that are company1. Set your datasource to the view and proceed as normal.
March 8, 2008 at 9:50 am
How many "companies" do you need to create? Will they increment by 10?
You could do this in single SQL Statement:
While @num <= [target]
Begin
Insert Into table
Select
'Company ' + Convert(varchar(3), @num) as company,
other columns....
From
table
Where
company = 'company 1'
Set @num = @num + increment
End
If you have a numbers/tally table you could do this:
Select
'company ' + convert(varchar(4), num) as company,
other columns...
From
table cross Join
number_table
Where
company = 'Company 1' and
(num mod 10 % 0 and num <= target num)
This would be if you want the customer name to be multiples of 10. You could obviously go with another increment.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 8, 2008 at 9:52 am
Thank you very much for teaching me. Got it working, thanks again!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply