StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
primarykey
Id
6210238
data
AcceptedAnswerId
0
AnswerCount
0
ClosedDate
CommentCount
0
CommunityOwnedDate
CreationDate
2011-06-02T04:05:48.737
FavoriteCount
0
LastActivityDate
2011-06-02T04:05:48.737
LastEditDate
LastEditorUserId
0
OwnerUserId
0
ParentId
5502189
PostTypeId
2
Score
3
ViewCount
0
LastEditorDisplayName
text
Body
The process that you are trying to achieve in the question can be done with the help of the <code>Output Columns</code> in <code>Script Component Transformation</code> task instead of using the variables. The below example explains how this can be achieved and also it demonstrates how variables can be used within the script component. However the same functionality can be achieved using <code>Derived Column Transformation</code> task as well. This example reads a CSV file and based on the first column value, it will extract value from second column starting from a give position and length. All these values will be stored in a variable. Step-by-step process: <ol> <li>Create a CSV file as shown in screenshot #1. If the first column has the value <code>Customer</code>, we will extract 6 characters from 9th character on the second column value. If the first column has the value <code>Vendor</code>, we will extract 7 characters from 21st character on the second column value. For every other value, the package will assume value zero for second column value.</li> <li>Create a table named <code>dbo.Destination</code> using the script under SQL Scripts section. The package will insert the CSV data into this table.</li> <li>On the SSIS package, create 6 variables as shown in screenshot #2. Configure an <code>OLE DB connection</code> to connect to the SQL Server instance. Configure a <code>Flat File connection</code> to read the CSV file as shown in screenshots #3 - #6. Also, place a <code>Data Flow Task</code> on the <code>Control Flow</code> tab of the package. </li> <li>Configure the Data Flow Tab with a <code>Flat File Source</code>, <code>Script Component Transformation Task</code> (Refer screenshot #7) and an <code>OLE DB Destination</code>.</li> <li>Configure the <code>Flat File Source</code> task as shown in screenshots #8 and #9. </li> <li>Configure the <code>Script Component</code> transformation task as shown in screenshots #10 and #11. A new <code>Output Column</code> named SNumber of data type <code>four-byte signed integer [DT_I4]</code> is created to store the extracted value. Replace the <code>Script Component</code> code with code displayed under <code>Script Component Code</code> section. <code>PreExecute</code> method reads the package variables' values and the method <code>Input0_ProcessInputRow</code> processes the logic to populate the Output Column <code>SNumber</code>.</li> <li>Configure the <code>OLE DB Destination</code> task as shown in screenshots #12 and #13.</li> <li>Screenshot #14 shows sample package execution.</li> <li>Screenshot #15 shows the data in the table <code>dbo.Destination</code> after the package execution.</li> </ol> Hope that helps. SQL Scripts: . <pre><code>CREATE TABLE [dbo].[Destination]( [Id] [int] IDENTITY(1,1) NOT NULL, [Header] [varchar](50) NOT NULL, [Value] [varchar](50) NOT NULL, [SNumber] [int] NOT NULL, CONSTRAINT [PK_Destination] PRIMARY KEY CLUSTERED ([Id] ASC)) ON [PRIMARY] GO </code></pre> Script Component Code: C# code that can be used only in <code>SSIS 2008 and above</code>. . <pre><code>/* Microsoft SQL Server Integration Services Script Component * Write scripts using Microsoft Visual C# 2008. * ScriptMain is the entry point class of the script.*/ using System; using System.Data; using Microsoft.SqlServer.Dts.Pipeline.Wrapper; using Microsoft.SqlServer.Dts.Runtime.Wrapper; [Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute] public class ScriptMain : UserComponent { IDTSVariables100 varCollection = null; string customer = string.Empty; int customerPosition = 0; int customerLength = 0; string vendor = string.Empty; int vendorPosition = 0; int vendorLength = 0; public override void PreExecute() { this.VariableDispenser.LockForRead("User::Customer"); this.VariableDispenser.LockForRead("User::CustomerPosition"); this.VariableDispenser.LockForRead("User::CustomerLength"); this.VariableDispenser.LockForRead("User::Vendor"); this.VariableDispenser.LockForRead("User::VendorPosition"); this.VariableDispenser.LockForRead("User::VendorLength"); this.VariableDispenser.GetVariables(out varCollection); customer = varCollection["User::Customer"].Value.ToString(); customerPosition = Convert.ToInt32(varCollection["User::CustomerPosition"].Value); customerLength = Convert.ToInt32(varCollection["User::CustomerLength"].Value); vendor = varCollection["User::Vendor"].Value.ToString(); vendorPosition = Convert.ToInt32(varCollection["User::VendorPosition"].Value); vendorLength = Convert.ToInt32(varCollection["User::VendorLength"].Value); base.PreExecute(); } public override void PostExecute() { base.PostExecute(); } public override void Input0_ProcessInputRow(Input0Buffer Row) { if (Row.Header.ToString().Trim() == customer) { Row.SNumber = Convert.ToInt32(Row.Value.Substring(customerPosition, customerLength)); } else if (Row.Header.ToString().Trim() == vendor) { Row.SNumber = Convert.ToInt32(Row.Value.Substring(vendorPosition, vendorLength)); } else { Row.SNumber = 0; } } } </code></pre> Screenshot #1: <img src="https://i.stack.imgur.com/hHRzj.png" alt="1"> Screenshot #2: <img src="https://i.stack.imgur.com/a2tY2.png" alt="2"> Screenshot #3: <img src="https://i.stack.imgur.com/YiDfI.png" alt="3"> Screenshot #4: <img src="https://i.stack.imgur.com/p1OyP.png" alt="4"> Screenshot #5: <img src="https://i.stack.imgur.com/ad42L.png" alt="5"> Screenshot #6: <img src="https://i.stack.imgur.com/CieO9.png" alt="6"> Screenshot #7: <img src="https://i.stack.imgur.com/bkukG.png" alt="7"> Screenshot #8: <img src="https://i.stack.imgur.com/HM0yJ.png" alt="8"> Screenshot #9: <img src="https://i.stack.imgur.com/uHbBE.png" alt="9"> Screenshot #10: <img src="https://i.stack.imgur.com/HJsZK.png" alt="10"> Screenshot #11: <img src="https://i.stack.imgur.com/Jhy56.png" alt="11"> Screenshot #12: <img src="https://i.stack.imgur.com/OqAcN.png" alt="12"> Screenshot #13: <img src="https://i.stack.imgur.com/ToI9o.png" alt="13"> Screenshot #14: <img src="https://i.stack.imgur.com/cQhJc.png" alt="14"> Screenshot #15: <img src="https://i.stack.imgur.com/gm7WH.png" alt="15">
Tags
Title
singulars
PostAcceptedAnswerId
1. This table or related slice is empty.
PostParentId
1. POHow to write to a Variable in script component
 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
CommentsPostId
1. This table or related slice is empty.

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.