.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
MarieAdela
Imran Ghani
Post New Web Links

Script Component

Posted By:      Posted Date: August 31, 2010    Points: 0   Category :Sql Server
 
Hi Guys,   I am newbie and never done coding in vb. script, I am using SSIS component for this. I am using below code to allow user to enter storeid and change file name inside the directory from filename.ajk to storeid_filename.ajk.   Example:  01012010.ajk 02012010.ajk 03012010.ajk Once the user enter the storeid all the files in that particular directory will change to like this automatically 192572_01012010.ajk 192572_02012010.ajk 192572_03012010.ajk Now all I want is to change the file name from 01012010 to 20100101. So once the user enter the storeid it has to change it to  192572_20100101.ajk 192572_201001012.ajk 192572_20100103.ajk     Please help me guys its a urgent help.       Public Sub Main() ' ' Add your code here         '         Dim InputFilePath As String         Dim StoreNo As String = InputBox("Enter Store No", "StoreNo")         InputFilePath = Dts.Variables("strInputFolder").Value         Dim strFileSize As String = ""           Dts.Variables("StoreNo").Value = StoreNo         Dim di As New IO.DirectoryInfo(InputFilePath)         Dim aryFi As IO.FileInfo


View Complete Post


More Related Resource Links

Script Component HELP!

  
Hi Guys, I have some recordset in the data flow. CURRENT Column1 Column2 Column3 Column4 Column5 Column6 Dine In DISCOUNT 20100824 121618 1859 37 Dine In TAX 20100824 121618 1859 -15.8 Dine In TOTAL 20100824 121618 1859 6.77 Dine In TENDER 20100824 121618 1859 63.2 Dine In TAX 20100824 121618 1859 63.2 Dine In DISCOUNT 20100824 121618 1860 37 Dine In TAX 20100824 121618 1860 -15.8 Dine In TOTAL 20100824 121618 1860 6.77 Dine In TENDER 20100824 121618 1860 63.2 Dine In TAX 20100824 121618 1860 63.2 I want to rename the second "TAX" in Column2 with the value of "DELIVERY CHARGE" and i was thinking on how to do this using script component. TARGET Column1 Column2 Column3 Column4 Column5 Column6 Dine In DISCOUNT 20100824 121618 1859 37 Dine In TAX 20100824 121618 1859 -15.8 Dine In TOTAL 20100824 121618 1859 6.77 Dine In TENDER 20100824 121618 1859 63.2 Dine In DELIVERY CHARGE 20100824 121618 1859 63.2 Dine In DISCOUNT 20100824 121618 1860 37 Dine In TAX 20100824 121618 1860 -15.8 Dine In TOTAL 20100824 121618 1860 6.77 Dine In TENDER 20100824 121618 1860 63.2 Dine In DELIVERY CHARGE 20100824 121618 1860 63.2 Thanks in advance,

Question using Script Component

  
Hi Guys, I have this records on my dataflow.   Column1 Column2 Column3 ITEM 1 P ITEM 2 S ITEM 3 N ITEM 4 O ITEM 5 M ITEM 6 MC ITEM 7 MAO ITEM 8 MT ITEM 9 P ITEM 10 S ITEM 11 N ITEM 12 O ITEM 13 M ITEM 14 MC ITEM 15 MAO ITEM 16 MT    I want to add a new column that based on these condition. 1. If Column1 and Column3 is "S", "N", "O", "P" then Column4 value is  1 2. If Column1 and Column3 is "M" then Column4 values is 0 3. if Column1 is not "ITEM" then Column4 value is NULL 4. If Column1 and Column3 is "MC", "MAO" , "MT" then get the last column2 value place for Column4.  Column1 Column2 Column3 Column4 ITEM 1 P 1 ITEM 2 S 1 ITEM 3 N 1 ITEM 4 O 1 ITEM 5 M 5 ITEM 6 MC 5 ITEM 7 MAO 5 ITEM 8 MT 5 ITEM 9 P 1 ITEM 10 S 1 ITEM 11 N 1 ITEM 12 M 12 ITEM 13 O 1 ITEM 14 MC 1 ITEM 15 MAO 1 ITEM 16 MT 1  I have the following script to get some of the conditions, but im stuck in the last one with the "MC, MAO, MT" in Column3.  If Column1 = "ITEM" And Column3 = "S" Or Column3 = "N" Or Column3 = "O" Or Column3 = "P" Then             Column4 = "1" &

Error in Script component in the Data Flow Task

  
Hi, I need a small help. This is what i require. I need to generate a auto increment number using script component and assign it to target column. use db1 go create table src (name varchar(10) ) insert into src select 'A' union all select 'B' union all select 'C'   create table trg (id int, name varchar(10) ) while loading the trg table, i need to generate a auto increment number and then load it to target. Finally, my trg table data should look as follows id name 1  A 2  B 3  C This is what i have done inside the package. step1 : declared a global variable "tmp" of int32 step2 : created a connection manager pointing to "db1" database step3 : drag n drop data flow task step : inside the data flow task , i have the following        OLEDB Source  (select name from src)           |        Script Component (i have added a new Output column "v_id" of int datatype in INPUTS AND OUTPUTS page)           |        OLEDB Destination In the Script page in Script component , in ReadWriteVariables | User::tmp, I have chose Visual Basic 2008 and clicked the EDit script and inside the Editor this and inisde the below routine i have added only two

Script Component Question

  
Hi Guys, I got this recordet from a flat file. C1 C2 ITM 0 ITM 0 ITM 0 ITM 0 TX 0 DC 0 TOT 1 TN 0 ITM 0 ITM 0 ITM 0 ITM 0 TX 0 DC 0 TOT 2 TN 0 The requirement is if C1 = TOT, then the C2 value remains the same. If C1 <> TOT then C2 value is equal to C1 = TOT value. C1 C2 ITM 1 ITM 1 ITM 1 ITM 1 TX 1 DC 1 TOT 1 TN 1 ITM 2 ITM 2 ITM 2 ITM 2 TX 2 DC 2 TOT 2 TN 2 The sorting is fix. TOT always come first before the TN. Can a script component do this and how? Thanks,

SSIS script component bugs at around 10k rows

  
I'm trying to load a dimension table from a tab delimited file to a parent-child table. I have made a script like this: public override void Input0_ProcessInputRow(Input0Buffer Row) { String level0 = null, level1 = null, level2 = null, level3 = null, level4 = null, level5 = null; while (!Row.EndOfRowset()) { if (Row.Description_IsNull || Row.Description == "") { if (!Row.NextRow()) break; continue; } if (!Row.Level0_IsNull) { Row.ID = Row.Level0; Row.ParentID = null; level0 = Row.Level0; Row.Description = Row.Description; } else if (!Row.Level1_IsNull) { Row.ID = Row.Level1; Row.ParentID = level0; level1 = Row.Level1; Row.Description = Row.Description; } else if (!Row.Level2_IsNull) { Row.ID = Row.Level2; Row.ParentID = level1; level2 = Row.Level2; Row.Description = Row.Description; } else if (!Row.Level3_IsNull) { Row.ID = Row.Level3; Row.ParentID = level2; level3 = Row.Level3; Row.Description = Row.Description; } else if (!Row.Level4_IsNull) { Row.ID = Row.Level4; Row.ParentID = level3; level4 = Row.Level4; Row.Description = Row.Description; } else if (!Row.Level5_IsNull) { Row.ID = Row.Level5; Row.ParentID = level4; level5

Script Component Help

  
Hi Guys, I have the following recordset in my dataflow.   Col1 Col2 Col3 Col4 Col5 Col6 Col7 Col8 Col9 Col10 Col11 Col12 Col13 Col14 Col15 Col16 Col17 Col18 Delivery       DELIVERY 1234567 1 Ewoks 0 0 0 0 0 0 0 0 0 0 0 0   Delivery       ITEM   20100804 111524 25 1 1 4001 5 1 5 1 0 JED SPAGHETTI 1 42   Delivery       TAX   20100804 111524 25 1 1 0 0 0 0 0 0 TAX 1 92.18   Delivery       TOTAL   20100804 111524 25 1 1 5 0 7 1 0 1 Delivery       1 489.5   Delivery       TENDER   20100804 111524 25 1 1 0 1 1 0 0 0 CASH 1 489.5   DELIVERY 6671775   Sari-Sari Store 0 0 0 0 0 0 0 0 0 0 0 0     DELIVERY 6671775   Sari-Sari Store 0 0 0 0 0 0 0 0 0 0 0 0     PaidOut-Refd KPP Dep PAIDOUT   20100804 104450 1304 1 -1 7 0 0 0 0 0 PaidOut-Refd KPP Dep 1 500     Conditions: If Col1 <> "DELIVERY" And Col2 = "DELIVERY" Then Col18 = Col3 + "," + Col4 Where Col2 = "TENDER" If Col1 = "DELIVERY" And Col2 = "DELIVERY" Then Col18 = Col3 + "," + Col4 Where Col2 = "TENDER" If Col1 in first row = "DEL

SQL Server 2008 Script Componant Error - [SSIS.Pipeline] Error: component "Script Comp Name" (48) fa

  
Hi All,   I am facing one strange issue in SSIS 2008. I have developed one SSIS package which is importing excel data into SQL Server 2008. This Package contains some Scirpt componants. This package is working fine on Windows XP machine but when I am trying to run on Windows 2003 server it gives me error "[SSIS.Pipeline] Error: component "SCR RDSTableRelation" (48) failed the post-execute phase and returned error code 0x80004002". Surprising thing is that if on windows server 2003server  if I drag a new script componat and paste the previous script code itself then it works fine. Even if I copy-paste the existing script componant and give source-destination connectino to this new script componant then also it works.

Script Component - Change One Row Input to Multiple Row Output

  
Hi All I am new to SSIS, and looking for help. I do not know if it is possible, but I like to try using the "Script Component" to change a input like this:   1234 1235 1236 ...   to and output like this:   1234, Value1 1235, Value2 1235, Value3 1235, Value4 1236, Value5 ...   Depending of the input value, 1 or more Rows are pushed out the Output, Like the value 1234 -> result in 1 Row in the output, where the value 1235 -> result in 3 rows in the output. Is this possible to do in a Script Component?? If it is how do you program such behavior? Do some good sources to learn SSIS exist out there?? I think it is not easy to find information. Peter Schwennesen

Follow up question for Script Component

  
Hi Guys, Just a follow up question on http://social.msdn.microsoft.com/Forums/en/sqlintegrationservices/thread/29145a28-75b0-4027-a356-95e21cc341a4. I just realized that the "TN" can have multiple lines. ie. C1 C2 ITM 1 ITM 1 ITM 1 ITM 1 TX 1 DC 1 TOT 1 TN 1 ITM 2 ITM 2 ITM 2 ITM 2 TX 2 DC 2 TOT 2 TN 2 TN 2 TN 2 In RAAD's code, it actually scanned for "TN" as a breakpoint per transaction.  However, if i have atleast 2 or 3 "TN" in the same transactions, the value is null. The question is how can i have the value of 2nd or 3rd TN like the previous TN? Thanks, 

Using Script Component

  
Hi Guys, I have these recordset in my dataflow.   C1 C2 C3 C4 C5 Dvry DVRY 12 1 A Dvry ITM 54 9/16/2010 9:03:12 Dvry ITM 54 9/16/2010 9:03:12 Dvry DISC 54 9/16/2010 9:03:12 Dvry TAX 54 9/16/2010 9:03:12 Dvry DC 54 9/16/2010 9:03:12 Dvry TOT 54 9/16/2010 9:03:12 Dvry TDR 54 9/16/2010 9:03:12 Dvry ITM 55 9/16/2010 10:03:12 Dvry ITM 55 9/16/2010 10:03:12 Dvry DISC 55 9/16/2010 10:03:12 Dvry TAX 55 9/16/2010 10:03:12 Dvry DC 55 9/16/2010 10:03:12 Dvry TOT 55 9/16/2010 10:03:12 Dvry TDR 55 9/16/2010 10:03:12 Din DVRY    B 0 Din ITM 56 9/16/2010 11:03:12 Din ITM 56 9/16/2010 11:03:12 Din DISC 56 9/16/2010 11:03:12 Din TAX 56 9/16/2010 11:03:12 Din DC 56 9/16/2010 11:03:12 Din TOT 56 9/16/2010 11:03:12 Din TDR 56 9/16/2010 11:03:12 Din ITM 57 9/16/2010 11:03:12 Din ITM 57 9/16/2010 11:03:12 Din DISC 57 9/16/2010 11:03:12 Din TAX 57 9/16/2010 11:03:12 Din DC 57 9/16/2010 11:03:12 Din TOT 57 9/16/2010 11:03:12 Din TDR 57 9/16/2010 11:03:12 I want to add a new column and have output just like these using Script Component.   C1 C2 C3 C4 C5 C6 Dvry DVRY 12 1 A   Dvry ITM 54 9/16/2010 9:03:12   Dvry ITM 54 9/16/2010 9:03:12   Dvry DISC 54 9/16/2010 9:03:12   Dvry TAX 54 9/16/2010 9:03:12   Dvry DC 54 9/16/2010 9:03:12   Dvry TOT 54 9/16/2010 9:03:12  

Script Component Transformation as a Lookup

  
Hi Guys, I have the following code from my Script Component as a Lookup. Imports System Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper Imports Microsoft.SqlServer.Dts.Runtime.Wrapper Imports System.Collections.Generic Imports System.Data.SqlClient <Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute()> _ <CLSCompliant(False)> _ Public Class ScriptMain     Inherits UserComponent     Dim connMgr As IDTSConnectionManager100     Dim sqlConn As SqlConnection     Dim sqlCmd As SqlCommand     Private vTaxAmt As Decimal     Private vDelChgAmt As Decimal     Public Overrides Sub AcquireConnections(ByVal Transaction As Object)         connMgr = Me.Connections.Connection         sqlConn = CType(connMgr.AcquireConnection(Nothing), SqlConnection)     End Sub     Public Overrides Sub PreExecute()         sqlCmd = New SqlCommand("SELECT ItemTypeCode FROM LkpItemDepartment WHERE (ItemDeptCode = @ItemDeptCode) AND (ItemClassCode = @ItemClassCode)", sqlConn)                 With sqlCmd.Parameters   

accessing package level variables in a Script Component in 2008 version

  

how to access the package level variables within a script component in SSIS 2008


how to store the values in a object variable (package level scope) in a script component

  
I have declared a variable of object type with package level scope.  I want to assign values to this variable inside a script component.  My object should hold 5 columns in a row.  How to implement this ?

Problems to Add a Extra Row in Script Component

  

Hello,

#) I have a Script Task which is of Transform Type and Takes Input from OLEDB Source

#) Now I can easily parse through all records and successful push them in OLEDB destination.

#) Now I need to add an extra row in Script Task, which calculates the totals of the amount across Rows. This Row should be added at end.

 

Can anybody help me on this.

Thanks in advance.

Regards,

Sachin


Question on Script Component

  

Hi Guys,

I have this recordset in my data flow.

C1 C2 C3
DLV 1234567 JED
ITM 1 A
DSC 1 A

Sorting inside Script Component

  

Hi Guys,

A weird question, but i need to do a sorting inside of the Script Component. I have a RowNumber Column, that i need to sort by ascending.

Is their away to do this?

Thanks,

 


Using Script Component For Lookup

  

Hey everyone. First let me say that I'm new to the forums and new to SSIS.  I've been a .Net developer since the beginning but never used SSIS until now.  With that said, here is my issue. 

My Problem:  I have a situation where I have data coming from a mainframe Db2 source and one of the fields is a state column.  It can hold either the state code (NC) or the name of the state (North Carolina).  Our target table will only hold the 2-character state code. So I need to check the State column for each record and if the length is greater than 2 characters, I need to do a lookup and return the state code for the given state name.

Possible Solutions:

I initially was going down the path of using a Conditinoal-Split task to split the records based on the state column being 2 characters or bigger, with a Fuzzy Lookup for the state name and then a Merge task to merge the two sets of records back together before inserting into my target.  However, I was in an SSIS training class this past week and ran this solution by my instructor and he thought that a Script Component would be better for this kind of transformation.

So I've been working on my second solution using a Script Component Transformation task and my input was the State column and my output was the transformed state code.&n

Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend