Programmatically Developing An Entire Package..
Jun 8, 2006
Hi all,
I am trying to write a program that creates packages on the fly depending on the variables you pass. for eg. It should create connection managers on the fly specific to a certain file in the variable (eg. sample.csv). the package has a dataflow task and it has flat file source and oledb destination.The problem I am facing is the flat file source when assigned to a flat file connection manager(dynamically), it is not giving any source output columns. i.e, the value for DFSource.OutputCollection(0).OutputColumnCollection.Count is Zero. But when I use the same code and reverse the source and destination(oledb as source and flatfile as destination), it is working fine. I searched everywhere for resources on how to develop packages programmatically, but could not find any except one example on msdn. If anyone knows about this prob or any useful resources on this subject, it would be really helpful.
Thanks,
Prithvi.
View 1 Replies
ADVERTISEMENT
Feb 13, 2006
Hello,
i have a problem that i don't understand why it occurs: i have a package like this (resumed and ordered by its correspondingdata flow):
1. move data files to a path through by a file system task
2. import data of data files to a sqlserver database (each file through its own data flow). The origin of data is in files that have been moved to the path of step 1
3. execute a procedure of database (sql task) to manipulate data and finally export resume (data flow) to a new file in the path (moved by other file system task)
PROBLEM: if i execute the package task by task (one by one), it runs ok, but then when i build and run package as a whole (debug), it produces an error in the data flow such as it doesnt' find the path to take data of data flow (it doesn't find the source). Really it doesn't find source because the data files aren't in its correct path (like step 1 had failed). In summary, it looks like if the order of the task of package aren't correct when i execute the entire package, because the log doesn't thread any error about step 1 (file system task).
ERROR MESSAGE: an OLE DB error has occurred. Error code: 0x80040E37
Thanks for any help.
Gema
View 3 Replies
View Related
Mar 24, 2008
I created a simple SSIS package that downloads a file from an FTP server and does some processing on it. I scheduled it as a job step with the Sql Job Agent. The problem is that this file is not always available for pick up, but when it is I need it very quickly. I'm setting the schedule to look for it every minute. Anytime the file is not there, the package fails and shows up in the job history in red.
Is there any way to prevent an error in this task from registering a package failure?
View 3 Replies
View Related
Nov 28, 2007
I have created a DTS package that pulls data from one column (varchar,600) in a table and exports it to a text file. The max length in this field is only 285 characters long, however, the end of one of the records is being truncated in the text file. Data is tab delimited in the table and is being exported that way.
Any tips would be greatly appreciated.
View 13 Replies
View Related
May 4, 2007
Help!
I am using Script Transformation to output a new column as image[DT_IMAGE]
field to store serialized object. In the VB script, the sample code as
Row.serializedobject.AddBlobData ( binaryArrayReturnedFromC#dll )
The package always runs fine on my developing machine and will halt on other
machine at AddBlobData after certain number row records were processed. I am
stuck here. Anyone has any suggestion?
What I need is reading data from mutiple tables in one database and writing
into a single table in another datable. In order preserve all the columns
data, I use input column fields to construct a new object and then serialize
it, and store the serialize data into detination db table. (The object and
serialization function is coming from c# dll.)
Dim b As BusinessLicense = New BusinessLicense()
b.ApprovalDate = Row.approvaldate
b.BusinessId = Row.busid
b.BusinessName = Row.busname
b.NaicsCode = Row.naicscode
b.NaicsDescription = Row.naicsdescr
b.OwnerName = Row.ownername
b.Phone = Row.phone
b.Pkey = Row.pkey
b.RenewalDate = Row.renewaldate
b.StartDate = Row.startdate
b.Suite = Row.suite
Row.serializedobject.AddBlobData(Serializer.Serialize(b)) '''----This is blocking line
Row.infoType = BusinessLicense.TYPE
Both machine is xp with sp2. and standard SQL Server 2005 - 9.00.1399.06
Thanks!
View 4 Replies
View Related
Jul 31, 2007
Can a package executed from code be aborted in code? The Package class has an Execute() method but no Abort() method. Clearly the debugger can stop a package at any point, so it must be possible somehow.
View 12 Replies
View Related
Feb 7, 2006
Hi,
I built a small application that helps me create new packages by loading a template and modifying some parameters, and eventually save the updated package to a user defined location. This application uses the SSIS programming API.
I know all the packages created this way from the same template will have the same package ID. Is there a way to regenerate the package ID programmatically? I don't want to use dtutil.exe, but rather a nice package level method in the API.
Is there such?
Thanks.
View 5 Replies
View Related
Jun 19, 2007
Hi,
I was wondering if it is possible to change the password on a SQL Server 2000 DTS package programatically instead of justmanually using the the interface?
I have over 150 DTS packages used for building BI data cubes and our password policy requires a password change every 60 days.
I would like to write a windows vb.net application that loops through all the DTS packages and changes the password.
Please advise.
Thanks
Chris
View 2 Replies
View Related
Jan 4, 2007
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] ([NAME] NVARCHAR(50),[AGE] NVARCHAR(50),[GENDER] NVARCHAR(50)) GO";
// 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.
View 4 Replies
View Related
Nov 28, 2007
Hi,
I am working on modifying a VB6 app that dynamically creates DTS packages to copy data from one database to another depending on the selections made in UI. The project currently uses DTSPackage object library and DTSDataDump Scripting object library.
We are in the process of upgrading the server to SQL 2005. I am exploring the possibility of replacing code that generates DTS packages on the fly with SSIS packages.
Is it feasible to do this in VB6 ? I have referred to similar posts which focus mainly on VB.NET or C#.
Any help with white paper or sample code would be appreciated.
Thanks in advance
View 6 Replies
View Related
Feb 7, 2007
Hi everyone,
Once I've accesed to package by means of LoadFromSqlServer method how to read its Sql Tasks, for example?
I'm trying with the Executables property but unsuccessfully results:
pkg.Executables.Item(0)
Thanks in advance,
View 14 Replies
View Related
Oct 17, 2007
I have a data flow task within a package. I want to be able to stop the whole package if I come across a certain condition based on data.
I've got it to the point where I can detect the condition, but all I'm able to do right now is then insert a row into a log table, and the rest of the package continues running. What I really want to do is stop the whole package in its tracks.
How can I tell the package to stop?
View 1 Replies
View Related
Aug 8, 2006
Context: I have a web page that uplaods a file that is then imported to the database via an SSIS package.
Problem: If and when the package encounters an error, I want to surface that error to the user via the web page.
Approach: I derived my own class from the DefaultEvents class and then overrode the OnError event handler to catch any errors raised during package execution. I then pass my class to the package Execute method.
Results: The OnError is triggered, but I don't know how to abort package execution nor how to pass the error context to the user. I tried raising a custom exception in the OnError handler, but it failed to propogate to the calling code (ie the code that executes the package).
Another approach would be to write to a database table and then do a query from my web page after the package is done executing (alternatively, I could generate a log file). Seems like a lot of work though. All I want is to pass the error context back to my calling code so I can tell the user what happened.
Thanks in advance for your help.
View 1 Replies
View Related
Dec 14, 2006
Hi,
I have a package comprising a number of Data Flow Task steps, to import various tables of data from some Access databases into SQL Server. The name of some of these Access databases will change depending on the date, e.g. last year's data is currently in a database called "2005data.mdb". At year end this will be superseded by "2006data.mdb". The Access databases are within a 3rd party system so I have no control over the file names.
I have a Script Task that checks the current date, and changes the name of an Access database in the connection string to reflect last year's date. But to complicate matters, last year's file might not exist.
So the Script Task checks whether the file name exists. If not, I would like to disable the Task that uses this connection. But how do I reference a task within the package that contains my Script Task, by name, to set the Disabled attribute to true?
I want to do something like CurrentPackage.Tasks("MyDataFlowTask").Disabled = True.
I would also welcome alternative suggestions for how to achieve this.
Many thanks,
Keith.
View 10 Replies
View Related
Oct 1, 2007
How can I specify the target object when trying to set a parent variable package configuration programatically?
I am trying to set the Target Object to be "ParentLogID" and the Target Property to be it's "Value". I think this is how I'd add the configuration and set the source.
'Add a Configuration
Dim config1 As Configuration
config1 = package.Configurations.Add()
config1.Description = "Set ParentLogID"
config1.ConfigurationType = DTSConfigurationType.ParentVariable
config1.ConfigurationString = "User::LogID"
View 3 Replies
View Related
Nov 9, 2007
Below is C# code used to create a FuzzyLookup SSIS package programmatically. It does 95% of what I need it to. The only thing missing that I cannot figure out is how to take a Fuzzy Lookup Input column (OLE DB Output Column) and make it "pass through" the fuzzy lookup component to the OLE DB Destination. In the example below, that means I need the QuarantinedEmployeeId to make it into the destination.
Look in the "Test Dependencies" region below to get instructions and scripts used to set assembly references, create the sample tables used for this example, and insert test data.
Can anyone help me get past this last hurdle? You will see at the end of my Fuzzy Lookup region a bunch of commented out code that I've used to try to accomplish this last problem.
Code Block
using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
namespace CreateSsisPackage
{
public class TestFuzzyLookup
{
public static void Test()
{
#region Test Dependencies
// Assembly references:
// Microsoft.SqlServer.DTSPipelineWrap
// Microsoft.SQLServer.DTSRuntimeWrap
// Microsoft.SQLServer.ManagedDTS
// First create a database called TestFuzzyLookup
// Next, create tables:
//SET ANSI_NULLS ON
//GO
//SET QUOTED_IDENTIFIER ON
//GO
//CREATE TABLE [dbo].[EmployeeMatch](
// [RecordId] [int] IDENTITY(1,1) NOT NULL,
// [EmployeeId] [int] NOT NULL,
// [QuarantinedEmployeeId] [int] NOT NULL,
// [_Similarity] [real] NOT NULL,
// [_Confidence] [real] NOT NULL,
// CONSTRAINT [PK_EmployeeMatch] PRIMARY KEY CLUSTERED
//(
// [RecordId] ASC
//)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
//) ON [PRIMARY]
//GO
//SET ANSI_NULLS ON
//GO
//SET QUOTED_IDENTIFIER ON
//GO
//CREATE TABLE [dbo].[QuarantinedEmployee](
// [QuarantinedEmployeeId] [int] IDENTITY(1,1) NOT NULL,
// [QuarantinedEmployeeName] [varchar](50) NOT NULL,
// CONSTRAINT [PK_QuarantinedEmployee] PRIMARY KEY CLUSTERED
//(
// [QuarantinedEmployeeId] ASC
//)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
//) ON [PRIMARY]
//GO
//SET ANSI_NULLS ON
//GO
//SET QUOTED_IDENTIFIER ON
//GO
//CREATE TABLE [dbo].[Employee](
// [EmployeeId] [int] IDENTITY(1,1) NOT NULL,
// [EmployeeName] [varchar](50) NOT NULL,
// CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED
//(
// [EmployeeId] ASC
//)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
//) ON [PRIMARY]
// Next, insert test data
//insert into employee values ('John Doe')
//insert into employee values ('Jane Smith')
//insert into employee values ('Ryan Johnson')
//insert into quarantinedemployee values ('John Dole')
#endregion Test Dependencies
#region Create Package
// Create a new package
Package package = new Package();
package.Name = "FuzzyLookupTest";
// Add a Data Flow task
TaskHost taskHost = package.Executables.Add("DTS.Pipeline") as TaskHost;
taskHost.Name = "Fuzzy Lookup";
IDTSPipeline90 pipeline = taskHost.InnerObject as MainPipe;
// Get the pipeline's component metadata collection
IDTSComponentMetaDataCollection90 componentMetadataCollection = pipeline.ComponentMetaDataCollection;
#endregion Create Package
#region Source
// Add a new component metadata object to the data flow
IDTSComponentMetaData90 oledbSourceMetadata = componentMetadataCollection.New();
// Associate the component metadata object with the OLE DB Source Adapter
oledbSourceMetadata.ComponentClassID = "DTSAdapter.OLEDBSource";
// Instantiate the OLE DB Source adapter
IDTSDesigntimeComponent90 oledbSourceComponent = oledbSourceMetadata.Instantiate();
// Ask the component to set up its component metadata object
oledbSourceComponent.ProvideComponentProperties();
// Add an OLE DB connection manager
ConnectionManager connectionManagerSource = package.Connections.Add("OLEDB");
connectionManagerSource.Name = "OLEDBSource";
// Set the connection string
connectionManagerSource.ConnectionString = "Data Source=localhost;Initial Catalog=TestFuzzyLookup;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;";
// Set the connection manager as the OLE DB Source adapter's runtime connection
IDTSRuntimeConnection90 runtimeConnectionSource = oledbSourceMetadata.RuntimeConnectionCollection["OleDbConnection"];
runtimeConnectionSource.ConnectionManagerID = connectionManagerSource.ID;
// Tell the OLE DB Source adapter to use the source table
oledbSourceComponent.SetComponentProperty("OpenRowset", "QuarantinedEmployee");
oledbSourceComponent.SetComponentProperty("AccessMode", 0);
// Set up the connection manager object
runtimeConnectionSource.ConnectionManager = DtsConvert.ToConnectionManager90(connectionManagerSource);
// Establish the database connection
oledbSourceComponent.AcquireConnections(null);
// Set up the column metadata
oledbSourceComponent.ReinitializeMetaData();
// Release the database connection
oledbSourceComponent.ReleaseConnections();
// Release the connection manager
runtimeConnectionSource.ReleaseConnectionManager();
#endregion Source
#region Fuzzy Lookup
// Add a new component metadata object to the data flow
IDTSComponentMetaData90 fuzzyLookupMetadata = componentMetadataCollection.New();
// Associate the component metadata object with the Fuzzy Lookup object
fuzzyLookupMetadata.ComponentClassID = "DTSTransform.BestMatch.1";
// Instantiate
IDTSDesigntimeComponent90 fuzzyLookupComponent = fuzzyLookupMetadata.Instantiate();
// Ask the component to set up its component metadata object
fuzzyLookupComponent.ProvideComponentProperties();
// Add an OLE DB connection manager
ConnectionManager connectionManagerFuzzy = package.Connections.Add("OLEDB");
connectionManagerFuzzy.Name = "OLEDBFuzzy";
// Set the connection string
connectionManagerFuzzy.ConnectionString = "Data Source=localhost;Initial Catalog=TestFuzzyLookup;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;";
// Set the connection manager as the fuzzy lookup component's runtime connection
IDTSRuntimeConnection90 runtimeConnectionFuzzy = fuzzyLookupMetadata.RuntimeConnectionCollection["OleDbConnection"];
runtimeConnectionFuzzy.ConnectionManagerID = connectionManagerFuzzy.ID;
// Set up the connection manager object
runtimeConnectionFuzzy.ConnectionManager = DtsConvert.ToConnectionManager90(connectionManagerFuzzy);
// Establish the database connection
fuzzyLookupComponent.AcquireConnections(null);
// Set up the external metadata column
fuzzyLookupComponent.ReinitializeMetaData();
// Release the database connection
fuzzyLookupComponent.ReleaseConnections();
// Release the connection manager
runtimeConnectionFuzzy.ReleaseConnectionManager();
// Get the standard output of the OLE DB Source adapter
IDTSOutput90 oledbSourceOutput = oledbSourceMetadata.OutputCollection["OLE DB Source Output"];
// Get the input of the Fuzzy Lookup component
IDTSInput90 fuzzyInput = fuzzyLookupMetadata.InputCollection["Fuzzy Lookup Input"];
// Create a new path object
IDTSPath90 path = pipeline.PathCollection.New();
// Connect the source to Fuzzy Lookup
path.AttachPathAndPropagateNotifications(oledbSourceOutput, fuzzyInput);
// Get the output column collection for the OLE DB Source adapter
IDTSOutputColumnCollection90 oledbSourceOutputColumns = oledbSourceOutput.OutputColumnCollection;
// Get the external metadata column collection for the fuzzy lookup component
IDTSExternalMetadataColumnCollection90 externalMetadataColumns = fuzzyInput.ExternalMetadataColumnCollection;
// Get the virtual input for the fuzzy lookup component
IDTSVirtualInput90 virtualInput = fuzzyInput.GetVirtualInput();
// Loop through output columns and relate columns that will be fuzzy matched on
foreach (IDTSOutputColumn90 outputColumn in oledbSourceOutputColumns)
{
IDTSInputColumn90 col = fuzzyLookupComponent.SetUsageType(fuzzyInput.ID, virtualInput, outputColumn.LineageID, DTSUsageType.UT_READONLY);
if (outputColumn.Name == "QuarantinedEmployeeName")
{
// column name is one of the columns we'll match with
fuzzyLookupComponent.SetInputColumnProperty(fuzzyInput.ID, col.ID, "JoinToReferenceColumn", "EmployeeName");
fuzzyLookupComponent.SetInputColumnProperty(fuzzyInput.ID, col.ID, "MinSimilarity", 0.6m);
// set to be fuzzy match (not exact match)
fuzzyLookupComponent.SetInputColumnProperty(fuzzyInput.ID, col.ID, "JoinType", 2);
}
}
fuzzyLookupComponent.SetComponentProperty("MatchIndexOptions", 1);
fuzzyLookupComponent.SetComponentProperty("MaxOutputMatchesPerInput", 100);
fuzzyLookupComponent.SetComponentProperty("ReferenceTableName", "Employee");
fuzzyLookupComponent.SetComponentProperty("WarmCaches", true);
fuzzyLookupComponent.SetComponentProperty("MinSimilarity", 0.6);
IDTSOutput90 fuzzyLookupOutput = fuzzyLookupMetadata.OutputCollection["Fuzzy Lookup Output"];
// add output columns that will simply pass through from the reference table (Employee)
IDTSOutputColumn90 outCol = fuzzyLookupComponent.InsertOutputColumnAt(fuzzyLookupOutput.ID, 0, "EmployeeId", "");
outCol.SetDataTypeProperties(Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_I4, 0, 0, 0, 0);
fuzzyLookupComponent.SetOutputColumnProperty(fuzzyLookupOutput.ID, outCol.ID, "CopyFromReferenceColumn", "EmployeeId");
// add output columns that will simply pass through from the oledb source (QuarantinedEmployeeId)
//IDTSOutput90 sourceOutputCollection = oledbSourceMetadata.OutputCollection["OLE DB Source Output"];
//IDTSOutputColumnCollection90 sourceOutputCols = sourceOutputCollection.OutputColumnCollection;
//foreach (IDTSOutputColumn90 outputColumn in sourceOutputCols)
//{
// if (outputColumn.Name == "QuarantinedEmployeeId")
// {
// IDTSOutputColumn90 col = fuzzyLookupComponent.InsertOutputColumnAt(fuzzyLookupOutput.ID, 0, outputColumn.Name, "");
// col.SetDataTypeProperties(
// outputColumn.DataType, outputColumn.Length, outputColumn.Precision, outputColumn.Scale, outputColumn.CodePage);
// //fuzzyLookupComponent.SetOutputColumnProperty(
// // fuzzyLookupOutput.ID, col.ID, "SourceInputColumnLineageId", outputColumn.LineageID);
// }
//}
// add output columns that will simply pass through from the oledb source (QuarantinedEmployeeId)
//IDTSInput90 fuzzyInputCollection = fuzzyLookupMetadata.InputCollection["Fuzzy Lookup Input"];
//IDTSInputColumnCollection90 fuzzyInputCols = fuzzyInputCollection.InputColumnCollection;
//foreach (IDTSInputColumn90 inputColumn in fuzzyInputCols)
//{
// if (inputColumn.Name == "QuarantinedEmployeeId")
// {
// IDTSOutputColumn90 col = fuzzyLookupComponent.InsertOutputColumnAt(fuzzyLookupOutput.ID, 0, inputColumn.Name, "");
// col.SetDataTypeProperties(
// inputColumn.DataType, inputColumn.Length, inputColumn.Precision, inputColumn.Scale, inputColumn.CodePage);
// fuzzyLookupComponent.SetOutputColumnProperty(
// fuzzyLookupOutput.ID, col.ID, "SourceInputColumnLineageId", inputColumn.LineageID);
// }
//}
#endregion Fuzzy Lookup
#region Destination
// Add a new component metadata object to the data flow
IDTSComponentMetaData90 oledbDestinationMetadata = componentMetadataCollection.New();
// Associate the component metadata object with the OLE DB Destination Adapter
oledbDestinationMetadata.ComponentClassID = "DTSAdapter.OLEDBDestination";
// Instantiate the OLE DB Destination adapter
IDTSDesigntimeComponent90 oledbDestinationComponent = oledbDestinationMetadata.Instantiate();
// Ask the component to set up its component metadata object
oledbDestinationComponent.ProvideComponentProperties();
// Add an OLE DB connection manager
ConnectionManager connectionManagerDestination = package.Connections.Add("OLEDB");
connectionManagerDestination.Name = "OLEDBDestination";
// Set the connection string
connectionManagerDestination.ConnectionString = "Data Source=localhost;Initial Catalog=TestFuzzyLookup;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;";
// Set the connection manager as the OLE DBDestination adapter's runtime connection
IDTSRuntimeConnection90 runtimeConnectionDestination = oledbDestinationMetadata.RuntimeConnectionCollection["OleDbConnection"];
runtimeConnectionDestination.ConnectionManagerID = connectionManagerDestination.ID;
// Tell the OLE DB Destination adapter to use the destination table
oledbDestinationComponent.SetComponentProperty("OpenRowset", "EmployeeMatch");
oledbDestinationComponent.SetComponentProperty("AccessMode", 0);
// Set up the connection manager object
runtimeConnectionDestination.ConnectionManager = DtsConvert.ToConnectionManager90(connectionManagerDestination);
// Establish the database connection
oledbDestinationComponent.AcquireConnections(null);
// Set up the external metadata column
oledbDestinationComponent.ReinitializeMetaData();
// Release the database connection
oledbDestinationComponent.ReleaseConnections();
// Release the connection manager
runtimeConnectionDestination.ReleaseConnectionManager();
// Get the standard output of the fuzzy lookup componenet
IDTSOutput90 fuzzyLookupOutputCollection = fuzzyLookupMetadata.OutputCollection["Fuzzy Lookup Output"];
// Get the input of the OLE DB Destination adapter
IDTSInput90 oledbDestinationInput = oledbDestinationMetadata.InputCollection["OLE DB Destination Input"];
// Create a new path object
IDTSPath90 ssisPath = pipeline.PathCollection.New();
// Connect the source and destination adapters
ssisPath.AttachPathAndPropagateNotifications(fuzzyLookupOutputCollection, oledbDestinationInput);
// Get the output column collection for the OLE DB Source adapter
IDTSOutputColumnCollection90 fuzzyLookupOutputColumns = fuzzyLookupOutputCollection.OutputColumnCollection;
// Get the external metadata column collection for the OLE DB Destination adapter
IDTSExternalMetadataColumnCollection90 externalMetadataCols = oledbDestinationInput.ExternalMetadataColumnCollection;
// Get the virtual input for the OLE DB Destination adapter.
IDTSVirtualInput90 vInput = oledbDestinationInput.GetVirtualInput();
// Loop through our output columns
foreach (IDTSOutputColumn90 outputColumn in fuzzyLookupOutputColumns)
{
// Add a new input column
IDTSInputColumn90 inputColumn = oledbDestinationComponent.SetUsageType(oledbDestinationInput.ID,
vInput, outputColumn.LineageID, DTSUsageType.UT_READONLY);
// Get the external metadata column from the OLE DB Destination
// using the output column's name
IDTSExternalMetadataColumn90 externalMetadataColumn = externalMetadataCols[outputColumn.Name];
// Map the new input column to its corresponding external metadata column.
oledbDestinationComponent.MapInputColumn(oledbDestinationInput.ID, inputColumn.ID, externalMetadataColumn.ID);
}
#endregion Destination
// Save the package
Application application = new Application();
application.SaveToXml(@"c:TempTestFuzzyLookup.dtsx", package, null);
}
}
}
View 6 Replies
View Related
Mar 24, 2008
I am making some changes to a tracking SP that I'm using in about 100 SSIS packages. I don't want to go into each package and add the additional parameter (A "?") and map a variable to some ordinal position. I'd like to do this programmatically.
I have some expirence in validing variables and manipulating connection objects in packages though a utility that I wrote. However, I cannot find any guidance on How to create/edit Execute SQL Tasks via code in the package. I would like to do something like this..
Code Snippet
private Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();
Package p = app.LoadPackage(DTSPackage, null);
p.Executables["My Execute SQL Task"]
However, the Executables returns an Executable object that is a DtsObject. Do I have to add a reference to the task and cast this executable to that type? Any help is appreciated.
Thanks
Scott
View 4 Replies
View Related
May 7, 2007
Hi there,
I'm trying to keep track of the ETL process inserting/updating a row in one table for each package that finish in my ETL process when executing. So far, I created a Script task that increments by one a variable (counter) and then open a connection to my database an insert/update my table. What I want to see is Step 1/30, Step 2/30 and so on. Right know I can display Step 1, Step 2 but how can I get the overall number of tasks within a package?
Thanks in advance for any help you can give me.
Sincerely,
Ricardo
View 10 Replies
View Related
Sep 13, 2007
Hi!
I have to run a remote dtsx-package programmatically.....with Visual Basic 6.0.
Is this possible? I've only found this .net code:
http://msdn2.microsoft.com/en-us/library/ms403355.aspx
Thanks
Chris
View 3 Replies
View Related
Aug 15, 2002
I have an ActiveX task containing vbscript that when placed in a DTS package, will set all Connection and DataTransformation (DataPump) information correctly. Additionally, the package appears to work perfectly afterwards.
The Question:
Why when I go into the DTS Designer and open a transformation (Black Arrow) between connections - that I get the "Verifying Transformations" dialog with the three radio button options of "Remove Invalid transforamtions", "Change Source/Destination", and "Remove all transformations and redo auto-mapping"?
It appears that the SourceObjectName and SourceSQLStatements are getting correctly updated as well as the DestinationObjectName, etc.
Is there a property that is supposed to be set when I modify the source and destination properties in my code to prevent this dialog?
Thanks a bunch in advance for any assistance
View 1 Replies
View Related
Apr 23, 2007
Hi,
I have created an application that loads a package and executes it using DTS runtime classes. But when I run the application on a machine where only SQL Express edition is installed it's throwing
"Retreiving COM class factory for component with CLSID E44847F1-FD8C-4251-B5DA-B04BB22E236E failed due to the following error : 80040154"
Can someone help?
View 1 Replies
View Related
Mar 4, 2006
I'm building packages programmatically and all is well. I have a new custom transform that I developed. It also works fine. Now I'm trying to add my new component to my packages when I programmatically build them, and I'm unable to do that.
Has anyone added their own custom components to a programmatically built package successfully?
I get a COM error on the line that calls ProvideComponentProperties. I've attempted various modifications including not overriding ProvideComponentProperties or just having it do nothing. I always get the same result. What I don't understand is that the custom transform works and handles ProvideComponentProperties fine when it is added to a package in BIDS.
Thanks.
'Add new dataflow task
combitronics = dataFlow.ComponentMetaDataCollection.New()
'Set assembly to my component
combitronics.ComponentClassID = "Ewrap.SSIS.Combitronics"
'Get instance of component
Dim instance As CManagedComponentWrapper = combitronics.Instantiate()
'Initialize component ***Error Occurs Here***
instance.ProvideComponentProperties()
...
View 2 Replies
View Related
Apr 27, 2007
SSIS Experts,
I am executing a child package programmatically and want few lines of code to detect cause of the failure on the fly. One way will be to run the child package through dtexec command uility. But on the fly i will be assigning few values in Parent package variable to the child variables and finally run the child package. Since we cannot apply expressions or use precendence constraints in place, It seems that we are left with only choice to programmatically detect errors in tasks. In DTS, it was achieved this way:
Code Snippet
For Each ostep In oPkg
If ostep.ExecutionResult = DTSStepExecResult_Failure Then
ProcessSubDTSPackage = DTSTaskExecResult_Failure
End If
Next
Thanks
Subhash Subramanyam
View 4 Replies
View Related
Mar 1, 2007
We are trying to start a server job running an SSIS package and supply some parameters to the package when we start the job using SMO.
What we have now is this:
string cmdLine = job.JobSteps[0].Command;
cmdLine += @" /SET PackageGetGroupRatingYear_Id.Variables[User::RatingId].Value;1";
cmdLine += @" /SET PackageGetGroupRatingYear_Id.Variables[User::GroupId].Value;1";
cmdLine += " /SET \Package.Variables[User::period].Value;"" + periodEndDate + """;
job.JobSteps[0].Command = cmdLine;
job.Start();
It appears that when the job is run, the modified command line is not used.
What is needed to supply runtime parameters to a job step when starting the job via SMO?
Thanks,
View 3 Replies
View Related
Dec 8, 2006
Hi,
I am trying to programmatically execute a package that contains an Execute SQL Task component bound to a variable for its "SqlStatementSource" property (via an expression). The variable is of type String and contains a simple value of "SELECT 1". The Execute SQL Task contains an expression that sets the SqlStatementSource property to the value of this variable.
The package runs fine when I execute it via dtexec or BIDS, but when I attempt to run it via the object model, I receive the following error message:
The result of the expression ""@[User::Sql]"" on property "SqlStatementSource" cannot be written to the property. The expression was evaluated, but cannot be set on the property.
I did a search on this forum and noticed quite a few threads about this same issue, but no explanation/solution. We have quite a few packages that have dynamically constructed SQL statements for Execute SQL Tasks, and they are all failing to run via the object model. Is there something that I am missing?
Thanks,
Vitaly
View 1 Replies
View Related
Mar 7, 2007
Hi,
I am trying to apply the sample provided by Microsoft in the following article:
http://msdn2.microsoft.com/en-us/library/ms403355.aspx
I am trying to call a SSIS package from a web service hosted on the same machine as the package file is sitting. The package is running fine from the Agent and also by the "Integration Services Project" in VS.NET.
I had a lot of problems with permissions but they are resolved, at least I have no error messages to point to that direction. Now I am getting these results:
1. Error: -1073659874 / Description: The file name "\Diver-svrInputDataFilesdn_cust.txt" specified in the connection was not valid.
2. Error: -1073659875 / Description: Connection "bdn_cust" failed validation.
3. Error: -1073659874 / Description: The file name "\Diver-svrInputDataFilesdn_cust.txt" specified in the connection was not valid.
4. Error: -1073659875 / Description: Connection "SourceConnectionFlatFile" failed validation.
Where \DiverMInputFilesdn_cust.txt" is a file processed by the package.
Is there anybody who can give me some directions. Thank you in advance.
View 4 Replies
View Related
Apr 19, 2007
Hi folks,
I have come across a situation where there 10 tasks. The second task on the flow is a script task which disables all further tasks based on a condition. I thought that the logic would be better if we force terminate the package successfully at this stage itself. How can this be done.
Thanks
Subhash Subramanyam
View 4 Replies
View Related
Mar 6, 2007
HI All,
In several threads there has been discussion regarding adding connection managers to a package's data flow, etc. My challenge is that I have a large solution that contains many packages, and I need to change the connection manager linked to the data flow in all of the packages. When the solution was initially designed, data sources were used, and it has become a tedious maintenance issue to keep those in sync. We want to use a standard OLEDB connection manager, but adding a connection manager to each package and editing the corresponding data flow tasks in each package to use that new connection manager is a daunting task. I've coded a .Net module to access the packages, remove the old connection manager (data source) and add the new OLEDB data source. However, as I traverse the objects in the package hierarchy, when I come to the data flow object, the innerobject is not a dts object, but rather a _com object.. I can't seem to find any documentation/examples as to how to iterate the tasks within a data flow and change the connection manager. If you have any information, that would be quite helpful. If you reply with a code sample, if you would be so kind as to relate it to one of the sample packages provided with SSIS so I can run it, that would be great.
Thank you.
Steve.
View 1 Replies
View Related
Sep 23, 2007
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 = ". 1105.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, "
");
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 = "
";
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";
}
}
}
View 3 Replies
View Related
Sep 25, 2004
Hi all,
I would like to develop WAP using ASP.NET which can connect to the Database( MS SQL Server ). Is it possible to do it? I try to find the tutorial but I cannot find it.
Another thing I want to ask is can I connect the WAP to the web services that I create using ASP.NET, anyone can tell me how to do it.
Thanks, Regards.
RED
View 1 Replies
View Related
Jun 22, 2001
I need to create a query that will pull all of the records that are statused as 'OUT' from my RECORDS table and then get the last (chronologically) 'OUT' record from my IN/OUT table for each record it pulled from RECORDS.
I can get the records out of RECORDS without any problems but I don't know how to set up a query that will pull the last chronological 'OUT' record from my IN/OUT table. Is there a way to do this in SQL?
Thank you in advance for any help.
View 1 Replies
View Related
Oct 6, 2006
Hi
Can i inherit a existing task to a new custom class to add functionality to custom class.
for eg: Inherit lookup transfor which is already present in SSIS to add some funstionality into it.
Thanks
Vipul
View 1 Replies
View Related
Jul 16, 2013
company is developing a database system containing information about cities, towns, and villages in the country. The number of cities, towns, and villages are in thousands and the database program should allow users to search a particular place by name. Users should also be able to search all places that match a particular value or range of values for attributes such as location or population size. This particular feature is known as a range query.
If the database is meant to support range queries that can return many cities that match the query specification, the entire operation may be allowed to take longer. To meet this requirement, it will be necessary to support operations that process range queries efficiently by processing all cities in the range as a batch, rather than as a series of operations on individual cities.Different indexing methods can be used to perform this operation. What will be your choice if:
a) database is created once and has no change over a period of time
b) database is created once and database is changed over a period of time
View 2 Replies
View Related
