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.
