SSIS – concatenate variables with SQL query

In previous post, SSIS – dynamic file name I was generating dynamic file name.
Later in my project I had combine it with dynamic file path, stored in variable.
For this scenario I will reuse previous project. All I have to do is add existing variable which stores file location to Parameter Mapping.

ssis01

Does not matter how many parameters you have, just remember to number them correctly (0, 1, 2…) because we are going to use parameter mapping (?
) in our SQL query

All we have to do is modify our query like below. Notice how @Path gets value. ? represents FileLocation variable.

DECLARE @Path Varchar (256)
SET @Path = ?

SELECT @Path + 'SomeFile_' +
CAST(DATEPART(yyyy,GetDate()) AS NVARCHAR(4)) +
CAST(RIGHT('0' + DATEPART(mm,GetDate()) ,2) AS NVARCHAR(2)) +
CAST(RIGHT('0' + DATEPART(dd,GetDate()) ,2) AS NVARCHAR(2)) +
'.xls' as SQLFileName

--or

@[User::FileDestination] + @[User::FileNamePrefix] 
+ (DT_STR,4,1252)YEAR( DATEADD( "dd", -1, getdate() )) + (DT_STR,4,1252)MONTH( DATEADD( "dd", -1, getdate() )) + (DT_STR,4,1252)DAY( DATEADD( "dd", -1, getdate() )) + ".csv"

SSIS – dynamic file name (date as file name)

We will be using Execute SQL Task for that, quick and easy.
First we need a variable, say FileName with type String

ssis01

Properties of SQL Task:

ssis02

  • Connection: create SQL Connection to any database, does not matter which one
  • SQLSourceType: Direct Input – we will create simple inline query
  • SQLStatement: click on 3 dots (…) and paste query below:
    SELECT 'SomeFile_' +
    CAST(DATEPART(yyyy,GetDate()) AS NVARCHAR(4)) +
    CAST(RIGHT('0' + DATEPART(mm,GetDate()) ,2) AS NVARCHAR(2)) +
    CAST(RIGHT('0' + DATEPART(dd,GetDate()) ,2) AS NVARCHAR(2)) +
    '.xls' as SQLFileName
    

    File will be called SomeFile_date.xls and saved with SQLFileName alias which will be accessing in next step

  • ResultSet: Single Row
  • Switch to Parameter Mapping of SQL Task.

    We will be mapping to previously created FileName variable
    Type VARCHAR, Parameter 0

    ssis03

    Now we need to set our FileName variable value

    • Switch to Result Set
    • click Add
    • type SQLFileName (our previously created alias in SQL Statement)
    • select User::FileName viariable

    ssis04

    To test it, create Script Task

    And connect with our SQL Task:

    ssis05

    Map ReadOnlyVariable property with our FileName variable and click Edit Script

    ssis06

    After code editor shows up, find line saying

    // TODO: Add your code here

    and paste line below

    MessageBox.Show(Dts.Variables["User::FileName"].Value.ToString());

    ssis07

    Now you can simply close script editor.
    Task execution will pop up window with file name

    ssis08

Multithreading in .NET 4.0

.NET 4.5 has way more multhreading and asynch features than 4.0.
Unfortunately I was stuck with 4.0 and had to create simple multithreading solution for one of project modules.
It appeared it was really simple to use:

int param1 = 10;
var task = Task.Factory.StartNew(() => CallSomeMethod(param1));

That’s it!

Entity Framework – Attaching an entity of type failed because another entity of the same type already has the same primary key value

This happened to me while updating model from viewmodel and changing its State to Modified.
Before save, I wanted to check model property values existing in the database.
The solution is to use AsNoTracking() with your query. This way newly retrieved object won’t be tracked by the context. That means context will not recognize that the object has the same Id as a primary key value.

if (entity.CustomerID > 0)
{
	Customer existing =  _context.Set<Customer>().AsNoTracking().Where(c => c.CustomerID == entity.CustomerID).FirstOrDefault();
	entity.PasswordHash = existing.PasswordHash;
	entity.PasswordSalt = existing.PasswordSalt;
	entity.rowguid = existing.rowguid;
}

Entity Framework – Validation failed for one or more entities. See ‘EntityValidationErrors’ property for more details.

This is how Visual Studio tells you that something went wrong while updating your entity.
Simple solution would be to catch System.Data.Entity.Validation.DbEntityValidationException and iterate through EntityValidationErrors collection.

try
{
	return _context.SaveChanges();
}
catch (System.Data.Entity.Validation.DbEntityValidationException dbvEx)
{
    List<string> errors = new List<string>();
    foreach (var validationErrors in dbvEx.EntityValidationErrors)
    {
        foreach (var validationError in validationErrors.ValidationErrors)
        {
            errors.Add(string.Format("Property: {0} Error: {1}", validationError.PropertyName, validationError.ErrorMessage));
        }
    }
    return 0;
}

errors

Unity – The type Interface does not have an accessible constructor

Had a constructor accepting string parameter

public CustomerRepo(string dbContext)
{
	if (dbContext == "AWContext")
	{
		_context = new AWContext();
	}
	else
	{
		throw new ArgumentException("Invalid dbContext name");
	}
}

registered following instructions from MSDN

container.RegisterType<ICustomerRepo, CustomerRepo>(
    "test", 
    new InjectionConstructor("AWContext")
);

The “test” is just a name of current mapping. But it didn’t work until I completely removed it(!):

container.RegisterType<ICustomerRepo, CustomerRepo>(
    new InjectionConstructor("AWContext")
);

This database file is not compatible with the current instance of SQL Server

This error occurred to me in Visual Studio 2013 while create connection to local AdventureWorks 2012 mdf file.

This database file is not compatible with the current instance of SQL Server. To resolve this issue, you must upgrade the database file by creating a new data connection, or you must modify the existing connection to this database file. For more information, see http://go.microsoft.com/fwlink/?LinkId=235986

In order to upgrade the database, just follow steps described under the link above:

To upgrade a database file to use LocalDB

  1. In Server Explorer, choose the Connect to Database button.
  2. In the Add Connection dialog box, specify the following information:
    • Data Source: Microsoft SQL Server (SqlClient)
    • Server Name: (LocalDB)\v11.0
    • Attach a database file: Path, where Path is the physical path of the primary .mdf file.
    • Logical Name: Name, where Name is the name that you want to use with the file
  3. Choose the OK button.
  4. When prompted, choose the Yes button to upgrade the file

The database is upgraded, attached to the LocalDB database engine, and no longer compatible with SQL Server 2008 Express.

C# String was not recognized as a valid DateTime

Sometimes while testing someones code, I am getting such exception. Last thing what I want to do is to is fix it :)
For example:

// ...
var p = new List<Person>()
{
    new Person() { FirstName="John", LastName="Koenig",
	StartDate = DateTime.Parse("10/17/1975"), Rating=6 },
// ...

This date format, looks like US one, would not work if your current Culture is, say, Canadian.
Just override system setting by adding following line before the code. (If you have different time
format, adjust CultureInfo setting accordingly of course)

// ...
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
// ...