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.
... Try your own manual migration and come up with a white
... Read below link