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

Top 5 Contributors of the Month
david stephan
Santhakumar Munuswamy
Asad Ali
Fauzul Azmi
Post New Web Links

SSIS script component bugs at around 10k rows

Posted By:      Posted Date: September 08, 2010    Points: 0   Category :Sql Server
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

View Complete Post

More Related Resource Links

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.

How to generate an excel and send it as email attachment in SSIS 2005 Script Task Component?


I am using a "Script Task" component. I am trying with following three simple steps:

1) Obtain the excel content in HTML

Dim reportInHtml As String = GenerateHTMLReport()

2) Generate an excel with below code:

Dim message As MailMessage

message = New MailMessage(fromAddress, toAddress, subject, String.Empty)

Using memoryStream As MemoryStream = New MemoryStream()

        Dim contentAsBytes As Byte() = Encoding.UTF8.GetBytes(reportInHtml)
        memoryStream.Write(contentAsBytes, 0, contentAsBytes.Length)
        memoryStream.Seek(0, SeekOrigin.Begin)

        Dim contentType As ContentType = New ContentType()
        contentType.MediaType = "application/ms-excel"
        contentType.Name = "Excel_Report.xls"

        Dim excelAttachment

Error while trying to assign a value to a Read Write variable in SSIS package script component



       I am trying to develop a SSIS package which will read the records from the flat file and insert them into a destination table. I have some validations written in script component. I have declared two Read Write variables with package level scope. when i try to assign a value to the variable in the script component and run the package, the package throws me an error "The collection of variables locked for read and write access is not available outside of PostExecute".


What should be done to over come the problem please help me on this regard 




How to execute a parameterized stored procedure using ssis dataflow script component transformation


Hi All,

Can any body send code for how to execute a parameterized stored procedure  using ssis dataflow script component transformation using .net

I know how to execute storedproc using control flow script task, but I need using ssis dataflow script component transformation.




How to Call .Exe in Shared Folder using Script Task Component in SSIS 2005 ?


Hi friends,

I have a Shared Folder where Two .NET .Exes are placed.

My requirment is to call this two Exes using Script Task Component.

I am very bigginer in SSIS.

Please provide me Assistance to solve this issue.

if you need more information regarding problem, let me know.


Find and replacement SSIS Custom component issues

Hi All, I developed one custom component in SSIS  Find and Replacement I register the dll and copy and paste in to Pipleline component. but it's not visible in the Toolbox while choose Items Please suggest why it's not comming.. using   System; using   System.Collections.Generic; using   System.Linq; using   System.Text; using   Microsoft.SqlServer.Dts.Pipeline; using   Microsoft.SqlServer.Dts.Design; using   Microsoft.SqlServer.Dts.Runtime.Wrapper; using   Microsoft.SqlServer.Dts.Pipeline.Wrapper; using   System.Data.OleDb; using   System.Data.SqlClient; using   System.Windows.Forms; using   System.Runtime.InteropServices; using   System.Data; using   System.Collections; using   Microsoft.SqlServer.Dts.Runtime; using   Microsoft.SqlServer.Server; namespace   FindAndReplace { [ ComVisible(true)] [ DtsPipelineComponent ( DisplayName = "Find and Replace", Description = "Finding Find and replace", IconResource = "Microsoft.Samples.SqlServer.Dts.Find and Replace.ico", ComponentType = ComponentType.Transform )]   class FindAndReplace :PipelineComponent {   #region   ProvideComponentProperties   /// <summary>   /// </summary>   public ove

Output parameters in OLEDB Command component - SSIS 2008

I have a package that I developed in SSIS 2005 and recently ported to 2008. Everything runs great except an OLE DB Command component that calls a stored procedure that uses output parameters. When I try to run this, I get this error: Error: 0xC0202009 at Data Flow Task, OLE DB Command [100]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Syntax error, permission violation, or other nonspecific error". Error: 0xC004701A at Data Flow Task, SSIS.Pipeline: component "OLE DB Command" (100) failed the pre-execute phase and returned error code 0xC0202009. When I do the same thing in SSIS2005, it works fine. So I setup a new package with 1 data flow. In that data flow, I added a source component, an OLE DB Command, and a destination.  I then tested this with a stored procedure that took no parameters. It worked fine. I then tested it with a stored procedure with 1 parameter defined as OUTPUT. This generated the error.   Is there an issue with output parameters in an OLE DB Command in SSIS 2008?   Thanks!http://bobp1339.blogspot.com

Custom SSIS Data Flow Component Not Showing in Toolbox or GAC

Hello - I have created a very simple data flow component for SSIS (Actually, I am following this example:  http://www.microsoft.com/downloads/details.aspx?familyid=1C2A7DD2-3EC3-4641-9407-A5A337BEA7D3&displaylang=en).  However, when I register the DLL to the GAC, I am unable to find the assembly in C:\Windows\Assembly - even though the GACUTIL says "Assembly Registered Successfully".  Furthermore, after copying the DLL to the PipelineComponents folder for SSIS (C:\Program Files (x86)\Microsoft SQL Server\100\DTS\PipelineComponents), it does not show in the "Choose Items . . ." dialog box of SSIS.   I am running SQL Server 2008 Dev edition, Visual Studio 2010 with .NET 4.0, and Windows 7 Enterprise 64-bit edition.  Any assistance/thoughts would be appreciated. Thanks!

C# newbie stuck - trying to access column data in a SharePoint list in an SSIS script task

Hello, I'm sure this is the simplest question but I can't figure it out, even with Google's help. I am trying to stumble through some C# code in an SSIS script task and I am frustrated that I can't figure out how to do the easiest things.  I eventually want to find data in a column,and then use another list as a lookup to replace that value with another where the existing value matches a value in the lookup list.  So, the data in my (multiple choice) column might be "apples; bananas" and in another list I have a row that contains two columns, the first holding the value "Apples" and the second containing "Red Delicious" and my original column should read: "Red Delicious; bananas." But, alas, I can't even figure out how to see the data that is in a column. Here is my code: /*<br/> Microsoft SQL Server Integration Services Script Task<br/> Write scripts using Microsoft Visual C# 2008.<br/> The ScriptMain is the entry point class of the script.<br/> */<br/> <br/> using System;<br/> using System.Data;<br/> using Microsoft.SharePoint;<br/> using Microsoft.SqlServer.Dts.Runtime;<br/> using System.Windows.Forms;<br/> using Microsoft.SharePoint.Utilities;<br/> <br/> namespace ST_08becda4c05c49cd9f30ea76110076cd.csproj<br/> {<br/> [

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,

Script Component

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

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" &

Accessing ActiveX Component functionality from SSIS

Hi I have my SSIS Package, from which I need to access the functionality of an external application.  The external application have an API exposed in form of ActiveX Component.  I have to access that ActiveX components. Now, how to have that ActiveX components exposed to my SSIS package? What are the steps to access that ActiveX component ? Request you to reply at the earliest as this is a critical requirement.  Please note that I am very new to SSIS technology.  

SSIS Script Task

I am trying get user input from a form created via script but the form flashes on the screen and then disappear...is there a special implementation for this?? The following code is in my main; FormIU   from = new FormIU();   from.Show();    

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

I need to create a script in SSIS which creates a data source that connects to an Access database.

I need to create a script in SSIS which creates a data source that connects to an Access database. The Access database file name needs to be set as a variable as it will change from month to month. I have no idea what I am doing can anybody give me some tips? Mr Shaw

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,
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