n00b: best way to import flat file with BIDS

Hi all,

I'm totally new to SQL Server 2k5 and need to do something rather basic: import some CSV files into tables. I'm getting translation errors and would like to know what's the best way to cast the strings before inserts.

I'm doing the import in BI Development Studio.

Current situation:

Created connection managers to csv files
created SQL server destinations pointing to the tables
connected them directly with a dataflow path
Ran the packadge: one import went just fine, the other one complains about conversion errors like "Conversion DT_STR and DT_I4 not supported"

Both tables have the same kind of fields (varchar, float, datetime, int)

I looked at converting the data using a transformation but am somewhat confused of which one to use.

What's the best way to transform the data before insert: derived column, import column or data conversion? Or something else I overlooked?

TIA

PeterFor doing this kind of work, I prefer to use bcp.exe over SSIS/DTS. It's a simple command line utility.

Look for "bcp utility" in BOL.

n00b question: Connecting to SQL Server at ISP?

Hey all, sorry for asking such a grunt question, and thanks in advance
for any help...

My hosting ISP offers access to their MS-SQL database, and my
understanding is that it's generally simplest to use some sort of
client app to do stuff like adding tables and whatnot.

SQL Server includes Enterprise Manager, but since my ISP is running the
server, I don't need my own copy of SQL Server, just the client.

Any tips? I couldn't seem to find an appropriate download on the
Microsoft site...

thanks again, -Scottturnstyle (scott@.turnstyle.com) writes:
> Hey all, sorry for asking such a grunt question, and thanks in advance
> for any help...
> My hosting ISP offers access to their MS-SQL database, and my
> understanding is that it's generally simplest to use some sort of
> client app to do stuff like adding tables and whatnot.
> SQL Server includes Enterprise Manager, but since my ISP is running the
> server, I don't need my own copy of SQL Server, just the client.
> Any tips? I couldn't seem to find an appropriate download on the
> Microsoft site...

You could download Evaluation Edition. I believe that the tools don't
expire. But you may violate some license that way.

Developer Edition is 49 dollars only. Then again, if this is a production
database, I don't know if the license permits use.

As for creating tables, the best is to run T-SQL script. Then
again, the best tool for this is Query Analyzer, which is among
the SQL Server Tools you are looking for.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||So there isn't an authorized way to simply download the client tools in
order to connect to a properly licensed server?

1) Does anybody here know if Evaluation Edition tools expire?

2) Another option seems to be Aqua Data Studio
(http://www.aquafold.com) -- should I use something like that, or stick
with Enterprise Manager and its Query Tool?

3) I think I have a copy of SQL-7 somewhere, I suppose I could use that
client, but are the client tools now much better with 2000?

4) Sorry, one last question -- I actually *tried* to purchase an
upgrade from SQL-7 to SQL-2000 but I couldn't figure out what I was
supposed to get -- any tips on how to actually do that?

thanks again & sorry for so many questions...
-Scott|||turnstyle (scott@.turnstyle.com) writes:
> So there isn't an authorized way to simply download the client tools in
> order to connect to a properly licensed server?

No, I don't think so. But I've directed a question on my Microsoft contacts
to see what the exact status is.

> 2) Another option seems to be Aqua Data Studio
> (http://www.aquafold.com) -- should I use something like that, or stick
> with Enterprise Manager and its Query Tool?

I can comment on any third party tools, as I have not use them. But
indeed that may be the only option.

> 3) I think I have a copy of SQL-7 somewhere, I suppose I could use that
> client, but are the client tools now much better with 2000?

I would expect Query Analyzer from SQL 7 to be able to connect, although
it would be able to handle bigint and sql_variant columns correctly. As
I recall QA in SQL 7 was quite a bleak tool; had I at the time had reason to
work with SQL 7, I would probably have stuck to the 6.5 tools. (As it
turned out, we went directly to SQL 2000.)

EM 7 may be barred from access altogether, but you could always try.
Again, bigint and sql_variant, will not make a huge success. I
can't comment on how much better EM 2000 is than EM 7, but I can't
say I'm overly excited over EM 2000.

> 4) Sorry, one last question -- I actually *tried* to purchase an
> upgrade from SQL-7 to SQL-2000 but I couldn't figure out what I was
> supposed to get -- any tips on how to actually do that?

I guess you would get the edition of SQL 2000 that matches your SQL 7
license. If that's a developer license, I would not expect that there
are any special upgrades available today, since DevEdition been slashed
to 50 USD.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Am 29 Jul 2005 03:28:11 -0700 schrieb turnstyle:

> So there isn't an authorized way to simply download the client tools in
> order to connect to a properly licensed server?
> 1) Does anybody here know if Evaluation Edition tools expire?
> 2) Another option seems to be Aqua Data Studio
> (http://www.aquafold.com) -- should I use something like that, or stick
> with Enterprise Manager and its Query Tool?
For managing SQL-Server there is a nice free Tool here:
http://sqlmanager.net/products/mssql/manager/
look for the Lite version.

bye,
Helmut

n00b Question Re: MSDE SQL Server Install

I am trying to install some code on my website which is called EasyDisk. It
creates a forum, like any forum you see anywhere. Anyways it requires an SQL
Server, so I acquired MSDE SQL Server 2000. I created an instance of it, and
can see the folder on my C drive. The instance is called
MYFIRSTDBMSSQL$MYFIRSTDB oddly enough since I guess I didn't understand the
instruction for naming an instance. In any case, the forum software requires
I know 1. The name of my SQL Server, 2. My username 3. My password and 4. My
database name.
So, best I can tell my db name is my instance name, and my username and
password are what I use to log onto Windows XP. How far off am I? ... and I
have no idea what my sql server name is. How do I find out? The Books online
is no help whatsoever in this regard.
Thanks,
Jim
hi Jim,
"newgenre" <newgenre@.mindspring.com> ha scritto nel messaggio
news:%xchc.3922$e4.2441@.newsread2.news.pas.earthli nk.net...
>.....
> I know 1. The name of my SQL Server, 2. My username 3. My password and 4.
My
> database name.
> So, best I can tell my db name is my instance name, and my username and
> password are what I use to log onto Windows XP. How far off am I? ... and
I
> have no idea what my sql server name is. How do I find out? The Books
online
> is no help whatsoever in this regard.
your instance name shoul'd be MYFIRSTDB (and you can verify this opening
your service management applet, in the part right to the $ sign)... the
service manager in your tray bar area shoul'd provide this information
too...
so the full instance name is YourComputerName\MYFIRSTDB
your db name for the connection string is not the instance name, but the
database you want to connect to..
as regard your credential, it depends on the authentication mode you want to
connect to SQL Server with..
SQL Server accepst 2 kinds of authentication mode:
trusted (WinNT) authentication, where you are not required to provide user's
information;
SQL Server authentication, where you have to provide them in the form of
"user id=username;password=yourStrongPwd;" as indicated in
http://www.connectionstrings.com/ ...
you can find more about authentication modes beginning at
http://msdn.microsoft.com/library/de...setup_6p9v.asp
you can find which kind of authentication your MSDE instance supports by
inspecting the registry at the
HKLM\Software\Microsoft\Microsoft SQL
Server\YourInstanceName\MSSQLServer\LoginMode key
a value of 0 or 2 specifies Mixed mode (both modes), while a value of 1
spefies truste authentication only.
hth
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.7.0 - DbaMgr ver 0.53.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
sql

n00b Question Re: MSDE SQL Server Install

Sorry if I sound to simple for you, I'm not sure what your
experience is, and this is how I understand it best.)
SQL Server runs as a Service. That's an app that runs in
the background.
You can have more that one 'Instance' of SQL Server
running at a time. (The number allowed depends on the
version you install.) This allows, let's say, your Website
database running in one instance to crash without having
your accounting db (on another instance) going down also.
On each instance of SQL Server, you can have a number of
Databases.
Hopes that clears some things up. Did you install MSDE (MS
SQL Server Desktop Edition) or SQL Server
Standard/Enterprise/Personal edition?
MSDE does not install some management tools that make
things real easy.
It was Desktop Edition.
"M K" <mark@.nospamcenturycolor.com> wrote in message
news:252001c427c3$54b21740$a001280a@.phx.gbl...
> Sorry if I sound to simple for you, I'm not sure what your
> experience is, and this is how I understand it best.)
> SQL Server runs as a Service. That's an app that runs in
> the background.
> You can have more that one 'Instance' of SQL Server
> running at a time. (The number allowed depends on the
> version you install.) This allows, let's say, your Website
> database running in one instance to crash without having
> your accounting db (on another instance) going down also.
> On each instance of SQL Server, you can have a number of
> Databases.
> Hopes that clears some things up. Did you install MSDE (MS
> SQL Server Desktop Edition) or SQL Server
> Standard/Enterprise/Personal edition?
> MSDE does not install some management tools that make
> things real easy.
|||Yea, Desktop Edition, MSDE, is the redistribution version.
Stripped down. I would think your app would create the
database for itself.
If you don't have a version of SQL running you can install
MSDE with mostly defaults. You don't need to set an
Instance. Then install the app and it should set up the
database.
I hate it when I don't have enough documentation, don't
you.

n00b question - permissions from website

Hi everyone,

I've inherited a website based on the dotnetnuke frameowork.

this website also uses the reporting services from sqlserver.

When the report button is clicked in the website, the user is prompted

with a login and password (standard windows dialog)

Entering the username/password for the web site fails,

entering username/password for the machine works.

The user - of course - doesn't want to enter enything, let alone see the darn dialog box,

just wants to see the report on the screen.

Does anyone have any ideas on where to start looking for a solution to this problem?

thanks

tony

The settings for the permissions of the report service are in the service's properties in IIS settings in control panel -> administrative tools. You might be able to set it to use the same password as the rest of the website there.