At times I have seen in SSIS forums people ask about the way they can know the number of records updated by a query or deleted by a query in an Execute SQL Task in control flow. Even I replied at times that its not possible(in a direct manner), until I read about the property ExecValueVariable.
Read more here.
| ID | CustomerName | Amount |
| 1 | Sudeep | 100 |
| 2 | Ankur | -10 |
| 3 | Manju | 20 |
CREATE TABLE [dbo].[tblCust](
[ID] [int] IDENTITY(1,1) NOT NULL,
[CustName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Amount] [int] NULL
) ON [PRIMARY]
INSERT INTO [RAJ].[dbo].[tblCust]([CustName],[Amount])
(
SELECT 'Sudeep',100
UNION ALL
SELECT 'Ankur',-10
UNION ALL
SELECT 'Manju',3 )
DECLARE @tableHTML NVARCHAR(MAX) ;
SET @tableHTML =
N'<html><body><h1>Mail Header</h1>' +
N'<table border="1" width="100%">' +
N'<tr bgcolor="gray"><td>ID</td><td>CustomerName</td><td>Amount</td></tr>' +
CAST((
SELECT
td = ID, '',
td = CustName, '',
'td/@bgcolor'=CASE WHEN Amount>0 THEN 'Green'
ELSE 'Red' END,
td = Amount, ''
FROM tblCust
FOR XML PATH('tr'), TYPE) AS NVARCHAR(MAX)) + N'</table></body></html>'EXEC msdb.dbo.sp_send_dbmail @recipients='<MailID@maildomain.Com>',
@subject = 'SQL Errors Report',
@body = @tableHTML,
@body_format = 'HTML',
@profile_name = 'SudeepTest';
In this query the HTML body is
formed in the variable @tableHTML, and the body format is set to ‘HTML’.
In the SSIS forum very often there are questions on dynamic name for a flat file connection manager
I have split it in two parts:
In this post I will talk about the first scenario.
Scenario:
For simplicity sake I will be using a case where the file name is saved in a DB table.
Need to fetch the file name from a database table.
Need to use a Data Flow Task which has a Flat File Source.
This flat file source needs the file name to be set up dynamically at run time.
The folder for the file is fixed.
Create a string Variable: FileName with the scope as the package(Practice in my case). Set the value of the variable to a file name that exists (I set it to Demo.txt) just while development.
Now set up the Flat File Connection Manager for the above file that exists and has the same meta data as the file that will be used later.
File name: Click on the Browse button and select the file.
I am using the other default settings.
Click on the Columns tab to verify the columns.
Then click OK.
To set up the Execute SQL task to fetch the file name and save it to a user variable FileName.
In the Execute SQL task Editor window go to the General tab.
Do the following settings:
Now select the Result Set tab
Click the Add Button.
You will see the below screen with a row for the result details.
In the Result Name set it to : 0 (Numeric zero)
In the Variable Name select the string type variable which has the scope set to the package.
Once this is done click OK.
Now select the Flat File Connection Manager 1 and click "F4" or right click and select “Properties”.
In the properties pane for the above connection manager:
Set the DelayValidation to True
Expand the Expressions by clicking the ellipse.
On the Property Expressions Editor window:
In the Property column select the ConnectionString property from the drop down list.
Now expand the Expression column by clicking the ellipse. An Expression Builder window will pop up.
On the Expression Builder window:
Set the Expression as: “C:\\Data\\” + @[User::FileName]
Click the Evaluate expression button at the left hand bottom to see if the expression is creating the desired output.
Click OK.
Click OK on the Property Expression Editor as well.
Now set up the Data Flow Task as below:
Double click the Flat File Source.
In the Flat File Source Editor:
In the Connection Manager tab, select the appropriate Flat file connection manager from the drop down list.
Now in the OLEDB Task:
Select the OLEDB Connection needed to connect to the destination Data base.
Select the table where the columns need to be inserted.
Goto the mapping
Go the mapping and do the mapping as required and click OK.
