how to create xml config with different databases from same server

Question

how to create xml config with different databases from same server

mcfarlandparkway

SSCertifiable

Points: 7699
More actions
October 14, 2016 at 8:11 am

#313969

I am trying to create an xml config for my SSIS package ,Where I need to setup 10 connection strings in one xml config file.
I need to use this config file in SSIS package where it loops and checks in each database and load into final destination table that si different server.
Note - each database has same structure of table eg : Employee in all 10 databases ( In all these 10 databases I need to bring data from employee and load into staging table.
How to identify those ten conenctions using " name" property? do I need to give a different name for those 10 conencuitons in xml file?
Here is the xml file -
<?xml version="1.0"?>
<DTSConfiguration>
<DTSConfigurationHeading>
<DTSConfigurationFileInfo GeneratedBy="------" GeneratedFromPackageName="Emloyee" GeneratedFromPackageID="{CA8C-4B20-B32A}" GeneratedDate="10/20/2009 4:37:53 AM"/>
</DTSConfigurationHeading>
<Configuration ConfiguredType="Property" Path="\Package.Connections[---source 1 connection string---].Properties[ConnectionString]" ValueType="String">
<ConfiguredValue>Data Source=-----------------;Initial Catalog=InformationData;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;</ConfiguredValue>
</Configuration>
<Configuration ConfiguredType="Property" Path=" Destination connection string" ValueType="String">
<ConfiguredValue>T:\Jobs\XML\GTEToLocal\</ConfiguredValue>
</Configuration>
</DTSConfiguration>

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

If I've understood correctly, you want to load from one database and then from another and then from another. A config file is only for specifying properties of a package when you load it. If you want the properties to change during execution, use expressions and variables to set the values of those properties.

John

mcfarlandparkway SSCertifiable Points: 7699 More actions · Answer 2

Is there any example or link should I refer to?

I am confused here ..If you have any proper links where I can see and implement please forward it to me.

Phil Parkin SSC Guru Points: 246695 More actions · Answer 3

mcfarlandparkway (10/14/2016)
Is there any example or link should I refer to?
I am confused here ..If you have any proper links where I can see and implement please forward it to me.

What I think John is suggesting is

a) That your config file contains the 10 connection strings

b) That you configure your connection to the source database (in the package) to use a variable.

c) That you put a FOR loop in your package which sets the above variable to the values for connstr1, connstr2 etc. each time it loops.

d) Put your data flow inside the FOR loop.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.