SSIS Connection Object and Expressions

SSIS Connection Object and Expressions

• Kamran Haider

  Mark,

  Steve is a colleague of mine. I believe that the expression is defined on the connection manager. I'll make sure he replies here.

  In the meantime, surely it should get re-evaluated on each loop iteration

  -Jamie

  
  
  

• MSDN Dev

  Are you calling AcquireConnection() on the connection manager in the code you wrote

  Here is what I have working write now:

  1. I have a foreach loop with foreach file enumerator (looping over files)
  2. I am using a loop variable (set in the Variable Mappings section of the foreach loop which will have the names of the files the loop is getting every iteration) as an expression on the connection string on the connection manager. This is done by expanding the expressions property on the connection manager, and setting an expression on the connection string property to be the loop variable (@[User::Variable] in my case.)
  3. Inside the loop, I have a script task, with code in it to AcquireConnection() from the connection manager (Dts.Connections.Item("MyFile").AcquireConnection(0)). After that I print out the connection string of the connection (msgbox(Dts.Connections.Item("MyFile").ConnectionString)
  4. I execute the package and I get the connection string evaluated like I expect it to; Having the file name that loop got in each iteration

  Here are possible problem causing areas:

  1. Setting of the loop, Loop variable, Foreach File Enumerator, Index value of the variable in the loop, ...
  2. The expression on the connection manager's connection string property can be invalid or may yield a wrong value,
  3. Missing the call to AcquireConnection().

  Mohammed.

  
  

• zq3

  I have the same problem and I am not using acquire connection. Thus the problem.

  However I don't see why you have a connectionString property that can be accessed before the acquireConnection is called if to get the correct value you need to call AcquireConnection.

  can someone elaborate on what the acquire connection actually does on the flat file connection manager,as the reason I am not using the flat file source is that it tries to get an exclusive lock, which is no good to me as the file I want to read is locked for writing already.

  Thanks

  
  
  

• guoguowenzi

  Expressions get evaluated at certain points, which differ from one object to the other. For connection managers, they get evaluated at loading and saving to xml, and at AcquireConnection() calls. I tried something similar to what you are describing, by having a foreach loop with a script task which calls acquireconnection on a connection manager that has an expression set on its connection string. The expression is set to the variable of the loop that gets the name of the file looped over in the foreach loop, and I managed to get the correct file name I wanted.

  
  

• Daniel P.

  My appologies for the delay in responding...

  The answer depends on when the expression on the connection manager is evaluated. I'm not sure (a colleague is following up) but it is possible that it is only evaluated once, which would explain what you are seeing.

  You may need to put the property on your custom source. The data flow task expressions should be evaluated each time through the loop. I realize that defeats the purpose of having use the file connection manager however...

  I will respond later with a more definitive answer.

  Mark

  
  
  

• Teodora

  Hi Mohammed

  Yes I am calling AcquireConnection() method on the connection manager within my method. I have managed to get the what I need to work now by changing the code, that I found from somewhere, from this :

  if(ComponentMetaData.RuntimeConnectionCollection["SourceFileConnection"].ConnectionManager != null){

  cm = DtsConvert.ToConnectionManager(ComponentMetaData.RuntimeConnectionCollection["SourceFileConnection"].ConnectionManager);

  if(cm.CreationName == "FILE")

  {

  fileUsage = (Microsoft.SqlServer.Dts.Runtime.DTSFileConnectionUsageType)cm.Properties["FileUsageType"].GetValue(cm);

  if(fileUsage == Microsoft.SqlServer.Dts.Runtime.DTSFileConnectionUsageType.FileExists)

  {

  cmFile = cm.InnerObject as ConnectionManagerFile;

  if (cmFile != null)

  {

  connectionString = cmFile.AcquireConnection(transaction) as string;

  if (connectionString == null || connectionString.Length == 0)

  {

  throw new Exception("No file name specfiy");

  }

  }

  }

  else throw new Exception("Incorrect file connection usage type, should be set to exiting file type");

  }

  else throw new Exception("Connection is not a file connection");

  }

  else throw new Exception("Connection is not as assign");

  To this:

  if (ComponentMetaData.RuntimeConnectionCollection["SourceFileConnection"].ConnectionManager != null)

  {

  cm = DtsConvert.ToConnectionManager(ComponentMetaData.RuntimeConnectionCollection["SourceFileConnection"].ConnectionManager);

  if (cm.CreationName == "FILE")

  {

  fileUsage = (Microsoft.SqlServer.Dts.Runtime.DTSFileConnectionUsageType)cm.Properties["FileUsageType"].GetValue(cm);

  if (fileUsage == Microsoft.SqlServer.Dts.Runtime.DTSFileConnectionUsageType.FileExists)

  {

  connectionString = ComponentMetaData.RuntimeConnectionCollection["SourceFileConnection"].ConnectionManager.AcquireConnection(transaction).ToString();

  if (connectionString == null || connectionString.Length == 0)

  {

  throw new Exception("No file name specfiy");

  }

  }

  else throw new Exception("Incorrect file connection usage type, should be set to exiting file type");

  }

  else throw new Exception("Connection is not a file connection");

  }

  else throw new Exception("Connection is not as assign");

  And now my expression on the connection manger is being evaluated for each iteration of the loop.

  Thanks

  Steve

  
  

• pwtsky

  The expression is defined on the File Connection that the dataflow uses. The expression is just the variable, that the for-each loop updates with a full path of the file.
  

• dspace

  Where is the expression defined On the connection manager within the dataflow If so, it will only be evaluated once before the dataflow is executed.
  
  

• stevenpr_MS

  Thanks Mohammed.  Steve, do you see similar behaviour when calling AquireConnection(), or is it still not working for you

  Thanks
  Mark

  
  
  

• VN

  Hi Mark

  It's still not working for me. Also I don't know if it would make a difference but I did write the source adapter as a .Net assembly

  Steve

  
  
• SSIS Connection Object and Expressions