Flat file data types - defined from Table?

This question is around how we can get the data types and lengths populated into the flat file source columns.

In Connection Manager, you have your flat file defined. You can choose "Suggest Types...", and the minimum lengths and correct data types will be returned from within the data in the flat file.

Is there some way to automate this data type definition, but coming from the other direction (coming from the destination table that we are loading)?

For example, you have mapped the columns that will be loaded. Can you then reverse engineer the data types and lengths for the columns in the flat file from the destination table?

TIA

No, not possible. Unless you're going to write your own package using the SSIS API. But then, if you're going to do all of that work, SSIS may not be the best fit after all.|||

Phil

Thanks much for your answer. Maybe I'm not thinking flexibly enough regarding the solution.

It seems the flat file will pretty much default to DT_STR with length of 50 for all columns. Obviously some data could be truncated.

Where I'm trying to head is:

1. Assuming your table is mapped correctly to data types and lengths, would it not be best practice to set the same data types and lengths in your flat file definition? Or...does it matter?

2. Again, back to flexible thinking about the problem and solution. Is there another path that I'm not seeing? Perhaps the definition of data types and lengths for the flat file columns is moot, b/c it is also defined (and overridden) at another point, such as during the mapping or transforms?

Thanks again

|||

Alfred Kokintz wrote:

Phil

Thanks much for your answer. Maybe I'm not thinking flexibly enough regarding the solution.

It seems the flat file will pretty much default to DT_STR with length of 50 for all columns. Obviously some data could be truncated.

Where I'm trying to head is:

1. Assuming your table is mapped correctly to data types and lengths, would it not be best practice to set the same data types and lengths in your flat file definition? Or...does it matter?

2. Again, back to flexible thinking about the problem and solution. Is there another path that I'm not seeing? Perhaps the definition of data types and lengths for the flat file columns is moot, b/c it is also defined (and overridden) at another point, such as during the mapping or transforms?

Thanks again

Generally, if a flat file column contains integers, you will want to define it as such up front in the flat file connection manager. The more work you do there, the easier the rest of the puzzle will be.

Flat File Connection Manager does NOT fail

I use Flat File connection manager where I have defined the format of the file as "Ragged Right" (CR,LF Dos file) with no header rows. The columns are fixed width with Row Delimiter "{CR}{LF}".

The problem is when I process a file with incorrect format. The file execution does not throw an error. Instead it throws a warning " There is a partial row at the end of the file.".

How can I force the Flat file connection manager to FAIL if the format of the file is not matched exactly. I would think this would be the default behavior of Connection manager.

ie. If I have Connection manager setup for a dos file with 2 columns. Column 1 is 5 characters long, column 2 is 3 character long, and the end of line characters are CR, LF. However, if I send in file with 10 rows of 1 character and CR LF, the data flow works and processes these rows incorrectly. How can I force the task to fail if incoming file is not in defined format.

The "Ragged Right" format does not have any limitation on the size of the last column (delimited one), so if your file is missing some row delimiters the flat file parser will continue to look for them and probably swallow succeeding row(s).

You can restrict the size of your "ragged right" column by setting the OutputColumnWidth property of those columns on the Advanced page of the Flat File Connection Manager UI. That will make the flat file source fail if the truncation happens. If you would rather to redirect such rows, you can do that by setting "Redirect Row" for Truncation on the Error Output page of the Flat File Source UI, and then define your error flow.

HTH.|||Thanks. It worked.

FKY but no index

I am looking at various databases I am supporting which has FKY defined
but
not index on those columns. I believe when the child table (having FKY)
is joined with the parent table (having PKY), it will definitely result
in table lock of the child table.
I want to index all those columns which are part of FKY. Is there a
quick script (in stored procedure or TSQL) which can pinpoint all
those tables with the said columns.
TIA.IMHO the executing of some standard script on a production database should
be treated very carefully, if you want to create indexes on the columns you
should consider doing this manually to know what you´ve done. Ok, it´s kind
of work , but it will worth it (if you comment your work) if you want to
mantain this on your own.
Sorry for not suggesting es standard solution, but in my opinion this is
best practise.
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Data Cruncher" <dcruncher4@.netscape.net> schrieb im Newsbeitrag
news:1115307235.398241.216360@.g14g2000cwa.googlegroups.com...
>I am looking at various databases I am supporting which has FKY defined
> but
> not index on those columns. I believe when the child table (having FKY)
> is joined with the parent table (having PKY), it will definitely result
> in table lock of the child table.
> I want to index all those columns which are part of FKY. Is there a
> quick script (in stored procedure or TSQL) which can pinpoint all
> those tables with the said columns.
> TIA.
>|||Jens S=FC=DFmeyer wrote:
> IMHO the executing of some standard script on a production database
should
> be treated very carefully, if you want to create indexes on the
columns you
> should consider doing this manually to know what you=B4ve done. Ok,
it=B4s kind
> of work , but it will worth it (if you comment your work) if you want
to
> mantain this on your own.
> Sorry for not suggesting es standard solution, but in my opinion this
is
> best practise.
> HTH, Jens Suessmeyer.
sorry I was not clear. I want a script to pinpoint tables which
has missing indexes on the FKY columns. I don't want a script
to automatically build the index. I agree with you fully that
it has to be done with caution.

First sp after re-start fails because of User-defined type

Hi,
I'm having a problem with SQL Server 7. If I restart the
server, and the first stroed proc I run contains a
reference to a user defined data type then the query fails
because the data type is not recognised. (In this case I'm
creating a temporary table in the stored proc, where one
column in the table uses the user defined data type)
A workaround is to run another stored proc first, and then
everything is fine.
Has anyone got a more elegant solution or reason why this
would happen?
Thanks,
Neil
Seems to be some issue with a user defined datatype not existing in tempdb. Hard to ell without a
repro. Perhaps it is as easy as creating that dt in the model database so you know it will exist in
tempdb?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Neil" <anonymous@.discussions.microsoft.com> wrote in message
news:49e801c4a0b1$d37e6cb0$a301280a@.phx.gbl...
> Hi,
> I'm having a problem with SQL Server 7. If I restart the
> server, and the first stroed proc I run contains a
> reference to a user defined data type then the query fails
> because the data type is not recognised. (In this case I'm
> creating a temporary table in the stored proc, where one
> column in the table uses the user defined data type)
> A workaround is to run another stored proc first, and then
> everything is fine.
> Has anyone got a more elegant solution or reason why this
> would happen?
> Thanks,
> Neil
|||I don't think so - In the SP,before I create the temporary
table, I copy all the user data types to the tempdb, but
this isn't being run. In fact, I put some logging calls in
the SP and ran it from Query analyzer, and nothing is run.
It seems that SQL Server is doing some sort of
compilation/validation on the SP and failing it before any
attempt is made to run it. Running some other query seems
to make it 'aware' of the user defined data types, and
then it is happy with the original SP.
Thanks anyway,
Neil

>--Original Message--
>Seems to be some issue with a user defined datatype not
existing in tempdb. Hard to ell without a
>repro. Perhaps it is as easy as creating that dt in the
model database so you know it will exist in
>tempdb?
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"Neil" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:49e801c4a0b1$d37e6cb0$a301280a@.phx.gbl...
fails[vbcol=seagreen]
I'm[vbcol=seagreen]
then[vbcol=seagreen]
this
>
>.
>
|||It seems I spoke too soon! The following code reproduces
the problem:
CREATE PROCEDURE MyUDTTest AS
BEGIN
declare @.stmt as nvarchar(255)
set @.stmt = 'USE tempdb' + CHAR(13) + 'exec
sp_addtype ''udtBuySell'' , ''char(1)'''
exec sp_executesql @.stmt
CREATE TABLE #MyTable (MyColumn udtBuySell)
END
trying to run this SP after a stop/start gives the
following error:
Server: Msg 2715, Level 16, State 7, Procedure MyUDTTest,
Line 10
Column or parameter #1: Cannot find data type udtBuySell.
in tempdb create the udtBuySell data type. Then drop it.
Now the sp will run.
Any thoughts?
Neil

>--Original Message--
>Seems to be some issue with a user defined datatype not
existing in tempdb. Hard to ell without a
>repro. Perhaps it is as easy as creating that dt in the
model database so you know it will exist in
>tempdb?
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"Neil" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:49e801c4a0b1$d37e6cb0$a301280a@.phx.gbl...
fails[vbcol=seagreen]
I'm[vbcol=seagreen]
then[vbcol=seagreen]
this
>
>.
>
|||This is a scoping problem. Since the UDT is created in dynamic SQL, the datatype doesn't exist when
SQL Server parses the code in the procedure. Create the datatype in model instead.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Neil" <anonymous@.discussions.microsoft.com> wrote in message
news:244901c4a176$b0fdea60$a601280a@.phx.gbl...[vbcol=seagreen]
> It seems I spoke too soon! The following code reproduces
> the problem:
> CREATE PROCEDURE MyUDTTest AS
> BEGIN
> declare @.stmt as nvarchar(255)
> set @.stmt = 'USE tempdb' + CHAR(13) + 'exec
> sp_addtype ''udtBuySell'' , ''char(1)'''
> exec sp_executesql @.stmt
> CREATE TABLE #MyTable (MyColumn udtBuySell)
> END
> trying to run this SP after a stop/start gives the
> following error:
> Server: Msg 2715, Level 16, State 7, Procedure MyUDTTest,
> Line 10
> Column or parameter #1: Cannot find data type udtBuySell.
> in tempdb create the udtBuySell data type. Then drop it.
> Now the sp will run.
> Any thoughts?
> Neil
>
> existing in tempdb. Hard to ell without a
> model database so you know it will exist in
> message
> fails
> I'm
> then
> this
|||Tibor,
Thanks very much for your help. Creating the data type in
model has been ruled out from above (!) as it would mean
remembering to change it in two places (in the working db
and in model).
I'm curious as to why the procedure should work after the
datatype has been created then dropped in tempdb. I can
see what you are saying about scoping, but why would it be
OK after creating/dropping the type in tempdb? It still
doesn't exist in tempdb after all...
Our current plan is to run a job when SQLAgent starts to
copy the user types from our working db to tempdb (so now
we have to remember to restart the server after a change
to the user types - where will it all end!)
Thanks again for your interest in this problem,
Neil

>--Original Message--
>This is a scoping problem. Since the UDT is created in
dynamic SQL, the datatype doesn't exist when
>SQL Server parses the code in the procedure. Create the
datatype in model instead.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"Neil" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:244901c4a176$b0fdea60$a601280a@.phx.gbl...
MyUDTTest,[vbcol=seagreen]
udtBuySell.[vbcol=seagreen]
the[vbcol=seagreen]
case[vbcol=seagreen]
one
>
>.
>

First sp after re-start fails because of User-defined type

Hi,
I'm having a problem with SQL Server 7. If I restart the
server, and the first stroed proc I run contains a
reference to a user defined data type then the query fails
because the data type is not recognised. (In this case I'm
creating a temporary table in the stored proc, where one
column in the table uses the user defined data type)
A workaround is to run another stored proc first, and then
everything is fine.
Has anyone got a more elegant solution or reason why this
would happen?
Thanks,
NeilSeems to be some issue with a user defined datatype not existing in tempdb. Hard to ell without a
repro. Perhaps it is as easy as creating that dt in the model database so you know it will exist in
tempdb?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Neil" <anonymous@.discussions.microsoft.com> wrote in message
news:49e801c4a0b1$d37e6cb0$a301280a@.phx.gbl...
> Hi,
> I'm having a problem with SQL Server 7. If I restart the
> server, and the first stroed proc I run contains a
> reference to a user defined data type then the query fails
> because the data type is not recognised. (In this case I'm
> creating a temporary table in the stored proc, where one
> column in the table uses the user defined data type)
> A workaround is to run another stored proc first, and then
> everything is fine.
> Has anyone got a more elegant solution or reason why this
> would happen?
> Thanks,
> Neil|||I don't think so - In the SP,before I create the temporary
table, I copy all the user data types to the tempdb, but
this isn't being run. In fact, I put some logging calls in
the SP and ran it from Query analyzer, and nothing is run.
It seems that SQL Server is doing some sort of
compilation/validation on the SP and failing it before any
attempt is made to run it. Running some other query seems
to make it 'aware' of the user defined data types, and
then it is happy with the original SP.
Thanks anyway,
Neil
>--Original Message--
>Seems to be some issue with a user defined datatype not
existing in tempdb. Hard to ell without a
>repro. Perhaps it is as easy as creating that dt in the
model database so you know it will exist in
>tempdb?
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"Neil" <anonymous@.discussions.microsoft.com> wrote in
message
>news:49e801c4a0b1$d37e6cb0$a301280a@.phx.gbl...
>> Hi,
>> I'm having a problem with SQL Server 7. If I restart the
>> server, and the first stroed proc I run contains a
>> reference to a user defined data type then the query
fails
>> because the data type is not recognised. (In this case
I'm
>> creating a temporary table in the stored proc, where one
>> column in the table uses the user defined data type)
>> A workaround is to run another stored proc first, and
then
>> everything is fine.
>> Has anyone got a more elegant solution or reason why
this
>> would happen?
>> Thanks,
>> Neil
>
>.
>|||This is a scoping problem. Since the UDT is created in dynamic SQL, the datatype doesn't exist when
SQL Server parses the code in the procedure. Create the datatype in model instead.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Neil" <anonymous@.discussions.microsoft.com> wrote in message
news:244901c4a176$b0fdea60$a601280a@.phx.gbl...
> It seems I spoke too soon! The following code reproduces
> the problem:
> CREATE PROCEDURE MyUDTTest AS
> BEGIN
> declare @.stmt as nvarchar(255)
> set @.stmt = 'USE tempdb' + CHAR(13) + 'exec
> sp_addtype ''udtBuySell'' , ''char(1)'''
> exec sp_executesql @.stmt
> CREATE TABLE #MyTable (MyColumn udtBuySell)
> END
> trying to run this SP after a stop/start gives the
> following error:
> Server: Msg 2715, Level 16, State 7, Procedure MyUDTTest,
> Line 10
> Column or parameter #1: Cannot find data type udtBuySell.
> in tempdb create the udtBuySell data type. Then drop it.
> Now the sp will run.
> Any thoughts?
> Neil
>
> >--Original Message--
> >Seems to be some issue with a user defined datatype not
> existing in tempdb. Hard to ell without a
> >repro. Perhaps it is as easy as creating that dt in the
> model database so you know it will exist in
> >tempdb?
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >http://www.karaszi.com/sqlserver/default.asp
> >http://www.solidqualitylearning.com/
> >
> >
> >"Neil" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:49e801c4a0b1$d37e6cb0$a301280a@.phx.gbl...
> >> Hi,
> >>
> >> I'm having a problem with SQL Server 7. If I restart the
> >> server, and the first stroed proc I run contains a
> >> reference to a user defined data type then the query
> fails
> >> because the data type is not recognised. (In this case
> I'm
> >> creating a temporary table in the stored proc, where one
> >> column in the table uses the user defined data type)
> >>
> >> A workaround is to run another stored proc first, and
> then
> >> everything is fine.
> >>
> >> Has anyone got a more elegant solution or reason why
> this
> >> would happen?
> >>
> >> Thanks,
> >>
> >> Neil
> >
> >
> >.
> >|||Tibor,
Thanks very much for your help. Creating the data type in
model has been ruled out from above (!) as it would mean
remembering to change it in two places (in the working db
and in model).
I'm curious as to why the procedure should work after the
datatype has been created then dropped in tempdb. I can
see what you are saying about scoping, but why would it be
OK after creating/dropping the type in tempdb? It still
doesn't exist in tempdb after all...
Our current plan is to run a job when SQLAgent starts to
copy the user types from our working db to tempdb (so now
we have to remember to restart the server after a change
to the user types - where will it all end!)
Thanks again for your interest in this problem,
Neil
>--Original Message--
>This is a scoping problem. Since the UDT is created in
dynamic SQL, the datatype doesn't exist when
>SQL Server parses the code in the procedure. Create the
datatype in model instead.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"Neil" <anonymous@.discussions.microsoft.com> wrote in
message
>news:244901c4a176$b0fdea60$a601280a@.phx.gbl...
>> It seems I spoke too soon! The following code reproduces
>> the problem:
>> CREATE PROCEDURE MyUDTTest AS
>> BEGIN
>> declare @.stmt as nvarchar(255)
>> set @.stmt = 'USE tempdb' + CHAR(13) + 'exec
>> sp_addtype ''udtBuySell'' , ''char(1)'''
>> exec sp_executesql @.stmt
>> CREATE TABLE #MyTable (MyColumn udtBuySell)
>> END
>> trying to run this SP after a stop/start gives the
>> following error:
>> Server: Msg 2715, Level 16, State 7, Procedure
MyUDTTest,
>> Line 10
>> Column or parameter #1: Cannot find data type
udtBuySell.
>> in tempdb create the udtBuySell data type. Then drop it.
>> Now the sp will run.
>> Any thoughts?
>> Neil
>>
>> >--Original Message--
>> >Seems to be some issue with a user defined datatype not
>> existing in tempdb. Hard to ell without a
>> >repro. Perhaps it is as easy as creating that dt in the
>> model database so you know it will exist in
>> >tempdb?
>> >
>> >--
>> >Tibor Karaszi, SQL Server MVP
>> >http://www.karaszi.com/sqlserver/default.asp
>> >http://www.solidqualitylearning.com/
>> >
>> >
>> >"Neil" <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >news:49e801c4a0b1$d37e6cb0$a301280a@.phx.gbl...
>> >> Hi,
>> >>
>> >> I'm having a problem with SQL Server 7. If I restart
the
>> >> server, and the first stroed proc I run contains a
>> >> reference to a user defined data type then the query
>> fails
>> >> because the data type is not recognised. (In this
case
>> I'm
>> >> creating a temporary table in the stored proc, where
one
>> >> column in the table uses the user defined data type)
>> >>
>> >> A workaround is to run another stored proc first, and
>> then
>> >> everything is fine.
>> >>
>> >> Has anyone got a more elegant solution or reason why
>> this
>> >> would happen?
>> >>
>> >> Thanks,
>> >>
>> >> Neil
>> >
>> >
>> >.
>> >
>
>.
>

Firing User defined Function from Select, without using function name, is this possib

Hi all, Can anyone tell me if it is possible to fire a user defined
function in SQL Server directly from an ordinary select function.
Example:
I have a function fx_Str_Title_Case(varchar). (change string to title
case, caps first letter of each word in sentence).
At present I call this as follows:
SELECT fx_Str_Title_Case(aColumn) AS Result
FROM aTable
I wont to know if I can call this like this:
SELECT aColumn AS Result
FROM aTable
to get the same result?
ThanksPut your function in a view and query the view.
David Portas
SQL Server MVP
--|||Thanks, I though of that, but was wondering if there is any way to
trigger the function based on constraints on the table or something
like.|||You can use a function in a constraint but constraints are referenced
only for updates, not for a SELECT.
David Portas
SQL Server MVP
--|||You can create a computed column with the UDF call.
CREATE TABLE Test(name VARCHAR(15), ProperName as (dbo.Proper(name)))
INSERT INTO Test Values('abc xyz')
INSERT INTO Test Values('abc XYZ aVC')
SELECT * FROM Test
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
<philip.mckee@.pramerica.ie> wrote in message
news:1123672753.295824.65610@.g44g2000cwa.googlegroups.com...
> Thanks, I though of that, but was wondering if there is any way to
> trigger the function based on constraints on the table or something
> like.
>|||On 10 Aug 2005 04:15:50 -0700, David Portas wrote:

>Put your function in a view and query the view.
Or in a computed column in the table.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||What is wrong with specifying the function, and how would you know what
function is beging "fired"?
<philip.mckee@.pramerica.ie> wrote in message
news:1123672116.950885.206380@.g49g2000cwa.googlegroups.com...
> Hi all, Can anyone tell me if it is possible to fire a user defined
> function in SQL Server directly from an ordinary select function.
> Example:
> I have a function fx_Str_Title_Case(varchar). (change string to title
> case, caps first letter of each word in sentence).
> At present I call this as follows:
> SELECT fx_Str_Title_Case(aColumn) AS Result
> FROM aTable
> I wont to know if I can call this like this:
> SELECT aColumn AS Result
> FROM aTable
> to get the same result?
> Thanks
>