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

PROGRESS import of field freezes SQL Server

hi,
I've got a SQL 2005 with an odbc connection to a PROGRESS database. We can
transfer data from the progress to the sql but there is 1 field we can not
transfer. This field, proclaimed by the PROGRESS administration, is a field
wich can contain an empty string or up to 8 characters.
But when we try to do a 'select problemfield, goodfield1,goodfield2 from
pub.table' in the import and export wizard and try to preview this select,
SQL Server freezes for a loooooong time resulting in an error "defined by
component".
We have tried to put the problem field into the select clause, with the
where clause without the where clause... always the same result. The datatyp
e
that we use is a varchar(8) or more but it always freezes.
Does anyone have an idea?
Note: when progress exports to a flat file and we import that flat file,
this works without any problems. So instead of using the odbc connection to
transfer the data overnight, we would like to use flat files and import them
with Integration S. Good solution or not?Ok,
We managed to get the import; but via a flatfile source not with the odbc
connector. Wat went wrong I cant say, it wasn't possible to import the field
s
via a ODBC connection, the flatfime however imported like nothing (although
it where millions of rows)
We have to do a lot of cleansing, the progress software sets a ? in fields
that are not filled in. In this case, implicit conversions to smalldatatime
fails. Now that we have everything in nvarchar, I'm sure SSIS will bring us
where we want.
Thx,
"Philippe" wrote:

> hi,
> I've got a SQL 2005 with an odbc connection to a PROGRESS database. We can
> transfer data from the progress to the sql but there is 1 field we can not
> transfer. This field, proclaimed by the PROGRESS administration, is a fiel
d
> wich can contain an empty string or up to 8 characters.
> But when we try to do a 'select problemfield, goodfield1,goodfield2 from
> pub.table' in the import and export wizard and try to preview this select,
> SQL Server freezes for a loooooong time resulting in an error "defined by
> component".
> We have tried to put the problem field into the select clause, with the
> where clause without the where clause... always the same result. The datat
ype
> that we use is a varchar(8) or more but it always freezes.
> Does anyone have an idea?
> Note: when progress exports to a flat file and we import that flat file,
> this works without any problems. So instead of using the odbc connection t
o
> transfer the data overnight, we would like to use flat files and import th
em
> with Integration S. Good solution or not?

Programming SSIS - Exception 0xC0204006

Hello. I am attempting to use SSIS to import a table from MS Access in to SQL Server. However, when i set the destination component properties I get the following error:

Exception from HRESULT: 0xC0204006

The table exists within the destination database and if I comment the line out, it is able to acquire the connection.Anyone have any ideas?

Thanks

ConnectionManager connMgr;
ConnectionManager connMgr1;

//Create the package
Microsoft.SqlServer.Dts.Runtime.Package package = new Microsoft.SqlServer.Dts.Runtime.Package();

//Create to connections to the package
Connections packageConns = package.Connections;

connMgr1 = package.Connections.Add("OLEDB");
connMgr1.ConnectionString = accessModelConnectString;
connMgr1.Name = "OLEDB ConnectionManager";

connMgr = package.Connections.Add("OLEDB");
connMgr.ConnectionString = sqlConnectionString;
connMgr.Name = "OLEDB ConnectionManager1";

//Add a dataflow task to the package.
MainPipe dataFlowTask = ((Microsoft.SqlServer.Dts.Runtime.TaskHost)package.Executables.Add("DTS.Pipeline")).InnerObject as MainPipe;

IDTSComponentMetaData90 sourceComponent = dataFlowTask.ComponentMetaDataCollection.New();
sourceComponent.ComponentClassID = "DTSAdapter.OleDbSource.1";
sourceComponent.Name = "yyy";

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

// Initialize the component
instance.ProvideComponentProperties();

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

// Set the custom properties.
instance.SetComponentProperty("AccessMode", 2);
instance.SetComponentProperty("SqlCommand", "SELECT Test FROM Test");

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

IDTSComponentMetaData90 destinationComponent = dataFlowTask.ComponentMetaDataCollection.New();
destinationComponent.ComponentClassID = "DTSAdapter.OleDBDestination.1";
destinationComponent.Name = "xxx";

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

// Initialize the component
instance1.ProvideComponentProperties();

// Specify the connection manager.
if (destinationComponent.RuntimeConnectionCollection.Count > 0)
{
destinationComponent.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(package.Connections[1]);
destinationComponent.RuntimeConnectionCollection[0].ConnectionManagerID = package.Connections[1].ID;
}

instance1.SetComponentProperty("AccessMode", 3);
instance1.SetComponentProperty("OpenRowSet", "[PedestrianFlow].[dbo].[OLE DB Destination]");

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

// Create the path.
IDTSPath90 path = dataFlowTask.PathCollection.New();
path.AttachPathAndPropagateNotifications(sourceComponent.OutputCollection[0],destinationComponent.InputCollection[0]);

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

// Iterate through the virtual column collection.
foreach (IDTSVirtualInputColumn90 vColumn in vInput.VirtualInputColumnCollection)
{
string y = vColumn.Name;
// Call the SetUsageType method of the design time instance of the component.
instance1.SetUsageType(input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY);
}

Microsoft.SqlServer.Dts.Runtime.DTSExecResult result = package.Execute();

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

Can anybody help? I am getting nowhere fast.

Thanks.|||

This explains it quite well I think, but feel free to update-

0xC0204006
(http://wiki.sqlis.com/default.aspx/SQLISWiki/0xC0204006.html)

Check the property you are using, it does not exist.

|||... case sensitive name is what I meant to add.|||

Thankyou, you are a life saver. I can't believe I missed that, I have been staring at that code for hours.

Thanks again.|||

cjturner wrote:

Thankyou, you are a life saver. I can't believe I missed that, I have been staring at that code for hours.

Thanks again.

Please be sure to mark the appropriate post as the answer to your question.

Programmatically exporting and importing data SQL Server 2000 and

What should I use to import and export data from, to, and accross SQL Server
2000 and 2005. Need to export to a file and then import from a file.
Currently my program uses SQLDMO, which is very easy to use, powerfull, and
works great with SQL Server 2000. But it is not supported by SQL Server
2005, also, 2005's SMO doesn't have such functionality. SQL Server 2005's
Integration Services (SSIS) is availbale for this, but don't know if it
supported in 2000. There is also a 'bcp' command, but this isn't even part
of SQL Server 2005 Enterprise 64-Bit edition, so I don't want to make client
s
install extra components for this. I beleive it is standard on Server 2000
though. I tried to play around with DTS, or Data Transformation
Import/Export Wizard, is this the only thing I can use that will work the
same way for SQL Server 2000 and 2005.NoFear wrote:
> What should I use to import and export data from, to, and accross SQL
> Server 2000 and 2005. Need to export to a file and then import from
> a file. Currently my program uses SQLDMO, which is very easy to use,
> powerfull, and works great with SQL Server 2000. But it is not
> supported by SQL Server 2005, also, 2005's SMO doesn't have such
> functionality. SQL Server 2005's Integration Services (SSIS) is
> availbale for this, but don't know if it supported in 2000. There is
> also a 'bcp' command, but this isn't even part of SQL Server 2005
> Enterprise 64-Bit edition, so I don't want to make clients install
> extra components for this. I beleive it is standard on Server 2000
> though. I tried to play around with DTS, or Data Transformation
> Import/Export Wizard, is this the only thing I can use that will work
> the same way for SQL Server 2000 and 2005.
SQL-DMO is supported on SQL 2005.
David Gugick - SQL Server MVP
Quest Software

Programmatically exporting and importing data SQL Server 2000 and

What should I use to import and export data from, to, and accross SQL Server
2000 and 2005. Need to export to a file and then import from a file.
Currently my program uses SQLDMO, which is very easy to use, powerfull, and
works great with SQL Server 2000. But it is not supported by SQL Server
2005, also, 2005's SMO doesn't have such functionality. SQL Server 2005's
Integration Services (SSIS) is availbale for this, but don't know if it
supported in 2000. There is also a 'bcp' command, but this isn't even part
of SQL Server 2005 Enterprise 64-Bit edition, so I don't want to make clients
install extra components for this. I beleive it is standard on Server 2000
though. I tried to play around with DTS, or Data Transformation
Import/Export Wizard, is this the only thing I can use that will work the
same way for SQL Server 2000 and 2005.NoFear wrote:
> What should I use to import and export data from, to, and accross SQL
> Server 2000 and 2005. Need to export to a file and then import from
> a file. Currently my program uses SQLDMO, which is very easy to use,
> powerfull, and works great with SQL Server 2000. But it is not
> supported by SQL Server 2005, also, 2005's SMO doesn't have such
> functionality. SQL Server 2005's Integration Services (SSIS) is
> availbale for this, but don't know if it supported in 2000. There is
> also a 'bcp' command, but this isn't even part of SQL Server 2005
> Enterprise 64-Bit edition, so I don't want to make clients install
> extra components for this. I beleive it is standard on Server 2000
> though. I tried to play around with DTS, or Data Transformation
> Import/Export Wizard, is this the only thing I can use that will work
> the same way for SQL Server 2000 and 2005.
SQL-DMO is supported on SQL 2005.
--
David Gugick - SQL Server MVP
Quest Software

Programmatically exporting and importing data SQL Server 2000 and

What should I use to import and export data from, to, and accross SQL Server
2000 and 2005. Need to export to a file and then import from a file.
Currently my program uses SQLDMO, which is very easy to use, powerfull, and
works great with SQL Server 2000. But it is not supported by SQL Server
2005, also, 2005's SMO doesn't have such functionality. SQL Server 2005's
Integration Services (SSIS) is availbale for this, but don't know if it
supported in 2000. There is also a 'bcp' command, but this isn't even part
of SQL Server 2005 Enterprise 64-Bit edition, so I don't want to make clients
install extra components for this. I beleive it is standard on Server 2000
though. I tried to play around with DTS, or Data Transformation
Import/Export Wizard, is this the only thing I can use that will work the
same way for SQL Server 2000 and 2005.
NoFear wrote:
> What should I use to import and export data from, to, and accross SQL
> Server 2000 and 2005. Need to export to a file and then import from
> a file. Currently my program uses SQLDMO, which is very easy to use,
> powerfull, and works great with SQL Server 2000. But it is not
> supported by SQL Server 2005, also, 2005's SMO doesn't have such
> functionality. SQL Server 2005's Integration Services (SSIS) is
> availbale for this, but don't know if it supported in 2000. There is
> also a 'bcp' command, but this isn't even part of SQL Server 2005
> Enterprise 64-Bit edition, so I don't want to make clients install
> extra components for this. I beleive it is standard on Server 2000
> though. I tried to play around with DTS, or Data Transformation
> Import/Export Wizard, is this the only thing I can use that will work
> the same way for SQL Server 2000 and 2005.
SQL-DMO is supported on SQL 2005.
David Gugick - SQL Server MVP
Quest Software

Programmatically creating SSIS package

Hi guys,

I was intended to write a program that will create a SSIS package which will import data from a CSV file to the SQL server 2005. But I did not find any good example for this into the internet. I found some example which exports data from SQL server 2005 to CSV files. And following those examples I have tried to write my own. But I am facing some problem with that. What I am doing here is creating two connection manager objects, one for Flat file and another for OLEDB. And create a data flow task that has two data flow component, one for reading source and another for writing to destination. While debugging I can see that after invoking the ReinitializedMetaData() for the flat file source data flow component, there is not output column found. Why it is not fetching the output columns from the CSV file? And after that when it invokes the ReinitializedMetaData() for the destination data flow component it simply throws exception.

Can any body help me to get around this problem? Even can anyone give me any link where I can find some useful article to accomplish this goal?

I am giving my code here too.

I will appreciate any kind of suggestion on this.

Code snippet:

public void CreatePackage()

{

string executeSqlTask = typeof(ExecuteSQLTask).AssemblyQualifiedName;

Package pkg = new Package();

pkg.PackageType = DTSPackageType.DTSDesigner90;

ConnectionManager oledbConnectionManager = CreateOLEDBConnection(pkg);

ConnectionManager flatfileConnectionManager =

CreateFileConnection(pkg);

// creating the SQL Task for table creation

Executable sqlTaskExecutable = pkg.Executables.Add(executeSqlTask);

ExecuteSQLTask execSqlTask = (sqlTaskExecutable as Microsoft.SqlServer.Dts.Runtime.TaskHost).InnerObject as ExecuteSQLTask;

execSqlTask.Connection = oledbConnectionManager.Name;

execSqlTask.SqlStatementSource =

"CREATE TABLE [MYDATABASE].[dbo].[MYTABLE] \n ([NAME] NVARCHAR(50),[AGE] NVARCHAR(50),[GENDER] NVARCHAR(50)) \nGO";

// creating the Data flow task

Executable dataFlowExecutable = pkg.Executables.Add("DTS.Pipeline.1");

TaskHost pipeLineTaskHost = (TaskHost)dataFlowExecutable;

MainPipe dataFlowTask = (MainPipe)pipeLineTaskHost.InnerObject;

// Put a precedence constraint between the tasks.

PrecedenceConstraint pcTasks = pkg.PrecedenceConstraints.Add(sqlTaskExecutable, dataFlowExecutable);

pcTasks.Value = DTSExecResult.Success;

pcTasks.EvalOp = DTSPrecedenceEvalOp.Constraint;

// Now adding the data flow components

IDTSComponentMetaData90 sourceDataFlowComponent = dataFlowTask.ComponentMetaDataCollection.New();

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 managedInstance = sourceDataFlowComponent.Instantiate();

managedInstance.ProvideComponentProperties();

sourceDataFlowComponent.

RuntimeConnectionCollection[0].ConnectionManagerID = flatfileConnectionManager.ID;

sourceDataFlowComponent.

RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(flatfileConnectionManager);

managedInstance.AcquireConnections(null);

managedInstance.ReinitializeMetaData();

managedInstance.ReleaseConnections();

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

IDTSOutput90 output = sourceDataFlowComponent.OutputCollection[0];

// Here I dont find any columns at all..why?

// Now adding the data flow components

IDTSComponentMetaData90 destinationDataFlowComponent = dataFlowTask.ComponentMetaDataCollection.New();

destinationDataFlowComponent.Name =

"Destination Oledb compoenent";

// Here is the component class id for Oledvb data

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);

// Set the custom properties.

managedOleInstance.SetComponentProperty("AccessMode", 2);

managedOleInstance.SetComponentProperty("OpenRowset", "[MYDATABASE].[dbo].[MYTABLE]");

managedOleInstance.AcquireConnections(null);

managedOleInstance.ReinitializeMetaData(); // Throws exception

managedOleInstance.ReleaseConnections();

// Create the path.

IDTSPath90 path = dataFlowTask.PathCollection.New(); path.AttachPathAndPropagateNotifications(sourceDataFlowComponent.OutputCollection[0],

destinationDataFlowComponent.InputCollection[0]);

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

IDTSInput90 input = destinationDataFlowComponent.InputCollection[0];

IDTSVirtualInput90 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);

}

DTSExecResult res = pkg.Execute();

}

public ConnectionManager CreateOLEDBConnection(Package p)

{

ConnectionManager ConMgr;

ConMgr = p.Connections.Add("OLEDB");

ConMgr.ConnectionString =

"Data Source=VSTS;Initial Catalog=MYDATABASE;Provider=SQLNCLI;Integrated Security=SSPI;Auto Translate=false;";

ConMgr.Name = "SSIS Connection Manager for Oledb";

ConMgr.Description = "OLE DB connection to the Test database.";

return ConMgr;

}

public ConnectionManager CreateFileConnection(Package p)

{

ConnectionManager connMgr;

connMgr = p.Connections.Add("FLATFILE");

connMgr.ConnectionString = @."D:\MyCSVFile.csv";

connMgr.Name = "SSIS Connection Manager for Files";

connMgr.Description = "Flat File connection";

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

connMgr.Properties["HeaderRowDelimiter"].SetValue(connMgr, Environment.NewLine);

return connMgr;

}

And my CSV files is as follows

NAME, AGE, GENDER

Jon,52,MALE

Linda, 26, FEMALE

Thats all. Thanks.

As mentioned earlier, this CSV to OLEDB package generator is not far from working.

First, the AccessMode on the OLEDB destination is set to the constant 2, which the "Sql Command" constant. Change that back to 0 ("Table or View").

FYI, the OLEDB destination's Access Mode enumeration is undocumented, but nevertheless can be found in the following dll: Microsoft SQL Server\DTS\PipelineComponents\OleDbDest.dll using a program like "PE Explorer".

Enum AccessMode;

AM_OPENROWSET = 0
AM_OPENROWSET_VARIABLE = 1
AM_SQLCOMMAND = 2
AM_OPENROWSET_FASTLOAD = 3
AM_OPENROWSET_FASTLOAD_VARIABLE = 4

Next, the target table doesn't exist , yet ReinitializeMetadata() for the OLEDB destination component will by default attempt to retrieve the target table's meta data. The table isn't there, so an exception is thrown. Therefore, as part of the driving program, you may wish to create the table temporarily (this table create is separate from the create in the Execute SQL Task), so that there is meta-data to reinitialize.

That should eliminate exceptions, but it doesn't mean the package will validate or execute successfully, just that you could save it to xml.

Then, add source columns for your flat file. SSIS does not materialize them for you (at least as far as I know), so the user needs to do perform that programmatically.|||

Hello jaegd,

At last I made it work today . Your tips tremendously helped me. After implementing your suggestions I had to do very little coding to make it working. Thanks a lot .

Anyway, as I am creating the source columns programmatically for the Flat file connection, I had to read the columns from the CSV file using .net IO functionality. Is there any way to accomplish this through SSIS APIs?

Thank you very much again.

Regards

Moim

|||

Hello,

How did you create the metadata for the destination?

Thanks in advance.

Cheers,

kix

Programmatically creating SSIS package

Hi guys,

I was intended to write a program that will create a SSIS package which will import data from a CSV file to the SQL server 2005. But I did not find any good example for this into the internet. I found some example which exports data from SQL server 2005 to CSV files. And following those examples I have tried to write my own. But I am facing some problem with that. What I am doing here is creating two connection manager objects, one for Flat file and another for OLEDB. And create a data flow task that has two data flow component, one for reading source and another for writing to destination. While debugging I can see that after invoking the ReinitializedMetaData() for the flat file source data flow component, there is not output column found. Why it is not fetching the output columns from the CSV file? And after that when it invokes the ReinitializedMetaData() for the destination data flow component it simply throws exception.

Can any body help me to get around this problem? Even can anyone give me any link where I can find some useful article to accomplish this goal?

I am giving my code here too.

I will appreciate any kind of suggestion on this.

Code snippet:

public void CreatePackage()

{

string executeSqlTask = typeof(ExecuteSQLTask).AssemblyQualifiedName;

Package pkg = new Package();

pkg.PackageType = DTSPackageType.DTSDesigner90;

ConnectionManager oledbConnectionManager = CreateOLEDBConnection(pkg);

ConnectionManager flatfileConnectionManager =

CreateFileConnection(pkg);

// creating the SQL Task for table creation

Executable sqlTaskExecutable = pkg.Executables.Add(executeSqlTask);

ExecuteSQLTask execSqlTask = (sqlTaskExecutable as Microsoft.SqlServer.Dts.Runtime.TaskHost).InnerObject as ExecuteSQLTask;

execSqlTask.Connection = oledbConnectionManager.Name;

execSqlTask.SqlStatementSource =

"CREATE TABLE [MYDATABASE].[dbo].[MYTABLE] \n ([NAME] NVARCHAR(50),[AGE] NVARCHAR(50),[GENDER] NVARCHAR(50)) \nGO";

// creating the Data flow task

Executable dataFlowExecutable = pkg.Executables.Add("DTS.Pipeline.1");

TaskHost pipeLineTaskHost = (TaskHost)dataFlowExecutable;

MainPipe dataFlowTask = (MainPipe)pipeLineTaskHost.InnerObject;

// Put a precedence constraint between the tasks.

PrecedenceConstraint pcTasks = pkg.PrecedenceConstraints.Add(sqlTaskExecutable, dataFlowExecutable);

pcTasks.Value = DTSExecResult.Success;

pcTasks.EvalOp = DTSPrecedenceEvalOp.Constraint;

// Now adding the data flow components

IDTSComponentMetaData90 sourceDataFlowComponent = dataFlowTask.ComponentMetaDataCollection.New();

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 managedInstance = sourceDataFlowComponent.Instantiate();

managedInstance.ProvideComponentProperties();

sourceDataFlowComponent.

RuntimeConnectionCollection[0].ConnectionManagerID = flatfileConnectionManager.ID;

sourceDataFlowComponent.

RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(flatfileConnectionManager);

managedInstance.AcquireConnections(null);

managedInstance.ReinitializeMetaData();

managedInstance.ReleaseConnections();

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

IDTSOutput90 output = sourceDataFlowComponent.OutputCollection[0];

// Here I dont find any columns at all..why?

// Now adding the data flow components

IDTSComponentMetaData90 destinationDataFlowComponent = dataFlowTask.ComponentMetaDataCollection.New();

destinationDataFlowComponent.Name =

"Destination Oledb compoenent";

// Here is the component class id for Oledvb data

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);

// Set the custom properties.

managedOleInstance.SetComponentProperty("AccessMode", 2);

managedOleInstance.SetComponentProperty("OpenRowset", "[MYDATABASE].[dbo].[MYTABLE]");

managedOleInstance.AcquireConnections(null);

managedOleInstance.ReinitializeMetaData(); // Throws exception

managedOleInstance.ReleaseConnections();

// Create the path.

IDTSPath90 path = dataFlowTask.PathCollection.New(); path.AttachPathAndPropagateNotifications(sourceDataFlowComponent.OutputCollection[0],

destinationDataFlowComponent.InputCollection[0]);

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

IDTSInput90 input = destinationDataFlowComponent.InputCollection[0];

IDTSVirtualInput90 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);

}

DTSExecResult res = pkg.Execute();

}

public ConnectionManager CreateOLEDBConnection(Package p)

{

ConnectionManager ConMgr;

ConMgr = p.Connections.Add("OLEDB");

ConMgr.ConnectionString =

"Data Source=VSTS;Initial Catalog=MYDATABASE;Provider=SQLNCLI;Integrated Security=SSPI;Auto Translate=false;";

ConMgr.Name = "SSIS Connection Manager for Oledb";

ConMgr.Description = "OLE DB connection to the Test database.";

return ConMgr;

}

public ConnectionManager CreateFileConnection(Package p)

{

ConnectionManager connMgr;

connMgr = p.Connections.Add("FLATFILE");

connMgr.ConnectionString = @."D:\MyCSVFile.csv";

connMgr.Name = "SSIS Connection Manager for Files";

connMgr.Description = "Flat File connection";

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

connMgr.Properties["HeaderRowDelimiter"].SetValue(connMgr, Environment.NewLine);

return connMgr;

}

And my CSV files is as follows

NAME, AGE, GENDER

Jon,52,MALE

Linda, 26, FEMALE

Thats all. Thanks.

As mentioned earlier, this CSV to OLEDB package generator is not far from working.

First, the AccessMode on the OLEDB destination is set to the constant 2, which the "Sql Command" constant. Change that back to 0 ("Table or View").

FYI, the OLEDB destination's Access Mode enumeration is undocumented, but nevertheless can be found in the following dll: Microsoft SQL Server\DTS\PipelineComponents\OleDbDest.dll using a program like "PE Explorer".

Enum AccessMode;

AM_OPENROWSET = 0
AM_OPENROWSET_VARIABLE = 1
AM_SQLCOMMAND = 2
AM_OPENROWSET_FASTLOAD = 3
AM_OPENROWSET_FASTLOAD_VARIABLE = 4

Next, the target table doesn't exist , yet ReinitializeMetadata() for the OLEDB destination component will by default attempt to retrieve the target table's meta data. The table isn't there, so an exception is thrown. Therefore, as part of the driving program, you may wish to create the table temporarily (this table create is separate from the create in the Execute SQL Task), so that there is meta-data to reinitialize.

That should eliminate exceptions, but it doesn't mean the package will validate or execute successfully, just that you could save it to xml.

Then, add source columns for your flat file. SSIS does not materialize them for you (at least as far as I know), so the user needs to do perform that programmatically.

|||

Hello jaegd,

At last I made it work today . Your tips tremendously helped me. After implementing your suggestions I had to do very little coding to make it working. Thanks a lot .

Anyway, as I am creating the source columns programmatically for the Flat file connection, I had to read the columns from the CSV file using .net IO functionality. Is there any way to accomplish this through SSIS APIs?

Thank you very much again.

Regards

Moim

|||

Hello,

How did you create the metadata for the destination?

Thanks in advance.

Cheers,

kix

Programmaing with SSIS Object model

Hi there,

Can anyone point me to some sample source codes or any articles that describes how I can programmatically create a package which will import data from a flatfile (csv) to Sql server database.

I know there is some example that describes exporting data from sql server to flatfiles. Anyway I have failed to accomplish my goal by following those examples.

If anyone have a code snippet to do that please help me with that.

Thanks in advance

Moim Hossain

If you want, post the code you have already to build the SSIS package (or a link to it). This response is not the code snippet you're looking for, but it sounds like the package builder code you already have (from the other post), is not far from working.|||

jaegd ,

Sorry I did not understand. Should I post my code to you?

I have already posted my code here into another posting. you can take a look on my code here http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1073656&SiteID=1

Can you tell me where I am going wrong?

Thanks

Moim

Programatic Import (Space delimited) import into SQL Server 2000

I am trying to find a way to do a programatic import of a space delimited
file into a SQL Server 2000 database.
The file is actually an IIS web log file. I can run through the file and
strip out the header information, I can even create a seperate file for each
change in header information is needed.
The problem becomes importing that file into the database. The log files are
on average 6MB each. The import will need to run daily on just a single log
file (after previous log files have been imported)
I do not want to use BULK INSERT because I have had a lot of inconsistant
problems with that functionality in the past.
Does anyone have any ideas?
John WycoffI would start with the DTS data import wizard. You can specify a text
file as input, and space as the column delimiter, and the package it
generates can be saved and modified later. It does assume that all
the rows are in the same format.
Roy Harvey
Beacon Falls, CT
On Thu, 20 Jul 2006 07:15:01 -0700, John Wycoff <John
Wycoff@.discussions.microsoft.com> wrote:
>I am trying to find a way to do a programatic import of a space delimited
>file into a SQL Server 2000 database.
>The file is actually an IIS web log file. I can run through the file and
>strip out the header information, I can even create a seperate file for each
>change in header information is needed.
>The problem becomes importing that file into the database. The log files are
>on average 6MB each. The import will need to run daily on just a single log
>file (after previous log files have been imported)
>I do not want to use BULK INSERT because I have had a lot of inconsistant
>problems with that functionality in the past.
>Does anyone have any ideas?
>John Wycoff|||Use DTS. If the file is 'space' delimited, or is fixed position, DTS can
handle the import. You can create a DTS 'Package' (the set of instructions),
and then schedule that Package to run when appropriate.
--
Arnie Rowland
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"John Wycoff" <John Wycoff@.discussions.microsoft.com> wrote in message
news:170BB6CA-D3E7-4BEA-A336-390BF6A3DF9E@.microsoft.com...
> I am trying to find a way to do a programatic import of a space delimited
> file into a SQL Server 2000 database.
> The file is actually an IIS web log file. I can run through the file and
> strip out the header information, I can even create a seperate file for
> each
> change in header information is needed.
> The problem becomes importing that file into the database. The log files
> are
> on average 6MB each. The import will need to run daily on just a single
> log
> file (after previous log files have been imported)
> I do not want to use BULK INSERT because I have had a lot of inconsistant
> problems with that functionality in the past.
> Does anyone have any ideas?
> John Wycoff|||The problem is that I do not know what the filenames will be. And the formats
will be different. The closest I can get is to run through the log files
generating a seperate log file for every different format. But I cannot
programattically change the DTS package everytime, nor can I tell the DTS
package to import every file in a specific directory.|||On Tue, 25 Jul 2006 06:08:02 -0700, John Wycoff
<JohnWycoff@.discussions.microsoft.com> wrote:
>The problem is that I do not know what the filenames will be. And the formats
>will be different. The closest I can get is to run through the log files
>generating a seperate log file for every different format. But I cannot
>programattically change the DTS package everytime, nor can I tell the DTS
>package to import every file in a specific directory.
I am still pretty unclear about what the files look like. How many
different tables is the data being loaded into? Do the headers have
any data you need? Is it one header per file? Or a header, followed
by several items, then another header and more items?
It IS possible to get DTS to import every file in a folder. There is
VBA coding involved.
Roy Harvey
Beacon Falls, CT

Programatic Import (Space delimited) import into SQL Server 2000

I am trying to find a way to do a programatic import of a space delimited
file into a SQL Server 2000 database.
The file is actually an IIS web log file. I can run through the file and
strip out the header information, I can even create a seperate file for each
change in header information is needed.
The problem becomes importing that file into the database. The log files are
on average 6MB each. The import will need to run daily on just a single log
file (after previous log files have been imported)
I do not want to use BULK INSERT because I have had a lot of inconsistant
problems with that functionality in the past.
Does anyone have any ideas?
John WycoffI would start with the DTS data import wizard. You can specify a text
file as input, and space as the column delimiter, and the package it
generates can be saved and modified later. It does assume that all
the rows are in the same format.
Roy Harvey
Beacon Falls, CT
On Thu, 20 Jul 2006 07:15:01 -0700, John Wycoff <John
Wycoff@.discussions.microsoft.com> wrote:

>I am trying to find a way to do a programatic import of a space delimited
>file into a SQL Server 2000 database.
>The file is actually an IIS web log file. I can run through the file and
>strip out the header information, I can even create a seperate file for eac
h
>change in header information is needed.
>The problem becomes importing that file into the database. The log files ar
e
>on average 6MB each. The import will need to run daily on just a single log
>file (after previous log files have been imported)
>I do not want to use BULK INSERT because I have had a lot of inconsistant
>problems with that functionality in the past.
>Does anyone have any ideas?
>John Wycoff|||Use DTS. If the file is 'space' delimited, or is fixed position, DTS can
handle the import. You can create a DTS 'Package' (the set of instructions),
and then schedule that Package to run when appropriate.
Arnie Rowland
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"John Wycoff" <John Wycoff@.discussions.microsoft.com> wrote in message
news:170BB6CA-D3E7-4BEA-A336-390BF6A3DF9E@.microsoft.com...
> I am trying to find a way to do a programatic import of a space delimited
> file into a SQL Server 2000 database.
> The file is actually an IIS web log file. I can run through the file and
> strip out the header information, I can even create a seperate file for
> each
> change in header information is needed.
> The problem becomes importing that file into the database. The log files
> are
> on average 6MB each. The import will need to run daily on just a single
> log
> file (after previous log files have been imported)
> I do not want to use BULK INSERT because I have had a lot of inconsistant
> problems with that functionality in the past.
> Does anyone have any ideas?
> John Wycoff