Program Connection: created

Showing posts with label created. Show all posts

Friday, March 30, 2012

Proprties not imported with Table

I am developing a DB with others in my group. When I import tables created on other servers to my server, the primary key and other properties do not import with the tables. Can anyone explain why this is happening? Is there a setting I have over looked?If you use DTS, make usre you use the Copy object(s) instead of Copy table(s) option.|||Nope...what method are you using to migrate the Data

Look at DTS transfer Database Task option

Or better yet, script the objects then build them...I prefer this method, and use bcp|||Originally posted by joejcheng
If you use DTS, make usre you use the Copy object(s) instead of Copy table(s) option.
The copy object worked ... Thanks

Wednesday, March 28, 2012

Property AnsiNullsStatus is not available for UserDefinedFunction

I created a table valued function in SQL Server 2000 SP3 using SQL 2005 Management Studio, but when I try to edit it (Right-Click, Modify or Script Function As...) I get the following error:

Property AnsiNullsStatus is not available for UserDefinedFunction '[dbo].[fnMyTDF]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights. (Microsoft.SqlServer.Smo)

I can edit it in other tools (Sql Query Analyzer) just fine. I'm in the sysadmin role.

Versions:

Microsoft SQL Server Management Studio 9.00.2047.00
Microsoft Analysis Services Client Tools 2005.090.2047.00
Microsoft Data Access Components (MDAC) 2000.085.1117.00 (xpsp_sp2_rtm.040803-2158)
Microsoft MSXML 2.6 3.0 4.0 5.0 6.0
Microsoft Internet Explorer 6.0.2900.2180
Microsoft .NET Framework 2.0.50727.42
Operating System 5.1.2600

Any thoughts?

Thanks in advance
Dennis

This defect has been fixed in Service Pack 2.

Thanks,
Steve

|||Thanks for the quick response. For future reference, is there a way I could have found this out myself. I thought I searched all the usual places.

thanks
-dennis
|||Do you mean SP2 for Sql 2005? That hasn't been relased, has it?|||

I've installed SP2 for SQL Server 2005 but I get the same error.

I'm using Express Edition and the SP2 Install reports

"The edition of product instance MSSQLSERVER does not qualify for this update" for MSSQLSERVER\Database Services.

Has anybody any other suggestions?

Martin Wills

Property AnsiNullsStatus is not available for UserDefinedFunction

I created a table valued function in SQL Server 2000 SP3 using SQL 2005 Management Studio, but when I try to edit it (Right-Click, Modify or Script Function As...) I get the following error:

I can edit it in other tools (Sql Query Analyzer) just fine. I'm in the sysadmin role.

Versions:

Any thoughts?

Thanks in advance
Dennis

This defect has been fixed in Service Pack 2.

Thanks,
Steve

|||Thanks for the quick response. For future reference, is there a way I could have found this out myself. I thought I searched all the usual places.

thanks
-dennis|||Do you mean SP2 for Sql 2005? That hasn't been relased, has it?|||

I've installed SP2 for SQL Server 2005 but I get the same error.

I'm using Express Edition and the SP2 Install reports

"The edition of product instance MSSQLSERVER does not qualify for this update" for MSSQLSERVER\Database Services.

Has anybody any other suggestions?

Martin Wills

sql

Property AnsiNullsStatus is not available for UserDefinedFunction

I created a table valued function in SQL Server 2000 SP3 using SQL 2005 Management Studio, but when I try to edit it (Right-Click, Modify or Script Function As...) I get the following error:

I can edit it in other tools (Sql Query Analyzer) just fine. I'm in the sysadmin role.

Versions:

Any thoughts?

Thanks in advance
Dennis

This defect has been fixed in Service Pack 2.

Thanks,
Steve

|||Thanks for the quick response. For future reference, is there a way I could have found this out myself. I thought I searched all the usual places.

thanks
-dennis|||Do you mean SP2 for Sql 2005? That hasn't been relased, has it?|||

I've installed SP2 for SQL Server 2005 but I get the same error.

I'm using Express Edition and the SP2 Install reports

"The edition of product instance MSSQLSERVER does not qualify for this update" for MSSQLSERVER\Database Services.

Has anybody any other suggestions?

Martin Wills

Monday, March 26, 2012

Proper indexs against query and optimization

Viewing trace following query gives Duration - 517470
Which indexes should be created on tables and how to make this query
optimized.
================================================== =====
SELECT top 1 package_description.name,
package_description.tier,
package_description.pid
FROM package_description
inner join package on package_description.pid = package.package_id
inner join courses on package.course_id = courses.id
inner join commission on package_description.pid =
commission.package_id
inner join cinfo on commission.owner_id = cinfo.cid
WHERE (courses.id = 45448) and
(cinfo.cid = 121) and
(package_description.type <> 2)
and package_description.state_id = 41
ORDER BY package_description.available ASC
================================================== =======
TIA
Kay
hi,
check your indexes and see if you are using them properly at your join
tables. may be you also need to open an execution plan and see at which step
your query is taking most precentage. also may be you should also defragment
or reubild your indexes after checking the showcontig (focus on the log and
extent results).
thx,
Tomer
"Kay" wrote:

> Viewing trace following query gives Duration - 517470
> Which indexes should be created on tables and how to make this query
> optimized.
> ================================================== =====
> SELECT top 1 package_description.name,
> package_description.tier,
> package_description.pid
> FROM package_description
> inner join package on package_description.pid = package.package_id
> inner join courses on package.course_id = courses.id
> inner join commission on package_description.pid =
> commission.package_id
> inner join cinfo on commission.owner_id = cinfo.cid
> WHERE (courses.id = 45448) and
> (cinfo.cid = 121) and
> (package_description.type <> 2)
> and package_description.state_id = 41
> ORDER BY package_description.available ASC
> ================================================== =======
>
> TIA
> Kay
>
>
|||Its tough to tell you without more information about these tables, their keys
and the relationships between them (and the execution plan). Also, from the
looks of the join it appears that your model is potentially de-normalized,
this adds another potential issue.
But, from what you have listed.
A good starting point is the following (these are not always true, but a
good starting point)
1) Make sure all the tables have a primary key
2) Set the primary key as clustered
3) Create a non-clustered index on the foreign keys
So, in your case
indexes for package_description
ON pid PK clustered
ON state_id, type, available NonClustered
indexes for package
ON package_id PK Clustered
ON course_id NonClustered
index for courses
id PK Clustered
index for commission
package_ID PK clustered
owner_id nonclustered
index cinfo
cid PK Clustered
Be forewarned, this is a bit of a blind guess. But, it will hopefully get
you started in the right direction. The root of your issue could very well
be outside of just index creation, and might be related to your schema itself.
HTH
"Kay" wrote:

> Viewing trace following query gives Duration - 517470
> Which indexes should be created on tables and how to make this query
> optimized.
> ================================================== =====
> SELECT top 1 package_description.name,
> package_description.tier,
> package_description.pid
> FROM package_description
> inner join package on package_description.pid = package.package_id
> inner join courses on package.course_id = courses.id
> inner join commission on package_description.pid =
> commission.package_id
> inner join cinfo on commission.owner_id = cinfo.cid
> WHERE (courses.id = 45448) and
> (cinfo.cid = 121) and
> (package_description.type <> 2)
> and package_description.state_id = 41
> ORDER BY package_description.available ASC
> ================================================== =======
>
> TIA
> Kay
>
>
sql

Proper indexs against query and optimization

Viewing trace following query gives Duration - 517470
Which indexes should be created on tables and how to make this query
optimized.
========================================
===============
SELECT top 1 package_description.name,
package_description.tier,
package_description.pid
FROM package_description
inner join package on package_description.pid = package.package_id
inner join courses on package.course_id = courses.id
inner join commission on package_description.pid =
commission.package_id
inner join cinfo on commission.owner_id = cinfo.cid
WHERE (courses.id = 45448) and
(cinfo.cid = 121) and
(package_description.type <> 2)
and package_description.state_id = 41
ORDER BY package_description.available ASC
========================================
=================
TIA
Kayhi,
check your indexes and see if you are using them properly at your join
tables. may be you also need to open an execution plan and see at which step
your query is taking most precentage. also may be you should also defragment
or reubild your indexes after checking the showcontig (focus on the log and
extent results).
thx,
Tomer
"Kay" wrote:

> Viewing trace following query gives Duration - 517470
> Which indexes should be created on tables and how to make this query
> optimized.
> ========================================
===============
> SELECT top 1 package_description.name,
> package_description.tier,
> package_description.pid
> FROM package_description
> inner join package on package_description.pid = package.package_id
> inner join courses on package.course_id = courses.id
> inner join commission on package_description.pid =
> commission.package_id
> inner join cinfo on commission.owner_id = cinfo.cid
> WHERE (courses.id = 45448) and
> (cinfo.cid = 121) and
> (package_description.type <> 2)
> and package_description.state_id = 41
> ORDER BY package_description.available ASC
> ========================================
=================
>
> TIA
> Kay
>
>|||Its tough to tell you without more information about these tables, their key
s
and the relationships between them (and the execution plan). Also, from the
looks of the join it appears that your model is potentially de-normalized,
this adds another potential issue.
But, from what you have listed.
A good starting point is the following (these are not always true, but a
good starting point)
1) Make sure all the tables have a primary key
2) Set the primary key as clustered
3) Create a non-clustered index on the foreign keys
So, in your case
indexes for package_description
ON pid PK clustered
ON state_id, type, available NonClustered
indexes for package
ON package_id PK Clustered
ON course_id NonClustered
index for courses
id PK Clustered
index for commission
package_ID PK clustered
owner_id nonclustered
index cinfo
cid PK Clustered
Be forewarned, this is a bit of a blind guess. But, it will hopefully get
you started in the right direction. The root of your issue could very well
be outside of just index creation, and might be related to your schema itsel
f.
HTH
"Kay" wrote:

> Viewing trace following query gives Duration - 517470
> Which indexes should be created on tables and how to make this query
> optimized.
> ========================================
===============
> SELECT top 1 package_description.name,
> package_description.tier,
> package_description.pid
> FROM package_description
> inner join package on package_description.pid = package.package_id
> inner join courses on package.course_id = courses.id
> inner join commission on package_description.pid =
> commission.package_id
> inner join cinfo on commission.owner_id = cinfo.cid
> WHERE (courses.id = 45448) and
> (cinfo.cid = 121) and
> (package_description.type <> 2)
> and package_description.state_id = 41
> ORDER BY package_description.available ASC
> ========================================
=================
>
> TIA
> Kay
>
>

Proper indexs against query and optimization

Viewing trace following query gives Duration - 517470
Which indexes should be created on tables and how to make this query
optimized.
======================================================= SELECT top 1 package_description.name,
package_description.tier,
package_description.pid
FROM package_description
inner join package on package_description.pid = package.package_id
inner join courses on package.course_id = courses.id
inner join commission on package_description.pid = commission.package_id
inner join cinfo on commission.owner_id = cinfo.cid
WHERE (courses.id = 45448) and
(cinfo.cid = 121) and
(package_description.type <> 2)
and package_description.state_id = 41
ORDER BY package_description.available ASC
=========================================================
TIA
Kayhi,
check your indexes and see if you are using them properly at your join
tables. may be you also need to open an execution plan and see at which step
your query is taking most precentage. also may be you should also defragment
or reubild your indexes after checking the showcontig (focus on the log and
extent results).
thx,
Tomer
"Kay" wrote:
> Viewing trace following query gives Duration - 517470
> Which indexes should be created on tables and how to make this query
> optimized.
> =======================================================> SELECT top 1 package_description.name,
> package_description.tier,
> package_description.pid
> FROM package_description
> inner join package on package_description.pid = package.package_id
> inner join courses on package.course_id = courses.id
> inner join commission on package_description.pid => commission.package_id
> inner join cinfo on commission.owner_id = cinfo.cid
> WHERE (courses.id = 45448) and
> (cinfo.cid = 121) and
> (package_description.type <> 2)
> and package_description.state_id = 41
> ORDER BY package_description.available ASC
> =========================================================>
> TIA
> Kay
>
>|||Its tough to tell you without more information about these tables, their keys
and the relationships between them (and the execution plan). Also, from the
looks of the join it appears that your model is potentially de-normalized,
this adds another potential issue.
But, from what you have listed.
A good starting point is the following (these are not always true, but a
good starting point)
1) Make sure all the tables have a primary key
2) Set the primary key as clustered
3) Create a non-clustered index on the foreign keys
So, in your case
indexes for package_description
ON pid PK clustered
ON state_id, type, available NonClustered
indexes for package
ON package_id PK Clustered
ON course_id NonClustered
index for courses
id PK Clustered
index for commission
package_ID PK clustered
owner_id nonclustered
index cinfo
cid PK Clustered
Be forewarned, this is a bit of a blind guess. But, it will hopefully get
you started in the right direction. The root of your issue could very well
be outside of just index creation, and might be related to your schema itself.
HTH
"Kay" wrote:
> Viewing trace following query gives Duration - 517470
> Which indexes should be created on tables and how to make this query
> optimized.
> =======================================================> SELECT top 1 package_description.name,
> package_description.tier,
> package_description.pid
> FROM package_description
> inner join package on package_description.pid = package.package_id
> inner join courses on package.course_id = courses.id
> inner join commission on package_description.pid => commission.package_id
> inner join cinfo on commission.owner_id = cinfo.cid
> WHERE (courses.id = 45448) and
> (cinfo.cid = 121) and
> (package_description.type <> 2)
> and package_description.state_id = 41
> ORDER BY package_description.available ASC
> =========================================================>
> TIA
> Kay
>
>

Friday, March 23, 2012

Propagate NEW Table to Anon Merge Replicas

How do I get a New Table to propagate to Anonymous Merge Subscribers, WITH
Add, Update, Delete Triggers?
I created a new Table (by running a SQL Script created on a Developer
machine) on a Remote SBS 2003 Server (SQL 2K). It Propagated to Subscribers,
OK. But it is useless, because is missing the Triggers: dbo.ins_ ... ,
dbo.upd_ ... , dbo.del_ ...
What did I miss, and Where? when I created the Prototype?
Aubrey Kelley
It sounds like merge replication has not placed all of the object it
requires on the subscriber. Are all objects owned by dbo? There were some
problems on pre SP 4 service packs with this.
How did you create the new table and add it to the merge publication? How
did you deploy it to your subscribers?
The best way to fix this is to reinitialize, generate a new snapshot and
send it to the subscribers. Any other approach may fix this particular
problem but there could be further missing objects and the merge replication
metadata may be in an inconsistent state.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Aubrey" <miscuates@.online.nospam> wrote in message
news:B1E8B402-7B5B-4DD3-8389-9BBC7D99F109@.microsoft.com...
> How do I get a New Table to propagate to Anonymous Merge Subscribers, WITH
> Add, Update, Delete Triggers?
> I created a new Table (by running a SQL Script created on a Developer
> machine) on a Remote SBS 2003 Server (SQL 2K). It Propagated to
> Subscribers,
> OK. But it is useless, because is missing the Triggers: dbo.ins_ ... ,
> dbo.upd_ ... , dbo.del_ ...
> What did I miss, and Where? when I created the Prototype?
> --
> Aubrey Kelley

Wednesday, March 21, 2012

Project_Central

We created Project_Central Database using this code:
osql -U dbadmin -i Crttable.sql
osql -U dbadmin -P glance -d Project_CentralDB -i Insdefsq.sql
osql -U dbadmin -P glance -d Project_CentralDB -i Insdefsi.sql
Even though the Project_Central Database does not show single user in the
server itself. It displays this error. How can we turn the single user mode
off. Please help!!
*****************
Logon failed.
The server is currently in single-user mode.
See your Microsoft Project Central administrator to return the server to
normal mode.
Return to logon.
*******************
Arturo
Could it be that the data server itself is in single user mode? The data
server can be placed in single user mode via the -m start up flag. Check your
start-up parameters and see if the -m flag is one of them. If so, remove the
flag and restart the data server.
"Arturo" wrote:

> We created Project_Central Database using this code:
> osql -U dbadmin -i Crttable.sql
> osql -U dbadmin -P glance -d Project_CentralDB -i Insdefsq.sql
> osql -U dbadmin -P glance -d Project_CentralDB -i Insdefsi.sql
> Even though the Project_Central Database does not show single user in the
> server itself. It displays this error. How can we turn the single user mode
> off. Please help!!
> *****************
> Logon failed.
> The server is currently in single-user mode.
> See your Microsoft Project Central administrator to return the server to
> normal mode.
> Return to logon.
> *******************
> --
> Arturo

Project_Central

We created Project_Central Database using this code:
osql -U dbadmin -i Crttable.sql
osql -U dbadmin -P glance -d Project_CentralDB -i Insdefsq.sql
osql -U dbadmin -P glance -d Project_CentralDB -i Insdefsi.sql
Even though the Project_Central Database does not show single user in the
server itself. It displays this error. How can we turn the single user mod
e
off. Please help!!
*****************
Logon failed.
The server is currently in single-user mode.
See your Microsoft Project Central administrator to return the server to
normal mode.
Return to logon.
*******************
--
ArturoCould it be that the data server itself is in single user mode? The data
server can be placed in single user mode via the -m start up flag. Check you
r
start-up parameters and see if the -m flag is one of them. If so, remove the
flag and restart the data server.
"Arturo" wrote:

> We created Project_Central Database using this code:
> osql -U dbadmin -i Crttable.sql
> osql -U dbadmin -P glance -d Project_CentralDB -i Insdefsq.sql
> osql -U dbadmin -P glance -d Project_CentralDB -i Insdefsi.sql
> Even though the Project_Central Database does not show single user in the
> server itself. It displays this error. How can we turn the single user m
ode
> off. Please help!!
> *****************
> Logon failed.
> The server is currently in single-user mode.
> See your Microsoft Project Central administrator to return the server to
> normal mode.
> Return to logon.
> *******************
> --
> Arturo

Project_Central

We created Project_Central Database using this code:
osql -U dbadmin -i Crttable.sql
osql -U dbadmin -P glance -d Project_CentralDB -i Insdefsq.sql
osql -U dbadmin -P glance -d Project_CentralDB -i Insdefsi.sql
Even though the Project_Central Database does not show single user in the
server itself. It displays this error. How can we turn the single user mode
off. Please help!!
*****************
Logon failed.
The server is currently in single-user mode.
See your Microsoft Project Central administrator to return the server to
normal mode.
Return to logon.
*******************
--
ArturoCould it be that the data server itself is in single user mode? The data
server can be placed in single user mode via the -m start up flag. Check your
start-up parameters and see if the -m flag is one of them. If so, remove the
flag and restart the data server.
"Arturo" wrote:
> We created Project_Central Database using this code:
> osql -U dbadmin -i Crttable.sql
> osql -U dbadmin -P glance -d Project_CentralDB -i Insdefsq.sql
> osql -U dbadmin -P glance -d Project_CentralDB -i Insdefsi.sql
> Even though the Project_Central Database does not show single user in the
> server itself. It displays this error. How can we turn the single user mode
> off. Please help!!
> *****************
> Logon failed.
> The server is currently in single-user mode.
> See your Microsoft Project Central administrator to return the server to
> normal mode.
> Return to logon.
> *******************
> --
> Arturosql

Friday, March 9, 2012

Programmatically Data Conversion component creation

Hi there,

I have created a package which simply imports data from a flat file to a SQL Server table. But I need to incorporate a data conversion component by which I may change the source-destination column mapping programmatically. So what I thought that I need to add a data conversion component into the dataflow task. After adding this component (I found its component id as {C3BF62C8-7C5C-4F85-83C3-E0B6F6BE267C}) I have created a path which establishes the mapping between output columns of source component and the input columns of data conversion component. Now I am not sure how to establish the mapping between the data conversion component’s input column collection and output column collection.

I am giving my code snippet here,

IDTSComponentMetaData90 sourceDataFlowComponent = dataFlowTask.ComponentMetaDataCollection.New();

sourceDataFlowComponent.ComponentClassID = "{90C7770B-DE7C-435E-880E-E718C92C0573}";

… … …. // Code for configuring the source data flow component

IDTSComponentMetaData90 conversionDataFlowComponent = dataFlowTask.ComponentMetaDataCollection.New();// creating data conversion

conversionDataFlowComponent.ComponentClassID = "{C3BF62C8-7C5C-4F85-83C3-E0B6F6BE267C}";// This is the GUID for data conversion component

CManagedComponentWrapper conversionInstance = conversionDataFlowComponent.Instantiate();//Instantiate

conversionInstance.ProvideComponentProperties();

// Now creating a path to connet the source and conversion

IDTSPath90 fPath = dataFlowTask.PathCollection.New();fPath.AttachPathAndPropagateNotifications(

sourceDataFlowComponent.OutputCollection[0],

conversionDataFlowComponent.InputCollection[0]);

// Sould I need to accuire connect for data conversion? Im not sure

conversionInstance.AcquireConnections(null);

conversionInstance.ReinitializeMetaData();

// Get the input collection

IDTSInput90 input = conversionDataFlowComponent.InputCollection[0];

IDTSVirtualInput90 vInput = input.GetVirtualInput();

foreach (IDTSVirtualInputColumn90 vColumn in vInput.VirtualInputColumnCollection){

conversionInstance.SetUsageType(

input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY);

}

.. . // Well here I am stucked. What I need to do here to establish a map

// between conversionDataFlowComponent.InputCollection[0] and

// conversionDataFlowComponent.OutputCollection[0]?

As you can see I am just away from creating the mapping between input and output collection. Can anybody give me an idea how can I achieve this?

I will appreciate all kind of suggestions and comments.

Regards

Moim

Have a look at this code snippet where I use this transform, selecting all columns, and converting types where required based on the result of a helper function-

#region Select Conversion Columns

IDTSOutput90 output = dataConversion.OutputCollection[0];

IDTSVirtualInput90 virtualInput = dataConversion.InputCollection[0].GetVirtualInput();

int inputId = dataConversion.InputCollection[0].ID;

foreach (IDTSVirtualInputColumn90 column in virtualInput.VirtualInputColumnCollection)

{

int length = column.Length;

int precision = column.Precision;

int scale = column.Scale;

DataType dataType = ComponentHelper.ConvertBufferDataTypeToFitExcel(column.DataType, ref length, ref precision, ref scale);

if (dataType != column.DataType)

{

IDTSInputColumn90 inputColumn = dataConversionInstance.SetUsageType(inputId, virtualInput, column.LineageID, DTSUsageType.UT_READONLY);

IDTSOutputColumn90 outputColumn = dataConversionInstance.InsertOutputColumnAt(output.ID, output.OutputColumnCollection.Count, column.Name, "Excel compatible conversion column");

dataConversionInstance.SetOutputColumnDataTypeProperties(output.ID, outputColumn.ID, dataType, length, precision, scale, 0);

outputColumn.CustomPropertyCollection[0].Value = inputColumn.LineageID;

inputColumn.Name = DataConversionInputRenamePrefix + inputColumn.Name;

}

#endregion

Btw, how did you get your code to paste so nicely into this editor Window?

|||

DarrenSQLIS wrote:

Btw, how did you get your code to paste so nicely into this editor Window?

Try this:

http://www.codeproject.com/jscript/CopyasHTML.asp

Or just go here: http://www.google.co.uk/search?hl=en&q=copyashtml&meta=

I have add-in that enables me to do it but I can't for the life of me find it again.

-Jamie

|||

Jamie Thomson wrote:

DarrenSQLIS wrote:

Btw, how did you get your code to paste so nicely into this editor Window?

I have add-in that enables me to do it but I can't for the life of me find it again.

-Jamie

Here it is: http://www.jtleigh.com/people/colin/software/CopySourceAsHtml/|||Nice, thanks Gents.|||

Hello Darran,

Thanks a lot. After implementing your suggestion I believe I am very near to make it work. I think I am missing/doing some silly things/mistakes so that the package is not working as expected. I observed the log traces and found that it is failing to validate the destination data flow component. And debugging it I found that after creating a path between the data conversion component and the destination component, when I populate the input columns for the destination component it is actually getting the source components output columns lineageID instead of data conversion component’s output columns lineageID. Can you help me to find out the problem please?

Anyway, thank you very much for your previous help.

Here is the code snippet:

private void ConfigureSourceComponent()

{

sourceDataFlowComponent.Name = "Source Data from Flat file";

// Here is the component class id for flat file source data

sourceDataFlowComponent.ComponentClassID = "{90C7770B-DE7C-435E-880E-E718C92C0573}";

CManagedComponentWrapper managedFlatFileInstance =

sourceDataFlowComponent.Instantiate();

managedFlatFileInstance.ProvideComponentProperties();sourceDataFlowComponent.RuntimeConnectionCollection[0].

ConnectionManagerID =flatfileConnectionManager.ID;sourceDataFlowComponent.RuntimeConnectionCollection[0].

ConnectionManager =DtsConvert.ToConnectionManager90(flatfileConnectionManager);

managedFlatFileInstance.AcquireConnections(null);

managedFlatFileInstance.ReinitializeMetaData();

IDTSExternalMetadataColumn90 exOutColumn;

foreach (IDTSOutputColumn90 outColumn in sourceDataFlowComponent.OutputCollection[0].OutputColumnCollection)

{

exOutColumn = sourceDataFlowComponent.OutputCollection[0].

ExternalMetadataColumnCollection[outColumn.Name];managedFlatFileInstance.MapOutputColumn(

sourceDataFlowComponent.OutputCollection[0].ID, outColumn.ID, exOutColumn.ID, true);

}

managedFlatFileInstance.ReleaseConnections()

}

private void ConfigureConversionComponent()

{

conversionDataFlowComponent.Name = "Conversion compoenent";

// component class id for Data conversion component

conversionDataFlowComponent.ComponentClassID = "{C3BF62C8-7C5C-4F85-83C3-E0B6F6BE267C}";

CManagedComponentWrapper conversionInstance =

conversionDataFlowComponent.Instantiate();

conversionInstance.ProvideComponentProperties();

// Create the path.

IDTSPath90 fPath = dataFlowTask.PathCollection.New();fPath.AttachPathAndPropagateNotifications(

sourceDataFlowComponent.OutputCollection[0],

conversionDataFlowComponent.InputCollection[0]);

conversionInstance.AcquireConnections(null);

conversionInstance.ReinitializeMetaData();

// Get the output collection

IDTSOutput90 output = conversionDataFlowComponent.OutputCollection[0];

// Get the destination's default input and virtual input.

input = conversionDataFlowComponent.InputCollection[0];

vInput = input.GetVirtualInput();

// Iterate through the virtual input column collection.

foreach (IDTSVirtualInputColumn90 vColumn in vInput.VirtualInputColumnCollection)

{

IDTSInputColumn90 inputColumn =

conversionInstance.SetUsageType(

input.ID, vInput, vColumn.LineageID,

DTSUsageType.UT_READONLY);

IDTSOutputColumn90 outputColumn =

conversionInstance.InsertOutputColumnAt

(output.ID, output.OutputColumnCollection.Count,

vColumn.Name, "Sql server compatible conversion column");

conversionInstance.SetOutputColumnDataTypeProperties(

output.ID, outputColumn.ID, vColumn.DataType,

vColumn.Length, vColumn.Precision, vColumn.Scale, 0);

outputColumn.CustomPropertyCollection[0].Value =

inputColumn.LineageID;

}

conversionInstance.ReleaseConnections();

}

private void ConfigureDestinationComponent()

{

destinationDataFlowComponent.Name = "Destination Oledb compoenent";

// component class id for Oledb

destinationDataFlowComponent.ComponentClassID = "{E2568105-9550-4F71-A638-B7FE42E66922}";

CManagedComponentWrapper managedOleInstance =

destinationDataFlowComponent.Instantiate();

managedOleInstance.ProvideComponentProperties();

destinationDataFlowComponent.RuntimeConnectionCollection[0].

ConnectionManagerID = oledbConnectionManager.ID;

destinationDataFlowComponent.RuntimeConnectionCollection[0].

ConnectionManager =DtsConvert.ToConnectionManager90(oledbConnectionManager);

managedOleInstance.SetComponentProperty("AccessMode", 0);

// .. .. .. setting other component properties here..

// Create the path.

IDTSPath90 path = dataFlowTask.PathCollection.New();

path.AttachPathAndPropagateNotifications(

conversionDataFlowComponent.OutputCollection[0],

destinationDataFlowComponent.InputCollection[0]

);

managedOleInstance.AcquireConnections(null);

managedOleInstance.ReinitializeMetaData();

// Get the destination's default input and virtual input.

IDTSInput90 input;

IDTSVirtualInput90 vInput;

// Get the destination's default input and virtual input.

input = destinationDataFlowComponent.InputCollection[0];

vInput = input.GetVirtualInput();

// Iterate through the virtual input column collection.

foreach (IDTSVirtualInputColumn90 vColumn in

vInput.VirtualInputColumnCollection)

{

managedOleInstance.SetUsageType(

input.ID, vInput, vColumn.LineageID,

DTSUsageType.UT_READONLY);

// culprit Line. I can found vColumn.LineageID = source lineage ids

// while debugging.. can you please help me out here?

}

IDTSExternalMetadataColumn90 exColumn;

foreach (IDTSInputColumn90 inColumn in

destinationDataFlowComponent.InputCollection[0].InputColumnCollection)

{

exColumn = destinationDataFlowComponent.InputCollection[0].

ExternalMetadataColumnCollection[inColumn.Name];managedOleInstance.MapInputColumn(destinationDataFlowComponent.

InputCollection[0].ID, inColumn.ID, exColumn.ID);

}

managedOleInstance.ReleaseConnections();

}

Here you can see the red marked lines. Where I can found that the destination component is watching the source output columns lineage althoug its mapped with data conversion component.

Regards

Moim

|||

The input buffer of the destination will include all upstream columns, so that means both the ones that came from the source and those from the Data Conversion Tx. You will need to be selective in choosing the input columns.

Think of the buffer as it passes down the pipeline, or rather what columns are available -

Source Output - SourceCol1

Data Conversion Output - SourceCol1, SourceCol1(converted)

You don't seem to be distiguishing between the input columns and output columns in the data conversion setup, that seems strange. You would only want to select the converted columns I assume. You could use a name, description or the UpstreamComponentName property of the column perhaps.

|||

Hi,

I have similar requirement.I need to import data from csv files to sql server tables using ssis programatically.iam not able to create a data conversion task programatically and map the columns from the dataconversion task to the oledb destination.tried my level best and got struck.Is there a code demo availble?appreciating your help

|||

adarsha wrote:

Hi,

I have similar requirement.I need to import data from csv files to sql server tables using ssis programatically.iam not able to create a data conversion task programatically and map the columns from the dataconversion task to the oledb destination.tried my level best and got struck.Is there a code demo availble?appreciating your help

It would really help if you explain what you are getting stuck on and how far you have come so far. Posting your code would be even better.

-Jamie

|||

Hi,

//In the below example i have pumped data from sql table to csvs which works fine for me .But now i need to do the exact opposite task.(Pump data from csv files to sql table)

Problems i have :

After adding a flatfile source iam not very sure how to exactly add input columns in FlatFile source as they dont get populated by themselves.I tried using datatable reading the columns from the file and add it and somewhat succeeded.But need a clear implementation help.

To some extent when i succeeded in adding input columns to flat file source, the package failed due to data conversion problems(unicode to non unicode conversion error).I tried programatically building dataconversion task and had no clue how exactly to do it correctly and make it to work.

So i need help in adding input columns to flat file source and adding data conversion task and selecting only the converted columns to the oledb destination.

using System;

using System.Collections.Generic;

using System.Collections.Specialized;

using System.Reflection;

using System.Threading;

using System.Text;

using System.IO;

using Microsoft.SqlServer.Dts.Runtime;

using Microsoft.SqlServer.Dts.Pipeline.Wrapper;

using wrap = Microsoft.SqlServer.Dts.Runtime.Wrapper;

namespace CCE_ETL_Engine_Flatfiles

{

class Flatfile_Pump

{

Boolean mblnDebug = true;

string mstrAppLogFile;

string mstrDTSXFile;

string DataFileName;

string DestinationDataDirectory;

#region intialize

public void InitializeVariables(string strDTSPackageName)

{

DestinationDataDirectory = System.Configuration.ConfigurationManager.AppSettings.Get("DataFilePath");

DataFileName = System.Configuration.ConfigurationManager.AppSettings.Get("DataFileName");

Object objLock = new Object();

Monitor.Enter(objLock);

string strTimeStamp = DateTime.Now.Hour.ToString().PadLeft(2, '0') + DateTime.Now.Minute.ToString().PadLeft(2, '0') + DateTime.Now.Second.ToString().PadLeft(2, '0') + DateTime.Now.Millisecond.ToString();

Monitor.Exit(objLock);

mstrAppLogFile = System.Configuration.ConfigurationManager.AppSettings.Get("ApplicationLogFilePath") + @."\" + strDTSPackageName + "_" + strTimeStamp + ".log";

mstrDTSXFile = System.Configuration.ConfigurationManager.AppSettings.Get("DTSXFilePath") + @."\" + strDTSPackageName + "_" + strTimeStamp + ".dtsx";

if (System.Configuration.ConfigurationManager.AppSettings.Get("Debug").ToUpper() == "FALSE")

mblnDebug = false;

else

mblnDebug = true;

}

#endregion

#region constructor

public Flatfile_Pump(string strDTSPackageName)

{

InitializeVariables(strDTSPackageName);

}

#endregion

#region CreatePackage

public Package CreatePackage(string Name, string Description)

{

Package DataPump = new Package();

DataPump.PackageType = DTSPackageType.DTSDesigner90;

DataPump.Name = Name;

DataPump.Description = Description;

DataPump.CreatorComputerName = System.Environment.MachineName;

DataPump.CreatorName = System.Environment.UserName;

return DataPump;

}

#endregion

#region AddConnectionManagers

/// <summary>

/// Adds the OLEDB and FlatFile connection managers to the package.

/// </summary>

public void AddConnectionManagers(Package DataPump, string SqlConnection) Here iam adding 2 connection managers 1 for flatfile and 1 for OLEDB

{

// Add the OLEDB connection manager.

ConnectionManager ConnectionName;

ConnectionManager cmflatFile;

ConnectionName = DataPump.Connections.Add("OLEDB");

// Set stock properties.

ConnectionName.Name = "Remote Source";

ConnectionName.ConnectionString = SqlConnection;

//@."Provider=SQLNCLI;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=cce_control;Data Source=(wxp-9plyf1s);Auto Translate=False;";

// Add the Destination connection manager.

cmflatFile = DataPump.Connections.Add("FLATFILE");

// Set the stock properties.

cmflatFile.Properties["ConnectionString"].SetValue(cmflatFile, DestinationDataDirectory + DataFileName);

cmflatFile.Properties["Format"].SetValue(cmflatFile, "Delimited");

cmflatFile.Properties["DataRowsToSkip"].SetValue(cmflatFile, 0);

cmflatFile.Properties["ColumnNamesInFirstDataRow"].SetValue(cmflatFile, true);

cmflatFile.Properties["Name"].SetValue(cmflatFile, "FlatFileConnection");

cmflatFile.Properties["RowDelimiter"].SetValue(cmflatFile, "\r\n");

cmflatFile.Properties["TextQualifier"].SetValue(cmflatFile, "\"");

}

#endregion

#region AddDataFlowTask

/// <summary>

/// Adds a DataFlow task to the Executables collection of the package.

/// Retrieves the MainPipe object from the TaskHost and stores it in

/// the dataFlow member variable

/// </summary>

public MainPipe AddDataFlowTask(Package DataPump)

{

TaskHost th = DataPump.Executables.Add("DTS.Pipeline") as TaskHost;

th.Name = "DataFlow";

th.Description = "The DataFlow task in the package.";

MainPipe dataFlow = th.InnerObject as MainPipe;

ComponentEvents componentEvents = new ComponentEvents();

dataFlow.Events = componentEvents as wrap.IDTSComponentEvents90;

return dataFlow;

}

#endregion

#region AddOLEDBSource

public IDTSComponentMetaData90 AddOLEDBSource(Microsoft.SqlServer.Dts.Pipeline.Wrapper.MainPipe dataFlow, Package DataPump, string strSourceQuery)

{

// Declare and Add the component to the dataFlow metadata collection

IDTSComponentMetaData90 OLEDBsource;

OLEDBsource = dataFlow.ComponentMetaDataCollection.New();

// Set the common properties

OLEDBsource.ComponentClassID = "DTSAdapter.OLEDBSource";

OLEDBsource.Name = "cce_control";

OLEDBsource.Description = "Remote Source Server";

// Create an instance of the component

CManagedComponentWrapper instance = OLEDBsource.Instantiate();

instance.ProvideComponentProperties();

// Associate the runtime ConnectionManager with the component

OLEDBsource.RuntimeConnectionCollection[0].ConnectionManagerID = DataPump.Connections["Remote Source"].ID;

OLEDBsource.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(DataPump.Connections["Remote Source"]);

instance.SetComponentProperty("SqlCommand", strSourceQuery);

instance.SetComponentProperty("AccessMode", 2);

instance.AcquireConnections(null);

instance.ReinitializeMetaData();

instance.ReleaseConnections();

return OLEDBsource;

}

#endregion

#region AddFlatFileDestination

public IDTSComponentMetaData90 AddFlatFileDestination(Microsoft.SqlServer.Dts.Pipeline.Wrapper.MainPipe dataFlow, Package DataPump, IDTSComponentMetaData90 OLEDBsource)

{

// Declare and Add the component to the dataFlow metadata collection

IDTSComponentMetaData90 flatfileDestination;

flatfileDestination = dataFlow.ComponentMetaDataCollection.New();

// Set the common properties

flatfileDestination.ComponentClassID = "DTSAdapter.FlatFileDestination";

flatfileDestination.Name = "FlatFileDestination";

flatfileDestination.Description = "Flat file destination";

// Create an instance of the component

CManagedComponentWrapper instance = flatfileDestination.Instantiate();

instance.ProvideComponentProperties();

// Associate the runtime ConnectionManager with the component

flatfileDestination.RuntimeConnectionCollection[0].ConnectionManagerID

= DataPump.Connections["FlatFileConnection"].ID;

flatfileDestination.RuntimeConnectionCollection[0].ConnectionManager

= DtsConvert.ToConnectionManager90(DataPump.Connections["FlatFileConnection"]);

// Map a path between the Sort transformation component to the FlatFileDestination

dataFlow.PathCollection.New().AttachPathAndPropagateNotifications(OLEDBsource.OutputCollection[0], flatfileDestination.InputCollection[0]);

// Add columns to the FlatFileConnectionManager

AddColumnsToFlatFileConnection(DataPump, flatfileDestination);

// Acquire the connection, reinitialize the metadata,

// map the columns, then release the connection.

instance.AcquireConnections(null);

instance.ReinitializeMetaData();

MapFlatFileDestinationColumns(flatfileDestination);

instance.ReleaseConnections();

return flatfileDestination;

}

#endregion

#region AddColumnsToFlatFileConnection

public void AddColumnsToFlatFileConnection(Package DataPump, IDTSComponentMetaData90 flatfileDestination)

{

wrap.IDTSConnectionManagerFlatFile90 ff = null;

foreach (ConnectionManager cm in DataPump.Connections)

{

if (cm.Name == "FlatFileConnection")

{

ff = cm.InnerObject as wrap.IDTSConnectionManagerFlatFile90;

DtsConvert.ToConnectionManager90(cm);

}

int count = ff.Columns.Count;

// if the connection manager is null here, then we have a problem

if (ff != null)

{

// Get the upstream columns

IDTSVirtualInputColumnCollection90 vColumns = flatfileDestination.InputCollection[0].GetVirtualInput().VirtualInputColumnCollection;

for (int cols = 0; cols < vColumns.Count; cols++)

{

wrap.IDTSConnectionManagerFlatFileColumn90 col = ff.Columns.Add();

// If this is the last column, set the delimiter to CRLF.

// Otherwise keep the delimiter as ",".

if (cols == vColumns.Count - 1)

{

col.ColumnDelimiter = "\r\n";

}

else

{

col.ColumnDelimiter = @.",";

}

col.ColumnType = "Delimited";

col.DataType = vColumns[cols].DataType;

col.DataPrecision = vColumns[cols].Precision;

col.DataScale = vColumns[cols].Scale;

wrap.IDTSName90 name = col as wrap.IDTSName90;

name.Name = vColumns[cols].Name;

}

#endregion

#region MapFlatFileDestination Columns

public void MapFlatFileDestinationColumns(IDTSComponentMetaData90 flatfileDestination)

{

CManagedComponentWrapper wrp = flatfileDestination.Instantiate();

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

foreach (IDTSVirtualInputColumn90 vColumn in vInput.VirtualInputColumnCollection)

{

wrp.SetUsageType(flatfileDestination.InputCollection[0].ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY);

}

// For each column in the input collection

// find the corresponding external metadata column.

foreach (IDTSInputColumn90 col in flatfileDestination.InputCollection[0].InputColumnCollection)

{

IDTSExternalMetadataColumn90 exCol = flatfileDestination.InputCollection[0].ExternalMetadataColumnCollection[col.Name];

wrp.MapInputColumn(flatfileDestination.InputCollection[0].ID, col.ID, exCol.ID);

}

#endregion

#region Exec_DataPump--This is just the execution where i get values from like server name etc and create the package and execute it.

public Boolean DataPump(string SqlConnection, string strSourceQuery)

{

Package DataPump;

PackageEvents packageEvents;

MainPipe dataFlow;

IDTSComponentMetaData90 OLEDBsource, flatfileDestination;

packageEvents = new PackageEvents();

DataPump = CreatePackage("SSISPackage", "The package for the Flatfile pump");

AddConnectionManagers(DataPump, SqlConnection);

dataFlow = AddDataFlowTask(DataPump);

OLEDBsource = AddOLEDBSource(dataFlow, DataPump, strSourceQuery);

flatfileDestination = AddFlatFileDestination(dataFlow, DataPump, OLEDBsource);

DTSExecResult status = DataPump.Validate(null, null, packageEvents, null);

if (mblnDebug)

{

Application dts = new Application();

dts.SaveToXml(mstrDTSXFile, DataPump, packageEvents);

}

foreach (Variable v in DataPump.Variables)

{

if (v.Name.ToUpper() == "EXECUTIONINSTANCEGUID")

{

break;

}

if (status == DTSExecResult.Success)

{

DTSExecResult result = DataPump.Execute(null, null, packageEvents, null, null);

foreach (Variable v in DataPump.Variables)

{

Console.WriteLine(v.Name.ToUpper());

}

return true;

}

#endregion

}

Programmatically Data Conversion component creation

Hi there,

I am giving my code snippet here,

IDTSComponentMetaData90 sourceDataFlowComponent = dataFlowTask.ComponentMetaDataCollection.New();

sourceDataFlowComponent.ComponentClassID = "{90C7770B-DE7C-435E-880E-E718C92C0573}";

… … …. // Code for configuring the source data flow component

IDTSComponentMetaData90 conversionDataFlowComponent = dataFlowTask.ComponentMetaDataCollection.New();// creating data conversion

conversionDataFlowComponent.ComponentClassID = "{C3BF62C8-7C5C-4F85-83C3-E0B6F6BE267C}";// This is the GUID for data conversion component

CManagedComponentWrapper conversionInstance = conversionDataFlowComponent.Instantiate();//Instantiate

conversionInstance.ProvideComponentProperties();

// Now creating a path to connet the source and conversion

IDTSPath90 fPath = dataFlowTask.PathCollection.New();fPath.AttachPathAndPropagateNotifications(

sourceDataFlowComponent.OutputCollection[0],

conversionDataFlowComponent.InputCollection[0]);

// Sould I need to accuire connect for data conversion? Im not sure

conversionInstance.AcquireConnections(null);

conversionInstance.ReinitializeMetaData();

// Get the input collection

IDTSInput90 input = conversionDataFlowComponent.InputCollection[0];

IDTSVirtualInput90 vInput = input.GetVirtualInput();

foreach (IDTSVirtualInputColumn90 vColumn in vInput.VirtualInputColumnCollection){

conversionInstance.SetUsageType(

input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY);

}

.. . // Well here I am stucked. What I need to do here to establish a map

// between conversionDataFlowComponent.InputCollection[0] and

// conversionDataFlowComponent.OutputCollection[0]?

As you can see I am just away from creating the mapping between input and output collection. Can anybody give me an idea how can I achieve this?

I will appreciate all kind of suggestions and comments.

Regards

Moim

Have a look at this code snippet where I use this transform, selecting all columns, and converting types where required based on the result of a helper function-

#region Select Conversion Columns

IDTSOutput90 output = dataConversion.OutputCollection[0];

IDTSVirtualInput90 virtualInput = dataConversion.InputCollection[0].GetVirtualInput();

int inputId = dataConversion.InputCollection[0].ID;

foreach (IDTSVirtualInputColumn90 column in virtualInput.VirtualInputColumnCollection)

{

int length = column.Length;

int precision = column.Precision;

int scale = column.Scale;

DataType dataType = ComponentHelper.ConvertBufferDataTypeToFitExcel(column.DataType, ref length, ref precision, ref scale);

if (dataType != column.DataType)

{

IDTSInputColumn90 inputColumn = dataConversionInstance.SetUsageType(inputId, virtualInput, column.LineageID, DTSUsageType.UT_READONLY);

IDTSOutputColumn90 outputColumn = dataConversionInstance.InsertOutputColumnAt(output.ID, output.OutputColumnCollection.Count, column.Name, "Excel compatible conversion column");

dataConversionInstance.SetOutputColumnDataTypeProperties(output.ID, outputColumn.ID, dataType, length, precision, scale, 0);

outputColumn.CustomPropertyCollection[0].Value = inputColumn.LineageID;

inputColumn.Name = DataConversionInputRenamePrefix + inputColumn.Name;

}

#endregion

Btw, how did you get your code to paste so nicely into this editor Window?

|||

DarrenSQLIS wrote:

Btw, how did you get your code to paste so nicely into this editor Window?

Try this:

http://www.codeproject.com/jscript/CopyasHTML.asp

Or just go here: http://www.google.co.uk/search?hl=en&q=copyashtml&meta=

I have add-in that enables me to do it but I can't for the life of me find it again.

-Jamie

|||

Jamie Thomson wrote:

DarrenSQLIS wrote:
Btw, how did you get your code to paste so nicely into this editor Window?
I have add-in that enables me to do it but I can't for the life of me find it again.
-Jamie

Here it is: http://www.jtleigh.com/people/colin/software/CopySourceAsHtml/|||Nice, thanks Gents.|||

Hello Darran,

Anyway, thank you very much for your previous help.

Here is the code snippet:

private void ConfigureSourceComponent()

{

sourceDataFlowComponent.Name = "Source Data from Flat file";

// Here is the component class id for flat file source data

sourceDataFlowComponent.ComponentClassID = "{90C7770B-DE7C-435E-880E-E718C92C0573}";

CManagedComponentWrapper managedFlatFileInstance =

sourceDataFlowComponent.Instantiate();

managedFlatFileInstance.ProvideComponentProperties();sourceDataFlowComponent.RuntimeConnectionCollection[0].

ConnectionManagerID =flatfileConnectionManager.ID;sourceDataFlowComponent.RuntimeConnectionCollection[0].

ConnectionManager =DtsConvert.ToConnectionManager90(flatfileConnectionManager);

managedFlatFileInstance.AcquireConnections(null);

managedFlatFileInstance.ReinitializeMetaData();

IDTSExternalMetadataColumn90 exOutColumn;

foreach (IDTSOutputColumn90 outColumn in sourceDataFlowComponent.OutputCollection[0].OutputColumnCollection)

{

exOutColumn = sourceDataFlowComponent.OutputCollection[0].

ExternalMetadataColumnCollection[outColumn.Name];managedFlatFileInstance.MapOutputColumn(

sourceDataFlowComponent.OutputCollection[0].ID, outColumn.ID, exOutColumn.ID, true);

}

managedFlatFileInstance.ReleaseConnections()

}

private void ConfigureConversionComponent()

{

conversionDataFlowComponent.Name = "Conversion compoenent";

// component class id for Data conversion component

conversionDataFlowComponent.ComponentClassID = "{C3BF62C8-7C5C-4F85-83C3-E0B6F6BE267C}";

CManagedComponentWrapper conversionInstance =

conversionDataFlowComponent.Instantiate();

conversionInstance.ProvideComponentProperties();

// Create the path.

IDTSPath90 fPath = dataFlowTask.PathCollection.New();fPath.AttachPathAndPropagateNotifications(

sourceDataFlowComponent.OutputCollection[0],

conversionDataFlowComponent.InputCollection[0]);

conversionInstance.AcquireConnections(null);

conversionInstance.ReinitializeMetaData();

// Get the output collection

IDTSOutput90 output = conversionDataFlowComponent.OutputCollection[0];

// Get the destination's default input and virtual input.

input = conversionDataFlowComponent.InputCollection[0];

vInput = input.GetVirtualInput();

// Iterate through the virtual input column collection.

foreach (IDTSVirtualInputColumn90 vColumn in vInput.VirtualInputColumnCollection)

{

IDTSInputColumn90 inputColumn =

conversionInstance.SetUsageType(

input.ID, vInput, vColumn.LineageID,

DTSUsageType.UT_READONLY);

IDTSOutputColumn90 outputColumn =

conversionInstance.InsertOutputColumnAt

(output.ID, output.OutputColumnCollection.Count,

vColumn.Name, "Sql server compatible conversion column");

conversionInstance.SetOutputColumnDataTypeProperties(

output.ID, outputColumn.ID, vColumn.DataType,

vColumn.Length, vColumn.Precision, vColumn.Scale, 0);

outputColumn.CustomPropertyCollection[0].Value =

inputColumn.LineageID;

}

conversionInstance.ReleaseConnections();

}

private void ConfigureDestinationComponent()

{

destinationDataFlowComponent.Name = "Destination Oledb compoenent";

// component class id for Oledb

destinationDataFlowComponent.ComponentClassID = "{E2568105-9550-4F71-A638-B7FE42E66922}";

CManagedComponentWrapper managedOleInstance =

destinationDataFlowComponent.Instantiate();

managedOleInstance.ProvideComponentProperties();

destinationDataFlowComponent.RuntimeConnectionCollection[0].

ConnectionManagerID = oledbConnectionManager.ID;

destinationDataFlowComponent.RuntimeConnectionCollection[0].

ConnectionManager =DtsConvert.ToConnectionManager90(oledbConnectionManager);

managedOleInstance.SetComponentProperty("AccessMode", 0);

// .. .. .. setting other component properties here..

// Create the path.

IDTSPath90 path = dataFlowTask.PathCollection.New();

path.AttachPathAndPropagateNotifications(

conversionDataFlowComponent.OutputCollection[0],

destinationDataFlowComponent.InputCollection[0]

);

managedOleInstance.AcquireConnections(null);

managedOleInstance.ReinitializeMetaData();

// Get the destination's default input and virtual input.

IDTSInput90 input;

IDTSVirtualInput90 vInput;

// Get the destination's default input and virtual input.

input = destinationDataFlowComponent.InputCollection[0];

vInput = input.GetVirtualInput();

// Iterate through the virtual input column collection.

foreach (IDTSVirtualInputColumn90 vColumn in

vInput.VirtualInputColumnCollection)

{

managedOleInstance.SetUsageType(

input.ID, vInput, vColumn.LineageID,

DTSUsageType.UT_READONLY);

// culprit Line. I can found vColumn.LineageID = source lineage ids

// while debugging.. can you please help me out here?

}

IDTSExternalMetadataColumn90 exColumn;

foreach (IDTSInputColumn90 inColumn in

destinationDataFlowComponent.InputCollection[0].InputColumnCollection)

{

exColumn = destinationDataFlowComponent.InputCollection[0].

ExternalMetadataColumnCollection[inColumn.Name];managedOleInstance.MapInputColumn(destinationDataFlowComponent.

InputCollection[0].ID, inColumn.ID, exColumn.ID);

}

managedOleInstance.ReleaseConnections();

}

Here you can see the red marked lines. Where I can found that the destination component is watching the source output columns lineage althoug its mapped with data conversion component.

Regards

Moim

|||

Think of the buffer as it passes down the pipeline, or rather what columns are available -

Source Output - SourceCol1

Data Conversion Output - SourceCol1, SourceCol1(converted)

|||

Hi,

|||

adarsha wrote:
Hi,
I have similar requirement.I need to import data from csv files to sql server tables using ssis programatically.iam not able to create a data conversion task programatically and map the columns from the dataconversion task to the oledb destination.tried my level best and got struck.Is there a code demo availble?appreciating your help

It would really help if you explain what you are getting stuck on and how far you have come so far. Posting your code would be even better.

-Jamie

|||

Hi,

//In the below example i have pumped data from sql table to csvs which works fine for me .But now i need to do the exact opposite task.(Pump data from csv files to sql table)

Problems i have :

So i need help in adding input columns to flat file source and adding data conversion task and selecting only the converted columns to the oledb destination.

using System;

using System.Collections.Generic;

using System.Collections.Specialized;

using System.Reflection;

using System.Threading;

using System.Text;

using System.IO;

using Microsoft.SqlServer.Dts.Runtime;

using Microsoft.SqlServer.Dts.Pipeline.Wrapper;

using wrap = Microsoft.SqlServer.Dts.Runtime.Wrapper;

namespace CCE_ETL_Engine_Flatfiles

{

class Flatfile_Pump

{

Boolean mblnDebug = true;

string mstrAppLogFile;

string mstrDTSXFile;

string DataFileName;

string DestinationDataDirectory;

#region intialize

public void InitializeVariables(string strDTSPackageName)

{

DestinationDataDirectory = System.Configuration.ConfigurationManager.AppSettings.Get("DataFilePath");

DataFileName = System.Configuration.ConfigurationManager.AppSettings.Get("DataFileName");

Object objLock = new Object();

Monitor.Enter(objLock);

Monitor.Exit(objLock);

mstrAppLogFile = System.Configuration.ConfigurationManager.AppSettings.Get("ApplicationLogFilePath") + @."\" + strDTSPackageName + "_" + strTimeStamp + ".log";

mstrDTSXFile = System.Configuration.ConfigurationManager.AppSettings.Get("DTSXFilePath") + @."\" + strDTSPackageName + "_" + strTimeStamp + ".dtsx";

if (System.Configuration.ConfigurationManager.AppSettings.Get("Debug").ToUpper() == "FALSE")

mblnDebug = false;

else

mblnDebug = true;

}

#endregion

#region constructor

public Flatfile_Pump(string strDTSPackageName)

{

InitializeVariables(strDTSPackageName);

}

#endregion

#region CreatePackage

public Package CreatePackage(string Name, string Description)

{

Package DataPump = new Package();

DataPump.PackageType = DTSPackageType.DTSDesigner90;

DataPump.Name = Name;

DataPump.Description = Description;

DataPump.CreatorComputerName = System.Environment.MachineName;

DataPump.CreatorName = System.Environment.UserName;

return DataPump;

}

#endregion

#region AddConnectionManagers

/// <summary>

/// Adds the OLEDB and FlatFile connection managers to the package.

/// </summary>

public void AddConnectionManagers(Package DataPump, string SqlConnection) Here iam adding 2 connection managers 1 for flatfile and 1 for OLEDB

{

// Add the OLEDB connection manager.

ConnectionManager ConnectionName;

ConnectionManager cmflatFile;

ConnectionName = DataPump.Connections.Add("OLEDB");

// Set stock properties.

ConnectionName.Name = "Remote Source";

ConnectionName.ConnectionString = SqlConnection;

//@."Provider=SQLNCLI;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=cce_control;Data Source=(wxp-9plyf1s);Auto Translate=False;";

// Add the Destination connection manager.

cmflatFile = DataPump.Connections.Add("FLATFILE");

// Set the stock properties.

cmflatFile.Properties["ConnectionString"].SetValue(cmflatFile, DestinationDataDirectory + DataFileName);

cmflatFile.Properties["Format"].SetValue(cmflatFile, "Delimited");

cmflatFile.Properties["DataRowsToSkip"].SetValue(cmflatFile, 0);

cmflatFile.Properties["ColumnNamesInFirstDataRow"].SetValue(cmflatFile, true);

cmflatFile.Properties["Name"].SetValue(cmflatFile, "FlatFileConnection");

cmflatFile.Properties["RowDelimiter"].SetValue(cmflatFile, "\r\n");

cmflatFile.Properties["TextQualifier"].SetValue(cmflatFile, "\"");

}

#endregion

#region AddDataFlowTask

/// <summary>

/// Adds a DataFlow task to the Executables collection of the package.

/// Retrieves the MainPipe object from the TaskHost and stores it in

/// the dataFlow member variable

/// </summary>

public MainPipe AddDataFlowTask(Package DataPump)

{

TaskHost th = DataPump.Executables.Add("DTS.Pipeline") as TaskHost;

th.Name = "DataFlow";

th.Description = "The DataFlow task in the package.";

MainPipe dataFlow = th.InnerObject as MainPipe;

ComponentEvents componentEvents = new ComponentEvents();

dataFlow.Events = componentEvents as wrap.IDTSComponentEvents90;

return dataFlow;

}

#endregion

#region AddOLEDBSource

public IDTSComponentMetaData90 AddOLEDBSource(Microsoft.SqlServer.Dts.Pipeline.Wrapper.MainPipe dataFlow, Package DataPump, string strSourceQuery)

{

// Declare and Add the component to the dataFlow metadata collection

IDTSComponentMetaData90 OLEDBsource;

OLEDBsource = dataFlow.ComponentMetaDataCollection.New();

// Set the common properties

OLEDBsource.ComponentClassID = "DTSAdapter.OLEDBSource";

OLEDBsource.Name = "cce_control";

OLEDBsource.Description = "Remote Source Server";

// Create an instance of the component

CManagedComponentWrapper instance = OLEDBsource.Instantiate();

instance.ProvideComponentProperties();

// Associate the runtime ConnectionManager with the component

OLEDBsource.RuntimeConnectionCollection[0].ConnectionManagerID = DataPump.Connections["Remote Source"].ID;

OLEDBsource.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(DataPump.Connections["Remote Source"]);

instance.SetComponentProperty("SqlCommand", strSourceQuery);

instance.SetComponentProperty("AccessMode", 2);

instance.AcquireConnections(null);

instance.ReinitializeMetaData();

instance.ReleaseConnections();

return OLEDBsource;

}

#endregion

#region AddFlatFileDestination

public IDTSComponentMetaData90 AddFlatFileDestination(Microsoft.SqlServer.Dts.Pipeline.Wrapper.MainPipe dataFlow, Package DataPump, IDTSComponentMetaData90 OLEDBsource)

{

// Declare and Add the component to the dataFlow metadata collection

IDTSComponentMetaData90 flatfileDestination;

flatfileDestination = dataFlow.ComponentMetaDataCollection.New();

// Set the common properties

flatfileDestination.ComponentClassID = "DTSAdapter.FlatFileDestination";

flatfileDestination.Name = "FlatFileDestination";

flatfileDestination.Description = "Flat file destination";

// Create an instance of the component

CManagedComponentWrapper instance = flatfileDestination.Instantiate();

instance.ProvideComponentProperties();

// Associate the runtime ConnectionManager with the component

flatfileDestination.RuntimeConnectionCollection[0].ConnectionManagerID

= DataPump.Connections["FlatFileConnection"].ID;

flatfileDestination.RuntimeConnectionCollection[0].ConnectionManager

= DtsConvert.ToConnectionManager90(DataPump.Connections["FlatFileConnection"]);

// Map a path between the Sort transformation component to the FlatFileDestination

dataFlow.PathCollection.New().AttachPathAndPropagateNotifications(OLEDBsource.OutputCollection[0], flatfileDestination.InputCollection[0]);

// Add columns to the FlatFileConnectionManager

AddColumnsToFlatFileConnection(DataPump, flatfileDestination);

// Acquire the connection, reinitialize the metadata,

// map the columns, then release the connection.

instance.AcquireConnections(null);

instance.ReinitializeMetaData();

MapFlatFileDestinationColumns(flatfileDestination);

instance.ReleaseConnections();

return flatfileDestination;

}

#endregion

#region AddColumnsToFlatFileConnection

public void AddColumnsToFlatFileConnection(Package DataPump, IDTSComponentMetaData90 flatfileDestination)

{

wrap.IDTSConnectionManagerFlatFile90 ff = null;

foreach (ConnectionManager cm in DataPump.Connections)

{

if (cm.Name == "FlatFileConnection")

{

ff = cm.InnerObject as wrap.IDTSConnectionManagerFlatFile90;

DtsConvert.ToConnectionManager90(cm);

}

int count = ff.Columns.Count;

// if the connection manager is null here, then we have a problem

if (ff != null)

{

// Get the upstream columns

IDTSVirtualInputColumnCollection90 vColumns = flatfileDestination.InputCollection[0].GetVirtualInput().VirtualInputColumnCollection;

for (int cols = 0; cols < vColumns.Count; cols++)

{

wrap.IDTSConnectionManagerFlatFileColumn90 col = ff.Columns.Add();

// If this is the last column, set the delimiter to CRLF.

// Otherwise keep the delimiter as ",".

if (cols == vColumns.Count - 1)

{

col.ColumnDelimiter = "\r\n";

}

else

{

col.ColumnDelimiter = @.",";

}

col.ColumnType = "Delimited";

col.DataType = vColumns[cols].DataType;

col.DataPrecision = vColumns[cols].Precision;

col.DataScale = vColumns[cols].Scale;

wrap.IDTSName90 name = col as wrap.IDTSName90;

name.Name = vColumns[cols].Name;

}

#endregion

#region MapFlatFileDestination Columns

public void MapFlatFileDestinationColumns(IDTSComponentMetaData90 flatfileDestination)

{

CManagedComponentWrapper wrp = flatfileDestination.Instantiate();

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

foreach (IDTSVirtualInputColumn90 vColumn in vInput.VirtualInputColumnCollection)

{

wrp.SetUsageType(flatfileDestination.InputCollection[0].ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY);

}

// For each column in the input collection

// find the corresponding external metadata column.

foreach (IDTSInputColumn90 col in flatfileDestination.InputCollection[0].InputColumnCollection)

{

IDTSExternalMetadataColumn90 exCol = flatfileDestination.InputCollection[0].ExternalMetadataColumnCollection[col.Name];

wrp.MapInputColumn(flatfileDestination.InputCollection[0].ID, col.ID, exCol.ID);

}

#endregion

#region Exec_DataPump--This is just the execution where i get values from like server name etc and create the package and execute it.

public Boolean DataPump(string SqlConnection, string strSourceQuery)

{

Package DataPump;

PackageEvents packageEvents;

MainPipe dataFlow;

IDTSComponentMetaData90 OLEDBsource, flatfileDestination;

packageEvents = new PackageEvents();

DataPump = CreatePackage("SSISPackage", "The package for the Flatfile pump");

AddConnectionManagers(DataPump, SqlConnection);

dataFlow = AddDataFlowTask(DataPump);

OLEDBsource = AddOLEDBSource(dataFlow, DataPump, strSourceQuery);

flatfileDestination = AddFlatFileDestination(dataFlow, DataPump, OLEDBsource);

DTSExecResult status = DataPump.Validate(null, null, packageEvents, null);

if (mblnDebug)

{

Application dts = new Application();

dts.SaveToXml(mstrDTSXFile, DataPump, packageEvents);

}

foreach (Variable v in DataPump.Variables)

{

if (v.Name.ToUpper() == "EXECUTIONINSTANCEGUID")

{

break;

}

if (status == DTSExecResult.Success)

{

DTSExecResult result = DataPump.Execute(null, null, packageEvents, null, null);

foreach (Variable v in DataPump.Variables)

{

Console.WriteLine(v.Name.ToUpper());

}

return true;

}

#endregion

}

Programmatically Created SSIS Package, CSV file to OLDDB (SQLSever 2005)

Hi everyone,

I wanted to thank everyone for posting a ton of valuable information in these forums. I also want to thank all the moderators that have been replying with really insightful help!

I am trying to programmatically create an SSIS package to take .CSV data and put it into a SQL Server 2005. I am assuming that this is pretty common scenario.

I have used many of the examples in this forum as well as heavily borrowing from this example http://www.codeproject.com/csharp/Digging_SSIS_object_model.asp written by Moim Hossain.

I can get my package to create and execute properly but no data is being written to the SQL Server table. This has puzzled me for the last 2 days!

I know the issue isnt with the server itself because I tested it by graphically creating a test SSIS package and it transfers the .CSV data to the table perfectly.

Would anyone know why this would happen? The Execution results are returning success but no data is written to the table!

Could anyone please provide insight as to what my issue may be?

Thanks in advance!

Code Snippet

using System;
using System.IO;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Text;
using Microsoft.SqlServer.Dts.Runtime;
using PipeLineWrapper = Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using RuntimeWrapper = Microsoft.SqlServer.Dts.Runtime.Wrapper;

namespace SumCodeApp
{
class SumCodeApp
{
// Variables.
private Package package;
private ConnectionManager flatFileConnectionManager;
private ConnectionManager destinationDatabaseConnectionManager;
private Executable dataFlowTask;
private List<String> srcColumns;

int file_count;
SqlConnection connection;

String folder_path;
String username;
String password;
String DB_server;
String catalog;

// Default Constructor.
public SumCodeApp()
{
}

// Constructor taking in user info.
public SumCodeApp(String folder_path, String username, String password,
String DB_server, String catalog)
{
this.folder_path = folder_path;
this.username = username;
this.password = password;
this.DB_server = DB_server;
this.catalog = catalog;
}

private void CreatePackage()
{
package = new Package();
package.CreationDate = DateTime.Now;
package.ProtectionLevel = DTSProtectionLevel.DontSaveSensitive;
package.Name = "SumCode Package";
package.Description = "Upload the SumCode files to the database";
package.DelayValidation = true;
package.PackageType = DTSPackageType.DTSDesigner90;
}

private void CreateFlatFileConnection()
{
String flatFileName = ".\01105.csv";
String flatFileMoniker = "FLATFILE";
flatFileConnectionManager = package.Connections.Add(flatFileMoniker);
flatFileConnectionManager.Name = "SSIS Connection Manager for Files";
flatFileConnectionManager.Description = String.Concat("SSIS Connection Manager");
flatFileConnectionManager.ConnectionString = flatFileName;

// Set some common properties of the connection manager object.
//flatFileConnectionManager.Properties["ColumnNamesInFirstRow"].SetValue(flatFileConnectionManager, false);
flatFileConnectionManager.Properties["Format"].SetValue(flatFileConnectionManager, "Delimited");
flatFileConnectionManager.Properties["TextQualifier"].SetValue(flatFileConnectionManager, "\"");
flatFileConnectionManager.Properties["RowDelimiter"].SetValue(flatFileConnectionManager, "\r\n");
flatFileConnectionManager.Properties["DataRowsToSkip"].SetValue(flatFileConnectionManager, 0);

// Create the source columns into the connection manager.
CreateSourceColumns();
}

private void CreateSourceColumns()
{
// Get the actual connection manager instance
RuntimeWrapper.IDTSConnectionManagerFlatFile90 flatFileConnection = flatFileConnectionManager.InnerObject as RuntimeWrapper.IDTSConnectionManagerFlatFile90;

RuntimeWrapper.IDTSConnectionManagerFlatFileColumn90 column;
RuntimeWrapper.IDTSName90 name;

// Fill the source column collection.
srcColumns = new List<String>();
srcColumns.Add("CreateDate");
srcColumns.Add("CorpID");
srcColumns.Add("SumCodeID");
srcColumns.Add("Priority");
srcColumns.Add("SumCodeAbv");
srcColumns.Add("SumCodeDesc");
srcColumns.Add("SumCodeGroupID");

foreach (String colName in srcColumns)
{
column = flatFileConnection.Columns.Add();
if (srcColumns.IndexOf(colName) == (srcColumns.Count - 1))
//column.ColumnDelimiter = "\r\n";
column.ColumnDelimiter = "{CR}{LF}";
else
//column.ColumnDelimiter = ",";
column.ColumnDelimiter = "Comma {,}";

name = (RuntimeWrapper.IDTSName90)column;
name.Name = colName;

column.TextQualified = true;
column.ColumnType = "Delimited";
column.DataType = Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_STR;
column.ColumnWidth = 0;
column.MaximumWidth = 255;
column.DataPrecision = 0;
column.DataScale = 0;

}

private void CreateDestinationDatabaseConnection()
{
destinationDatabaseConnectionManager = package.Connections.Add("OLEDB");
destinationDatabaseConnectionManager.Name = "Destination Connection - SumCodeCorpGroup";
destinationDatabaseConnectionManager.Description = "Connection to the temporary table SumCodCorpGroup";
destinationDatabaseConnectionManager.ConnectionString = "Data Source=DIVWL-356KCB1;Initial Catalog=SumCode;Provider=SQLOLEDB;Persist Security Info=True;User ID=sum;Password=code";
}

public class Column
{
private String name;
private Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType dataType;
private int length;
private int precision;
private int scale;
private int codePage = 0;

public String Name
{
get { return name; }
set { name = value; }
}

public Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType DataType
{
get { return dataType; }
set { dataType = value; }
}

public int Length
{
get { return length; }
set { length = value; }
}

public int Precision
{
get { return precision; }
set { precision = value; }
}

public int Scale
{
get { return scale; }
set { scale = value; }
}

public int CodePage
{
get { return codePage; }
set { codePage = value; }
}
}

private Column GetTargetColumnInfo(string sourceColumnName)
{
Column cl = new Column();
if (sourceColumnName.Contains("CreateDate"))
{
cl.Name = "CreateDate";
cl.DataType = Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_STR;
cl.Precision = 0;
cl.Scale = 0;
cl.Length = 255;
cl.CodePage = 1252;
}
else if (
sourceColumnName.Contains("CorpID"))
{
cl.Name = "CorpID";
cl.DataType = Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_STR;
cl.Precision = 0;
cl.Scale = 0;
cl.Length = 255;
cl.CodePage = 1252;
}
else if (sourceColumnName.Contains("SumCodeID"))
{
cl.Name = "SumCodeID";
cl.DataType = Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_STR;
cl.Precision = 0;
cl.Scale = 0;
cl.Length = 255;
cl.CodePage = 1252;
}
else if (sourceColumnName.Contains("Priority"))
{
cl.Name = "Priority";
cl.DataType = Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_STR;
cl.Precision = 0;
cl.Scale = 0;
cl.Length = 255;
cl.CodePage = 1252;
}
else if (sourceColumnName.Contains("SumCodeAbv"))
{
cl.Name = "SumCodeAbv";
cl.DataType = Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_STR;
cl.Precision = 0;
cl.Scale = 0;
cl.Length = 255;
cl.CodePage = 1252;
}
else if (sourceColumnName.Contains("SumCodeDesc"))
{
cl.Name = "SumCodeDesc";
cl.DataType = Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_STR;
cl.Precision = 0;
cl.Scale = 0;
cl.Length = 255;
cl.CodePage = 1252;
}
else if (sourceColumnName.Contains("SumCodeGroupID"))
{
cl.Name = "SumCodeGroupID";
cl.DataType = Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_STR;
cl.Precision = 0;
cl.Scale = 0;
cl.Length = 255;
cl.CodePage = 1252;
}
return cl;
}

private void CreateDataFlowTask()
{
String dataFlowTaskMoniker = "DTS.Pipeline";
dataFlowTask = package.Executables.Add(dataFlowTaskMoniker);

}

public void ImportFile(String directory_path)
{
// Create the package.
CreatePackage();

// Create Flat File Source Connection.
CreateFlatFileConnection();

// Create Database Destination Connection.
CreateDestinationDatabaseConnection();

// Create DataFlowTask.
CreateDataFlowTask();

// Create the DataFlowTask
PipeLineWrapper.IDTSComponentMetaData90 sourceComponent = ((dataFlowTask as TaskHost).InnerObject as PipeLineWrapper.MainPipe).ComponentMetaDataCollection.New();
sourceComponent.Name = "Source File Component";
sourceComponent.ComponentClassID = "DTSAdapter.FlatFileSource";

PipeLineWrapper.CManagedComponentWrapper managedFlatFileInstance = sourceComponent.Instantiate();
managedFlatFileInstance.ProvideComponentProperties();
sourceComponent.RuntimeConnectionCollection[0].ConnectionManagerID = flatFileConnectionManager.ID;
sourceComponent.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(flatFileConnectionManager);

managedFlatFileInstance.AcquireConnections(null);
managedFlatFileInstance.ReinitializeMetaData();

Dictionary<String, int> outputColumnLineageIDs = new Dictionary<String, int>();
PipeLineWrapper.IDTSExternalMetadataColumn90 exOutColumn = null;

foreach (PipeLineWrapper.IDTSOutputColumn90 outColumn in sourceComponent.OutputCollection[0].OutputColumnCollection)
{
exOutColumn = sourceComponent.OutputCollection[0].ExternalMetadataColumnCollection[outColumn.Name];
managedFlatFileInstance.MapOutputColumn(sourceComponent.OutputCollection[0].ID, outColumn.ID, exOutColumn.ID, true);
outputColumnLineageIDs.Add(outColumn.Name, outColumn.ID);
}
managedFlatFileInstance.ReleaseConnections();

String a = sourceComponent.RuntimeConnectionCollection[0].Name.ToString();
String b = sourceComponent.OutputCollection[0].Name;
String c = sourceComponent.OutputCollection[0].Description;
String d = sourceComponent.OutputCollection[0].OutputColumnCollection.Count.ToString();

// Create DataFlowTask Destination Component.
PipeLineWrapper.IDTSComponentMetaData90 destinationComponent = ((dataFlowTask as TaskHost).InnerObject as PipeLineWrapper.MainPipe).ComponentMetaDataCollection.New();
destinationComponent.Name = "OLEDB SQL Connection";
destinationComponent.ComponentClassID = "DTSAdapter.OLEDBDestination";

PipeLineWrapper.CManagedComponentWrapper managedOleInstance = destinationComponent.Instantiate();
managedOleInstance.ProvideComponentProperties();

// Create a path and attach the output of the source to the input of the destination.
PipeLineWrapper.IDTSPath90 path = ((dataFlowTask as TaskHost).InnerObject as PipeLineWrapper.MainPipe).PathCollection.New();
path.AttachPathAndPropagateNotifications(sourceComponent.OutputCollection[0], destinationComponent.InputCollection[0]);

destinationComponent.RuntimeConnectionCollection[0].ConnectionManagerID = destinationDatabaseConnectionManager.ID;
destinationComponent.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(destinationDatabaseConnectionManager);

managedOleInstance.SetComponentProperty("AccessMode", 0);
managedOleInstance.SetComponentProperty("OpenRowset", "[SumCode].[dbo].[SumCodeCorpGroup]");
managedOleInstance.SetComponentProperty("AlwaysUseDefaultCodePage", false);
managedOleInstance.SetComponentProperty("DefaultCodePage", 1252);
managedOleInstance.SetComponentProperty("FastLoadKeepIdentity", false); // Fast load
managedOleInstance.SetComponentProperty("FastLoadKeepNulls", false);
managedOleInstance.SetComponentProperty("FastLoadMaxInsertCommitSize", 0);
managedOleInstance.SetComponentProperty("FastLoadOptions","TABLOCK,CHECK_CONSTRAINTS");

managedOleInstance.AcquireConnections(null);
managedOleInstance.ReinitializeMetaData();

PipeLineWrapper.IDTSInput90 input = destinationComponent.InputCollection[0];
PipeLineWrapper.IDTSVirtualInput90 vInput = input.GetVirtualInput();

foreach (PipeLineWrapper.IDTSVirtualInputColumn90 vColumn in vInput.VirtualInputColumnCollection)
{
//if (outputColumnLineageIDs.ContainsKey(vColumn.LineageID.ToString()))
//{
managedOleInstance.SetUsageType(input.ID, vInput, vColumn.LineageID, Microsoft.SqlServer.Dts.Pipeline.Wrapper.DTSUsageType.UT_READONLY);
//}
}

List<String> tmp = new List<String>();
foreach(PipeLineWrapper.IDTSInputColumn90 inc in destinationComponent.InputCollection[0].InputColumnCollection)
{
tmp.Add(inc.Name);
}

PipeLineWrapper.IDTSExternalMetadataColumn90 exColumn;
foreach (PipeLineWrapper.IDTSInputColumn90 inColumn in destinationComponent.InputCollection[0].InputColumnCollection)
{
exColumn = destinationComponent.InputCollection[0].ExternalMetadataColumnCollection[inColumn.Name];
Column mappedColumn = GetTargetColumnInfo(exColumn.Name);
String destName = mappedColumn.Name;

exColumn.Name = destName;

managedOleInstance.MapInputColumn(destinationComponent.InputCollection[0].ID, inColumn.ID, exColumn.ID);
}

managedOleInstance.ReleaseConnections();

DTSExecResult result = package.Execute();
a = "0";
}

}
}

A good first step in troubleshooting these types of problems: Save the file out to a DTSX (using the Application.SaveToXml method, then open and run it in BIDS. That might give you a better idea what's going on.

|||

I include a save to file by default, but have it for Debug confiurations only -

Code Snippet

#if DEBUG

// Save package to disk, DEBUG only

new Application().SaveToXml(@."C:\Temp\" + package.Name, package, null);

#endif

You would also be better off using the overloaded Execute method that enables you to pass in IDTSEvents amongst other things. This way you can capture error events in your application, otherwise you have no idea of what has one wrong, which when deployed will be an issue I guess. The save to file trick is easier when developing the code as you get the power of the designer and debugger, rather than just events you capture.

Friday, March 30, 2012

Proprties not imported with Table

Wednesday, March 28, 2012

Property AnsiNullsStatus is not available for UserDefinedFunction

Property AnsiNullsStatus is not available for UserDefinedFunction

Property AnsiNullsStatus is not available for UserDefinedFunction

Monday, March 26, 2012

Proper indexs against query and optimization

Proper indexs against query and optimization

Proper indexs against query and optimization

Friday, March 23, 2012

Propagate NEW Table to Anon Merge Replicas

Wednesday, March 21, 2012

Project_Central

Project_Central

Project_Central

Friday, March 9, 2012

Programmatically Data Conversion component creation

Programmatically Data Conversion component creation

Programmatically Created SSIS Package, CSV file to OLDDB (SQLSever 2005)

Program Connection

Blog Archive

About Me