SSIS: Execute Process Task (call a command line app)

from the MSDN doco:

“The Execute Process task runs an application or batch file as part of a SQL Server 2005 Integration Services (SSIS) package workflow. Although you can use the Execute Process task to open any standard application, such as Microsoft Excel or Microsoft Word, you typically use it to run business applications or batch files that work against a data source. For example, you can use the Execute Process task to expand a compressed text file. Then the package can use the text file as a data source for the data flow in the package. As another example, you can use the Execute Process task to run a custom Visual Basic application that generates a daily sales report. Then you can attach the report to a Send Mail task and forward the report to a distribution list.

When the Execute Process task runs a command-line application, it provides input to the application through a variable that you specify in a property setting. The Execute Process task also includes properties for specifying the variables that consume the standard output and error output of the application. For more information about variables, see Integration Services Variables and Using Variables in Packages.

The Execute Process task can specify the command prompt arguments that the executable file or batch file requires. For example, if the task opens a document in Word, the command prompt argument can name the .doc file. You can use multiple command arguments in one task by using spaces to delimit arguments.

Additionally, you can configure the Execute Process task to specify a working directory, a time-out period, or a value to indicate that the executable ran successfully. The task can also be configured to fail if the return code of the executable does not match the value that indicates success, or if the executable is not found at the specified location.“

see: http://msdn.microsoft.com/en-us/library/ms141166(SQL.90).aspx

SSIS: do so much with WMI Data Task and WMI Event Task

great blog post in this link, some really interesting stuff:

http://www.databasejournal.com/features/mssql/article.php/3552621

Quote from the Article:

“

Probably the most common data processing scenario in which WMI events could be helpful, involve launching a sequence of tasks activities as soon as data to be processed becomes available (e.g. by being copied to target computer). This can be accomplished by employing a SELECT query against __InstanceCreationEvent class in combination with another class, for which creation of a new instance is equivalent to creation of a new file on a managed computer. The best match in this case (from the performance point of view) is the CIM_DirectoryContainsFile class (this class forms an association between CIM_Directory and CIM_File classes, which are referenced as its GroupComponent and PartComponent properties, respectively). In order to detect a new file appearing in a specific directory (in our example c:\Data), you would run the following query (note that the last entry contains extra backslashes as so-called escape characters, which ensure proper string parsing. Such characters need to be applied whenever you want the next character to be treated literally. For example, by placing it after "Win32_Directory.Name=\, you ensure that the following double quote does not indicate the end of this string, but rather the beginning of the path to target folder c:\Data. Similarly, we added it in front of each backslash appearing in the folder path):

SELECT * FROM __InstanceCreationEvent WITHIN 10
 WHERE TargetInstance ISA "CIM_DirectoryContainsFile"
 AND TargetInstance.GroupComponent = "Win32_Directory.Name=\"c:\\\\Data\""

Now it is time to look into incorporating this functionality into SSIS packages using the WMI Event Watcher Task. Start by creating a new project using SQL Server Business Intelligence Development Studio. Drag the WMI Event Watcher Task from the toolbox onto the Control Flow area of the package designer interface. Right-click on it and select Edit option from the context sensitive menu. In the resulting WMI Event Watcher Task Editor window, switch to the WMI Options entry.
“

SSIS Options for actions on file existance (like a File Watcher)

1) File.Exists(path) call Script Task

2) ForEach File Enumerator see: http://www.sqlis.com/post/Looping-over-files-with-the-Foreach-Loop.aspx

3) File Watcher Task see: http://www.sqlis.com/post/File-Watcher-Task.aspx

SSIS: VSA requires DLLs to be in the Microsoft.Net folder (but not all the time)

from: http://blogs.conchango.com/jamiethomson/archive/2005/11/02/2341.aspx

Very interesting post, quoted below, gets around some deployment issues:

“Recently I have been using the SSIS Script Task and Script Component quite heavily. For those that don’t know, these items use Visual Studio for Applications (VSA) under the covers.

There is a peculiar limitation of VSA that requires you to place any DLLs that you want to reference into the Microsoft.Net\Framework folder of your machine – it isn’t enough to GAC it.

Happily this only needs to be done on development machines because this is a design-time requirement. When the VSA apps run they use DLLs in the GAC – they don’t need to be in the Microsoft.Net\Framework folder. Or so it seems to me anyway. To prove this I changed the name of my DLL in the Microsoft.Net\Framework folder and sure enough I got design-time errors but my packages containing script tasks/components that referenced that DLL still ran successfully. One caveat – this will only work if the script is pre-compiled – so make sure the PreCompile setting on your script task/component is set to TRUE.“

Calling C# Assembly from SSIS Script Task

To call a C# assembly from an SSIS Task you need to do the following:

1- Build the C# with strong naming ( SDK Command :>sn /K c:\key.snk, add the Key.snk to the project and use it to sign it)

2- Add the Assembly to the CAG (VS Command:>gacutil /i AssemblyName.dll)

3- Copy the assembly to the “C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727″ folder for the Develpment or Production server

4- Form the SSIS Script designer, add the reference as it should appear in the list.

5- Import the library and instentiate an object to be used for the task

from: http://oassaf.wordpress.com/2007/05/24/calling-c-assembly-from-ssis-script-task/