Wednesday, 5 June 2013

Calling webservice from SSIS package

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