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 for the advanced programmers!

I have a string column but I need to check if it contains any numeric
data ( in a WHERE Clause).
I did the following without any success
WHERE CONVERT(int,geocode) > 0
Gives an error as soon as it finds any non-numeric data.
Thanks> I have a string column but I need to check if it contains any numeric
> data ( in a WHERE Clause).
Try:
WHERE geocode LIKE '%[0-9]%'
Hope this helps.
Dan Guzman
SQL Server MVP
"S Chapman" <s_chapman47@.hotmail.co.uk> wrote in message
news:1140448043.080918.304840@.g47g2000cwa.googlegroups.com...
> I have a string column but I need to check if it contains any numeric
> data ( in a WHERE Clause).
> I did the following without any success
> WHERE CONVERT(int,geocode) > 0
> Gives an error as soon as it finds any non-numeric data.
> Thanks
>|||S Chapman wrote:
> I have a string column but I need to check if it contains any numeric
> data ( in a WHERE Clause).
> I did the following without any success
> WHERE CONVERT(int,geocode) > 0
> Gives an error as soon as it finds any non-numeric data.
> Thanks
WHERE geocode NOT LIKE '%[^0-9]%'
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Hi
CREATE TABLE Sometable (currencyvalues VARCHAR(20) PRIMARY KEY)
INSERT INTO Sometable VALUES ('xnl 23.15')
INSERT INTO Sometable VALUES ('xnl')
INSERT INTO Sometable VALUES ('-23.15')
INSERT INTO Sometable VALUES ('muu')
SELECT * FROM Sometable WHERE currencyvalues LIKE '%[0-9]%'
--or
SELECT * FROM Sometable WHERE PATINDEX('%[0-9]%',currencyvalues)>0
Be aware that SQL Server will not probably use an index on the columnn ( if
you have one)
"S Chapman" <s_chapman47@.hotmail.co.uk> wrote in message
news:1140448043.080918.304840@.g47g2000cwa.googlegroups.com...
> I have a string column but I need to check if it contains any numeric
> data ( in a WHERE Clause).
> I did the following without any success
> WHERE CONVERT(int,geocode) > 0
> Gives an error as soon as it finds any non-numeric data.
> Thanks
>|||S wrote on 20 Feb 2006 07:07:23 -0800:

> I have a string column but I need to check if it contains any numeric
> data ( in a WHERE Clause).
> I did the following without any success
> WHERE CONVERT(int,geocode) > 0
> Gives an error as soon as it finds any non-numeric data.
Are you look for numbers within the column, or a column that is just a
number? If the latter then you can using this:
WHERE ISNUMERIC(geocode) = 1
Dan|||Daniel
It will resturns wrong result as well as ISNUMERIC function is inaccurate
http://www.aspfaq.com/show.asp?id=2390
INSERT INTO Sometable VALUES ('xnl 23.15')
INSERT INTO Sometable VALUES ('.')
SELECT * FROM Sometable WHERE ISNUMERIC(currencyvalues)=1
"Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message
news:%237y6iGjNGHA.740@.TK2MSFTNGP12.phx.gbl...
>S wrote on 20 Feb 2006 07:07:23 -0800:
>
> Are you look for numbers within the column, or a column that is just a
> number? If the latter then you can using this:
> WHERE ISNUMERIC(geocode) = 1
>
> Dan
>

OLE DB Source using table name variable

I have a package-level variable [User::viewName], type = string, containing a view name. I want to setup an OLE DB source to use this variable value as the source, so Data access mode = "Table name or view name variable". The Variable name dropdown contains [User:viewName], so I select it. When I click OK to leave the edit dialog I get the error:

The variable User::viewName is required to be of type "VT_BSTR".

Only variables of type String occur in the Variable name dropdown; if I try changing it to a type other than string it doesn't occur in the dropdown. What is VT_BSTR and how can I change the variable type to it?

I've never seen VT_BSTR in my life, that isn't a data type that I know of. Something is awry here - I wonder if it could be package corruption. Does the same happen when you try a differrent string variable?

-Jamie

|||I'm copying and pasting a package in Solution Explorer, then playing with the copy to see what works, then going back to the original. The package this problem is occurring in is a copy; might that be the problem? This seems like such an ordinary thing to do (get the table or view name from a variable) that I'm really surprised it's happening. Are there known problems with copying packages?|||

I don't know of any - but it does sound as though some corruption has occurred somewhere. Can you share the contents of the .dtsx file? (i.e. open it in notepad and copy the contents to here)?

-Jamie

|||

mruniqueid wrote:

I'm copying and pasting a package in Solution Explorer, then playing with the copy to see what works, then going back to the original. The package this problem is occurring in is a copy; might that be the problem? This seems like such an ordinary thing to do (get the table or view name from a variable) that I'm really surprised it's happening. Are there known problems with copying packages?

Can't think it matters, but be sure you generate a new GUID on the copied package. Control-flow background: right click, properties. Select the drop down in the ID field and generate a new GUID.|||

In my original package Data Flow I had an OLE DB Source with Data access mode = "Table or view". When I try to set Data access mode = "Table name or view name variable" in the original package I run into this trouble.

I tried creating a new package from scratch and realized I've got a knowledge gap. If you create an OLE DB Source and set Data access mode = "Table name or view name variable" right from the start, then how do you define the source output columns since there's no table to derive them from? I have several views with the same structure; in my original package I picked one of these as "Name of the table or the view" which of course defined output columns for the source, then tried to change Data access mode to a variable as described above.

Can I start with Data access mode = variable? If I try that I get the error "A destination table name has not been provided". I created an OLE DB Destination and connected it to the OLE DB Source but the error persists. What is the proper sequence of steps and settings to use a variable name for Data Access Mode in an OLE DB Source?

|||

mruniqueid wrote:

In my original package Data Flow I had an OLE DB Source with Data access mode = "Table or view". When I try to set Data access mode = "Table name or view name variable" in the original package I run into this trouble.

I tried creating a new package from scratch and realized I've got a knowledge gap. If you create an OLE DB Source and set Data access mode = "Table name or view name variable" right from the start, then how do you define the source output columns since there's no table to derive them from?

The name of the table needs to be stored in the variable.

mruniqueid wrote:

I have several views with the same structure; in my original package I picked one of these as "Name of the table or the view" which of course defined output columns for the source, then tried to change Data access mode to a variable as described above.

Can I start with Data access mode = variable? If I try that I get the error "A destination table name has not been provided". I created an OLE DB Destination and connected it to the OLE DB Source but the error persists. What is the proper sequence of steps and settings to use a variable name for Data Access Mode in an OLE DB Source?

1. Create the variable of type string

2. Add the table name into teh variable

3. Create your OLE DB Source and select the variable that you have just chosen.

-Jamie

|||

I didn't realize the variable value was evaluated at design time, since it's set at runtime. Makes sense though - thanks a lot!

- Dana

OLE DB Services and SQL Native Client

Is the option OLE DB Services = -4 still valid in the connection string with
the SQL Native Client ?Hello Michael,
The connection string in the SQL Native Client did not contain the OLE DB
Services option.
If you want to disable the resource pooling, please set the Pooling
property to false in the connection string.
SqlConnection.ConnectionString Property
http://msdn2.microsoft.com/en-us/li...t.sqlconnection
connectionstring(VS.80).aspx
This article described all the propertied you could use in the
ConnectionString.
Sincerely,
Wei Lu
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hello,
I'm NOT using SqlConnection.
I'm using ADO (NOT ADO.NET) with Provider=SQLNCLI
"Wei Lu [MSFT]" <weilu@.online.microsoft.com> wrote in message
news:S5EFMreZHHA.2372@.TK2MSFTNGHUB02.phx.gbl...
> Hello Michael,
> The connection string in the SQL Native Client did not contain the OLE DB
> Services option.
> If you want to disable the resource pooling, please set the Pooling
> property to false in the connection string.
> SqlConnection.ConnectionString Property
> [url]http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection[/ur
l]
> connectionstring(VS.80).aspx
> This article described all the propertied you could use in the
> ConnectionString.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ========================================
==========
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
==========
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Hello Michael,
Thanks for the update.
Since the SQL Native Client works different with ADO, I am doult that ADO
did not fully compatitable with SQL Native Client. I will consult the
internal team to confirm whether we could use the Pooling setting in the
connnection string for ADO.
My suggestion is that you may considaring migrate your application from ADO
to ADO.NET if you do not have other concern. Please let me know whether you
have any concern about the ADO.NET. Thank you!
Sincerely,
Wei Lu
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.|||The application is HUGE and was developed using ADO which is why we can not
migrate.
"Wei Lu [MSFT]" <weilu@.online.microsoft.com> wrote in message
news:nwGUjviZHHA.3800@.TK2MSFTNGHUB02.phx.gbl...
> Hello Michael,
> Thanks for the update.
> Since the SQL Native Client works different with ADO, I am doult that ADO
> did not fully compatitable with SQL Native Client. I will consult the
> internal team to confirm whether we could use the Pooling setting in the
> connnection string for ADO.
> My suggestion is that you may considaring migrate your application from
> ADO
> to ADO.NET if you do not have other concern. Please let me know whether
> you
> have any concern about the ADO.NET. Thank you!
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ========================================
==========
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
==========
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Hello Michael,
I did not get the reply from the internal right now.
Once I get the response, I will update you ASAP.
Sincerely,
Wei Lu
Microsoft Online Community Support
========================================
==========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications.
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/subscript...t/default.aspx.
========================================
==========
(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Hello Michael,
According to the response from internal team, OLE DB SERVICES works for any
OLE DB Provider because it operates at the OLE DB service layer that is
used to instantiate a provider instance. i.e. it's not a provider-specific
setting. So you could still use the OLE DB SERVICES = -4 in ADO string.
Sincerely,
Wei Lu
Microsoft Online Community Support
========================================
==========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications.
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/subscript...t/default.aspx.
========================================
==========
(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.|||Ah why do you keep polling like this? It just adds noise to the newsgroup.
We know you're there and want to help.
Thanks
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
----
---
"Wei Lu [MSFT]" <weilu@.online.microsoft.com> wrote in message
news:kqiT3vGbHHA.3820@.TK2MSFTNGHUB02.phx.gbl...
> Hi ,
> How is everything going? Please feel free to let me know if you need any
> assistance.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ========================================
==========
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
==========
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Hello William,
I just want to check the status of the issue. Thank you!
Sincerely,
Wei Lu
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.

OLE DB Services and SQL Native Client

Is the option OLE DB Services = -4 still valid in the connection string with
the SQL Native Client ?
Hi Michael,
I notice that you have posted the same question in
microsoft.public.sqlserver.clients newsgroup, to which I have already
responded. Please check my answer there, and if you need any further
assistance on this particular issue please reply to me in that thread so I
can follow up with you. In the future, please don't cross-post the same
question in multiple newsgroups. This will help our engineers work on your
question more efficiently. Your understanding and cooperation is
appreciated.
For your convenience, I have included my reply as follows:
Hello Michael,
The connection string in the SQL Native Client did not contain the OLE DB
Services option.
If you want to disable the resource pooling, please set the Pooling
property to false in the connection string.
SqlConnection.ConnectionString Property
http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection
connectionstring(VS.80).aspx
This article described all the propertied you could use in the
ConnectionString.
Sincerely,
Wei Lu
Microsoft Online Community Support

Thank you and have a nice day!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

OLE DB Services and SQL Native Client

Is the option OLE DB Services = -4 still valid in the connection string with
the SQL Native Client ?
Hello Michael,
The connection string in the SQL Native Client did not contain the OLE DB
Services option.
If you want to disable the resource pooling, please set the Pooling
property to false in the connection string.
SqlConnection.ConnectionString Property
http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection
connectionstring(VS.80).aspx
This article described all the propertied you could use in the
ConnectionString.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Hello,
I'm NOT using SqlConnection.
I'm using ADO (NOT ADO.NET) with Provider=SQLNCLI
"Wei Lu [MSFT]" <weilu@.online.microsoft.com> wrote in message
news:S5EFMreZHHA.2372@.TK2MSFTNGHUB02.phx.gbl...
> Hello Michael,
> The connection string in the SQL Native Client did not contain the OLE DB
> Services option.
> If you want to disable the resource pooling, please set the Pooling
> property to false in the connection string.
> SqlConnection.ConnectionString Property
> http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection
> connectionstring(VS.80).aspx
> This article described all the propertied you could use in the
> ConnectionString.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
|||Hello Michael,
Thanks for the update.
Since the SQL Native Client works different with ADO, I am doult that ADO
did not fully compatitable with SQL Native Client. I will consult the
internal team to confirm whether we could use the Pooling setting in the
connnection string for ADO.
My suggestion is that you may considaring migrate your application from ADO
to ADO.NET if you do not have other concern. Please let me know whether you
have any concern about the ADO.NET. Thank you!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
|||The application is HUGE and was developed using ADO which is why we can not
migrate.
"Wei Lu [MSFT]" <weilu@.online.microsoft.com> wrote in message
news:nwGUjviZHHA.3800@.TK2MSFTNGHUB02.phx.gbl...
> Hello Michael,
> Thanks for the update.
> Since the SQL Native Client works different with ADO, I am doult that ADO
> did not fully compatitable with SQL Native Client. I will consult the
> internal team to confirm whether we could use the Pooling setting in the
> connnection string for ADO.
> My suggestion is that you may considaring migrate your application from
> ADO
> to ADO.NET if you do not have other concern. Please let me know whether
> you
> have any concern about the ADO.NET. Thank you!
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
|||Hello Michael,
I did not get the reply from the internal right now.
Once I get the response, I will update you ASAP.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
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/subscriptions/support/default.aspx.
==================================================
(This posting is provided "AS IS", with no warranties, and confers no
rights.)
|||Hello Michael,
According to the response from internal team, OLE DB SERVICES works for any
OLE DB Provider because it operates at the OLE DB service layer that is
used to instantiate a provider instance. i.e. it's not a provider-specific
setting. So you could still use the OLE DB SERVICES = -4 in ADO string.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
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/subscriptions/support/default.aspx.
==================================================
(This posting is provided "AS IS", with no warranties, and confers no
rights.)
|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Ah why do you keep polling like this? It just adds noise to the newsgroup.
We know you're there and want to help.
Thanks
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
------
"Wei Lu [MSFT]" <weilu@.online.microsoft.com> wrote in message
news:kqiT3vGbHHA.3820@.TK2MSFTNGHUB02.phx.gbl...
> Hi ,
> How is everything going? Please feel free to let me know if you need any
> assistance.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
|||Hello William,
I just want to check the status of the issue. Thank you!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

OLE DB Connection String for FTP?

Does there exist an OLE DB Connection string for FTP? If so what is the format? I'm trying to build a custom connection manager and I'm not sure what the supplied connection string will look like for FTP. Thanks in advance.Probably not, no. You could write your own, I suppose.

Doubtful you will find an OLE driver for FTP. There exists an FTP task, though!