Implementing SSIS Package Using File System.

Depending on how you or your organizations want to implement SSIS packagesduring Development, Test or Production environment, Let's see how we can do file system implementation.  You can save your ssis package in either SQL Server Store or in a folder. I will be specially looking at File System implementation.

To do so, we will have to create following files first:
1. SSIS_Cmd.cmd
2. DB_cfg.dtsConfig
3. PackageName_ExeCfg.txt


So let's look at each of them:


SSIS_Cmd.cmd

This cmd file list where our sql server is installed, where our integration engine is running, our package directory,  where we are saving our log file. Our database config, and how to handle error. You have to create just one file for a each server.

So let's look at the file itself:
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
set BIN=C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn
set ROOTDIR=C:\SSIS

SetLocal EnableDelayedExpansion
set content=
for /F "delims=" %%i in (%1) do set %%i

set SSISPACKAGESDIR=%ROOTDIR%\%PACKAGEDIRNAME%\jobs\SSIS_Packages
set LOGDIR=%SSISPACKAGESDIR%\logs

"%BIN%\dtexec.exe" /FILE "%SSISPACKAGESDIR%\%SSISPACKAGE%" /CONFIGFILE "%SSISPACKAGESDIR%\%CONFIG_CUSTOM%" /CONFIGFILE "%ROOTDIR%\%CONFIG_DB%"  /CHECKPOINTING OFF /REPORTING EWCDI 1>"%LOGDIR%\%LOGFILE%"

IF %errorlevel% NEQ 0 GOTO DTSRUN_ERR_EXIT
GOTO SUCCESS_EXIT
:DTSRUN_ERR_EXIT
echo.
echo ABENDED: ERROR running SSIS package
exit /B %errorlevel%

GOTO EXIT
:SUCCESS_EXIT
echo.
echo COMPLETED SUCCESSFULLY

GOTO EXIT
:EXIT
echo.
echo FINISHED: %date% %time%
echo on

EndLocal

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Next is DB_ConFig.cmd file

DB_cfg.dtsConfig
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
<?xml version="1.0" encoding="utf-8"?>
<DTSConfiguration>
  <DTSConfigurationHeading>
    <DTSConfigurationFileInfo GeneratedBy="AMERICAS\straleyd" GeneratedFromPackageName="PackageName" GeneratedFromPackageID="{7DD1CDBA-6AD6-4D5D-84A0-4258BA92E894}" GeneratedDate="5/30/2014 10:01:10 AM" />
  </DTSConfigurationHeading>
  <Configuration ConfiguredType="Property" Path="\Package.Connections[Connection_Manager_Name].Properties[InitialCatalog]" ValueType="String">
    <ConfiguredValue>DatabaseName</ConfiguredValue>
  </Configuration>
  <Configuration ConfiguredType="Property" Path="\Package.Connections[Connection_Manager_Name].Properties[Password]" ValueType="String">
    <ConfiguredValue>Password!!!!!</ConfiguredValue>
  </Configuration>
  <Configuration ConfiguredType="Property" Path="\Package.Connections[Connection_Manager_Name].Properties[ServerName]" ValueType="String">
    <ConfiguredValue>ServerName</ConfiguredValue>
  </Configuration>
  <Configuration ConfiguredType="Property" Path="\Package.Connections[Connection_Manager_Name].Properties[UserName]" ValueType="String">
    <ConfiguredValue>USerLoginName!!!!</ConfiguredValue>
  </Configuration>
</DTSConfiguration>

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
PackageName_ExeCfg.txt

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SSISPACKAGE= SSIS_Package_Name.dtsx
PACKAGEDIRNAME=DevBox !!!!!!!!!!!Directory where you saving package file DevBox
CONFIG_CUSTOM=Config file of the package which you want to usecfg.dtsConfig
CONFIG_DB=DB_dev_Cfg.dtsConfig   !!! DB Config file
LOGFILE=Name of Log File_log.txt

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

To Implement.

Create all the necessary folders. For this I will take Development as folder Name

Inside Development Folder, I will create following folders:

archive -- if I have to archive any file from any of the package
inbound - folder to hold any files
Jobs- where I will save all my ssis package related files, and
outbound, if I have to write to any files.

Inside job folder, I will create two more folder

SSIS_EXEC and SSIS_Package
Inside SSIS_Package, I will save all the executable's related


For example say I create a dtsx file.

Example.dtsx - Package file
Example.dtsConfig --ConFig file
Example_Exe.txt - file with all the information.


Now go to sql server agent and create a job using  Operating System (CMDEXEC) and List the path  where you all the files are:

C:\SSIS\SSIS_Cmd.cmd   C:\SSIS\Develpoment\jobs\ssis_packages\Example_ExeCfg.txt

and execute it.