Using variables of Connection Manager, Connectionstring
Hello, I'll spare everyone my diatribe about MS and Booksonline... I considered myself pretty good at Server 2000 DTS. It certainly has it's shortcomings, but there area couple books on the marketthat helped a lot. I'm now trying to learn Integration Services, and not getting very far. There are two books out so far. one is poor, the other worthless. What I want to do seems very simple. That is to loop through a series of .txt files, and extract the information into a three table database. So far, I'm unable to figure out how to use a variable in the ConnectionString property of a connection manager. I see how to use variables in scripts (I should hope so) but using the same syntax in properties either errors at design time or run time. Do I have to do everything in VB just as in DTS? Or do the new objects really work in a usuable fashion? Color me frustrated. Any help will be appreciated. Thanks, Roger
June 12th, 2006 10:58pm

Hi, Select the properties of your connection, click on 'Expressions' and in the expression window select 'Connection String' in the left hand side and mention your variable name in the right hand side. And don't forget to pass value to that variable
Free Windows Admin Tool Kit Click here and download it now
June 13th, 2006 12:01am

Hi Thiru, Aha! You've moved me closer! But I'm getting an error that the variable is emtpywhen I try to close the dialog box. Obviously, the variable is empty because that depends on the for-each loop and the package is not running yet. I've set the DelayValidation property to true. What else I am doing wrong? Thanks for your help. Roger
June 14th, 2006 4:25pm

Roger, At design-time you need to put a validvalue into the variable otherwise you'll get the error that you are seeing. As you have observed, this will get overwritten at execution-time so it doesn't really matter what you put in there. Which dialog are you trying to close when this error occurs? -Jamie
Free Windows Admin Tool Kit Click here and download it now
June 14th, 2006 4:29pm

Which books have you looked at...? I'm reading the Wrox book and it has been fairly helpful so far.
June 14th, 2006 6:58pm

Hi Jamie, I still get the error even when I manually put a value into the variable. I get the error when I click OK in the build expression dialog box, the error message is below. Thanks for your response. Roger TITLE: Expression Builder------------------------------ Expression cannot be evaluated. For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.42&EvtSrc=Microsoft.DataTransformationServices.Controls.TaskUIFramework.TaskUIFrameworkSR&EvtID=FailToEvaluateExpression&LinkId=20476 ------------------------------ADDITIONAL INFORMATION: There was no expression to compute. An attempt was made to compute or get the string of an empty expression. (Microsoft.DataTransformationServices.Controls) ------------------------------BUTTONS: OK------------------------------
Free Windows Admin Tool Kit Click here and download it now
June 14th, 2006 9:52pm

Hi, The Wrox book is the one I described as "poor." The "worthless"one is the rational press book by Farmer. In fairness, it was apparently written using an early beta. It describes scripting fairly well, but little else. Roger
June 14th, 2006 9:56pm

Hi Again, Jamie, I have friends in MENSA. I belong in another group: Called DENSA! I just realized that my problem is that I needed to drag the expession down to the bottom of the dialog box to be stored. My prototype appears to run now! On to the next step... Thanks, Again! Roger
Free Windows Admin Tool Kit Click here and download it now
June 14th, 2006 10:10pm

I am now an official member of DENSA too, at least as far as SSIS is concerned. I just had the same problem! Thanks for posting so we DENSA folks can learn from each other. Lisa MorganLisa Z. Morgan Senior Program Manager, Dynamics Research Corporation
November 19th, 2010 3:29pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics