SSIS – How to setup parameter for Flat File Connection Manager connection string

Possibly save 1 hour of your time: This shows how you can add an expression to use as parameter for the Flat File Connection Manager or any other ones.

Right click on your connection manager and choose Properties. The properties window will show up. Find the Expressions and then click on the ellipsis on the right.

In the Property Expressions Editor that opens, you can now choose different Property such as ConnectionString. In my case, I set it to a user variable that tells me where the error log file is.

expression parameter

Advertisements

SSIS – Add logging to SQL Server database table sysssislog

Possibly save 1 hour of your time: This shows how you can add logging for your SSIS package to SQL Server database table sysssislog. The table is automatically created and records will be added automatically.

Open your package in Visual Studio or SSDT. On the top menu, select SSIS >> Logging. Check the checkbox at the top so that you want logging at the package level. Then on the right pane, select provider type of SSIS log provider for SQL Server. Then click Add to add the provider. Check the checkbox for the provider and select your database configuration.

configure SSIS Logs part 1

On the Details tab, you can check the checkbox for logging OnError and OnTaskFailed. This will ensure that you will get log in your table when there is a package error or when a task failed.

configure SSIS Logs part 2

After you write to log to your table, you can also setup Send Mail Task on these log data. On the tab Event Handlers in Visual Studio or SSDT, you can set up OnTaskFailed with Data Flow task to get data from this database table sysssislog. Then you can generate the log flat file somewhere. Then use Send Mail Task to email this log.

 

SQL Server database – changes from SIMPLE to FULL recovery mode after dacpac deployment

Possibly save 1 hour of your time: Our SQL Server ran out of disk space and my operations team found out that one of the database is recording huge transaction logs because the database is in FULL recovery mode in our system test environment.

What causes it? We ran the following command to pinpoint which process changes the recovery mode and at what time. Based on the date and time, we figure out that this happens at similar time as our deployment.

Sp_readerrorlog 0, 1, 'recovery'

Then you can run one of the following to determine the user and time when the recovery mode was changed. id below should be replaced with actual id from above query.

sp_who id

or

sp_who2

The cause of this was from a setting I was not aware of when creating the dacpac deployment file. To change this setting, right click on your database project and choose Properties. Under Project Settings, click on Database Settings. Then Operational. Change Recovery to SIMPLE or FULL as desired.

SIMPLE recovery mode

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

Possibly save 1 hour of your time: This error happens after I deployed a script to create my SQL Server Agent job with SSIS packages as steps.

This error basically means your package is not referencing the proper environment. The configuration on your package needs to be associated with the right environment id.

Solution:

  • Right click on your SQL Server Agent job, and choose Steps. The step in our case represents each SSIS package.
  • Click on a specific step and click edit.
  • Then click on Configuration.
  • At the bottom, check the box Environment and then select the environment from the dropdown you have created for the project (You can verify the environment is created under Integration Services Catalogs >> SSISDB >> Project Name >> Environments). Click OK.
  • That should fix most people’s issues when they manually create the job.
  • In our case, we automate using script to create the job. So we can right click on the job and Script Jobs As >> Create To >> New Query Editor Window.
  • By looking at the script, you can find the @command variable for each step. On the value, you will see ENVREFERENCE and then a number. That number is the id you need to use in your script to tie the job to the environment reference properly.

ENVREFERENCE.png

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 – import csv with UTF-8 encoding to avoid weird characters

Possibly save 1 hour of your time: After you import a csv data file into your SQL Server database, you realize some data has funny characters.

That is because your csv file is probably encoded in ANSI. Make sure your original file is in UTF-8 encoding and ensure all characters appear as normal.

You can fix this in notepad as follows. I change the xA0 to a space and save as encode in UTF-8.

encode utf-8

Book: Professional Microsoft SQL Server 2012 Integration Services

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.

web.config

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.

content include.png

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.

configuration manager.png

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