The easies way to do this using SQL Server 2017 and up;
DECLARE @FileName NVARCHAR(255) = N'Need to get extension of this file.xlsx';
--PRINT @FileName
SELECT
@FileName ExcelFileName,
--check there is a '.' in ExcelFileName
CASE WHEN @FileName LIKE '%.%' THEN
REVERSE(left(REVERSE(@FileName), CHARINDEX('.', REVERSE(@FileName)) - 1))
ELSE ''
END ExcelFileExtension
WHERE 1=1
Earlier versions can use this approach;
DECLARE @FileName NVARCHAR(255) = N'Need to get extension of this file.xlsx';
--PRINT @FileName
SELECT
@FileName ExcelFileName,
CASE
WHEN @FileName LIKE '%.%' THEN
RIGHT(@FileName, LEN(@FileName) - CHARINDEX('.', @FileName))
ELSE ''
END ExcelFileExtension
WHERE 1=1
I would like to get data out of Object variable in Script task or Script component. My object variable name is VersionList;
If we have used OLE DB to load data into Object variable, we need to use this;
// Set up the DataAdapter to extract the data, and the DataTable object to capture those results. This will write the contents of the Recordset to a new DataTable object.
// Create the connector
OleDbDataAdapter da = new OleDbDataAdapter();
// Create the output DataTable
DataTable dt = new DataTable();
// Load the DataTable
da.Fill(dt, Dts.Variables["VersionList"].Value);
// Now, replace the value in the VersionList variable. This SSIS variable will now contain a DataTable object filled with the results extracted from the ADO Recordset
Dts.Variables["VersionList"].Value = dt;
If we have used ADO.NET to load data into Object, then we need to use this;
try
{
var ds = Dts.Variables["User::VersionList"].Value as DataSet;
DataTable dt = new DataTable();
// Create the output DataTable
dt.TableName = "VersionList";
// Now, replace the value in the VersionList object variable. This SSIS variable will now contain a DataTable object filled with the results extracted from the ADO Recordset
dt = ds.Tables[0];
Dts.Variables["User::VersionList"].Value = dt;
Dts.TaskResult = (int)ScriptResults.Success;
}
catch (Exception ex)
{
var message = $"Error comparing version numbers. ERR: {ex.Message.ToString()}";
//MessageBox.Show(message);
Dts.Events.FireError(1, ex.TargetSite.ToString(), message, "", 0);
Dts.TaskResult = (int)ScriptResults.Failure;
}
We can use it in script component like this;
//get version and rules from VersionList variable
DataTable dt = (DataTable)Variables.VersionList;
//iterate over rows
foreach (DataRow row in dt.Rows)
{
string message = $@"Details: {row["VersionNumber"].ToString()} - {row["VersionCode"].ToString()}
- {row["TabName"].ToString()} - {row["CellRangeCode"].ToString()}
- {row["CellRange"].ToString()}";
MessageBox.Show(message);
}
If for some reasons we are using Foreach loop and keep referring to same record set, then best design is to keep it ouside Foreach loop. An example is this;
We are getting VersionList from (1). We convert it to a data table in (2). We use it inside data flow task (3) of Foreach loop container.
Shared projects are used to facilitate cross platform development. This allows you to reference an entire project as opposed to just a single assembly.
Shared project is a shred bucket of code. At compile time, any project that reference the shared project will have all of the files (including folder structure) and then they will be compiled. You wouldn’t see any separate DLL as you might have seen in PCL (Portable class libraries).
A shared project is not going to be compiled on its own. The code in the shared project is incorporated into assembly that reference it and compiled within that assembly.
Let’s create a shared project;
Create a class Math with a static method Add.
namespace SharedProject1
{
public class Math
{
public static int Add(int x, int y)
{
#if NETCOREAPP1_1
return (x + y) + 3;
#else
return (x + y) + 13;
#endif
}
}
}
Add SharedProject reference to your project. If your project is targeting .NET Core 1.1, the relevant piece of code in #if/#endif will run.
Here is some recommendation of using Shared Projects and Portable Class Libraries;
How the code is reused
Shared Projects: Source Code (All source code is available to your reference project)
PCL: Reference is available at Assembly level (for example MyLibrary.dll)
Compile time behavior
Shared Projects: All source code is copied into each referenced project and compiled there
PCL: Nothing new. Its compiled as usuall.
Visual Studio support
Shared Projects: Full Support
PCL: Each plateform is compiled separately. This can be accomplished thru IOC.
#IFDEF Support
Shared Projects: Full Support
PCL: Unsupported
.NET Framework Support
Shared Projects: Full Support
PCL: Limited
The core problem with shared project is difficulty of code testing because of conditional compilation directives. This in turn introduce errors that you wouldn’t know until you have actually compiled your application.