BIML Scripts fails package generation on second attempt with no changes to files

2
votes

First time posting a question, please forgive if I don't have enough information. I have a tiered BIML Script that has the following tiers:

  • 10-Connection – create the connection nodes
  • 20- Model – loop through the connections to build database, table nodes
  • 30-Create/Drop Staging Tables – This is included as the package/s need to be run prior to the remainder of the creation process.
  • 30- Flat File – loop through the table object to create flat file formats and connections
  • 40-Packages – loop through the table objects and create extract and load packages
  • 45-Project Params & Connections– attaches the project params and connections (using named connections and GUIDs from 10-Connections). Project params manually created in SSIS.

The process successfully connects to the source SQL Server database, generates the Create/Drop Staging Tables packages with correct metadata, and will create the extract packages successfully the first time.

Upon a second attempt to process the same BIML scripts with no changes made to the files, the process fails with “Object reference not set to an instance of an object.” & “Unable to Query on Connection” on the OleDBSource Node.

The BIML files generated in preview and output debugging have valid queries and source metadata that indicate a positive connection and proper model. I have used emitted queries in SSMS without error. When I move the BIML files to a new project, the process is successful the first time and fails subsequently.

I have tried the following:

Connection Managers

  • Delete project connection managers prior to package re-generation
  • GUIDs annotated and used in PackageProject and Packages Nodes.
  • Delay Validation/Validate External Metadata – I have tried with both true and false on Package, DFT and OleDBSource

Project

  • Delete .proj files from directory
  • Direct PackageProject to new ProjectSubpath

I also tried simply hard coding the BimlScript to simplify and remove any variables with the same result.

The most maddening point is that the metadata and queries all indicate the process can connect and query this exact table and it functions, but only on initial creation. Adding or re-generating during testing fails. Anyone ever come across this before?

Great thanks and shout out to cathrine-wilhelmsen, billinkc, whose posts and tutorials have been very helpful. Any and all help would be greatly appreciated.

1
ssisbiml
Solid first question and you're most welcome. Your workflow is 1) select T10, T20, T30 right click and generate packages. 2) Run packages to ensure tables are dropped and created. 3) Select T10, T20, T30.5, T40, T50 and right click generate packages. Doing all that works great but if you repeat it, which step fails, 1 or 2? – billinkc
@billinkc - Thank you very much. Step one never fails, it is always the second step, after running the drop and re-create table packages. For example, if I add a table to my list of tables, drop and recreate (step one) runs fine. The second step to generate the extract packages fails. Depending on what I have wiped out, and I have tried this dozens of times, clearing the cache, removing proj files, etc, it will sometimes work once, then back to this error. Thanks again. – jpvangurp
My mental model says you're doing things as one would expect, any chance you can distill your problem down into a repro? – billinkc
Sorry for the delay. I have narrowed this down to one table. When I remove this table from the list, everything else functions as expected. Any thoughts on why a certain table would cause this? What level of detail is needed in a repo to be useful? Apologies for the green horn question, but I will not be able to provide connection, database name, etc so I am not sure how helpful I can be. – jpvangurp
How about adding the offending table definition itself? Is it on SQL Server, if so, what version? – billinkc

1 Answers

0
votes

I changed the driver from SQLNCLI11 to SQLOLEDB with no changes to code. I tested different drivers after seeing a few example connection strings with different drivers.

I wish I could explain why.