I understand and the expression works great! What task should I try with this expression? I'm thinking the Script Task may be my best option because we are talking about file paths and not input within the file itself. And what clause/command statements would I try with the SQL Task to evaluate User::FilePathName against my supplied table? Sorry, it's been sometime since I've done anything fancy with SQL commands. SUBSTRING( @[User::FileName] , FINDSTRING( @[User::FileName] , "\\", 5) + 1, FINDSTRING( @[User::FileName] , "\\", 6) - FINDSTRING( @[User::FileName] , "\\", 5 ) - 1) Evan Johnson

I would use this as an expression on a variable - that will give you the company name in the variable. Then, you can use a simple Execute SQL Task that just compares the value of the variable against the table: SELECT CompanyName FROM MyTable WHERE CompanyName = ? and map the variable with the company name to the parameter in the Execute SQL. If you just want a check of whether any records exist for that company, use SELECT COUNT(1) FROM MyTable WHERE CompanyName = ?John Welch | www.varigence.com | www.agilebi.com | ssisUnit.codeplex.com

I'm thinking it should be set like: SELECT CompanyName FROM MyTable WHERE CompanyName = User::FilePathName because after setting the expressions tab in Execute SQL for the User::FilePathName it then will only return the Company Name. I assume from what you've said that CompanyName is the parameter in Execute SQL. @johnwelch I totally agree with you on using the expression on the variable User::FilePathName to get the Company Name. I'm under the impression you're avoiding a script task all together, such that everything can be done within the Execute SQL Task. I'm assuming the Substring/Findstring expression would be placed under the Expressions tab in the Execute SQL Task. Is that correct? Then under the General tab I'd set SQLSourceType to DirectInput and write out my SQLStatement in the next dialog box. But how would my SQL statement know I want the expression written (within the same task), which finds Company Name, within the variable's value set to variable CompanyName? Do I just call on the variable User::FilePathName instead of CompanyName? Or do I need to set CompanyName to variable User::FilePathName? How would that be done? thx! Evan Johnson

Close, but not quite. You are going to create a 2nd variable, called CompanyName, and set the expression on it. To do this, select the new variable, open the properties window (F4, if it's not already visible) and change the EvaluateAsExpression property to true. Then add the expression to the Expression property. Now the value of this variable will evaluate to the result of this expression. Now in the Execute SQL, you don't have to use an expression - just map the variable to your parameter on the Parameters page in the task. I'll post a sample package in a few minutes. John Welch | www.varigence.com | www.agilebi.com | ssisUnit.codeplex.com

I implemented most of this. Thanks! Under the Parameter Mapping tab, would CompanyName be the Parameter Name? I assume -1 for Parameter Size is ok because it's the default value. Maybe -1 means Parameter Size unknown or expands/contracts as needed?Evan Johnson

Parameter name would actually be 0 - OLE DB likes paramters named for their ordinal position (zero-based). -1 for the Parameter Size should be fine. Sorry, got sidetracked onthe sample package, but it sounds like you are OK without it. John Welch | www.varigence.com | www.agilebi.com | ssisUnit.codeplex.com

string path = @\\ddrftp\a$\ddr\JNJ\DDR\Parts\JNJ_parts.xml; string [] result = path.Split('\\'); MessageBox.Show(result[5].ToString()); Lee Everest http://www.texastoo.com

The Scenario: I have 20 different companies which each import 50+ xml files to me daily. I access the companies’ files thru a networked drive, say: Static Static Dynamic Static Static \\ddrftp\a$\ddr\JNJ\DDR\Parts\JNJ_parts.xml \\ddrftp\a$\ddr\Lockheed\DDR\Labor\Lockheed_labor.xml \\ddrftp\a$\ddr\IBM\DDR\Service\IBM_service.xml Here the companies are JNJ, Lockheed, and IBM. As you can see I have all company folders FTP’ed in one directory on my computer. These folders are what I consider to be Company Names. The Problem: Companies might accidently or deliberately send bad data within the xml files. I use the xml file’s data element Company_ID as a primary key in my table. What I need to do is validate Company_ID by cross-referencing the Company Name (found in the file path) to a table (supplied to us from each company) which has the valid Company IDs. Similar to, Ex. A table supplied to us by the company we service Company Name Company ID JNJ 495 JNJ 501 JNJ 502 My Objective I want to use an expression to find the Company Name apart of the file path, so that I can quickly or dynamically reference valid Company IDs for validation. If I had a task to validate and dump invalid Company IDs into an Excel file I’d be knocking out 2 birds with 1 stone.Evan Johnson

I take it your commands work the same as, SUBSTRING( @[User::FileName] , FINDSTRING( @[User::FileName] , "\\", 5) + 1, FINDSTRING( @[User::FileName] , "\\", 6) - FINDSTRING( @[User::FileName] , "\\", 5 ) - 1) but is a little more clean cut. Thanks, but I already have this part working Right now I’m working with Execute SQL Task - Validate CompanyIDs Exist in CompanyIDFactTable. Inside the Execute SQL Task I first connect to my database where I previously created the table CompanyIDFactTable over in SQL Server 2008. The columns are varchar CompanyName and int CompanyID. That table looks like, CompanyName CompanyID jnj 486 jnj 487 jnj 488 jnj 495 jnj 493 jnj 496 Then I use, SQLSourceType: File connection FileConnection: CompanyIDFactTable.sql That file says, SELECT CompanyName, CompanyID FROM dbo.CompanyIDFactTable WHERE (CompanyName = 'JNJ') and (CompanyID = 486) I press the green triangle to Start Debugging. The task completes and turns green. I can’t see any of the underworking’s of this task, so it’s difficult to verify exactly what it’s doing. In parallel, I can execute this task over in SQL Server 2008, so at least I got something to do my checks on. However, I tested this script with an invalid CompanyID (one that’s not in my table) by changing 486 => 999. I press save and Start Debugging again. Still, even with an invalid CompanyID the task turns green, but that’s not what I want. I understand the script works and return no records just column headers, hence turning Execute SQL Task green. However, I want Execute SQL Task to fail when a CompanyID is invalid. How can I rewrite my SQL statements to fail on bad CompanyIDs? I’ll have a red failure path from Execute SQL Task to File System Task, where files with bad DealerIDs get drop into my desktop folder. · The next step will be testing with 2 xml files. 1 xml file will have a valid CompanyID # (486) and 1 xml file will have an invalid CompanyID # (999). Evan Johnson

Well, the Execute SQL task is executing successfully. Just because it doesn't return any rows doesn't mean the task failed. :) You need to retrieve a row count, store it in a variable, and then check the value of the variable. I'd do this by changing the SELECT statement to SELECT COUNT(*) FROM CompanyIDFactTable WHERE CompanyName = 'JNJ' AND CompanyID = 486 And then storing the count in a new variable. Then you can use an expression on a precedence constraint to evaluate what to do with the results. If you have BIDS Helper installed, you can use it to expand the Biml snippet I've provided below into an example package that shows how to do this. (If not, BIDS Helper is free, open source, and can be installed by copying files if youdon't have local admin privileges). For a quick tutorial on using the Biml Package Generator feature, see: http://agilebi.com/jwelch/2011/05/13/creating-a-basic-package-using-biml/ <Biml xmlns="http://schemas.varigence.com/biml.xsd"> <Connections> <OleDbConnection Name="AW" ConnectionString="Data Source=.;Initial Catalog=AdventureWorks;Provider=SQLNCLI10.1;Integrated Security=SSPI;"/> </Connections> <Packages> <Package Name="GetResult" ConstraintMode="Parallel" AutoCreateConfigurationsType="None"> <Variables> <Variable Name="Person" DataType="Int32">10</Variable> <Variable Name="RowCount" DataType="Int32">0</Variable> </Variables> <Tasks> <ExecuteSQL Name="Get RowCount" ConnectionName="AW" ResultSet="SingleRow"> <DirectInput>SELECT COUNT(*) FROM Person.Contact WHERE ContactID = ?</DirectInput> <Parameters> <Parameter Name="0" DataType="Int32" VariableName="User.Person"/> </Parameters> <Results> <Result Name="0" VariableName="User.RowCount"/> </Results> </ExecuteSQL> <Container Name="Cleanup Tasks" ConstraintMode="Parallel"> <PrecedenceConstraints> <Inputs> <Input OutputPathName="Get RowCount.Output" EvaluationOperation="ExpressionAndConstraint" Expression="@RowCount > 0"/> </Inputs> </PrecedenceConstraints> </Container> </Tasks> </Package> </Packages> </Biml> John Welch | www.varigence.com | www.agilebi.com | ssisUnit.codeplex.com

Evan, From what I understood, the path till the company name is going to be constant i.e., \\ddrftp\a$\ddr\ Is my understanding right? And if that's the case, then this issue is solvable. However, you'd need to use a Script task for passing the Company name into a variable and once thats done, since, you said you already have a table for the company names and Ids, we can easily evaluate it against that table using an execute SQL task. You can actually do it without the script task as well. Finally, the Excel file issue ain't a big deal either. This can be done.

Assuming you have the full path in a variable named User::FileName, you can use the following expression to get the company name portion: SUBSTRING( @[User::FileName] , FINDSTRING( @[User::FileName] , "\\", 5) + 1, FINDSTRING( @[User::FileName] , "\\", 6) - FINDSTRING( @[User::FileName] , "\\", 5 ) - 1)John Welch | www.varigence.com | www.agilebi.com | ssisUnit.codeplex.com

@johnwelch Could you please explain what the +1 and -1 are doing? And to add FilePathName has a value of 0 representing the index (I think), not a path name. I call out the variable in a task. I don't even use the full path in my foreach loop because I traverse subfolders. Evan Johnson

Your understanding is correct. how would you do it without a script task..or with one? Thanks a lot guys!! I just bought a book on SSIS but could use some help now.Evan Johnson

@johnwelch Could you please explain what the +1 and -1 are doing? And to add FilePathName has a value of 0 representing the index (I think), not a path name. I call out the variable in a task. I don't even use the full path in my foreach loop because I traverse subfolders. Evan Johnson John tried to use SUBSTRING expression function to fetch the part of string you want, the +1 / -1 used in the expression is for justifiying the substring for fetch correct result, I suggest to use the SSIS expression tester tool: http://expressioneditor.codeplex.com/http://www.rad.pasfu.com