Detailled error description in a script component (data flow)

Hi,

I'm pretty new in SSIS and i have some problems with error log. I want to get detailled error description in a script component of a dataflow. for the moment I use thooses lines

Row.ErrorDesc = ComponentMetaData.GetErrorDescription(Row.ErrorCode)

and for unique constraints on a sql table I have this error : The data value violates integrity constraints.

For the same error, if i use an event handler on error, i have more row and the first of them is more explicit (Variable System::ErrorDescription)

An OLE DB error has occurred. Error code: 0x80040E2F.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E2F Description: "The statement has been terminated.".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E2F Description: "Cannot insert duplicate key row in object 'dbo.dimDepot' with unique index 'IX_dimDepot'.".

Is that possible to have a so detailled error text in a script componnent of a data flow? If yes, How?
Or if i use error event how can authorize the dataflow go ahead even if there is error.

thanks for you help

krest

Having written on the inadequate specificity of OLEDB error messaging available to the data flow as compared to that available to the OnError handler from the underlying SQL Native client, the following route was chosen to circumvent the problem.

Use the ADO.NET destination available in the SSIS Integration Services book written by Kirk Haselden.

Two modifications to the provided ADO.NET destination component in the book were made, to do exactly what you're looking for: inject detail error descriptions/codes as columns in the error output, to allow for their use when error row redirection is desired. They are as follows:

1. With the ADO.NET destination component, add two columns to the error output, NativeErrorMessage (type DT_WSTR ) and NativeErrorCode (type DT_I4).

2. Modify the error handler to populate the NativeErrorMessage and the NativeErrorCode from the the SqlException object.

I would post the code, but I'm not certain if that's allowed by the author or not.

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.

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.

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.

Design Table; Columns/Description get with SP??

Hi,

I'm using stored procedures to create an online data dictionary for
all of our dbs and I thought it would be very cool if there was a way
that I could somehow pull the columns description entered in the table
design view (lower pane) of EM. I have poked around, but have not
found a way. It is my suspcion that this may be impossible. Does any
smart person out there know?

Also want to pull pks and fks and other constraints, which I think I
can figure out how to do, but if anyone has any pointers, that would
also be appreciated.

Many Thanks,

Georgiavesta (vesta0424@.gmail.com) writes:

Quote:

Originally Posted by

It looks to me like you create your extended properties with this
function. Are there any default functions to get the information that
has been manually entered in the description field?

No, functions don't create anything, they can only retrieve data. To
add an extended property from T-SQL, you use sp_addextendedproperty.

I cannot really give any examples of using either, because I have not
used extended properties myself very much. Or rather not at all.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Design Solution Required

We are facing design issues, Could you please advice us how to proceed?

Problem description: Web App will pass a complex dynamic SQL query to
backend and it should return result set as fast as it can
Issue 1: SQL query will have lot of JOINS and WHERE clause
Issue 2: Each Table contain millions of records

Requirement: Turn around time of the SQL query should be as far as
possible minimum.

Could you please advice us which technology we should use, such that
users get the resultset in few seconds.

We are Microsoft Partner. We use only Microsoft technology for our
product development.

Your Help is much appreciated

With Regards
S a t h y a RCould you please advice us which technology we should use, such that

Quote:

Originally Posted by

users get the resultset in few seconds.

Pay particular attention to index and query tuning. Make sure you have
indexes that the optimizer can use to generate the most efficient plan.
Prioritize tuning so that the most often executed and expensive queries are
addressed first. Also consider indexed views, which are especially
appropriate for aggregated data. Keep in mind that too many indexes can
hurt performance if you do a lot of inserts/updates so you'll need to
perform cost-benefit analysis.

I suggest you get a good book that covers query and index tuning in depth.
I recommend Inside Microsoft SQL Server 2005: T-SQL Querying, ISBN
9780735623132.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Sathya" <sathyamca@.gmail.comwrote in message
news:1164801200.132687.7070@.j72g2000cwa.googlegrou ps.com...

Quote:

Originally Posted by

We are facing design issues, Could you please advice us how to proceed?
>
Problem description: Web App will pass a complex dynamic SQL query to
backend and it should return result set as fast as it can
Issue 1: SQL query will have lot of JOINS and WHERE clause
Issue 2: Each Table contain millions of records
>
Requirement: Turn around time of the SQL query should be as far as
possible minimum.
>
>
Could you please advice us which technology we should use, such that
users get the resultset in few seconds.
>
We are Microsoft Partner. We use only Microsoft technology for our
product development.
>
>
Your Help is much appreciated
>
With Regards
S a t h y a R
>

|||Sathya wrote:

Quote:

Originally Posted by

We are facing design issues, Could you please advice us how to proceed?
>
Problem description: Web App will pass a complex dynamic SQL query to
backend and it should return result set as fast as it can
Issue 1: SQL query will have lot of JOINS and WHERE clause
Issue 2: Each Table contain millions of records

Quote:

Originally Posted by

Could you please advice us which technology we should use, such that
users get the resultset in few seconds.
>

Use sp_executesql to execute your dynamic SQL (not EXEC). Even better,
try to use a prepared statement. In your queries, make sure to use the
indexes, avoid calling functions and do not sort in SQL unless it is
absolutely necessary (sort on client side instead).

You could also save typical queries and run them through the Database
Tuning Advisor, which will suggest how to index your tables. This
wizard is available with SQL Server 2005 in the Management Studio, but
it can help to tune SQL Server 2000 databases as well.

If you can afford it, use SQL 2005 Enterprise Edition, which will allow
you to partition your tables. Partitions can greatly improve speed.
Again save a typical query and run it through the Database Tuning
Advisor, which can suggest how to create optimal partitions.

This wizard is just awesome, but of course if your queries are
completely random and different it won't be of much help since it need
a specific workload to make suggestions.

Regard,
lucm