Scenario: How to extract the file name from a file path where the number of sub folders or name of folder is not fixed. In simple terms you do not know the position where the file name starts.
Typical example: You have a foreach loop set to traverse sub folders and you set the retrieve file name to "fully qualified" and in parameter mapping you map this to a string variable strFilePath. You use this variable to set up the connection string for may be a flat file source(will not be discussing that). Now lets say you need to retrieve the file path for logging purpose or sending a mail. How do we do this??
In the SSIS Forum I cams across the following query and thought of writing a post on the same.
“Can anyone give guidance on how to set the mask for a Foreach File Enumerator programmatically? I have a DB that has a list of masks. I am retrieving that into an ADO recordset. For each record I retrieve the value into a variable. I would like to look at a directory and copy all files that begin with the chars for each item in my recordset variable.”
The solution I provide uses a Flat File to store the various file mask in stead of a Table as required in the above case.
1.b*.txt2.a*.txt3.d*4.*z.xlsxThe above scenario could be broken into 3 parts:
In most ETL we use Foreach loop to iterate through each file in a folder or records fetched from a table. Now the question arises how do we use Foreach loop based on each record in a text file.
Scenario: We have a text file having 2 columns, one containing source file relative path and 2nd column having the destination file relative path. The objective is to fetch each file from the source folder that are there in the our text file and move them to destination based on the connections available in the text file on the same record.
File Layout:
InputFilePath,OutputFilePath
\Input\Input1.txt,Output\Output1.txt
\Input\Input2.txt,Output\Output2.txt
\Input\Input3.txt,Output\Output3.txt
The overall package:
The first Data Flow Task(DFT) uses a Flat File Source to read the data and send it to an object type user variable using Recordset Destination.
Continue……
1,1 2,1 3,0 4,1 5,0 6,0 7,1
Col1 Col2 Col3 1 1 1 2 1 2 3 0 2 4 1 4 5 0 4 6 0 4 7 1 7
Output:
READ MORE HERE...
Name,Rohit Age,28 Sex,Male Name,Mohit Age,20 Sex,Male Name,Ram Age,28 Sex,Male Name,Shyam Age,20 Sex,Male
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.
Scenario: How to delete files created a number of days before today. The files will have the date appended to the file name and we will not check the file properties to decide the file age. The file name would follow the naming convention: File_ddmmyyyy.txt
This can be achieved very easily in SSIS. Create an integer variable to store the value stating how old files do you wish to delete. Suppose you wish to delete all files older than 3 days, the variable would have the value 3. I name this variable intFileMaxAge. Make this variable configurable so that it can be changed as and when required.
Next I show the layout of the package.
Fig. 1
The Foreach Loop Container is configured to pick the files from a particular folder(you should make this connection configurable) and the “Retrieve file name” property is set to “Name and extension”. Refer fig. 2
Fig. 2
Next we need to save the file name for each iteration to a string variable strFileName. For this go to the Variable Mappings tab and select the variable strFileName from the drop down box and set the index to zero. Refer fig. 3
Fig. 3
Once the above is done put a script task in the Foreach Loop Container. Do not make any changes to the script task. It is just there so that we can have Precedence Constraint before the File system task to control the flow. We need the File System Task to be triggered only when the condition is met i.e. files created before certain days (Refer fig. 1). Double click the precedence and set the Evaluation operation to Expression. In the Expression put the below expression:
DATEDIFF("dd",(DT_Date)(SUBSTRING(@[User::strFileName],6,2)+"-"+SUBSTRING(@[User::strFileName],8,2)+"-"+SUBSTRING(@[User::strFileName],10,4)),GETDATE()) > @[User::intFileMaxAge]
The above expression extracts the date part from the file name and converts it to date type. Once that’s done it gets the difference of the between the file date and today’s date. After this the expression checks whether the difference is greater than the value specified in the variable intFileMaxAge.
If the above expression will be evaluated to true the control will go to the File Sytem Task.
Setting up the File System Task. (refer fig. 4) The operation is set to Delete file as we need to delete files. Set the IsSourcePathVariable to False as I create a connection to connect to the file and use expression to modify the connection at run time.
Fig. 4
In the Connection Managers select the connection manager configured above(refer fig. 4) go to its properties and in the expression set connection string to the following expression:
@[User::strFolderPath]+ @[User::strFileName]
Where the variable strFolderPath contains the folder in which the files are placed. Again make this configurable.
That completes the package.
| 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’.