December 18, 2009 at 8:51 am
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?
December 18, 2009 at 9:48 am
[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]
December 18, 2009 at 10:04 am
Excellent, that's what I need. One (that would be me) should explore those drop down menus better.
Thank you.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply