Determining Security: creating

Showing posts with label creating. Show all posts

Monday, March 19, 2012

Details on Subscription.Status property

Hi,
I need to know all the possible values that the "Subscription.Status"
property can return ( I'm creating my own UI and all text has to be
translated into another language )
Thanks,
AntonioThis field can contain an unknown number of values. Delivery extensions are
allowed to write any value in there. The two delivery extensions that
shipped with RS (file and email) will write out Success and error messages.
The error messages alone will be numerous.
The status however should already be localized into the langue of the user
which created the subscription. Is this not sufficient?
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Antonio Bruno" <AntonioBruno@.discussions.microsoft.com> wrote in message
news:CA153CAC-3246-404D-92A6-75E9839A2528@.microsoft.com...
> Hi,
> I need to know all the possible values that the "Subscription.Status"
> property can return ( I'm creating my own UI and all text has to be
> translated into another language )
> Thanks,
> Antonio|||Daniel,
Thanks for that.
I'm using the standard e-mail extension and what I really would like to have
is a way of programatically distinguish an error status from a success
status. Could it be that all error messages have the string "error" somewhere
in there?
I've decided best not to develop a new security extension (it's an Internet
app.), and I'm working my way around using the standard security and the
web-service. So all subscriptions are created by the same user via the
web-service (I have a separate table on my app. DB to know who's which).
When you say that the status will be translated into the user's language I'm
assuming that it'll be into one of the available ones in RS. Well ... my
language isn't any of those, so I would always have to translate. One thing
that might be helpfull in the future would be to have either the sources for
the resource files or some other way that would enable us to easily do the
translations ourselves in case of unsupported languages. This would also make
it easy to know all strings that an extension outputs, which would be a nice
way of solving my problem.
Anyway, your tip was very helpfull. I'll "reflector" the e-mail extension
resource file and try to figure out those values.
Thanks again,
Antonio
"Daniel Reib [MSFT]" wrote:
> This field can contain an unknown number of values. Delivery extensions are
> allowed to write any value in there. The two delivery extensions that
> shipped with RS (file and email) will write out Success and error messages.
> The error messages alone will be numerous.
> The status however should already be localized into the langue of the user
> which created the subscription. Is this not sufficient?
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Antonio Bruno" <AntonioBruno@.discussions.microsoft.com> wrote in message
> news:CA153CAC-3246-404D-92A6-75E9839A2528@.microsoft.com...
> > Hi,
> >
> > I need to know all the possible values that the "Subscription.Status"
> > property can return ( I'm creating my own UI and all text has to be
> > translated into another language )
> >
> > Thanks,
> >
> > Antonio
>
>|||As a follow-up on my previous post (in case someone else needs it):
Using Lutz's Reflector on ReportingServicesLibrary.dll, the
Microsoft.ReportingServices.Library.RepLibRes.resources shows the following
keys:
New Subscription=New Subscription
SubscriptionDone=Done: {0} processed of {1} total; {2} errors.
SubscriptionError=Error: {0}
SubscriptionParametersInvalid=Subscription contains invalid parameters.
SubscriptionPending=Pending
SubscriptionProcessing=Processing: {0} processed of {1} total; {2} errors.
SubscriptionReady=Ready
I'll just parse these strings to do my translations.
Regards,
Antonio
"Daniel Reib [MSFT]" wrote:
> This field can contain an unknown number of values. Delivery extensions are
> allowed to write any value in there. The two delivery extensions that
> shipped with RS (file and email) will write out Success and error messages.
> The error messages alone will be numerous.
> The status however should already be localized into the langue of the user
> which created the subscription. Is this not sufficient?
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Antonio Bruno" <AntonioBruno@.discussions.microsoft.com> wrote in message
> news:CA153CAC-3246-404D-92A6-75E9839A2528@.microsoft.com...
> > Hi,
> >
> > I need to know all the possible values that the "Subscription.Status"
> > property can return ( I'm creating my own UI and all text has to be
> > translated into another language )
> >
> > Thanks,
> >
> > Antonio
>
>|||While this will get you started. The valuable information for the error is
what is inside the {0}. This information will be in the language in which
the subscription was created. (one of the supported languages). If all you
are looking for is pass fail, then looking for error will suffice (assuming
all subscriptions are made in English). We are looking at putting a
pass/fail property on the subscription object to help in cases like this.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Antonio Bruno" <AntonioBruno@.discussions.microsoft.com> wrote in message
news:B35CCFE7-89D3-4B82-9844-367479E8FD06@.microsoft.com...
> As a follow-up on my previous post (in case someone else needs it):
> Using Lutz's Reflector on ReportingServicesLibrary.dll, the
> Microsoft.ReportingServices.Library.RepLibRes.resources shows the
> following
> keys:
> New Subscription=New Subscription
> SubscriptionDone=Done: {0} processed of {1} total; {2} errors.
> SubscriptionError=Error: {0}
> SubscriptionParametersInvalid=Subscription contains invalid parameters.
> SubscriptionPending=Pending
> SubscriptionProcessing=Processing: {0} processed of {1} total; {2} errors.
> SubscriptionReady=Ready
> I'll just parse these strings to do my translations.
> Regards,
> Antonio
>
> "Daniel Reib [MSFT]" wrote:
>> This field can contain an unknown number of values. Delivery extensions
>> are
>> allowed to write any value in there. The two delivery extensions that
>> shipped with RS (file and email) will write out Success and error
>> messages.
>> The error messages alone will be numerous.
>> The status however should already be localized into the langue of the
>> user
>> which created the subscription. Is this not sufficient?
>> --
>> -Daniel
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "Antonio Bruno" <AntonioBruno@.discussions.microsoft.com> wrote in message
>> news:CA153CAC-3246-404D-92A6-75E9839A2528@.microsoft.com...
>> > Hi,
>> >
>> > I need to know all the possible values that the "Subscription.Status"
>> > property can return ( I'm creating my own UI and all text has to be
>> > translated into another language )
>> >
>> > Thanks,
>> >
>> > Antonio
>>

Detailed description on 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.

Detailed description on 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.

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,

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.

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:

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

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.

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.

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

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.

Detailed description on 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.

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,

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.

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:

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

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.

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.

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

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.

Wednesday, March 7, 2012

Destination InputColumnCollection is empty

I am creating and running a package programmatically. I have the source component set up fine, and the destination component seems good, but when the package is run, it gets the error message: "Excel destination failed validation and returned validation status "VS_NEEDSNEWMETADATA" ". This would lead me to believe that I need a ReinitializeMetaData() call, but I already have that (see below). How do I fix this? Thanks for your help.

' Create and configure an OLE DB destination.

Dim conDest As ConnectionManager = package.Connections.Add("Excel")

conDest.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _

Dts.Variables("User::gsExcelFile").Value.ToString & ";Extended Properties=""Excel 8.0;HDR=YES"""

conDest.Name = "Excel File"

conDest.Description = "Excel File"

Dim destination As IDTSComponentMetaData90 = dataFlowTask.ComponentMetaDataCollection.New

destination.ComponentClassID = "DTSAdapter.ExcelDestination"

' Create the design-time instance of the destination.

Dim destDesignTime As CManagedComponentWrapper = destination.Instantiate

' The ProvideComponentProperties method creates a default input.

destDesignTime.ProvideComponentProperties()

destination.RuntimeConnectionCollection(0).ConnectionManagerID = conDest.ID

destination.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.ToConnectionManager90(conDest)

destDesignTime.SetComponentProperty("AccessMode", 0)

destDesignTime.SetComponentProperty("OpenRowset", Dts.Variables("User::gsSheetName").Value.ToString)

destDesignTime.AcquireConnections(Nothing)

destDesignTime.ReinitializeMetaData()

destDesignTime.ReleaseConnections()

' Create the path from source to destination.

Dim path As IDTSPath90 = dataFlowTask.PathCollection.New

path.AttachPathAndPropagateNotifications(source.OutputCollection(0), _

destination.InputCollection(0))

' Get the destination's default input and virtual input.

Dim input As IDTSInput90 = destination.InputCollection(0)

Dim vInput As IDTSVirtualInput90 = input.GetVirtualInput

' Iterate through the virtual input column collection.

For Each vColumn As IDTSVirtualInputColumn90 In vInput.VirtualInputColumnCollection

' Call the SetUsageType method of the destination

' to add each available virtual input column as an input column.

destDesignTime.SetUsageType(input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY)

You have already called ReinitializeMetaData, but for a destination it should be done after you have connected the input. ReinitializeMetaData for destination is all about creating input columns and mapping them to external columns. If you have no input, then it will no do much. Subsequently I woudl expect validate to fail saying to call RMD again, as no columns is ivalid in my book.|||

I just tried moving the ReinitializeMetaData call to after the SetUsageType loop. There is no validation error now, but the package still fails. It appears that the source output was not mapped to the dest input. I get warning messages that

"the source columns are not subsequently being used in the data flow", and also an error message on the destination that "the number of columns is incorrect", and

"Cannot create OLEDB accessor. Verify that the column metadata is valid.", and finally a return error code of 0xc0202025

Is there something else I am missing, or something else in my code that is incorrect, out of order? Thanks...

|||

Have a think about the process required, and what each method does. For a destination, it is usual that you have an input and some external metadata columns. You need to first select columns. This makes them into input columns, and available to the component. You select columns from the virtual input, which represents all columns potentially available. Once you have input columns, you map them to the external metadata columns. These will have been generated during ReinitializeMetaData, and represent the external destination itself.

If you have a look at components with the Advanced Editor, things like how inputs, outputs and external columns are all used, and related.

Put simply, you need to select colums (from the virtual input) and then map what are now input columns, to the external columns. Here is a snippet from MS supplied CreatePackage sample. You can download new and updated samples from MS Downloads.

Code Snippet

#region MapFlatFileDestination Columns
private void MapFlatFileDestinationColumns()
{
CManagedComponentWrapper wrp = this.flatfileDestination.Instantiate();

IDTSVirtualInput90 vInput = this.flatfileDestination.InputCollection[0].GetVirtualInput();
foreach (IDTSVirtualInputColumn90 vColumn in vInput.VirtualInputColumnCollection)
{
wrp.SetUsageType(this.flatfileDestination.InputCollection[0].ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY);
}

// For each column in the input collection
// find the corresponding external metadata column.
foreach (IDTSInputColumn90 col in this.flatfileDestination.InputCollection[0].InputColumnCollection)
{
IDTSExternalMetadataColumn90 exCol = this.flatfileDestination.InputCollection[0].ExternalMetadataColumnCollection[col.Name];
wrp.MapInputColumn(this.flatfileDestination.InputCollection[0].ID, col.ID, exCol.ID);
}
}
#endregion

|||

Thanks for the info, Darren. Sorry if I seem a little dense, I didn't see any example of MapInputColumn in the BOL, so that was new to me Smile I still have a problem, though. If you look at my code below, you'll see that I added the MapInputColumn to the end. However, when I run it, the destination.InputCollection(0).InputColumnCollection set is empty. I specified an existing file with a table defined. The SetUsageType works, so there are the correct number of columns (4) in the VirtualInputColumnCollection. Again, sorry to have so many questions, but we're really close on this one and I just want to solve it.

Dim conDest As ConnectionManager = package.Connections.Add("Excel")

conDest.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _

Dts.Variables("User::gsExcelFile").Value.ToString & ";Extended Properties=""Excel 8.0;HDR=YES"""

conDest.Name = "Excel File"

conDest.Description = "Excel File"

Dim destination As IDTSComponentMetaData90 = dataFlowTask.ComponentMetaDataCollection.New

destination.ComponentClassID = "DTSAdapter.ExcelDestination"

' Create the design-time instance of the destination.

Dim destDesignTime As CManagedComponentWrapper = destination.Instantiate

' The ProvideComponentProperties method creates a default input.

destDesignTime.ProvideComponentProperties()

destination.RuntimeConnectionCollection(0).ConnectionManagerID = conDest.ID

destination.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.ToConnectionManager90(conDest)

destDesignTime.SetComponentProperty("AccessMode", 0)

destDesignTime.SetComponentProperty("OpenRowset", Dts.Variables("User::gsSheetName").Value.ToString)

' Create the path from source to destination.

Dim path As IDTSPath90 = dataFlowTask.PathCollection.New

path.AttachPathAndPropagateNotifications(source.OutputCollection(0), _

destination.InputCollection(0))

' Get the destination's default input and virtual input.

Dim input As IDTSInput90 = destination.InputCollection(0)

Dim vInput As IDTSVirtualInput90 = input.GetVirtualInput

MsgBox(input.InputColumnCollection.Count)

' Iterate through the virtual input column collection.

For Each vColumn As IDTSVirtualInputColumn90 In vInput.VirtualInputColumnCollection

' Call the SetUsageType method of the destination

' to add each available virtual input column as an input column.

destDesignTime.SetUsageType(input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY)

destDesignTime.AcquireConnections(Nothing)

destDesignTime.ReinitializeMetaData()

destDesignTime.ReleaseConnections()

Dim exCol As IDTSExternalMetadataColumn90

For Each column As IDTSInputColumn90 In input.InputColumnCollection

exCol = destination.InputCollection(0).ExternalMetadataColumnCollection(column.Name)

destDesignTime.MapInputColumn(destination.InputCollection(0).ID, column.ID, exCol.ID)

|||

I am creating/running a package programmatically. I have the source component defined, and am trying to map the destination's input columns. The problem is that the destination.InputCollection(0).InputColumnCollection is empty, even though I have specified a destination Excel file that exists and has the specified table. The VirtualInputColumnCollection does have the correct columns, so I don't know why the InpuColumnCollection would be empty.

Am I missing a step in my code, or is there a way to create the columns manually? Thanks for your help.

Code Snippet

' Create and configure an OLE DB destination.

Dim conDest As ConnectionManager = package.Connections.Add("Excel")

conDest.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _

Dts.Variables("User::gsExcelFile").Value.ToString & ";Extended Properties=""Excel 8.0;HDR=YES"""

conDest.Name = "Excel File"

conDest.Description = "Excel File"

Dim destination As IDTSComponentMetaData90 = dataFlowTask.ComponentMetaDataCollection.New

destination.ComponentClassID = "DTSAdapter.ExcelDestination"

' Create the design-time instance of the destination.

Dim destDesignTime As CManagedComponentWrapper = destination.Instantiate

' The ProvideComponentProperties method creates a default input.

destDesignTime.ProvideComponentProperties()

destination.RuntimeConnectionCollection(0).ConnectionManagerID = conDest.ID

destination.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.ToConnectionManager90(conDest)

destDesignTime.SetComponentProperty("AccessMode", 0)

destDesignTime.SetComponentProperty("OpenRowset", Dts.Variables("User::gsSheetName").Value.ToString)

' Create the path from source to destination.

Dim path As IDTSPath90 = dataFlowTask.PathCollection.New

path.AttachPathAndPropagateNotifications(source.OutputCollection(0), _

destination.InputCollection(0))

' Get the destination's default input and virtual input.

Dim input As IDTSInput90 = destination.InputCollection(0)

Dim vInput As IDTSVirtualInput90 = input.GetVirtualInput

' Iterate through the virtual input column collection.

For Each vColumn As IDTSVirtualInputColumn90 In vInput.VirtualInputColumnCollection

' to add each available virtual input column as an input column.

destDesignTime.SetUsageType(input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY)

destDesignTime.AcquireConnections(Nothing)

destDesignTime.ReinitializeMetaData()

destDesignTime.ReleaseConnections()

Dim exCol As IDTSExternalMetadataColumn90

' This for loop does not get executed because the collection is empty

For Each column As IDTSInputColumn90 In destination.InputCollection(0).InputColumnCollection

exCol = destination.InputCollection(0).ExternalMetadataColumnCollection(column.Name)

destDesignTime.MapInputColumn(destination.InputCollection(0).ID, column.ID, exCol.ID)

app.SaveToXml("c:\newpackage.dtsx", package, Nothing)

Dim ret As DTSExecResult

ret = package.Execute()

Console.WriteLine(ret.ToString)

MsgBox(ret.ToString)

|||

Have a look at this post where i had a similar problem.

http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=2181121&SiteID=17

Try to delegate the input column generation to the destination component.

Manuel Bauer

|||

I'll guess that you should call RMD before you select the columns. The SetUsageType method selects the column, which makes it avilable in the Input Column Collection.

I have written a complete sample. It assumes you have a workbook in the write location, with a sheet. We will leave creating workbooks and sheets for another day. Assuming the file is there and you have a local SQL server default instance, it will work I assure you! Pick what you need -

Code Block

namespace SSISExcelExport

{

class SimplePackage

{

public void CreatePackage()

{

Package package = new Package();

// Add the SQL connection

ConnectionManager sqlConnection = AddSqlConnection(package, "localhost", "master");

// Add the Excel connection

ConnectionManager excelConnection = AddExcelConnection(package, @."C:\Temp\Export.xls");

// Add the Data Flow task

package.Executables.Add("DTS.Pipeline.1");

// Get the pipeline

TaskHost dataFlowTask = package.Executables[0] as TaskHost;

MainPipe pipeline = dataFlowTask.InnerObject as MainPipe;

// Add the SQL Server source

string query = "SELECT id, name FROM sysobjects";

IDTSComponentMetaData90 source = pipeline.ComponentMetaDataCollection.New();

source.ComponentClassID = "DTSAdapter.OleDbSource.1";

source.Name = "SQL Source";

CManagedComponentWrapper sourceInstance = source.Instantiate();

sourceInstance.ProvideComponentProperties();

source.RuntimeConnectionCollection[0].ConnectionManagerID = sqlConnection.ID;

source.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(sqlConnection);

sourceInstance.SetComponentProperty("AccessMode", 2);

sourceInstance.SetComponentProperty("SqlCommand", query);

sourceInstance.AcquireConnections(null);

sourceInstance.ReinitializeMetaData();

sourceInstance.ReleaseConnections();

// Add Excel destination

string sheetName = "Sheet";

IDTSComponentMetaData90 destination = pipeline.ComponentMetaDataCollection.New();

destination.ComponentClassID = "DTSAdapter.ExcelDestination.1";

destination.Name = "Excel Destination";

CManagedComponentWrapper destinationInstance = destination.Instantiate();

destinationInstance.ProvideComponentProperties();

destination.RuntimeConnectionCollection[0].ConnectionManagerID = excelConnection.ID;

destination.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(excelConnection);

destinationInstance.SetComponentProperty("AccessMode", 0);

destinationInstance.SetComponentProperty("OpenRowset", sheetName);

destinationInstance.AcquireConnections(null);

destinationInstance.ReinitializeMetaData();

destinationInstance.ReleaseConnections();

IDTSInput90 destinationInput = destination.InputCollection[0];

// Connect the source to the destination

IDTSPath90 path = pipeline.PathCollection.New();

path.AttachPathAndPropagateNotifications(source.OutputCollection[0], destinationInput);

// Select destination input columns

IDTSVirtualInput90 virtualInput = destinationInput.GetVirtualInput();

foreach (IDTSVirtualInputColumn90 column in virtualInput.VirtualInputColumnCollection)

{

destinationInstance.SetUsageType(destinationInput.ID, virtualInput, column.LineageID, DTSUsageType.UT_READONLY);

}

// Map input column to external metadata column.

for (int index = 0; index < destinationInput.InputColumnCollection.Count; index++)

{

destinationInstance.MapInputColumn(destinationInput.ID, destinationInput.InputColumnCollection[index].ID, destinationInput.ExternalMetadataColumnCollection[index].ID);

}

#if DEBUG

// Save package to disk, DEBUG only

new Application().SaveToXml(@."C:\Temp\" + package.Name, package, null);

#endif

package.Execute();

}

#region Add Connections

private static ConnectionManager AddExcelConnection(Package package, string filename)

{

return AddConnection(package, "EXCEL", String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=YES\"", filename));

}

private static ConnectionManager AddSqlConnection(Package package, string server, string database)

{

return AddConnection(package, "OLEDB", String.Format("Provider=SQLOLEDB.1;Data Source={0};Persist Security Info=False;Initial Catalog={1};Integrated Security=SSPI;", server, database));

}

private static ConnectionManager AddConnection(Package package, string type, string connectionString)

{

ConnectionManager manager = package.Connections.Add(type);

manager.ConnectionString = connectionString;

manager.Name = String.Format("{0} Connection", type);

return manager;

}

#endregion

}

Saturday, February 25, 2012

Designing relational tables

Hi, not sure if this is the right forum for this question.

I am creating relational tables for the first time in sql server express. I will have an orderItems table and an orders table. the MenuItems table is the problem. It is a catalogue of books. There will be about ten columns. all are unique to each book. i.e isbn number, title, author, publisher etc. but ten columns seems to be quite cumbersome. it may be easier to break the table down into two tables (i.e. primary details and secondary details perhaps) However to populate the table in the first place it would be easier to have it as one table instead of opening and closing 2 tables Adding the odd book to the two tables in the future would not be a problem. so the question is can i create a table and then brak it into two relational tables afterwards. If so how do i do this. this is my foirst go at relational tables and i am still trying to get a handle on visualising them. If my logic is up the wall please let me know...

Nick

Yes you can do this. Once you have your "big" table populated simply create the second smaller table and just execute an update statement:

update mySmallTable
set field1 = b.field1
, field2 = b.field2
, field3 = b.field3
from myBigTable b

|||

Great. Im really moving forward now. thanks for your time

Nick

Designing calendar databases

Is there a best practice/sample database design for creating database applications for scheduling events?

I am looking at creating an online work schedule using the .net calendar control tied back to a SQL Server database (think Outlook Calendar). I am at a loss as to how to efficiently design the database. Will I need to create an entry for every day? Is it prudent to use a datetime value for a primary key (I would have to whack the time portion)? How does one handle the number of days in a month/year?

Anyhoo... I would greatly appreciate any ideas/resources on how to design a database of this type.

Thanks in advance.I can't see any reason to have an entry for every day. Why have an entry if no events are scheduled for a certain day? Just store the events in an event table with a date as one of the columns.|||I am also, have you had any luck?|||You probably have a solution to this, but I was tasked with creating a calendar to schedule meeting rooms as one of my first projects for my current employer.

My table calendar has the following design.

CalendarID, int, autoinc
calendarDateTime, datetime
Appointment, varchar, 50
Reservedby, varchar, 50
Duration, varchar, 50
Freetime, varchar, 50
RoomValues, varchar, 50
AppointmentTime, varchar, 50

Each appointment is it's own row. The asp.net pages check to see if a appointment exists at the time the user selects, if not, it checks the duration which is in 1/2 hour increments if there is an overlap it won't enter the appointment. Otherwise if the time is free it inserts the record. I then query all the appointments for the day and build a table to display the information in a graphical way, Green background for free time, and yellow for occupied. I only display from 8:00AM to 5:00PM on the webpage, but allow scheduling to be anytime.

I hope this helps. BTW, it has been working like a charm since the first week I started here back in March.

Greg

Sunday, February 19, 2012

Design Question

I am creating a table and would like to know the most efficent way to do
this.
One of the colums will be text datatype and can have several thousand
characters in it (less than10k). The 'ntext' datatype in SQL and hold over
a million characters so that isn't a problem.
The question is: Is it more efficent to have the data directly in the
database or should I write it to a file and store a filename?
I would prefer storing the data directly in the table but if this is a
terribly bad idea I want to know. The problem I have with the file aspect
is maintenace of all the files and over years of use there could be quite a
few files generated.

Thank you in advance,
EricEric, in my opinion the choice of keeping the data in the database and
keeping only a reference to external data really depends on the use of
the data.

Will the files in question be updated after initial creation?
How often?
Are the updates complete file replacement or changes to data within the
file?
What are the chances multiple uses will attempt to update a file at the
same time?
How many different applications need access to the data?

The more changes, changes to only peices of the files, the more times
the data is accessed, and the more concurrent update activity the more
I would favor storing the data in the database.
IMHO -- Mark D Powell --|||Thank you for your response. Answers to your questions.

> Will the files in question be updated after initial creation?
Yes
> How often?
A small subset would rarely be updated.
> Are the updates complete file replacement or changes to data within the
> file?
The updates would be edits only.
> What are the chances multiple uses will attempt to update a file at the
> same time?
Infrequent.
> How many different applications need access to the data?
1

I favor the database method too, despite the fact that the updates would not
be a frequent.
Partly for security, easy to back up the database compared to many files.
My concern was eventual database size, but there must be many large database
that store records at least as large as I mentioned.

Thanks again,
Eric|||Beringer (borden_eric@.invalid.com) writes:
> I am creating a table and would like to know the most efficent way to do
> this.
> One of the colums will be text datatype and can have several thousand
> characters in it (less than10k). The 'ntext' datatype in SQL and hold
> over a million characters so that isn't a problem.
> The question is: Is it more efficent to have the data directly in the
> database or should I write it to a file and store a filename?
> I would prefer storing the data directly in the table but if this is a
> terribly bad idea I want to know. The problem I have with the file
> aspect is maintenace of all the files and over years of use there could
> be quite a few files generated.

There are people who quite strongly recommend storing only the the filename
in the database, and the text as separate files.

I am not one of those. The point with storing on file is mainly simplicity
in programming. The text datatype is messy to work with, not the least in
ADO.

On the other hand, you perfectly right that backing one database is
easier than backing up one database + lots of files. Add to that you
cannot get transactional consistency with a file system, and that a
file may disappear without the knowledge of SQL Server.

Thus, in my opinion, storing only the filename is a low-budget solution
when your requirements are lax. Storing the data in the database may
require more development, and, yes, it can also have some negative
effect on performance. But it is a more robust solution.

And in your case, since your texts are no more than 10000 chars, it's
not really an issue. Go with ntext.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||SQL server is capable of handling that sort of data in a column.. So I
would suggest holding it in the database rather than putting them in a
filesystem. What will happen if you need to migrate things to a
different server.. or one of the path to that folder changes...? Plus
if you need to remove the data, you need to delete the file plus the
path/file name reference in the database.. Things can go wrong if you
dont stay consistant with all the updates... Plus, if more than one
person need to update one record in a particular time(dont ask me how
often thats gonna happen... but as an architect you need count that
into account when making a decision) that will complicate things...!
Plus its easy to do searches of a text in sql than thru windows
search....

Design Question

Here is my scenario:
I'm creating a thin client viewer/editor for a client that has a rebate
program.
There are several types of rebate programs, and incentives are different for
each. There are approximately 160 predicating companies and each "reports"
it's sales amount or other qualifying criteria each quarter.
Now, all this is pretty vanilla until, except that the rebate percentage for
each company can change in midstream of a fiscal year.
The old records (pre percentage) change must be kept for viewing only, but
post change must be editable and updateable.
What is the best way to accomplish this without having to nest a lot of
tables?
Thanks in advance for you input..
~ChrisI'd track the old and new percentage rates in a table and based on your
query date (Run from date) you select the proper percentage rate from
that table to use for the calculation.
Ex.
PercentageValidDate Percentage
1/1/2000 6.5
1/1/2003 6.85
6/1/2003 6.93
SELECT TOP 11 Percentage where PercentageValidDATE < [paramater run date]
order by PercentageValidDATE DESC
Smitty wrote:
> Here is my scenario:
> I'm creating a thin client viewer/editor for a client that has a rebate
> program.
> There are several types of rebate programs, and incentives are different for
> each. There are approximately 160 predicating companies and each "reports"
> it's sales amount or other qualifying criteria each quarter.
> Now, all this is pretty vanilla until, except that the rebate percentage for
> each company can change in midstream of a fiscal year.
> The old records (pre percentage) change must be kept for viewing only, but
> post change must be editable and updateable.
> What is the best way to accomplish this without having to nest a lot of
> tables?
> Thanks in advance for you input..
> ~Chris
>

Friday, February 17, 2012

Design opinion?

Hi
I am starting the task of creating a data warehouse for my company's OLTP
system and we would like to offload reporting to a reporting schema at a
data warehouse. We have 2 kinds of reports: operational (used daily and
need data just about real time) and analytical (used to determine sales
trends, etc).
For the analytical reports, I think we can just do a nightly ETL load since
the data doesn't need to be too recent. However, I was thinking of using
replication to replicate the table data used in the operational reports,
capturing the data in stored procedures as they come across the pipe and
then performing ETL within the stored procs. That way I think we can have
real time data available for reporting.
Does anyone have an opinion on this scheme?
Thank youI've done this kind of configuration before on SQL 2K. Transactional
replication is about the best you can do since log shipping takes the DB
offline. We preferred to pause replication during the staging portion of
the ETL cycle to avoid blocking and dead locking. This also gave us a
consistent point in time snap shot of the source to work from. Using TSQL
as an ETL tool is fast but has limitations around data transformation and no
access to the bulk load api.
Good luck,
Danny
"Dodo Lurker" <none@.noemailplease> wrote in message
news:BIKdneofYum6-2reRVn-pQ@.comcast.com...
> Hi
> I am starting the task of creating a data warehouse for my company's OLTP
> system and we would like to offload reporting to a reporting schema at a
> data warehouse. We have 2 kinds of reports: operational (used daily and
> need data just about real time) and analytical (used to determine sales
> trends, etc).
> For the analytical reports, I think we can just do a nightly ETL load
> since
> the data doesn't need to be too recent. However, I was thinking of
> using
> replication to replicate the table data used in the operational reports,
> capturing the data in stored procedures as they come across the pipe and
> then performing ETL within the stored procs. That way I think we can have
> real time data available for reporting.
> Does anyone have an opinion on this scheme?
> Thank you
>|||Hello,
Timing is everything.
I would work on getting your data warehouse overnight processes
completed.
In a short period of time new replication options will be available in
SQL Server 2005.
Even the existing options may meet your needs, but with the right
timing you may
have better options available to you.|||Depending on what method you use to migrate data to the warehouse (and also
the volitility of the OLTP data), this may impact the performance of the
OLTP system. Also, once the user expects analysis in real time, any
interruption will be perceived as a system malfunction, and thus your
maintenance requirements and responsilbility have increased. Do they really
need real time analysis? Also, what type of analysis; OLAP?
"Dodo Lurker" <none@.noemailplease> wrote in message
news:BIKdneofYum6-2reRVn-pQ@.comcast.com...
> Hi
> I am starting the task of creating a data warehouse for my company's OLTP
> system and we would like to offload reporting to a reporting schema at a
> data warehouse. We have 2 kinds of reports: operational (used daily and
> need data just about real time) and analytical (used to determine sales
> trends, etc).
> For the analytical reports, I think we can just do a nightly ETL load
> since
> the data doesn't need to be too recent. However, I was thinking of
> using
> replication to replicate the table data used in the operational reports,
> capturing the data in stored procedures as they come across the pipe and
> then performing ETL within the stored procs. That way I think we can have
> real time data available for reporting.
> Does anyone have an opinion on this scheme?
> Thank you
>

Design opinion?

Hi
I am starting the task of creating a data warehouse for my company's OLTP
system and we would like to offload reporting to a reporting schema at a
data warehouse. We have 2 kinds of reports: operational (used daily and
need data just about real time) and analytical (used to determine sales
trends, etc).
For the analytical reports, I think we can just do a nightly ETL load since
the data doesn't need to be too recent. However, I was thinking of using
replication to replicate the table data used in the operational reports,
capturing the data in stored procedures as they come across the pipe and
then performing ETL within the stored procs. That way I think we can have
real time data available for reporting.
Does anyone have an opinion on this scheme?
Thank you
I've done this kind of configuration before on SQL 2K. Transactional
replication is about the best you can do since log shipping takes the DB
offline. We preferred to pause replication during the staging portion of
the ETL cycle to avoid blocking and dead locking. This also gave us a
consistent point in time snap shot of the source to work from. Using TSQL
as an ETL tool is fast but has limitations around data transformation and no
access to the bulk load api.
Good luck,
Danny
"Dodo Lurker" <none@.noemailplease> wrote in message
news:BIKdneofYum6-2reRVn-pQ@.comcast.com...
> Hi
> I am starting the task of creating a data warehouse for my company's OLTP
> system and we would like to offload reporting to a reporting schema at a
> data warehouse. We have 2 kinds of reports: operational (used daily and
> need data just about real time) and analytical (used to determine sales
> trends, etc).
> For the analytical reports, I think we can just do a nightly ETL load
> since
> the data doesn't need to be too recent. However, I was thinking of
> using
> replication to replicate the table data used in the operational reports,
> capturing the data in stored procedures as they come across the pipe and
> then performing ETL within the stored procs. That way I think we can have
> real time data available for reporting.
> Does anyone have an opinion on this scheme?
> Thank you
>
|||Hello,
Timing is everything.
I would work on getting your data warehouse overnight processes
completed.
In a short period of time new replication options will be available in
SQL Server 2005.
Even the existing options may meet your needs, but with the right
timing you may
have better options available to you.
|||Depending on what method you use to migrate data to the warehouse (and also
the volitility of the OLTP data), this may impact the performance of the
OLTP system. Also, once the user expects analysis in real time, any
interruption will be perceived as a system malfunction, and thus your
maintenance requirements and responsilbility have increased. Do they really
need real time analysis? Also, what type of analysis; OLAP?
"Dodo Lurker" <none@.noemailplease> wrote in message
news:BIKdneofYum6-2reRVn-pQ@.comcast.com...
> Hi
> I am starting the task of creating a data warehouse for my company's OLTP
> system and we would like to offload reporting to a reporting schema at a
> data warehouse. We have 2 kinds of reports: operational (used daily and
> need data just about real time) and analytical (used to determine sales
> trends, etc).
> For the analytical reports, I think we can just do a nightly ETL load
> since
> the data doesn't need to be too recent. However, I was thinking of
> using
> replication to replicate the table data used in the operational reports,
> capturing the data in stored procedures as they come across the pipe and
> then performing ETL within the stored procs. That way I think we can have
> real time data available for reporting.
> Does anyone have an opinion on this scheme?
> Thank you
>

design of the classes of data access layer

hi all

for designing the data access logic layer,if i have 3 table that relate to each other ,for example contact,address,sometime with creating the customer address should be created too.

should i design one class for each of them seperately and design 1 class(in bussiness layer or data access layer(i am not sure)) for calling the create stored procedures of 2 tables and put the 2 stored procedures in one transaction or not ,should I design 1 class for both tables and add the transaction in sp part?

because i need also the sp of creating each table seperately.

thanks

I wouldn't use a stored procedure for creating a table, if that is what you are suggesting. If you do not want the data to persist, use a temporay table, then drop it when it is no longer in use. As for the classes, the best and easiest option is to create a class (business layer) for each table, and a dataset class (data access layer)for each table. Then the class interacts with the dataset to validate data, insert update and delete data, and should contain constants for the procedure names, and separate methods which call stored procedures to do the inserting, deleting and updating of data, as well as loading the data. An instance of the data access class can be passed into these methods, or form a member variable of the business layer class. If you are updating several tables at a time, then use one transaction, ensuring that all the changes to all the related table will be rolled back on error.

However, there are many ways to approach this, whatever way works best for your data model and business model is best.

HTH

|||

thank you for your answere ,but maybe i have displayed something wrong ,my purpose of create table means inserting the data in table ,according to that I want to know that how do i design my classes that if update of one table relate to update another table ?

should i put the transaction in the store procedure in that case the seperate class for each table is not sutable?,otherwise i need the seperate clase for each table ,should i have third class for joining 2 class (in bussiness layer or data layer?)

thanks

|||You can interact with multiple classes from the front end inside one single transaction. ALternatively, you can enforce referential integrity across tables using relationships, or create update triggers to update other tables based on updates to one table.

Design Issue

I posted this also in the Visual Basic section:

I'm working with: VB6 and SqlServer 2000

I'm creating an application that requires users to answer several questions and based on answers to those questions, certain conditions result, such as the ability to view certain sections of the questionaire and/or more questions appear and/or some questions become irrelavent and/or mathematical formulas work one way or another based on an answer to a question.

Here is an example:
The user has to answer a question. For this particular question, if the user chooses Yes then I must "Activate" all of the questions that are flagged with the code X. If the user chooses No then I must "Activate" all of the questions that are flagged with Y and "Deactivate" all of the questions that are flagged with Z.

I'm coming across a design issue (both with the application itself and the database). What is the best way to store all of this in a database? Should I store the questions along with the list of possible answers and their corresponding conditions in the database as well as the actual data for each record (each questionaire)? Should I be referencing the database to populate all of my questions on my VB form or should I manually put labels and combo boxes containing the questions and possible answers on my form? Or should I be using another technology to store my questions and conditions OR my results, such as XML?

I looking for suggestions. I want to optimize my program as much as possible. I don't want to slow it down by making several calls to the database, but I want to make sure I'm also taking away optimization by hardcoding everything.

Thanks for your time and help!Should I store the questions along with the list of possible answers and their corresponding conditions in the database as well as the actual data for each record (each questionaire)? Should I be referencing the database to populate all of my questions on my VB form or should I manually put labels and combo boxes containing the questions and possible answers on my form?
I would always choose storing reference data in a database over hard-coding in the application.|||blindman,

thanks for responding. i had a feeling that would be the best option. i'm still a bit confused on how to logically put everything in the database.

MORE INFO:

Picture an application with an explorer style view. On the left in folders are different sections of a questionaire. On the right side will be the corresponding questions to that section. Each section, each question and it's corresponding possible answers, the controls that they will be held in (VB) will all need to be in the database. I'm still confused on how to setup tables relating each part...There is the data that will be stored to set up the screen, then there is data that will be stored for each record (each questionaire's answers, so to speak).

Thanks again!|||questionaire
section
question
answers
...but what do you mean , "the controls that they will be held in (VB) will all need to be in the database"? I hope you are just referring to the lookup lists...|||i think i was thinking too much. Seeing your code example has helped me clear up a lot. Thank you!...I was adding in a bunch of tables that didn't need to be there. The structure above makes a lot more sense, and will work with all of the lookup tables that I'll need. Again Thank you!...it always helps to have another set of eyes looking at the same issue.|||Again Thank you!...it always helps to have another set of eyes looking at the same issue. blind ones at that

Design idea Help for Database

we are creating a database of sales agents. Basically I have a table
of about 35,000 people, a second one with 8000 offices, and a third
table of around 400,000 transactions done by those 35,000 people in
those 8000 offices. We get new data everyday that just updates the
existing tables with the updated rosters and transactions.

I want to build a quick website where our recruiters can look up those
people and keep contact info and all that fun stuff but also pull
numbers on those people. Like

* How Many Transactions that Sales Agent did last year
* Rosters by office showing production

The goal is to click the users name and see all the percentages,
commissions and data like that which we will get by searching that
table of transactions by the agents ID.

the problems I see right away are stuff like
* If I were to pull a report showing all agents in a single office with
their number of transactions next to their name, that is a HUGE query.
It would have to search the 400k worth of records for each of the
agents on just that one report.

A suggestions I was given
I was told by a fellow programmer a better way to do this is to have an
additional table that houses stats info and have the SQL server run
automated reports everyday at say midnight where it updates that table.
This table could show stuff like
** number of transactions for each user
** avg sales price on all transactions for each user
** avg commission on transaction for each user

let me know your thoughts

thanks in advance
Monkey Girlbob1barker@.yahoo.com wrote:

> we are creating a database of sales agents. Basically I have a table
> of about 35,000 people, a second one with 8000 offices, and a third
> table of around 400,000 transactions done by those 35,000 people in
> those 8000 offices. We get new data everyday that just updates the
> existing tables with the updated rosters and transactions.
> I want to build a quick website where our recruiters can look up those
> people and keep contact info and all that fun stuff but also pull
> numbers on those people. Like
> * How Many Transactions that Sales Agent did last year
> * Rosters by office showing production
> The goal is to click the users name and see all the percentages,
> commissions and data like that which we will get by searching that
> table of transactions by the agents ID.
> the problems I see right away are stuff like
> * If I were to pull a report showing all agents in a single office with
> their number of transactions next to their name, that is a HUGE query.
> It would have to search the 400k worth of records for each of the
> agents on just that one report.
> A suggestions I was given
> I was told by a fellow programmer a better way to do this is to have an
> additional table that houses stats info and have the SQL server run
> automated reports everyday at say midnight where it updates that table.
> This table could show stuff like
> ** number of transactions for each user
> ** avg sales price on all transactions for each user
> ** avg commission on transaction for each user
> let me know your thoughts
> thanks in advance
> Monkey Girl

You didn't specify how quickly your data is growing but less than 1
million rows is a small database by most standards and probably isn't
going to be much trouble under any decent SQL Server implementation.

Pre-aggregating the data may be worthwhile but SQL Server has tools to
do that for you: Reporting Services or Analysis Services. It would be a
waste to create your own summary tables and then write the code just to
update them once a day.

> It would have to search the 400k worth of records for each of the
> agents on just that one report.

Not if you know what an index is... :-)

Hope this helps.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--

Monday, March 19, 2012

Details on Subscription.Status property

Detailed description on creating a dynamic excel file

Detailed description on creating a dynamic excel file

Detailed description on creating a dynamic excel file

Wednesday, March 7, 2012

Destination InputColumnCollection is empty

Saturday, February 25, 2012

Designing relational tables

Designing calendar databases

Sunday, February 19, 2012

Design Question

Design Question

Friday, February 17, 2012

Design opinion?

Design opinion?

design of the classes of data access layer

Design Issue

Design idea Help for Database

Determining Security

Blog Archive

About Me