SSIS

• tariqueiqbal 68459

  SSC Rookie

  Points: 27

  More actions

  December 29, 2011 at 5:07 am

  #262233

  Dear ALL,

  I want to create excel file dynamically in SSIS, I am Using Script task write this Code. Please give hints so that I solve the Problem

  string excelFilePath = Convert.ToString(Dts.Variables("User::CurrentDirectory").Value) + Convert.ToString(Dts.Variables("DestExcelFileName").Value);

  string TableName = null;

  string ExcelTable = null;

  TableName = Convert.ToString(Dts.Variables("User::ExcelTable").Value);

  if (TableName.Length > 30)

  {

  ExcelTable = Strings.Left(TableName, 31);

  //Excel sheet Name can only be 30 characters length.

  }

  else

  {

  ExcelTable = TableName;

  }

  //Retrieve the ExcelColumnNames from the Variable and build the String Here.

  string ColumnNames = Convert.ToString(Dts.Variables("User::ExcelColumns").Value);

  string strCn = " Insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0'," + "'Excel 8.0;Database=" + excelFilePath + "','SELECT " + ColumnNames + " FROM [" + ExcelTable + "$]') SELECT " + ColumnNames + " FROM [" + TableName + "]";

  //Uncomment the following message to check the query.

  //MsgBox(strCn)

  Dts.Variables("User::InsertScripter").Value = strCn;

  Thanks&Regard

  tarique

• CHANDRASHEKHAR SINGH

  SSC Enthusiast

  Points: 164

  More actions

  December 29, 2011 at 6:01 am

  #1426397

  Hi,

  In EXCEL Destination, you can create teh excel at run time-

  Sample code-

  CREATE TABLE `Excel Destination` (

  `Col1` LongText,

  `Col2` Long,

  )

  After this you can use File Sys Task to update you file name as desired.

  Regards,

  Chandrashekhar

• Raunak Jhawar

  SSCoach

  Points: 15701

  More actions

  December 29, 2011 at 6:33 am

  #1426409

  Step 1: Create an Excel connection manager

  Step 2: Use expression to configure your excel file path (C:\Test\ExcelFile_01012001.xls) where the date part is varying

  Step 3: Use a Execute SQL Task and specify the connection manaer as excel pointing to the newly created Excel connection manager

  Step 4: Create the excel table schema, as desired

  This creates an excel file dynamically

  Raunak J