My SqlComand on SSIS - DataFlow OLE DB Command seems not works

0
votes

Hello Im using OLE DB Source for get rows from a dBase IV file and it works, then I split the data and perform a group by with aggregate component. So I obtain a row with two columns with "null" value : 

CompanyID | CompanyName |  SubTotal  |    Tax      | TotalRevenue 
   Null        Null         145487      27642.53      173129.53

this success because all rows have been grouped with out taking care about the firsts columns and just Summing the valuable columns, so I need to change that null for default values as CompanyID = "100000000" and CompanyName = "Others". I try use SqlCommand on a OLE DB Command Component : 

SELECT "10000000" AS RUCCLI , "Otros - Varios" AS RAZCLI FROM RGVCAFAC 

<property id="1505" name="SqlCommand" dataType="System.String" state="default" isArray="false" description="The SQL command to be executed." typeConverter="" UITypeEditor="Microsoft.DataTransformationServices.Controls.ModalMultilineStringEditor, Microsoft.DataTransformationServices.Controls, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" containsID="false" expressionType="Notify">SELECT "10000000" AS RUCCLI , "Otros - Varios" AS RAZCLI FROM RGVCAFAC</property>

but nothings happens, why? and finally the task finish when the data is inserted on a SQL Server Table.

Im using the same connection manager on extracting data and transform. (View Code)

<DTS:Property DTS:Name="ConnectionString">Data Source=C:\CONTA\Resocen\Agosto\;Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Extended Properties=dBASE IV;</DTS:Property></DTS:ConnectionManager></DTS:ObjectData></DTS:ConnectionManager>

alt text http://web5.twitpic.com/img/30512459-a822658c0dc883a7543171829bff8d9e.4ab399cd-full.jpg

all work is on memory, Im not using cache manager connections

1
ssis
Where are the rows when you are trying to update them? In the pipeline/memory? - Sam

1 Answers

1
votes

I think this is your scenario:

  • You are extracting data from an OLEDB source which puts the data in a buffer.
  • Next you process the contents of the buffer with an Aggregate component.
  • After that, you run a Sql Command (which selects two constants from the database) which doesn't affect the buffer (or anything else).
  • Finally, you send the output of the buffer into an OLEDB destination.

Instead of the SQL Command (which doesn't affect the buffer) you need to use a derived column to replace the value in the two columns that are null with the constant values you want in those columns.

To clarify, the SQL Command task is used to take information from your data flow and effect a change on an actual database. It is not used to gather information from your database to change data in your data flow.