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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
- 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.
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.
Book: Professional Microsoft SQL Server 2012 Integration Services
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:
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
Possibly save 1 hour of your time: When you have a data task flow that imports data from a flat file source and into a OLE DB destination database, you may encounter this issue where it fails to create the record because some columns have default values.
On your OLE DB destination, you must use Data Access mode of Table or view instead of Table or view (fast Load). Fast load does not provide ability to create records with default values.
Book – Professional Microsoft SQL Server 2012 Integration Services