We can call web service from within SSIS using Script transform.
Copy below code to Script Component Transform.
public override void CreateNewOutputRows()
    {
        try
        {
            string siteURL = Variables.SharePointSiteName.ToString();
            string[] UrlComp = siteURL.Split(new string[] { "/" }, StringSplitOptions.None);
            string siteName = UrlComp[0] + "//" + UrlComp[2];
            string webserviceURL = siteName + "/metrology/projects/_vti_bin/usergroup.asmx";
            SC_68ba55403dda41ccb864e2de8b43f7bc.csproj.PIMSService1.UserGroup wService = new UserGroup();
            wService.Url = webserviceURL;
            wService.UseDefaultCredentials = true;          
           
            XmlNode xn = wService.GetAllUserCollectionFromWeb();
            
            foreach (XmlNode ch1 in xn.ChildNodes)
            {
                foreach (XmlNode ch in ch1.ChildNodes)
                {
                    if (ch.Name == "User")
                    {
                        XmlAttributeCollection xac = ch.Attributes;
                        UserListBuffer.AddRow();
                        UserListBuffer.UserID = Convert.ToInt32(xac[0].Value.ToString());
                        UserListBuffer.SID = xac[1].Value.ToString();
                        UserListBuffer.Name = xac[2].Value.ToString();
                        UserListBuffer.LoginName = xac[3].Value.ToString();
                        UserListBuffer.Email = xac[4].Value.ToString();
                        UserListBuffer.Notes = xac[5].Value.ToString();
                        UserListBuffer.IsSiteAdmin = xac[6].Value.ToString();
                        UserListBuffer.IsDomainGroup = xac[7].Value.ToString();
                        UserListBuffer.Flags = xac[8].Value.ToString();
                    }
                }
            }
            ErrorMsg = "No Error";
        }
        catch (InvalidOperationException ex)
        {
            ErrorMsg = "InvalidOperationException : " + ex.Message + "\n Inner Exception : " + ex.InnerException;
        }
        catch (SoapException ex)
        {
            ErrorMsg = "SoapException : " + ex.Message + "\n Inner Exception : " + ex.InnerException;
        }
        catch(Exception ex)
        {
            ErrorMsg = "Exception :" + ex.Message + "\n Inner Exception : " + ex.InnerException;
        }
    }
No comments:
Post a Comment