wawrzyk3.blogspot.com

Tuesday, November 27, 2018

SAP ABAP: Get email from table with username

1. Create email variable at data section

DATA: L_SMTP_ADDR type adr6-SMTP_ADDR.

2. Get username from ADR6 and USR21 tables

select ADR6~SMTP_ADDR INTO L_SMTP_ADDR FROM
  USR21 LEFT OUTER JOIN ADR6 On
   USR21~addrnumber =  ADR6~addrnumber
                and USR21~persnumber =  ADR6~persnumber
  WHERE USR21~BNAME = sy-uname.
  ENDSELECT.

Monday, November 26, 2018

SSIS SQL Server Integration Services - execute query based on flat file csv datasource

SSIS SQL Server Integration Services - execute oracle query based on flat file csv datasource

1. Create variable to store file path

f.e.

FilePath with value C:\file.txt

File.txt will be ";" separeted flat file. I will use second column of file with material numbers.

2. Create variable to store query you wish to execute

f.e Query which will later be referenced as User::Query

3. Create script task to read file contents and prepare query

4. Create Execute SQL Task to run query created inside previous script task, using variable User::Query

// Script

public void Main()
        {
            // TODO: Add your code here

            //get file path
            string FilePath = Dts.Variables["User::FilePath"].Value.ToString();
            string temp;
            string email = "przemyslaw.wawrzyczek@valeo.com";
            string content = "";
            int i = 0;
            bool contentExists = false ;

            // Open file reader
            StreamReader sr = new StreamReader(FilePath);

            // Loop while end of file, read line
            while ((temp = sr.ReadLine()) != null)
            {
                // Split into columns
                string[] columns = temp.Split(';');

                // Skip header
                if (i > 0)
                {
                    //Get distinct values
                    if (!content.Contains(columns[1]))
                    {
                        // add value
                        content += "'"  + columns[1] +  "',";
                        email = columns[12].ToString();
                        contentExists = true;
                    }
                }
                else
                {
                    // open bracket
                    content = "(";
                }
                i++;
            }

            //remove last coma
            content = content.TrimEnd(',');

            //close bracket
            content += ")";

            if (contentExists)
            {
                // create query and pass it into ssis variable
                Dts.Variables["User::Query"].Value = "BEGIN DELETE FROM TABLE WHERE material_number in " + content + "; COMMIT; END;";
                Dts.Variables["User::Email"].Value = email;
                content = content.Replace("'", "");
                Dts.Variables["User::EmailSubject"].Value += " " + content;

                Dts.TaskResult = (int)ScriptResults.Success;

            }
            else
            {
                Dts.Variables["User::Query"].Value = "";
                Dts.TaskResult = (int)ScriptResults.Failure;
            }
           

        
        }