Creating a new database

I created a database using the following command :

create database krish on (Name='krish', filename='C:\Program Files\Microsoft SQL Server\MSSQL\data\krish.mdf', size=25, maxsize=50,filegrowth=5%)

This actually created the database. Now, I wanted to view this database info being stored inside the SQL Server system tables. I looked at "sysdatabases" table and found an entry as expected for "krish" but I could not trace where the info corresponding to the size of the database was stored ie.25MB . (I looked ad "sysdevices" but couldn't find any entry for the newly created database).

In which table is it stored ?

Any help is appreciated.select size
from krish.dbo.sysfiles|||Thanks Hans. That worked

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 database

Hi;
If I have a schema for creating a database, is there a command line
application I can run and pass it the filename of the schema and it will then
create that database? If so, can someone point me to a url showing the
command line syntax for it?
And, what registry entry can I use to determine where this program is - as
some people do not install in C:\Program Files\...
thanks - dave
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com
Cubicle Wars - http://www.windwardreports.com/film.htm
Maybe this helps. http://support.microsoft.com/kb/325003
Use the variable %programfiles%
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"David Thielen" wrote:
| Hi;
|
| If I have a schema for creating a database, is there a command line
| application I can run and pass it the filename of the schema and it will
then
| create that database? If so, can someone point me to a url showing the
| command line syntax for it?
|
| And, what registry entry can I use to determine where this program is - as
| some people do not install in C:\Program Files\...
|
| thanks - dave
|
| --
| thanks - dave
| david_at_windward_dot_net
| http://www.windwardreports.com
|
| Cubicle Wars - http://www.windwardreports.com/film.htm
|
|
|||osql is the tool I need.
I am retecient to use %programfiles% because we used to put Sql Server on d:
while programfiles was on c: - so on those systems at least, that would not
find it.
Is there anything Sql Server writes to the registry with it's location? I
think there was and I used it in a program where I used to work - but I don't
have access to that code.
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com
Cubicle Wars - http://www.windwardreports.com/film.htm
"Dave Patrick" wrote:

> Maybe this helps. http://support.microsoft.com/kb/325003
> Use the variable %programfiles%
> --
> Regards,
> Dave Patrick ...Please no email replies - reply in newsgroup.
> Microsoft Certified Professional
> Microsoft MVP [Windows]
> http://www.microsoft.com/protect
> "David Thielen" wrote:
> | Hi;
> |
> | If I have a schema for creating a database, is there a command line
> | application I can run and pass it the filename of the schema and it will
> then
> | create that database? If so, can someone point me to a url showing the
> | command line syntax for it?
> |
> | And, what registry entry can I use to determine where this program is - as
> | some people do not install in C:\Program Files\...
> |
> | thanks - dave
> |
> | --
> | thanks - dave
> | david_at_windward_dot_net
> | http://www.windwardreports.com
> |
> | Cubicle Wars - http://www.windwardreports.com/film.htm
> |
> |
>
>
|||You could look at 'Path' and or 'SQLPath' found below but you really
shouldn't need the fully qualified path;
HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\80\Tools\ClientSetup
HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\90\Tools\ClientSetup
since the installation of client tools appends to the system path variable.
C:\Program Files\Microsoft SQL Server\90\Tools\binn\;C:\Program
Files\Microsoft SQL Server\80\Tools\Binn\

Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"David Thielen" wrote:
| osql is the tool I need.
|
| I am retecient to use %programfiles% because we used to put Sql Server on
d:
| while programfiles was on c: - so on those systems at least, that would
not
| find it.
|
| Is there anything Sql Server writes to the registry with it's location? I
| think there was and I used it in a program where I used to work - but I
don't
| have access to that code.
|
| --
| thanks - dave
| david_at_windward_dot_net
| http://www.windwardreports.com
|
| Cubicle Wars - http://www.windwardreports.com/film.htm
|||thank you - that looks perfect.
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com
Cubicle Wars - http://www.windwardreports.com/film.htm
"Dave Patrick" wrote:

> You could look at 'Path' and or 'SQLPath' found below but you really
> shouldn't need the fully qualified path;
> HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\80\Tools\ClientSetup
> HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\90\Tools\ClientSetup
> since the installation of client tools appends to the system path variable.
> C:\Program Files\Microsoft SQL Server\90\Tools\binn\;C:\Program
> Files\Microsoft SQL Server\80\Tools\Binn\
>
> --
> Regards,
> Dave Patrick ...Please no email replies - reply in newsgroup.
> Microsoft Certified Professional
> Microsoft MVP [Windows]
> http://www.microsoft.com/protect
> "David Thielen" wrote:
> | osql is the tool I need.
> |
> | I am retecient to use %programfiles% because we used to put Sql Server on
> d:
> | while programfiles was on c: - so on those systems at least, that would
> not
> | find it.
> |
> | Is there anything Sql Server writes to the registry with it's location? I
> | think there was and I used it in a program where I used to work - but I
> don't
> | have access to that code.
> |
> | --
> | thanks - dave
> | david_at_windward_dot_net
> | http://www.windwardreports.com
> |
> | Cubicle Wars - http://www.windwardreports.com/film.htm
>
>
|||You can use sp_helpfile to return the file locations.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"David Thielen" <thielen@.nospam.nospam> wrote in message
news:FC83AF0A-B244-4B64-A83F-36A21A6ADECA@.microsoft.com...[vbcol=seagreen]
> osql is the tool I need.
> I am retecient to use %programfiles% because we used to put Sql Server on
> d:
> while programfiles was on c: - so on those systems at least, that would
> not
> find it.
> Is there anything Sql Server writes to the registry with it's location? I
> think there was and I used it in a program where I used to work - but I
> don't
> have access to that code.
> --
> thanks - dave
> david_at_windward_dot_net
> http://www.windwardreports.com
> Cubicle Wars - http://www.windwardreports.com/film.htm
>
>
> "Dave Patrick" wrote:
|||You're welcome.
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"David Thielen" wrote:
| thank you - that looks perfect.
|
| --
| thanks - dave
| david_at_windward_dot_net
| http://www.windwardreports.com
|
| Cubicle Wars - http://www.windwardreports.com/film.htm

Creating a custom Delivery Extension (File Share)

I would like to create a custom delivery extension wherby I take the filename of my report and then append a date time stamp to it. While I have some knowledge of RS I have little practical programming expierience.

I have little fear of learning something new, but I like to take known good working model, understand why / how it works and then apply that to my situation.

Are there any "Dummies" type of tutorials out there to get me started down this road?

Thanks for reading

hi , you can use this code .

public bool Deliver(Notification notification)
{
string reportName = notification.Report.Name;
}

Creating a custom Delivery Extension (File Share)

I would like to create a custom delivery extension wherby I take the filename of my report and then append a date time stamp to it. While I have some knowledge of RS I have little practical programming expierience.

I have little fear of learning something new, but I like to take known good working model, understand why / how it works and then apply that to my situation.

Are there any "Dummies" type of tutorials out there to get me started down this road?

Thanks for reading

hi , you can use this code .

public bool Deliver(Notification notification)
{
string reportName = notification.Report.Name;
}