Get SSIS Version of Deployed SSIS Packages
use msdb;select * from dbo.sysssispackages
SELECT *
FROM Northwind.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'Customers'
-------------------------------------------------------------------------------------------SQL Query to retrieve Schema name and Display name - CRM 2011
---------------------------------------------------------------------------------------
select en.Name, en.LogicalName, displayname.ObjectColumnName, displayname.Label, IsCustomEntity from
entityview en
inner join LocalizedLabelLogicalView displayname
on en.EntityId = displayname.ObjectId
and displayname.ObjectColumnName = 'LocalizedName'
where IsCustomEntity = 1
and name like '%customerstatus%'
-- remove this if you want to show the system entities as well
--and add the isActivity = 0 if you want to filter out the Activity entity
--if you want to remove like post album, filter, etc
--and en.Name not like '%msdyn%'
order by label
--------------------------------------------------------------------------
delete from Vrp_PartyReferralDetails_CONTACT where documentnumberreference not in(1000177777,2368903759)
select CustomerNumber,PartyCustomerNumber,DocumentNumberReference,IsNew,ContactId from Vrp_PartyReferralDetails_CONTACT
select * from Vrp_PartyReferralDetails_CONTACT where IsNew=1
and DocumentNumberReference is not null
-- and DocumentNumberReference!=0
select PartyCustomerNumber, * from Vrp_PartyReferralDetails_COMPANY
update Vrp_PartyReferralDetails_CONTACT set status=1 where
select * from Vrp_PartyReferralDetails_CONTACT A inner join
Vrp_PartyReferralDetails_CONTACT B ON A.DocumentNumberReference=B.DocumentNumberReference
------------------------------------------------------------------------------------------------------------------------------
select top(NumberOfNew)* from
--update B set ContactId=A.ContactId from
(select DocumentNumberReference,count(1) as NumberOfNew from Vrp_PartyReferralDetails_CONTACT group by DocumentNumberReference,IsNew having IsNew=1
and DocumentNumberReference is not null and DocumentNumberReference!=0) A
inner join Vrp_PartyReferralDetails_CONTACT B
on A.DocumentNumberReference=B.DocumentNumberReference
select * from Vrp_PartyReferralDetails_CONTACT where IsNew=1
---------------------------------------------------------------------------------------------------
-- USE THIS TASK JUST AFTER CHECK CONTACT AND COMPANY
-----------------------------------------------------------------------------------------------------
----CONTACT
------------------------
update t set t.IsNew = 0
--select *
from (
select *, row_number() over(partition by PartyCustomerNumber order by (select CustomerNumber)) row_num
from Vrp_PartyReferralDetails_CONTACT where IsNew=1 and PartyCustomerNumber is not null and PartyCustomerNumber!=0
) t
where row_num > 1
---------------------------------------------------------------
update t set t.IsNew = 0
--select *
from (
select *, row_number() over(partition by DocumentNumberReference order by (select CustomerNumber)) row_num
from Vrp_PartyReferralDetails_CONTACT where IsNew=1 and DocumentNumberReference is not null and DocumentNumberReference!=0
) t
where row_num > 1
--------------------------------------------------------------
--COMPANY
-------------------------------------------------------------
update t set t.IsNew = 0
--select *
from (
select *, row_number() over(partition by PartyCustomerNumber order by (select CustomerNumber)) row_num
from Vrp_PartyReferralDetails_COMPANY where IsNew=1 and PartyCustomerNumber is not null and PartyCustomerNumber!=0
) t
where row_num > 1
----------------------------------------------------------------
-- Use this Task after Inserting Contact
--select B.DocumentNumberReference, IsNew,A.ContactId from
update B set ContactId=A.ContactId from
(select distinct DocumentNumberReference, IsNew as IsNewContact,contactId from Vrp_PartyReferralDetails_CONTACT where IsNew=1
and DocumentNumberReference is not null and DocumentNumberReference!=0) A
inner join Vrp_PartyReferralDetails_CONTACT B
on A.DocumentNumberReference=B.DocumentNumberReference and B.IsNew=0
update B set ContactId=A.ContactId from
(select distinct PartyCustomerNumber, IsNew as IsNewContact,contactId from Vrp_PartyReferralDetails_CONTACT where IsNew=1
and PartyCustomerNumber is not null and PartyCustomerNumber!=0) A
inner join Vrp_PartyReferralDetails_CONTACT B
on A.PartyCustomerNumber=B.PartyCustomerNumber and B.IsNew=0
-- Use this Task after Inserting Company
--update B set AccountId=A.AccountID from
select B.PartyCustomerNumber, IsNew,A.AccountId from
(select distinct DocumentNumberReference, IsNew as IsNewContact,AccountId from Vrp_PartyReferralDetails_COMPANY where IsNew=1
and PartyCustomerNumber is not null and PartyCustomerNumber!=0) A
inner join Vrp_PartyReferralDetails_COMPANY B
on A.PartyCustomerNumber=B.PartyCustomerNumber and B.IsNew=0
----- To Export Multiple SSIS Package------------------
select ActivityTypeCode,count(1) as noofActivities from ActivityPointerBase APB
INNER JOIN EntityView EV ON APB.ActivityTypeCode=EV.ObjectTypeCode
where APB.StateCode=0
and EV.name like 'vrp_%'
group by ActivityTypeCode
order by noofActivities desc
------------------------------------------------------------------------------
Query to get Column names for CRM entity
-----------------------------------------------------------------------------
Select * from [MetadataSchema].[Attribute] a inner join [MetadataSchema].[Entity] e on e.EntityId=a.EntityId where e.Name='entity name'
No comments:
Post a Comment