SSIS – The value violated the integrity constraints for the column

Possibly save 1 hour of your time: I got this error when I am extracting data from a file and pushing those data to database.

This issue stems from the data missing values. Hence, it was trying to insert NULL values into a non-null column.

This is easily fixed by changing the database table column to allow NULL values. If you don’t expect null values, then you will have to filter those data out before inserting into the database.

Advertisements

Request to run job refused because the job is already running from a request by user

Possibly save 1 hour of your time: I have a job that can take awhile to finish on development environment. I have started running the job and realize something I have missed. Now I want to stop or cancel the job, but I get this error telling me that the job is already running.

Solution: The easiest way is to restart the SQL Server Agent Service by going to SQL Server Configuration Manager or sqlservermanager11.msc in command prompt.

 

 

 

SSIS – File path is not valid – job failed on reading second file path

Possibly save 1 hour of your time: We have a SQL Server agent job that runs multiple SSIS packages. Each package is independent. Each package will process a data file that is located on another server. The data files for all packages are stored at same location. When the job runs, it always failed on the second package regardless of which data file it is looking at. So if step 1 ran, then step 2 will failed. The next time you run it, Step 2 will passed and Step 3 will failed on reading the file. The error will say the file path to the file is not valid.

Funny enough, when we give full administrator right to the identity running the job, all the steps passed.

We start thinking this was a permission issue so we check all the database permissions for this identity and could not find the issue.

Solution: We found out that there was a mapped share drive created. This was causing the issue. We use UNC path as environment variable to find the file. However the mapped share drive has the same beginning path like \\server\folder. Once we removed the mapped share drive, the job is reading subsequent file properly and behaving as expected.

SSIS – Could not find stored procedure sp_ssis_addlogentry

Possibly save 1 hour of your time: For some reason when the SSIS package was deployed in a different environment and when it ran, it gives this error.

The stored procedure is not automatically created.

Solution: In my case, the issue was actually related to the SQL Server Agent job not setup probably to tie to the environment variables. For some reason that causes this issue.

After the job steps have the environment variables linked, then job is running fine and the stored procedure was automatically created.

 

SSIS – no column information was returned by the sql command

Possibly save 1 hour of your time: In SSIS, I have a data flow task that selects the source with some logic using table variable. It causes this compilation error when you try to save.

SSIS doesn’t know like using table variable in the SQL command.

Solution:

I have some complex logic so I need to use variable table. Temp table would most likely have same issue. So I just use a stored procedure instead. In my SQL command I have exec MyStoredProcedureName.

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

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.

 

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

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