copy database: package

Showing posts with label package. Show all posts

Friday, March 30, 2012

How to create slideshow of a report?

Hi Expert!
I got a report showing the list of current sale volume by each office
branch. We got 20 offices. Is there a slideshow software package or ASP.NET
control out there to display my report as what PowerPoint does. I would like
to be able to pass in the report parameters.
Thanks in advance for any clue
ThanhOn Feb 26, 3:11 pm, Thanh Nguyen
<ThanhNgu...@.discussions.microsoft.com> wrote:
> Hi Expert!
> I got a report showing the list of current sale volume by each office
> branch. We got 20 offices. Is there a slideshow software package or ASP.NET
> control out there to display my report as what PowerPoint does. I would like
> to be able to pass in the report parameters.
> Thanks in advance for any clue
> Thanh
One option would be to use SnagIt to record the parameter passing,
report execution, etc; and then play it back as video or use
screenshot frames. Hope this is helpful.
Regards,
Enrique Martinez
Sr. SQL Server Developer|||Thanks for your reply.
I would like to my slideshow to be real-time as much as possible, that means
once it reaches the end of the slideshow, it should be able to re-query again
and display the new sale volume in the next 1st slide. You can think of this
like stocks banner or something like that.
"EMartinez" wrote:
> On Feb 26, 3:11 pm, Thanh Nguyen
> <ThanhNgu...@.discussions.microsoft.com> wrote:
> > Hi Expert!
> >
> > I got a report showing the list of current sale volume by each office
> > branch. We got 20 offices. Is there a slideshow software package or ASP.NET
> > control out there to display my report as what PowerPoint does. I would like
> > to be able to pass in the report parameters.
> >
> > Thanks in advance for any clue
> > Thanh
>
> One option would be to use SnagIt to record the parameter passing,
> report execution, etc; and then play it back as video or use
> screenshot frames. Hope this is helpful.
> Regards,
> Enrique Martinez
> Sr. SQL Server Developer
>|||You can burn your powerpoint slideshow to dvd,
by this way you can a real-time and interactive dvd which can control each
silde play with a remote.
Play the DVD with a dvd player.
Burn the powerpoint slide show with Wondershare PPT2DVD
http://www.ppt-to-dvd.com/download.php?sid=4
--
PowerPoint software
http://www.ppt-to-dvd.com
PowerPoit free templates
http://www.ppt-to-dvd.com/free-templates.php?sid=4
"Thanh Nguyen" wrote:
> Thanks for your reply.
> I would like to my slideshow to be real-time as much as possible, that means
> once it reaches the end of the slideshow, it should be able to re-query again
> and display the new sale volume in the next 1st slide. You can think of this
> like stocks banner or something like that.
> "EMartinez" wrote:
> > On Feb 26, 3:11 pm, Thanh Nguyen
> > <ThanhNgu...@.discussions.microsoft.com> wrote:
> > > Hi Expert!
> > >
> > > I got a report showing the list of current sale volume by each office
> > > branch. We got 20 offices. Is there a slideshow software package or ASP.NET
> > > control out there to display my report as what PowerPoint does. I would like
> > > to be able to pass in the report parameters.
> > >
> > > Thanks in advance for any clue
> > > Thanh
> >
> >
> > One option would be to use SnagIt to record the parameter passing,
> > report execution, etc; and then play it back as video or use
> > screenshot frames. Hope this is helpful.
> >
> > Regards,
> >
> > Enrique Martinez
> > Sr. SQL Server Developer
> >
> >sql

Wednesday, March 28, 2012

how to create package

i need to transfer the data from allbase (old database) to sql server 2005 .

moreover the structure is different say in allbase we may have 10 tables but now we want more tables .and some tables may have more fields than the previous table's fields for ex: productdigit (previous table name) have 2 fields and present table productline have 4 fields (while using export/import wizard those are set to null but we dont want to set null).

and i dont have knowledge on ssis now i am learning it. but i could not understand how to use those items in toolbox and how to specify datasource for odbc and all.

and please help me to create custom component if needed.

thanks in advance

B L,

I would reccomend you to go to books on line; there are some tutorials that will help you on getting started. Getting some books will help you as well. Then you can come back with specific questions/issues.

http://msdn2.microsoft.com/en-us/library/bb418492.aspx

|||

thanks for immediate response .

we have different data flow sources in toolbox but there is nothing for odbc .

If i want to connect to odbc what item should i use?

and for transferring data from source to destination which item i should use in in data flow transformation.

do i need to write any script if so can i write c# instead of vb.net wt property i need to set

|||

Few things:

Toolbox is context sensitive; it will show a diffrent set of tools depending on where you are: control flow or data flow To pump data from an ODBC source: Create a connection manager that points to your ODBC source (DNSmust be define in the machine running the package) Create a data flow task in in the control flow In Data flow task add a data reader that uses the connection manager you created add a destination component COnnect the source component to the destination component.|||

thanks for valuable suggestion .

for transferring data from old database to new one when the structure is different means

--> the number of tables / name of tables is different.

--> the structure of table is also different say the old table contains n number of columns and the new table may contain either n+ or n- number of columns and some columns in new table might not be there in old table .can i have new columns as primary?

can u guide me in that way and can you tell me which items i should use in data flow task.

|||

hello,

i have data in old database which contains many tables and i want to retrive data from two tables and insert that data into one table in the destination by SSIS package.

for example:

input output

- --

userinfo ( table ) userdetails ( table )

user_id --> id

userdata ( table2 )

name --> name

city --> city

can i do like that? please suggest me to do so .

|||

There are several ways. My first approach would be to write a query (IF the 2 source tables are in the same DB); then use that query in the source component. That way the dataflow will have all required columns right out of the source component.

Another apporach is to use 2 source componnets and then a Merge Join transformation to join the 2 data sets. This approach requires the 2 data sets to be order before hiting the merge join. Performance can also suffer with this approach since the Join takes place inside SSIS rather than in the DB engine.

|||

B L Rao wrote:

do i need to write any script if so can i write c# instead of vb.net wt property i need to set

The script task and script component only support VB.NET, not C#.

You can build your own custom SSIS component DLLs using any .NET language, but this adds a LOT of complexity.

|||

hi Rafel Salas ,

thank you for your reply by which i got some idea ,

yes we have those tables in same DB.

but still some confusion in that first approach that is where to write that query and if possible what query i can write .

so plz give me clear idea .

and second approach is clear to me .

thanks and regards

B L Rao.

|||Just write the query that gives the desired output and then you can place it directly in the OLE DB source component by changing the data access mode to "SQL Command". Another option is to place the query in a SSIS string variable and then use data access mode "SQL Command from variable"|||

hi,

The approaches are best suitable in the scenario where we can make join between two tables. Means when we have one common column on the basis of which we can create join.

But what should we do if we want to fetch data of column1, column2 from table 1 and data of col5 from table 2 without making join.

For example

table 1 has

column1

column2

column3

table 2

col1

col2

col3

col4

col5

I have tried Union All task, and Merge task but it returned 2 rows like following

null,null,col5

column1,column2,null

but output through SSIS in destination table 3 should be combination of three columns in one row like this :

column1,column2,col5 .

can you please help as soon as possible?

if possible can u please send a small package or query for this?

thanks & Regards,

Hariax

|||Try creating a dummy column (like a simple sequential row number) in each table to join on and use the Merge Join.|||

Hi,

Please help me, I am getting the 'DTS_E_UNABLETORETRIEVEMETADATA' error while I am performing bellow mentioned steps.

1. Created 'Execute Sql Task'

2. In the editor of this task, General Page ->Sql statement, I wrote create table query.

3. I tried to execute the package, and got following error.

ADDITIONAL INFORMATION:

Error at Data Flow Task 1 [SQL Server Destination [376]]: The metadata for "[dbo].[temptable]" cannot be retrieved. Make sure the object name is correct and the object exists.

Error at Data Flow Task 1 [DTS.Pipeline]: "component "SQL Server Destination" (376)" failed validation and returned validation status "VS_ISBROKEN".

Error at Data Flow Task 1 [DTS.Pipeline]: One or more component failed validation.

Error at Data Flow Task 1: There were errors during task validation.

(Microsoft.DataTransformationServices.VsIntegration)

BUTTONS:

If anybody has the solution of this then please let me know.

same query is running successfully from sql server management studio.

Thanks & Regards,

Hariax J. Thoria

|||If you are creating the table for the destination in the same package, make sure the DelayValidation property on the SQL Server Destination is set to TRUE.

How to create or save files in a different domain

Hey,

I have an SSIS package that creates a new file and saves results into that file. However, the requirement has it that I need to create this file on a server residing in a different domain. How can I achieve this in an SSIS package when it is scheduled as a job?

Thanks,

Aravind

When using SQLAgent, you should be aware of permission-related issues like the use of proxy accounts that may come into play. SSIS doesn't have special handling for different domains. For example, if you are using the flat file destination adapter to write data, it will just attempt to write to the file without recognizing where the file is residing. If you are having trouble writing directly to a file in a different domain, it might be easier if you create and write to a file locally using IS (feasibility depending on the size of file) and then copy it over using another mechanism that's proven to work across domains.|||

The obvious method would be to setup a trust between the domains, and just grant permission in the normal way. If that is not an option for you, then the only other method I have used is to manipulate files accross domains is NET USE the old DOS command. You can connect to a share and supply a username and password.

Friday, March 23, 2012

How to create an SSIS package correctly by programming?

Recently I try to create a package completely by c# code,not the ETL tool.
The package is designed to transfer data from source table to destination table.
And I wrote the following codes by referring to the msdn library,but unfortunately,it doesn't work.

//this is my code
///////////////////////////////////////////////////////////////////////
Package package = new Package();
MainPipe dataFlow = ((TaskHost)package.Executables.Add("DTS.Pipeline.1")).InnerObject as MainPipe;

//add connectionmanagers to package.
ConnectionManager srcCM = package.Connections.Add("OLEDB");
srcCM.Name = "OLEDBSource";
srcCM.ConnectionString = "Data Source=srcServerName;Initial Catalog=srcDBName;Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Auto Translate=False;";

ConnectionManager destCM = package.Connections.Add("OLEDB");
destCM.Name = "OLEDBDestination";
destCM.ConnectionString = "Data Source=destServerName;Initial Catalog=destDBName;Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Auto Translate=False;";

//create the source component

IDTSComponentMetaData90 srcComponent = dataFlow.ComponentMetaDataCollection.New();
srcComponent.Name = "ComponetOLEDBSource";
srcComponent.ComponentClassID = "DTSAdapter.OLEDBSource.1";

// Get the design time instance of the component and initialize the component
CManagedComponentWrapper srcDesignTime = srcComponent.Instantiate();
srcDesignTime.ProvideComponentProperties();

// Specify the connection manager.
if (srcComponent.RuntimeConnectionCollection.Count > 0)
{
srcComponent.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(package.Connections[0]);
}
// Set the custom properties.
srcDesignTime.SetComponentProperty("AccessMode", 0);
srcDesignTime.SetComponentProperty("OpenRowset", "[dbo].[srcTableName]");

// Reinitialize the metadata.
srcDesignTime.AcquireConnections(null);
srcDesignTime.ReinitializeMetaData();
srcDesignTime.ReleaseConnections();

// Create the destination component.
IDTSComponentMetaData90 destComponent = dataFlow.ComponentMetaDataCollection.New();
destComponent.Name = "ComponetOLEDBDestination";
destComponent.ComponentClassID = "DTSAdapter.OLEDBDestination.1";

CManagedComponentWrapper destDesignTime = destComponent.Instantiate();
destDesignTime.ProvideComponentProperties();
// Specify the connection manager.
if (destComponent.RuntimeConnectionCollection.Count > 0)
{
destComponent.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(package.Connections[1]);
}

// Set the custom properties.
destDesignTime.SetComponentProperty("AccessMode", 0);
destDesignTime.SetComponentProperty("OpenRowset", "[dbo].[destTableName]");

// Create the path.
IDTSPath90 path = dataFlow.PathCollection.New();
path.AttachPathAndPropagateNotifications(srcComponent.OutputCollection[0], destComponent.InputCollection[0]);

// Iterate through the inputs of the component.
foreach (IDTSInput90 input in destComponent.InputCollection)
{
// Get the virtual input column collection for the input.
IDTSVirtualInput90 vInput = input.GetVirtualInput();
// Iterate through the virtual column collection.
foreach (IDTSVirtualInputColumn90 vColumn in vInput.VirtualInputColumnCollection)
{
// Call the SetUsageType method of the design time instance of the component.
destDesignTime.SetUsageType(input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY);
}
}

DataFlowEvent dataFlowEvent = new DataFlowEvent();
package.Validate(null, null, dataFlowEvent, null);
package.Execute(null, null, dataFlowEvent, null, null);

//DataFlowEvent is derived from class DefaultEvent
///////////////////////////////////////////////////////////////////////////

and this is the error message in console

An Error occurs:
errorCode:-1073446884
subComponent:DTS.Pipeline
description:The "runtime connection "OleDbConnection" (10)" in the connection manager collection, Connections, of "component "OLE DB Source" (1)" does not have a value for the ID property. Verify that the ConnectionManagerID property of the runtime connection object has been set for the component.

It seems that the ConnectionManagerID is not assigned,but as I know it is read-only.
Or maybe there is something wrong with my code.
Is there anybody know why?
Thanks.

The error is telling you exactly what your problem is. The connection manager ID in the runtime connection isn't set. You need to set it. Your knowledge about ConnectionManagerID in IDTSRuntimeConnection90 is not accurate. It is not read-only.

if (srcComponent.RuntimeConnectionCollection.Count > 0)
{
==> srcComponent.RuntimeConnectionCollection[0].ConnectionManagerID = srcCM.ID
srcComponent.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(srcCM);
}

HTH,
Matt|||

Thank you Matt.
But there still be an error during the validation.

An Error occurs:
errorCode:-1073450901
subComponent:DTS.Pipeline
description:"component "OLE DB Destination" (43)" failed validation and returned validation status "VS_NEEDSNEWMETADATA".

In order to fix this bug,I connect to the destination and get the metadata before
// Iterate through the inputs of the component.
,so I modify code as:

destDesignTime.AcquireConnections(null);
destDesignTime.ReinitializeMetaData();
destDesignTime.ReleaseConnections();
(I think this is not necessary)
To my disappointment,it still doesn't work.

Would you please check my code again.It is my first time to do such a thing,I am not very familiar with SSIS programming.

Thanks again.

|||Let me preface with the disclaimer that I'm speculating here.

According to the API documentation, the integer ID property of an IDTSRuntimeConnection90 object is read/write (although the ID of a ConnectionManager is not). This should be set at creation when the data flow's AutoGenerateIDForNewObjects is true (which it is by default), but must be set by the developer otherwise.

Perhaps the default value of AutoGenerateIDForNewObjects doesn't apply when one is building a package programmatically? Perhaps it's only the IDE that sets its default value to true?

In any case, it appears (as Matt first replied) that the error message you received is probably accurate and that, as it states, you need to assign IDs to your connections.|||Thanks DouglasL.
I have thoroughly resolved this problem by adding following codes:

am doing something like what you do. But my metadata collection for the

input doesnt seem to get populated. Althought I seem to be doing

exactly like how you do. Here is my code, if you do find anything that

seems to be wrong please let me know.
. Package package = new Package();

MainPipe dataFlow =

((TaskHost)package.Executables.Add("DTS.Pipeline")).InnerObject as

MainPipe;

Add an OLEDB connection manager that is used by the component to the

package.

ConnectionManager cm = package.Connections.Add("OLEDB");

cm.Name = "OLEDB ConnectionManager";

cm.ConnectionString = "Data Source=XXXX;Initial

Catalog=cdfg05;Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist

Security Info=False;Auto Translate=False;";

cm = package.Connections.Add("FLATFILE");

cm.Name = "FLATFILE Connection Manager";

cm.ConnectionString = "C:\\Documents and

Settings\\sdssfdsf\\Desktop\\Sdfdfdf_Test.csv";

IDTSComponentMetaData90 component =

dataFlow.ComponentMetaDataCollection.New();

component.Name = "OLEDBSource";

component.ComponentClassID = "DTSAdapter.OleDbSource.1";

You can also use the CLSID of the component instead of the PROGID.

//component.ComponentClassID = "{2C0A8BE5-1EDC-4353-A0EF-B778599C65A0}";

// Get the design time instance of the component.

CManagedComponentWrapper instance = component.Instantiate();

// Initialize the component

instance.ProvideComponentProperties();

// Specify the connection manager.

if (component.RuntimeConnectionCollection.Count > 0)

{

component.RuntimeConnectionCollection[0].ConnectionManager =

DtsConvert.ToConnectionManager90(package.Connections[1]);

component.RuntimeConnectionCollection[0].ConnectionManagerID =

package.Connections[1].ID;

}

// Set the custom properties.

instance.SetComponentProperty("AccessMode", 2);

instance.SetComponentProperty("SqlCommand", "Select * from t_ggg where

Zip='01757'");

// Reinitialize the metadata.

instance.AcquireConnections(null);

instance.ReinitializeMetaData();

instance.ReleaseConnections();

// Create the destination component.

IDTSComponentMetaData90 destination =

dataFlow.ComponentMetaDataCollection.New();

destination.Name = "FLATFILE";

destination.ComponentClassID = "DTSAdapter.FlatFileDestination";

CManagedComponentWrapper destDesignTime = destination.Instantiate();

destDesignTime.ProvideComponentProperties();

// Specify the connection manager.

if (destination.RuntimeConnectionCollection.Count > 0)

{

destination.RuntimeConnectionCollection[0].ConnectionManager =

DtsConvert.ToConnectionManager90(package.Connections[0]);

destination.RuntimeConnectionCollection[0].ConnectionManagerID =

package.Connections[0].ID;

}

destDesignTime.AcquireConnections(null);

destDesignTime.ReinitializeMetaData();

destDesignTime.ReleaseConnections();

IDTSPath90 path = dataFlow.PathCollection.New();

path.AttachPathAndPropagateNotifications(component.OutputCollection[0],

destination.InputCollection[0]);

// Iterate through the inputs of the component.

foreach (IDTSInput90 input in destination.InputCollection)

{

// Get the virtual input column collection for the input.

IDTSVirtualInput90 vInput = input.GetVirtualInput();

// Iterate through the virtual column collection.

foreach (IDTSVirtualInputColumn90 vColumn in

vInput.VirtualInputColumnCollection)

{

// Call the SetUsageType method of the design time instance of the

component.

destDesignTime.SetUsageType(input.ID, vInput, vColumn.LineageID,

DTSUsageType.UT_READONLY);

}

//map external metadata to the inputcolumn

//int index = 0;

foreach (IDTSInputColumn90 inputColumn in input.InputColumnCollection)

{

IDTSExternalMetadataColumn90 exMetaColumn =

input.ExternalMetadataColumnCollection.New();

exMetaColumn.CodePage = inputColumn.CodePage;

exMetaColumn.DataType = inputColumn.DataType;

exMetaColumn.Length = inputColumn.Length;

exMetaColumn.Name = inputColumn.Name;

inputColumn.ExternalMetadataColumnID = exMetaColumn.ID;

destDesignTime.MapInputColumn(input.ID, inputColumn.ID,

exMetaColumn.ID);

}

DTSExecResult result = package.Execute();

foreach (DtsError pkgerror in package.Errors)

{

string err = pkgerror.Description;
}|||

Do you get any errors? From the code it appears that you are trying to set the flatfile connection manager to be used by the oledb source and the oledb connection manager to be used by the flat file destination since you added the oledb connection manager 1st that would be index 0 and the flat file second so that would be index 1.

Matt

//- Add a OLEDB connection SOURCE
ConnectionManager cm = package.Connections.Add("OLEDB");
cm.Name = "OLEDB ConnectionManager";
cm.ConnectionString = "Data Source=PAGCOMPUTER;Initial Catalog=DBProva;Provider=SQLOLEDB;Integrated Security=SSPI;"; //Persist Security Info=False;Auto Translate=False;";

//- Add a OLEDB connection DESTINATION
ConnectionManager cm2 = package.Connections.Add("OLEDB");
cm2.Name = "OLEDB ConnectionManager2";
cm2.ConnectionString = "Data Source=PAGCOMPUTER;Initial Catalog=DBProva;Provider=SQLOLEDB;Integrated Security=SSPI;"; //Persist Security Info=False;Auto Translate=False;";

//- Create a OLEDB source component
IDTSComponentMetaData90 component = dataFlow.ComponentMetaDataCollection.New();
component.Name = "OLEDBSource";
component.ComponentClassID = "DTSAdapter.OleDbSource.1";

// Get the design time instance of the component.
CManagedComponentWrapper instance = component.Instantiate();

// Initialize the component
instance.ProvideComponentProperties();

// Specify the connection manager.
if (component.RuntimeConnectionCollection.Count > 0)
{
component.RuntimeConnectionCollection[0].ConnectionManagerID = cm.ID;
component.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(package.Connections[0]);
}
// Set the custom properties.

instance.SetComponentProperty("CommandTimeout", 0);
instance.SetComponentProperty("OpenRowset","[dbo].[FirstName-SurName]");
instance.SetComponentProperty("AlwaysUseDefaultCodePage",false);
instance.SetComponentProperty("AccessMode",0);

// Reinitialize the metadata.
instance.AcquireConnections(null);
instance.ReinitializeMetaData();
instance.ReleaseConnections();

//- Create the DERIVEDCOLUMN component
IDTSComponentMetaData90 derivedColumn = dataFlow.ComponentMetaDataCollection.New();
derivedColumn.ComponentClassID = "DTSTransform.DerivedColumn.1";
derivedColumn.Name = "MyRole";
derivedColumn.Description = "Transformations Role";
derivedColumn.LocaleID = -1;
derivedColumn.UsesDispositions = true;
derivedColumn.ValidateExternalMetadata = true;
derivedColumn.Version = 0;
derivedColumn.PipelineVersion = 0;

CManagedComponentWrapper dcDesignTime = derivedColumn.Instantiate();
dcDesignTime.ProvideComponentProperties();

//- Create the DESTINATION component
IDTSComponentMetaData90 destination = dataFlow.ComponentMetaDataCollection.New();
destination.ComponentClassID = "DTSAdapter.OLEDBDestination.1";
CManagedComponentWrapper destDesignTime = destination.Instantiate();
destDesignTime.ProvideComponentProperties();

if (destination.RuntimeConnectionCollection.Count > 0)
destination.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(package.Connections[1]);

destDesignTime.SetComponentProperty("CommandTimeout", 0);
destDesignTime.SetComponentProperty("OpenRowset", "[dbo].[FirstNameSurName]");
destDesignTime.SetComponentProperty("AlwaysUseDefaultCodePage", false);
destDesignTime.SetComponentProperty("AccessMode", 3);
destDesignTime.SetComponentProperty("FastLoadKeepIdentity", false);
destDesignTime.SetComponentProperty("FastLoadKeepNulls", false);
destDesignTime.SetComponentProperty("FastLoadOptions", "TABLOCK, CHECK_CONSTRAINTS");
destDesignTime.SetComponentProperty("FastLoadMaxInsertCommitSize",0);
// Reinitialize the metadata.
destDesignTime.AcquireConnections(null);
destDesignTime.ReinitializeMetaData();
destDesignTime.ReleaseConnections();

//- Create the PATH:
// * source->derivedColumn
IDTSPath90 path = dataFlow.PathCollection.New();
path.AttachPathAndPropagateNotifications(component.OutputCollection[0], derivedColumn.InputCollection[0]);
// * derivedColumn->destionation
IDTSPath90 path2 = dataFlow.PathCollection.New();
path2.AttachPathAndPropagateNotifications(derivedColumn.OutputCollection[0], destination.InputCollection[0]);

derivedColumn.InputCollection[0].Name = "Derived Column Input";
derivedColumn.InputCollection[0].Description = "Input to the Derived Column Transformation";
derivedColumn.InputCollection[0].HasSideEffects = false;
derivedColumn.InputCollection[0].ExternalMetadataColumnCollection.IsUsed = false;

IDTSVirtualInput90 vInput = derivedColumn.InputCollection[0].GetVirtualInput();

IDTSInputColumn90 FirstName = null;
IDTSInputColumn90 SurName = null;

foreach (IDTSVirtualInputColumn90 vColumn in vInput.VirtualInputColumnCollection)
{
if (vColumn.Name.Contains("FirstName"))
{
IDTSInputColumn90 col = dcDesignTime.SetUsageType(derivedColumn.InputCollection[0].ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY);
FirstName = col;
}
if (vColumn.Name.Contains("SurName"))
{
IDTSInputColumn90 col = dcDesignTime.SetUsageType(derivedColumn.InputCollection[0].ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY);
SurName = col;
}
}

derivedColumn.OutputCollection[0].Name = "Derived Column Output";
derivedColumn.OutputCollection[0].Description = "Default Output of the Derived Column Transformation";
derivedColumn.OutputCollection[0].ExclusionGroup = 1;
derivedColumn.OutputCollection[0].DeleteOutputOnPathDetached = false;
derivedColumn.OutputCollection[0].Dangling = false;
derivedColumn.OutputCollection[0].IsErrorOut = false;
derivedColumn.OutputCollection[0].TruncationRowDisposition = DTSRowDisposition.RD_NotUsed;
derivedColumn.OutputCollection[0].ErrorRowDisposition = DTSRowDisposition.RD_NotUsed;

IDTSOutputColumn90 myCol = derivedColumn.OutputCollection[0].OutputColumnCollection.New();
myCol.Name = "fullname";

myCol.ExternalMetadataColumnID = 0;
myCol.ErrorRowDisposition = DTSRowDisposition.RD_FailComponent;
myCol.TruncationRowDisposition = DTSRowDisposition.RD_FailComponent;

myCol.SetDataTypeProperties(Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_STR,100,0,0,1252);

IDTSCustomProperty90 myProp = myCol.CustomPropertyCollection.New();
myProp.Name = "Expression";
myProp.Value = FirstName.LineageID + SurName.LineageID;

IDTSCustomProperty90 myProp2 = myCol.CustomPropertyCollection.New();
myProp2.Name = "FriendlyExpression";
myProp2.Value = FirstName.Name + SurName.Name;

Microsoft.SqlServer.Dts.Runtime.Application app;
DTSExecResult pkgResults;
app = new Microsoft.SqlServer.Dts.Runtime.Application();

pkgResults = package.Execute();

DTSExecResult result = package.Execute();

foreach (DtsError pkgerror in package.Errors)
{
string err = pkgerror.Description;
Console.WriteLine(err);
}
Console.WriteLine(pkgResults.ToString());
//////////////////////////////////////////////////////////////

and this is the error message in console:

"The custom property "Expression" needs to be of type VT_BSTR.

"component "Derived Column" (22)" failed validation and returned validation status "VS_ISCORRUPT".

One or more component failed validation.

There were errors during task validation.

Failure"

there is sure something wrong in the derivedcolumn propriety.
Is there anybody know where and why?
Thanks.

|||

Try to remove these rows:

IDTSCustomProperty90 myProp = myCol.CustomPropertyCollection.New();
myProp.Name = "Expression";
myProp.Value = FirstName.LineageID + SurName.LineageID;

I don't understand what there are for...

|||Can you convert the value to string and then assign to the property's Value

How to create an SSIS package correctly by programming?

//create the source component

IDTSComponentMetaData90 srcComponent = dataFlow.ComponentMetaDataCollection.New();
srcComponent.Name = "ComponetOLEDBSource";
srcComponent.ComponentClassID = "DTSAdapter.OLEDBSource.1";

// Get the design time instance of the component and initialize the component
CManagedComponentWrapper srcDesignTime = srcComponent.Instantiate();
srcDesignTime.ProvideComponentProperties();

// Reinitialize the metadata.
srcDesignTime.AcquireConnections(null);
srcDesignTime.ReinitializeMetaData();
srcDesignTime.ReleaseConnections();

// Set the custom properties.
destDesignTime.SetComponentProperty("AccessMode", 0);
destDesignTime.SetComponentProperty("OpenRowset", "[dbo].[destTableName]");

// Create the path.
IDTSPath90 path = dataFlow.PathCollection.New();
path.AttachPathAndPropagateNotifications(srcComponent.OutputCollection[0], destComponent.InputCollection[0]);

DataFlowEvent dataFlowEvent = new DataFlowEvent();
package.Validate(null, null, dataFlowEvent, null);
package.Execute(null, null, dataFlowEvent, null, null);

//DataFlowEvent is derived from class DefaultEvent
///////////////////////////////////////////////////////////////////////////

and this is the error message in console

It seems that the ConnectionManagerID is not assigned,but as I know it is read-only.
Or maybe there is something wrong with my code.
Is there anybody know why?
Thanks.

HTH,
Matt|||

Thank you Matt.
But there still be an error during the validation.

An Error occurs:
errorCode:-1073450901
subComponent:DTS.Pipeline
description:"component "OLE DB Destination" (43)" failed validation and returned validation status "VS_NEEDSNEWMETADATA".

In order to fix this bug,I connect to the destination and get the metadata before
// Iterate through the inputs of the component.
,so I modify code as:

destDesignTime.AcquireConnections(null);
destDesignTime.ReinitializeMetaData();
destDesignTime.ReleaseConnections();
(I think this is not necessary)
To my disappointment,it still doesn't work.

Would you please check my code again.It is my first time to do such a thing,I am not very familiar with SSIS programming.

Thanks again.

Perhaps the default value of AutoGenerateIDForNewObjects doesn't apply when one is building a package programmatically? Perhaps it's only the IDE that sets its default value to true?

//map external metadata to the inputcolumn
int index = 0;
foreach (IDTSInputColumn90 inputColumn in input.InputColumnCollection)
{
IDTSExternalMetadataColumn90 exMetaColumn = (IDTSExternalMetadataColumn90)input.ExternalMetadataColumnCollection[index++];
inputColumn.ExternalMetadataColumnID = exMetaColumn.ID;
}
and now my code-generated SSIS works well.|||Could you please give the complete code that works for you. I think I am doing something like what you do. But my metadata collection for the input doesnt seem to get populated. Althought I seem to be doing exactly like how you do. Here is my code, if you do find anything that seems to be wrong please let me know.

. Package package = new Package();
MainPipe dataFlow = ((TaskHost)package.Executables.Add("DTS.Pipeline")).InnerObject as MainPipe;
// Add an OLEDB connection manager that is used by the component to the package.
ConnectionManager cm = package.Connections.Add("OLEDB");
cm.Name = "OLEDB ConnectionManager";
cm.ConnectionString = "Data Source=XXXX;Initial Catalog=cdfg05;Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Auto Translate=False;";

cm = package.Connections.Add("FLATFILE");
cm.Name = "FLATFILE Connection Manager";
cm.ConnectionString = "C:\\Documents and Settings\\sdssfdsf\\Desktop\\Sdfdfdf_Test.csv";

IDTSComponentMetaData90 component = dataFlow.ComponentMetaDataCollection.New();
component.Name = "OLEDBSource";
component.ComponentClassID = "DTSAdapter.OleDbSource.1";
// You can also use the CLSID of the component instead of the PROGID.
//component.ComponentClassID = "{2C0A8BE5-1EDC-4353-A0EF-B778599C65A0}";
// Get the design time instance of the component.
CManagedComponentWrapper instance = component.Instantiate();
// Initialize the component
instance.ProvideComponentProperties();
// Specify the connection manager.
if (component.RuntimeConnectionCollection.Count > 0)
{
component.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(package.Connections[1]);
component.RuntimeConnectionCollection[0].ConnectionManagerID = package.Connections[1].ID;
}
// Set the custom properties.
instance.SetComponentProperty("AccessMode", 2);
instance.SetComponentProperty("SqlCommand", "Select * from t_ggg where Zip='01757'");
// Reinitialize the metadata.
instance.AcquireConnections(null);
instance.ReinitializeMetaData();
instance.ReleaseConnections();
// Create the destination component.
IDTSComponentMetaData90 destination = dataFlow.ComponentMetaDataCollection.New();
destination.Name = "FLATFILE";
destination.ComponentClassID = "DTSAdapter.FlatFileDestination";

CManagedComponentWrapper destDesignTime = destination.Instantiate();
destDesignTime.ProvideComponentProperties();
// Specify the connection manager.
if (destination.RuntimeConnectionCollection.Count > 0)
{
destination.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(package.Connections[0]);
destination.RuntimeConnectionCollection[0].ConnectionManagerID = package.Connections[0].ID;

}
destDesignTime.AcquireConnections(null);
destDesignTime.ReinitializeMetaData();
destDesignTime.ReleaseConnections();

IDTSPath90 path = dataFlow.PathCollection.New();
path.AttachPathAndPropagateNotifications(component.OutputCollection[0], destination.InputCollection[0]);

// Iterate through the inputs of the component.
foreach (IDTSInput90 input in destination.InputCollection)
{
// Get the virtual input column collection for the input.
IDTSVirtualInput90 vInput = input.GetVirtualInput();
// Iterate through the virtual column collection.
foreach (IDTSVirtualInputColumn90 vColumn in vInput.VirtualInputColumnCollection)
{
// Call the SetUsageType method of the design time instance of the component.
destDesignTime.SetUsageType(input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY);

}
//map external metadata to the inputcolumn
//int index = 0;
foreach (IDTSInputColumn90 inputColumn in input.InputColumnCollection)
{
IDTSExternalMetadataColumn90 exMetaColumn = input.ExternalMetadataColumnCollection.New();
exMetaColumn.CodePage = inputColumn.CodePage;
exMetaColumn.DataType = inputColumn.DataType;
exMetaColumn.Length = inputColumn.Length;
exMetaColumn.Name = inputColumn.Name;
inputColumn.ExternalMetadataColumnID = exMetaColumn.ID;
destDesignTime.MapInputColumn(input.ID, inputColumn.ID, exMetaColumn.ID);
}
}

DTSExecResult result = package.Execute();

foreach (DtsError pkgerror in package.Errors)
{
string err = pkgerror.Description;

}|||

Matt

|||Recently I try to create a package completely by c# code:

The package is designed to transfer and trasform a data from OLEDB Source table to OLE DB destination table.

In the package i add:
- a OleDB source componet
- a Derived Column component
- a OleDB Destination componet

I wrote the following codes but it doesn't work.
/////////////////////////////////////////
//- Create Package
Package package = new Package();
//- Aggiungo un task dataFlow
MainPipe dataFlow = ((TaskHost)package.Executables.Add("DTS.Pipeline")).InnerObject as MainPipe;
//- Add a OLEDB connection SOURCE
ConnectionManager cm = package.Connections.Add("OLEDB");
cm.Name = "OLEDB ConnectionManager";
cm.ConnectionString = "Data Source=PAGCOMPUTER;Initial Catalog=DBProva;Provider=SQLOLEDB;Integrated Security=SSPI;"; //Persist Security Info=False;Auto Translate=False;";