StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
primarykey
Id
8311938
data
AcceptedAnswerId
0
AnswerCount
0
ClosedDate
CommentCount
2
CommunityOwnedDate
CreationDate
2011-11-29T13:57:22.177
FavoriteCount
0
LastActivityDate
2011-11-29T15:34:39.047
LastEditDate
2011-11-29T15:34:39.047
LastEditorUserId
0
OwnerUserId
0
ParentId
8311752
PostTypeId
2
Score
6
ViewCount
0
LastEditorDisplayName
user756519
text
Body
Yes, one possible option is to make use of Script Task to process only the Worksheets of your preference. Following example was created using SSIS 2008 R2 and Excel 2010. The working folder for this example is <code>C:\Temp\</code>. I think the logic should still hold good for previous versions. In the folder path <code>C:\Temp\</code>, create an Excel 2007 spreadsheet file named <code>Country_States.xlsx</code> with three worksheets namely <code>US_1</code>, <code>US_2</code> and <code>Canada_1</code>. <code>US_1</code> of Country_States.xlsx contained the following data <img src="https://i.stack.imgur.com/r6wih.png" alt="States_1_Sheet_1"> <code>US_2</code> of Country_States.xlsx contained the following data <img src="https://i.stack.imgur.com/zAJ4Z.png" alt="States_1_Sheet_2"> <code>Canada_1</code> of Country_States.xlsx contained the following data <img src="https://i.stack.imgur.com/AvXVO.png" alt="[States_2_Sheet_1"> Create a table in SQL Server named dbo.Destination using the below create script. Excel sheet data will be inserted into this table. <pre><code>CREATE TABLE [dbo].[Destination]( [Id] [int] IDENTITY(1,1) NOT NULL, [State] [nvarchar](255) NULL, [Country] [nvarchar](255) NULL, [FilePath] [nvarchar](255) NULL, [SheetName] [nvarchar](255) NULL, CONSTRAINT [PK_Destination] PRIMARY KEY CLUSTERED ([Id] ASC)) ON [PRIMARY] GO </code></pre> The table is currently empty. <img src="https://i.stack.imgur.com/aXu6B.png" alt="Empty table"> Create a new SSIS package and on the package, create the following 6 variables. FolderPath will contain the folder where the Excel files are stored. FilePattern will contain the extension of the files that will be looped through and this example works only for <code>.xlsx</code>. FilePath will be assigned with a value by the Foreach Loop container but we need a valid path to begin with for design time and it is currently populated with the path <code>C:\temp\Country_States.xlsx</code> of the first Excel file. SheetName will contain the actual sheet name but we need to populate with initial value <code>US_1$</code> to avoid design time error. <code>ProcessTheSheet</code> will contain true/false and set with the default value of false. <code>PatternToMatch</code> will contain the pattern that will use to match only the worksheets of our choice. <img src="https://i.stack.imgur.com/JOJlJ.png" alt="Variables"> In the package's connection manager, create an ADO.NET connection with the following configuration and name it as ExcelSchema. Select the provider <code>Microsoft Office 12.0 Access Database Engine OLE DB Provider</code> under .Net Providers for OleDb. Provide the file path <code>C:\temp\Country_States.xlsx</code> <img src="https://i.stack.imgur.com/D8OGb.png" alt="ExcelSchema 1"> Click on the <code>All</code> section on the left side and set the property Extended Properties to <code>Excel 12.0</code> to denote the version of Excel. Here in this case 12.0 denotes <code>Excel 2007 or above</code>. Click on the Test Connection to make sure that the connection succeeds. <img src="https://i.stack.imgur.com/q69xl.png" alt="ExcelSchema 2"> Create an Excel connection manager named Excel as shown below. <img src="https://i.stack.imgur.com/stS6t.png" alt="Excel"> Create an OLE DB Connection SQL Server named <code>SQLServer</code>. So, we should have three connections on the package as shown below. <img src="https://i.stack.imgur.com/Y9im1.png" alt="Connections"> We need to do the following connection string changes so that the Excel file is dynamically changed as the files are looped through. On the connection ExcelSchema, configure the expression <code>ServerName</code> to use the variable <code>FilePath</code>. Click on the ellipsis button to configure the expression. <img src="https://i.stack.imgur.com/pmj9E.png" alt="ExcelSchema ServerName"> Similarly on the connection Excel, configure the expression <code>ServerName</code> to use the variable <code>FilePath</code>. Click on the ellipsis button to configure the expression. <img src="https://i.stack.imgur.com/oJIft.png" alt="Excel ServerName"> On the Control Flow, place two Foreach Loop containers one within the other. The first <code>Foreach Loop container</code> named Loop files will loop through the files. The second <code>Foreach Loop container</code> will through the sheets within the container. Within the inner For each loop container, place a Script Task that will validate only the sheets that should be processed and a Data Flow Task that will read the Excel files and load data into SQL <img src="https://i.stack.imgur.com/5dGX1.png" alt="Control Flow"> Configure the first Foreach loop container named Loop files as shown below: <img src="https://i.stack.imgur.com/cW7F7.png" alt="Foreach Loop 1 Collection"> <img src="https://i.stack.imgur.com/rlyUE.png" alt="Foreach Loop 1 Variable Mappings"> Configure the first Foreach loop container named Loop sheets as shown below: <img src="https://i.stack.imgur.com/bVnbR.png" alt="Foreach Loop 2 Collection"> <img src="https://i.stack.imgur.com/RZDbK.png" alt="Foreach Loop 2 Variable Mappings"> Script Task should be configured with following code that verifies the <code>SheetName</code> value against the pattern stored in <code>PatternToMatch</code> variable. If the SheetName matches the pattern, then the variable ProcessTheSheet is set to True or else it will be set to False. C# code for SSIS 2008 and above Include the statement <code>using System.Text.RegularExpressions;</code> for RegEx. <pre><code>public void Main() { Variables varCollection = null; Dts.VariableDispenser.LockForRead("User::SheetName"); Dts.VariableDispenser.LockForRead("User::PatternToMatch"); Dts.VariableDispenser.LockForWrite("User::ProcessTheSheet"); Dts.VariableDispenser.GetVariables(ref varCollection); string sheetName = varCollection["User::SheetName"].Value.ToString(); string pattern = varCollection["User::PatternToMatch"].Value.ToString(); Regex rgx = new Regex(pattern, RegexOptions.IgnoreCase); Match match = Regex.Match(sheetName, pattern); varCollection["User::ProcessTheSheet"].Value = match.Success; Dts.TaskResult = (int)ScriptResults.Success; } </code></pre> Right the connector that joins the Script Task and the Data Flow Task and select Edit option. It should bring the Precedence Constraint Editor dialog. Set the Evaluation operation to Expression and set the Expression to <code>@[User::ProcessTheSheet]</code> This expression will allow the package to continue to Data Flow Task only if the Sheetname matches the pattern provided in the variable PatternToMatch. You can notice that the connector contains fx, which means there is an expression in place and also the color changes from Green to Blue. <img src="https://i.stack.imgur.com/dqOuh.png" alt="Precedence Constraint"> Inside the data flow task, place an Excel Source, Derived Column and OLE DB Destination as shown below: <img src="https://i.stack.imgur.com/6FOcW.png" alt="Data Flow Task"> Configure the Excel Source to read the appropriate Excel file and the sheet that is currently being looped through. <img src="https://i.stack.imgur.com/Q1Ona.png" alt="Excel Source Connection Manager"> <img src="https://i.stack.imgur.com/3SEHc.png" alt="Excel Source Columns"> Configure the derived column to create new columns for file name and sheet name. This is just to demonstrate this example but has no significance. <img src="https://i.stack.imgur.com/eCKih.png" alt="Derived column"> Configure the OLE DB destination to insert the data into the SQL table. <img src="https://i.stack.imgur.com/ZUo6x.png" alt="OLE DB Destination Connection Manager"> <img src="https://i.stack.imgur.com/NprFQ.png" alt="OLE DB Destination Columns"> Below screenshot shows successful execution of the package. <img src="https://i.stack.imgur.com/K6PPc.png" alt="Execution successful"> When the <code>PatternToMatch</code> variable is set to the value CA*, the table is populated only with the values from the Sheet Canada_1. <img src="https://i.stack.imgur.com/SxqDJ.png" alt="CA_Pattern"> Deleted all the rows from the table. Changed the <code>PatternToMatch</code> variable value to US*, the table is populated only with the values from the sheets US_1 and US_2. <img src="https://i.stack.imgur.com/oIhQ8.png" alt="US_Pattern"> Hope that helps.
Tags
Title
singulars
PostAcceptedAnswerId
1. This table or related slice is empty.
PostParentId
1. POHow can I skip items in a foreach loop container set to foreach ado enumerator?
 singulars
 PostTypePostTypeId
 PTQuestion
PostTypePostTypeId
1. PTAnswer
UserLastEditorUserId
1. This table or related slice is empty.
UserOwnerUserId
1. This table or related slice is empty.
plurals
PostLinksPostIdRelatedPostId
1. This table or related slice is empty.
PostLinksRelatedPostIdPostId
1. This table or related slice is empty.
PostsAcceptedAnswerId
1. This table or related slice is empty.
PostsParentIdCreationDate
1. This table or related slice is empty.
VotesPostIdCreationDate
1. VO
 singulars
 PostPostId
 PO
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTUpMod
2. VO
 singulars
 PostPostId
 PO
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTUpMod
3. VO
 singulars
 PostPostId
 PO
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTUpMod
CommentsPostId
1. COI would make the expression more restrictive (e.g. `20*- claim report.xls`), but that would be nonetheless the easiest way to achieve what the question is asking
 singulars
 PostPostId
 PO
 UserUserId
 UScfrag
2. COjust to confirm, i need to filter the worksheet names in the ADO enumerator, not the filenames of the XLS files being processed.
 singulars
 PostPostId
 PO
 UserUserId
 USAdam McC

Querying!

Guidance

A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.