Setting Variable from row in csv file

You have to write SQL script



The first field is a variable name, the second is the actual value

Something like this

SELECT '<Variable1>',[ID] FROM [ID_GENERATOR] where ID_TYPE=1
union all
SELECT '<Variable2>',[ID] FROM [ID_GENERATOR] where ID_TYPE=2
union all
SELECT '<Variable3>',[ID] FROM [ID_GENERATOR] where ID_TYPE=3

Please also read this post especially about resetting variables
www.etl-tools.com/forum/advanced-etl-pro...failed-28000?start=0

with MS SQL

been trying this with no luck

Select [Legal_Lastname] As "<Legal_Lastname>",
Legal_Firstname As '<Legal_Firsttname>',
Work_Email As '<Work_Email>'
From [dbo].[MailMerge]

Why your SQL has 3 fields?

Here are some screenshots you may find useful

Thank you, that did help. the working SQL is:

Select '<Employee_Code>',[Employee_Code]
From [dbo].[MailMerge]
union all
Select '<Legal_Lastname>',[Legal_Lastname]
From [dbo].[MailMerge]
union all
Select '<Legal_Firstname>',[Legal_Firstname]
From [dbo].[MailMerge]
union all
Select '<Work_Email>',[Work_Email]
From [dbo].[MailMerge]

Hopefully one last question. The variables are passing fine into the the show message function but not into the email function. Do I have the in email call incorrect? see attached.

Since you are sending emails in HTML format "<" is a special character so a better option is to use something else instead for example #.

CREATE TABLE [dbo].[EMAILS](
[ID] [int] IDENTITY(1,1) NOT NULL,
[LEGAL_LASTNAME] [varchar](100) NULL,
[LEGAL_FIRSTNAME] [varchar](100) NULL,
[WORK_EMAIL] [varchar](100) NULL,
[PERSONAL_EMAIL] [varchar](100) NULL,
[EMPLOYEE_CODE] [varchar](100) NULL,
[HIRE_DATE] [datetime] NULL,
[PAID_TIME_OFF_ACCRUAL_HOUR] [int] NULL,
[PAID_TIME_OFF_BEGINNING_BALANCE] [int] NULL,
[PAID_TIME_OFF_TAKEN] [int] NULL,
[ACCRUAL_PROFILE_CODE] [varchar](100) NULL,
[ACCRUAL_START_DATE] [datetime] NULL,
[PROJECTED_PTO_FOR_YEAR] [int] NULL,
[REQUESTED_PTO] [int] NULL,
[REMAINING_PTO] [int] NULL
) ON [PRIMARY]
GO

SELECT '#LEGAL_LASTNAME#',LEGAL_LASTNAME from EMAILS where id=#ID#
UNION ALL
SELECT '#LEGAL_FIRSTNAME#',LEGAL_FIRSTNAME from EMAILS where id=#ID#
UNION ALL
SELECT '#WORK_EMAIL#',WORK_EMAIL from EMAILS where id=#ID#
UNION ALL
SELECT '#PERSONAL_EMAIL#',PERSONAL_EMAIL from EMAILS where id=#ID#
UNION ALL
SELECT '#EMPLOYEE_CODE#',EMPLOYEE_CODE from EMAILS where id=#ID#
UNION ALL
SELECT '#HIRE_DATE#',cast(HIRE_DATE as varchar) from EMAILS where id=#ID#
UNION ALL
SELECT '#PAID_TIME_OFF_ACCRUAL_HOUR#',cast(PAID_TIME_OFF_ACCRUAL_HOUR as varchar) from EMAILS where id=#ID#
UNION ALL
SELECT '#PAID_TIME_OFF_BEGINNING_BALANCE#',cast(PAID_TIME_OFF_BEGINNING_BALANCE as varchar) from EMAILS where id=#ID#
UNION ALL
SELECT '#PAID_TIME_OFF_TAKEN#',cast(PAID_TIME_OFF_TAKEN as varchar) from EMAILS where id=#ID#
UNION ALL
SELECT '#ACCRUAL_PROFILE_CODE#',cast(ACCRUAL_PROFILE_CODE as varchar) from EMAILS where id=#ID#
UNION ALL
SELECT '#ACCRUAL_START_DATE#',cast(ACCRUAL_START_DATE as varchar) from EMAILS where id=#ID#
UNION ALL
SELECT '#PROJECTED_PTO_FOR_YEAR#',cast(PROJECTED_PTO_FOR_YEAR as varchar) from EMAILS where id=#ID#
UNION ALL
SELECT '#REQUESTED_PTO#',cast(REQUESTED_PTO as varchar) from EMAILS where id=#ID#
UNION ALL
SELECT '#REMAINING_PTO#',cast(REMAINING_PTO as varchar) from EMAILS where id=#ID#