wawrzyk3.blogspot.com: November 2018

Tuesday, November 27, 2018

SSIS SQL Server Integration Services - Using variable in SSIS Send Email Task, Send email to dynamic address

1. Create variables for FromLine, ToLine, Subject and Source

f.e EmailFrom, Email, EmailSource, EmailSubject

2. Fill variables (you can do it dynamically at script task)
3. Fill send email task as below

4. Fill expression tab as below

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;
            }
           

        
        }