Drag and drop query onto Management Studio

Everytime I drag and drop a query file (.sql) from Windows Explorer onto
Management Studio, I am prompted to login. How do I get drag and drop to use
existing connection?
guy,
Install SP2 I believe. (At least it works for me.)
RLF
"guy" <guy@.hotmail.com> wrote in message
news:OiHKPyJqHHA.4872@.TK2MSFTNGP03.phx.gbl...
> Everytime I drag and drop a query file (.sql) from Windows Explorer onto
> Management Studio, I am prompted to login. How do I get drag and drop to
> use existing connection?
>
|||That"s the ticket. Thank you.
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:OGnQpwRqHHA.3740@.TK2MSFTNGP02.phx.gbl...
> guy,
> Install SP2 I believe. (At least it works for me.)
> RLF
> "guy" <guy@.hotmail.com> wrote in message
> news:OiHKPyJqHHA.4872@.TK2MSFTNGP03.phx.gbl...
>

Drag & Drop Multiple Query Files Into Management Studio

In SQL Server 2000, you could have a Query Analyzer (QA) query window open to a particular database, and drag and drop a group of query files from Windows Explorer on it. QA would then open a query window for each of the files, and automatically default it to that database.

I am trying to get the same functionality in SQL Server 2005 Management Studio, but it nags me with the "Connect to Database Engine" dialog box for every file. Worse still, it will not default to the same database being used by the query window on which it was dropped. And to add insult to injury, it makes you click the options button to change to other than the default database. The behavior is the same in both the tabbed and MDI environment.

I have looked for settings that would affect this behavior, but didn't notice any. Does anyone know if this behavior is possible with 2005? If so, how to you implement it? Thanks.

Kevin

Did you find an answer to your problem? I have just started using SQL Server 2005 and I am having the same problem.

Thanks

Danny

|||

Even if I use "open file" to open saved queries I get asked each time to connect to the database engine. What am I doing wrong?

Thanks

Danny

|||Unfortunately I still have not found a way around the problem. I assume it's something we will have to live with.|||

One of the solutions would be to create a SQL Project with connection info and the related query files as members of a the project. This is similar to the VS project model

You can have multiple projects with different connection settings in a solution.
You may also be able to leverage the new SQLCMD internal commands to embed the connect statement within your scripts.

Thanks,
Ramesh

|||There must be an easier solution to this. Shocking!|||

Please raise a suggestion at the product feedback centre

http://lab.msdn.microsoft.com/ProductFeedback

|||Added a suggestion - http://lab.msdn.microsoft.com/ProductFeedback/viewFeedback.aspx?feedbackId=FDBK48385

Drag & Drop Multiple Query Files Into Management Studio

In SQL Server 2000, you could have a Query Analyzer (QA) query window open to a particular database, and drag and drop a group of query files from Windows Explorer on it. QA would then open a query window for each of the files, and automatically default it to that database.

I am trying to get the same functionality in SQL Server 2005 Management Studio, but it nags me with the "Connect to Database Engine" dialog box for every file. Worse still, it will not default to the same database being used by the query window on which it was dropped. And to add insult to injury, it makes you click the options button to change to other than the default database. The behavior is the same in both the tabbed and MDI environment.

I have looked for settings that would affect this behavior, but didn't notice any. Does anyone know if this behavior is possible with 2005? If so, how to you implement it? Thanks.

Kevin

Did you find an answer to your problem? I have just started using SQL Server 2005 and I am having the same problem.

Thanks

Danny

|||

Even if I use "open file" to open saved queries I get asked each time to connect to the database engine. What am I doing wrong?

Thanks

Danny

|||Unfortunately I still have not found a way around the problem. I assume it's something we will have to live with.|||

One of the solutions would be to create a SQL Project with connection info and the related query files as members of a the project. This is similar to the VS project model

You can have multiple projects with different connection settings in a solution.
You may also be able to leverage the new SQLCMD internal commands to embed the connect statement within your scripts.

Thanks,
Ramesh

|||There must be an easier solution to this. Shocking!|||

Please raise a suggestion at the product feedback centre

http://lab.msdn.microsoft.com/ProductFeedback

|||Added a suggestion - http://lab.msdn.microsoft.com/ProductFeedback/viewFeedback.aspx?feedbackId=FDBK48385

Downloading sample SQL Server Studio Express Database

I'm looking for a sample Database that is well set up to learn from. Does anyone know where I can download one from?SeeSQL Server 2005 Samples and Sample Databases.

Download: SQL Server Management Studio Express

I have an instance of SQL Server 2005 running on a server. I know that Management Studio is available to me on the server through Programs.

What do I need on my client computer used for development to access the instance on the server? Is it as simple as a shortcut to the server Management Studio exe? Do I access it through Internet Explorer? Or what do I have to install on the client to be able to manager the server DB's?

Apparently this must be very easy because I've looked all over and can't find the answer. Thank you in advance.

Use the SQL Server installation CD, and choose the "Install Client Tools" options. At least you'll need to install client connectivity, and if you want the query editor, and other development tools, then you'll need the Management Studio.

I don't think it's just as simple as creating a shortcut link to the management studio EXE file on your server, you're best off installing it properly.|||

installed client components, as per description, client connectivity but there is no management studio only 2 tools: 'Surface Area Configuration' and '...Configueation Manager' - it doesn't realy help!

I'd like to have the management studio on client ... how can I get it?

|||Have a look at the following thread on MSDN's Channel 9. Others were having a similar issue.
http://channel9.msdn.com/ShowPost.aspx?PostID=142941

It appears that you may need to launch it separately from the Tools directory on your installation media.|||the reason: IE6SP1 was absent! after installation I could choose management studio from DVD/TOOLS. Thanks!|||Are the management tools available for Download?|||For the Express edition, you can download it here:
http://msdn.microsoft.com/vstudio/express/sql/download/

If you're using the Standard/Enterprise/Developer edition, it should be on your installation media (DVD > TOOLS directory)

Don't have any Server to choose from the Server name list

Hi
I have just installed SQL Server 2005 Enterprise Edition. I have Microsoft
Windows 2000. When I start SQL Server Management Studio and have to choose a
server from the Server name list, the list is empty. I have no clue what to
do?
Please Help me!!!
Explain carefully
FiaType the name of the server in the Server Name field. If
it's an named instance, you would need to type
ServerName\InstanceName
-Sue
On Thu, 07 Sep 2006 11:04:03 GMT, "fiaolle"
<fiaolle@.telia.com> wrote:

>Hi
>I have just installed SQL Server 2005 Enterprise Edition. I have Microsoft
>Windows 2000. When I start SQL Server Management Studio and have to choose
a
>server from the Server name list, the list is empty. I have no clue what to
>do?
>Please Help me!!!
>Explain carefully
>Fia
>

don''t find Management Studio after installed SQL 2008 CTP JULY.

My OS is WinXP

I don't find Management Studio after installed SQL 2008 CTP JULY.

Why?

Thanks!

bill

I now it sounds like a super obvious question, but did you select to install Management Studio? Did you hit any installation errors?

Take a look at C:\Program Files\microsoft sql server\100\Setup Bootstrap\LOG\Summary.txt to see if there were any installation errors.

Cheers,

Dan

|||

Bill,

Since we haven't heard back from you in ~10 days I'm going to close this as answered. If you're still encountering problems with Management Studio feel free to post a new thread.

Cheers,

Dan

|||

Same problem I am facing. As per comment I have checked with log file and content are as below:

Microsoft SQL Server 2008 10.0.1049.14
==============================
OS Version : Microsoft Windows XP Professional Service Pack 2 (Build 2600)
Time : Sun Sep 23 13:07:25 2007

So please help me resolve this issue.

don''t find Management Studio after installed SQL 2008 CTP JULY.

My OS is WinXP

I don't find Management Studio after installed SQL 2008 CTP JULY.

Why?

Thanks!

bill

I now it sounds like a super obvious question, but did you select to install Management Studio? Did you hit any installation errors?

Take a look at C:\Program Files\microsoft sql server\100\Setup Bootstrap\LOG\Summary.txt to see if there were any installation errors.

Cheers,

Dan

|||

Bill,

Since we haven't heard back from you in ~10 days I'm going to close this as answered. If you're still encountering problems with Management Studio feel free to post a new thread.

Cheers,

Dan

|||

Same problem I am facing. As per comment I have checked with log file and content are as below:

Microsoft SQL Server 2008 10.0.1049.14
==============================
OS Version : Microsoft Windows XP Professional Service Pack 2 (Build 2600)
Time : Sun Sep 23 13:07:25 2007

So please help me resolve this issue.

don''t find Management Studio after installed SQL 2008 CTP JULY.

My OS is WinXP

I don't find Management Studio after installed SQL 2008 CTP JULY.

Why?

Thanks!

bill

I now it sounds like a super obvious question, but did you select to install Management Studio? Did you hit any installation errors?

Take a look at C:\Program Files\microsoft sql server\100\Setup Bootstrap\LOG\Summary.txt to see if there were any installation errors.

Cheers,

Dan

|||

Bill,

Since we haven't heard back from you in ~10 days I'm going to close this as answered. If you're still encountering problems with Management Studio feel free to post a new thread.

Cheers,

Dan

|||

Same problem I am facing. As per comment I have checked with log file and content are as below:

Microsoft SQL Server 2008 10.0.1049.14
==============================
OS Version : Microsoft Windows XP Professional Service Pack 2 (Build 2600)
Time : Sun Sep 23 13:07:25 2007

So please help me resolve this issue.

Dont Display dbo. in Object Explorer

Is there a way to stop SQL Server Management Studio (2005) from showing "dbo." on everything in the object explorer.

I'd love to turn that off on a database I'm working on as, for example, if you have to find tblMember, you'd have to type d-b-o-.-t-b-l-m before you can actually start jumping to the table you're interested in - whereas in Enterprise Manager (2000) you'd only have to type t-b-l-m if you get my meaning.

Hi there,

There no such option as to hide the owner of the datatable. Actually this is good because the owner is a very strong security feature SQL Server provides. You may want to have in mind who is the owner especially if you are going to host your application in a shared environment where all tables will be owned by your Sql user (which will definitely not be sa).
Sorry for bringing the bad news,
Cheers,

Andreas Botsikas

don't find Management Studio after installed SQL 2008 CTP JULY.

My OS is WinXP

I don't find Management Studio after installed SQL 2008 CTP JULY.

Why?

Thanks!

bill

I now it sounds like a super obvious question, but did you select to install Management Studio? Did you hit any installation errors?

Take a look at C:\Program Files\microsoft sql server\100\Setup Bootstrap\LOG\Summary.txt to see if there were any installation errors.

Cheers,

Dan

|||

Bill,

Since we haven't heard back from you in ~10 days I'm going to close this as answered. If you're still encountering problems with Management Studio feel free to post a new thread.

Cheers,

Dan

|||

Same problem I am facing. As per comment I have checked with log file and content are as below:

Microsoft SQL Server 2008 10.0.1049.14
==============================
OS Version : Microsoft Windows XP Professional Service Pack 2 (Build 2600)
Time : Sun Sep 23 13:07:25 2007

So please help me resolve this issue.

Doing a data import using DTS Wizard in SQL Server 2005 - being efficient with 5 flat files

Hi,

I'm a new user of SQL Server 2005. I have the full version installed. I also have SQL Server Business Integration Dev Studio installed. My OS is Windows XP.

I'm importing a series of 5 flat files into a database on one of the SQL Servers we have. My goal is to get 5 different tables (though perhaps I should do one and add an extra field to distinguish each import) into the database for further analysis.

I tried doing an import via DTS Wizard. There are no column names in the flat file so I defined them during the import process (all 58 of them). When I got to the end, I had an option to save the import process as a SSIS (SQL Server Integration Service) Package on:

SQL SERVER (I don't have permission for this)

or

FILE SYSTEM (did this one)

I saved the Package locally in hopes of being able to go back in, change the source file and destination table of the package and quickly get the other 4 flat files imported.

My problems are:

1) I couldn't find how to run the *.DTSX Package file to run in SQL Server Studio (basically reuse the Package with minor changes and saving me having to redefine the same 58 columns on each flat file import)

2) Tried but didn't understand how to run it in SQL Server Bus Intel Dev Studio (i.e. understanding the mapping and getting the data types right so it wouldn't error out)

3) Don't know how to make the necessary changes so that the Package handles the next source file and puts in a new destination table (do I need to do 5 CREATE TABLES so this Package has a place to run to?)

4) Does the Package need to be part of a Project to run (I haven't found how to take an existing Package and make it part of a Project/Solution)?

5) Is there a good book or online resource for just getting the basics of using SQL Server 2005 and SQL Server Business Intelligence Development Studio?

I'm really at a loss after spending a day fruitlessly on it scouring the help files, forums and experimenting around.

Hope somebody can point me in the right direction.

Regards,

Patrick Briggs,
Pasadena, CA

Patrick Briggs wrote:

My problems are:

1) I couldn't find how to run the *.DTSX Package file to run in SQL Server Studio (basically reuse the Package with minor changes and saving me having to redefine the same 58 columns on each flat file import)

You can only run from SQL Server Management Studio if it "knows" about the package. Connect to the SSIS service in SSMS and you'll see that you can tell it about packages stored on the file system and then run them from there.

Patrick Briggs wrote:

2) Tried but didn't understand how to run it in SQL Server Bus Intel Dev Studio (i.e. understanding the mapping and getting the data types right so it wouldn't error out)

You need to add the package to a project. Open VS, start a new SSIS project, and add the package to it. All of this stuff is fairly self-explanatory but if you need a hand - reply here.

Patrick Briggs wrote:

3) Don't know how to make the necessary changes so that the Package handles the next source file and puts in a new destination table (do I need to do 5 CREATE TABLES so this Package has a place to run to?)

Do you want the package to create the tables or do you want the package to assume that the tables already exist? If the former, then use the Execute SQL Task to issue a CREATE TABLE... If the latter...create the tables in SSMS and don't do anything to the package.

Patrick Briggs wrote:

4) Does the Package need to be part of a Project to run (I haven't found how to take an existing Package and make it part of a Project/Solution)?

If you want to run it in BIDS - yes. Create a new project. Right-click on the project name in SOlution Explorer. Select Add->Existing Item... and then browse to you package.

Patrick Briggs wrote:

5) Is there a good book or online resource for just getting the basics of using SQL Server 2005 and SQL Server Business Intelligence Development Studio?

What are "the basics"? There are books devoted to all the things that leverage BIDS (i.e. SSIS, SSAS, SSRS) but nothing on that only covers BIDS without the context on one of those three.

HTH

-Jamie

|||

I just spent some time working out how to do a seemingly simple task.I’m sharing the steps I took to do this in hopes it saves other SQL Server 2005 users (especially newbies like myself) time.

My original question posed on several SQL newsgroups was based on this goal:

I'm importing a series of 5 flat files (all with same file layout) into a database on one of the SQL Servers we have using SQL Server 2005 (SQL Server Management Studio) . My goal is to get 5 different tables.I want to do this without having to redo all the layout criteria 4 additional times.

Below are the steps I followed to get a solution (all done in Microsoft SQL Server Management Studio):

Create the Package (data import)

1) Use the SQL Server Import Export Wizard (equivalent to SQL Server 2000 Data Transfer Wizard) to import your first flat file.At the CHOOSE DATA SOURCE window browse for your file.

2) Under the Advanced tab, you can set your Column attributes (“output column width” or “data type” to name a few).I highlighted all the columns and selected “string [DT_STR]” for data type.To avoid truncation errors, I selected 255 for output column width.You can name the columns whose data you are most concerned with (I did import all the available fields).

3) After choosing a server destination you will have a “SELECT SOURCE TABLES AND VIEWS” window pop up.Under the “Mapping” column you can choose to tweak your mapping further editing in SQL (see Edit SQL button).I didn’t.

4) The “SAVE AND EXECUTE PACKAGE” will pop up.The “Execute Immediately” box should be checked and you should check the “Save SSIS Package” (SQL Server Integration Services).When you do, select “File System” for where to save this import-file-package to.

5) Click OKAY for the Package Protection Level and the “SAVE SSIS PACKAGE” window will appear.Browse for a path on your local computer to save to.

Modify Package (data import) for Next Use

6) In SQL Server Management Studio, browse for the Package and open it.

Preparation for SQL Task – box

7) You should see a screen that shows two boxes (“Preparation for SQL Task”) and (“Data Flow Task”).

8) Right click on the former and select “Edit”.

9) On the “SQL Statement” row, click into the right column and select the “…” box

10) Change the destination table (the table you will create with this package) to a meaningful name and click OK.

11) Click OK for the “SQL Task Editor”

Data Flow Task - box

12) Right click on the “Data Flow Task” box and select “Edit”.

13) Three boxes will appear “SourceConnectionFlatFile”, “Data Conversion 1”, and “Destination - <whatever table name your original data import went to>”.Below them is a section that displays “Connection Managers”

SourceConnectionFlatFile - editing

14) The first thing you will want to do is change the import source to a new flat file.You do this by going below the boxes under the “Connection Managers” window and right clicking on “SourceConnectionFlatFile” and then selecting “Edit”

15) Browse for the new “File Name” and select it.

16) A “Microsoft SQL Server Management Studio” window will pop up asking you if you want to “keep or reset the existing metadata”.The metadata is just your column definitions and choosing “YES” to keep this makes sense if you are doing data imports on files with the same file layout.

17) Still in the “Flat File Connection Manager Editor” window, change the “Connection Manager Name” to something meaningful (I add<_> at the end and then the name of the table the flat file is going to) and click OK.

SourceConnectionFlatFile – box (editing)

18) Right click on the“SourceConnectionFlatFile” box and select “Edit”.

19) Your newly named “Flat File Connection Manager” should appear in select box.

20) Click OK, right click again on the “SourceConnectionFlatFile” box and select “Show Advanced Editor”.

21) Under the “Connections Manager” tab, your newly named “Flat File Connection” should appear (the prior step is necessary for the advanced editor to recognize your change).

22) Under the “Component Properties” tab, on the “Name” row, click into the right column and rename to something meaningful (notice the “Identification String” row description changes too once you click out of the “Name” row)

23) Under the “Column Mappings” tab, just confirm you are mapping your flat file fields (“Available External Columns”) to a destination table’s fields (“Available Output Columns”).

24) Under the “Input and Output Properties” tab you can check in “Flat File Source Output” to make modifications to either your “External Columns” or your “Output Columns” – you shouldn’t need to for a simple import.
((NOTE: any changes you make here would likely need to be consistent with the column properties found under the “Connection Manager Window” for the “SourceConnectionFlatFile” as well as the “Data Conversion 1” box under the “Data Flow Tasks” window, so exercise caution

25) NOTE:This process has worked for me by making my source columns all “string [DT_STR]” data type and the output columns all “Unicode String [DT_WSTR]” data type.

Data Conversion 1 – box (editing)

26) There is nothing you need to do here.By right clicking on the “Data Conversion 1” box and selecting “Edit”, you can see and change the data type of the output columns (the ones in the table your importing the flat file to).There are probably more edits one can do but they’re beyond what I’ve learned.

Destination - <whatever table name your original data import went to> – box (editing)

27) Right click on the “Destination - <whatever table name your original data import went to>” box and select “Show Advanced Editor”.

28) Select the “Component Properties” tab.

29) Select the right column at the “Name” row and change the name to something meaningful (ie. related to the source file name or the table name you’re importing to).

30) Select the right column at the “Identification String” row and it will update to this change.

31) Select the right column at the “OpenRowSet” and change it to the name of the table you are importing your flat file to (this should be consistent with table name under step 10).

32) Click OK

33) Select FILE and select “Save As…” and then give your package a new name that’s meaningful (this will be helpful if you have to rerun the import of the flat file later).

Run (execute) the Revised Package (data import)

34) Go back to SQL Server Management Studio and open the Object Explorer

35) Connect to an “Integration Services” component.This should essentially be a local instance (not sure where it is on the local computer or in SQL Server Management Studio on the local computer).

36) In “Object Explorer” go down to your “Integration Services” object and expand it.

37) Expand “Stored Packages”

38) Right click on “File System” and select “Import Package” and an “IMPORT PACKAGE” window will appear

39) For “Package Location” choose “File System” and then browse for the “Package Path”

40) Click into the “Package Name” and it defaults to your Package’s file name.

41) Click OK and the Package is imported.

42) Right click on the newly imported Package and select “Run Package”

43) An “Execute Package Utility” window appears

44) Select “Execute” and the package runs.

|||Patrick, Can you do this for multiple files in one shot or do you have to go thru these steps separately for every file? Please let me know...I have around 300 flat files with diff. layouts so I am trying to figure out the best way to do this. Thanks!|||I never really tried it for multiple files at once. I am new to SQL Server 2005. I've noticed that as I've used this means of doing multiple imports in my work, it's only time efficient if the files are many columns with varying data types.

You bring up a good question which I hope somebody here will try to answer. The steps I outlined are pretty tedious to do file after file.

Regards,

Patrick in Pasadena
|||What's wrong with containing the data flow in a foreach loop that opens each file?|||

Patrick Briggs wrote:

I never really tried it for multiple files at once. I am new to SQL Server 2005. I've noticed that as I've used this means of doing multiple imports in my work, it's only time efficient if the files are many columns with varying data types.

You bring up a good question which I hope somebody here will try to answer. The steps I outlined are pretty tedious to do file after file.

Regards,

Patrick in Pasadena

I've not followed this thread completely but if you're talking about loading multiple files, this link may help:

Processing data from multiple files all at once
(http://blogs.conchango.com/jamiethomson/archive/2006/10/14/SSIS_3A00_-Processing-data-from-multiple-files-all-at-once.aspx)

-Jamie

Doing a data import using DTS Wizard in SQL Server 2005 - being efficient with 5 flat files

Hi,

I'm a new user of SQL Server 2005. I have the full version installed. I also have SQL Server Business Integration Dev Studio installed. My OS is Windows XP.

I'm importing a series of 5 flat files into a database on one of the SQL Servers we have. My goal is to get 5 different tables (though perhaps I should do one and add an extra field to distinguish each import) into the database for further analysis.

I tried doing an import via DTS Wizard. There are no column names in the flat file so I defined them during the import process (all 58 of them). When I got to the end, I had an option to save the import process as a SSIS (SQL Server Integration Service) Package on:

SQL SERVER (I don't have permission for this)

or

FILE SYSTEM (did this one)

I saved the Package locally in hopes of being able to go back in, change the source file and destination table of the package and quickly get the other 4 flat files imported.

My problems are:

1) I couldn't find how to run the *.DTSX Package file to run in SQL Server Studio (basically reuse the Package with minor changes and saving me having to redefine the same 58 columns on each flat file import)

2) Tried but didn't understand how to run it in SQL Server Bus Intel Dev Studio (i.e. understanding the mapping and getting the data types right so it wouldn't error out)

3) Don't know how to make the necessary changes so that the Package handles the next source file and puts in a new destination table (do I need to do 5 CREATE TABLES so this Package has a place to run to?)

4) Does the Package need to be part of a Project to run (I haven't found how to take an existing Package and make it part of a Project/Solution)?

5) Is there a good book or online resource for just getting the basics of using SQL Server 2005 and SQL Server Business Intelligence Development Studio?

I'm really at a loss after spending a day fruitlessly on it scouring the help files, forums and experimenting around.

Hope somebody can point me in the right direction.

Regards,

Patrick Briggs,
Pasadena, CA

Patrick Briggs wrote:

My problems are:

1) I couldn't find how to run the *.DTSX Package file to run in SQL Server Studio (basically reuse the Package with minor changes and saving me having to redefine the same 58 columns on each flat file import)

You can only run from SQL Server Management Studio if it "knows" about the package. Connect to the SSIS service in SSMS and you'll see that you can tell it about packages stored on the file system and then run them from there.

Patrick Briggs wrote:

2) Tried but didn't understand how to run it in SQL Server Bus Intel Dev Studio (i.e. understanding the mapping and getting the data types right so it wouldn't error out)

You need to add the package to a project. Open VS, start a new SSIS project, and add the package to it. All of this stuff is fairly self-explanatory but if you need a hand - reply here.

Patrick Briggs wrote:

3) Don't know how to make the necessary changes so that the Package handles the next source file and puts in a new destination table (do I need to do 5 CREATE TABLES so this Package has a place to run to?)

Do you want the package to create the tables or do you want the package to assume that the tables already exist? If the former, then use the Execute SQL Task to issue a CREATE TABLE... If the latter...create the tables in SSMS and don't do anything to the package.

Patrick Briggs wrote:

4) Does the Package need to be part of a Project to run (I haven't found how to take an existing Package and make it part of a Project/Solution)?

If you want to run it in BIDS - yes. Create a new project. Right-click on the project name in SOlution Explorer. Select Add->Existing Item... and then browse to you package.

Patrick Briggs wrote:

5) Is there a good book or online resource for just getting the basics of using SQL Server 2005 and SQL Server Business Intelligence Development Studio?

What are "the basics"? There are books devoted to all the things that leverage BIDS (i.e. SSIS, SSAS, SSRS) but nothing on that only covers BIDS without the context on one of those three.

HTH

-Jamie

|||

I just spent some time working out how to do a seemingly simple task.I’m sharing the steps I took to do this in hopes it saves other SQL Server 2005 users (especially newbies like myself) time.

My original question posed on several SQL newsgroups was based on this goal:

I'm importing a series of 5 flat files (all with same file layout) into a database on one of the SQL Servers we have using SQL Server 2005 (SQL Server Management Studio) . My goal is to get 5 different tables.I want to do this without having to redo all the layout criteria 4 additional times.

Below are the steps I followed to get a solution (all done in Microsoft SQL Server Management Studio):

Create the Package (data import)

1) Use the SQL Server Import Export Wizard (equivalent to SQL Server 2000 Data Transfer Wizard) to import your first flat file.At the CHOOSE DATA SOURCE window browse for your file.

2) Under the Advanced tab, you can set your Column attributes (“output column width” or “data type” to name a few).I highlighted all the columns and selected “string [DT_STR]” for data type.To avoid truncation errors, I selected 255 for output column width.You can name the columns whose data you are most concerned with (I did import all the available fields).

3) After choosing a server destination you will have a “SELECT SOURCE TABLES AND VIEWS” window pop up.Under the “Mapping” column you can choose to tweak your mapping further editing in SQL (see Edit SQL button).I didn’t.

4) The “SAVE AND EXECUTE PACKAGE” will pop up.The “Execute Immediately” box should be checked and you should check the “Save SSIS Package” (SQL Server Integration Services).When you do, select “File System” for where to save this import-file-package to.

5) Click OKAY for the Package Protection Level and the “SAVE SSIS PACKAGE” window will appear.Browse for a path on your local computer to save to.

Modify Package (data import) for Next Use

6) In SQL Server Management Studio, browse for the Package and open it.

Preparation for SQL Task – box

7) You should see a screen that shows two boxes (“Preparation for SQL Task”) and (“Data Flow Task”).

8) Right click on the former and select “Edit”.

9) On the “SQL Statement” row, click into the right column and select the “…” box

10) Change the destination table (the table you will create with this package) to a meaningful name and click OK.

11) Click OK for the “SQL Task Editor”

Data Flow Task - box

12) Right click on the “Data Flow Task” box and select “Edit”.

13) Three boxes will appear “SourceConnectionFlatFile”, “Data Conversion 1”, and “Destination - <whatever table name your original data import went to>”.Below them is a section that displays “Connection Managers”

SourceConnectionFlatFile - editing

14) The first thing you will want to do is change the import source to a new flat file.You do this by going below the boxes under the “Connection Managers” window and right clicking on “SourceConnectionFlatFile” and then selecting “Edit”

15) Browse for the new “File Name” and select it.

16) A “Microsoft SQL Server Management Studio” window will pop up asking you if you want to “keep or reset the existing metadata”.The metadata is just your column definitions and choosing “YES” to keep this makes sense if you are doing data imports on files with the same file layout.

17) Still in the “Flat File Connection Manager Editor” window, change the “Connection Manager Name” to something meaningful (I add<_> at the end and then the name of the table the flat file is going to) and click OK.

SourceConnectionFlatFile – box (editing)

18) Right click on the“SourceConnectionFlatFile” box and select “Edit”.

19) Your newly named “Flat File Connection Manager” should appear in select box.

20) Click OK, right click again on the “SourceConnectionFlatFile” box and select “Show Advanced Editor”.

21) Under the “Connections Manager” tab, your newly named “Flat File Connection” should appear (the prior step is necessary for the advanced editor to recognize your change).

22) Under the “Component Properties” tab, on the “Name” row, click into the right column and rename to something meaningful (notice the “Identification String” row description changes too once you click out of the “Name” row)

23) Under the “Column Mappings” tab, just confirm you are mapping your flat file fields (“Available External Columns”) to a destination table’s fields (“Available Output Columns”).

24) Under the “Input and Output Properties” tab you can check in “Flat File Source Output” to make modifications to either your “External Columns” or your “Output Columns” – you shouldn’t need to for a simple import.
((NOTE: any changes you make here would likely need to be consistent with the column properties found under the “Connection Manager Window” for the “SourceConnectionFlatFile” as well as the “Data Conversion 1” box under the “Data Flow Tasks” window, so exercise caution

25) NOTE:This process has worked for me by making my source columns all “string [DT_STR]” data type and the output columns all “Unicode String [DT_WSTR]” data type.

Data Conversion 1 – box (editing)

26) There is nothing you need to do here.By right clicking on the “Data Conversion 1” box and selecting “Edit”, you can see and change the data type of the output columns (the ones in the table your importing the flat file to).There are probably more edits one can do but they’re beyond what I’ve learned.

Destination - <whatever table name your original data import went to> – box (editing)

27) Right click on the “Destination - <whatever table name your original data import went to>” box and select “Show Advanced Editor”.

28) Select the “Component Properties” tab.

29) Select the right column at the “Name” row and change the name to something meaningful (ie. related to the source file name or the table name you’re importing to).

30) Select the right column at the “Identification String” row and it will update to this change.

31) Select the right column at the “OpenRowSet” and change it to the name of the table you are importing your flat file to (this should be consistent with table name under step 10).

32) Click OK

33) Select FILE and select “Save As…” and then give your package a new name that’s meaningful (this will be helpful if you have to rerun the import of the flat file later).

Run (execute) the Revised Package (data import)

34) Go back to SQL Server Management Studio and open the Object Explorer

35) Connect to an “Integration Services” component.This should essentially be a local instance (not sure where it is on the local computer or in SQL Server Management Studio on the local computer).

36) In “Object Explorer” go down to your “Integration Services” object and expand it.

37) Expand “Stored Packages”

38) Right click on “File System” and select “Import Package” and an “IMPORT PACKAGE” window will appear

39) For “Package Location” choose “File System” and then browse for the “Package Path”

40) Click into the “Package Name” and it defaults to your Package’s file name.

41) Click OK and the Package is imported.

42) Right click on the newly imported Package and select “Run Package”

43) An “Execute Package Utility” window appears

44) Select “Execute” and the package runs.

|||Patrick, Can you do this for multiple files in one shot or do you have to go thru these steps separately for every file? Please let me know...I have around 300 flat files with diff. layouts so I am trying to figure out the best way to do this. Thanks!|||I never really tried it for multiple files at once. I am new to SQL Server 2005. I've noticed that as I've used this means of doing multiple imports in my work, it's only time efficient if the files are many columns with varying data types.

You bring up a good question which I hope somebody here will try to answer. The steps I outlined are pretty tedious to do file after file.

Regards,

Patrick in Pasadena|||What's wrong with containing the data flow in a foreach loop that opens each file?|||

Patrick Briggs wrote:

I never really tried it for multiple files at once. I am new to SQL Server 2005. I've noticed that as I've used this means of doing multiple imports in my work, it's only time efficient if the files are many columns with varying data types.

You bring up a good question which I hope somebody here will try to answer. The steps I outlined are pretty tedious to do file after file.

Regards,

Patrick in Pasadena

I've not followed this thread completely but if you're talking about loading multiple files, this link may help:

Processing data from multiple files all at once
(http://blogs.conchango.com/jamiethomson/archive/2006/10/14/SSIS_3A00_-Processing-data-from-multiple-files-all-at-once.aspx)

-Jamie

Does Visual Studio 2005 come with SQL Server 2005 Standard?

All, does VS2005 come with SQL Server 2005 Standard? If not, what version
of SQL does it come with? How can I identify that version installed?
Thanks for your help.
VS includes SQL Server Express edition. SQL Server Developer Edition is
included with VS MSDN subscriptions as a separate install.
One method to identify the installed version is with the query:SELECT
@.@.VERSION.
Hope this helps.
Dan Guzman
SQL Server MVP
"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:CA3B7A75-B0C5-4BC5-958B-6DA893C6D562@.microsoft.com...
> All, does VS2005 come with SQL Server 2005 Standard? If not, what
> version
> of SQL does it come with? How can I identify that version installed?
> Thanks for your help.
|||So, your telling me, if I have SQL Server 2005 Standard, there is no visual
Studio pro included, but if i have SQL 2005 Developer Edition, then VS Studio
Pro is?
"Dan Guzman" wrote:

> VS includes SQL Server Express edition. SQL Server Developer Edition is
> included with VS MSDN subscriptions as a separate install.
> One method to identify the installed version is with the query:SELECT
> @.@.VERSION.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Rob" <Rob@.discussions.microsoft.com> wrote in message
> news:CA3B7A75-B0C5-4BC5-958B-6DA893C6D562@.microsoft.com...
>
|||> So, your telling me, if I have SQL Server 2005 Standard, there is no
> visual
> Studio pro included, but if i have SQL 2005 Developer Edition, then VS
> Studio
> Pro is?
I understood that you original question was what SQL Server version(s) you
get with Visual Studio. However, you are now asking what version of Visual
Studio you get with SQL Server.
VS 2005 pro is not included with any SQL Server edition. A basic VS IDE is
included with SQL Server Standard, Enterprise and Developer that includes
support for only BI projects (SSIS, AS, etc.). If you already have VS
installed, support for those project types are added to your existing VS
installation so that you can use a single IDE for all project types.
Hope this helps.
Dan Guzman
SQL Server MVP
"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:7904116A-6079-4EF7-A0AC-A48DEF8CB67D@.microsoft.com...[vbcol=seagreen]
> So, your telling me, if I have SQL Server 2005 Standard, there is no
> visual
> Studio pro included, but if i have SQL 2005 Developer Edition, then VS
> Studio
> Pro is?
> "Dan Guzman" wrote:

Does Visual Studio 2005 come with SQL Server 2005 Standard?

All, does VS2005 come with SQL Server 2005 Standard? If not, what version
of SQL does it come with? How can I identify that version installed?
Thanks for your help.VS includes SQL Server Express edition. SQL Server Developer Edition is
included with VS MSDN subscriptions as a separate install.
One method to identify the installed version is with the query:SELECT
@.@.VERSION.
Hope this helps.
Dan Guzman
SQL Server MVP
"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:CA3B7A75-B0C5-4BC5-958B-6DA893C6D562@.microsoft.com...
> All, does VS2005 come with SQL Server 2005 Standard? If not, what
> version
> of SQL does it come with? How can I identify that version installed?
> Thanks for your help.|||So, your telling me, if I have SQL Server 2005 Standard, there is no visual
Studio pro included, but if i have SQL 2005 Developer Edition, then VS Studi
o
Pro is?
"Dan Guzman" wrote:

> VS includes SQL Server Express edition. SQL Server Developer Edition is
> included with VS MSDN subscriptions as a separate install.
> One method to identify the installed version is with the query:SELECT
> @.@.VERSION.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Rob" <Rob@.discussions.microsoft.com> wrote in message
> news:CA3B7A75-B0C5-4BC5-958B-6DA893C6D562@.microsoft.com...
>|||> So, your telling me, if I have SQL Server 2005 Standard, there is no
> visual
> Studio pro included, but if i have SQL 2005 Developer Edition, then VS
> Studio
> Pro is?
I understood that you original question was what SQL Server version(s) you
get with Visual Studio. However, you are now asking what version of Visual
Studio you get with SQL Server.
VS 2005 pro is not included with any SQL Server edition. A basic VS IDE is
included with SQL Server Standard, Enterprise and Developer that includes
support for only BI projects (SSIS, AS, etc.). If you already have VS
installed, support for those project types are added to your existing VS
installation so that you can use a single IDE for all project types.
Hope this helps.
Dan Guzman
SQL Server MVP
"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:7904116A-6079-4EF7-A0AC-A48DEF8CB67D@.microsoft.com...[vbcol=seagreen]
> So, your telling me, if I have SQL Server 2005 Standard, there is no
> visual
> Studio pro included, but if i have SQL 2005 Developer Edition, then VS
> Studio
> Pro is?
> "Dan Guzman" wrote:
>

Does Visual Studio 2005 come with SQL Server 2005 Standard?

All, does VS2005 come with SQL Server 2005 Standard? If not, what version
of SQL does it come with? How can I identify that version installed?
Thanks for your help.VS includes SQL Server Express edition. SQL Server Developer Edition is
included with VS MSDN subscriptions as a separate install.
One method to identify the installed version is with the query:SELECT
@.@.VERSION.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:CA3B7A75-B0C5-4BC5-958B-6DA893C6D562@.microsoft.com...
> All, does VS2005 come with SQL Server 2005 Standard? If not, what
> version
> of SQL does it come with? How can I identify that version installed?
> Thanks for your help.|||So, your telling me, if I have SQL Server 2005 Standard, there is no visual
Studio pro included, but if i have SQL 2005 Developer Edition, then VS Studio
Pro is?
"Dan Guzman" wrote:
> VS includes SQL Server Express edition. SQL Server Developer Edition is
> included with VS MSDN subscriptions as a separate install.
> One method to identify the installed version is with the query:SELECT
> @.@.VERSION.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Rob" <Rob@.discussions.microsoft.com> wrote in message
> news:CA3B7A75-B0C5-4BC5-958B-6DA893C6D562@.microsoft.com...
> > All, does VS2005 come with SQL Server 2005 Standard? If not, what
> > version
> > of SQL does it come with? How can I identify that version installed?
> >
> > Thanks for your help.
>|||> So, your telling me, if I have SQL Server 2005 Standard, there is no
> visual
> Studio pro included, but if i have SQL 2005 Developer Edition, then VS
> Studio
> Pro is?
I understood that you original question was what SQL Server version(s) you
get with Visual Studio. However, you are now asking what version of Visual
Studio you get with SQL Server.
VS 2005 pro is not included with any SQL Server edition. A basic VS IDE is
included with SQL Server Standard, Enterprise and Developer that includes
support for only BI projects (SSIS, AS, etc.). If you already have VS
installed, support for those project types are added to your existing VS
installation so that you can use a single IDE for all project types.
Hope this helps.
Dan Guzman
SQL Server MVP
"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:7904116A-6079-4EF7-A0AC-A48DEF8CB67D@.microsoft.com...
> So, your telling me, if I have SQL Server 2005 Standard, there is no
> visual
> Studio pro included, but if i have SQL 2005 Developer Edition, then VS
> Studio
> Pro is?
> "Dan Guzman" wrote:
>> VS includes SQL Server Express edition. SQL Server Developer Edition is
>> included with VS MSDN subscriptions as a separate install.
>> One method to identify the installed version is with the query:SELECT
>> @.@.VERSION.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Rob" <Rob@.discussions.microsoft.com> wrote in message
>> news:CA3B7A75-B0C5-4BC5-958B-6DA893C6D562@.microsoft.com...
>> > All, does VS2005 come with SQL Server 2005 Standard? If not, what
>> > version
>> > of SQL does it come with? How can I identify that version installed?
>> >
>> > Thanks for your help.