Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
BhaskarDasari
Starting Member
19 Posts |
 Posted - 2008-03-20 : 03:47:44
|
| 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,Bhaskar |
|
|
igorblackbelt
Constraint Violating Yak Guru
407 Posts |
Posted - 2008-03-20 : 09:01:23
|
| Bhaskar,This is doable, 2 things you have to do:1) Create a variable that can be accessed by your data transfer component, this variable will store the path string.2) Configure the ConnectionString of the file(s) connection managers using SSIS expressions, you'll use the variable that's being passed to the package as part of the string.I would create an expression for you, but I have some code to review, but you should be able to figure that out. |
 |
|
|
BhaskarDasari
Starting Member
19 Posts |
Posted - 2008-03-24 : 01:58:01
|
| okBut Will that variable be visible for other packages?? |
|
|
|
BhaskarDasari
Starting Member
19 Posts |
Posted - 2008-03-24 : 02:06:07
|
| ya i have created for xml file by using "package configurations" option in ssis menu.Through that xml which will be having connection details for database and path for flatfile.But this xml can be created for a package and will not be visible for other packages in aproject for reuse.(we can reuse in the same package that too the connection managers are accessing the same database and same path for flat files)In this case i have created 3 variables 1)folderpath 2)source filr path 3)destination file pathThe fact is we have two appraoches creating variables (or) creating xml .If you want to change the paths you will have to modify either in variables or in xmlBut my req is that variable or xml should be used for all the packages.(Note:we can create variables with scope package but not for project) |
|
|
|
|
|
|
Passing parameters dynamically