SSIS error – The environment reference id is not associated with the project.

This content has been migrated to:

https://www.echofavor.com/content/view/1784

Rename failed for Database Microsoft SqlServer. The database could not be exclusively locked to perform the operation

Possibly save 1 hour of your time: You want to rename a database that is shared with other developer and you receive this error.

Solution:

</pre>
DECLARE @DbName nvarchar(50)
SET @DbName = N'replace_your_database_name'

DECLARE @EXECSQL varchar(max)
SET @EXECSQL = ''

SELECT @EXECSQL = @EXECSQL + 'Kill ' + Convert(varchar, SPId) + ';'
FROM MASTER..SysProcesses
WHERE DBId = DB_ID(@DbName) AND loginame not like '%replace_your_login_name%'

EXEC(@EXECSQL)
<pre>

The above will remove other people’s connections. Once you close your query window, you can try to rename the database now.

However, I still get message that you cannot kill your own process.  Then you need to run the above SELECT statement without checking for your loginname to determine what other sessions you have. Then you can manually run EXEC(‘Kill 78;’). The number here should be your process id.

T-SQL – Arithmetic overflow error converting numeric to data type numeric.

Possibly save 1 hour of your time: When you have a float data value operation in T-SQL and assign it to a variable with less precision, you may get this error.

The following will give you this error since float cannot be converted to decimal.

Set PercentValue (decimal(5,2)) = Value (float) / AnotherValue (float)

Solution:

Increase the decimal data type to allow more values and precision like decimal (10, 4).

 

 

 

 

 

 

SSIS – Script component is executed in wrong order or multiple times

Possibly save 1 hour of your time: This actually happens when I copy the package file.

The Script Component id remains the same. Hence it was executing a previous package code. It is documented here by Microsoft:

https://support.microsoft.com/en-us/help/928323/fix-a-script-task-or-a-script-component-is-executed-multiple-times-in

One resolution mentioned there was to delete the script component and paste it again. It didn’t work for me. The id was still the same as before. You can see the id when you open in edit mode and it is near the top of the window.

The resolution for me is to recreate the script component. I just have to redo it unfortunately.

Book: Professional Microsoft SQL Server 2012 Integration Services

Add nested dependent web.config transformation

Possibly save 1 hour of your time: When you want to do a continuous integration build, you may want to specify different web.config or app.config used for the transformation based on a specific environment configuration.

In my environment, we used web.config for local use. Then we have SYST for system integration environment (team check in), UAT for user acceptance testing environment and finally PROD for production environment.

So you add a web.SYST.config and it is not nested under the web.config. It should look like this.

Some people suggest right click on the Web.config and find Add Config Transform. That did not show up for me.

The alternative is to go into the csproj file directly and edit the include to have DependentUpon element.

You also need to right click on the Solution and choose Configuration Manager. Under Active solution configuration, you can select New to create your own configuration. The name used here should also reflect the name in the config file.

Book:Murach’s ASP.NET 4.6 Web Programming with C 2015