' Microsoft SQL Server Integration Services Script Task ' Write scripts using Microsoft Visual Basic ' The ScriptMain class is the entry point of the Script Task. Imports System Imports System.Xml Imports System.Data Imports System.Math Imports System.Data.Odbc Imports Microsoft.SqlServer.Dts.Runtime Imports System.Data.OleDb Imports Oracle.DataAccess.Client Public Class ScriptMain ' The execution engine calls this method when the task executes. ' To access the object model, use the Dts object. Connections, variables, events, ' and logging features are available as static members of the Dts class. ' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure. ' ' To open Code and Text Editor Help, press F1. ' To open Object Browser, press Ctrl+Alt+J. Public Sub Main() Dim OrCon As OracleConnection = Nothing Dim OrCmd As OracleCommand Dim selectstrSQL As String Dim strSQL As String Dim ds As New DataSet() Dim da As OdbcDataAdapter = Nothing Dim command As OdbcCommand = Nothing Dim errorstr As Boolean = False Using con As New OdbcConnection(Dts.Variables("gSourceConnection").Value.ToString()) Try 'Connection opened con.Open() 'Set Command object command = New OdbcCommand("select Cust_Detail.ClientID,LegalEntity.LEGAL_NAME,LegalEntity.LEGAL_BUS_TYPE_CODE,Cust_External.AliasID,Cust_Detail.FullCustomerName, Cust_Detail.TaxId ,LegalEntity.SSN, LegalEntity.MAIN_PHONE,LegalEntity.REG_ADDRESS_LINE1, LegalEntity.REG_ADDRESS_LINE2, LegalEntity.REG_ADDRESS_LINE3,LegalEntity.REG_CITY, LegalEntity.REG_STATE_CODE, LegalEntity.REG_PROVINCE, LegalEntity.REG_ZIP, LegalEntity.REG_COUNTRY_CODE,LegalEntity.MAIL_ADDRESS_LINE1, LegalEntity.MAIL_ADDRESS_LINE2,LegalEntity.MAIL_CITY,LegalEntity.MAIL_STATE_CODE, LegalEntity.MAIL_ZIP,LegalEntity.MAIL_COUNTRY_CODE,LegalEntity.LEGAL_STRUCTURE_CODE,Cust_Detail.StockTicker,LegalEntity.NAICS_CODE,LegalEntity.ASSET_SIZE,LegalEntity.WEB_ADDRESS,LegalEntity.REVENUE,LegalEntity.BRANCH_SITE_INDICATOR,LegalEntity.BRANCH_LOCATION_NAME, Cust_External.SystemCode from Adaptiv.Cust_Detail Cust_Detail,Adaptiv.Cust_External Cust_External,GenericViews.LegalEntity LegalEntity where Cust_Detail.TAXID NOT IN ('999999999', '000000000' , '123456789') and LegalEntity.ClientId = Cust_External.CustomerID and Cust_External.CustomerID = Cust_Detail.ClientID and Cust_External.SystemCode in ('AFS','CLYPSO','ABLE','ILEASE','OPICS','CID')", con) command.CommandTimeout = 900 da = New OdbcDataAdapter() da.SelectCommand = command da.Fill(ds, "Customers") Catch ex As Exception 'Console.WriteLine(ex.Message) 'Dts.Variables("ErrorMessage").Value = ex.Message Dim vars As Variables Dts.VariableDispenser.LockOneForWrite("ErrorMessage", vars) 'vars(0).Value = " Error While retiving the data from SCI" vars(0).Value = "Error While retiving the data from SCI, Error Message::" & (ex.Message) vars.Unlock() errorstr = True Finally command.Dispose() End Try End Using If errorstr.Equals(False) Then strSQL = "INSERT INTO CRV.T_CRV_SCI_CUST_TEMPDATA(SCI_ID,LEGAL_NAME,LEGAL_BUS_TYPE_CODE,ALIAS_ID,FULL_CUSTOMER_NAME,TAX_ID,SSN,MAIN_PHONE,REG_ADDRESS_LINE1,REG_ADDRESS_LINE2,REG_ADDRESS_LINE3,REG_CITY, REG_STATE_CODE, REG_PROVINCE, REG_ZIP,REG_COUNTRY_CODE,MAIL_ADDRESS_LINE1, MAIL_ADDRESS_LINE2,MAIL_CITY,MAIL_STATE_CODE,MAIL_ZIP,MAIL_COUNTRY_CODE,LEGAL_STUCTURE_CODE,STOCK_TICKER,NAICS_CODE,ASSET_SIZE,WEB_ADDRESS,REVENUE,BRANCH_SITE_INDICATOR,BRANCH_LOCATION_NAME,WF_LEGAL_ENTITY_INDICATOR,SYSTEM_CODE) Values(:SCI_ID, :LEGAL_NAME,:LEGAL_BUS_TYPE_CODE,:ALIASID,:FULL_CUSTOMER_NAME,:TAX_ID,:SSN,:MAIN_PHONE,:REG_ADDRESS_LINE1,:REG_ADDRESS_LINE2,:REG_ADDRESS_LINE3,:REG_CITY, :REG_STATE_CODE, :REG_PROVINCE, :REG_ZIP,:REG_COUNTRY_CODE,:MAIL_ADDRESS_LINE1, :MAIL_ADDRESS_LINE2,:MAIL_CITY,:MAIL_STATE_CODE,:MAIL_ZIP,:MAIL_COUNTRY_CODE,:LEGAL_STRUCTURE_CODE,:STOCK_TICKER,:NAICS_CODE,:ASSET_SIZE,:WEB_ADDRESS,:REVENUE,:BRANCH_SITE_INDICATOR,:BRANCH_LOCATION_NAME,:WF_LEGAL_ENTITY_INDICATOR,:SYSTEM_CODE)" Dim recordcount As Int32 = Nothing If ds.Tables(0).Rows.Count > 0 Then recordcount = ds.Tables(0).Rows.Count End If Dim sci_id As String() = New String(recordcount - 1) {} Dim LEGAL_NAME As String() = New String(recordcount - 1) {} Dim LEGAL_BUS_TYPE_CODE As String() = New String(recordcount - 1) {} Dim ALIASID As String() = New String(recordcount - 1) {} Dim FULL_CUSTOMER_NAME As String() = New String(recordcount - 1) {} Dim TAX_ID As String() = New String(recordcount - 1) {} Dim SSN As String() = New String(recordcount - 1) {} Dim MAIN_PHONE As String() = New String(recordcount - 1) {} Dim REG_ADDRESS_LINE1 As String() = New String(recordcount - 1) {} Dim REG_ADDRESS_LINE2 As String() = New String(recordcount - 1) {} Dim REG_ADDRESS_LINE3 As String() = New String(recordcount - 1) {} Dim REG_CITY As String() = New String(recordcount - 1) {} Dim REG_STATE_CODE As String() = New String(recordcount - 1) {} Dim REG_PROVINCE As String() = New String(recordcount - 1) {} Dim REG_ZIP As String() = New String(recordcount - 1) {} Dim REG_COUNTRY_CODE As String() = New String(recordcount - 1) {} Dim MAIL_ADDRESS_LINE1 As String() = New String(recordcount - 1) {} Dim MAIL_ADDRESS_LINE2 As String() = New String(recordcount - 1) {} 'Dim MAIL_ADDRESS_LINE3 As String() = New String(recordcount - 1) {} Dim MAIL_CITY As String() = New String(recordcount - 1) {} Dim MAIL_STATE_CODE As String() = New String(recordcount - 1) {} 'Dim MAIL_PROVINCE As String() = New String(recordcount - 1) {} Dim MAIL_ZIP As String() = New String(recordcount - 1) {} Dim MAIL_COUNTRY_CODE As String() = New String(recordcount - 1) {} Dim LEGAL_STRUCTURE_CODE As String() = New String(recordcount - 1) {} Dim STOCK_TICKER As String() = New String(recordcount - 1) {} Dim NAICS_CODE As String() = New String(recordcount - 1) {} Dim ASSET_SIZE As String() = New String(recordcount - 1) {} Dim WEB_ADDRESS As String() = New String(recordcount - 1) {} Dim REVENUE As String() = New String(recordcount - 1) {} 'Dim REVENUE_AS_OF As String() = New String(recordcount - 1) {} Dim BRANCH_SITE_INDICATOR As String() = New String(recordcount - 1) {} Dim BRANCH_LOCATION_NAME As String() = New String(recordcount - 1) {} Dim WF_LEGAL_ENTITY_INDICATOR As String() = New String(recordcount - 1) {} Dim SYSTEM_CODE As String() = New String(recordcount - 1) {} Dim i As Integer = 0 For Each row As DataRow In ds.Tables(0).Rows sci_id(i) = row("ClientID").ToString().Replace("'", "''") LEGAL_NAME(i) = row("LEGAL_NAME").ToString().Replace("'", "''") LEGAL_BUS_TYPE_CODE(i) = row("LEGAL_BUS_TYPE_CODE").ToString().Replace("'", "''") ALIASID(i) = row("AliasID").ToString().Replace("'", "''") FULL_CUSTOMER_NAME(i) = row("FullCustomerName").ToString().Replace("'", "''") TAX_ID(i) = row("TaxId").ToString().Replace("'", "''") SSN(i) = row("SSN").ToString().Replace("'", "''") MAIN_PHONE(i) = row("MAIN_PHONE").ToString().Replace("'", "''") REG_ADDRESS_LINE1(i) = row("REG_ADDRESS_LINE1").ToString().Replace("'", "''") REG_ADDRESS_LINE2(i) = row("REG_ADDRESS_LINE2").ToString().Replace("'", "''") REG_ADDRESS_LINE3(i) = row("REG_ADDRESS_LINE3").ToString().Replace("'", "''") REG_CITY(i) = row("REG_CITY").ToString().Replace("'", "''") REG_STATE_CODE(i) = row("REG_STATE_CODE").ToString().Replace("'", "''") REG_PROVINCE(i) = row("REG_PROVINCE").ToString().Replace("'", "''") REG_ZIP(i) = row("REG_ZIP").ToString().Replace("'", "''") REG_COUNTRY_CODE(i) = row("REG_COUNTRY_CODE").ToString().Replace("'", "''") MAIL_ADDRESS_LINE1(i) = row("MAIL_ADDRESS_LINE1").ToString().Replace("'", "''") MAIL_ADDRESS_LINE2(i) = row("MAIL_ADDRESS_LINE2").ToString().Replace("'", "''") 'MAIL_ADDRESS_LINE3(i) = row("MAIL_ADDRESS_LINE3").ToString().Replace("'", "''") MAIL_CITY(i) = row("MAIL_CITY").ToString().Replace("'", "''") MAIL_STATE_CODE(i) = row("MAIL_STATE_CODE").ToString().Replace("'", "''") 'MAIL_PROVINCE(i) = row("MAIL_PROVINCE").ToString().Replace("'", "''") MAIL_ZIP(i) = row("MAIL_ZIP").ToString().Replace("'", "''") MAIL_COUNTRY_CODE(i) = row("MAIL_COUNTRY_CODE").ToString().Replace("'", "''") LEGAL_STRUCTURE_CODE(i) = row("LEGAL_STRUCTURE_CODE").ToString().Replace("'", "''") STOCK_TICKER(i) = row("StockTicker").ToString().Replace("'", "''") NAICS_CODE(i) = row("NAICS_CODE").ToString().Replace("'", "''") ASSET_SIZE(i) = row("ASSET_SIZE").ToString().Replace("'", "''") WEB_ADDRESS(i) = row("WEB_ADDRESS").ToString().Replace("'", "''") REVENUE(i) = row("REVENUE").ToString().Replace("'", "''") 'REVENUE_AS_OF(i) = String.Format("{0:dd-MMM-yyyy hh:mm:ss}", Convert.ToDateTime(row("REVENUE_AS_OF").ToString().Replace("'", "''"))) BRANCH_SITE_INDICATOR(i) = row("BRANCH_SITE_INDICATOR").ToString().Replace("'", "''") BRANCH_LOCATION_NAME(i) = row("BRANCH_LOCATION_NAME").ToString().Replace("'", "''") WF_LEGAL_ENTITY_INDICATOR(i) = "N" SYSTEM_CODE(i) = row("SYSTEMCODE").ToString().Replace("'", "''") i += 1 Next Dts.Variables("gRowProcessed").Value = i.ToString() Dim orcCommand As OracleCommand = Nothing Try OrCon = New OracleConnection(Dts.Variables("gDestinationConnection").Value.ToString()) 'Dim OrCon As New OleDbConnection(Dts.Variables("gDestinationConnection").Value.ToString()) OrCon.Open() orcCommand = OrCon.CreateCommand() orcCommand.CommandTimeout = 900 orcCommand.CommandText = strSQL orcCommand.CommandType = CommandType.Text orcCommand.BindByName = True orcCommand.ArrayBindCount = recordcount orcCommand.Parameters.Add(":sci_id", OracleDbType.Varchar2, sci_id, ParameterDirection.Input) orcCommand.Parameters.Add(":LEGAL_NAME", OracleDbType.Varchar2, LEGAL_NAME, ParameterDirection.Input) orcCommand.Parameters.Add(":LEGAL_BUS_TYPE_CODE", OracleDbType.Varchar2, LEGAL_BUS_TYPE_CODE, ParameterDirection.Input) orcCommand.Parameters.Add(":ALIASID", OracleDbType.Varchar2, ALIASID, ParameterDirection.Input) orcCommand.Parameters.Add(":FULL_CUSTOMER_NAME", OracleDbType.Varchar2, FULL_CUSTOMER_NAME, ParameterDirection.Input) orcCommand.Parameters.Add(":TAX_ID", OracleDbType.Varchar2, TAX_ID, ParameterDirection.Input) orcCommand.Parameters.Add(":SSN", OracleDbType.Varchar2, SSN, ParameterDirection.Input) orcCommand.Parameters.Add(":MAIN_PHONE", OracleDbType.Varchar2, MAIN_PHONE, ParameterDirection.Input) orcCommand.Parameters.Add(":REG_ADDRESS_LINE1", OracleDbType.Varchar2, REG_ADDRESS_LINE1, ParameterDirection.Input) orcCommand.Parameters.Add(":REG_ADDRESS_LINE2", OracleDbType.Varchar2, REG_ADDRESS_LINE2, ParameterDirection.Input) orcCommand.Parameters.Add(":REG_ADDRESS_LINE3", OracleDbType.Varchar2, REG_ADDRESS_LINE3, ParameterDirection.Input) orcCommand.Parameters.Add(":REG_CITY", OracleDbType.Varchar2, REG_CITY, ParameterDirection.Input) orcCommand.Parameters.Add(":REG_STATE_CODE", OracleDbType.Varchar2, REG_STATE_CODE, ParameterDirection.Input) orcCommand.Parameters.Add(":REG_PROVINCE", OracleDbType.Varchar2, REG_PROVINCE, ParameterDirection.Input) orcCommand.Parameters.Add(":REG_ZIP", OracleDbType.Varchar2, REG_ZIP, ParameterDirection.Input) orcCommand.Parameters.Add(":REG_COUNTRY_CODE", OracleDbType.Varchar2, REG_COUNTRY_CODE, ParameterDirection.Input) orcCommand.Parameters.Add(":MAIL_ADDRESS_LINE1", OracleDbType.Varchar2, MAIL_ADDRESS_LINE1, ParameterDirection.Input) orcCommand.Parameters.Add(":MAIL_ADDRESS_LINE2", OracleDbType.Varchar2, MAIL_ADDRESS_LINE2, ParameterDirection.Input) 'orcCommand.Parameters.Add(":MAIL_ADDRESS_LINE3", OracleDbType.Varchar2, MAIL_ADDRESS_LINE3, ParameterDirection.Input) orcCommand.Parameters.Add(":MAIL_CITY", OracleDbType.Varchar2, MAIL_CITY, ParameterDirection.Input) orcCommand.Parameters.Add(":MAIL_STATE_CODE", OracleDbType.Varchar2, MAIL_STATE_CODE, ParameterDirection.Input) 'orcCommand.Parameters.Add(":MAIL_PROVINCE", OracleDbType.Varchar2, MAIL_PROVINCE, ParameterDirection.Input) orcCommand.Parameters.Add(":MAIL_ZIP", OracleDbType.Varchar2, MAIL_ZIP, ParameterDirection.Input) orcCommand.Parameters.Add(":MAIL_COUNTRY_CODE", OracleDbType.Varchar2, MAIL_COUNTRY_CODE, ParameterDirection.Input) orcCommand.Parameters.Add(":LEGAL_STRUCTURE_CODE", OracleDbType.Varchar2, LEGAL_STRUCTURE_CODE, ParameterDirection.Input) orcCommand.Parameters.Add(":STOCK_TICKER", OracleDbType.Varchar2, STOCK_TICKER, ParameterDirection.Input) orcCommand.Parameters.Add(":NAICS_CODE", OracleDbType.Varchar2, NAICS_CODE, ParameterDirection.Input) orcCommand.Parameters.Add(":ASSET_SIZE", OracleDbType.Varchar2, ASSET_SIZE, ParameterDirection.Input) orcCommand.Parameters.Add(":WEB_ADDRESS", OracleDbType.Varchar2, WEB_ADDRESS, ParameterDirection.Input) orcCommand.Parameters.Add(":REVENUE", OracleDbType.Varchar2, REVENUE, ParameterDirection.Input) 'orcCommand.Parameters.Add(":REVENUE_AS_OF", OracleDbType.TimeStamp, REVENUE_AS_OF, ParameterDirection.Input) orcCommand.Parameters.Add(":BRANCH_SITE_INDICATOR", OracleDbType.Varchar2, BRANCH_SITE_INDICATOR, ParameterDirection.Input) orcCommand.Parameters.Add(":BRANCH_LOCATION_NAME", OracleDbType.Varchar2, BRANCH_LOCATION_NAME, ParameterDirection.Input) orcCommand.Parameters.Add(":WF_LEGAL_ENTITY_INDICATOR", OracleDbType.Varchar2, WF_LEGAL_ENTITY_INDICATOR, ParameterDirection.Input) orcCommand.Parameters.Add(":SYSTEM_CODE", OracleDbType.Varchar2, SYSTEM_CODE, ParameterDirection.Input) orcCommand.ExecuteNonQuery() Catch e As Exception 'Console.WriteLine(e.Message) 'Dts.Variables("ErrorMessage").Value = e.Message Dim vars As Variables Dts.VariableDispenser.LockOneForWrite("ErrorMessage", vars) vars(0).Value = "Error While inserting data into CV, Error Message::" & e.Message vars.Unlock() errorstr = True Finally ' Call Close when done reading. orcCommand.Dispose() If Not (OrCon.State = ConnectionState.Open Or OrCon.State = ConnectionState.Executing Or OrCon.State = ConnectionState.Connecting Or OrCon.State = ConnectionState.Fetching) Then OrCon.Close() End If End Try End If If errorstr = True Then Dts.TaskResult = Dts.Results.Failure Else Dts.TaskResult = Dts.Results.Success End If End Sub End Class This is the VB code which was written in a Script TASK. And i have 2 million records to insert.So while Running i was getting this Error message and this failing at orcCommand.ExecuteNonQuery(). ERROR message: ”Insufficient memory to continue the execution of the program” So how can i handle this.. i want to Commit 10k records every time so i guess this memory issue will be solved. Any ways i need help in Writing this Commit Statement inside my code.. How& where can write the commit statement in Vb.net..

You are doing this completely wrong. This is like using a hammer to cut a piece of plywood, and asking why it takes so long and why the edges are all splintered and broken. You need to use a Data Flow Task with an OLE DB Source and OLE DB Destination. You may need some transforms like Derived Column in the middle, but looking over your code, I doubt it. This is quite simple. You may want to try to use the Import/Export Wizard to define the transfer, then save those instructions as an SSIS package. You'll see it uses the Data Flow Task. Talk to me now on

1) Looks like, you are trying to implement the same .NET development in SSIS which might not be successful all the time. 2) Look for alternatives in SSIS for this, here it is data flow task. Happy to help! Please mark the post as an answer if I helped you to resolve the issue or vote up if I helped you.Thanks. Regards and good Wishes, Deepak.

why not you try with Todd's suggestion (Use DFT). Have you checked whether it's caused by there's no enough physical memory? OR is there any process consumes lots of memory during the deployment? Let us TRY this | My Blog :: http://quest4gen.blogspot.com/

i tried by Data Flow Task.. while using Composite driver as Source Connection.When i run the Package.. All the Rows are loading to Desination with NULL Values.(I have 20,000 records in source the Destination is loading 20,000 recods with null values..) Is this any BUG related to Composite Driver...Or How i can i fix this..

1) I am really not sure what this composite driver is , but if it is one of the sources that are used in the Data flow task, certainly this issue can be resolved. Happy to help! Thanks. Regards and good Wishes, Deepak.

I also have no idea what a Composite driver is. But you should attach a Data Viewer to the output of the Source component. That will allow you to see what form the data is in as it's extracted from the source system. If it's NULLs, then you know the problem is "upstream" with the driver/source database. If it isn't NULLs, then the problem is "downstream" with your other transformations and/or destination configuration. Talk to me now on