Passing a dynamic variable to a SSIS package in order to INSERT in different tables with same structure

• JJ Duru

  Right there with Babe

  Points: 715

  More actions

  December 18, 2009 at 8:51 am

  #207563

  Howdy,

  Here's the problem: I have to archive data from a DB production server to a reporting machine. The existing old data on prod is structured under TableName_OLD_20xx_xx where 20xx is year and following xx are the week number. These are coming from a partitioning management process.

  On the reporting machine there will be separate tables instead of a single partitioned table, bearing the same name for the same period as in production.

  I found it easy (so far) to design a single SSIS package per table that will accept as input a text variable specifying which table I'm going to select from. The SSIS package has a text variable like "SELECT * FROM RawCommunicationResponse_OLD_2009_35" which is passed to a OLEDB Reader Source in which I'm using the Data Access Mode "SQL command from variable name". This is the SSIS package wrapper that determines which table will be tackled:

  USE DBName

  GO

  DECLARE @cmd varchar(1000)

  DECLARE @SsisPath NVARCHAR(1000)

  DECLARE @SsisFile NVARCHAR(1000)

  DECLARE @SsisLocation NVARCHAR(1000)

  DECLARE @SsisVariable NVARCHAR(1000)

  SET @SsisPath= 'C:\SSIS-packages-repository\DBName\'

  SET @SsisFile= 'RawCommunicationResponse.dtsx'

  SET @SsisLocation= @SsisPath + @SsisFile

  SET @SsisVariable= 'SELECT * FROM RawCommunicationResponse_OLD_2009_35'

  SET @cmd = 'C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTExec.exe /F "' + @SsisLocation + '"' + ' /SET \Package.Variables[User::strReaderSQL].Properties[Value];"' + @SsisVariable + '"'

  SELECT @cmd

  --EXEC master..xp_cmdshell @cmd

  What I find hard to implement is to have some kind of variable that will allow me to INSERT in the table I want. It needs to be a variable and not a hardcoded table name inside the package as I intend to reuse it in scheduled jobs for different weeks of the year.

  Any idea how to do this?

• sabyasm

  SSCarpal Tunnel

  Points: 4324

  More actions

  December 18, 2009 at 9:48 am

  #1094134

  [font="Comic Sans MS"]

  Hi Daniel,

  If I understood you correctly - the tablename from source (DB2) and tablename at destination would be same or nearly same. You can use a variable to store the tablename and use the same to insert on the destination table if you are using the same package. Use OLEDB destination -- set the data access mode to be 'table or view name variable' -- use the dropdown to point to that variable and you are good to go.

  In case you want to call another package with parameter = destination tablename - it's nearly the same - but you would need to use parent-child package varible passing. For this - you may refer to:

  http://www.sqlis.com/post/Using-Parent-Package-Variables-in-Package-Configurations.aspx

  [/font]

  [font="Comic Sans MS"]--
  Sabya[/font]

• JJ Duru

  Right there with Babe

  Points: 715

  More actions

  December 18, 2009 at 10:04 am

  #1094151

  Excellent, that's what I need. One (that would be me) should explore those drop down menus better.

  Thank you.