Dynamic SSIS Packages, Part 3.

In this post let’s load the export server and export dynamic parameter tables, along with the file delimiter mapping.  Attached are three files – change the parameters (server names, file export locations) for your particular environment, paying attention to spaces, backslashes, etc. so that the package will work properly.  Also included are the two following queries, the first for the server level parameters, and second for the dynamic file parameters.  These queries will be placed in two separate ExecuteSQL tasks that will feed the script component. Yours should also look similar to these as well after you have inserted your records. Also, if you do not have the AdventureWorksDW2008R2 database loaded, go ahead and download this if you want to follow along with me. This is what we’ll source our example data from.

Make sure that you’ve read parts 1 & 2 of this series so your down with these blogs. In the next posting, I’ll attach the package for you to wire up to.

Lee

---------------------

Queries for SSIS ExecuteSQLTasks – Server parameter, and then file “dynamic” parameters.

SELECT ISExportSourceDBname as SourceDBName

        ,ISExportServername as ServerName 

FROM tbl_ISExportServerParms S

SELECT 

     ISExportSourceTableName AS TSK_DWH_TABLE

    ,ISExportSourceOutfileName AS PKG_OUTFILE_NAME

    ,ISExportSourceOutfilePath AS PKG_OUTFILE_PATH

    ,ISExportSourceLogfileName AS PKG_LOGFILE_NAME

    ,ISExportSourceLogfilePath AS PKG_LOGFILE_PATH

    ,ISExportSourceExtractSELECT  + ' ' + ISExportSourceTableName AS TSK_DWH_SQL_SELECT

    ,ISExportDelimiter

FROM tbl_ISExportDynamicParms D

INNER JOIN dbo.tbl_ISExportServerParms S

    ON D.ISExportConfigurationID = S.ISExportConfigurationID

WHERE D.ISExportExtractFlag & S.ISExportExtractFlag =1

Result for SSIS ExecuteSQLTasks - yours need to look similar to these.

Files-insert data into Export tables