One kind of transformation

original data type is string ,format like MM/dd/YYYY,also there exists null or "" value

I need to convert them into datetime,format like YYYY--MM-dd

any idea about this?

thanks in advance

Using a derived column and some simple string manipulation you could cut the string by the /'s and build it the way you want.|||

however, converting to datestamp failed

|||SSIS is rather rigid in it's conversion from string to DT.
See http://sqljunkies.com/WebLog/knight_reign/archive/2005/07/14/16073.aspx for examples on strings that will work.|||

yes, it's not difficult to convert from string to DT

but i still could not figure out how to determine if the current value is null or not

just like that,

[value]!=""?(DT_Date)(): ....

how to define the otherwise portion? or other better method?

thanks

|||Use the isnull() function.|||

yes, i used

(trim([value])!=""&&isnul[value]==false)?(DT_Date)[value]: ....

but, how to deal with null or "" to convert to DT?

thanks

|||

Are you asking us what datetime value you should pass out if the incoming value is null? How do we know - that depends on your business rules.

I guess I'm misunderstanding.

-jamie

|||

Sorry for my explains not clear

I want to know how to let it work if value is null

many thanks

|||

(TRIM([value]) != "" && ISNULL([value]) == FALSE ) ? (DT_DBDATE)(SUBSTRING([value],FINDSTRING([value],"/",2) + 1,4) + "-" + SUBSTRING([value],1,2) + "-" + SUBSTRING([value],FINDSTRING([value],"/",1) + 1,2)) : NULL(DT_DBDATE)

this can work now, thanks

sql

One Data Flow Task and Multiple Data Flows

I have a data flow task which has around 5 data flows (like the 2nd diagram shown here). These 5 simple flows with just a row count transformation in between. Now, I want to fail the entire task immediately even if one of the data flows failed. Right now if one flow fails the remaining flows fails after a long time, not immediately. How can I make it fails immediately.

The other I would like to do is Can I place these 5 data flows in a transaction, so that if one data flow fails, others data flows also roll backs? ( I assume its not possible)

Thanks

Hi Karunakaran,

I think the best way to accomplish what you're describing, assuming your destination is a database, would be to use a transansaction. You can cause your entire data flow to be placed in a single distributed transaction by setting the "TransactionOption" property on your Data Flow Task to "Required". This will cause the data flow to attempt to enlist each of the connections it uses into a single transaction.

-David

OLE-Db connection in Transformation Script Component

Hello,

Using the following documentation as a guide:

http://msdn2.microsoft.com/zh-cn/library/aa337080.aspx

I instantiated a new script component into an existing Data Flow in my SSIS project.

In the Script Transformation Editor, under the Connection Managers section, I associated the name dbConnManager to an already existing Connection Manager in the project.

My Connection Manager is of the type oOLEDB.

I then opened up the script designer and added the following lines of code where it said "Add your code here"

Dim myConnManager As IDTSConnectionManager90 = _

Me.Connections.ECFconnection

Dim dbConn As OleDb.OleDbConnection = _

CType(myConnManager.AcquireConnection(Nothing), OleDb.OleDbConnection)

When I test run the project I get the following error and the new script component is red:

Unable to cast COM object of type 'System.__ComObject' to class type 'System.Data.OleDb.OleDbConnection'. 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.

I know the database connection works since I am using it in a component that executes before this new script component.

I am stuck...Any suggestions?

Unfortunately you cannot use a Connection Manager that returns a native type in managed code. This includes OLE DB and Excel.

This limitation is noted in BOL in
http://msdn2.microsoft.com/en-us/library/ms136018.aspx
and
http://msdn2.microsoft.com/en-us/library/aa337080.aspx

with this suggestion:

If you need to call the AcquireConnection method of a connection manager that returns an unmanaged object, use an ADO.NET connection manager. When you configure the ADO.NET connection manager to use an OLE DB provider, it connects by using the .NET Framework Data Provider for OLE DB. In this case, the AcquireConnection method returns a System.Data.OleDb.OleDbConnection instead of an unmanaged object. To configure an ADO.NET connection manager for use with an Excel data source, select the Microsoft OLE DB Provider for Jet, specify an Excel workbook, and then enter Excel 8.0 (for Excel 97 and later) as the value of Extended Properties on the All page of the Connection Manager dialog box.

-Doug

|||

Thank you. That was the nudge in the right direction that I needed.

Greg.

OLE-Db connection in Transformation Script Component

Hello,

Using the following documentation as a guide:

http://msdn2.microsoft.com/zh-cn/library/aa337080.aspx

I instantiated a new script component into an existing Data Flow in my SSIS project.

In the Script Transformation Editor, under the Connection Managers section, I associated the name dbConnManager to an already existing Connection Manager in the project.

My Connection Manager is of the type oOLEDB.

I then opened up the script designer and added the following lines of code where it said "Add your code here"

Dim myConnManager As IDTSConnectionManager90 = _

Me.Connections.ECFconnection

Dim dbConn As OleDb.OleDbConnection = _

CType(myConnManager.AcquireConnection(Nothing), OleDb.OleDbConnection)

When I test run the project I get the following error and the new script component is red:

Unable to cast COM object of type 'System.__ComObject' to class type 'System.Data.OleDb.OleDbConnection'. 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.

I know the database connection works since I am using it in a component that executes before this new script component.

I am stuck...Any suggestions?

Unfortunately you cannot use a Connection Manager that returns a native type in managed code. This includes OLE DB and Excel.

This limitation is noted in BOL in
http://msdn2.microsoft.com/en-us/library/ms136018.aspx
and
http://msdn2.microsoft.com/en-us/library/aa337080.aspx

with this suggestion:

If you need to call the AcquireConnection method of a connection manager that returns an unmanaged object, use an ADO.NET connection manager. When you configure the ADO.NET connection manager to use an OLE DB provider, it connects by using the .NET Framework Data Provider for OLE DB. In this case, the AcquireConnection method returns a System.Data.OleDb.OleDbConnection instead of an unmanaged object. To configure an ADO.NET connection manager for use with an Excel data source, select the Microsoft OLE DB Provider for Jet, specify an Excel workbook, and then enter Excel 8.0 (for Excel 97 and later) as the value of Extended Properties on the All page of the Connection Manager dialog box.

-Doug

|||

Thank you. That was the nudge in the right direction that I needed.

Greg.

OLEDB command in SSIS

I AM working on DW building and i m using SSIS.I haev problem with data transformation OLEDB command.I have written a query to clean data in OLEDB command box ,but it takes whole lot of time because at a time it slects 6000 rows from the source and put in to destination but i have 300000 rows to process.How can i increase the size.The OLE DB command executes for every row in the data flow. Are you sure you're stating the correct component?

You should use an OLE DB Source component hooked to an OLE DB Destination component to move your data.|||I want to delete some data while transfering from one to another i.e i want to do cleaning.|||If you want this to be fast, try writing the keys for the rows to delete to a temporary table, then use an Execute SQL task to issue a batch DELETE statement. Since the OLEDB Command transform executes once for each row that passes through it, it will be slower than doing a batch.

ole db transformation - redirect rows problem

Hi, i have a OLE DB transformation that executes a stored procedure. The stored procedure RAISERROR when an error is caught. I have set the OLE DB transformation to redirect rows. But instead, the task turns red and fails. Did i do something wrong here ?Did you set the error output to redirect rows, or is it still set to the default of failing the component?|||Of course, if the source stored proc is failing, what rows will you be diverting?|||

ah... i compared Lookup transform and OLE DB transform. For lookup transform, you can control "ErrorRowDisposition" for both Command Input and Command Output....

But for OLE DB Transform, you can control "ErrorRowDisposition" for only input, not output !!

So i guess I cannot RAISERROR in the stored proc and let OLE DB transform separate the "bad" records from the "good" records.

Perhaps I need to modify the stored proc to catch all errors - and return an errorCode parameter as output ? And then check this output parameter to see what records are not processed correctly ?

please advice !

OlE Db Command

Hi,

Iam trying to generate the increment value for a column by taking Maximum value of that field by using OLE DB Command Transformation.

This is the query iam using for that :
select max(ApplicationId)+1 as APPLICATIONID from Source..Applications

Iam finding difficulty in getting the 'APPLICATIONID' as the out put from OLEDB Transformation.OLE DB is not going to work with out any parameters ?can you please throw some light on this .First time iam using this transformation.

Another alternate solution is generating through script task (Thanks Jamie Thomson for his article).But i dont know how i can modify this to get the Max value instead of declaring some constant value for increment

Public Class ScriptMain
Inherits UserComponent
Dim Counter As Int32
Public Sub New()
Counter = 1006
End Sub

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
'
' Add your code here
'
Counter += 1
Row.APPLICATIONTASKID = Counter

End Sub

End Class
Please me help me in finding one solution from the above two

Thanks
Niru
No replies ..

Am I dint explained properly or nobody are aware of this?

Thanks
Niru
|||

If I understand your question correctly, you're asking to generate a surrogate key id (1,2,3,..) which key id is started from last key id + 1 from your source table. If yes, you can try this:

On control flow, create an Execute SQL task with your select statement:

select isnull(max(ApplicationId),1) as APPLICATIONID from Source..Applications

Set Result Set property = Single Row

Create a variable, on the Result Set assign your variable.

On data flow, create a column, ex. ApplicationKeyID set to null. Follow by script component task; in Input Columns, select the ApplicationKeyID and Usage type = ReadWrite. On Script, type in the variable name for ReadOnlyVariables property. And your script will look something like this:

Public Class ScriptMain

Inherits UserComponent

Dim Counter As Integer = 0

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Row.ApplicationKeyID = Variables.VariableName+ Counter

Counter = Counter + 1

End Sub

End Class

Hope this does not cause any more confusion for you...