how to evaluate working day through script component in SSIS
I have a data source. I need to verify if the date belongs to working day, if it does belongs to holiday or weekend ,then I need to send that row to the out put table. I think the only way I can verify this working day issue through script component. Can any one give me some idea/thoughts?
You all have a wonderful weekend.
Thanks
Is there a way to evaluate an expression (like the derived column component) in a custom component? If so where should I look first? Is there an example?
An extremely simple sample is to put in an expression and evaluate one column and then add that to another column to create a new column. i.e. newcolumn = column1 + column2.
I realize that the derived column allows me to do this but I'm trying to figure out if it is possible to do this in a custom component without having to build my own expression evaluator.
Thanks!
-Thames
I have few tables that I need to export to an MS Access database each day to send off to the customer. In these tables I have a few columns that I need to strip all the HTML out of before they are put into Access since Access does not display the formatting correctly. Some of the fields are varchar and some are text, but all of the fields that are varchar are over 255 in size, so this forces me to use Access' Memo type for both.
In SQL 2000 I used the ActiveX Script to modify these fields, stripping out the HTML, and it gave me no complaints. In SQL 2005, I am adding a Script Component into the Data Flow before the Destination and adding the script in there. So far so good. The problem arises when I try to retrieve and update the data in these fields because they are treated as BLOB data. I believe what I need to do is to retrieve the Byte array from the row using the GetBlobData(), then convert that to a string, strip out the HTML, convert back to a Byte array, then clear the original value using the ResetBlobData(), and then update the field using the AddBlobData. I think?
I am not even sure that my code below is converting the byte array into a string correctly. If I throw a Messagebox in there to see what it has for the string, it just gives me the first character in the field. But even ignoring that, the package will not execute and I am stumped.
The error I now get is:
Array cannot be null.
Parameter name: bytes
''-----------------------------------------------------
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Dim b As Byte()
If (Row.ProjectDescription.Length > 0) And (Not
(Row.ProjectDescription_IsNull)) Then
b = Row.ProjectDescription.GetBlobData(0,
CInt(Row.ProjectDescription.Length))
End If
Dim str As String
Dim enc As New System.Text.ASCIIEncoding()
str = enc.GetString(b)
str = RemoveHTML(str)
b = enc.GetBytes(str)
Row.ProjectDescription.ResetBlobData()
If b.Length > 0 Then
Row.ProjectDescription.AddBlobData(b)
End If
End Sub
''-----------------------------------------------------
I have a blob which is essentially an email and I need to replace all carriage returns in the blob with a special character ("€°"). But it does not work. Please advice.
Dim intBlobLen As Integer = Convert.ToInt32(Row.body.Length)
Dim intFinish As Integer = intBlobLen - 1
Dim bytBlob(intFinish) As Byte
bytBlob = Row.body.GetBlobData(0, intFinish)
Dim strBlob As String = System.Text.Encoding.Unicode.GetString(bytBlob)
strBlob = Replace (strBlob,"/n","€°")
If strBlob.Length > 0 Then
Row.body.ResetBlobData()
Row.body.AddBlobData(System.Text.Encoding.Unicode.GetBytes(strBlob))
End If
Please help. Thanks in advance
I have a script component which loads a file which is in a custom format. The script component is inside a For Each Loop Container and it uses a flat file connection manager. The loop sets the connection string for the connection manager.
The problem I'm having is that the connection string needs to be set to something every time that I start the package but I don't know ahead of time what file there will be, so I get a System.IO.FileNotFoundException error on the script component. If I manually set the variable for the connection string and point it to a file that exists, then the package runs fine but at the end of the package the connection string is set to the last file loaded and this file will no longer exist the next time the package runs.
I hope this makes sense...
In a Data Flow, I have the necessity to use a SSIS variable of type €œObject€? inside Script Component and assign to it the content of 'n' variables of string type.
On exiting from the script the variable of type object should contain something like in the following lines:
AAAAAAAAAAAAAAAAAAAAAAAAAAAAA
BBBBBBBBBBBBBBBBBBBBBBBBBBBBB
CCCCCCCCCCCCCCCCCCCCCCCCCCCCC
DDDDDDDDDDDDDDDDDDDDDDDDDDDDD
€¦€¦€¦€¦€¦€¦€¦.
€¦€¦€¦€¦€¦€¦€¦.
On exiting from the data flow I will use the variable of type Object in a Script Task, by reading each element in a cyclic fashion.
Is there anyone who have experienced something like this? Could anyone provide any example of that?
Thanks in advance!
Hi all
I'm into a project which uses a lot of views for joining 2 or more tables. Using the MERGE component in SSIS will be a huge effort coz it only has 2 inputs and I gotta SORT the input too.
Isnt it possible to have a VIEW like component that joins more than 2 tables and DOESNT need sorting??
(I've thought about creating views in database engine but it breaks my data floe in SSIS and is'nt a practical solution)
I have an SCD that contains a historical output path. If I throw dataviewer in the flow before the SCD, I can see that it should trigger a trip down that lane, but its not. In the advanced editor, all looks good. Anything I can check. BTW, the datatype of the fields that should cause the SCD are datetime.
thanks in advance
I have few tables that I need to export to an MS Access database each day to send off to the customer. In these tables I have a few columns that I need to strip all the HTML out of before they are put into Access since Access does not display the formatting correctly. Some of the fields are varchar and some are text, but all of the fields that are varchar are over 255 in size, so this forces me to use Access' Memo type for both.
In SQL 2000 I used the ActiveX Script to modify these fields, stripping out the HTML, and it gave me no complaints. In SQL 2005, I am adding a Script Component into the Data Flow before the Destination and adding the script in there. So far so good. The problem arises when I try to retrieve and update the data in these fields because they are treated as BLOB data. I believe what I need to do is to retrieve the Byte array from the row using the GetBlobData(), then convert that to a string, strip out the HTML, convert back to a Byte array, then clear the original value using the ResetBlobData(), and then update the field using the AddBlobData. I think?
I am not even sure that my code below is converting the byte array into a string correctly. If I throw a Messagebox in there to see what it has for the string, it just gives me the first character in the field. But even ignoring that, the package will not execute and I am stumped.
The error I now get is:
Array cannot be null.
Parameter name: bytes
Code Block
''-----------------------------------------------------
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Dim b As Byte()
If (Row.ProjectDescription.Length > 0) And (Not (Row.ProjectDescription_IsNull)) Then
b = Row.ProjectDescription.GetBlobData(0,CInt(Row.ProjectDescription.Length))
End If
Dim str As String
Dim enc As New System.Text.ASCIIEncoding()
str = enc.GetString(b)
str = RemoveHTML(str)
b = enc.GetBytes(str)
Row.ProjectDescription.ResetBlobData()
If b.Length > 0 Then
Row.ProjectDescription.AddBlobData(b)
End If
End Sub
''-----------------------------------------------------
In the code behind the Script Transformation component, neither the MsgBox nor any of the breakpoints seem to work. Am I missing to do something in order for these to get executed?
Thanks.
Andy.
I have a timestamp column and a username column with default values of getdate and system user in my table and they are defined as not null.
even though i donot use these columns in scd type 2 in wizard
if the columns are not null the scd deosnot work and If the columns are defined null they work
Can anyone please explain or help me with what might be the problem associated with this
Hello,
I have a package that has a data lfow task. this task imports data from a db2 database (using the IBM Ole DB provider fro db2) and adds it to sql server database table. This package was created on the server. then though version control (using TFS source control) I check out the package on my local machine. and when I open the package I get the foll 3 errors.
Error 1 Validation error. Import Account Num from BMGP_BDR: DTS.Pipeline: The component metadata for "component "DataReader Source" (1113)" could not be upgraded to the newer version of the component. The PerformUpgrade method failed.
Error 2 Error loading BMAG Download Xref Tables - bmag.dtsx: Microsoft.SqlServer.Dts.Pipeline.ComponentVersionMismatchException: The version of component "DataReader Source" (1113) is not compatible with this version of the DataFlow. [[The version or pipeline version or both for the specified component is higher than the current version. This package was probably created on a new version of DTS or the component than is installed on the current PC.]] at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostCheckAndPerformUpgrade(IDTSManagedComponentWrapper90 wrapper, Int32 lPipelineVersion)
Error 3 Error loading BMAG Download Xref Tables - bmag.dtsx: The component metadata for "component "DataReader Source" (1113)" could not be upgraded to the newer version of the component. The PerformUpgrade method failed.
Please advice.
Thank you.
Hi,
I have a package which reads an Access file from a folder. My connection manager to this file is .NET providers for OledbMicrosoft Jet 4.0 OLE DB Provider.
Package works from my computer. But when I execute it on the server as a SQL Agent job, I get
The component metadata for "component "DataReader Source" (1) could not be upgraded to the newer version of the component. The PerformUpgrade method failed.
I copied the mdb file to a folder on the server which my packages have no problem reading data from.
My packages run under the same domain account as defined in proxies.
Appreciate a help.
Gulden
Hi all,
i am not very experienced with the SSIS. I am just wondering if there is something like a "if-then-else"-componente like the foreach-component in SSIS.
I want to delete the values of all tables in one database. So I took a foreach-component and selected the smo-enumeration with all tables. I store the tablename in a variable and execute a sql-task with "delete table.." with the variable tablename as parameter. Now I want to delete all except one certain table. I would like to add a selection where the variable tablename is checked. If the tablename is this certain table, I don't want to execute the sql-command, else I want to excecute the delete-command.
Are there any suggestions?
Thank you very much
Joachim
I have just migrated a DTS 2000 package
as an SSIS package.
one of the features that failed to migrate, was a transformation
that , selected 2 colums of data with a stored procedure,
file name, and full path of filename,
Then the file name only was written to a txt file,
Then there was an ActiveX transformation task that used the other
column (full file path) to copy said file to another location (specified as a global str variable eg \127.0.0.1directory..etc)
Now my question is this, with SSIS script task
can i save the path name (2nd column) to a variable and then
using this variable copy the file to another location (global str variable) ?
Is there a CopyFile function like there is in ActiveX ?
And can i add this script task along with the DATA FLOW ?
because if i add it outside the DF , it will only (im assuming) copy the last line (path) into the variable...
Dear all,
Such a pain, I know. Yesterday I did a couple of questions regarding transformation rows and very kindly I obtained answer (Thanks Jamie and Michael)
But now I face another stupid issue and is how to map source with destination columns inside Script Component Task.
On my Flat File Source I€™ve got defined thirteen columms (from Column0 till Column13); that€™s fine. And then, I€™ve got a sql table as destination with another names, of course€¦
Inputs and Outputs option from Script Component Editor leaf has been commited both (Input 0 and Output 0) but I wonder how the hell I€™m refer to them here:
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
** snippet of old code sql2 to migrate€¦
'If DTSSource("Col010") = "N" Then
' DTSDestination("ImpBase") = -1 * CDbl(DTSSource("Col011") / 100)
'Else
' DTSDestination("ImpBase") = CDbl(DTSSource("Col011") / 100)
'End I
.Net Script
If Row.Column10 = "N" Then
¿??????????????
End If
End Sub
Thanks a lot for your comments and thoughts,
Enric
I have just migrated a DTS 2000 package
as an SSIS package.
one of the features that failed to migrate, was a transformation
that , selected 2 colums of data with a stored procedure,
file name, and full path of filename,
Then the file name only was written to a txt file,
Then there was an ActiveX transformation task that used the other
column (full file path) to copy said file to another location (specified as a global str variable eg \127.0.0.1directory..etc)
Now my question is this, with SSIS script task
can i save the path name (2nd column) to a variable and then
using this variable copy the file to another location (global str variable) ?
Is there a CopyFilefunction like there is in ActiveX ?
And can i add this script task along with the DATA FLOW ?
because if i add it outside the DF , it will only (im assuming) copy the last line (path) into the variable...
Informatica has an XML parser component that allows me to read an xml file from a data source (Oracle Clob attribute in table in this case), parse it out in our mapping, and then transform the parsed date.
Does anyone know if SSIS has similar functionality?
Flow:
DataSource --> XML Parser --> Expression Component (Transform) --> DataTarget
Thanks
Scott
Hello,
My SSIS design: Source OLE DB -> Script Component -> Destination OLE DB
I have a script component that reads and proceeds each row in input. But I have no rows in output. How can you explain that ? with the viewer, I see the rows in input but in output, I have nothing after the script.
The script function: read the value of the ROW.column and flag like this :
ROW.columnout = TRUE (columnout is added in output columns)
What should I define at the component to retrieve the rows after the script component ?
Thanks !
Hi All,
I am creating an SSIS Package where I need to get the errorcolumn name in a script component to be inserted into a database table.
Even when I loop through ColumnNames in ComponentMetaData.InputCollection(0).InputColumnCollection and match their lineageId with the errorcolumn, I dont get a match. Can anybody please help me in this.
Thanks in advance.
Hi
When there is an error in one of the rows a script component (in a child package) is processing I want to fail the child package and the parent package and not continue processing any rows.
How do I do this?
I have every thing in the script component in a try catch statment. This is the catch block
Catch ex As Exception
ErrorMsg = ex.Message + " " + RecordMsg + " Error on Column : " + ColumnMsg
ComponentMetaData.FireError(0, ex.Source, ErrorMsg, String.Empty, 0, False)
End Try
Also I have the FailPackageOnFailure and FailParentOnFailure properties set to true and the max errors value set to 0.
Any suggestions?
Thanks
Hi guys, I got these errors when writing a scripting component. Anyone encounteer these errors before?
Warning 1 The dependency 'EnvDTE' could not be found.
Warning 2 The dependency 'Microsoft.SqlServer.VSAHosting' could not be found.
Warning 3 The dependency 'Microsoft.SqlServer.DtsMsg' could not be found.
Warning 4 The dependency 'Microsoft.SqlServer.VSAHostingDT' could not be found.
-Daren
There is a table with a column that contains Xml documents. For each record from my Data Flow Source, I want to pass in the Xml document and the node to interrogate, and return the value contained in the node. Like the Crm component, this is probably one I will have to write from scratch in C#, but I would like to avoid having to create the custom component if it already exists in the public arena.
Does anyone know of any Xml Ssis Data Flow Components that are downloadable for free?
I cannot open my script component in my SSIS package. Not sure if this is the cause, but I originally designed the package in BIDS and now have loaded Visual Studio 2005. Here are all the error messages:
===================================
Cannot show Visual Studio for Applications editor. (Microsoft Visual Studio)
------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.762&EvtSrc=Microsoft.DataTransformationServices.DataFlowUI.SR&EvtID=CouldNotShowVsaIDE&LinkId=20476
===================================
Engine returned Unknown Error (Microsoft.VisualBasic.Vsa)
------------------------------
Program Location:
at Microsoft.VisualBasic.Vsa.VsaEngine.LoadSourceState(IVsaPersistSite Site)
at Microsoft.SqlServer.VSAHosting.DesignTime.LoadEngineSource(String engineMoniker, String project)
at Microsoft.SqlServer.Dts.Pipeline.ScriptDesignTime.CreateDesignTimeEngine(String projectName, Boolean loadSource, ICodeGenerator codeGenerator)
at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ShowIDE()
at Microsoft.DataTransformationServices.DataFlowUI.ScriptUI.propPage_DesignScript(Object sender, EventArgs args)
===================================
A project with the name 'ScriptComponent_96f4738414c440d0b240beb6399cef36' already exists.
------------------------------
Program Location:
at Microsoft.Vsa.IVsaEngine.LoadSourceState(IVsaPersistSite site)
at Microsoft.VisualBasic.Vsa.VsaEngine.LoadSourceState(IVsaPersistSite Site)
Any help in solving this would be appreciated.
Thanks
David
Hi
I have managed to programmatically create data flows and components in an SSIS (2005) project (*.dtsx) by using VS2005 VB.NET but I have hit a road block in terms of progarmmatically inserting pre-tested VB.NET code into a newly created Data Flow - Script Component source code block
Can someone give me a little bit of direction on this? Is it possible? Direction to some example code would be great!
Thanks in advance
Wayneiz
Hello,
I have a flat file that contains detail in each record as indicated below:
HEADER_ID, ATTRIB_A(1..10), ATTRIB_B(1..10), ATTRIB_C(1..10), etc.
The index of the attribute relates it to other attributes with the same index. It needs to look like this in the detail table:
HEADER_ID, ATTRIB_A1, ATTRIB_B1, ATTRIB_C1
HEADER_ID, ATTRIB_A2, ATTRIB_B2, ATTRIB_C2
I need to pivot these attributes into a detail table that relates back to the header information. Because of the number of these, I don't want to use the UNPIVOT Task because there are so many. I was hoping to move the complexity to a Script Component where I could read one line and transform it to a normalized state.
Can someone point me in the right direction?
Thanks.
Hi All,
I would like to say thank you in advance for all your ideas. Here is my case i want to implement slowly changing dimension, i know that i can use SCD component of the SSIS, but because of performance issue i am thinking to use something else that can subtitute the SCD component, i want some idea from you guys if anyone has implemented before without Slowly Changing Dimension component.
If not, do you have any comment/suggestion to use the SCD component i mean if the worest comes and i use it, what draw backs does it has, for example interms of data size, performance. Note that i use Dedicated Server for the ETL in Production.
Thank you
SamiDC
I need to create an ODBC source script component that outputs into SQL Server. When I debug I get the following error message:
Error at Data Flow Task [Script Component [1]]: System.InvalidCastException: Unable to cast object of type 'System.Data.Odbc.OdbcConnection' to type 'System.Data.SqlClient.SqlConnection'. at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.HandleUserException(Exception e) at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.AcquireConnections(Object transaction) at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnections(IDTSManagedComponentWrapper90 wrapper, Object transaction)Error at Data Flow Task [DTS.Pipeline]: component "Script Component" (1) failed validation and returned error code 0x80004002.
Here the problem code:
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Math
Imports System.IO
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Public Class ScriptMain
Inherits UserComponent
Dim connMgr As IDTSConnectionManager90
Dim sqlConn As SqlConnection
Dim sqlReader As SqlDataReader
Public Overrides Sub AcquireConnections(ByVal Transaction As Object)
connMgr = Me.Connections.PP
sqlConn = CType(connMgr.AcquireConnection(Nothing), SqlConnection)
End Sub
Public Overrides Sub PreExecute()
Dim cmd As New SqlCommand("SELECT Solution_Code_From, Solution_Code_To FROM Solconv", sqlConn)
sqlReader = cmd.ExecuteReader
End Sub
Public Overrides Sub CreateNewOutputRows()
Do While sqlReader.Read
With SolutionOutputBuffer
.AddRow()
.solcodefr = sqlReader.GetString(1)
.solcodeto = sqlReader.GetString(0)
End With
Loop
End Sub
Public Overrides Sub PostExecute()
sqlReader.Close()
End Sub
Public Overrides Sub ReleaseConnections()
connMgr.ReleaseConnection(sqlConn)
End Sub
End Class
Would appreciate any advice.
Thanks in advance,
Pozzled
Hi,
In one of the SSIS package, I have a Script Component with ReadWrite variables --> TotalRecordCount, JobName, CycleCode
But suddenly in our Prod server from where the SSIS package is executed against our Prod DB server (SQL Server 2005 SP2), it failed. The error message was
Error: 2008-04-11 07:31:20.61
Code: 0xC0047062
Source: DFT PolicyTerm Load SCR Balancing [839]
Description: System.Runtime.InteropServices.COMException (0xC001404D): Exception from HRESULT: 0xC001404D
at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.HandleUserException(Exception e)
at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.PostExecute()
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPostExecute(IDTSManagedComponentWrapper90 wrapper)
End Error
I am attaching the code below here...
Imports System
Imports System.Data
Imports System.Data.OleDb
Imports System.Collections
Imports System.Text
Imports System.Windows.Forms
Imports System.Environment
Public Class ScriptMain
Inherits UserComponent
Public rowCount As Integer
Public Connections As New Connections(Me)
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
rowCount += 1
End Sub
Public Overrides Sub PostExecute()
'add to global variables
Variables.TotalRecordCount += rowCount
Dim stb As StringBuilder
stb = New StringBuilder
stb.Append(String.Format("<BalanceData ProgramName=""{0}"" JobName=""{1}"" CycleCode=""{2}"" >", "PST020", Variables.JobName, Variables.CycleCode))
stb.Append(String.Format("<BalanceLog BalanceItemId=""10040"" BalanceNumber=""{0}"" />", rowCount))
stb.Append("</BalanceData>")
With New OleDbCommand("dbo.uspInsertBalanceLog")
.CommandType = CommandType.StoredProcedure
'Define the common parameters
.Parameters.Add("@balanceLog", OleDbType.VarChar, 4000).Value = stb.ToString()
'Define and open the database connection
.Connection = New OleDbConnection(Connections.Prostar.ConnectionString)
.Connection.Open()
Try
.ExecuteNonQuery() 'Execute the procedure
Finally 'Always finalize expensive objects
.Connection.Close()
.Connection.Dispose()
End Try
End With
MyBase.PostExecute()
End Sub
End Class
Can tell me what the issue could be...
Is there a Data Flow Transromation in SSIS to take multiple columns from an excel sheet and concatinate them into one filed.
Ex:
'Strt-Address', 'City', 'State' into 'HomeAddress'.
I could dump them into a temp table and concatinate the fields and insert that into the destination but that seems like a waiste of using SSIS.
Thanks
Hi
I had a DTS package on sql2000 which i migrated succesfully to Sql2005 and im able to open the package and execute the package.Now i want to add a new database mail component on this package to send emails to recepients.In short i dont want to use SQL Mail component of Sql2000 which required outlook components,instead i want to use the new features of SSIS to my package which was designed on sql2000.
Is it possible to use the SSIS new features to be incorporated on my old DTS package?
Thanks in advance
Regards
Arvind
Is there a way of disabling a custom property in a component so that during design-time the property is grayed out? I looked around the properties of IDTSCustomProperty90 and nothing sticks out.
Thanks
Mike
Hi,
I am facing a problem with Lookup component in SSIS. I need to lookup from a transaction table for getting some info, But when im trying to implement the same, the Pre-Execute step itself got failed saying like,
€œ[DTS.Pipeline] Information: The buffer manager failed a memory allocation call for 524264 bytes, but was unable to swap out any buffers to relieve memory pressure. 9467 buffers were considered and 5956 were locked. Either not enough memory is available to the pipeline because not enough are installed, other processes were using it, or too many buffers are locked.
[Tracer [19717]] Error: A buffer could not be locked. The system is out of memory or the buffer manager has reached its quota.
[DTS.Pipeline] Error: component "Tracer" (19717) failed the pre-execute phase and returned error code 0xC020204B.€?
Component Tracer is the Look up. Tracer is having around 6.5 mil records. Is there any way to allocate more buffers thru buffer manager? Or is there any alternative to solve this problem? FYI, the hard disk free space is more than 250 GB.
Thanks in advance.