February 9, 2006 at 8:52 pm
Hi everyone,
I am trying to use DTS for the first time and am having some issues
I am sent a dump file from SAP in CSV format that I need to import into a user table (tb_users) on a SQL2k Box.
For the most part it is a simple matter of matching up source-destination fields.
But I have two instances of information that will require the same process to successfully update the tb_users table as I require.
tb_users.role is a FK/PK to tb_user_roles.id
there is also tb_user_roles.description that loosely matches a field in the CSV.
for clarity lets call it csv.user_role
So what I need to do is soemthing like.
if csv.user_role = 'this value'
myvar = 1
set tb_users.role, myvar
I have 8 roles defined in my tb_user_roles table so I realise I will need to go through this process 8 times.
it seems simpe enough, but I do not know how to use DTS to get the processing to work
Appreciate anyone's comments / ideas - thanks.
Gavin Baumanis
Smith and Wesson. The original point and click device.
February 10, 2006 at 7:06 am
In general it is better to think in terms of getting the data into SQL first, if necessary into an intermediate table that mirrors the csv. Once in a SQL table you can put the tricky stuff into SQL coding.
February 10, 2006 at 9:49 am
I agree with Juliekenny. Import the csv file into a staging table then use an UPDATE statement to join the staging table to tb_users and update the role column.
Greg
Greg
February 10, 2006 at 3:19 pm
Thanks to you both...
Nice. simple and easy to implement.
Why overcomoplicate things - if you don't need to.
Appreciate the assistance.
Gavin Baumanis
Smith and Wesson. The original point and click device.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply