BIML OleDbDestination Error

Topics: Standing Discussions
Nov 28, 2011 at 9:26 AM

I have a problem with BIML OleDbDestination.

I created a small BIML package as a template for basic projects.

Everything works fine until I added a OleDbDestination component.

 

This is my dataflow code:

<Transformations>

<OleDbSource Name="Read from Staging" ConnectionName="ETLStagingDb">

<DirectInput>SELECT name FROM sys.objects</DirectInput>

</OleDbSource>

<RowCount Name="Number Of Rows input"  VariableName="User.NrOfIn" />

<RowCount Name="Number Of Rows Output"  VariableName="User.NrOfOut" />

<Multicast Name="Multicast"/>

<OleDbDestination Name="Write into " ConnectionName="ETLDWHDb" UseFastLoadIfAvailable="true" >

<ExternalTableOutput Table ="[dbo].[test]" />

</OleDbDestination>

</Transformations>

 

For the last part (OleDbDestination) I get the following Error

Description:
Component Write Into of Type AstOleDbDestinationNode in Dataflow Read From does not have all inputs assigned.

Recommendation:

Ensure that the component  has an input mapped to it.

 

Every example that I found on the internet does not require more parameters.

My table does exist on my database.

 

If I remove the node ExternalTableOutput, I get the following Error message:

Required property ‘Destination’ was not supplied for ‘BIML Sample.Read From.Write Into’

But the property Destination does not exists in my version of bidshelper (V1.5.0.0)

 

Could someone tell me what I’m doing wrong?

 

Thx

Karel

Coordinator
Nov 30, 2011 at 6:02 PM

There are 2 problems in the Biml above. The first is that the Multicast component has no defined outputs.  You can add some like this (only include 1 OutputPath if you want a single output):

	<Multicast Name="Multicast1">
            <OutputPaths>
                <OutputPath Name="Output1" />
                <OutputPath Name="Output2" />
            </OutputPaths>
        </Multicast>
Now that the Multicast has outputs, you you need to tell the OleDbDestination how to map to it. Since a Multicast can have multiple outputs, we can't
automatically determine which output you want to use. So you can make it explicit by defining the InputPath on the OleDbDestination:

<OleDbDestination Name="Write into " ConnectionName="ETLDWHDb" UseFastLoadIfAvailable="true" >
<InputPath OutputPathName="Multicast1.Output2" />
<ExternalTableOutput Table ="[dbo].[test]" />
</OleDbDestination>

Hope that helps.

-John
Dec 1, 2011 at 8:42 AM

Hi John

THx for the feedback. I get a total different error message now:

Internal compiler error: Workflow EmitSSIS contains fatal errors. Phase execution halted

For assistance, please send Hadron error message to support@varingence.com

 

I got no error messages anymore.

So I created my final package

 

	<!--<Connection Name="BFC" ConnectionString="Server=BRUVM129;Initial Catalog=BusinessGrowthCockpit_Staging;Integrated Security=SSPI;Provider=SQLNCLI10" />-->
<Biml xmlns="http://schemas.varigence.com/biml.xsd"> 
	<Connections> 
		<!--<Connection Name="BFC" ConnectionString="Server=.;Initial Catalog=SourceDb;Integrated Security=SSPI;Provider=SQLNCLI10"/>--> 
		<Connection Name="ETLLoggingTable" ConnectionString="Server=.;Initial Catalog=DB_LoggingDb;Integrated Security=SSPI;Provider=SQLNCLI10"/>    
		<Connection Name="ETLStagingDb" ConnectionString="Server=.;Initial Catalog=DB_Staging;Integrated Security=SSPI;Provider=SQLNCLI10"/>    
		<Connection Name="ETLDWHDb" ConnectionString="Server=.;Initial Catalog=DB_DWH;Integrated Security=SSPI;Provider=SQLNCLI10"/>  
	</Connections>  
	<Packages>    
		<Package Name="Read in Staging" AutoCreateConfigurationsType="None" ConstraintMode="Linear">      
		<Variables>        
			<Variable Name="DbNameDWH" DataType="String">DB_DWH</Variable>        
			<Variable Name="DbNameStaging" DataType="String">DB_Staging</Variable>        
			<Variable Name="DbServerName" DataType="String">.</Variable>        
			<Variable Name="ETLRunId" DataType="Int32">0</Variable>        
			<Variable Name="MasterPackageName" DataType="String">Read from Source into Staging</Variable>        
			<Variable Name="NrOfDeletions" DataType="Int32">0</Variable>        
			<Variable Name="NrOfErrors" DataType="Int32">0</Variable>        
			<Variable Name="NrOfIn" DataType="Int32">0</Variable>        
			<Variable Name="NrOfNew" DataType="Int32">0</Variable>        
			<Variable Name="NrOfOut" DataType="Int32">0</Variable>        
			<Variable Name="NrOfUpdates" DataType="Int32">0</Variable>     
		</Variables>
		<Tasks>        
			<ExecuteSQL Name="Log ETL Start" ConnectionName ="ETLLoggingTable" ResultSet="SingleRow">          
				<Expressions>            
					<Expression PropertyName="SqlStatementSource">"DECLARE @InsertedRows AS TABLE (Id int)INSERT INTO ETL.Run (ExecutionInstanceGUID, Machine, Version, PackageName, StartTime) 
OUTPUT INSERTED.ETLRunId INTO @InsertedRows VALUES ('" +  @[System::ExecutionInstanceGUID] + "', '" +  @[System::MachineName] + "', '" + @[System::ProductVersion] + "', '" + @[System::PackageName] 
+ "', getdate())SELECT * FROM @InsertedRows" </Expression>          
				</Expressions>          
				<DirectInput>Must be here for no errors</DirectInput>          
				<Results>            
					<Result Name="Id" VariableName="User.ETLRunId" />          
				</Results>        
			</ExecuteSQL>          
			<Dataflow Name="Stage Ct_">            
				<Events>              
					<Event Name="OnPreExecute" ConstraintMode ="Linear" EventType ="OnPreExecute">                
					<Tasks>                  
						<ExecuteSQL Name="Log Start" ConnectionName ="ETLLoggingTable" ResultSet="None">                    
							<Expressions>                      
								<Expression PropertyName="SqlStatementSource">@[System::SourceDescription] == "No Logging" ? "SELECT 0 AS DoNothing" : "INSERT INTO ETL.RunDetail (TaskName, [LoopDescription], [TaskType], StartTime, [NrOfNew], [NrOfChangingUpdates], [NrOfHistoricalUpdates], [NrOfDeletions], [NrOfErrors], [Status], ETLRunId) VALUES ('" + @[System::SourceName] + "', 'LoopDescription', 'TaskType', getdate(), 0, 0, 0, 0, 0, 'Started', " + (DT_WSTR, 20) @[User::ETLRunId] + ")"</Expression>
							</Expressions>
							<DirectInput>Must be here for no errors</DirectInput>
						</ExecuteSQL>
        	        		</Tasks>
				</Event>
				<Event Name="OnPostExecute" ConstraintMode ="Linear" EventType ="OnPostExecute">
					<Tasks>
						<ExecuteSQL Name="Log End" ConnectionName ="ETLLoggingTable" ResultSet="None">
							<Expressions>
								<Expression PropertyName="SqlStatementSource">@[System::SourceDescription] == "No Logging" ? "SELECT 0 AS DoNothing" : "UPDATE ETL.RunDetail SET EndTime = getdate(), NrOfIn = " + (DT_WSTR, 10)@[User::NrOfIn] + ", NrOfOut = " + (DT_WSTR, 10)@[User::NrOfOut] + ", NrOfNew = " + (DT_WSTR, 10)@[User::NrOfNew] + ", NrOfChangingUpdates = " + (DT_WSTR, 10)@[User::NrOfUpdates] + ", NrOfHistoricalUpdates = " + (DT_WSTR, 10)@[User::NrOfUpdates] + ", NrOfDeletions = " + (DT_WSTR, 10)@[User::NrOfDeletions] + ", NrOfErrors = " + (DT_WSTR, 10)@[User::NrOfErrors] + " , Status = 'Done' WHERE ETLRunId = " + (DT_WSTR, 10) @[User::ETLRunId] + " AND TaskName = '" + @[System::SourceName] + "'"  </Expression>
							</Expressions>
							<DirectInput>Must be here for no errors</DirectInput>                
						</ExecuteSQL>                  
						<ExecuteSQL Name="Reset counters" ConnectionName ="ETLLoggingTable" ResultSet="SingleRow">                    
							<DirectInput>SELECT 0 AS Reset</DirectInput>                    
							<Results>                      
								<Result Name="Reset" VariableName="User.NrOfIn" />                      
								<Result Name="Reset" VariableName="User.NrOfNew" />                      
								<Result Name="Reset" VariableName="User.NrOfOut" />                      
								<Result Name="Reset" VariableName="User.NrOfUpdates" />
								<Result Name="Reset" VariableName="User.NrOfDeletions" />                      
								<Result Name="Reset" VariableName="User.NrOfErrors" />                  
							</Results>                  
						</ExecuteSQL>                
					</Tasks>              
				</Event>            
			</Events>            
			<Transformations>              
				<OleDbSource Name="Read from in Staging" ConnectionName="ETLDWHDb">                
					<DirectInput>SELECT object_Id, Name FROM sys.objects</DirectInput>              
				</OleDbSource>              
				<RowCount Name="Number Of Rows input"  VariableName="User.NrOfIn" />   
				<DerivedColumns Name="Transform Source data"></DerivedColumns>              
				<RowCount Name="Number Of Rows Output" VariableName="User.NrOfOut" /> 
				<Multicast Name="Multicast"> 
					<OutputPaths> 
						<OutputPath Name="Output1" /> 
						<OutputPath Name="Output2" /> 
					</OutputPaths> 
				</Multicast>              
				<OleDbDestination Name="Write into " ConnectionName="ETLStagingDb" UseFastLoadIfAvailable="true" > <InputPath OutputPathName="Multicast.Output1" />                
					<ExternalTableOutput Table ="Staging_Flow" />              
				</OleDbDestination>            
			</Transformations>          
			</Dataflow>        
				<ExecuteSQL Name="Log ETL End" ConnectionName ="ETLLoggingTable" ResultSet="None">          
					<Expressions>            
						<Expression PropertyName="SqlStatementSource">"UPDATE ETL.RunSETEndTime = getdate() WHERE ETLRunId = " + (DT_WSTR, 20) @[User::ETLRunId]</Expression>          
					</Expressions>          
					<DirectInput>Must be here for no errors</DirectInput>        
				</ExecuteSQL>      
			</Tasks>    
		</Package>  
	</Packages>
</Biml>

 

If you want to try my scenario: here are the scripts for the ETL tables:

CREATE TABLE [ETL].[Run]

([ETLRunId] [int] IDENTITY(1,1) NOT NULL,

[ExecutionInstanceGUID] [nchar](40) NOT NULL,

[Machine] [varchar](50) NOT NULL,

[Version] [varchar](50) NOT NULL,

[PackageName] [varchar](50) NOT NULL,

[StartTime] [datetime] NOT NULL,

[EndTime] [datetime] NULL,

[Duration]  AS ([EndTime]-[StartTime]), 

CONSTRAINT [PK_ETLRun] PRIMARY KEY CLUSTERED ([ETLRunId] ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]) ON [PRIMARY]

 


CREATE TABLE [ETL].[RunDetail]

( [ETLRunDetailId] [int] IDENTITY(1,1) NOT NULL,

 [TaskName] [varchar](50) NOT NULL,

 [LoopDescription] [varchar](50) NOT NULL,

 [TaskType] [varchar](50) NOT NULL, 

[StartTime] [datetime] NOT NULL,

[EndTime] [datetime] NULL, [Duration]  AS ([EndTime]-[StartTime]),

[NrOfIn] [int] NOT NULL,

[NrOfOut] [int] NOT NULL,

[NrOfNew] [int] NOT NULL,

[NrOfChangingUpdates] [int] NOT NULL,

[NrOfHistoricalUpdates] [int] NOT NULL,

[NrOfDeletions] [int] NOT NULL,

[NrOfErrors] [int] NOT NULL,

[Status] [nvarchar](50) NOT NULL,

[ETLRunId] [int] NOT NULL, 

CONSTRAINT [PK_ETLRunDetails] PRIMARY KEY CLUSTERED ( [ETLRunDetailId] ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]) ON [PRIMARY]

Dec 1, 2011 at 8:45 AM

Forgot the mention!

If I remove the OLE DB destination, I don't get an error. SO there is a problem with the OleDb destination

Karel

Coordinator
Dec 1, 2011 at 3:36 PM

Does the table Staging_Flow exist in your database? The OLE DB Destination doesn't create it automatically.

Dec 1, 2011 at 3:44 PM

Yes. Even the column names are the same!

I first thought that this was the problem. But I always get the same error.

Even the datatypes are the same.

Coordinator
Dec 1, 2011 at 6:10 PM

Found the problem - there is a space at the end of the OleDbDestination's name: 

<OleDbDestination Name="Write into " 

If you take the space out, it compiles correctly. I'll file a bug to investigate why that causes an error.
Dec 2, 2011 at 7:09 AM

Thanks John

 

This was indeed my problem. I never found it without your help!

Thanks a lot.

 

Keep up the good work

 

Karel