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 |
|
vasu4us
Posting Yak Master
102 Posts |
 Posted - 2007-05-29 : 09:56:34
|
| I have created a connection in connection manager.using expressions i have declared a connection string"Data Source=" + @[User::servername] + ";Initial Catalog=" + @[User::catalog] + ";Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;"when i click 'evaluate expression ' it just displaysData Source=;Initial Catalog=;Provider=SQLNCLI.1;Integrated Security=SSPI;any anyon etell me if iam doing any mistake |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-05-29 : 10:10:33
|
| It will use whatever is in the variables at that time. Your variables are blank.Could be that you haven't populated them or that you have populated a variable in a different scope.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
vasu4us
Posting Yak Master
102 Posts |
Posted - 2007-05-29 : 10:28:18
|
| Thanks NR for quick responce. I have SQL task and ForEachLoop.In sql task iam taking a table which has server name, databse name and a query as string.In the connection string that i mentioned above iam using the variables which mapp to the table valuefor server and DB names.is it the right way to do it. how do i see the variables values in the 'evaluate expression' windowiam working on this for the last 4 days still not thorugh, things like quotes, + were stoping me and i am not sure what more...my only hope is this place cos no book tell it all at one placeThanks all |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-05-29 : 11:06:33
|
| You are populating the variables in a loop? You won't be able to evaluate the expression outside the loop.I've written an article on using for each loops but it's not exactly what you are doing.http://www.simple-talk.com/sql/sql-server-2005/executing-ssis-packages-/It's worthwhile writing the vlues of the variables to an audit table in the loop so that you know what's happening.Actually what are your variables for? Are you setting a connection to use in the loop or setting the variables in the loop?==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
vasu4us
Posting Yak Master
102 Posts |
Posted - 2007-05-29 : 11:52:21
|
| I have to run querys on multiple servers (different DB) 1.some are AS400(located in different states around 30 servers) each serer gives me the sales and order count for that state. 2.some are sql servers for all states for which if i run one query ill get the sales and order count for all states3.some are html files which are again stored on sql server for which again i run query and get sales and order count for all statesthe purpose of doing this is to check the deviation between any of the 2 sources.the plan is to store all the 30 AS400 + 2 SQL server and database details along with the querys in a table.innitiate a for loop and run the querys one after the other, the connection strings also should be changing dynamically.OK thats what i wanted what i did is created a sql task which will store all the table details(srvr,DB,query) into a object variablestart a for loop and the enumerator for the loop of type ADO with object variable as enumerator.Mapped all the columns in the object variable to variables with scope of the package with index 0,1,2,3in connection manager created a connection for sql server in which i want to create a exprection for connection string property(iam not sure if it works with different databases) and the variables of connection string would be from the mapped variables from the for loop.i know its confusing even iam, with to little i know i am planing to do toomuch...I can explain in detail if it s not clear... Thanks |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-05-29 : 12:06:57
|
| What you are doing should work for sql server (or a single database engine).I'm not sure whether you would have probelms connecting to another database engine though - anyway if you get it working for sql server you can try it - you might need to have another task in the loop to cope with AS400.What you have as the expression looks ok too.As I said - try writing your variable values to a trace table at the start of the loop (just use an experession in an sql task to do it).==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
dynamic connection and query