Data conversion in script component?

• Charmer

  SSChampion

  Points: 14903

  More actions

  January 6, 2012 at 8:33 am

  #262574

  Hi Friends,

  i have a Excel source and Oledb destination...

  some times my work goes static...

  for example if there will be 10 columns in excel sheet...

  i want to write some codes in script component to check the data type of the column and need to change according to my destination as it is mostly static for some work(because same table for different excel sources)...i don't want to use data conversion for every package and wasting my time....

  i hope this would be possible but if it is not, kindly excuse me..

  pleas give your suggestions friends

  Thanks,
  Charmer

• Martin Schoombee

  SSCoach

  Points: 19128

  More actions

  January 6, 2012 at 9:14 am

  #1429702

  Data from an Excel source is always unicode, so unless your destination table's columns are all unicode you have no choice but to use the data conversion task to convert to non-unicode data types.

• Charmer

  SSChampion

  Points: 14903

  More actions

  January 10, 2012 at 4:13 am

  #1430983

  Martin Schoombee (1/6/2012)

  ---

  Data from an Excel source is always unicode, so unless your destination table's columns are all unicode you have no choice but to use the data conversion task to convert to non-unicode data types.

  i found some sample coding which we can used for converting data types...but i am not sure how far we can use this...?

  public override void ProcessInput(int InputID, PipelineBuffer Buffer)

  {

  bool fireAgain = true;

  ComponentMetaData.FireInformation(0, "",

  Buffer.ColumnCount.ToString() + " columns",

  "", 0, ref fireAgain);

  while (Buffer.NextRow())

  {

  for (int columnIndex = 0;

  columnIndex < Buffer.ColumnCount;

  columnIndex++)

  {

  string columnData = null;

  if (Buffer.IsNull(columnIndex))

  {

  columnData = "is NULL";

  }

  else

  {

  BufferColumn columnInfo = Buffer.GetColumnInfo(columnIndex);

  switch (columnInfo.DataType)

  {

  case DataType.DT_BOOL:

  columnData = Buffer.GetBoolean(columnIndex).ToString();

  break;

  case DataType.DT_WSTR:

  columnData += Buffer.GetString(columnIndex);

  break;

  // add code to support more data types here

  default:

  columnData = "";

  break;

  }

  }

  ComponentMetaData.FireInformation(0, "",

  "Column " + columnIndex.ToString() + ": " + columnData,

  "", 0, ref fireAgain);

  }

  }

  base.ProcessInput(InputID, Buffer);

  }

  Thanks,
  Charmer

• Martin Schoombee

  SSCoach

  Points: 19128

  More actions

  January 10, 2012 at 7:38 am

  #1431154

  You will have to test it in different scenarios to ensure that it works.

  I understand that creating a data conversion task for every package is a little bit of a pain, but before you go down this current road I would suggest that you carefully consider the whether it is really worth obfuscating some task which is already available in SSIS (in pre-packaged format), and also possibly making debugging more cumbersome in the process.

• Charmer

  SSChampion

  Points: 14903

  More actions

  January 10, 2012 at 7:44 am

  #1431165

  Martin Schoombee (1/10/2012)

  ---

  You will have to test it in different scenarios to ensure that it works.

  I understand that creating a data conversion task for every package is a little bit of a pain, but before you go down this current road I would suggest that you carefully consider the whether it is really worth obfuscating some task which is already available in SSIS (in pre-packaged format), and also possibly making debugging more cumbersome in the process.

  i tried to use this script but i get some error and i am not able to resolve it since i am not C# coder. bad luck for me.:satisfied:. but keep trying on that...

  Thanks,
  Charmer

• herladygeekedness

  SSCertifiable

  Points: 5878

  More actions

  January 11, 2012 at 8:48 am

  #1431854

  Script component uses VSA so is easier to learn if you aren't a C coder.

  I personally would start over with the script component and toss what you found. Debugging the component is ... difficult at best, with your own code. Figuring out where someone else's code went wrong just adds time and frustration to your task.

  I haven't done a lot of type-testing, conversions in it, but I do know it's possible.

  Possibly the biggest problem is that your data will all look alike when it enters the script (thanks, Excel) so your data type testing may be extensive.

  Good luck!