using ssis to create a table in ssms with todays date in it

• Fee01

  Old Hand

  Points: 374

  More actions

  February 24, 2009 at 8:27 am

  #200216

  hi, i am fairly new to this. i am trying to copy a lot of data from one table in my database to another table which i want to name dynamically so it has the name of todays date in it..eg

  lifecycle02092009

  how do i do this please?? script task? i'm really stuck!

• Andrew Hinchcliffe

  SSC Veteran

  Points: 245

  More actions

  March 5, 2009 at 4:14 am

  #953902

  Create a variable called DateAsString as String

  Set the Properties of the Variable EvaluateAsExpression to true and ReadOnly as True. Set the Expression to:

  RIGHT("0" + (DT_STR,4,1252)DATEPART( "dd" , @[System::StartTime]) , 2) +

  RIGHT("0" + (DT_STR,4,1252)DATEPART( "mm" , @[System::StartTime]) , 2) +

  (DT_STR,4,1252)DATEPART( "yyyy" , @[System::StartTime] )

  Then create another variable as string called DestTable.

  Set EvaluateAsExpression to true and ReadOnly as True. Set Expression to:

  "lifecycle" + @[User:: DateAsString]

  Create a DataFlow. Create a OLE DB Source and a OLE DB Destination. Connect them up and set the source details.

  On the destination in the Data access mode select:

  Table name or view name variable - fast load

  or

  Table name or view name variable

  In the Variable name select DestTable.

  That way the destination table will come from the variable.

  Hope this helps.