changeing Dataflow tasks propeties by coding

Hi,
Is it possible to change the Dataflow tasks properties with VB coding? I've managed to change the properties of the control Flow tasks with VB code! as I wan to create a generic dataflow that will change everytime I run it.
CheersYes, you can. It is a bit more complicated, as many of the properties are contained in a CustomProperties collection on the data flow objects, so you need to find the names of the specific properties that you are interested in.|||

Kolf wrote:

Hi,
Is it possible to change the Dataflow tasks properties with VB coding? I've managed to change the properties of the control Flow tasks with VB code! as I wan to create a generic dataflow that will change everytime I run it.
Cheers

Kolf,

You know that you cannot change this stuff while the package is running right? You need to affect the change prior to the package running.

-Jamie

|||would you be able to give me on example so it will keep me going, yes and I am aware of the issue that I need to change the properties prior to running the package. therefore it dataflow should be in a sub-package.
Thanks|||

Kolf wrote:

would you be able to give me on example so it will keep me going, yes and I am aware of the issue that I need to change the properties prior to running the package. therefore it dataflow should be in a sub-package.
Thanks

This is quite a big area and I don't know of one example that covers everything. What in particular are you having trouble with? Navigating through the package to the property or knowing how to change it?

-Jamie

|||Thanks again Jamie, you've been very helpful

So there is no escape or work around this issue. I was hoping instead on using Transfer SQL Server object task (to copy the table to destination server, which dynamic tablelist feed – and using sub-package ), I could use dataflow tasks within one package (and avoid using sub-package and changing the property of the sub-package from parent package – as this is how it works now and it’s very slow) .

So I can use execute sql task to populate the table schema on the destination and then use a dataflowtask to push the data across , all in one package. And be able to put this in a loop to do for a list of tables.

I hope I’ve explained it clearly

|||

Kolf wrote:

would you be able to give me on example so it will keep me going, yes and I am aware of the issue that I need to change the properties prior to running the package. therefore it dataflow should be in a sub-package.
Thanks

Kolf,

Jessica Elise has posted some great code here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1404157&SiteID=1&mode=1 that may be of use.

-Jamie

changeing Dataflow tasks propeties by coding

Hi,
Is it possible to change the Dataflow tasks properties with VB coding? I've managed to change the properties of the control Flow tasks with VB code! as I wan to create a generic dataflow that will change everytime I run it.
Cheers

Yes, you can. It is a bit more complicated, as many of the properties are contained in a CustomProperties collection on the data flow objects, so you need to find the names of the specific properties that you are interested in.|||

Kolf wrote:

Hi,
Is it possible to change the Dataflow tasks properties with VB coding? I've managed to change the properties of the control Flow tasks with VB code! as I wan to create a generic dataflow that will change everytime I run it.
Cheers

Kolf,

You know that you cannot change this stuff while the package is running right? You need to affect the change prior to the package running.

-Jamie

|||would you be able to give me on example so it will keep me going, yes and I am aware of the issue that I need to change the properties prior to running the package. therefore it dataflow should be in a sub-package.
Thanks
|||

Kolf wrote:

would you be able to give me on example so it will keep me going, yes and I am aware of the issue that I need to change the properties prior to running the package. therefore it dataflow should be in a sub-package.
Thanks

This is quite a big area and I don't know of one example that covers everything. What in particular are you having trouble with? Navigating through the package to the property or knowing how to change it?

-Jamie

|||Thanks again Jamie, you've been very helpful

So there is no escape or work around this issue. I was hoping instead on using Transfer SQL Server object task (to copy the table to destination server, which dynamic tablelist feed – and using sub-package ), I could use dataflow tasks within one package (and avoid using sub-package and changing the property of the sub-package from parent package – as this is how it works now and it’s very slow) .

So I can use execute sql task to populate the table schema on the destination and then use a dataflowtask to push the data across , all in one package. And be able to put this in a loop to do for a list of tables.

I hope I’ve explained it clearly

|||

Kolf wrote:

would you be able to give me on example so it will keep me going, yes and I am aware of the issue that I need to change the properties prior to running the package. therefore it dataflow should be in a sub-package.
Thanks

Kolf,

Jessica Elise has posted some great code here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1404157&SiteID=1&mode=1 that may be of use.

-Jamie

Change the type of an expression container

Hi,

I use expressions to build the SQL query that is executed by the data flow.

Today, I ran into an issue. For the first time the SQL query has exceeded 4000 char so the expression cannot be validated and it is not possible to use it.

Is there a way to change the expression datatype to nvarchar(max) instead of the default nvarchar(4000) ?

Thanks,

Philippe

Philippe,

Unfortunately 4000 is the limit on the result of an expression.

-Jamie

|||

So i am back in the business of using multiple variables.

Too bad

|||Is there any way around the 4000 literal string masx issues. Its sad that the case because a table could contain many fields and that might cause the literal string to be more than 4000.

Change Self reference values in a data flow task

Hi,

We migrate data from a legacy system to new system using SSIS. The primary key of legacy system is a user-defined sql server which holds alpha-numeric values. The primary key of new system is a big int(sequential numbers).

When we migrate data, we generate a sequential number for each legacy key(the primary key of legacy data) and insert data in to new system tables. The newly generated sequential numbers and the legacy keys are persisted in an intermidiate table for look up operations of child tables.

We are facing problem when we try to migrate tables which has self referring coulumns. For example a table called Employee has a column ManagerKey which refers to Key column of Employee table. We are struck up in defining data flow tasks to replace legacy ManagerKey column values with the new values(sequential values) generated during the migration process.

Please help me to solve this problem.

Regards,

Gopi

Hi Gopi,

Did you ever resolve this? If so, how? I am facing the same issue.

Thanks,

Dave

|||The way to solve this is by creating your own lookup component using a custom script transformation. Instead of looking up reference values from an external table, your script should cache each unique key translation pair in a memory-based structure like a dictionary. Each old/new EmployeeKey pair gets cached into memory, and each old ManagerKey gets looked up in that cache and replaced with the new value. You would need to make sure that your data is sorted appropriately so that you don't get a ManagerKey before the EmployeeKey.
|||

Hi Dave,

Good morning. We preferred to resolve this problem at SQL Server Engine instead at Integration Services Flow. We are using query built with a Common Table Expression of SQL Server 2005 to identify all the invalid self referencing records. The SSIS Components are proven as very poor performing to identify invalid self referencing records

Cheers,

Gopi

Change Self reference values in a data flow task

Hi,

We migrate data from a legacy system to new system using SSIS. The primary key of legacy system is a user-defined sql server which holds alpha-numeric values. The primary key of new system is a big int(sequential numbers).

When we migrate data, we generate a sequential number for each legacy key(the primary key of legacy data) and insert data in to new system tables. The newly generated sequential numbers and the legacy keys are persisted in an intermidiate table for look up operations of child tables.

We are facing problem when we try to migrate tables which has self referring coulumns. For example a table called Employee has a column ManagerKey which refers to Key column of Employee table. We are struck up in defining data flow tasks to replace legacy ManagerKey column values with the new values(sequential values) generated during the migration process.

Please help me to solve this problem.

Regards,

Gopi

Hi Gopi,

Did you ever resolve this? If so, how? I am facing the same issue.

Thanks,

Dave|||The way to solve this is by creating your own lookup component using a custom script transformation. Instead of looking up reference values from an external table, your script should cache each unique key translation pair in a memory-based structure like a dictionary. Each old/new EmployeeKey pair gets cached into memory, and each old ManagerKey gets looked up in that cache and replaced with the new value. You would need to make sure that your data is sorted appropriately so that you don't get a ManagerKey before the EmployeeKey.|||

Hi Dave,

Good morning. We preferred to resolve this problem at SQL Server Engine instead at Integration Services Flow. We are using query built with a Common Table Expression of SQL Server 2005 to identify all the invalid self referencing records. The SSIS Components are proven as very poor performing to identify invalid self referencing records

Cheers,

Gopi