DTS and string manipulation

With Replace function. You can do this test in QA:

select replace('10.192.148.2','.','')

How would this fit into a DTS package where I have a table containing 1500 ip-addresses?

ID1     !  IPaddr         !   Land
------------------------------------------
   1     !  10.192.10.1  !   UK
   2     !  10.192.10.2  !   UK
   3     !  10.192.10.3  !   UK

New table
ID1               !  IPaddr         !   Land
---------------------------------------------------
10192101    !  10.192.10.1     !   UK
10192101    !  10.192.10.2     !   UK
10192101    !  10.192.10.3     !   UK

What are you trying to do with this IP address?  We might be able to help you with more information.

As far as I can see it, in DTS, you'd set your source equal to a query statement something like this:

SELECT replace('10.192.148.2','.','') AS ID1, IPaddr, Land FROM SourceTableName

Then, just map the ID1, IPaddr and Land fields to your destination table....

That should do the trick!

Regards,
Phil / peh803

You need to change your DTS. There's two options:
1) Instead of passing all table, use a query to pass data and in that query use the Replace function
2) Add an OnSuccess flow to execute an SQL Script to update the ID1 with IPaddr with Replace function

@adwiseman : please note that I've only taken @VMontalvao's query and put it in a very simple statement -- I was in no way trying to steal points.  Just wanted to make that clear in case anyone thought otherwise.

Regards,
Phil / peh803

I have a DTS which imports the contents of an Excel Spreadsheet.
The sheet contains 3 columns:
IPaddr, Land and Comment

The DTS
Creates a table structure containing IPaddr, Land and Comment.
Populates the table with the data.

I want to use the IPaddr as a unique key. I cannot use it In its present form of 10.192.10.3, so I need to convert it without the dots into a new unique key field. As shown in the previous tables.
 

How are you populating the table in the DTS?

Phil, no worries :)

One method I have used when dealing with the limitations of excel is a 2-step approach.

1.) import the excel data into SQL Server "import" in your database
2.) write a stored procedure to do your heavy lifting and call that once the data has been imported from excel

So in your case, you'd have something like this:
-- import data from excel to a table called "excel_import" or whatever you would want to call it
-- then, your stored procedure would do the select statement above + the insert

Your DTS package would run the import and then simply call the sproc.

This may be overkill in this case, but I think you'd definitely be able to get it working using this approach.

Regards,
Phil / peh803

removing the '.' from an IP address string will not result in a unique key. e.g. the 2 IP addresses 10.192.10.3 and 101.92.10.3 will give the same number, of 101921013. Instead you need to convert properly to the corresponding numeric value by multiplying by 256, 256^2 etc.
However there are too many IP addresses to fit into an integer data type - IP addresses cover the range from 0 to 255.255.255.255 which is 0 to 4,294,967,295, whereas the largest int you can have is 2,147,483,647 therefore you need to use another datatype, e.g. bigint to store a uniqie number representing an IP address.
The following function converts an IP string to the corresponding bigint.
create function ip2bigint (@ip varchar(15))
returns bigint
as
begin
      declare @d1 integer
      declare @d2 integer
      declare @d3 integer
      declare @ipint bigint
      set @d1 = charindex('.',@ip)
      set @d2 = charindex('.',@ip,@d1 + 1)      
      set @d3 = charindex('.',@ip,@d2 + 1)
      set @ipint = 0
      if @d3 > 0
      begin
            set @ipint = ((cast(substring(@ip,1,@d1-1) * 256 as bigint)  + substring(@ip,@d1+1,@d2-@d1-1)) * 256 +  substring(@ip,@d2+1,@d3-@d2-1))*256 + right(@ip,len(@ip) - @d3)
      end
      return @ipint
end