The number of input columns cannot be zero

Dec 2, 2014 at 4:43 PM
Edited Dec 2, 2014 at 5:15 PM
After I click Generate SSIS Packages in BIDSHelper 1.6.6, I go into the Data Flow tab on the generated package and see the following errors:

Validation error. Data Flow Task: Data Flow Task: The number of input columns for Target.Inputs[ADO NET Destination Input] cannot be zero. Validation error. Data Flow Task Target [77]: The number of input columns for Target.Inputs[ADO NET Destination Input] cannot be zero.

I can resolve the error by going into the SSIS package, double-clicking the Target, clicking on the Mappings tab, and clicking OK. I don't want to have to do that on every package every time I regenerate a package. Any thoughts on what I can do so that I can avoid this manual intervention?

Here's my BIML:
<Biml xmlns="http://schemas.varigence.com/biml.xsd">  
 <Connections>    
  <AdoNetConnection Name="EDW_Stage" ConnectionString="Data Source=SQLDWTEST;Initial Catalog=EDW_Stage;Integrated Security=SSPI;" Provider="SQL"/>      
  <AdoNetConnection Name="Product" ConnectionString="Data Source=SQLTEST;Initial Catalog=Product;Integrated Security=SSPI;" Provider="SQL" />  
 </Connections>  
 <Packages>    
  <Package Name="TestBimlPackage" ForcedExecutionValueDataType="Empty" Language="None" ConstraintMode="Parallel" ProtectionLevel="EncryptSensitiveWithUserKey" SsisPackageType="5" VersionBuild="85" CreatorName="HAI\bhurley" CreatorComputerName="ITCONSULTANT630" CreationDate="2014-11-07T12:50:51">       
   <Tasks>        
    <Dataflow Name="Data Flow Task" ForcedExecutionValueDataType="Empty">         
     <Transformations>           
      <AdoNetSource Name="Source" ConnectionName="Product" Timeout="0">                 
       <ExternalTableInput Table="&quot;DW&quot;.&quot;policy_changes_data&quot;"/>              
      </AdoNetSource>            
      <AdoNetDestination Name="Target" ConnectionName="EDW_Stage" Timeout="0" ValidateExternalMetadata="true" LocaleId="None" >              
        <Columns>                
         <Column SourceColumn="policy_id_i"/>                
         <Column SourceColumn="ChangeDate" TargetColumn="change_date"/>              
        </Columns>              
        <InputPath OutputPathName="Source.Output" SsisName="ADO NET Destination Input"></InputPath>              
        <ExternalTableOutput Table="&quot;ReplicationProduct&quot;.&quot;policy&quot;"></ExternalTableOutput>    
       </AdoNetDestination>                      
      </Transformations>        
     </Dataflow>      
    </Tasks>    
   </Package>  
  </Packages>
 </Biml>
Coordinator
Dec 2, 2014 at 10:48 PM
I think you'll find that if you define the source columns the destination will get mapped automatically. My suggestion would be to make the source a SQL query and alias the column names in the query.

eg.
<Biml xmlns="http://schemas.varigence.com/biml.xsd">  
 <Connections>    
  <AdoNetConnection Name="EDW_Stage" ConnectionString="Data Source=SQLDWTEST;Initial Catalog=EDW_Stage;Integrated Security=SSPI;" Provider="SQL"/>      
  <AdoNetConnection Name="Product" ConnectionString="Data Source=SQLTEST;Initial Catalog=Product;Integrated Security=SSPI;" Provider="SQL" />  
 </Connections>  
 <Packages>    
  <Package Name="TestBimlPackage" ForcedExecutionValueDataType="Empty" Language="None" ConstraintMode="Parallel" ProtectionLevel="EncryptSensitiveWithUserKey" SsisPackageType="5" VersionBuild="85" CreatorName="HAI\bhurley" CreatorComputerName="ITCONSULTANT630" CreationDate="2014-11-07T12:50:51">       
   <Tasks>        
    <Dataflow Name="Data Flow Task" ForcedExecutionValueDataType="Empty">         
     <Transformations>           
      <AdoNetSource Name="Source" ConnectionName="Product" Timeout="0">                 
       <DirectInput>SELECT policy_id_i, ChangeDate as change_date FROM &quot;ReplicationProduct&quot;.&quot;policy&quot;</DirectInput>              
      </AdoNetSource>            
      <AdoNetDestination Name="Target" ConnectionName="EDW_Stage" Timeout="0" ValidateExternalMetadata="true" LocaleId="None" >              
        <ExternalTableOutput Table="&quot;ReplicationProduct&quot;.&quot;policy&quot;"></ExternalTableOutput>    
       </AdoNetDestination>                      
      </Transformations>        
     </Dataflow>      
    </Tasks>    
   </Package>  
  </Packages>
 </Biml>
Dec 4, 2014 at 2:34 PM
Thanks dgosbell. That pointed me in the right direction to solve the problem.

When I ran your suggested BIML, I received the same error.

But when I changed the SQL statement to retrieve only one field (policy_id_i), the error went away. In its place, I received warnings about 4 fields being "out of synchronization with the database column. The latest column has new precision: 0 new scale: 0. Use advanced editor to refresh vailable desintation columns if needed". If I tried to change the select clause in the SQL to include any of those 4 fields, the error returned.

I didn't really believe the warning, because I was seeing the warning immediately after generating the package, and I knew the database schema wasn't changing that fast. I tried to figure out what was the same about these 4 fields, and came up empty. They were all different data types (float, datetime, timestamp).

Then I tried changing my connection/source/destination from AdoNet to OleDb, and when I did that I only got 1 warning instead of 4. And the one field that remained had a SQL TIMESTAMP datatype. Realizing that it doesn't make sense to migrate a TIMESTAMP value between databases, I removed that field from my source and destination and everything works fine now.

Here is my final working BIML:
<Biml xmlns="http://schemas.varigence.com/biml.xsd">  
 <Connections>    
  <Connection Name="EDW_Stage" ConnectionString="Data Source=SQLDWTEST;Initial Catalog=EDW_Stage;Integrated Security=SSPI;Provider=SQLNCLI11;"/>      
  <Connection Name="Product" ConnectionString="Data Source=SQLTEST;Initial Catalog=Product;Integrated Security=SSPI;Provider=SQLNCLI11;" />  
 </Connections>  
 <Packages>    
  <Package Name="TestBimlPackage" ConstraintMode="Linear" ProtectionLevel="EncryptSensitiveWithUserKey" SsisPackageType="5" VersionBuild="85" CreatorName="HAI\bhurley" CreatorComputerName="ITCONSULTANT630" CreationDate="2014-11-07T12:50:51">       
   <Tasks>        
    <Dataflow Name="Data Flow Task">         
     <Transformations>           
      <OleDbSource Name="Source" ConnectionName="Product">
        <DirectInput>select * from &quot;DW&quot;.&quot;policy_changes_data&quot;</DirectInput>
      </OleDbSource>
      <OleDbDestination Name="Target" ConnectionName="EDW_Stage" >
        <ExternalTableOutput Table="&quot;ReplicationProduct&quot;.&quot;policy&quot;"></ExternalTableOutput>
      </OleDbDestination>                            
      </Transformations>        
     </Dataflow>      
    </Tasks>    
   </Package>  
  </Packages>
 </Biml>
Marked as answer by bhurley on 12/4/2014 at 6:35 AM