Passing variable in to SQL query in loop

• jonathan.crockett

  SSC Enthusiast

  Points: 111

  More actions

  April 30, 2012 at 7:32 am

  #255094

  I have hunted around and can't find any example of what I'm looking to do.

  I have a SQL query which I run on a monthly basis for the previous months data. These come from monthly tables named with the format

  TABLENAME_YYYYMM so last months was TABLENAME_201203

  I currently run the query declaring @mth as convert(char(6),dateadd(mm,-1,getdate()),112) then passing this in so I have TABLENAME_@mth

  I currently have this query saved as a 2000 DTS package which outputs the result to a flat file, I run this with DTSrun. This package was created when I only had access to import/export wizard.

  I now have BIDS and want to create a package to loop through the last 24 months data and export them to individual flat files. Is this possible? I can create a package that loops, but not one that I can pass a variable into that alters the value of @mth.

  Any help would be greatky appreciated

• Elliott Whitlow

  SSC Guru

  Points: 102402

  More actions

  April 30, 2012 at 10:11 am

  #1481341

  Yes you can. I would probably query the list of tables in an EXEC SQL task and use the full resultset option and write the output to an object variable. The use the object variable as the source for a ForEach Loop, map the fields from the query you generated in the EXEC SQL task. I would then add a Data-Flow task inside the FEL. Add an OLEDB source to the DFT. Chose your connection manager, for data access mode chose: Table name or view name variable, chose the variable for the table name. Be sure to set a valid table name in the variable at the start. BIDS will not evaluate the object variable and set the table name variable to a name until run-time, you need to provide a design time value. This gets you to the point you can read from the table list. You said you wanted to write to CSV files. All named something different? That is a bit more complex but doable.

  CEWII

• Orlando Colamatteo

  SSC Guru

  Points: 182293

  More actions

  April 30, 2012 at 12:28 pm

  #1481407

  Here is a video demoing the For Loop in SSIS. Using it would be a slightly different approach than the path Elliott provided, although I am confident that what Elliott provided will get the job done too.

  Since you have a fixed # of months you care about for any given run a For Loop from 1 - 24 with an expression to derive each table name could be all you need. Similar between the two approaches, inside the For Loop you would have a Data Flow Task to get the data out of your table and into a flat file.

  SSIS For Loop Container

  PS I won't comment too much on the table names since it is not central to what you were asking, but if all have the same schema having a date stamp in a table name is not a recommended practice since it forces us into having to dynamically generate queries to access data. An alternative (possibly the most preferred) way to handle this case have been to have all the data in one table with a "date stamp (YYYYMM) column" so you can simply supply the YYYYMM value you want to retrieve in the WHERE-clause.

  There are no special teachers of virtue, because virtue is taught by the whole community.
  --Plato

• Elliott Whitlow

  SSC Guru

  Points: 102402

  More actions

  May 2, 2012 at 8:44 am

  #1482439

  Sorry, I was otherwise occupied.. But opc.three's approach is fine as well and I agree with the points about the table naming.

  CEWII