I made SQLCLR function that returns SqlDateTime form integer value. I
deployed it succesfully on the server but I dont know, how to use it in the
DataFlow.

When I use it in the SQLQuery window on the SQL Server, it works well
example: SELECT dbo.JulianToGregorian (98001, 1900)
returns: 1998-01-01 00:00:00.000

But I would like to use it in the DataFlow component like OLE DB Command is,
or Derived Column, or in expression of the variable. I need to convert flown
value during dataflow by this SQLCLR function. How to do it?

Thanks.

Hi Mike,
Thank you for using Microsoft MSDN Managed Newsgroup.

Regarding your question, could you please elaborate your requirement so that we can better understand it and give out a
specific resolution?

There is no difference to use a normal UDF or a CLR UDF in OLE DB Command. You can simply input "SELECT
dbo.JulianToGregorian (98001, 1900)" in the SqlCommand property field. If you want to use the result as an input column
to the next component, I recommend that you use OLE DB Source. If you want to use SSIS variables together with the T-
SQL statement, I recommend that you consider using Execute SQL Task together with your data flow task. You can refer to
the following articles:
Amazing SSIS: Parameters in Execute SQL Task
http://blogs.msdn.com/msdnts/archive...-sql-task.aspx
SSIS: Mapping parameter inside of a Execute SQL Task, OLE DB Source Component or Datareader component
http://rafael-salas.blogspot.com/200...inside-of.html

Hope this helps!

Best regards,
Charles Wang
Microsoft Online Community Support
================================================== =========
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg (AT) microsoft (DOT) com.
================================================== =========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...#notifications.

Note: The MSDN Managed Newsgroup support offering is for
non-urgent issues where an initial response from the community
or a Microsoft Support Engineer within 1 business day is acceptable.
Please note that each follow up response may take approximately
2 business days as the support professional working with you may
need further investigation to reach the most efficient resolution.
The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by
contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscripti...t/default.aspx.
================================================== ==========
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== =======

Hi Mike,
Thank you for using Microsoft MSDN Managed Newsgroup.

Regarding your question, could you please elaborate your requirement so that we can better understand it and give out a
specific resolution?

There is no difference to use a normal UDF or a CLR UDF in OLE DB Command. You can simply input "SELECT
dbo.JulianToGregorian (98001, 1900)" in the SqlCommand property field. If you want to use the result as an input column
to the next component, I recommend that you use OLE DB Source. If you want to use SSIS variables together with the T-
SQL statement, I recommend that you consider using Execute SQL Task together with your data flow task. You can refer to
the following articles:
Amazing SSIS: Parameters in Execute SQL Task
http://blogs.msdn.com/msdnts/archive...-sql-task.aspx
SSIS: Mapping parameter inside of a Execute SQL Task, OLE DB Source Component or Datareader component
http://rafael-salas.blogspot.com/200...inside-of.html

Hope this helps!

Best regards,
Charles Wang
Microsoft Online Community Support
================================================== =========
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg (AT) microsoft (DOT) com.
================================================== =========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...#notifications.

Note: The MSDN Managed Newsgroup support offering is for
non-urgent issues where an initial response from the community
or a Microsoft Support Engineer within 1 business day is acceptable.
Please note that each follow up response may take approximately
2 business days as the support professional working with you may
need further investigation to reach the most efficient resolution.
The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by
contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscripti...t/default.aspx.
================================================== ==========
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== =======

Hi Mike,
Thank you for using Microsoft MSDN Managed Newsgroup.

Regarding your question, could you please elaborate your requirement so that we can better understand it and give out a
specific resolution?

There is no difference to use a normal UDF or a CLR UDF in OLE DB Command. You can simply input "SELECT
dbo.JulianToGregorian (98001, 1900)" in the SqlCommand property field. If you want to use the result as an input column
to the next component, I recommend that you use OLE DB Source. If you want to use SSIS variables together with the T-
SQL statement, I recommend that you consider using Execute SQL Task together with your data flow task. You can refer to
the following articles:
Amazing SSIS: Parameters in Execute SQL Task
http://blogs.msdn.com/msdnts/archive...-sql-task.aspx
SSIS: Mapping parameter inside of a Execute SQL Task, OLE DB Source Component or Datareader component
http://rafael-salas.blogspot.com/200...inside-of.html

Hope this helps!

Best regards,
Charles Wang
Microsoft Online Community Support
================================================== =========
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg (AT) microsoft (DOT) com.
================================================== =========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...#notifications.

Note: The MSDN Managed Newsgroup support offering is for
non-urgent issues where an initial response from the community
or a Microsoft Support Engineer within 1 business day is acceptable.
Please note that each follow up response may take approximately
2 business days as the support professional working with you may
need further investigation to reach the most efficient resolution.
The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by
contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscripti...t/default.aspx.
================================================== ==========
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== =======

Hi Mike,
Thank you for using Microsoft MSDN Managed Newsgroup.

Regarding your question, could you please elaborate your requirement so that we can better understand it and give out a
specific resolution?

There is no difference to use a normal UDF or a CLR UDF in OLE DB Command. You can simply input "SELECT
dbo.JulianToGregorian (98001, 1900)" in the SqlCommand property field. If you want to use the result as an input column
to the next component, I recommend that you use OLE DB Source. If you want to use SSIS variables together with the T-
SQL statement, I recommend that you consider using Execute SQL Task together with your data flow task. You can refer to
the following articles:
Amazing SSIS: Parameters in Execute SQL Task
http://blogs.msdn.com/msdnts/archive...-sql-task.aspx
SSIS: Mapping parameter inside of a Execute SQL Task, OLE DB Source Component or Datareader component
http://rafael-salas.blogspot.com/200...inside-of.html

Hope this helps!

Best regards,
Charles Wang
Microsoft Online Community Support
================================================== =========
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg (AT) microsoft (DOT) com.
================================================== =========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...#notifications.

Note: The MSDN Managed Newsgroup support offering is for
non-urgent issues where an initial response from the community
or a Microsoft Support Engineer within 1 business day is acceptable.
Please note that each follow up response may take approximately
2 business days as the support professional working with you may
need further investigation to reach the most efficient resolution.
The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by
contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscripti...t/default.aspx.
================================================== ==========
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== =======

Hi Mike,
Thank you for using Microsoft MSDN Managed Newsgroup.

Regarding your question, could you please elaborate your requirement so that we can better understand it and give out a
specific resolution?

There is no difference to use a normal UDF or a CLR UDF in OLE DB Command. You can simply input "SELECT
dbo.JulianToGregorian (98001, 1900)" in the SqlCommand property field. If you want to use the result as an input column
to the next component, I recommend that you use OLE DB Source. If you want to use SSIS variables together with the T-
SQL statement, I recommend that you consider using Execute SQL Task together with your data flow task. You can refer to
the following articles:
Amazing SSIS: Parameters in Execute SQL Task
http://blogs.msdn.com/msdnts/archive...-sql-task.aspx
SSIS: Mapping parameter inside of a Execute SQL Task, OLE DB Source Component or Datareader component
http://rafael-salas.blogspot.com/200...inside-of.html

Hope this helps!

Best regards,
Charles Wang
Microsoft Online Community Support
================================================== =========
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg (AT) microsoft (DOT) com.
================================================== =========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...#notifications.

Note: The MSDN Managed Newsgroup support offering is for
non-urgent issues where an initial response from the community
or a Microsoft Support Engineer within 1 business day is acceptable.
Please note that each follow up response may take approximately
2 business days as the support professional working with you may
need further investigation to reach the most efficient resolution.
The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by
contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscripti...t/default.aspx.
================================================== ==========
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== =======

Hi Mike,
Thank you for using Microsoft MSDN Managed Newsgroup.

Regarding your question, could you please elaborate your requirement so that we can better understand it and give out a
specific resolution?

There is no difference to use a normal UDF or a CLR UDF in OLE DB Command. You can simply input "SELECT
dbo.JulianToGregorian (98001, 1900)" in the SqlCommand property field. If you want to use the result as an input column
to the next component, I recommend that you use OLE DB Source. If you want to use SSIS variables together with the T-
SQL statement, I recommend that you consider using Execute SQL Task together with your data flow task. You can refer to
the following articles:
Amazing SSIS: Parameters in Execute SQL Task
http://blogs.msdn.com/msdnts/archive...-sql-task.aspx
SSIS: Mapping parameter inside of a Execute SQL Task, OLE DB Source Component or Datareader component
http://rafael-salas.blogspot.com/200...inside-of.html

Hope this helps!

Best regards,
Charles Wang
Microsoft Online Community Support
================================================== =========
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg (AT) microsoft (DOT) com.
================================================== =========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...#notifications.

Note: The MSDN Managed Newsgroup support offering is for
non-urgent issues where an initial response from the community
or a Microsoft Support Engineer within 1 business day is acceptable.
Please note that each follow up response may take approximately
2 business days as the support professional working with you may
need further investigation to reach the most efficient resolution.
The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by
contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscripti...t/default.aspx.
================================================== ==========
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== =======

Hi Mike,
Thank you for using Microsoft MSDN Managed Newsgroup.

Regarding your question, could you please elaborate your requirement so that we can better understand it and give out a
specific resolution?

There is no difference to use a normal UDF or a CLR UDF in OLE DB Command. You can simply input "SELECT
dbo.JulianToGregorian (98001, 1900)" in the SqlCommand property field. If you want to use the result as an input column
to the next component, I recommend that you use OLE DB Source. If you want to use SSIS variables together with the T-
SQL statement, I recommend that you consider using Execute SQL Task together with your data flow task. You can refer to
the following articles:
Amazing SSIS: Parameters in Execute SQL Task
http://blogs.msdn.com/msdnts/archive...-sql-task.aspx
SSIS: Mapping parameter inside of a Execute SQL Task, OLE DB Source Component or Datareader component
http://rafael-salas.blogspot.com/200...inside-of.html

Hope this helps!

Best regards,
Charles Wang
Microsoft Online Community Support
================================================== =========
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg (AT) microsoft (DOT) com.
================================================== =========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...#notifications.

Note: The MSDN Managed Newsgroup support offering is for
non-urgent issues where an initial response from the community
or a Microsoft Support Engineer within 1 business day is acceptable.
Please note that each follow up response may take approximately
2 business days as the support professional working with you may
need further investigation to reach the most efficient resolution.
The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by
contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscripti...t/default.aspx.
================================================== ==========
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== =======

Hi Mike,
Thank you for using Microsoft MSDN Managed Newsgroup.

Regarding your question, could you please elaborate your requirement so that we can better understand it and give out a
specific resolution?

There is no difference to use a normal UDF or a CLR UDF in OLE DB Command. You can simply input "SELECT
dbo.JulianToGregorian (98001, 1900)" in the SqlCommand property field. If you want to use the result as an input column
to the next component, I recommend that you use OLE DB Source. If you want to use SSIS variables together with the T-
SQL statement, I recommend that you consider using Execute SQL Task together with your data flow task. You can refer to
the following articles:
Amazing SSIS: Parameters in Execute SQL Task
http://blogs.msdn.com/msdnts/archive...-sql-task.aspx
SSIS: Mapping parameter inside of a Execute SQL Task, OLE DB Source Component or Datareader component
http://rafael-salas.blogspot.com/200...inside-of.html

Hope this helps!

Best regards,
Charles Wang
Microsoft Online Community Support
================================================== =========
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg (AT) microsoft (DOT) com.
================================================== =========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...#notifications.

Note: The MSDN Managed Newsgroup support offering is for
non-urgent issues where an initial response from the community
or a Microsoft Support Engineer within 1 business day is acceptable.
Please note that each follow up response may take approximately
2 business days as the support professional working with you may
need further investigation to reach the most efficient resolution.
The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by
contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscripti...t/default.aspx.
================================================== ==========
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== =======

Hello Charles.

The problem is, that I need replace or create new column in the row by the
tranformation that will use this function. For example: Im retrieving row
from the DB2 (AS400) database. In one column is date, that is represented as
Julian date (integer: 102154 = year 2002 and 154 day of the year). My SQL
function can transofrm this number to the SqlDateTime.

I need to create (Derive) new column on the fly (during datalfow) that will
be transformed by my function and will use the integer from the retrieved row.

Hope, this explain it is more clear.

Thanks for help

""Charles Wang [MSFT]"" wrote: