October 11, 2011 at 2:15 pm
I have some process that creates an excel file with timestamp in one particular folder everyday
e.g.- test_file_20111011.xls
The process creates these kind of files everyday. 1 file per day.
I just need this latest file emailed to me.
I want to do this using easiest method, I think SSIS is easiest.
So I've crerated For Each loop container. Then some variables like
uVar_CurrentFileDateTime
uVar_CurrentFileName
uVar_FolderNameOfFilesToBeProcessed -->String --> C:\Test\test_file_20111011.xls
uVar_latestFileName
uVar_SourceFileExtension -->String --> *.xls
Now I need to find the latest file amonst all the *.xls files. I think i need to write a script for that
But I have no experience writing a script in .NET or VB.
The script should find latest file (by comparing all files with current date )
Can anyone help me witht that script?
And then I need to send that file as an attachment using SMTP task.
October 12, 2011 at 5:30 am
I guess this might help you...Copy the Code in the script task and Change the Email's and SMTP Connection...
/*This code will take the latest file that was created in the directory and compare with system date(today) and if both are matched then matched file will be emailed.. (Just i have just hardcoded some of the properties like folderpath and messgagesubject and E-mail is.. these things you can directly take from the variables and pass to script task..)*/
/*
Microsoft SQL Server Integration Services Script Task
Write scripts using Microsoft Visual C# 2008.
The ScriptMain is the entry point class of the script.
*/
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.IO;
using System.Collections.Generic;
using System.Text.RegularExpressions;
namespace ST_5d7ac726a5f5410491e6b0c65b942491.csproj
{
[System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
#region VSTA generated code
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
#endregion
/*
The execution engine calls this method when the task executes.
To access the object model, use the Dts property. Connections, variables, events,
and logging features are available as members of the Dts property as shown in the following examples.
To reference a variable, call Dts.Variables["MyCaseSensitiveVariableName"].Value;
To post a log entry, call Dts.Log("This is my log text", 999, null);
To fire an event, call Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, true);
To use the connections collection use something like the following:
ConnectionManager cm = Dts.Connections.Add("OLEDB");
cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;";
Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
To open Help, press F1.
*/
/* This method*/
public static string RemoveSpecialCharacters(string str)
{
return Regex.Replace(str, "[^0-9]+", "", RegexOptions.Compiled); }
public void Main()
{
// TODO: Add your code here
string filename="test_file_20111011";
string startPath = @"C:\Documents and Settings\ainampudi\Desktop\exam\";
DirectoryInfo di = new DirectoryInfo(startPath);
FileInfo[] rgFiles = di.GetFiles("*.doc");
foreach (FileInfo fi in rgFiles)
{
MessageBox.Show(fi.FullName+"_"+fi.CreationTime);
// DateTime dt = new DateTime();
string str = DateTime.Now.ToString("MM/dd/yyyy");
string str1 = fi.CreationTime.ToString("MM/dd/yyyy");
if (str==str1)
{
MessageBox.Show("sendMail");
System.Net.Mail.MailMessage message = new System.Net.Mail.MailMessage();
message.To.Add("asd@ad.com");
message.Subject = "<Latest Created file>";
message.From = new System.Net.Mail.MailAddress("asd@ad.com");
System.Net.Mail.Attachment attachment = new System.Net.Mail.Attachment(fi.FullName);
message.Attachments.Add(attachment);
message.IsBodyHtml = true;
System.Net.Mail.SmtpClient smtp = new System.Net.Mail.SmtpClient("ad.ad.ad");
smtp.Send(message);
}
}
Dts.TaskResult = (int)ScriptResults.Success;
}
}
}
I hope this helps...
Thanks
Anil Inampudi 🙂
October 12, 2011 at 8:41 am
Thanks a lot Anil. 2 Questions. Can I use this same code in SQL 2005?? I mean C# 2005??
I tried something in Visual .Net 2005 in main Body
---------------------------------
Dim FileToTest As String = Dts.Variables("uVar_currentFileName").Value.Tostring()
Dim FileDate As Date
If File.Exists(FileToTest) Then
FileDate = File.GetLastWriteTime(FileToTest)
End If
If FileDate < Dts.Variables("uVar_CurrentFileDateTime").Value.ToString() Then
Dts.Variables("uVar_CurrentFileDateTime").Value =FileDate
Dts.Variables("uVar_latestFileName").Value = Dts.Variables("uVar_currentFileName").Value.Tostring()
End If
Dts.TaskResult = ScriptResults.success
-------------------
When I tried to debug\Build this I got following erros,
1)Name 'File' is not declared and 2) Option Strict On disallows implicit conversions from 'String' to 'Date'. Can you tell me how to correct it?
Thanks
October 12, 2011 at 1:05 pm
Can you rely on the format of the excel file name to contain the date? And if so, is your request to have the file corresponding to the most recent date, based on the file name, emailed to you?
If that's the case, then what you could also do is use a ForEach loop, with the File enumerator, to loop through the file contents in the folder, using *.xls. Then for each file name, you can use an expression to calculate the date, based on that file name, and store that as a variable. Then, have a variable storing the "max" date, and do a comparison based on the variable containing the max date and the variable storing the current date.
It's not the cleanest solution, but it does avoid the need to use the .NET code.
anil's solution should work though, if you want to go with the .NET approach.
As for the problem you're having with his code, for the first error, make sure you've included System.IO in your list of includes. In the VB.NET version, you would add Imports System.IO to your list of imports. The second error is because the .NET environment in SSIS requires you to explicitly handle conversions. Use Date.Parse() on the strings that you need to become dates. For example, Date.Parse(Dts.Variables("uVar_CurrentFileDateTime").Value.ToString())
October 18, 2011 at 2:46 pm
Thanks for the reply but its still not working somehow. It says,
Error: Failed to lock variable "\C:\Shared\jobs\test_20111001.xls" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.".
I have attached a package here. Can you please see that? I dont have VB installed, I have only SQL 2005 installed with proper .NET framework.
October 19, 2011 at 4:37 am
You don't need VB installed. Script components come with the built-in VB environment to program with.
As for your code, you have two problems.
First, in your Script Task, you're attempting to set your ReadOnlyVariables and ReadWriteVariables through expressions. What you end up doing is getting SSIS to think that the VALUE of those variables should be treated as a variable.
What you should be doing is putting those variables directly in the ReadOnlyVariables and ReadWriteVariables section. See screenshot attached.
Your second problem is that SSIS is a case-sensitive environment. One of your variables is lower-cased when it is declared as upper-cased, and same for within your code. The variable is the currentfilename.
Finally, you have duplicate variables, one set at the package scope and the other at the foreach loop scope. Delete the ones at the foreach loop scope, they're not necessary.
October 19, 2011 at 9:42 am
Thanks for your reply. I have corrected all those errors but this time I see,
DTS Script Task : Runtime Error
The element cannot be found in a collection. This error happens when you try to retrieve an element from a collection on a container during execution of the package and the element is not there.
at Microsoft.SqlServer.Dts.Runtime.Variables.get_Item(Object index)
at ScriptTask_b64bc510a6a84e4ea2beaab666d4e44d.ScriptMain.Main()
What this means? How do I correct it?
October 19, 2011 at 9:56 am
It's pretty self-explanatory. The Script Transformation is attempting to access a variable which doesn't exist. Again, remember that it is case-sensitive. Look in your .NET code and make sure that the variables you're using in there are the exact same as the ones you're declaring in your ReadOnlyVariables and ReadWriteVariables.
I looked at your code and noticed that there were a few cases where this wasn't the case, so make sure you've got em all!
October 19, 2011 at 10:46 am
I did exactly what u said but no luck. If you get chance then can u please take a look at my package?
October 19, 2011 at 11:27 am
In The Script Task, I tried the variables as
ReadOnlyVariables = uVar_CurrentFileName
ReadWriteVariables = uVar_latestFileName
But Still same error.
Is there any way to learn VB step by step or quick tutorial?
October 19, 2011 at 11:49 am
Got it.
the variable was missing. in READWRITEOnly = uVar_CurrentFileDateTime,uVar_latestFileName
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply