Is it possible to modify Report Builder template ?

Hi

I would like to be able to modify the basic report template offered by Report Builder , so as to be able to include custom fields that I would like to be present in every report (Company name , Logo , Date , etc) to achieve consistency in the presentation of the reports created with Report Builder

In Bob's Blog he explained how to include RDL expressions in Report Builder
http://blogs.msdn.com/bobmeyers/archive/2006/03/26/561147.aspx

What I would like to do is to use that technique (if possible) to modify the original template or templates offered by Report Builder to include my own fields.

============================================

I will appreciate some comments or suggestions about other approaches that can be used to address the issue mentioned in the first paragraph .

Thanks in advance ,

George

Just create a report as a template, with all your controls on it, and copy the rdl to this directory:

RS 2005:

C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject

It will show up in your add new item window. I named my report templates Portrait.rdl and Landscape.rdl with the margins set in each. plus i added a table and changed the cell padding and fonts.

For 2000, I think the location is in \program files\sql server... But i can't remember where. You could search your PC for *.rdl

Anyway, there is a file called report.rdl; that is the default one from MS. One caveat, you will have to do this on every machine as the template is on your local PC.

|||Hi Steve ,

Thank you for your answer . I am refering to the Report Builder (ad-hoc reporting tool), not the Report Designer of Visual Studio .

Have you managed to do something similar with the Report Builder Tool ?

Regards ,

George
|||

My mistake. I have never used the report builder. here are a couple of threads I found, and it doesn't look to promising:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=303097&SiteID=1

and here:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=285749&SiteID=1

|||I see what you mean . Thank you very much for your help

George
|||

Hi,

I am truying to acheive the same. Please let me know if you found the solution.

Regards,

Prakash

|||

Hi
At moment support for templates, styles, and images are not available with report builder. For more information please see: http://www.microsoft.com/sql/technologies/reporting/faq.mspx, http://msdn2.microsoft.com/en-gb/library/aa964121.aspx (Understanding the Product Limitations)

The workaround for this is to have a report (just format) that will be served as template. User will required to open this report (template) then after making the required changes they make copy of the same by doing "Save As" instead of "Save"

Please let me know if you need some more information in this regard.

Regards;
Rakesh

Is it possible to modify Report Builder template ?

Hi

I would like to be able to modify the basic report template offered by Report Builder , so as to be able to include custom fields that I would like to be present in every report (Company name , Logo , Date , etc) to achieve consistency in the presentation of the reports created with Report Builder

In Bob's Blog he explained how to include RDL expressions in Report Builder
http://blogs.msdn.com/bobmeyers/archive/2006/03/26/561147.aspx

What I would like to do is to use that technique (if possible) to modify the original template or templates offered by Report Builder to include my own fields.

============================================

I will appreciate some comments or suggestions about other approaches that can be used to address the issue mentioned in the first paragraph .

Thanks in advance ,

George

Just create a report as a template, with all your controls on it, and copy the rdl to this directory:

RS 2005:

C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject

It will show up in your add new item window. I named my report templates Portrait.rdl and Landscape.rdl with the margins set in each. plus i added a table and changed the cell padding and fonts.

For 2000, I think the location is in \program files\sql server... But i can't remember where. You could search your PC for *.rdl

Anyway, there is a file called report.rdl; that is the default one from MS. One caveat, you will have to do this on every machine as the template is on your local PC.

|||Hi Steve ,

Thank you for your answer . I am refering to the Report Builder (ad-hoc reporting tool), not the Report Designer of Visual Studio .

Have you managed to do something similar with the Report Builder Tool ?

Regards ,

George|||

My mistake. I have never used the report builder. here are a couple of threads I found, and it doesn't look to promising:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=303097&SiteID=1

and here:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=285749&SiteID=1

|||I see what you mean . Thank you very much for your help

George|||

Hi,

I am truying to acheive the same. Please let me know if you found the solution.

Regards,

Prakash

|||

Hi
At moment support for templates, styles, and images are not available with report builder. For more information please see: http://www.microsoft.com/sql/technologies/reporting/faq.mspx, http://msdn2.microsoft.com/en-gb/library/aa964121.aspx (Understanding the Product Limitations)

The workaround for this is to have a report (just format) that will be served as template. User will required to open this report (template) then after making the required changes they make copy of the same by doing "Save As" instead of "Save"

Please let me know if you need some more information in this regard.

Regards;
Rakesh

Is it possible to modify Report Builder template ?

Hi

I would like to be able to modify the basic report template offered by Report Builder , so as to be able to include custom fields that I would like to be present in every report (Company name , Logo , Date , etc) to achieve consistency in the presentation of the reports created with Report Builder

In Bob's Blog he explained how to include RDL expressions in Report Builder
http://blogs.msdn.com/bobmeyers/archive/2006/03/26/561147.aspx

What I would like to do is to use that technique (if possible) to modify the original template or templates offered by Report Builder to include my own fields.

============================================

I will appreciate some comments or suggestions about other approaches that can be used to address the issue mentioned in the first paragraph .

Thanks in advance ,

George

Just create a report as a template, with all your controls on it, and copy the rdl to this directory:

RS 2005:

C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject

It will show up in your add new item window. I named my report templates Portrait.rdl and Landscape.rdl with the margins set in each. plus i added a table and changed the cell padding and fonts.

For 2000, I think the location is in \program files\sql server... But i can't remember where. You could search your PC for *.rdl

Anyway, there is a file called report.rdl; that is the default one from MS. One caveat, you will have to do this on every machine as the template is on your local PC.

|||Hi Steve ,

Thank you for your answer . I am refering to the Report Builder (ad-hoc reporting tool), not the Report Designer of Visual Studio .

Have you managed to do something similar with the Report Builder Tool ?

Regards ,

George|||

My mistake. I have never used the report builder. here are a couple of threads I found, and it doesn't look to promising:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=303097&SiteID=1

and here:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=285749&SiteID=1

|||I see what you mean . Thank you very much for your help

George|||

Hi,

I am truying to acheive the same. Please let me know if you found the solution.

Regards,

Prakash

|||

Hi
At moment support for templates, styles, and images are not available with report builder. For more information please see: http://www.microsoft.com/sql/technologies/reporting/faq.mspx, http://msdn2.microsoft.com/en-gb/library/aa964121.aspx (Understanding the Product Limitations)

The workaround for this is to have a report (just format) that will be served as template. User will required to open this report (template) then after making the required changes they make copy of the same by doing "Save As" instead of "Save"

Please let me know if you need some more information in this regard.

Regards;
Rakesh

Is it possible to modify column data type of view

The SQL Server Management Studio shows the data type of each column of views. I am wondering how SQL server determines the types since my SQL code of views does not specifiy data types for any columns.

I am much more interested in knowing whether the data types can be modified. Could anyone offer some hint?

Thanks,

hz

SQL Server uss the underlying schema information to do so unless you don′t specify a different data type than the source data type (liek within CONVERT). You can change the resulting data type in the view e.g. via CONNVERT(VARCHAR(10),GETDATE(),112), which was a datetime before and a varchar afterwards.

HTH, Jens Suessmeyer.|||

Jens, thanks a lot! That is exactly what I was looking for.

hz

Is it possible to generate alter Table statements using SMO

Hi

I'm trying to modify existing tables in a database.

How can I create alter Table scripts using SMO/DMO

Thank you

Yep, you can use the following to either execute and capture, just execute (which is the default) or just capture the executed commands:

Server s = new Server(".");

s.ConnectionContext.SqlExecutionModes = Microsoft.SqlServer.Management.Common.SqlExecutionModes.CaptureSql

//Microsoft.SqlServer.Management.Common.SqlExecutionModes.CaptureSql

//Microsoft.SqlServer.Management.Common.SqlExecutionModes.ExecuteAndCaptureSql

//Microsoft.SqlServer.Management.Common.SqlExecutionModes.ExecuteSql

//s.ConnectionContext.CapturedSql.Text; //Get the Text

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks again Jens

I'm trying the follwong code

Server server1 = new Server(".");

Database db= server1.Databases["master"];

server1.ConnectionContext.SqlExecutionModes = SqlExecutionModes.CaptureSql;

foreach (Table Tbl in db.Tables)

{

tabl.Alter ();

}

db1.Refresh();

//writing to a file

writeToFile(server1.ConnectionContext.CapturedSql.Text, "alter", "tables");

But it is not generating Alter statments.

But if I use Create(), in place of alter(), it's generating Create statments.

|||Hi,
if you do not change anything, what are you supposed to see in the ALTER script :-) ?

In this sample I added a column to the table resulting in a script with an ALTER Script and an ADD column command.

Server s = new Server(".");

s.ConnectionContext.SqlExecutionModes = Microsoft.SqlServer.Management.Common.SqlExecutionModes.CaptureSql;

Table t = s.Databases["SMOTest"].Tables["TestTable"];

t.Columns.Add(new Column(t,"SomeSMOTest",DataType.DateTime));

t.Alter();

foreach (string st in s.ConnectionContext.CapturedSql.Text)

{

Console.WriteLine(st);

}

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks Jens

Got it.

while comparing a table in one database to other table (identical) in other database,

if the Source table has some modified(altered) columns and need to be modified in the target table.

How to solve this problem.Any Idea.

I generated the alter scripts manually for each column.

Like

ALTER TABLE [dbo].[wo]

ADD [requested-time] varchar (8 ) NULL

Thank you

|||You will have to do this manually. Load the two schemas and compare the columns (if you just want to check the columns) with each other. Change the columns appropiately with SMO and get the script from the Context. if you want an integrated tool which can do this on its own use Visual Studio for database professionals, this does have a comparer and script generator for keeping the databases in sync.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Thanks alot Jens.

I'll try for this

Is anyone using C# and the SSIS Objects to Develop/Modify Packages?

Anyone out there developing or modifying packages w/ C# and the SSIS objects that I can compare notes with?

Thanks!

Done a bit, loading and poking around and some creating directly in code.|||

Hi Darren ...

I've taken a look around SQLIS. Very helpful indeed!

Do you have any examples of something as simple as the following, using C# and the object model:

Source: SQL Server A, Table Foo

Transformations: None

Target SQL Server B, Table FooTwo

The real trick with the above is that at runtime I won't know what the tables are. I need to work w/ a SQL String and the re-initializemetadata stuff.

Looking for any help here.

Thanks!

|||

I would probably rebuild the package each time, and Books Online covers this quite well I think.

Start with ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/dtsref9/html/0ca03712-a82e-4aa7-949b-f869a8936ddf.htm, then move onto the related Data Flow section.

The Data Flow sample includes setting the SQL and calling RMD, see "Adding and Configuring a Component".

|||

Thank you very much for the suggestions!

I've looked over these items in the past and to be honest I think they are a bit weak in terms of examples, etc. My experience has been that some of the docs on this go into great detail, but they don't give you "the big picture." I got a copy of Professional SQL Server 2005 Integration Services (wrox) and this has been a help, but I'm still looking for more examples.

Any other suggestions?

All the best,

DB

|||

Chapters 14 and 15 of that book is where you want to head. I've already been through Chapter 14 and it was a really great tutorial.

Darren wrote chapter 15 and his partner Allan Mitchell wrote chapter 14 and they're probably too modest to sing their own praises ...so I'll do it for them.

-Jamie

|||

Thank you for the plug, but those chapters are more about building components, not components. Actually if you do understand how to build components, and the workings of adding and removing columns which they cover, then it will help your understanding for when building packages as well, I know it does for me. You do similar things in your component as when building packages, such as working on the managed interface wrapper, and selecting columns.

I don't know of anymore examples, I have always managed to do what I need quite effectively on what is above. You have the basics of building packages, so now everything else is just the nuances for the task or component type, which is more about understanding that component as opposed to general building package knowledge. Perhaps you could post individual problems as you get them and we'll see if we can help.

|||

Thanks to both of you for the supportive words. I'm doing my best to solve this problem. I'm sure once I get a few components to work it becomes more of a cookie cutter process.

Here's an example of a problem I'm trying to solve using the Object Model. If anyone has a code sample that does some or all of these simple steps from end-to-end that would be an extreme help! It *seems* that this should be so simple!

In a nutshell:

I'm using C# and the SSIS object modle to try to create a single package that at runtime can look up a table and list of columns from a source SQL Server. With this table and column list in hand, I simply want to move the table's source data in its identical format as quickly as possible to another destination SQL Server into an identical table.

The reason I'm using the C# object model approach is so that I can build one package/application that can be used to move data for many different tables, determined at runtime, without having to have multiple packages.

In other words, if I have 300 tables to move data from SQL Server A to SQL Server B, I want one package, not 300. Earlier posts in this forum said to accomplish this requires C# and the object model. Reference: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=112283&SiteID=1

In the final product there will be one transformation, but I'd be happy right now just to get all the column outputs and inputs working correctly.

So far I'm having success with:

- Creating the initial package

- Creating the MainPipe

- Creating source and target OLE-DB Connection Managers

- Setting up a source component (see issues below)

- Doing an Instantiate and ProvideComponentProperties

- Associating the source component with the source connection manager

- Using SetComponentProperties to set the SQL statement (see issues below)

- Connecting to the source and running reinitializemetadata

- Repeating the above for the destination component

- Setting up a path between the two

Here are the issues that I'm pretty sure are screwing me up. I'm having a hard time with:

1. With the source component, all I want is "select col1, col2 from foo". I'm pretty sure that I'm missing some important steps to tell SSIS the specific list of columns that I want to have in my source component output. I am running the reinitializemetadata step, but I don't *think* this is enough by itself. I'm pretty sure there are some commands related to IDTSOutput90 but I'm having a hard time figuring out how to accomplish this step by step. I haven't been able to follow BOL to put this together.

2. I'm also missing the corresponding IDTSInput90 techniques to identify the input columns coming into my destination component. I looked at

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/dtsref9/html/ef258c93-446f-44e6-b040-8164315b58ee.htm

as a reference, but this didn't seem to help (translation: too complex for me to understand without some assistance, and pilot error.) The idea of having this as a dynamic list would be extremely helpful for my particular problem.

3. OK, once my data shows up at my destination component if possible I need to UPSERT the data. A code example on this would be helpful ... how to set up the SQL statements for the SetComponentProperty.

4. Finally, I've been trying to figure out the right combination of AccessMode integer values that match SQL statements and such used by SetComponentProperty. For example:

mySrcDTInstance.SetComponentProperty("CommandTimeout", 0);

mySrcDTInstance.SetComponentProperty("OpenRowset", "[dbo].[test]");

mySrcDTInstance.SetComponentProperty("AccessMode", 0);

I've run every search I could think of and have posted the question before: What are the proper combinations of AccessMode integer values and SQL statments, OpenRowset, etc.

Any samples or suggestions or doc references I can delve into would be a GREAT help!!!!!

DB

|||

Hi Doug B,

We are facing the same problem here. We need to move 200 tables with Data Flow Task. I was wondering if you figure out a solution since the last posting.

Mathieu

|||

Doug B wrote:

I'm using C# and the SSIS object modle to try to create a single package that at runtime can look up a table and list of columns from a source SQL Server. With this table and column list in hand, I simply want to move the table's source data in its identical format as quickly as possible to another destination SQL Server into an identical table.

I haven't looked at your post in detail but I think I should pick you up one one point here. You say you want to "create a single package that at runtime can look up a table and list of columns from <somewhere>". I think your approach here is slightly awry. Yes, you need to write dotnet code to do this. But in your sentance here it sounds as though you want to SSIS to be a host for that dotnet code - and I think that is wrong. Sure, write a dotnet app that uses the SSIS object model to create a package based on some defined metadata - but there's no need to run that code actually within a SSIS package. Why not jsut run it from the command-line?

Remember that it is not possible for a SSIS package to change itself using the object model. You could do this in DTS, but not in SSIS.

I hope the subtle distinction is clear here.

-Jamie

|||

Doug B wrote:

3. OK, once my data shows up at my destination component if possible I need to UPSERT the data. A code example on this would be helpful ... how to set up the SQL statements for the SetComponentProperty.

That's simply not possible just in a destination component. If you want to do an upsert then you will need to compare the pipeline data with the destination and that needs to happen upstream of the destination component. I've talked about this technique more here:

Checking if a row exists and if it does, has it changed?
http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Checking-if-a-row-exists-and-if-it-does_2C00_-has-it-changed.aspx

-Jamie

|||

Doug B wrote:

1. With the source component, all I want is "select col1, col2 from foo". I'm pretty sure that I'm missing some important steps to tell SSIS the specific list of columns that I want to have in my source component output. I am running the reinitializemetadata step, but I don't *think* this is enough by itself. I'm pretty sure there are some commands related to IDTSOutput90 but I'm having a hard time figuring out how to accomplish this step by step. I haven't been able to follow BOL to put this together.

I vaguely remember trying this myself once. You can't just give the source adapter a SQL statement and let it work out the metadata itself. Although this is what it appears as if the Source Adapters do in the SSIS Designer UI, it isn't actually the case. The adapters still have to interogate the source to find the metadata - you have to do the same (Unless you have the column data types in your metadata store).

-Jamie

|||

Hi Mathieu ...

Unfortunately we were not able to get this to work. Perhaps someone with more C# experience would have had better luck.

All the best,

Doug

|||

Hi everyone,

I've been working on this issue since one week.

Finally, I built a c# class that generate a package with the 200 Dataflow Task (source, lookup, destination) with the SSIS object API.

It is not that complicated except that the api is quite undocumented...

My program is doing the following task :

#1 - From a table, I get the table source (TableName) and the destination (tableName) with the load order

#2 - Using Package API, I create a package from a template

#3 - With the load order, I create a DataFlow Task using API that contain :

A) Source

B) LookUp

C) Destination

We will save some precious time on refactoring aspect using this class...

Jamie : Nice website... I used it since I'm working with SSIS...I get some practical information...Keep on good work...

Doug_b : About IDTSOutput90, this is an example for a simple DataFlow Task.

I hope It can help you

#region Map MetaData
public void MetaDataMapping(IDTSComponentMetaData90 dstMetaData, IDTSDesigntimeComponent90 dstComp)
{

IDTSInput90 destinationInput = dstMetaData.InputCollection[0];
int destinationInputID = destinationInput.ID;
IDTSVirtualInput90 destinationVirtualInput = destinationInput.GetVirtualInput();
foreach (IDTSVirtualInputColumn90 virtualInputColumn in destinationVirtualInput.VirtualInputColumnCollection)
{

// This will create an input column on the component.
dstComp.SetUsageType(destinationInputID,destinationVirtualInput,virtualInputColumn.LineageID,DTSUsageType.UT_READONLY);
// Get input column.
IDTSInputColumn90 inputColumn = destinationInput.InputColumnCollection.GetInputColumnByLineageID(virtualInputColumn.LineageID);
// Getting the corresponding external column.
// Ex : We will use the column name as the basis for matching data flow columns to external columns.
IDTSExternalMetadataColumn90 externalColumn = destinationInput.ExternalMetadataColumnCollection[virtualInputColumn.Name];
// Tell the component how to map.
dstComp.MapInputColumn(destinationInputID,inputColumn.ID,externalColumn.ID);
}
}
#endregion

|||

Hi,

I tried to create SSIS package programatically,I successfully added OLE DB source & destination but

got problem in adding lookup transformation.

How can I get reference table column output.& how can I set join column property for specific column

plz help.

Is anyone using C# and the SSIS Objects to Develop/Modify Packages?

Anyone out there developing or modifying packages w/ C# and the SSIS objects that I can compare notes with?

Thanks!

Done a bit, loading and poking around and some creating directly in code.|||

Hi Darren ...

I've taken a look around SQLIS. Very helpful indeed!

Do you have any examples of something as simple as the following, using C# and the object model:

Source: SQL Server A, Table Foo

Transformations: None

Target SQL Server B, Table FooTwo

The real trick with the above is that at runtime I won't know what the tables are. I need to work w/ a SQL String and the re-initializemetadata stuff.

Looking for any help here.

Thanks!

|||

I would probably rebuild the package each time, and Books Online covers this quite well I think.

Start with ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/dtsref9/html/0ca03712-a82e-4aa7-949b-f869a8936ddf.htm, then move onto the related Data Flow section.

The Data Flow sample includes setting the SQL and calling RMD, see "Adding and Configuring a Component".

|||

Thank you very much for the suggestions!

I've looked over these items in the past and to be honest I think they are a bit weak in terms of examples, etc. My experience has been that some of the docs on this go into great detail, but they don't give you "the big picture." I got a copy of Professional SQL Server 2005 Integration Services (wrox) and this has been a help, but I'm still looking for more examples.

Any other suggestions?

All the best,

DB

|||

Chapters 14 and 15 of that book is where you want to head. I've already been through Chapter 14 and it was a really great tutorial.

Darren wrote chapter 15 and his partner Allan Mitchell wrote chapter 14 and they're probably too modest to sing their own praises ...so I'll do it for them.

-Jamie

|||

Thank you for the plug, but those chapters are more about building components, not components. Actually if you do understand how to build components, and the workings of adding and removing columns which they cover, then it will help your understanding for when building packages as well, I know it does for me. You do similar things in your component as when building packages, such as working on the managed interface wrapper, and selecting columns.

I don't know of anymore examples, I have always managed to do what I need quite effectively on what is above. You have the basics of building packages, so now everything else is just the nuances for the task or component type, which is more about understanding that component as opposed to general building package knowledge. Perhaps you could post individual problems as you get them and we'll see if we can help.

|||

Thanks to both of you for the supportive words. I'm doing my best to solve this problem. I'm sure once I get a few components to work it becomes more of a cookie cutter process.

Here's an example of a problem I'm trying to solve using the Object Model. If anyone has a code sample that does some or all of these simple steps from end-to-end that would be an extreme help! It *seems* that this should be so simple!

In a nutshell:

I'm using C# and the SSIS object modle to try to create a single package that at runtime can look up a table and list of columns from a source SQL Server. With this table and column list in hand, I simply want to move the table's source data in its identical format as quickly as possible to another destination SQL Server into an identical table.

The reason I'm using the C# object model approach is so that I can build one package/application that can be used to move data for many different tables, determined at runtime, without having to have multiple packages.

In other words, if I have 300 tables to move data from SQL Server A to SQL Server B, I want one package, not 300. Earlier posts in this forum said to accomplish this requires C# and the object model. Reference: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=112283&SiteID=1

In the final product there will be one transformation, but I'd be happy right now just to get all the column outputs and inputs working correctly.

So far I'm having success with:

- Creating the initial package

- Creating the MainPipe

- Creating source and target OLE-DB Connection Managers

- Setting up a source component (see issues below)

- Doing an Instantiate and ProvideComponentProperties

- Associating the source component with the source connection manager

- Using SetComponentProperties to set the SQL statement (see issues below)

- Connecting to the source and running reinitializemetadata

- Repeating the above for the destination component

- Setting up a path between the two

Here are the issues that I'm pretty sure are screwing me up. I'm having a hard time with:

1. With the source component, all I want is "select col1, col2 from foo". I'm pretty sure that I'm missing some important steps to tell SSIS the specific list of columns that I want to have in my source component output. I am running the reinitializemetadata step, but I don't *think* this is enough by itself. I'm pretty sure there are some commands related to IDTSOutput90 but I'm having a hard time figuring out how to accomplish this step by step. I haven't been able to follow BOL to put this together.

2. I'm also missing the corresponding IDTSInput90 techniques to identify the input columns coming into my destination component. I looked at

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/dtsref9/html/ef258c93-446f-44e6-b040-8164315b58ee.htm

as a reference, but this didn't seem to help (translation: too complex for me to understand without some assistance, and pilot error.) The idea of having this as a dynamic list would be extremely helpful for my particular problem.

3. OK, once my data shows up at my destination component if possible I need to UPSERT the data. A code example on this would be helpful ... how to set up the SQL statements for the SetComponentProperty.

4. Finally, I've been trying to figure out the right combination of AccessMode integer values that match SQL statements and such used by SetComponentProperty. For example:

mySrcDTInstance.SetComponentProperty("CommandTimeout", 0);

mySrcDTInstance.SetComponentProperty("OpenRowset", "[dbo].[test]");

mySrcDTInstance.SetComponentProperty("AccessMode", 0);

I've run every search I could think of and have posted the question before: What are the proper combinations of AccessMode integer values and SQL statments, OpenRowset, etc.

Any samples or suggestions or doc references I can delve into would be a GREAT help!!!!!

DB

|||

Hi Doug B,

We are facing the same problem here. We need to move 200 tables with Data Flow Task. I was wondering if you figure out a solution since the last posting.

Mathieu

|||

Doug B wrote:

I'm using C# and the SSIS object modle to try to create a single package that at runtime can look up a table and list of columns from a source SQL Server. With this table and column list in hand, I simply want to move the table's source data in its identical format as quickly as possible to another destination SQL Server into an identical table.

I haven't looked at your post in detail but I think I should pick you up one one point here. You say you want to "create a single package that at runtime can look up a table and list of columns from <somewhere>". I think your approach here is slightly awry. Yes, you need to write dotnet code to do this. But in your sentance here it sounds as though you want to SSIS to be a host for that dotnet code - and I think that is wrong. Sure, write a dotnet app that uses the SSIS object model to create a package based on some defined metadata - but there's no need to run that code actually within a SSIS package. Why not jsut run it from the command-line?

Remember that it is not possible for a SSIS package to change itself using the object model. You could do this in DTS, but not in SSIS.

I hope the subtle distinction is clear here.

-Jamie

|||

Doug B wrote:

3. OK, once my data shows up at my destination component if possible I need to UPSERT the data. A code example on this would be helpful ... how to set up the SQL statements for the SetComponentProperty.

That's simply not possible just in a destination component. If you want to do an upsert then you will need to compare the pipeline data with the destination and that needs to happen upstream of the destination component. I've talked about this technique more here:

Checking if a row exists and if it does, has it changed?
http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Checking-if-a-row-exists-and-if-it-does_2C00_-has-it-changed.aspx

-Jamie

|||

Doug B wrote:

1. With the source component, all I want is "select col1, col2 from foo". I'm pretty sure that I'm missing some important steps to tell SSIS the specific list of columns that I want to have in my source component output. I am running the reinitializemetadata step, but I don't *think* this is enough by itself. I'm pretty sure there are some commands related to IDTSOutput90 but I'm having a hard time figuring out how to accomplish this step by step. I haven't been able to follow BOL to put this together.

I vaguely remember trying this myself once. You can't just give the source adapter a SQL statement and let it work out the metadata itself. Although this is what it appears as if the Source Adapters do in the SSIS Designer UI, it isn't actually the case. The adapters still have to interogate the source to find the metadata - you have to do the same (Unless you have the column data types in your metadata store).

-Jamie

|||

Hi Mathieu ...

Unfortunately we were not able to get this to work. Perhaps someone with more C# experience would have had better luck.

All the best,

Doug

|||

Hi everyone,

I've been working on this issue since one week.

Finally, I built a c# class that generate a package with the 200 Dataflow Task (source, lookup, destination) with the SSIS object API.

It is not that complicated except that the api is quite undocumented...

My program is doing the following task :

#1 - From a table, I get the table source (TableName) and the destination (tableName) with the load order

#2 - Using Package API, I create a package from a template

#3 - With the load order, I create a DataFlow Task using API that contain :

A) Source

B) LookUp

C) Destination

We will save some precious time on refactoring aspect using this class...

Jamie : Nice website... I used it since I'm working with SSIS...I get some practical information...Keep on good work...

Doug_b : About IDTSOutput90, this is an example for a simple DataFlow Task.

I hope It can help you

#region Map MetaData
public void MetaDataMapping(IDTSComponentMetaData90 dstMetaData, IDTSDesigntimeComponent90 dstComp)
{

IDTSInput90 destinationInput = dstMetaData.InputCollection[0];
int destinationInputID = destinationInput.ID;
IDTSVirtualInput90 destinationVirtualInput = destinationInput.GetVirtualInput();
foreach (IDTSVirtualInputColumn90 virtualInputColumn in destinationVirtualInput.VirtualInputColumnCollection)
{

// This will create an input column on the component.
dstComp.SetUsageType(destinationInputID,destinationVirtualInput,virtualInputColumn.LineageID,DTSUsageType.UT_READONLY);
// Get input column.
IDTSInputColumn90 inputColumn = destinationInput.InputColumnCollection.GetInputColumnByLineageID(virtualInputColumn.LineageID);
// Getting the corresponding external column.
// Ex : We will use the column name as the basis for matching data flow columns to external columns.
IDTSExternalMetadataColumn90 externalColumn = destinationInput.ExternalMetadataColumnCollection[virtualInputColumn.Name];
// Tell the component how to map.
dstComp.MapInputColumn(destinationInputID,inputColumn.ID,externalColumn.ID);
}
}
#endregion

|||

Hi,

I tried to create SSIS package programatically,I successfully added OLE DB source & destination but

got problem in adding lookup transformation.

How can I get reference table column output.& how can I set join column property for specific column

plz help.

Is anyone using C# and the SSIS Objects to Develop/Modify Packages?

Anyone out there developing or modifying packages w/ C# and the SSIS objects that I can compare notes with?

Thanks!

Done a bit, loading and poking around and some creating directly in code.|||

Hi Darren ...

I've taken a look around SQLIS. Very helpful indeed!

Do you have any examples of something as simple as the following, using C# and the object model:

Source: SQL Server A, Table Foo

Transformations: None

Target SQL Server B, Table FooTwo

The real trick with the above is that at runtime I won't know what the tables are. I need to work w/ a SQL String and the re-initializemetadata stuff.

Looking for any help here.

Thanks!

|||

I would probably rebuild the package each time, and Books Online covers this quite well I think.

Start with ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/dtsref9/html/0ca03712-a82e-4aa7-949b-f869a8936ddf.htm, then move onto the related Data Flow section.

The Data Flow sample includes setting the SQL and calling RMD, see "Adding and Configuring a Component".

|||

Thank you very much for the suggestions!

I've looked over these items in the past and to be honest I think they are a bit weak in terms of examples, etc. My experience has been that some of the docs on this go into great detail, but they don't give you "the big picture." I got a copy of Professional SQL Server 2005 Integration Services (wrox) and this has been a help, but I'm still looking for more examples.

Any other suggestions?

All the best,

DB

|||

Chapters 14 and 15 of that book is where you want to head. I've already been through Chapter 14 and it was a really great tutorial.

Darren wrote chapter 15 and his partner Allan Mitchell wrote chapter 14 and they're probably too modest to sing their own praises ...so I'll do it for them.

-Jamie

|||

Thank you for the plug, but those chapters are more about building components, not components. Actually if you do understand how to build components, and the workings of adding and removing columns which they cover, then it will help your understanding for when building packages as well, I know it does for me. You do similar things in your component as when building packages, such as working on the managed interface wrapper, and selecting columns.

I don't know of anymore examples, I have always managed to do what I need quite effectively on what is above. You have the basics of building packages, so now everything else is just the nuances for the task or component type, which is more about understanding that component as opposed to general building package knowledge. Perhaps you could post individual problems as you get them and we'll see if we can help.

|||

Thanks to both of you for the supportive words. I'm doing my best to solve this problem. I'm sure once I get a few components to work it becomes more of a cookie cutter process.

Here's an example of a problem I'm trying to solve using the Object Model. If anyone has a code sample that does some or all of these simple steps from end-to-end that would be an extreme help! It *seems* that this should be so simple!

In a nutshell:

I'm using C# and the SSIS object modle to try to create a single package that at runtime can look up a table and list of columns from a source SQL Server. With this table and column list in hand, I simply want to move the table's source data in its identical format as quickly as possible to another destination SQL Server into an identical table.

The reason I'm using the C# object model approach is so that I can build one package/application that can be used to move data for many different tables, determined at runtime, without having to have multiple packages.

In other words, if I have 300 tables to move data from SQL Server A to SQL Server B, I want one package, not 300. Earlier posts in this forum said to accomplish this requires C# and the object model. Reference: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=112283&SiteID=1

In the final product there will be one transformation, but I'd be happy right now just to get all the column outputs and inputs working correctly.

So far I'm having success with:

- Creating the initial package

- Creating the MainPipe

- Creating source and target OLE-DB Connection Managers

- Setting up a source component (see issues below)

- Doing an Instantiate and ProvideComponentProperties

- Associating the source component with the source connection manager

- Using SetComponentProperties to set the SQL statement (see issues below)

- Connecting to the source and running reinitializemetadata

- Repeating the above for the destination component

- Setting up a path between the two

Here are the issues that I'm pretty sure are screwing me up. I'm having a hard time with:

1. With the source component, all I want is "select col1, col2 from foo". I'm pretty sure that I'm missing some important steps to tell SSIS the specific list of columns that I want to have in my source component output. I am running the reinitializemetadata step, but I don't *think* this is enough by itself. I'm pretty sure there are some commands related to IDTSOutput90 but I'm having a hard time figuring out how to accomplish this step by step. I haven't been able to follow BOL to put this together.

2. I'm also missing the corresponding IDTSInput90 techniques to identify the input columns coming into my destination component. I looked at

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/dtsref9/html/ef258c93-446f-44e6-b040-8164315b58ee.htm

as a reference, but this didn't seem to help (translation: too complex for me to understand without some assistance, and pilot error.) The idea of having this as a dynamic list would be extremely helpful for my particular problem.

3. OK, once my data shows up at my destination component if possible I need to UPSERT the data. A code example on this would be helpful ... how to set up the SQL statements for the SetComponentProperty.

4. Finally, I've been trying to figure out the right combination of AccessMode integer values that match SQL statements and such used by SetComponentProperty. For example:

mySrcDTInstance.SetComponentProperty("CommandTimeout", 0);

mySrcDTInstance.SetComponentProperty("OpenRowset", "[dbo].[test]");

mySrcDTInstance.SetComponentProperty("AccessMode", 0);

I've run every search I could think of and have posted the question before: What are the proper combinations of AccessMode integer values and SQL statments, OpenRowset, etc.

Any samples or suggestions or doc references I can delve into would be a GREAT help!!!!!

DB

|||

Hi Doug B,

We are facing the same problem here. We need to move 200 tables with Data Flow Task. I was wondering if you figure out a solution since the last posting.

Mathieu

|||

Doug B wrote:

I'm using C# and the SSIS object modle to try to create a single package that at runtime can look up a table and list of columns from a source SQL Server. With this table and column list in hand, I simply want to move the table's source data in its identical format as quickly as possible to another destination SQL Server into an identical table.

I haven't looked at your post in detail but I think I should pick you up one one point here. You say you want to "create a single package that at runtime can look up a table and list of columns from <somewhere>". I think your approach here is slightly awry. Yes, you need to write dotnet code to do this. But in your sentance here it sounds as though you want to SSIS to be a host for that dotnet code - and I think that is wrong. Sure, write a dotnet app that uses the SSIS object model to create a package based on some defined metadata - but there's no need to run that code actually within a SSIS package. Why not jsut run it from the command-line?

Remember that it is not possible for a SSIS package to change itself using the object model. You could do this in DTS, but not in SSIS.

I hope the subtle distinction is clear here.

-Jamie

|||

Doug B wrote:

3. OK, once my data shows up at my destination component if possible I need to UPSERT the data. A code example on this would be helpful ... how to set up the SQL statements for the SetComponentProperty.

That's simply not possible just in a destination component. If you want to do an upsert then you will need to compare the pipeline data with the destination and that needs to happen upstream of the destination component. I've talked about this technique more here:

Checking if a row exists and if it does, has it changed?
http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Checking-if-a-row-exists-and-if-it-does_2C00_-has-it-changed.aspx

-Jamie

|||

Doug B wrote:

1. With the source component, all I want is "select col1, col2 from foo". I'm pretty sure that I'm missing some important steps to tell SSIS the specific list of columns that I want to have in my source component output. I am running the reinitializemetadata step, but I don't *think* this is enough by itself. I'm pretty sure there are some commands related to IDTSOutput90 but I'm having a hard time figuring out how to accomplish this step by step. I haven't been able to follow BOL to put this together.

I vaguely remember trying this myself once. You can't just give the source adapter a SQL statement and let it work out the metadata itself. Although this is what it appears as if the Source Adapters do in the SSIS Designer UI, it isn't actually the case. The adapters still have to interogate the source to find the metadata - you have to do the same (Unless you have the column data types in your metadata store).

-Jamie

|||

Hi Mathieu ...

Unfortunately we were not able to get this to work. Perhaps someone with more C# experience would have had better luck.

All the best,

Doug

|||

Hi everyone,

I've been working on this issue since one week.

Finally, I built a c# class that generate a package with the 200 Dataflow Task (source, lookup, destination) with the SSIS object API.

It is not that complicated except that the api is quite undocumented...

My program is doing the following task :

#1 - From a table, I get the table source (TableName) and the destination (tableName) with the load order

#2 - Using Package API, I create a package from a template

#3 - With the load order, I create a DataFlow Task using API that contain :

A) Source

B) LookUp

C) Destination

We will save some precious time on refactoring aspect using this class...

Jamie : Nice website... I used it since I'm working with SSIS...I get some practical information...Keep on good work...

Doug_b : About IDTSOutput90, this is an example for a simple DataFlow Task.

I hope It can help you

#region Map MetaData
public void MetaDataMapping(IDTSComponentMetaData90 dstMetaData, IDTSDesigntimeComponent90 dstComp)
{

IDTSInput90 destinationInput = dstMetaData.InputCollection[0];
int destinationInputID = destinationInput.ID;
IDTSVirtualInput90 destinationVirtualInput = destinationInput.GetVirtualInput();
foreach (IDTSVirtualInputColumn90 virtualInputColumn in destinationVirtualInput.VirtualInputColumnCollection)
{

// This will create an input column on the component.
dstComp.SetUsageType(destinationInputID,destinationVirtualInput,virtualInputColumn.LineageID,DTSUsageType.UT_READONLY);
// Get input column.
IDTSInputColumn90 inputColumn = destinationInput.InputColumnCollection.GetInputColumnByLineageID(virtualInputColumn.LineageID);
// Getting the corresponding external column.
// Ex : We will use the column name as the basis for matching data flow columns to external columns.
IDTSExternalMetadataColumn90 externalColumn = destinationInput.ExternalMetadataColumnCollection[virtualInputColumn.Name];
// Tell the component how to map.
dstComp.MapInputColumn(destinationInputID,inputColumn.ID,externalColumn.ID);
}
}
#endregion

|||

Hi,

I tried to create SSIS package programatically,I successfully added OLE DB source & destination but

got problem in adding lookup transformation.

How can I get reference table column output.& how can I set join column property for specific column

plz help.