Name of the column mapping UI control?

I am developing a transformation component and I'd like the gui to feature one of the mapping controls that are used for mapping input columns to, for example, SQL Server database columns in the OLE Database Destintation component, among others. I cannot for the life of me discover what the control is called or even whether it is available for general use. Can anyone help me out? Sorry if this is OT but it seemed like the people here would be the most likely to know immediatelly what I was on about.

Thanks
Charlie.

I am trying to do a similar thing and the tool you described would be useful for me too. Did you have any luck finding it?

Your help will be greatly appreciated,

Savita

|||No not at all. I ended up using a tree view to show the mapping and two list boxes, one for each side of the mapping. It doesn't look *great* but it works and besides, the mapping component I was talking about is pretty hellish to actually use if the mapping involves lots of lines that cross over. Even SSIS has two views on the same thing to make it look fancy and be clear.

Charlie|||As far as I know, and I have asked and looked, that control and much of the data flow UI controls are both undocumented and/or inaccessible. In fact I don't think the UI is managed at all, so would be rather nasty to try and use even if you could find and access it.

Name of the column mapping UI control?

I am developing a transformation component and I'd like the gui to feature one of the mapping controls that are used for mapping input columns to, for example, SQL Server database columns in the OLE Database Destintation component, among others. I cannot for the life of me discover what the control is called or even whether it is available for general use. Can anyone help me out? Sorry if this is OT but it seemed like the people here would be the most likely to know immediatelly what I was on about.

Thanks
Charlie.

I am trying to do a similar thing and the tool you described would be useful for me too. Did you have any luck finding it?

Your help will be greatly appreciated,

Savita

|||No not at all. I ended up using a tree view to show the mapping and two list boxes, one for each side of the mapping. It doesn't look *great* but it works and besides, the mapping component I was talking about is pretty hellish to actually use if the mapping involves lots of lines that cross over. Even SSIS has two views on the same thing to make it look fancy and be clear.

Charlie
|||As far as I know, and I have asked and looked, that control and much of the data flow UI controls are both undocumented and/or inaccessible. In fact I don't think the UI is managed at all, so would be rather nasty to try and use even if you could find and access it.

Name of the column mapping UI control?

I am developing a transformation component and I'd like the gui to feature one of the mapping controls that are used for mapping input columns to, for example, SQL Server database columns in the OLE Database Destintation component, among others. I cannot for the life of me discover what the control is called or even whether it is available for general use. Can anyone help me out? Sorry if this is OT but it seemed like the people here would be the most likely to know immediatelly what I was on about.

Thanks
Charlie.

I am trying to do a similar thing and the tool you described would be useful for me too. Did you have any luck finding it?

Your help will be greatly appreciated,

Savita

|||No not at all. I ended up using a tree view to show the mapping and two list boxes, one for each side of the mapping. It doesn't look *great* but it works and besides, the mapping component I was talking about is pretty hellish to actually use if the mapping involves lots of lines that cross over. Even SSIS has two views on the same thing to make it look fancy and be clear.

Charlie|||As far as I know, and I have asked and looked, that control and much of the data flow UI controls are both undocumented and/or inaccessible. In fact I don't think the UI is managed at all, so would be rather nasty to try and use even if you could find and access it.sql

Name of primary key

What 's the way to know
the name of the column that is
the primary key of a tableTry this one:

select 'Table ['+su.name+'.'+so.name+']',sc.name
from sysindexes sy
join sysobjects so on so.id=sy.id and so.xtype='U'
join sysusers su on su.uid=so.uid
join sysindexkeys si on si.id=so.id and si.indid=sy.indid
join syscolumns sc on sc.id=so.id and sc.colid=si.colid
join sysobjects sop on sop.parent_obj=so.id and sop.xtype='PK' and sop.name=sy.name
where sy.indid not in(0,255)|||Thank you

forgot that existed sysindexes|||Thank you very much

name column returned

I have the following sql:

SELECT courseModuleCodeFROM coursesWHERE courseCodelike'%' + @.prefixText +'%'

UNION

SELECTcourseNameFROM coursesWHEREcourseNamelike'%' + @.prefixText +'%'

UNION

SELECTTeacherNameFROM coursesWHERETeacherNamelike'%' + @.prefixText +'%'

it returns one column, i want to name tha column as "words"

how do i do it?

thanks 

SELECT courseModuleCode as 'words' ... and do the rest of the statement the same.

|||

PS:

How many records will your statement return if the following records are in your database:

Here's a real course from a local university.

CourseModuleCodeCourseName

PHYS 212 Heat

TeacherName

Heat

Would the number of records change if you used a UNION ALL instead of a UNION? Why?

|||

thanks, my brain has not been working today!

check this:

http://www.fmsinc.com/FRee/NewTips/SQL/SQLtip5.asp

N UP style reports

Is there a way to create N UP style reports in Reporting Services. N UP
style reports have a newspaper column style of appearance where the same
database column with different data is displayed in multiple columns across
the same page of a report.See if the multi-column functionality meets your need. A fuller discussion
about multi-column reports can be found at
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_layout_v1_4uwj.asp
NOTE: Use must render multi_column reports to PDF, TIFF, or Print Preview to
see them a multi-column. Multi-column reports are rendered as a single
column if you use HTML, Excel, or Preview.
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Reporting Services Developer - Atlanta" <Reporting Services Developer -
Atlanta@.discussions.microsoft.com> wrote in message
news:D216D411-55A0-4014-A787-ACFA5A165C15@.microsoft.com...
> Is there a way to create N UP style reports in Reporting Services. N UP
> style reports have a newspaper column style of appearance where the same
> database column with different data is displayed in multiple columns
> across
> the same page of a report.|||Bruce, thanks for the reply. These reports are typically not printed or
exported. The data is highly dynamic and is viewed/used by our support staff
online (in HTML).
I'll read the information on the link but I'm still interested in knowing if
there is some way to detect the context of a drilldown/row group.
Thanks,
Glenn
"Bruce Johnson [MSFT]" wrote:
> See if the multi-column functionality meets your need. A fuller discussion
> about multi-column reports can be found at
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_layout_v1_4uwj.asp
> NOTE: Use must render multi_column reports to PDF, TIFF, or Print Preview to
> see them a multi-column. Multi-column reports are rendered as a single
> column if you use HTML, Excel, or Preview.
>
> --
> Bruce Johnson [MSFT]
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Reporting Services Developer - Atlanta" <Reporting Services Developer -
> Atlanta@.discussions.microsoft.com> wrote in message
> news:D216D411-55A0-4014-A787-ACFA5A165C15@.microsoft.com...
> > Is there a way to create N UP style reports in Reporting Services. N UP
> > style reports have a newspaper column style of appearance where the same
> > database column with different data is displayed in multiple columns
> > across
> > the same page of a report.
>
>|||The link below provides good information -
but is there a way to overide the "snaking" behavior that is discussed ?
Crystal allows you to specify whether data for these types of reports is
displayed down the page or across the page.
When converting reports from Cyrstal to SRS, it is important to make them
look exactly the same to improve my organization's confidence in SRS. Most
people will see this as a limitation of SRS.
"Bruce Johnson [MSFT]" wrote:
> See if the multi-column functionality meets your need. A fuller discussion
> about multi-column reports can be found at
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_layout_v1_4uwj.asp
> NOTE: Use must render multi_column reports to PDF, TIFF, or Print Preview to
> see them a multi-column. Multi-column reports are rendered as a single
> column if you use HTML, Excel, or Preview.
>
> --
> Bruce Johnson [MSFT]
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Reporting Services Developer - Atlanta" <Reporting Services Developer -
> Atlanta@.discussions.microsoft.com> wrote in message
> news:D216D411-55A0-4014-A787-ACFA5A165C15@.microsoft.com...
> > Is there a way to create N UP style reports in Reporting Services. N UP
> > style reports have a newspaper column style of appearance where the same
> > database column with different data is displayed in multiple columns
> > across
> > the same page of a report.
>
>

N' in criteria ?

Hi,
I have new in SQL server. I am using Access for quite a bit. When I
put the value under Criteria column, in query, It puts N' before the
value, what's that mean ' Why they have to do it ?
Thanks
GinolaThe N denotes that the string is Unicode.
Andrew J. Kelly SQL MVP
"Ginola" <ginola@.mailcity.om> wrote in message
news:40020c51.6962015@.msnews.microsoft.com...

quote:

---

> Hi,
> I have new in SQL server. I am using Access for quite a bit. When I
> put the value under Criteria column, in query, It puts N' before the
> value, what's that mean ' Why they have to do it ?
> Thanks
>
> --
> Ginola

---

|||N indicates that the string after it is a Unicode string. Use N prefix, only
when comparing a value against a Unicode column, otherwise you will see
performance degradation, as an index may not be used.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Ginola" <ginola@.mailcity.om> wrote in message
news:40020c51.6962015@.msnews.microsoft.com...
Hi,
I have new in SQL server. I am using Access for quite a bit. When I
put the value under Criteria column, in query, It puts N' before the
value, what's that mean ' Why they have to do it ?
Thanks
Ginola|||thanks all for helping.
On Mon, 12 Jan 2004 10:52:06 -0000, "Narayana Vyas Kondreddi"
<answer_me@.hotmail.com> wrote:

quote:

---

>N indicates that the string after it is a Unicode string. Use N prefix, onl
y
>when comparing a value against a Unicode column, otherwise you will see
>performance degradation, as an index may not be used.

---

Ginola

N' in criteria ?

Hi,
I have new in SQL server. I am using Access for quite a bit. When I
put the value under Criteria column, in query, It puts N' before the
value, what's that mean ' Why they have to do it ?
Thanks
--
GinolaThe N denotes that the string is Unicode.
--
Andrew J. Kelly SQL MVP
"Ginola" <ginola@.mailcity.om> wrote in message
news:40020c51.6962015@.msnews.microsoft.com...
> Hi,
> I have new in SQL server. I am using Access for quite a bit. When I
> put the value under Criteria column, in query, It puts N' before the
> value, what's that mean ' Why they have to do it ?
> Thanks
>
> --
> Ginola|||N indicates that the string after it is a Unicode string. Use N prefix, only
when comparing a value against a Unicode column, otherwise you will see
performance degradation, as an index may not be used.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Ginola" <ginola@.mailcity.om> wrote in message
news:40020c51.6962015@.msnews.microsoft.com...
Hi,
I have new in SQL server. I am using Access for quite a bit. When I
put the value under Criteria column, in query, It puts N' before the
value, what's that mean ' Why they have to do it ?
Thanks
Ginola|||thanks all for helping.
On Mon, 12 Jan 2004 10:52:06 -0000, "Narayana Vyas Kondreddi"
<answer_me@.hotmail.com> wrote:
>N indicates that the string after it is a Unicode string. Use N prefix, only
>when comparing a value against a Unicode column, otherwise you will see
>performance degradation, as an index may not be used.
--
Ginola

MyTable, ServerTimeDimension, Null-Values

Hello experts,

I’ve got one Problem and four solutions, but none is a good one =(

My entire problem:

I’ve got a Dimension with a column Production_Date. I would like to relate it with a ServerTimeDimension. I created a ServerTimeDimension and related about the Dimension Usage it to my Dimension. In the Production_Date column are some Null values and now I’ve got some Problems.

My four solutions:

1. to alter my dimension through one query and take just this rows with correct date à not good, because I don’t want to lose some Information
2. to create a new named Calculation and alter all Null values through a very old date. Something like this:
   case when (INS_PRODUCTION_DATE is null)
   then '01.01.1969'
   else (CONVERT(VARCHAR(10), INS_PRODUCTION_DATE, 104))
   end à there are some wrong information now, that’s not so good too
3. make a time dimension one this column à not so good, because I can’t make a hierarchy right now
4. and to work with the ErrorConfiguration and KeyErrorLimit is the worst solution

Have somebody another good idea?

Best regards

Tschi2001

As a best practice, it is recommended you implement a date dimension table in your data warehouse and then add foreign key references to your fact table. The NULL date can be represented easily with this approach.

If that is not an option here, what is wrong with the UnknownMember/ErrorConfiguration option (Option 4)? This type of situation is just what that is for.

B.

|||

Hello Bryan,

You are right, that’s not a option here.

In my Opinion a correct cube have to work without use ErrorConfiguration.

And the Option UnknownMember doesn’t work with my cube or I make something wrong.

The way I try to solve this problem with UnknownMember:

- I take the Production Time Dimension (it’s my ServerTimeDimension)

- Went to Dimension Structure

- Look at the Date-Properties (it’s the Key of this Dimension)

- Went to KeyColumns

- Set NullProcessing to “ZeroOrBlank”

- Try to deploy

Came the old failure…

Made I something wrong or could it be that I misunderstand something?

Best regards

Alexander

|||

So, the first thing you need to do is configure the UnknownMember in your time dimension. To do this, set the UnknownMember property on the dimension to either Hidden of Visible. Then, open the cube and move to the Cube Structure tab and select each measure group referencing the time dimension. Set up the ErrorConfiguration as follows:

NullKeyConvertedToUnknown = IgnoreError

KeyErrorLimitAction = Stop Logging

Try that and see if that fixes the problem for you.

B.

|||

Thank you very much, it works very good !!!!

MyTable, ServerTimeDimension, Null-Values

Hello experts,

I’ve got one Problem and four solutions, but none is a good one =(

My entire problem:

I’ve got a Dimension with a column Production_Date. I would like to relate it with a ServerTimeDimension. I created a ServerTimeDimension and related about the Dimension Usage it to my Dimension. In the Production_Date column are some Null values and now I’ve got some Problems.

My four solutions:

1. to alter my dimension through one query and take just this rows with correct date à not good, because I don’t want to lose some Information
2. to create a new named Calculation and alter all Null values through a very old date. Something like this:
   case when (INS_PRODUCTION_DATE is null)
   then '01.01.1969'
   else (CONVERT(VARCHAR(10), INS_PRODUCTION_DATE, 104))
   end à there are some wrong information now, that’s not so good too
3. make a time dimension one this column à not so good, because I can’t make a hierarchy right now
4. and to work with the ErrorConfiguration and KeyErrorLimit is the worst solution

Have somebody another good idea?

Best regards

Tschi2001

As a best practice, it is recommended you implement a date dimension table in your data warehouse and then add foreign key references to your fact table. The NULL date can be represented easily with this approach.

If that is not an option here, what is wrong with the UnknownMember/ErrorConfiguration option (Option 4)? This type of situation is just what that is for.

B.

|||

Hello Bryan,

You are right, that’s not a option here.

In my Opinion a correct cube have to work without use ErrorConfiguration.

And the Option UnknownMember doesn’t work with my cube or I make something wrong.

The way I try to solve this problem with UnknownMember:

- I take the Production Time Dimension (it’s my ServerTimeDimension)

- Went to Dimension Structure

- Look at the Date-Properties (it’s the Key of this Dimension)

- Went to KeyColumns

- Set NullProcessing to “ZeroOrBlank”

- Try to deploy

Came the old failure…

Made I something wrong or could it be that I misunderstand something?

Best regards

Alexander

|||

So, the first thing you need to do is configure the UnknownMember in your time dimension. To do this, set the UnknownMember property on the dimension to either Hidden of Visible. Then, open the cube and move to the Cube Structure tab and select each measure group referencing the time dimension. Set up the ErrorConfiguration as follows:

NullKeyConvertedToUnknown = IgnoreError

KeyErrorLimitAction = Stop Logging

Try that and see if that fixes the problem for you.

B.

|||

Thank you very much, it works very good !!!!

Mystery Database IDs in Profiler

I'm currently running Profiler sessions to track down Lock Timeout
problems.

My Profiler view contains (amongst others) the dbid column.

Much of the time, this displays familiar dbids, such as 2 (tempdb) and
5 (my main user db). However, it also regularly displays IDs of 0 and
132.

Using SELECT DB_NAME(), these translate as "master" and "NULL"
respectively.

Does anyone know:
a) why dbid = 0 translates to "master", when the actual id of this
database is 1, and
b) why Profiler reports these dbids in the first place?Hi

Database ID is a default data column and should be populated for all events.
What other columns are you capturing? Which event is displaying this value?

Not directly related to your question but these may help:
http://support.microsoft.com/defaul...kb;en-us;832524
http://msdn.microsoft.com/library/d...atabse_5xrn.asp
http://support.microsoft.com/kb/271509/EN-US/

John

"Phil" <philip.yale@.gmail.com> wrote in message
news:1109931995.630468.189250@.g14g2000cwa.googlegr oups.com...
> I'm currently running Profiler sessions to track down Lock Timeout
> problems.
> My Profiler view contains (amongst others) the dbid column.
> Much of the time, this displays familiar dbids, such as 2 (tempdb) and
> 5 (my main user db). However, it also regularly displays IDs of 0 and
> 132.
> Using SELECT DB_NAME(), these translate as "master" and "NULL"
> respectively.
> Does anyone know:
> a) why dbid = 0 translates to "master", when the actual id of this
> database is 1, and
> b) why Profiler reports these dbids in the first place?|||"John Bell" <jbellnewsposts@.hotmail.com> wrote in message news:<42299950$0$8744$db0fefd9@.news.zen.co.uk>...
> Hi
> Database ID is a default data column and should be populated for all events.
> What other columns are you capturing? Which event is displaying this value?

Have variously tried capturing just the columns I thought I needed,
and then finally capturing every column possible just in case this
altered any output!

The only event I'm capturing is the LOCK TIMEOUT event. I've since
noticed that these lock timeouts (with the dbid equal to 0 or 255) all
have the same lock type (which I can't remember right now, and I'm
nowhere that I can look it up conveniently), which essentially
translates to a "key range test for a record insert". i.e. they're
not proper locks at all, and so the timeout is almost certainly an
internal mechanism for releasing them

I was just intrigued why these were associated with a dbid at all, why
0 and 255 was chosen, and why 0 additionally translates to 'master'.

BTW - genuine lock timeouts correctly display valid database IDs.

> Not directly related to your question but these may help:
> http://support.microsoft.com/defaul...kb;en-us;832524
> http://msdn.microsoft.com/library/d...atabse_5xrn.asp
> http://support.microsoft.com/kb/271509/EN-US/
>
> John
> "Phil" <philip.yale@.gmail.com> wrote in message
> news:1109931995.630468.189250@.g14g2000cwa.googlegr oups.com...
> > I'm currently running Profiler sessions to track down Lock Timeout
> > problems.
> > My Profiler view contains (amongst others) the dbid column.
> > Much of the time, this displays familiar dbids, such as 2 (tempdb) and
> > 5 (my main user db). However, it also regularly displays IDs of 0 and
> > 132.
> > Using SELECT DB_NAME(), these translate as "master" and "NULL"
> > respectively.
> > Does anyone know:
> > a) why dbid = 0 translates to "master", when the actual id of this
> > database is 1, and
> > b) why Profiler reports these dbids in the first place?|||Hi

It sounds like the INDID which will can be 0 or 255.

John

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Mysterious text in query result

Hi,
I have a database with a column defined as varchar(100) called Surname.
When I use Query Analyzer to retrieve data from the column and display as
text, I get strange text appearing for one of the records. Thus, the field
contains "Wilson" but when I run the following:
SELECT '''' + Surname + '''', Surnname FROM tblManagers
WHERE FirstName = 'Glenn'
I get the following result:
'Wilson d g e d w i t h t h e a ' Wilson
Where has the "d ged with the a" come from. If I show the results in a grid
the final apostrophe is missing from the first part of the query:
'Wilson
If I run a query using ADO and show the result on a web page, the extra text
is not shown.
Any ideas what is going on here?
Glenn
[Reposted, as posts from outside msnews.microsoft.com does not seem to make
it in.]
BigMan2001 (BigMan2001@.discussions.microsoft.com) writes:
> I have a database with a column defined as varchar(100) called Surname.
> When I use Query Analyzer to retrieve data from the column and display
> as text, I get strange text appearing for one of the records. Thus, the
> field contains "Wilson" but when I run the following:
> SELECT '''' + Surname + '''', Surnname FROM tblManagers
> WHERE FirstName = 'Glenn'
> I get the following result:
> 'Wilson d g e d w i t h t h e a ' Wilson
> Where has the "d ged with the a" come from. If I show the results in a
> grid the final apostrophe is missing from the first part of the query:
> 'Wilson
> If I run a query using ADO and show the result on a web page, the extra
> text is not shown.
Looks like some junk data slipped in, and there is a NUL character hiding
there. See what
SELECT convert(varbinary(100), Surname) FROM tblManagers
WHERE FisttName = 'Glenn'
returns.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.seBooks Online for SQL
Server 2005
athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000
athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

My Table Is Empty

I just created a new report and the table header is visible, as is the
first column, but all of the other columns are blank. The first column
has a product name, and the others have sales data, etc.
I can run the query from the data tab in VS, and it works fine. But
the preview and live version both have blank values in the main part of
hte table.
Why?Hi,
Have you checked the mapping of your query columns with the dataset return
values? Press Ctrl+Alt+D to make then visible if they aren't.
Jan Pieter Posthuma
"John" wrote:
> I just created a new report and the table header is visible, as is the
> first column, but all of the other columns are blank. The first column
> has a product name, and the others have sales data, etc.
> I can run the query from the data tab in VS, and it works fine. But
> the preview and live version both have blank values in the main part of
> hte table.
> Why?
>|||Yep. I even tried to create a new report and it did not work. I'm
totally lost on this one.|||Man, this was all me. My stored procedure had a little glitch. It
really threw me though since the first column was populated correctly.
Thanks for the help Jan Pieter Posthuma

My SSIS problems text file, date data types

There are things I just don't get about MS programming:

1. Text file source - recognizes column as "database date [DT_DBDATE]" - but not able to process it as date - values like "2006-12-06T13:16:04"

2. When using import export wizard - it errors out when trying to transfer large amount of rows (40 million) - 1st time out of space error - but the db is in simple recovery model - and even though I increased logs to max & moved them to different HDD - I DO NOT NEED the logging.

3. MS Full text search & full-text index performace issues - our keyword search more often than not times out (it's probably not related to SSIS - but if anyone has suggestions - please do)

I just wish that I could spend more time on doing stuff - so any help on those problems are appreciated.
Thanks,The fact I got problem no.2 makes me look stupid - and I don't like to look stupid - so therefore I blame the thing which made me look stupid = MS.

What you need to do to solve number 2 is: save wizard output to SSIS package, change destination setting "Maximum insert commit size" to appropriate value (1mill in my case).

By default Import/Export wizard tries to save all records in 1 transaction - BUT saving 40 million rows in 1 shot (even in simple recovery model) is STUPID STUPID STUPID|||

TheViewMaster wrote:

By default Import/Export wizard tries to save all records in 1 transaction - BUT saving 40 million rows in 1 shot (even in simple recovery model) is STUPID STUPID STUPID

I disagree. What should the default be, then?|||If only they'd add the mind reading interface I keep requesting |||

Perhaps another way to look at this is in the context of "reasonable defaults." I would not expect the default import/export size to be 40 M rows, so I personally do not think that the package created by default should need to handle this data volume without some sort of review or modification.

Still, it would not hurt to have an "Advanced Options" (or whatever - pick your name) page in the wizard that asked data size and volume questions, and set some of these properties more intelligently based on this input.

|||

MatthewRoche wrote:

Still, it would not hurt to have an "Advanced Options" (or whatever - pick your name) page in the wizard that asked data size and volume questions, and set some of these properties more intelligently based on this input.

This is a good idea. Either yourself or TheViewMaster should post a Connect feedback item requesting this feature to be added.|||

Phil Brammer wrote:

MatthewRoche wrote:

Still, it would not hurt to have an "Advanced Options" (or whatever - pick your name) page in the wizard that asked data size and volume questions, and set some of these properties more intelligently based on this input.

This is a good idea. Either yourself or TheViewMaster should post a Connect feedback item requesting this feature to be added.

Posted: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=278281

Odd that there is no SSIS category available...

|||

I'm having a heck of a time converting a very simple text file import dts package (takes 2 minutes to create the whole thing in dts). Initially, I had the same problem as you - couldn't get the date columns to work no matter what I tried. Finally I changed the destination columns to strings (now I'll see if I can convert the strings in that table to another table with the proper 'date' data type.

I'm also having trouble with the thing telling me it can't import a column (from the same file) because of potential data loss (I've tried real, float, numeric types). The source data is not that large (less than 10,000,000.00).

I 've been a huge fan of Microsoft, SQL Server (especially dts) since SS 7.0. So far, this is the worst software product I've ever encountered.

|||

jw6587 wrote:

I'm having a heck of a time converting a very simple text file import dts package (takes 2 minutes to create the whole thing in dts). Initially, I had the same problem as you - couldn't get the date columns to work no matter what I tried. Finally I changed the destination columns to strings (now I'll see if I can convert the strings in that table to another table with the proper 'date' data type.

I'm also having trouble with the thing telling me it can't import a column (from the same file) because of potential data loss (I've tried real, float, numeric types). The source data is not that large (less than 10,000,000.00).

I 've been a huge fan of Microsoft, SQL Server (especially dts) since SS 7.0. So far, this is the worst software product I've ever encountered.

Wow.

I certainly cannot argue with your experience, but for me the opposite was true. For me, DTS was practically impossible to work with for anything but the most trivial uses. My standard line when speaking on BI topics is that would prefer to chew off my own mouse finger rather than work with DTS on a project. SSIS certainly has its faults (and upgeading DTS packages is definitely not one of its strengths), but for data warehousing and other real-world ETL purposes it is light-years ahead of DTS.

|||

MatthewRoche wrote:

Posted: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=278281

Odd that there is no SSIS category available...

There is... Sort of. It's DTS.

Can you edit the posting and change it to DTS?

Thanks,
Phil|||

Phil Brammer wrote:

MatthewRoche wrote:

Posted: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=278281

Odd that there is no SSIS category available...

There is... Sort of. It's DTS.

Can you edit the posting and change it to DTS?

Thanks,
Phil

Apparently not. The only field that I can edit is the private/public flag.

|||

MatthewRoche wrote:

Apparently not. The only field that I can edit is the private/public flag.

That is weird. I just checked one of mine and I can edit most fields. Especially the category drop down, which is what needs to be changed in this case. I can't offer any help on that, I guess.|||Awesome feedback (and at least I am not the only one who experiences technical difficulties with this product ) - thanks guys.

The only improvement over DTS I see is the error handling/logging mechanism. Since working with SSIS close to a year now - I've trimmed down my toolbox to just "Execute SQL", "Script Task" and Very Simple data-flow.

One thing I've spent a lot of time on though is setting up the proper data types for text file (I've already moaned about how cumbersome that is - there should be a way to input your format file instead of typing & mouse selecting) - but by looking that SSIS can't handle datetime + it is difficult to correct production SSIS package when text file field changes (with the amount of files we process it happens quite often - few times a month perhaps) - our consultant suggested to import all data as string to database - and then use SQL to do conversions. In that case when a file changes - you only need to modify your SQL (views)

So what are your thoughts about trimming down SSIS to just importing everything as text to database?|||

TheViewMaster wrote:

So what are your thoughts about trimming down SSIS to just importing everything as text to database?

My thoughts are its a stupid idea.

My SSIS problems text file, date data types

There are things I just don't get about MS programming:

1. Text file source - recognizes column as "database date [DT_DBDATE]" - but not able to process it as date - values like "2006-12-06T13:16:04"

2. When using import export wizard - it errors out when trying to transfer large amount of rows (40 million) - 1st time out of space error - but the db is in simple recovery model - and even though I increased logs to max & moved them to different HDD - I DO NOT NEED the logging.

3. MS Full text search & full-text index performace issues - our keyword search more often than not times out (it's probably not related to SSIS - but if anyone has suggestions - please do)

I just wish that I could spend more time on doing stuff - so any help on those problems are appreciated.
Thanks,The fact I got problem no.2 makes me look stupid - and I don't like to look stupid - so therefore I blame the thing which made me look stupid = MS.

What you need to do to solve number 2 is: save wizard output to SSIS package, change destination setting "Maximum insert commit size" to appropriate value (1mill in my case).

By default Import/Export wizard tries to save all records in 1 transaction - BUT saving 40 million rows in 1 shot (even in simple recovery model) is STUPID STUPID STUPID|||

TheViewMaster wrote:

By default Import/Export wizard tries to save all records in 1 transaction - BUT saving 40 million rows in 1 shot (even in simple recovery model) is STUPID STUPID STUPID

I disagree. What should the default be, then?|||If only they'd add the mind reading interface I keep requesting |||

Perhaps another way to look at this is in the context of "reasonable defaults." I would not expect the default import/export size to be 40 M rows, so I personally do not think that the package created by default should need to handle this data volume without some sort of review or modification.

Still, it would not hurt to have an "Advanced Options" (or whatever - pick your name) page in the wizard that asked data size and volume questions, and set some of these properties more intelligently based on this input.

|||

MatthewRoche wrote:

Still, it would not hurt to have an "Advanced Options" (or whatever - pick your name) page in the wizard that asked data size and volume questions, and set some of these properties more intelligently based on this input.

This is a good idea. Either yourself or TheViewMaster should post a Connect feedback item requesting this feature to be added.|||

Phil Brammer wrote:

MatthewRoche wrote:

Still, it would not hurt to have an "Advanced Options" (or whatever - pick your name) page in the wizard that asked data size and volume questions, and set some of these properties more intelligently based on this input.

This is a good idea. Either yourself or TheViewMaster should post a Connect feedback item requesting this feature to be added.

Posted: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=278281

Odd that there is no SSIS category available...

|||

I'm having a heck of a time converting a very simple text file import dts package (takes 2 minutes to create the whole thing in dts). Initially, I had the same problem as you - couldn't get the date columns to work no matter what I tried. Finally I changed the destination columns to strings (now I'll see if I can convert the strings in that table to another table with the proper 'date' data type.

I'm also having trouble with the thing telling me it can't import a column (from the same file) because of potential data loss (I've tried real, float, numeric types). The source data is not that large (less than 10,000,000.00).

I 've been a huge fan of Microsoft, SQL Server (especially dts) since SS 7.0. So far, this is the worst software product I've ever encountered.

|||

jw6587 wrote:

I'm having a heck of a time converting a very simple text file import dts package (takes 2 minutes to create the whole thing in dts). Initially, I had the same problem as you - couldn't get the date columns to work no matter what I tried. Finally I changed the destination columns to strings (now I'll see if I can convert the strings in that table to another table with the proper 'date' data type.

I'm also having trouble with the thing telling me it can't import a column (from the same file) because of potential data loss (I've tried real, float, numeric types). The source data is not that large (less than 10,000,000.00).

I 've been a huge fan of Microsoft, SQL Server (especially dts) since SS 7.0. So far, this is the worst software product I've ever encountered.

Wow.

I certainly cannot argue with your experience, but for me the opposite was true. For me, DTS was practically impossible to work with for anything but the most trivial uses. My standard line when speaking on BI topics is that would prefer to chew off my own mouse finger rather than work with DTS on a project. SSIS certainly has its faults (and upgeading DTS packages is definitely not one of its strengths), but for data warehousing and other real-world ETL purposes it is light-years ahead of DTS.

|||

MatthewRoche wrote:

Posted: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=278281

Odd that there is no SSIS category available...

There is... Sort of. It's DTS.

Can you edit the posting and change it to DTS?

Thanks,
Phil|||

Phil Brammer wrote:

MatthewRoche wrote:

Posted: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=278281

Odd that there is no SSIS category available...

There is... Sort of. It's DTS.

Can you edit the posting and change it to DTS?

Thanks,
Phil

Apparently not. The only field that I can edit is the private/public flag.

|||

MatthewRoche wrote:

Apparently not. The only field that I can edit is the private/public flag.

That is weird. I just checked one of mine and I can edit most fields. Especially the category drop down, which is what needs to be changed in this case. I can't offer any help on that, I guess.|||Awesome feedback (and at least I am not the only one who experiences technical difficulties with this product ) - thanks guys.

The only improvement over DTS I see is the error handling/logging mechanism. Since working with SSIS close to a year now - I've trimmed down my toolbox to just "Execute SQL", "Script Task" and Very Simple data-flow.

One thing I've spent a lot of time on though is setting up the proper data types for text file (I've already moaned about how cumbersome that is - there should be a way to input your format file instead of typing & mouse selecting) - but by looking that SSIS can't handle datetime + it is difficult to correct production SSIS package when text file field changes (with the amount of files we process it happens quite often - few times a month perhaps) - our consultant suggested to import all data as string to database - and then use SQL to do conversions. In that case when a file changes - you only need to modify your SQL (views)

So what are your thoughts about trimming down SSIS to just importing everything as text to database?|||

TheViewMaster wrote:

So what are your thoughts about trimming down SSIS to just importing everything as text to database?

My thoughts are its a stupid idea.

My SSIS problems

There are things I just don't get about MS programming:

1. Text file source - recognizes column as "database date [DT_DBDATE]" - but not able to process it as date - values like "2006-12-06T13:16:04"

2. When using import export wizard - it errors out when trying to transfer large amount of rows (40 million) - 1st time out of space error - but the db is in simple recovery model - and even though I increased logs to max & moved them to different HDD - I DO NOT NEED the logging.

3. MS Full text search & full-text index performace issues - our keyword search more often than not times out (it's probably not related to SSIS - but if anyone has suggestions - please do)

I just wish that I could spend more time on doing stuff - so any help on those problems are appreciated.
Thanks,The fact I got problem no.2 makes me look stupid - and I don't like to look stupid - so therefore I blame the thing which made me look stupid = MS.

What you need to do to solve number 2 is: save wizard output to SSIS package, change destination setting "Maximum insert commit size" to appropriate value (1mill in my case).

By default Import/Export wizard tries to save all records in 1 transaction - BUT saving 40 million rows in 1 shot (even in simple recovery model) is STUPID STUPID STUPID|||

TheViewMaster wrote:

By default Import/Export wizard tries to save all records in 1 transaction - BUT saving 40 million rows in 1 shot (even in simple recovery model) is STUPID STUPID STUPID

I disagree. What should the default be, then?|||If only they'd add the mind reading interface I keep requesting |||

Perhaps another way to look at this is in the context of "reasonable defaults." I would not expect the default import/export size to be 40 M rows, so I personally do not think that the package created by default should need to handle this data volume without some sort of review or modification.

Still, it would not hurt to have an "Advanced Options" (or whatever - pick your name) page in the wizard that asked data size and volume questions, and set some of these properties more intelligently based on this input.

|||

MatthewRoche wrote:

Still, it would not hurt to have an "Advanced Options" (or whatever - pick your name) page in the wizard that asked data size and volume questions, and set some of these properties more intelligently based on this input.

This is a good idea. Either yourself or TheViewMaster should post a Connect feedback item requesting this feature to be added.|||

Phil Brammer wrote:

MatthewRoche wrote:

Still, it would not hurt to have an "Advanced Options" (or whatever - pick your name) page in the wizard that asked data size and volume questions, and set some of these properties more intelligently based on this input.

This is a good idea. Either yourself or TheViewMaster should post a Connect feedback item requesting this feature to be added.

Posted: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=278281

Odd that there is no SSIS category available...

|||

I'm having a heck of a time converting a very simple text file import dts package (takes 2 minutes to create the whole thing in dts). Initially, I had the same problem as you - couldn't get the date columns to work no matter what I tried. Finally I changed the destination columns to strings (now I'll see if I can convert the strings in that table to another table with the proper 'date' data type.

I'm also having trouble with the thing telling me it can't import a column (from the same file) because of potential data loss (I've tried real, float, numeric types). The source data is not that large (less than 10,000,000.00).

I 've been a huge fan of Microsoft, SQL Server (especially dts) since SS 7.0. So far, this is the worst software product I've ever encountered.

|||

jw6587 wrote:

I'm having a heck of a time converting a very simple text file import dts package (takes 2 minutes to create the whole thing in dts). Initially, I had the same problem as you - couldn't get the date columns to work no matter what I tried. Finally I changed the destination columns to strings (now I'll see if I can convert the strings in that table to another table with the proper 'date' data type.

I'm also having trouble with the thing telling me it can't import a column (from the same file) because of potential data loss (I've tried real, float, numeric types). The source data is not that large (less than 10,000,000.00).

I 've been a huge fan of Microsoft, SQL Server (especially dts) since SS 7.0. So far, this is the worst software product I've ever encountered.

Wow.

I certainly cannot argue with your experience, but for me the opposite was true. For me, DTS was practically impossible to work with for anything but the most trivial uses. My standard line when speaking on BI topics is that would prefer to chew off my own mouse finger rather than work with DTS on a project. SSIS certainly has its faults (and upgeading DTS packages is definitely not one of its strengths), but for data warehousing and other real-world ETL purposes it is light-years ahead of DTS.

|||

MatthewRoche wrote:

Posted: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=278281

Odd that there is no SSIS category available...

There is... Sort of. It's DTS.

Can you edit the posting and change it to DTS?

Thanks,
Phil|||

Phil Brammer wrote:

MatthewRoche wrote:

Posted: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=278281

Odd that there is no SSIS category available...

There is... Sort of. It's DTS.

Can you edit the posting and change it to DTS?

Thanks,
Phil

Apparently not. The only field that I can edit is the private/public flag.

|||

MatthewRoche wrote:

Apparently not. The only field that I can edit is the private/public flag.

That is weird. I just checked one of mine and I can edit most fields. Especially the category drop down, which is what needs to be changed in this case. I can't offer any help on that, I guess.|||Awesome feedback (and at least I am not the only one who experiences technical difficulties with this product ) - thanks guys.

The only improvement over DTS I see is the error handling/logging mechanism. Since working with SSIS close to a year now - I've trimmed down my toolbox to just "Execute SQL", "Script Task" and Very Simple data-flow.

One thing I've spent a lot of time on though is setting up the proper data types for text file (I've already moaned about how cumbersome that is - there should be a way to input your format file instead of typing & mouse selecting) - but by looking that SSIS can't handle datetime + it is difficult to correct production SSIS package when text file field changes (with the amount of files we process it happens quite often - few times a month perhaps) - our consultant suggested to import all data as string to database - and then use SQL to do conversions. In that case when a file changes - you only need to modify your SQL (views)

So what are your thoughts about trimming down SSIS to just importing everything as text to database?|||

TheViewMaster wrote:

So what are your thoughts about trimming down SSIS to just importing everything as text to database?

My thoughts are its a stupid idea.