SQL Kit

So Now that I have a package that works and loads data from a CSV file to a Untyped Table and then a Typed , I think it is the time when we try to find out what is the best (or only) way to use Configuration driven properties.

SSIS Gives these way to set up config :-

Go to SSIS à Package ConfigurationsàAdd you will see these options

• I don’t like registry entry, and neither will your System / Database Admin. So I am leaving it there.
• Parent Package variable, well don’t want that.
• SQL Server Option wont work  because only database I have is Sybase but there is a Bug here which I would like to Point out

If you say SQL Server , and select “Specify Configuration Settings Directly” even you Sybase Connection will show up

But you wont go too far from here because after you create a new Config table to hold the config values, you wont be able to add the configuration filter. It is a bit dumb because Sybase connection should not surface as SQL Connection if it is not a SQL Connection. But anyway we are left with Environment variable and XML Configuration.

So I would Say

1. Use Env variable to store the path to XML connection
2. Use XML config to store rest of the properties

So Just select the XML Configuration Option

3. Select the properties that you want to drive from config. I have selected the connection string here.

Next àNext àFinish

Don’t forget to enable package Configuration

1. Now Open the Config file(Best way to do which I know is , add to your SSIS Project and Reformat the document using Ctrl+k , Ctrl+D. it will look like

<Configuration ConfiguredType="Property" Path="\Package.Connections[staging].Properties[ConnectionString]" ValueType="String">
              <ConfiguredValue>Data Source=ash-pc:5400;User ID=ash;Password=roy;Initial Catalog=etl;Provider=ASEOLEDB.1;</ConfiguredValue>
       </Configuration>
       <Configuration ConfiguredType="Property" Path="\Package.Connections[staging].Properties[Description]" ValueType="String">
              <ConfiguredValue></ConfiguredValue>
       </Configuration>
       <Configuration ConfiguredType="Property" Path="\Package.Connections[staging].Properties[InitialCatalog]" ValueType="String">
              <ConfiguredValue>etl </ConfiguredValue>
       </Configuration>
       <Configuration ConfiguredType="Property" Path="\Package.Connections[staging].Properties[Name]" ValueType="String">
              <ConfiguredValue>staging</ConfiguredValue>
       </Configuration>
       <Configuration ConfiguredType="Property" Path="\Package.Connections[staging].Properties[Password]" ValueType="String">
              <ConfiguredValue></ConfiguredValue>
       </Configuration>
       <Configuration ConfiguredType="Property" Path="\Package.Connections[staging].Properties[ProtectionLevel]" ValueType="Int32">
              <ConfiguredValue>1</ConfiguredValue>
       </Configuration>
       <Configuration ConfiguredType="Property" Path="\Package.Connections[staging].Properties[RetainSameConnection]" ValueType="Boolean">
              <ConfiguredValue>0</ConfiguredValue>
       </Configuration>
       <Configuration ConfiguredType="Property" Path="\Package.Connections[staging].Properties[ServerName]" ValueType="String">
              <ConfiguredValue> ash-pc:5400</ConfiguredValue>
       </Configuration>
       <Configuration ConfiguredType="Property" Path="\Package.Connections[staging].Properties[UserName]" ValueType="String">
              <ConfiguredValue>ash</ConfiguredValue>
       </Configuration>
       <Configuration ConfiguredType="Property" Path="\Package.Connections[staging].Properties[Password]" ValueType="String">
              <ConfiguredValue>roy</ConfiguredValue>
   </Configuration>

So If you are using SSIS as ETL on a Datawarehouse that is not Microsoft SQL Server then you can use this as a way to use config driven SSIS .