March 20, 2008 at 3:43 am
Hi,
I have a requirement to load tables in the database from files on a shared server (lets say 50 tables from 50 files). I do not want to hardocde the file path anywhere in the package since this would mean changing 50 packages everytime the path changes (say when moving to to a diff server).
In SQL Server 2000, I used a .ini file to pass the path and used a Dynamic properties task to set run time variables. That way, evertime the path changes, I only had to change 1 ini file and all packages picked up the new path from it. How do I do this in SQL Server 2005 ?
Appreciate any assistance / suggestion in this regard.
Thanks,
Das
March 20, 2008 at 4:20 am
Das
The SSIS equivalent of dynamic properties in DTS is Package Configurations. You'll probably need to do a bit of reading about it, since I don't think it works in exactly the same way. In Visual Studio, with your package in design mode, choose Package Configurations from SSIS menu.
John
March 20, 2008 at 4:29 am
Package Configurations which were developed for one package will not be visible for using in other packages.I want to make those package configurations dynamic.
March 20, 2008 at 4:41 am
Are you sure about that? Can you not create a different configuration filter for each package, and store them in a SQL Server table? Then, when your file path changes, all you need to do is write a query to update the rows in the table.
John
March 20, 2008 at 4:56 am
thanks.it is very good approach.
But can we create a file which is used to keep all the paths instead of storing as a table in database as this method could give problem when we change the database.
All the packages should look into that file so that we can change the paths in the file only once instead of running update method..
March 20, 2008 at 4:59 am
Yes, I think you can use an XML file, although I've never done it that way myself. I think you'll need to study this area quite carefully since it took me quite a while to understand exactly how it worked when I set it up with the table.
John
March 20, 2008 at 5:14 am
i tried with xml file.But the again the problem here is...
The xml file created for one package will not be available in other packages(I created configuration(xml) file by using ssis-->Package configurations option.while creating we have to chose the connection strings for sources and dest connections .so that would allow us to reuse the xml when package is using the same sourceconn and dest conn's and that too within the same package)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply