XML Parser Component For SSIS?

Jul 6, 2006

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

View 5 Replies


ADVERTISEMENT

SSIS Vs. Excel's CSV Parser

Jan 31, 2007

Hi All,

I€™ve been battling with a client who€™s supplied us with what they consider to be a legitimate CSV file. 1st off let me say that I understand that there€™s no CSV €œspecification€? per say, but here€™s the situation regardless.

The client has a test string in one of their fields that looks something like this:

He said "STOP" so, of course he stopped

The CSV best practice requires that you double quote, so a valid CSV filed would look like this:

He said ""STOP"" so, of course he stopped

Once this is placed into a comma delimited CSV it looks something like this

"Col1","Col2"
"He said ""STOP"" so, of course he stopped","value in col2"

---------------------

So the problem here is that the client saves the above as a CSV, opens it in Excel and say - Look, Excel deals with this just fine €“ why can€™t you handle it?

Trying to explain to the client that SSIS can€™t deal with a field that has embedded commas in it but that Excel can is quite honestly a little embarrassing (especially considering the const difference between the two).

It seem that having embedded quotes in the filed is fine, but that as soon as you have an embedded comma €“ SSIS can€™t handle it, yet Excel can.

---------------------

That said €“ I€™ve also read quite a few posts where people flame the original poster saying €¦ change your delimiter. That€™s all good and well when you€™re the one generating the CSV but when your client knows hinks they€™re generating a legit CSV (according the Excel and quite a few other CSV parsers) it€™s not a pleasant argument €“ especially when you know that asking them to make this change is going to take a few weeks of your project timeline.

I know I€™m not the 1st person to experience the problem, but I did want to see if I could get a straight answer as to why the Excel CSV Parser would dela with this situation but the SSIS parser would not.

Thanks
Warrick

View 8 Replies View Related

Help On Native SSIS Xml Parser With One-many Relationships In The Same Element

Jan 3, 2008

Hi All,
I receive data in the form of xml file and it has one to many relationships. Below I am giving you the sample node and how it should be parsed. Please suggest whether this is possible in SSIS using XML Task if not pls suggest a fast and efficient method.
<Element1>
<SubElement1>S1</SubElement1>
<SubElement2>S2</SubElement2>
..
..
<SubElement20>
<Child1>c1</Child1>
<Child2>c2</Child2>
</SubElement20>
<SubElement20>
<Child1>c3</Child1>
<Child2>c4</Child2>
</SubElement20>
..
..
<SubElement25>
<SubElement25Child1>s25_One</SubElement25Child1>
</SubElement25>
<SubElement25>
<SubElement25Child1>s25_Two</SubElement25Child1>
</SubElement25>
</Element1>
The above one is a sample xml. I should get 8 rows for the above element. There are 2 elements with "SubElement20" and it has 2 childs each. So there are 4 such nodes. The element "SubElement25" occured 2 times. Now we need to multiply these two counts.i.e. 4 * 2 = 8.
The relationship in the xml is one to many.
After parsing the xml I should get the rows in the following format and it should be one to one mapping only.
S1, S2, ....c1,... s25_One
S1, S2, ....c1,... s25_Two
S1, S2, ....c2,... s25_One
S1, S2, ....c2,... s25_Two
S1, S2, ....c3,... s25_One
S1, S2, ....c3,... s25_Two
S1, S2, ....c4,... s25_One
S1, S2, ....c4,... s25_Two
The options are as follows
1) Writing a custom source component to parse this xml and produces the rows in the specified way.
2) First write a XSLT or Style Sheet to break the above one to many relationships to one to one relationships and create an xml file and then use native SSIS xml parser.
3) Using the Script Task as the source component and do the parsing. But I think it would be difficult to handle and maintain.

If any other suggessions please share. Thanks in advance.
Regards
Venkat.

View 13 Replies View Related

Use Of A SSIS Variable Of Type “Object� Inside Script Component And Task Component

Mar 16, 2007

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!

View 3 Replies View Related

A Custom Component For Use As A VIEW In SSIS- Is It Possible To Create One MERGE Like Component With More Than 2 Inputs

Aug 13, 2007

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)

View 4 Replies View Related

The Component Metadata For Component DataReader Source (1113) Could Not Be Upgraded To The Newer Version Of The Component.

Oct 26, 2007

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.





View 7 Replies View Related

The Component Metadata For Component DataReader Source Could Not Be Upgraded To The Newer Version Of The Component.

Jan 23, 2007

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

 

 

View 4 Replies View Related

If-then-else-component In SSIS

Nov 23, 2007

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

View 4 Replies View Related

SSIS Script Component

Feb 6, 2008

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

View 2 Replies View Related

SSIS Script Component

May 16, 2006

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

View 16 Replies View Related

SSIS Script Component

Feb 6, 2008

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

View 1 Replies View Related

SSIS SCRIPT COMPONENT

Oct 11, 2006

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 !

View 2 Replies View Related

SSIS, Script Component, How To Get ErrorColumnName

Feb 26, 2008



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.

View 3 Replies View Related

Failing A SSIS Script Component

Mar 7, 2006

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

View 3 Replies View Related

4 Errors When Using SSIS Scripting Component

Nov 7, 2006



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

View 4 Replies View Related

Xml Ssis Data Flow Component?

Jul 2, 2007

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?

View 3 Replies View Related

SSIS Script Component Error

Mar 28, 2008



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

View 16 Replies View Related

SSIS (2005) Script Component API

Jul 13, 2007

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

View 4 Replies View Related

SSIS: First Time Script Component

Nov 29, 2006

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.

View 6 Replies View Related

Any Idea To Implement SCD Without SCD Component Of The SSIS

Dec 21, 2007

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

View 8 Replies View Related

SSIS ODBC Source Component

Apr 24, 2006

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

View 9 Replies View Related

SSIS Script Component Error

Apr 11, 2008

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

View 4 Replies View Related

SSIS Script Component To Concatenate Field

Jun 28, 2007

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

View 1 Replies View Related

Can I Use Db Mail Component Of SSIS On Migrated DTS Package ?

Dec 10, 2007

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

View 1 Replies View Related

Can Custom Properties In A SSIS Component Be Disabled

Feb 26, 2008

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

View 5 Replies View Related

SSIS Buffer Problem - Lookup Component

Aug 15, 2006

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.





View 13 Replies View Related

Comparing Current Row To Next Row Using SSIS Script Component

Jun 19, 2007

I'm trying to determine proper end date for measuring the effectiveness of my client's email campaigns. For each email address that received the campaign, I would like to determine a measurement end date. This date should be the lesser of 7 days from the Sent date or the date of the last email offer. This way, I prevent double-counting the revenue associated with each email campaign. Here's an example of what I'd like to see for each unique Sent Date and Email Address:



Sent Date Email Address Measurement End Date <<<< This is what I'm trying to derive with a script

2/22/2007 test@test.com 2/29/2007 <<<< 7 days from Sent Date

2/18/2007 test@test.com 2/22/2007 <<<< 4 days from Sent date due to the 2/22/2007 email

1/20/2007 test@test.com 1/27/2007 <<<< 7 days from Sent Date

etc.



If the input data stream is sorted by Email Address and descending Sent Date, then I need to be able to compare the Sent Date on the current record to the Sent Date on the next record (until the Email Address changes and then the process starts over).



I'm open to any solutions as this has really stumped me for awhile.

View 1 Replies View Related

SSIS Script Component Date Check

Oct 24, 2007



All,

I am facing below problems in migrating my DTS packages to SSIS.
Could any one answer this ?

1.How to do Tranformations based on condition.Earlier in DTS we are using Activex.
For ex:

if isDate(DTSSource("Due Date")) then
DTSDestination("Due_Date") = DTSSource("Due Date")
end if

I am using script component with below logic:


If Not Row.Due_Date_IsNull Then



If IsDate(Row.Due_Date) Then


Row.Due_Date= Row.EntryDate

End If

End If

Is that correct ?
2.My flat file is having 37 columns.But I am able to see only 21 columns;First 20 columns are showing correctly
and remaining data is showing in 21st column.delimiter is semicolon; Any guess ?

Thanks,
Ravi

View 1 Replies View Related

How To Declare Failure For An SSIS Script Component

Aug 14, 2007

I have a script component in a data flow task that acts as a destination source. I need to know how to access the DTS Task Result object to declare failure in the task if something goes bad. I'm not sure how to access this object via the object model. Can someont send me a sample of how to do this?

View 3 Replies View Related

SSIS Custom Component/task Examples

Feb 8, 2006

Hi,

Near the end of 2005 Microsoft made available some sample C# apps that implemented custom SSIS tasks and components. I think Doug Ladenshlager may have had a heavy hand in building them.

Does anyone know where they are? I can't darned well find them!

-Jamie

View 1 Replies View Related

Resources For SSIS Custom Component Development

Dec 13, 2007

Does anyone here have a favorite site or set of sites with resources on SSIS custom component (tasks, transforms, log providers, etc.) development? I've been searching around to try to find this on my own, but so far I've had little luck.

Darren - I've noticed that you always seem to have the most insightful responses to questions related to SSIS .NET development (such as your recent response to evaluating expressions in a custom component) so I am particularly interested in anything that you have to share.

Thanks in advance, everyone!

View 6 Replies View Related

Connectiong To SQLServer In Script Component (SSIS)

Jul 22, 2007

Hi

I'm very new to SSIS but can't find the answer to this anywhere.

I'm simply trying to connect to a SQLServer database from within a script component.

I am doing this using a [Native OLE DBSQL Native Client] connection manager which i've added to the script component and called it SQLconnection.

this is the code:

Dim SQLconnMgr As IDTSConnectionManager90 = Me.Connections.SQLconnection

Dim SQLConn As sqlClient.SQLConnection = CType(SQLconnMgr .AcquireConnection(Nothing), sqlClient.sqlConnection)

The first line is fine...but I get this error in the second line:

[ Unable to cast COM object of type 'System.__ComObject' to class type 'System.Data.sqlClient.sqlConnection'. Instances of types that represent COM components cannot be cast to types that do not represent COM components; however they can be cast to interfaces as long as the underlying COM component supports QueryInterface calls for the IID of the interface. ]

Can anyone help? I'm totally stuck

thanks!

andy

View 10 Replies View Related

Code Sample For SSIS Script Component

May 15, 2007

Greetings,



I have been developing VBA apps in Access and Excel for sometime and am fairly proficient in VBA. Now we are moving all of our data to SQL Server 2005. I am in need of learning how to write code for the Script Component of a data flow task. And so I have a couple of questions.



First, are there any books you recommend for learning ???? (I'm not even sure what I need to learn: .NET? ADO.NET?)



And as a follow-up, any good websites that provide good reference documentation?



And my second question is more specific to my current problem. If I had a bit of code to get me started, I'm sure I could scream all the way to the bottom of the hill.



Data source is coming from a sort task where the data is sorted by STATUS and then MOD_DATE and the AUDIT_ID.
I need to read each row and compare it to the next row. If STATUS is the same, discard the second row.
When STATUS is different, send the first row to the output (to be used by the next task in the data flow).
Using the "different" row from step 3, go to step 2.

I know how to write if statements, case statements, for/next statements. I'm just not understanding how to read the rows in and then send them back out. I've been searching for some sample code but everything I find tends to be solving much bigger issues.



Any help you can provide would be much appreciated.



Rob

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved