Tuesday 28 October 2014

SSIS - find element in table, use its key and insert to another table

I have database with following 3 tables:
·         Customers.CustomerId is integer and PK
·         Addresses.AddressId is integer and PK
·         CustomerAddresses.Customer_CustomerId is integer and FK to Customers
·         CustomerAddresses.Address_AddressId is integer and FK to Addresses
·         Customers.EntityId is GUID
·         Addresses.EntityId is GUID

I use SQL Server Integration Services.
I have also XML file with pairs of addressEntityID and customerEntityId. These are are EntityIDs (GUID), that need to be mapped to IDs (integer) and inserted into CustomerAddresses.

How to map this relationship and import to CustomerAddresses?
1.  In Data Flow drop XML source
2.  Add Derived column, map values from XML (from string to GUID)

3.  Add Lookup, connect to Addresses table, create JOIN from derived AddressGuid to EntityId. AddressId is output.
4.  Add another Lookup and do the same for Customers table
On "Specify how to handle rows with no matching entries" choose "Redirect rows to error output".
5.  Add DB destination, map values from Lookup to AddressId and CustomerId

Complete Data Flow

Monday 27 October 2014

Drop all foreign keys

DECLARE @SQL varchar(4000)=''
SELECT @SQL = @SQL + 'ALTER TABLE ' + FK.TABLE_NAME + ' DROP CONSTRAINT [' + RTRIM(C.CONSTRAINT_NAME) +'];' + CHAR(13)
  FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
    ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK
    ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU
    ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN (
            SELECT i1.TABLE_NAME, i2.COLUMN_NAME
              FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
             INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2
                ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
            WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
           ) PT
    ON PT.TABLE_NAME = PK.TABLE_NAME

EXEC (@SQL)

PRINT @SQL

Friday 24 October 2014

Search within range with LINQ and DbGeography

    var myLocation = DbGeography.FromText(string.Format("POINT({0} {1})", lon, lat)); 
    var result = (from u in _db.Restaurants
           orderby u.Location.Distance(myLocation)
           where u.Location.Distance(myLocation) < distance
           select u).Take(limit).ToList();
distance is a double and its value is in meters.

Thursday 23 October 2014

Mobile site - device channel configuration

1.       Go to Site collection features and activate SharePoint Server Publishing Infrastructure


2.       Go to Device Channels

3.       Create new device channel, give it alias "mobile". Under Device Inclusion Rules enter user agent substrings for devices, that should use mobile view. Check as active.

Wednesday 22 October 2014

Przechowywanie datasource pomiędzy PostBack

        private string _countriesList = "countries";
        private List<CountryInfo> _countries;
        private List<CountryInfo> Countries
        {
            get
            {
                if (ViewState[_countriesList] == null)
                    return new List<CountryInfo>();
                return (List<CountryInfo>)ViewState[_countriesList];
            }
            set
            {
                ViewState[_countriesList] = value;
                _countries = value;
            }

        }