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 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

Design Question- Dynamic Configuration of Metadata in Dataflow

I have a design question that I'd like some input on. I am trying to archive data from an extremely large production database. The tables to be archived changes quite often. It is currently along the lines of 80-100 tables with the possibility (likelihood) to grow from there.

If at all possible, I'd like to avoid writing an individual dataflow transformation for each table. I know that SSIS does not offer the same capabilities to change the metadata at runtime as DTS. I am currently exploring the option of programmatically creating/modifying the packages through the .Net framework. (using this link as a guide: http://msdn2.microsoft.com/en-us/library/ms345167.aspx).

I have concerns about the performance of this approach and was wondering if anyone had any feedback, or has implemented something similar, or has any other ideas on a different way to accomplish the same thing.

Thanks so much for your help,

Jessica

JessicaElise wrote:

I have a design question that I'd like some input on. I am trying to archive data from an extremely large production database. The tables to be archived changes quite often. It is currently along the lines of 80-100 tables with the possibility (likelihood) to grow from there.

If at all possible, I'd like to avoid writing an individual dataflow transformation for each table. I know that SSIS does not offer the same capabilities to change the metadata at runtime as DTS. I am currently exploring the option of programmatically creating/modifying the packages through the .Net framework. (using this link as a guide: http://msdn2.microsoft.com/en-us/library/ms345167.aspx).

I have concerns about the performance of this approach and was wondering if anyone had any feedback, or has implemented something similar, or has any other ideas on a different way to accomplish the same thing.

Thanks so much for your help,

Jessica

Assuming that the package you build programatically is the same as the package that you build in the SSIS Designer then performance will be exactly the same. Generating the package using the API shouldn't take too long - depends how good a coder you are

To be honest alot of people have mentioned they are attempting this but nobody has reported that they have achieved it. But then again why would they? People come on here to report problems, not successes.

Does that help at all? Probbaly not!!

-Jamie

|||Programmatically creating packages for each table is the way to go unfortunately. Have a look at this link: http://www.ivolva.com/ssis_code_generator.html I haven't used it myself, but if it does what it says, it should be a big help in writing the code to dynamically generate packages.|||

Well I'm reporting a success, Jamie. I got it working in a very base, proof-of-concept form. I've only tested it for very small datasets though. We'll see what happens when I increase the load (and run it multi-threaded). But, in case this can be of any use to anyone else:

I created a custom component that will create the a dynamic package based on an inputted source table, destination table and package name (source and destination connection strings are currently hardcoded, but I'm going to change that as I develop the component). I've been successful in using this component to create the package and then using the "Execute Package Task" to execute the package that was just created. Chuck that into a ForEach Loop that goes through a recordset containing a DestinationTable, SourceTable, and PackageName and modifies the expressions of the component and it works.

Here's the meat-and-potatoes of the Custom Component I created.

Code Snippet

using System;

using System.Collections.Generic;

using System.Text;

using Microsoft.SqlServer.Dts.Runtime;

using System.IO;

using System.Data;

using Microsoft.SqlServer.Dts.Pipeline;

using Microsoft.SqlServer.Dts.Pipeline.Wrapper ;

namespace DynamicDataTransfer

{

[

DtsTask(

DisplayName = "Dynamic Data Transfer Task",

Description = "Dynamic Data Transfer.",

UITypeName = "DynamicDataTransfer.DynamicDataTransferTaskUI, DynamicDataTransfer, Version=1.0.0.0, Culture=Neutral, PublicKeyToken=xxxxxxxxxxxxxxxx"

)

]

public class DynamicDataTransfer : Task

{

private string SourceTableNameInternal = "";

public string SourceTableName

{

get

{

return this.SourceTableNameInternal;

}

set

{

this.SourceTableNameInternal = value;

}

}

private string PackageNameInternal = "";

public string PackageName

{

get

{

return this.PackageNameInternal;

}

set

{

this.PackageNameInternal = value;

}

}

private string DestinationTableNameInternal = "";

public string DestinationTableName

{

get

{

return this.DestinationTableNameInternal;

}

set

{

this.DestinationTableNameInternal = value;

}

}

/// <summary>

/// Executes the task

/// </summary>

public override DTSExecResult Execute(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log, object transaction)

{

try

{

Microsoft.SqlServer.Dts.Runtime.Application a = new Microsoft.SqlServer.Dts.Runtime.Application();

String SSISPackageFilePath;

SSISPackageFilePath = PackageNameInternal;

if (File.Exists(SSISPackageFilePath))

File.Delete(SSISPackageFilePath);

Package pkg = new Package();

MainPipe dataFlow;

ConnectionManager conMgrSource = pkg.Connections.Add("OLEDB");

ConnectionManager conMgrDestination = pkg.Connections.Add("OLEDB");

conMgrSource.Name = "OLEDBConnectionSource";

conMgrSource.ConnectionString = "Data Source=SOURCESERVER;Initial Catalog=SOURCEDB;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;";

conMgrDestination.Name = "OLEDBConnectionDestination";

conMgrDestination.ConnectionString = "Data Source=DESTINATIONSERVER;Initial Catalog=DESTINATIONDB;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;";

Executable exe = pkg.Executables.Add("DTS.Pipeline.1");

TaskHost th = exe as TaskHost;

th.Name = "DynamicDataFlowTask";

dataFlow = th.InnerObject as MainPipe;

IDTSComponentMetaDataCollection90 metadataCollection = dataFlow.ComponentMetaDataCollection;

IDTSComponentMetaData90 OLEDBSource = dataFlow.ComponentMetaDataCollection.New();

OLEDBSource.Name = "OLEDBSource";

OLEDBSource.ComponentClassID = "DTSAdapter.OLEDBSource.1";

IDTSComponentMetaData90 OLEDBDestination = dataFlow.ComponentMetaDataCollection.New();

OLEDBDestination.Name = "OLEDBDestination";

OLEDBDestination.ComponentClassID = "DTSAdapter.OLEDBDestination.1";

// Get the design time instance of the component.

CManagedComponentWrapper InstanceSource = OLEDBSource.Instantiate();

// Initialize the component

InstanceSource.ProvideComponentProperties();

// Specify the connection manager.

if (OLEDBSource.RuntimeConnectionCollection.Count > 0)

{

OLEDBSource.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(pkg.Connections["OLEDBConnectionSource"]);

OLEDBSource.RuntimeConnectionCollection[0].ConnectionManagerID = pkg.Connections["OLEDBConnectionSource"].ID;

}

InstanceSource.SetComponentProperty("OpenRowset", SourceTableNameInternal);

InstanceSource.SetComponentProperty("AccessMode", 0);

//reinitialize the component

InstanceSource.AcquireConnections(null);

InstanceSource.ReinitializeMetaData();

InstanceSource.ReleaseConnections();

// Get the design time instance of the component.

CManagedComponentWrapper InstanceDestination = OLEDBDestination.Instantiate();

// Initialize the component

InstanceDestination.ProvideComponentProperties();

// Specify the connection manager.

if (OLEDBDestination.RuntimeConnectionCollection.Count > 0)

{

OLEDBDestination.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(pkg.Connections["OLEDBConnectionDestination"]);

OLEDBDestination.RuntimeConnectionCollection[0].ConnectionManagerID = pkg.Connections["OLEDBConnectionDestination"].ID;

}

InstanceDestination.SetComponentProperty("OpenRowset", DestinationTableNameInternal);

InstanceDestination.SetComponentProperty("AccessMode", 0);

//reinitialize the component

InstanceDestination.AcquireConnections(null);

InstanceDestination.ReinitializeMetaData();

InstanceDestination.ReleaseConnections();

//map the columns

IDTSPath90 path = dataFlow.PathCollection.New();

path.AttachPathAndPropagateNotifications(OLEDBSource.OutputCollection[0], OLEDBDestination.InputCollection[0]);

IDTSInput90 input = OLEDBDestination.InputCollection[0];

IDTSVirtualInput90 vInput = input.GetVirtualInput();

foreach (IDTSVirtualInputColumn90 vColumn in vInput.VirtualInputColumnCollection)

{

IDTSInputColumn90 vCol = InstanceDestination.SetUsageType(input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READWRITE);

InstanceDestination.MapInputColumn(input.ID, vCol.ID, input.ExternalMetadataColumnCollection[vColumn.Name].ID);

}

a.SaveToXml(SSISPackageFilePath, pkg, null);

th = null;

pkg = null;

a = null;

OLEDBSource = null;

OLEDBDestination = null;

InstanceSource = null;

InstanceDestination = null;

return DTSExecResult.Success;

}

catch (Exception exc)

{

componentEvents.FireError(0, "DynamicDataTransfer.Execute", "Task Errored: " + exc.ToString(), "", -1);

return DTSExecResult.Failure;

}

}

}

}

(Just as a side-note, your blog has helped me a ton since I've started with SSIS. Thanks for posting all of your findings.)

Jess

|||

Hey Jess,

I'm impressed. This is the first time I've seen a working bit of code that does this. Do you mind if I put a link from my blog to this? I think this needs to be shared.

One thing, the correct nomenclature is a custom task, not a custom component. My god, I must be in a critical mood

Regards

-Jamie

|||Custom Task, got it. Sure thing, link to your blog, I'll be proud. |||

JessicaElise wrote:

Custom Task, got it. Sure thing, link to your blog, I'll be proud.

Cool. Done!

Building Packages Programatically

http://blogs.conchango.com/jamiethomson/archive/2007/03/28/SSIS_3A00_-Building-Packages-Programatically.aspx

Thanks again Jess.

-Jamie

|||

Hi Jamie,

Since the Script task only support VB.NET, I'd like to know how the above code can be deployed in our SSIS Package

Are there any conversion tool available?

|||

Subhash512525 wrote:

Hi Jamie,

Since the Script task only support VB.NET, I'd like to know how the above code can be deployed in our SSIS Package

Are there any conversion tool available?

Yes, I'm sure there is. Not being a .Net expert myself I don't know where to find them but I'm sure they exist.

-Jamie

|||

Thanks for your reply Jamie,

I was successful implementing the above code in VB.NET code with slight modifications and additions required for my package using online code converter available here . I must thank you both a lot for the code.

Subhash Subramanyam

|||

Jess,

I was directed to your post by John Welch. I have a need very similar to yours, however, I am not a developer and I am having difficulties implementing your code snippet. I have VS Express for C# loaded and when I create a class library project I get the following error:

The type or namespace name 'Dts' does not exist in the namespace 'Microsoft.SqlServer' (are you missing an assembly reference?)

Can someone point me in the right direction? What other code elements are needed to make this "meat and potatoes" portion work?

Thanks in advance,

Keith

|||

Hi ,

I am also facing this problem, but I didn't understand where I have to write this code. If it is in script task then where is the main() .Can you please tell me or reply on this email address.(ramanandap@.gmail.com).

Thanks in advance

|||

KC wrote:

Jess,

I was directed to your post by John Welch. I have a need very similar to yours, however, I am not a developer and I am having difficulties implementing your code snippet. I have VS Express for C# loaded and when I create a class library project I get the following error:

The type or namespace name 'Dts' does not exist in the namespace 'Microsoft.SqlServer' (are you missing an assembly reference?)

Can someone point me in the right direction? What other code elements are needed to make this "meat and potatoes" portion work?

Thanks in advance,

Keith

You need to add a reference to the Microsoft.SQLServer.DTSRuntimeWrap assembly to your project. Use Project..Add Reference.

|||

Mahi12345 wrote:

Hi ,

I am also facing this problem, but I didn't understand where I have to write this code. If it is in script task then where is the main() .Can you please tell me or reply on this email address.(ramanandap@.gmail.com).

Thanks in advance

This was created as a custom task in VB.NET. That gets compiled to a .NET assembly (DLL), registered in the GAC, and then can be used from your SSIS packages. If you look under "Developing Custom Tasks" in Books Online, you can see more information about this.

Design Question- Dynamic Configuration of Metadata in Dataflow

I have a design question that I'd like some input on. I am trying to archive data from an extremely large production database. The tables to be archived changes quite often. It is currently along the lines of 80-100 tables with the possibility (likelihood) to grow from there.

If at all possible, I'd like to avoid writing an individual dataflow transformation for each table. I know that SSIS does not offer the same capabilities to change the metadata at runtime as DTS. I am currently exploring the option of programmatically creating/modifying the packages through the .Net framework. (using this link as a guide: http://msdn2.microsoft.com/en-us/library/ms345167.aspx).

I have concerns about the performance of this approach and was wondering if anyone had any feedback, or has implemented something similar, or has any other ideas on a different way to accomplish the same thing.

Thanks so much for your help,

Jessica

JessicaElise wrote:

I have a design question that I'd like some input on. I am trying to archive data from an extremely large production database. The tables to be archived changes quite often. It is currently along the lines of 80-100 tables with the possibility (likelihood) to grow from there.

If at all possible, I'd like to avoid writing an individual dataflow transformation for each table. I know that SSIS does not offer the same capabilities to change the metadata at runtime as DTS. I am currently exploring the option of programmatically creating/modifying the packages through the .Net framework. (using this link as a guide: http://msdn2.microsoft.com/en-us/library/ms345167.aspx).

I have concerns about the performance of this approach and was wondering if anyone had any feedback, or has implemented something similar, or has any other ideas on a different way to accomplish the same thing.

Thanks so much for your help,

Jessica

Assuming that the package you build programatically is the same as the package that you build in the SSIS Designer then performance will be exactly the same. Generating the package using the API shouldn't take too long - depends how good a coder you are

To be honest alot of people have mentioned they are attempting this but nobody has reported that they have achieved it. But then again why would they? People come on here to report problems, not successes.

Does that help at all? Probbaly not!!

-Jamie

|||Programmatically creating packages for each table is the way to go unfortunately. Have a look at this link: http://www.ivolva.com/ssis_code_generator.html I haven't used it myself, but if it does what it says, it should be a big help in writing the code to dynamically generate packages.
|||

Well I'm reporting a success, Jamie. I got it working in a very base, proof-of-concept form. I've only tested it for very small datasets though. We'll see what happens when I increase the load (and run it multi-threaded). But, in case this can be of any use to anyone else:

I created a custom component that will create the a dynamic package based on an inputted source table, destination table and package name (source and destination connection strings are currently hardcoded, but I'm going to change that as I develop the component). I've been successful in using this component to create the package and then using the "Execute Package Task" to execute the package that was just created. Chuck that into a ForEach Loop that goes through a recordset containing a DestinationTable, SourceTable, and PackageName and modifies the expressions of the component and it works.

Here's the meat-and-potatoes of the Custom Component I created.

Code Snippet

using System;

using System.Collections.Generic;

using System.Text;

using Microsoft.SqlServer.Dts.Runtime;

using System.IO;

using System.Data;

using Microsoft.SqlServer.Dts.Pipeline;

using Microsoft.SqlServer.Dts.Pipeline.Wrapper ;

namespace DynamicDataTransfer

{

[

DtsTask(

DisplayName = "Dynamic Data Transfer Task",

Description = "Dynamic Data Transfer.",

UITypeName = "DynamicDataTransfer.DynamicDataTransferTaskUI, DynamicDataTransfer, Version=1.0.0.0, Culture=Neutral, PublicKeyToken=xxxxxxxxxxxxxxxx"

)

]

public class DynamicDataTransfer : Task

{

private string SourceTableNameInternal = "";

public string SourceTableName

{

get

{

return this.SourceTableNameInternal;

}

set

{

this.SourceTableNameInternal = value;

}

}

private string PackageNameInternal = "";

public string PackageName

{

get

{

return this.PackageNameInternal;

}

set

{

this.PackageNameInternal = value;

}

}

private string DestinationTableNameInternal = "";

public string DestinationTableName

{

get

{

return this.DestinationTableNameInternal;

}

set

{

this.DestinationTableNameInternal = value;

}

}

/// <summary>

/// Executes the task

/// </summary>

public override DTSExecResult Execute(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log, object transaction)

{

try

{

Microsoft.SqlServer.Dts.Runtime.Application a = new Microsoft.SqlServer.Dts.Runtime.Application();

String SSISPackageFilePath;

SSISPackageFilePath = PackageNameInternal;

if (File.Exists(SSISPackageFilePath))

File.Delete(SSISPackageFilePath);

Package pkg = new Package();

MainPipe dataFlow;

ConnectionManager conMgrSource = pkg.Connections.Add("OLEDB");

ConnectionManager conMgrDestination = pkg.Connections.Add("OLEDB");

conMgrSource.Name = "OLEDBConnectionSource";

conMgrSource.ConnectionString = "Data Source=SOURCESERVER;Initial Catalog=SOURCEDB;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;";

conMgrDestination.Name = "OLEDBConnectionDestination";

conMgrDestination.ConnectionString = "Data Source=DESTINATIONSERVER;Initial Catalog=DESTINATIONDB;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;";

Executable exe = pkg.Executables.Add("DTS.Pipeline.1");

TaskHost th = exe as TaskHost;

th.Name = "DynamicDataFlowTask";

dataFlow = th.InnerObject as MainPipe;

IDTSComponentMetaDataCollection90 metadataCollection = dataFlow.ComponentMetaDataCollection;

IDTSComponentMetaData90 OLEDBSource = dataFlow.ComponentMetaDataCollection.New();

OLEDBSource.Name = "OLEDBSource";

OLEDBSource.ComponentClassID = "DTSAdapter.OLEDBSource.1";

IDTSComponentMetaData90 OLEDBDestination = dataFlow.ComponentMetaDataCollection.New();

OLEDBDestination.Name = "OLEDBDestination";

OLEDBDestination.ComponentClassID = "DTSAdapter.OLEDBDestination.1";

// Get the design time instance of the component.

CManagedComponentWrapper InstanceSource = OLEDBSource.Instantiate();

// Initialize the component

InstanceSource.ProvideComponentProperties();

// Specify the connection manager.

if (OLEDBSource.RuntimeConnectionCollection.Count > 0)

{

OLEDBSource.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(pkg.Connections["OLEDBConnectionSource"]);

OLEDBSource.RuntimeConnectionCollection[0].ConnectionManagerID = pkg.Connections["OLEDBConnectionSource"].ID;

}

InstanceSource.SetComponentProperty("OpenRowset", SourceTableNameInternal);

InstanceSource.SetComponentProperty("AccessMode", 0);

//reinitialize the component

InstanceSource.AcquireConnections(null);

InstanceSource.ReinitializeMetaData();

InstanceSource.ReleaseConnections();

// Get the design time instance of the component.

CManagedComponentWrapper InstanceDestination = OLEDBDestination.Instantiate();

// Initialize the component

InstanceDestination.ProvideComponentProperties();

// Specify the connection manager.

if (OLEDBDestination.RuntimeConnectionCollection.Count > 0)

{

OLEDBDestination.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(pkg.Connections["OLEDBConnectionDestination"]);

OLEDBDestination.RuntimeConnectionCollection[0].ConnectionManagerID = pkg.Connections["OLEDBConnectionDestination"].ID;

}

InstanceDestination.SetComponentProperty("OpenRowset", DestinationTableNameInternal);

InstanceDestination.SetComponentProperty("AccessMode", 0);

//reinitialize the component

InstanceDestination.AcquireConnections(null);

InstanceDestination.ReinitializeMetaData();

InstanceDestination.ReleaseConnections();

//map the columns

IDTSPath90 path = dataFlow.PathCollection.New();

path.AttachPathAndPropagateNotifications(OLEDBSource.OutputCollection[0], OLEDBDestination.InputCollection[0]);

IDTSInput90 input = OLEDBDestination.InputCollection[0];

IDTSVirtualInput90 vInput = input.GetVirtualInput();

foreach (IDTSVirtualInputColumn90 vColumn in vInput.VirtualInputColumnCollection)

{

IDTSInputColumn90 vCol = InstanceDestination.SetUsageType(input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READWRITE);

InstanceDestination.MapInputColumn(input.ID, vCol.ID, input.ExternalMetadataColumnCollection[vColumn.Name].ID);

}

a.SaveToXml(SSISPackageFilePath, pkg, null);

th = null;

pkg = null;

a = null;

OLEDBSource = null;

OLEDBDestination = null;

InstanceSource = null;

InstanceDestination = null;

return DTSExecResult.Success;

}

catch (Exception exc)

{

componentEvents.FireError(0, "DynamicDataTransfer.Execute", "Task Errored: " + exc.ToString(), "", -1);

return DTSExecResult.Failure;

}

}

}

}

(Just as a side-note, your blog has helped me a ton since I've started with SSIS. Thanks for posting all of your findings.)

Jess

|||

Hey Jess,

I'm impressed. This is the first time I've seen a working bit of code that does this. Do you mind if I put a link from my blog to this? I think this needs to be shared.

One thing, the correct nomenclature is a custom task, not a custom component. My god, I must be in a critical mood

Regards

-Jamie

|||Custom Task, got it. Sure thing, link to your blog, I'll be proud. |||

JessicaElise wrote:

Custom Task, got it. Sure thing, link to your blog, I'll be proud.

Cool. Done!

Building Packages Programatically

http://blogs.conchango.com/jamiethomson/archive/2007/03/28/SSIS_3A00_-Building-Packages-Programatically.aspx

Thanks again Jess.

-Jamie

|||

Hi Jamie,

Since the Script task only support VB.NET, I'd like to know how the above code can be deployed in our SSIS Package

Are there any conversion tool available?

|||

Subhash512525 wrote:

Hi Jamie,

Since the Script task only support VB.NET, I'd like to know how the above code can be deployed in our SSIS Package

Are there any conversion tool available?

Yes, I'm sure there is. Not being a .Net expert myself I don't know where to find them but I'm sure they exist.

-Jamie

|||

Thanks for your reply Jamie,

I was successful implementing the above code in VB.NET code with slight modifications and additions required for my package using online code converter available here . I must thank you both a lot for the code.

Subhash Subramanyam

|||

Jess,

I was directed to your post by John Welch. I have a need very similar to yours, however, I am not a developer and I am having difficulties implementing your code snippet. I have VS Express for C# loaded and when I create a class library project I get the following error:

The type or namespace name 'Dts' does not exist in the namespace 'Microsoft.SqlServer' (are you missing an assembly reference?)

Can someone point me in the right direction? What other code elements are needed to make this "meat and potatoes" portion work?

Thanks in advance,

Keith

|||

Hi ,

I am also facing this problem, but I didn't understand where I have to write this code. If it is in script task then where is the main() .Can you please tell me or reply on this email address.(ramanandap@.gmail.com).

Thanks in advance

|||

KC wrote:

Jess,

I was directed to your post by John Welch. I have a need very similar to yours, however, I am not a developer and I am having difficulties implementing your code snippet. I have VS Express for C# loaded and when I create a class library project I get the following error:

The type or namespace name 'Dts' does not exist in the namespace 'Microsoft.SqlServer' (are you missing an assembly reference?)

Can someone point me in the right direction? What other code elements are needed to make this "meat and potatoes" portion work?

Thanks in advance,

Keith

You need to add a reference to the Microsoft.SQLServer.DTSRuntimeWrap assembly to your project. Use Project..Add Reference.

|||

Mahi12345 wrote:

Hi ,

I am also facing this problem, but I didn't understand where I have to write this code. If it is in script task then where is the main() .Can you please tell me or reply on this email address.(ramanandap@.gmail.com).

Thanks in advance

This was created as a custom task in VB.NET. That gets compiled to a .NET assembly (DLL), registered in the GAC, and then can be used from your SSIS packages. If you look under "Developing Custom Tasks" in Books Online, you can see more information about this.

Design Question- Dynamic Configuration of Metadata in Dataflow

I have a design question that I'd like some input on. I am trying to archive data from an extremely large production database. The tables to be archived changes quite often. It is currently along the lines of 80-100 tables with the possibility (likelihood) to grow from there.

If at all possible, I'd like to avoid writing an individual dataflow transformation for each table. I know that SSIS does not offer the same capabilities to change the metadata at runtime as DTS. I am currently exploring the option of programmatically creating/modifying the packages through the .Net framework. (using this link as a guide: http://msdn2.microsoft.com/en-us/library/ms345167.aspx).

I have concerns about the performance of this approach and was wondering if anyone had any feedback, or has implemented something similar, or has any other ideas on a different way to accomplish the same thing.

Thanks so much for your help,

Jessica

JessicaElise wrote:

I have a design question that I'd like some input on. I am trying to archive data from an extremely large production database. The tables to be archived changes quite often. It is currently along the lines of 80-100 tables with the possibility (likelihood) to grow from there.

If at all possible, I'd like to avoid writing an individual dataflow transformation for each table. I know that SSIS does not offer the same capabilities to change the metadata at runtime as DTS. I am currently exploring the option of programmatically creating/modifying the packages through the .Net framework. (using this link as a guide: http://msdn2.microsoft.com/en-us/library/ms345167.aspx).

I have concerns about the performance of this approach and was wondering if anyone had any feedback, or has implemented something similar, or has any other ideas on a different way to accomplish the same thing.

Thanks so much for your help,

Jessica

Assuming that the package you build programatically is the same as the package that you build in the SSIS Designer then performance will be exactly the same. Generating the package using the API shouldn't take too long - depends how good a coder you are

To be honest alot of people have mentioned they are attempting this but nobody has reported that they have achieved it. But then again why would they? People come on here to report problems, not successes.

Does that help at all? Probbaly not!!

-Jamie

|||Programmatically creating packages for each table is the way to go unfortunately. Have a look at this link: http://www.ivolva.com/ssis_code_generator.html I haven't used it myself, but if it does what it says, it should be a big help in writing the code to dynamically generate packages.
|||

Well I'm reporting a success, Jamie. I got it working in a very base, proof-of-concept form. I've only tested it for very small datasets though. We'll see what happens when I increase the load (and run it multi-threaded). But, in case this can be of any use to anyone else:

I created a custom component that will create the a dynamic package based on an inputted source table, destination table and package name (source and destination connection strings are currently hardcoded, but I'm going to change that as I develop the component). I've been successful in using this component to create the package and then using the "Execute Package Task" to execute the package that was just created. Chuck that into a ForEach Loop that goes through a recordset containing a DestinationTable, SourceTable, and PackageName and modifies the expressions of the component and it works.

Here's the meat-and-potatoes of the Custom Component I created.

Code Snippet

using System;

using System.Collections.Generic;

using System.Text;

using Microsoft.SqlServer.Dts.Runtime;

using System.IO;

using System.Data;

using Microsoft.SqlServer.Dts.Pipeline;

using Microsoft.SqlServer.Dts.Pipeline.Wrapper ;

namespace DynamicDataTransfer

{

[

DtsTask(

DisplayName = "Dynamic Data Transfer Task",

Description = "Dynamic Data Transfer.",

UITypeName = "DynamicDataTransfer.DynamicDataTransferTaskUI, DynamicDataTransfer, Version=1.0.0.0, Culture=Neutral, PublicKeyToken=xxxxxxxxxxxxxxxx"

)

]

public class DynamicDataTransfer : Task

{

private string SourceTableNameInternal = "";

public string SourceTableName

{

get

{

return this.SourceTableNameInternal;

}

set

{

this.SourceTableNameInternal = value;

}

}

private string PackageNameInternal = "";

public string PackageName

{

get

{

return this.PackageNameInternal;

}

set

{

this.PackageNameInternal = value;

}

}

private string DestinationTableNameInternal = "";

public string DestinationTableName

{

get

{

return this.DestinationTableNameInternal;

}

set

{

this.DestinationTableNameInternal = value;

}

}

/// <summary>

/// Executes the task

/// </summary>

public override DTSExecResult Execute(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log, object transaction)

{

try

{

Microsoft.SqlServer.Dts.Runtime.Application a = new Microsoft.SqlServer.Dts.Runtime.Application();

String SSISPackageFilePath;

SSISPackageFilePath = PackageNameInternal;

if (File.Exists(SSISPackageFilePath))

File.Delete(SSISPackageFilePath);

Package pkg = new Package();

MainPipe dataFlow;

ConnectionManager conMgrSource = pkg.Connections.Add("OLEDB");

ConnectionManager conMgrDestination = pkg.Connections.Add("OLEDB");

conMgrSource.Name = "OLEDBConnectionSource";

conMgrSource.ConnectionString = "Data Source=SOURCESERVER;Initial Catalog=SOURCEDB;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;";

conMgrDestination.Name = "OLEDBConnectionDestination";

conMgrDestination.ConnectionString = "Data Source=DESTINATIONSERVER;Initial Catalog=DESTINATIONDB;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;";

Executable exe = pkg.Executables.Add("DTS.Pipeline.1");

TaskHost th = exe as TaskHost;

th.Name = "DynamicDataFlowTask";

dataFlow = th.InnerObject as MainPipe;

IDTSComponentMetaDataCollection90 metadataCollection = dataFlow.ComponentMetaDataCollection;

IDTSComponentMetaData90 OLEDBSource = dataFlow.ComponentMetaDataCollection.New();

OLEDBSource.Name = "OLEDBSource";

OLEDBSource.ComponentClassID = "DTSAdapter.OLEDBSource.1";

IDTSComponentMetaData90 OLEDBDestination = dataFlow.ComponentMetaDataCollection.New();

OLEDBDestination.Name = "OLEDBDestination";

OLEDBDestination.ComponentClassID = "DTSAdapter.OLEDBDestination.1";

// Get the design time instance of the component.

CManagedComponentWrapper InstanceSource = OLEDBSource.Instantiate();

// Initialize the component

InstanceSource.ProvideComponentProperties();

// Specify the connection manager.

if (OLEDBSource.RuntimeConnectionCollection.Count > 0)

{

OLEDBSource.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(pkg.Connections["OLEDBConnectionSource"]);

OLEDBSource.RuntimeConnectionCollection[0].ConnectionManagerID = pkg.Connections["OLEDBConnectionSource"].ID;

}

InstanceSource.SetComponentProperty("OpenRowset", SourceTableNameInternal);

InstanceSource.SetComponentProperty("AccessMode", 0);

//reinitialize the component

InstanceSource.AcquireConnections(null);

InstanceSource.ReinitializeMetaData();

InstanceSource.ReleaseConnections();

// Get the design time instance of the component.

CManagedComponentWrapper InstanceDestination = OLEDBDestination.Instantiate();

// Initialize the component

InstanceDestination.ProvideComponentProperties();

// Specify the connection manager.

if (OLEDBDestination.RuntimeConnectionCollection.Count > 0)

{

OLEDBDestination.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(pkg.Connections["OLEDBConnectionDestination"]);

OLEDBDestination.RuntimeConnectionCollection[0].ConnectionManagerID = pkg.Connections["OLEDBConnectionDestination"].ID;

}

InstanceDestination.SetComponentProperty("OpenRowset", DestinationTableNameInternal);

InstanceDestination.SetComponentProperty("AccessMode", 0);

//reinitialize the component

InstanceDestination.AcquireConnections(null);

InstanceDestination.ReinitializeMetaData();

InstanceDestination.ReleaseConnections();

//map the columns

IDTSPath90 path = dataFlow.PathCollection.New();

path.AttachPathAndPropagateNotifications(OLEDBSource.OutputCollection[0], OLEDBDestination.InputCollection[0]);

IDTSInput90 input = OLEDBDestination.InputCollection[0];

IDTSVirtualInput90 vInput = input.GetVirtualInput();

foreach (IDTSVirtualInputColumn90 vColumn in vInput.VirtualInputColumnCollection)

{

IDTSInputColumn90 vCol = InstanceDestination.SetUsageType(input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READWRITE);

InstanceDestination.MapInputColumn(input.ID, vCol.ID, input.ExternalMetadataColumnCollection[vColumn.Name].ID);

}

a.SaveToXml(SSISPackageFilePath, pkg, null);

th = null;

pkg = null;

a = null;

OLEDBSource = null;

OLEDBDestination = null;

InstanceSource = null;

InstanceDestination = null;

return DTSExecResult.Success;

}

catch (Exception exc)

{

componentEvents.FireError(0, "DynamicDataTransfer.Execute", "Task Errored: " + exc.ToString(), "", -1);

return DTSExecResult.Failure;

}

}

}

}

(Just as a side-note, your blog has helped me a ton since I've started with SSIS. Thanks for posting all of your findings.)

Jess

|||

Hey Jess,

I'm impressed. This is the first time I've seen a working bit of code that does this. Do you mind if I put a link from my blog to this? I think this needs to be shared.

One thing, the correct nomenclature is a custom task, not a custom component. My god, I must be in a critical mood

Regards

-Jamie

|||Custom Task, got it. Sure thing, link to your blog, I'll be proud. |||

JessicaElise wrote:

Custom Task, got it. Sure thing, link to your blog, I'll be proud.

Cool. Done!

Building Packages Programatically

http://blogs.conchango.com/jamiethomson/archive/2007/03/28/SSIS_3A00_-Building-Packages-Programatically.aspx

Thanks again Jess.

-Jamie

|||

Hi Jamie,

Since the Script task only support VB.NET, I'd like to know how the above code can be deployed in our SSIS Package

Are there any conversion tool available?

|||

Subhash512525 wrote:

Hi Jamie,

Since the Script task only support VB.NET, I'd like to know how the above code can be deployed in our SSIS Package

Are there any conversion tool available?

Yes, I'm sure there is. Not being a .Net expert myself I don't know where to find them but I'm sure they exist.

-Jamie

|||

Thanks for your reply Jamie,

I was successful implementing the above code in VB.NET code with slight modifications and additions required for my package using online code converter available here . I must thank you both a lot for the code.

Subhash Subramanyam

|||

Jess,

I was directed to your post by John Welch. I have a need very similar to yours, however, I am not a developer and I am having difficulties implementing your code snippet. I have VS Express for C# loaded and when I create a class library project I get the following error:

The type or namespace name 'Dts' does not exist in the namespace 'Microsoft.SqlServer' (are you missing an assembly reference?)

Can someone point me in the right direction? What other code elements are needed to make this "meat and potatoes" portion work?

Thanks in advance,

Keith

|||

Hi ,

I am also facing this problem, but I didn't understand where I have to write this code. If it is in script task then where is the main() .Can you please tell me or reply on this email address.(ramanandap@.gmail.com).

Thanks in advance

|||

KC wrote:

Jess,

I was directed to your post by John Welch. I have a need very similar to yours, however, I am not a developer and I am having difficulties implementing your code snippet. I have VS Express for C# loaded and when I create a class library project I get the following error:

The type or namespace name 'Dts' does not exist in the namespace 'Microsoft.SqlServer' (are you missing an assembly reference?)

Can someone point me in the right direction? What other code elements are needed to make this "meat and potatoes" portion work?

Thanks in advance,

Keith

You need to add a reference to the Microsoft.SQLServer.DTSRuntimeWrap assembly to your project. Use Project..Add Reference.

|||

Mahi12345 wrote:

Hi ,

I am also facing this problem, but I didn't understand where I have to write this code. If it is in script task then where is the main() .Can you please tell me or reply on this email address.(ramanandap@.gmail.com).

Thanks in advance

This was created as a custom task in VB.NET. That gets compiled to a .NET assembly (DLL), registered in the GAC, and then can be used from your SSIS packages. If you look under "Developing Custom Tasks" in Books Online, you can see more information about this.

Design Question- Dynamic Configuration of Metadata in Dataflow

I have a design question that I'd like some input on. I am trying to archive data from an extremely large production database. The tables to be archived changes quite often. It is currently along the lines of 80-100 tables with the possibility (likelihood) to grow from there.

If at all possible, I'd like to avoid writing an individual dataflow transformation for each table. I know that SSIS does not offer the same capabilities to change the metadata at runtime as DTS. I am currently exploring the option of programmatically creating/modifying the packages through the .Net framework. (using this link as a guide: http://msdn2.microsoft.com/en-us/library/ms345167.aspx).

I have concerns about the performance of this approach and was wondering if anyone had any feedback, or has implemented something similar, or has any other ideas on a different way to accomplish the same thing.

Thanks so much for your help,

Jessica

JessicaElise wrote:

I have a design question that I'd like some input on. I am trying to archive data from an extremely large production database. The tables to be archived changes quite often. It is currently along the lines of 80-100 tables with the possibility (likelihood) to grow from there.

If at all possible, I'd like to avoid writing an individual dataflow transformation for each table. I know that SSIS does not offer the same capabilities to change the metadata at runtime as DTS. I am currently exploring the option of programmatically creating/modifying the packages through the .Net framework. (using this link as a guide: http://msdn2.microsoft.com/en-us/library/ms345167.aspx).

I have concerns about the performance of this approach and was wondering if anyone had any feedback, or has implemented something similar, or has any other ideas on a different way to accomplish the same thing.

Thanks so much for your help,

Jessica

Assuming that the package you build programatically is the same as the package that you build in the SSIS Designer then performance will be exactly the same. Generating the package using the API shouldn't take too long - depends how good a coder you are

To be honest alot of people have mentioned they are attempting this but nobody has reported that they have achieved it. But then again why would they? People come on here to report problems, not successes.

Does that help at all? Probbaly not!!

-Jamie

|||Programmatically creating packages for each table is the way to go unfortunately. Have a look at this link: http://www.ivolva.com/ssis_code_generator.html I haven't used it myself, but if it does what it says, it should be a big help in writing the code to dynamically generate packages.
|||

Well I'm reporting a success, Jamie. I got it working in a very base, proof-of-concept form. I've only tested it for very small datasets though. We'll see what happens when I increase the load (and run it multi-threaded). But, in case this can be of any use to anyone else:

I created a custom component that will create the a dynamic package based on an inputted source table, destination table and package name (source and destination connection strings are currently hardcoded, but I'm going to change that as I develop the component). I've been successful in using this component to create the package and then using the "Execute Package Task" to execute the package that was just created. Chuck that into a ForEach Loop that goes through a recordset containing a DestinationTable, SourceTable, and PackageName and modifies the expressions of the component and it works.

Here's the meat-and-potatoes of the Custom Component I created.

Code Snippet

using System;

using System.Collections.Generic;

using System.Text;

using Microsoft.SqlServer.Dts.Runtime;

using System.IO;

using System.Data;

using Microsoft.SqlServer.Dts.Pipeline;

using Microsoft.SqlServer.Dts.Pipeline.Wrapper ;

namespace DynamicDataTransfer

{

[

DtsTask(

DisplayName = "Dynamic Data Transfer Task",

Description = "Dynamic Data Transfer.",

UITypeName = "DynamicDataTransfer.DynamicDataTransferTaskUI, DynamicDataTransfer, Version=1.0.0.0, Culture=Neutral, PublicKeyToken=xxxxxxxxxxxxxxxx"

)

]

public class DynamicDataTransfer : Task

{

private string SourceTableNameInternal = "";

public string SourceTableName

{

get

{

return this.SourceTableNameInternal;

}

set

{

this.SourceTableNameInternal = value;

}

}

private string PackageNameInternal = "";

public string PackageName

{

get

{

return this.PackageNameInternal;

}

set

{

this.PackageNameInternal = value;

}

}

private string DestinationTableNameInternal = "";

public string DestinationTableName

{

get

{

return this.DestinationTableNameInternal;

}

set

{

this.DestinationTableNameInternal = value;

}

}

/// <summary>

/// Executes the task

/// </summary>

public override DTSExecResult Execute(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log, object transaction)

{

try

{

Microsoft.SqlServer.Dts.Runtime.Application a = new Microsoft.SqlServer.Dts.Runtime.Application();

String SSISPackageFilePath;

SSISPackageFilePath = PackageNameInternal;

if (File.Exists(SSISPackageFilePath))

File.Delete(SSISPackageFilePath);

Package pkg = new Package();

MainPipe dataFlow;

ConnectionManager conMgrSource = pkg.Connections.Add("OLEDB");

ConnectionManager conMgrDestination = pkg.Connections.Add("OLEDB");

conMgrSource.Name = "OLEDBConnectionSource";

conMgrSource.ConnectionString = "Data Source=SOURCESERVER;Initial Catalog=SOURCEDB;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;";

conMgrDestination.Name = "OLEDBConnectionDestination";

conMgrDestination.ConnectionString = "Data Source=DESTINATIONSERVER;Initial Catalog=DESTINATIONDB;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;";

Executable exe = pkg.Executables.Add("DTS.Pipeline.1");

TaskHost th = exe as TaskHost;

th.Name = "DynamicDataFlowTask";

dataFlow = th.InnerObject as MainPipe;

IDTSComponentMetaDataCollection90 metadataCollection = dataFlow.ComponentMetaDataCollection;

IDTSComponentMetaData90 OLEDBSource = dataFlow.ComponentMetaDataCollection.New();

OLEDBSource.Name = "OLEDBSource";

OLEDBSource.ComponentClassID = "DTSAdapter.OLEDBSource.1";

IDTSComponentMetaData90 OLEDBDestination = dataFlow.ComponentMetaDataCollection.New();

OLEDBDestination.Name = "OLEDBDestination";

OLEDBDestination.ComponentClassID = "DTSAdapter.OLEDBDestination.1";

// Get the design time instance of the component.

CManagedComponentWrapper InstanceSource = OLEDBSource.Instantiate();

// Initialize the component

InstanceSource.ProvideComponentProperties();

// Specify the connection manager.

if (OLEDBSource.RuntimeConnectionCollection.Count > 0)

{

OLEDBSource.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(pkg.Connections["OLEDBConnectionSource"]);

OLEDBSource.RuntimeConnectionCollection[0].ConnectionManagerID = pkg.Connections["OLEDBConnectionSource"].ID;

}

InstanceSource.SetComponentProperty("OpenRowset", SourceTableNameInternal);

InstanceSource.SetComponentProperty("AccessMode", 0);

//reinitialize the component

InstanceSource.AcquireConnections(null);

InstanceSource.ReinitializeMetaData();

InstanceSource.ReleaseConnections();

// Get the design time instance of the component.

CManagedComponentWrapper InstanceDestination = OLEDBDestination.Instantiate();

// Initialize the component

InstanceDestination.ProvideComponentProperties();

// Specify the connection manager.

if (OLEDBDestination.RuntimeConnectionCollection.Count > 0)

{

OLEDBDestination.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(pkg.Connections["OLEDBConnectionDestination"]);

OLEDBDestination.RuntimeConnectionCollection[0].ConnectionManagerID = pkg.Connections["OLEDBConnectionDestination"].ID;

}

InstanceDestination.SetComponentProperty("OpenRowset", DestinationTableNameInternal);

InstanceDestination.SetComponentProperty("AccessMode", 0);

//reinitialize the component

InstanceDestination.AcquireConnections(null);

InstanceDestination.ReinitializeMetaData();

InstanceDestination.ReleaseConnections();

//map the columns

IDTSPath90 path = dataFlow.PathCollection.New();

path.AttachPathAndPropagateNotifications(OLEDBSource.OutputCollection[0], OLEDBDestination.InputCollection[0]);

IDTSInput90 input = OLEDBDestination.InputCollection[0];

IDTSVirtualInput90 vInput = input.GetVirtualInput();

foreach (IDTSVirtualInputColumn90 vColumn in vInput.VirtualInputColumnCollection)

{

IDTSInputColumn90 vCol = InstanceDestination.SetUsageType(input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READWRITE);

InstanceDestination.MapInputColumn(input.ID, vCol.ID, input.ExternalMetadataColumnCollection[vColumn.Name].ID);

}

a.SaveToXml(SSISPackageFilePath, pkg, null);

th = null;

pkg = null;

a = null;

OLEDBSource = null;

OLEDBDestination = null;

InstanceSource = null;

InstanceDestination = null;

return DTSExecResult.Success;

}

catch (Exception exc)

{

componentEvents.FireError(0, "DynamicDataTransfer.Execute", "Task Errored: " + exc.ToString(), "", -1);

return DTSExecResult.Failure;

}

}

}

}

(Just as a side-note, your blog has helped me a ton since I've started with SSIS. Thanks for posting all of your findings.)

Jess

|||

Hey Jess,

I'm impressed. This is the first time I've seen a working bit of code that does this. Do you mind if I put a link from my blog to this? I think this needs to be shared.

One thing, the correct nomenclature is a custom task, not a custom component. My god, I must be in a critical mood

Regards

-Jamie

|||Custom Task, got it. Sure thing, link to your blog, I'll be proud. |||

JessicaElise wrote:

Custom Task, got it. Sure thing, link to your blog, I'll be proud.

Cool. Done!

Building Packages Programatically

http://blogs.conchango.com/jamiethomson/archive/2007/03/28/SSIS_3A00_-Building-Packages-Programatically.aspx

Thanks again Jess.

-Jamie

|||

Hi Jamie,

Since the Script task only support VB.NET, I'd like to know how the above code can be deployed in our SSIS Package

Are there any conversion tool available?

|||

Subhash512525 wrote:

Hi Jamie,

Since the Script task only support VB.NET, I'd like to know how the above code can be deployed in our SSIS Package

Are there any conversion tool available?

Yes, I'm sure there is. Not being a .Net expert myself I don't know where to find them but I'm sure they exist.

-Jamie

|||

Thanks for your reply Jamie,

I was successful implementing the above code in VB.NET code with slight modifications and additions required for my package using online code converter available here . I must thank you both a lot for the code.

Subhash Subramanyam

|||

Jess,

I was directed to your post by John Welch. I have a need very similar to yours, however, I am not a developer and I am having difficulties implementing your code snippet. I have VS Express for C# loaded and when I create a class library project I get the following error:

The type or namespace name 'Dts' does not exist in the namespace 'Microsoft.SqlServer' (are you missing an assembly reference?)

Can someone point me in the right direction? What other code elements are needed to make this "meat and potatoes" portion work?

Thanks in advance,

Keith

|||

Hi ,

I am also facing this problem, but I didn't understand where I have to write this code. If it is in script task then where is the main() .Can you please tell me or reply on this email address.(ramanandap@.gmail.com).

Thanks in advance

|||

KC wrote:

Jess,

I was directed to your post by John Welch. I have a need very similar to yours, however, I am not a developer and I am having difficulties implementing your code snippet. I have VS Express for C# loaded and when I create a class library project I get the following error:

The type or namespace name 'Dts' does not exist in the namespace 'Microsoft.SqlServer' (are you missing an assembly reference?)

Can someone point me in the right direction? What other code elements are needed to make this "meat and potatoes" portion work?

Thanks in advance,

Keith

You need to add a reference to the Microsoft.SQLServer.DTSRuntimeWrap assembly to your project. Use Project..Add Reference.

|||

Mahi12345 wrote:

Hi ,

I am also facing this problem, but I didn't understand where I have to write this code. If it is in script task then where is the main() .Can you please tell me or reply on this email address.(ramanandap@.gmail.com).

Thanks in advance

This was created as a custom task in VB.NET. That gets compiled to a .NET assembly (DLL), registered in the GAC, and then can be used from your SSIS packages. If you look under "Developing Custom Tasks" in Books Online, you can see more information about this.