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.

Advertisements

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

SSIS – cannot create null column

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

SSIS cannot debug breakpoint in Script Component

No solution yet but provide some insights: When I am targeting SSIS 2012, I got this issue.

I have a Data Flow task that includes an OLE DB source and then link to Script Component. In the OLE DB source, I use a SQL command query which uses a WHERE clause. The WHERE clause seems to be the issue that causes the debugger not break into the next Script Component. After I remove it, it seems to debug in the next script component, but then this doesn’t help in my case as I need to filter some data source. I am not sure if this is a bug.

P.S> This issue only happens with Script Component, and not with Script Task.

Book – Professional Microsoft SQL Server 2012 Integration Services

SSIS – import csv with commas in data fields

Possibly save 1 hour of your time: When you have a line in csv file that contains data with comma, the SSIS package may not parse it correctly.

i.e. country name, iso code, numeric code

“Bonaire, Saint Eustatius and Saba”,BES,535

You can use double quote to signify as text qualifier in the flat file connection manager.

text qualifier

This should parse as the fields correctly.

Book – Professional Microsoft SQL Server 2012 Integration Services

SSIS error 0xC0011008 “the package failed to load due to error error loading from xml”

Possibly save 2 hours of your time: If you are starting a new SSIS project in Visual Studio, you may encounter this error.

Note: If you get error 0xC0011008 such as “the package failed to load due to error error loading from xml”, ensure you are targeting the right version of database via project properties. Right click the Project (not Solution) and choose Properties. In my case, I am using SQL Server 2012.

targetserverversion

Book – Professional Microsoft SQL Server 2012 Integration Services