Dynamic Properties task Config file

I'm using a INI file with my Dynamic Properties task for the data sources for my connections in the package. Say the package goes from Q/A to production and the servers change. Now assuming the ini file is changed with the correct (new) server names etc. Where do we specify the location of the INI file?

This is really frustrating. Please helpHowdy,

Assuming you have a dynamic package properties task, open steps then DTSStep_DTSDynamic..... and double click on Property Name section called "Description". You can choose the Source to be "INI File" and the location is determined by the "...." button on the right hand side.

Cheers

SG|||Originally posted by sqlguy7777
Howdy,

Assuming you have a dynamic package properties task, open steps then DTSStep_DTSDynamic..... and double click on Property Name section called "Description". You can choose the Source to be "INI File" and the location is determined by the "...." button on the right hand side.

Cheers

SG

Yeah i figured that but once i move it to a different server i dont want to go through all the packages and changes the config file location. so i was wondering if it could be done through another way where promoting the package to another server w/o actually designing the packages all over again.

Thanks.|||Howdy

Well you can read all the config values from a table instaed of an ini file. So, you could just copy a table across into a database and use that as a config "module" that can be moved around.

Beyond that, I'd suggest its not straight forward.

Cheers,

SG|||I have tired that as well but once you move the DTS package to a different server the connections in the package still point to the original server. Also, for the DT properties in the package, how to specify the source connection to use for Queries in the package?

Thanks,

V|||Howdy

the problem is that you need to define local logons for the server, so each time you copy the package to a new server, you will need to manually alter the local server logon. No way around this I know of, unless all your servers, userIDs & passwords are the same.

A DT needs 2 connections : ( asuming you are copying data around on one server ) :

1 the source connection - server name & source database
2 the destination conn. - server name & destination database

To create a DT - click the soucre, then hold the control key & clikc the destination. Then while the two conenctions highlighted, do right click & choose Transform Data Task.

Cheers

SG

Dynamic Excel Sources

Please help....

I have different files that are sent from our vendors. Some are TXT and some are XLS. Some will have the same structure. I plan on grouping these together as best as I can.

My main problem is that I would like to go from one source that matches a group of files to a single SQL table. I'm still learning about SSIS and its capabilities. If I can get pointed in the right direction or have an example to work from, that would be great.

I've tried googl'ing to find some step by step, examples, and hints to do this right, but so far I'm at a bit of an impasse.

Thanks,
Dave

You could use a Foreach Enumerator to loop through each file with the same structure and load it to the destination table, by placing the data flow within the loop container.

-Doug

|||

How would I go about getting the file name to change appropriately with each file. I have the foreach creating a variable called FileName. I have a script task picking off the information in the filename (format: <vendor #>_<start date>_<stop date>.xls). I then put vendor#, start date, and stop date into package variables. I guess my main concern is not knowing if I'm using expressions right. I mapped the ExcelFilePath and ServerName to user::FileName. With the file changing, do I have to set anything else to keep errors from occurring?

Thanks,

Dave

|||

Dave,

You shouldn't need to worry about the ServerName property for Excel. As for ExcelFilePath, I would want to make sure that the ConnectionString property was updated immediately and automatically when I provide a new value (outside the UI) for ExcelFilePath. Otherwise you could use an expression on ConnectionString, instead of simply mapping ExcelFilePath to User::FileName.

Updating the ConnectionString for Excel by using an expression has been discussed here previously. The expression shown in http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=103273&SiteID=1 should work for you when you insert your FileName variable at the proper place.

-Doug

Dynamic Connection String

I've seen some threads here about Dynamic Data Sources.

I am using the ReportViewer Control for WebForms.

I simply want to say the following:

reportviewer fv = new Microsoft.Reporting.WebForms.ReportViewer();

rv.ServerReport.SetConnectionString(......);

I want to avoid using parameters to do this if I can. I've read that stored procedures don't work when you use parameters.

Is this possible?

Thanks

It sounds like you are using the ReportViewer control in LocalMode. In that case, your application has to directly provide the data as a dataset to the ReportViewer control. There is no connection string, because the ReportViewer control will never execute any queries in LocalMode. More information is available here: http://www.gotreportviewer.com/definedata/index.html and here: http://www.gotreportviewer.com/objectdatasources/index.html

If you use the ReportViewer control in ServerMode, it will just point to a report on a report server. That report is executed on the report server and could use an expression-based connection string, as discussed here: http://msdn2.microsoft.com/en-us/library/ms156450.aspx (scroll to the "Data Source expressions" section).

-- Robert

Dynamic Connection

I am trying to connect to multiple data sources of same type say (sql) pn different servers and run the same data flow task on all of them.

what i need to acomplish is make the connection dynamic, i tryed the sample flat file connection and i understand that i have to

1, declare a variable 'connectionstr'

2, in the connection manager properties > expression property i have to select connection string and exeression as @.[user::connectionstr']

then i am out of thoughts i know i must use FOR loop but how

even FOR loop has expression, enumerator.....

how would i point the connection string variable to a list of values(srvrname,databasename, tblname) stored in a table.

Thanks in advance...

Take a look at this example:

http://agilebi.com/cs/blogs/jwelch/archive/2007/03/21/using-for-each-to-iterate-a-resultset.aspx

Here's a search that might have some other useful samples - http://search.live.com/results.aspx?q=foreach&q1=macro:jamiet.ssis&first=1