Script Transformation in SSIS

• hepsi_P

  Say Hey Kid

  Points: 663

  More actions

  January 5, 2009 at 9:09 pm

  #128882

  I am using Script Transformation in my package. My source is OLEDB. I'm connecting the script with the OLEDB Source. Input to the script transformation is the entire row. Output is also entire row.

  But, how to access the row inside the scripts. Please tell me.

  Thanks in advance.

• Mukti

  SSCrazy

  Points: 2826

  More actions

  January 6, 2009 at 12:00 pm

  #922026

  You can your Row InputBuffer to access your entire row. If you want to update the row make sure in the chage the UsageType to ReadWrite.

  Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

  Row.Col1 = 1 'where col1 is one the columns in the row

  End Sub

• hepsi_P

  Say Hey Kid

  Points: 663

  More actions

  January 6, 2009 at 8:11 pm

  #922298

  Thanks mukti for your reply.

  Please tell me how to add two rows and make it as one row using script transformation.

  Thanks in advance.

• Phil Parkin

  SSC Guru

  Points: 246965

  More actions

  January 7, 2009 at 3:41 am

  #922399

  hepsi_P (1/6/2009)

  ---

  Thanks mukti for your reply.

  ... add two rows and make it as one row ...

  How can anyone help you when you ask a question like this? Please provide more detail - why are you doing this, what does the data look like and where does it come from?

  Phil

  The absence of evidence is not evidence of absence
  - Martin Rees
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin

• Jack Corbett

  SSC Guru

  Points: 184394

  More actions

  January 7, 2009 at 10:05 am

  #922695

  hepsi_P (1/6/2009)

  ---

  Thanks mukti for your reply.

  Please tell me how to add two rows and make it as one row using script transformation.

  Thanks in advance.

  What? Do you want to add columns to the row or add rows to the output?

  Here are a couple of blog posts by Jamie Thompson that may help you out:

  http://blogs.conchango.com/jamiethomson/archive/2006/12/13/SSIS-Nugget_3A00_-Unpivoting-using-the-script-component.aspx

  http://blogs.conchango.com/jamiethomson/archive/2005/07/25/SSIS-Nugget_3A00_-The-difference-between-synchronous-and-asynchronous-script-components.aspx

  Jack Corbett
  Consultant - Straight Path Solutions
  Check out these links on how to get faster and more accurate answers:
  Forum Etiquette: How to post data/code on a forum to get the best help
  Need an Answer? Actually, No ... You Need a Question

• hepsi_P

  Say Hey Kid

  Points: 663

  More actions

  January 7, 2009 at 7:51 pm

  #923049

  Thanks for all your replies. I would like to describe what I want in detail.

  I have a table, consider meterreading table.

  I have columns like,

  Date, meter1reading, meter2reading,.....meter100reading.

  values are like,

  12/2/2008 15:15:00,123.45,234.56,345.67......

  14/2/2008 11:11:00,678.90,234.50,123.78....

  I have to use SSIS. Where source table is the above table.

  the destination table also contains the same number of columns. All I need In the destination table is,

  (14/2/2008 15:15:00-12/2/2008 11:11:00),(678.90-123.45),(234.50-234.56),(123.78-345.67)....

  That is, the values in the destination table should be the substacted value between consecutive columns.

  If I have 3 rows, in the source table, then I need 2 rows in the destination table.

  THis is my requirement. Please give your suggestions on how to implement this using SSIS.

  I tried using script transformation. As I'm beginner to SSIS, could not come up with the solution.

  Please help me.

  Thank you.

• Jack Corbett

  SSC Guru

  Points: 184394

  More actions

  January 7, 2009 at 11:02 pm

  #923095

  Is the data in SQL Server? If it is I don't think you need to use SSIS to do this. Try this:

  Set DateFormat DMY

  Declare @meterreadings table(Date datetime, meter1reading float, meter2reading float, meter100reading float)

  Insert Into @meterreadings

  Select

  '12/2/2008 15:15:00',

  123.45,

  234.56,

  345.67

  Union ALl

  Select

  '14/2/2008 11:11:00',

  678.90,

  234.50,

  123.78

  ;With cteReadings As

  (

  Select

  Row_Number() Over(order by date desc) as row_id,

  date,

  meter1reading,

  meter2reading,

  meter100reading

  From

  @meterreadings

  )

  Select

  A.date,

  B.date,

  A.meter1Reading - B.meter1reading as meter1,

  A.meter2Reading - B.meter2reading as meter2,

  A.meter100Reading - B.meter100reading as meter100

  From

  cteReadings A Join

  cteReadings B On

  A.row_id = B.row_id - 1

  I had to use Set DateFormat because my default date settings are mdy.

  Jack Corbett
  Consultant - Straight Path Solutions
  Check out these links on how to get faster and more accurate answers:
  Forum Etiquette: How to post data/code on a forum to get the best help
  Need an Answer? Actually, No ... You Need a Question

• hepsi_P

  Say Hey Kid

  Points: 663

  More actions

  January 8, 2009 at 7:31 pm

  #923858

  Thanks Jack.