Creating a Knowledge Base

I am looking for some links or hints on how to get
started creating an SQL knowledge base that eventually I
can write a .net desktop application to interface with
the database. But what I am looking for is some info on
getting started...ie the types of tables I should have in
the Database, what keywords I should use, how to create
an index for searching, and so forth.
Any help would be greatly appreciated...a Google search
seems to only give me people and companies that want to
sell their product, but I want to create and customize my
own.
Thanks
Jeff SmyrskiHi Jeff,
I learned you want to create a SQL knowledge base. To design a database,
there are two components to designing a database: logical and physical.
Logical database design involves modeling your business requirements and
data using database components, such as tables and constraints, without
regard for how or where the data will be physically stored. Physical
database design involves mapping the logical design onto physical media,
taking advantage of the hardware and software features available, which
allows the data to be physically accessed and maintained as quickly as
possible, and indexing.
I am including the following articles for your reference.
Introduction to Relational Database Design
http://www.edm2.com/0612/msql7.html
Database Design Considerations
http://msdn.microsoft.com/library/d...-us/createdb/cm
_8_des_02_62ur.asp
Database Design Thoughts for Packaged Applications
http://www.sqlteam.com/item.asp?ItemID=14929
Database Design and Modeling Fundamentals
http://www.sqlteam.com/item.asp?ItemID=122
Also, due to the complexity of this issue, it would be best to contact
Microsoft Product Support Services via telephone so that a dedicated
Support Professional can assist with your request. To obtain the phone
numbers for specific technology request please take a look at the web site
listed below.
http://support.microsoft.com/defaul...US;PHONENUMBERS
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
This document contains references to a third party World Wide Web site.
Microsoft is providing this information as a convenience to you. Microsoft
does not control these sites and has not tested any software or information
found on these sites; therefore, Microsoft cannot make any representations
regarding the quality, safety, or suitability of any software or
information found there. There are inherent dangers in the use of any
software found on the Internet, and Microsoft cautions you to make sure
that you completely understand the risk before retrieving any software from
the Internet.

Creating a Knowledge Base

I am looking for some links or hints on how to get
started creating an SQL knowledge base that eventually I
can write a .net desktop application to interface with
the database. But what I am looking for is some info on
getting started...ie the types of tables I should have in
the Database, what keywords I should use, how to create
an index for searching, and so forth.
Any help would be greatly appreciated...a Google search
seems to only give me people and companies that want to
sell their product, but I want to create and customize my
own.
Thanks
Jeff Smyrski
Hi Jeff,
I learned you want to create a SQL knowledge base. To design a database,
there are two components to designing a database: logical and physical.
Logical database design involves modeling your business requirements and
data using database components, such as tables and constraints, without
regard for how or where the data will be physically stored. Physical
database design involves mapping the logical design onto physical media,
taking advantage of the hardware and software features available, which
allows the data to be physically accessed and maintained as quickly as
possible, and indexing.
I am including the following articles for your reference.
Introduction to Relational Database Design
http://www.edm2.com/0612/msql7.html
Database Design Considerations
http://msdn.microsoft.com/library/de...us/createdb/cm
_8_des_02_62ur.asp
Database Design Thoughts for Packaged Applications
http://www.sqlteam.com/item.asp?ItemID=14929
Database Design and Modeling Fundamentals
http://www.sqlteam.com/item.asp?ItemID=122
Also, due to the complexity of this issue, it would be best to contact
Microsoft Product Support Services via telephone so that a dedicated
Support Professional can assist with your request. To obtain the phone
numbers for specific technology request please take a look at the web site
listed below.
http://support.microsoft.com/default...S;PHONENUMBERS
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
This document contains references to a third party World Wide Web site.
Microsoft is providing this information as a convenience to you. Microsoft
does not control these sites and has not tested any software or information
found on these sites; therefore, Microsoft cannot make any representations
regarding the quality, safety, or suitability of any software or
information found there. There are inherent dangers in the use of any
software found on the Internet, and Microsoft cautions you to make sure
that you completely understand the risk before retrieving any software from
the Internet.

Creating a Knowledge Base

I am looking for some links or hints on how to get
started creating an SQL knowledge base that eventually I
can write a .net desktop application to interface with
the database. But what I am looking for is some info on
getting started...ie the types of tables I should have in
the Database, what keywords I should use, how to create
an index for searching, and so forth.
Any help would be greatly appreciated...a Google search
seems to only give me people and companies that want to
sell their product, but I want to create and customize my
own.
Thanks
Jeff SmyrskiHi Jeff,
I learned you want to create a SQL knowledge base. To design a database,
there are two components to designing a database: logical and physical.
Logical database design involves modeling your business requirements and
data using database components, such as tables and constraints, without
regard for how or where the data will be physically stored. Physical
database design involves mapping the logical design onto physical media,
taking advantage of the hardware and software features available, which
allows the data to be physically accessed and maintained as quickly as
possible, and indexing.
I am including the following articles for your reference.
Introduction to Relational Database Design
http://www.edm2.com/0612/msql7.html
Database Design Considerations
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm
_8_des_02_62ur.asp
Database Design Thoughts for Packaged Applications
http://www.sqlteam.com/item.asp?ItemID=14929
Database Design and Modeling Fundamentals
http://www.sqlteam.com/item.asp?ItemID=122
Also, due to the complexity of this issue, it would be best to contact
Microsoft Product Support Services via telephone so that a dedicated
Support Professional can assist with your request. To obtain the phone
numbers for specific technology request please take a look at the web site
listed below.
http://support.microsoft.com/default.aspx?scid=fh;EN-US;PHONENUMBERS
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
This document contains references to a third party World Wide Web site.
Microsoft is providing this information as a convenience to you. Microsoft
does not control these sites and has not tested any software or information
found on these sites; therefore, Microsoft cannot make any representations
regarding the quality, safety, or suitability of any software or
information found there. There are inherent dangers in the use of any
software found on the Internet, and Microsoft cautions you to make sure
that you completely understand the risk before retrieving any software from
the Internet.

Creating a dynamic excel file

Is it possible that i can create a dynamic excel file (destination)

ex, i want to create a Dyanamic Excel destination file with a filename base on the date

this will run on jobs. Is this possible?

11172006.xls, 11182006.xls

Sure. With just about any destination, including Excel, the name/location can be dynamic.

1. Create a string variable which represents the excel file name, set the variable's EvaluateAsExpression property to true, and set the expression to something dynamic, for example:

"ExcelTarget" + (DT_WSTR,4)DATEPART("yyyy",GETDATE()) + ".xls"

2. For your excel connection manager, in the expressions node of the Properties tab, set the connection string property to the variable you just created. That's it.

You can skip step I and write the dynamic file name expression directly as in step 2. However, the advantage of a variable is that you can easily view it by setting breakpoints, and looking at the dynamic value in the Locals or Watch windows.

If you could evaluate expressions in the immediate window, there would be less need for the variable to contain the filename.|||

Hi Thanks

anyway I'm gonna test it, if it's going to work, I hope it does.

I'll reply again after i check it out

Anyway thanks, hope this work

|||

Jaegd,

Not sure if that will work. I am working on a similar problem now. I am trying to load the contents of a table into an Excel file every week with a datestamp in the filename. I've tried a few approaches but haven't found a good solution yet. But here's what I found so far.

1. The first approach was to dynamically configure the connection string or filename property of the excel connection to generate a unique name every week. In design time, you will have no problem creating the first file, but at runtime, the package fails in validation as the file doesn't exist. I tried delaying validation but it only delays the inevitable.

The conculsion I came to is that, changing the filenames using expressions will only help you point to a different XL file thats already created but doesnt help you create a new one on the fly.

Jamie, Kirk or someone please comment on this.

2. The second approach is to have a target with a static name like "TargetExcelFile.xls", which already exists, load data into this file and use a file system task to make a copy of it with the appropriate filename, which is configured with a variable or an expression. That seemed to work but there is no way of truncating this excel file before loading every week. The data just keeps appending. I was unable to use a truncate or delete command on the XL connection.

One approach I am trying right now is to create the xl file by issueing an explicit create table command and then load data. I hope it works.

Thanks....

|||

Ravi G wrote:

Jaegd,

Not sure if that will work. I am working on a similar problem now. I am trying to load the contents of a table into an Excel file every week with a datestamp in the filename. I've tried a few approaches but haven't found a good solution yet. But here's what I found so far.

1. The first approach was to dynamically configure the connection string or filename property of the excel connection to generate a unique name every week. In design time, you will have no problem creating the first file, but at runtime, the package fails in validation as the file doesn't exist. I tried delaying validation but it only delays the inevitable.

The conculsion I came to is that, changing the filenames using expressions will only help you point to a different XL file thats already created but doesnt help you create a new one on the fly.

Jamie, Kirk or someone please comment on this.

2. The second approach is to have a target with a static name like "TargetExcelFile.xls", which already exists, load data into this file and use a file system task to make a copy of it with the appropriate filename, which is configured with a variable or an expression. That seemed to work but there is no way of truncating this excel file before loading every week. The data just keeps appending. I was unable to use a truncate or delete command on the XL connection.

One approach I am trying right now is to create the xl file by issueing an explicit create table command and then load data. I hope it works.

Thanks....

My suggestion would be to tweak a bit your 2^nd approach:

You may have, perhaps, an empty file with the required structure, let's say TargetExcelFile.xls that you copy/rename to the excel destination component's expected location prior to the dataflow. For that, you could use a file system task that uses an expression to rename the file with the right name every time. Then in the data flow the excel connection string should use the same expression to find the just renamed file.

|||Ravi, I did indeed forget a step.

Before the dataflow which writes to the dynamic excel target file, add in a Execute SQL task against the Excel connection manager to create the table (aka worksheet). This is what you suggested at the very end and it does work.

For example,
CREATE TABLE `Excel Destination` (
`GeneratedInt_1` INTEGER
)

Then create the connection string variable on the connection manager as follows:

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\\temp\\" + "ExcelTarget" + (DT_WSTR,4)DATEPART("yyyy",GETDATE()) + ".xls" + ";Extended Properties=\"EXCEL 8.0;HDR=YES\";"

And yes, as you were intimating, the delay validation on the dataflow should be set.|||

Jaegd,

I was just about the post the same thing and you beat me to it. I tried my third approach and it works exactly the way I wanted.

By the way, you can set the filename property dynamically instead of the connection string property, its simpler and more readable.

|||

Hi,

I'm kinda new here in SSIS, is it possible that you can help me to do this step by step, I'm kinda lost

Hope you can help me this one

THanks

jaegd wrote:

Ravi, I did indeed forget a step.

Before the dataflow which writes to the dynamic excel target file, add in a Execute SQL task against the Excel connection manager to create the table (aka worksheet). This is what you suggested at the very end and it does work.

For example,
CREATE TABLE `Excel Destination` (
`GeneratedInt_1` INTEGER
)

Then create the connection string variable on the connection manager as follows:

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\\temp\\" + "ExcelTarget" + (DT_WSTR,4)DATEPART("yyyy",GETDATE()) + ".xls" + ";Extended Properties=\"EXCEL 8.0;HDR=YES\";"

And yes, as you were intimating, the delay validation on the dataflow should be set.

|||

Sure. I was planning to post a summary of my findings anyway.

I'll be posting it soon.

|||

This example is useful for loading data from an OLEDB source into a dynamically created Excel file.

NOTE:
This is the core functionality. Things like logging, checkpointing, documentation, etc., are at the user's discretion.

Steps:
1. Click on package properties. Set "DelayValidation" property to True.
The package will not validate tasks, connections, until they are executed.

2. Create a package level variable "XLFileRootDir" as string and set it to the root
directory where you want the excel file to be created.
Example: C:\\Project\Data\

3. Create an Excel connection in the connection manager. Browse to the target directory
and select the destination XL filename or type it in. It doesn't matter if the file doesn't exist.

4. Go to the Excel connection properties and expand the expressions ellipse (The button
with "..." on it).
Under the property drop down, select 'ExcelFilePath' and click on the ellipse to
configure the expression:
@.[User::XLFileRootDir] + (DT_WSTR, 2) DATEPART("DD", GETDATE()) + (DT_WSTR, 2) DATEPART("MM", GETDATE()) + (DT_WSTR, 4) DATEPART("YYYY", GETDATE()) +".xls"
This should create an xl file like 01132007.xls.

5. Add a SQL task to package and double click to edit.
In the general tab, set 'ConnectionType' to 'Excel'.
For 'SQLStatement', enter the create table SQL to create destination table.
For example:
CREATE TABLE `Employee List` (
`EmployeeId` INTEGER,
`EmployeeName` NVARCHAR(20)
)
Copy the create table command. It will come in handy later.

6. Add a Data Flow task. In the data flow editor, add an OLEDB source and an Excel destination.
Configure the source to select EmployeeId and EmployeeName from a table.

7. Connect this to Excel destination. In the destination editor, select the Excel connection in the
manager, choose 'table or view' for data access mode and for 'name of the Excel sheet' click on
new button and paste the create table command from Step 5.
Map the columns appropriately in the mappings tab and you are done.

Let me know if you have any questions.

|||

Hi Ravi G and to other's who answer

thanks to all

anyway does anyone here know's how to generate a guid? and use it as a file name? do i need the script task?

lastly i hope this is not to much to ask, does anyone here know's how to connect to Active directory? the basic concept at least?

anyway thanks to all you guys!!!

cheers

|||

Hi, Ravi G

I successfully created the excel file but i still have one more problem, how would i dynamically map data from it after i created the excel file(I already have the filed and the table)? since the created excel file was the the destination file.

Hope you can still help me on this one

Thanks

Ravi G wrote:

This example is useful for loading data from an OLEDB source into a dynamically created Excel file.

NOTE:
This is the core functionality. Things like logging, checkpointing, documentation, etc., are at the user's discretion.

Steps:
1. Click on package properties. Set "DelayValidation" property to True.
The package will not validate tasks, connections, until they are executed.

2. Create a package level variable "XLFileRootDir" as string and set it to the root
directory where you want the excel file to be created.
Example: C:\\Project\Data\

3. Create an Excel connection in the connection manager. Browse to the target directory
and select the destination XL filename or type it in. It doesn't matter if the file doesn't exist.

4. Go to the Excel connection properties and expand the expressions ellipse (The button
with "..." on it).
Under the property drop down, select 'ExcelFilePath' and click on the ellipse to
configure the expression:
@.[User::XLFileRootDir] + (DT_WSTR, 2) DATEPART("DD", GETDATE()) + (DT_WSTR, 2) DATEPART("MM", GETDATE()) + (DT_WSTR, 4) DATEPART("YYYY", GETDATE()) +".xls"
This should create an xl file like 01132007.xls.

5. Add a SQL task to package and double click to edit.
In the general tab, set 'ConnectionType' to 'Excel'.
For 'SQLStatement', enter the create table SQL to create destination table.
For example:
CREATE TABLE `Employee List` (
`EmployeeId` INTEGER,
`EmployeeName` NVARCHAR(20)
)
Copy the create table command. It will come in handy later.

6. Add a Data Flow task. In the data flow editor, add an OLEDB source and an Excel destination.
Configure the source to select EmployeeId and EmployeeName from a table.

7. Connect this to Excel destination. In the destination editor, select the Excel connection in the
manager, choose 'table or view' for data access mode and for 'name of the Excel sheet' click on
new button and paste the create table command from Step 5.
Map the columns appropriately in the mappings tab and you are done.

Let me know if you have any questions.

|||

You map the columns at design time. You dont need to do that everytime the package runs.

As long as the column names and data types remain the same, you dont have to do anything.

|||

so it's impossible that after i create dynamically the excel file, in the control flow

can i automatically use it as a destination file? will be any problem if i don't map it?

My goal for this one is create a dynamic file in the excel and use it automatically as the destination file

which runs in one package

Thanks

|||

arsonist wrote:

will be any problem if i don't map it?

The package will fail if you don't map it. At the very least you wont see any data in the Excel file.

What we are trying to do is create an excel connection that dynamically creates an excel file under the covers.

You will use the excel connection just as you would use a regular OLEDB connetion, to create your package, as if you are working with a static Excel file.

Hope its clearer.

Creating a dimension based on uniqueidentifier

Is it possible to base a dimension on a uniqueidentifier? My company uses uniqueidentifiers for all primary keys in our tables. However, I am now trying to create a parent-child dimension in Analysis Manager using one of our tables but I always get the error

Unable to count the members of the 'PK' level.

Unable to open the record set.

Error: The count-unique aggregate operation cannot take a uniqueidentifier data type as an argument.

Any suggestions?Note: this is in AS 2000.

Anyone know if this is possible in 2005?

Createing SQL data base record for comma delimted records

I am a new user to SQL an I need to create a data base record from a comma delimted file (.CVS). The CVS file has up to ten fields all alpha/numeric. I must create this data base file using a stored procedure! The data base records will be displayed on a grid screen but this grid view will not be used to update the original CVS file.

hi,

have a look at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=720600&SiteID=1... you can there find a hint on how to bulk insert using a format file into an existing table, but, again, this is to populate a table object..

if you have to create a brand new database, you have to execute a "CREATE DATABASE newDB" statement, using the syntax provided at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create_1up1.asp, then you have to create the table as well or use a SELECT .. INTO statement which creates the destination table at execution time (the table must of course not already exists )

regards

|||

Thank you for the info! I was planning to create the database and file directly in SQL Express! It the stored procedure that will populate the data file that has me hung up! The original file (the CVS - comma delimited file) needs to be inserted into the database file. I seem to know the direction to go--but how to

Once again thank you

|||

hi,

I do apologise and please excuse my poor english, but I'm not able to understand your requirements...

can you please rephrase?

again, please excuse my poor understanding..

regards

|||Guess I am not explaining my problem correctly, my lack of understanding SQL is not helping. I have been tasked to create a data base using SQL Express (this part I think I know how to do) an then write a stored procedure to create a table. The input table is a CSV file that will have up to ten fields in it. Being a CSV file the fields are seperated by comma. After I create this table (with the data in it) I have to display it using VB.NET.|||

hi,

try having a look at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=709813&SiteID=1..

regards

Createing SQL data base record for comma delimted records

I am a new user to SQL an I need to create a data base record from a comma delimted file (.CVS). The CVS file has up to ten fields all alpha/numeric. I must create this data base file using a stored procedure! The data base records will be displayed on a grid screen but this grid view will not be used to update the original CVS file.

hi,

have a look at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=720600&SiteID=1... you can there find a hint on how to bulk insert using a format file into an existing table, but, again, this is to populate a table object..

if you have to create a brand new database, you have to execute a "CREATE DATABASE newDB" statement, using the syntax provided at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create_1up1.asp, then you have to create the table as well or use a SELECT .. INTO statement which creates the destination table at execution time (the table must of course not already exists )

regards

|||

Thank you for the info! I was planning to create the database and file directly in SQL Express! It the stored procedure that will populate the data file that has me hung up! The original file (the CVS - comma delimited file) needs to be inserted into the database file. I seem to know the direction to go--but how to

Once again thank you

|||

hi,

I do apologise and please excuse my poor english, but I'm not able to understand your requirements...

can you please rephrase?

again, please excuse my poor understanding..

regards

|||Guess I am not explaining my problem correctly, my lack of understanding SQL is not helping. I have been tasked to create a data base using SQL Express (this part I think I know how to do) an then write a stored procedure to create a table. The input table is a CSV file that will have up to ten fields in it. Being a CSV file the fields are seperated by comma. After I create this table (with the data in it) I have to display it using VB.NET.|||

hi,

try having a look at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=709813&SiteID=1..

regards

Create View

how to create view in sql server 2005 that recognize Identity And Primary key of base tableI don't understand what the requirement is, over any normal view. See CREATE VIEW in Books Online perhaps? Why is this related to SQL Server Integration Services, the title of this forum?|||

SELECT COLS.table_schema
,COLS.Table_name
,COLS.COLUMN_NAME
,cols.CONSTRAINT_NAME
,ac.IS_identity
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS COLS
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS CONS
ON COLS.CONSTRAINT_NAME = CONS.CONSTRAINT_NAME
JOIN sys.all_columns ac
ON OBJECT_NAME(ac.object_id) = COLS.table_name
AND ac.name=COLS.COLUMN_NAME
WHERE CONS.CONSTRAINT_TYPE LIKE 'PRIMARY KEY'
ORDER BY COLS.CONSTRAINT_NAME, COLS.ORDINAL_POSITION

as i wrote this pretty quick and didnt test it fully.. using it is on your own risk ;)

Guldmann, platon.dk