Disable A Task In The Current Package Programmatically?
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.
I have made one package which extracts data from the source does transformation and submits the data to destination. Subsequently it also updates the required control files.
Now I want to add a functionality :
If the package is executed again it should check the status of previous execution in control file if success mark all tasks disable and stop
if failure mark all tasks at enable and start extracting data and continue further with execution.
I was able to attain similar functionality in SQL Server 2000 using activeX script. What code do I need to write as a part of Script Task in order to attain above functionality.
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.
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
ESQL1 will get max(id) from table and then based on the ID the remaining needs to be work.
Ex: If the ID=AUS-Sdy-1 then ESQL2 will update the tbl1 and return one IDkey based on this return key the ESQL3 will update a tbl2. ----------*( i want to disable ESQL4) else If the ID=AUS-Sdy-2 then ESQL3 will update the tbl1 and return one IDkey based on this return key the ESQL4 will update a tbl2. ----------- *(i want to disable ESQL2)
Is it possible to add a Fuzzy Grouping Transformation in a Data flow task by Programmatically ? If it possible, what is the C# or VB .net code for that ?
I am having trouble with using disable property in the expression for data flow task. Here is the issue as explained below-
lets say i have 3 tables TableA, TableB, TableC from which i need to export data. So i create a table (TableList) where I save these table names and a unique id to these tables. e.g.
in the ssis package select these tableNames & Ids from tableList in Execute SQL Task. And assign the result set to a variable object (@TableList.
Then i use For Each Loop Container (For Each ADO Enumerator) , to loop through these tablesnames & iDs
Inside this loop container, i define three data flow tasks one for each table. So i have DataFlowTaskA (For TableA), DataFlowTaskB(For TableB), DataFlowTaskC (For TableC).
Now for a given table selected in the iteration, only the corresponding DataFlow Task should be exeuted. e.g. For the 1st iteration, if TableA is selected then only DataFlowTaskA should be executed and DataFlowTaskB& C should be skipped.
In order to achieve this, I am using a 3 variable @FlagA, @FlagB, @FlagC (type Boolean) one for each Table. and use the value of these flags for the "Disable" property of the data flow task (so @FlagA will be used for Disable property in the Expression for Data FlowTaskA, and so on..)
SotThe First Step inside the Loop, I use Script Task. (Input for the script task: read variable is @TableID and Read/Write varaibles are these 3 flags)
In this script task, I initialize these flags to true or false appropriately. So this is what i do
If (Dts.Variables("TableID").Value.ToString = "1") Then Dts.Variables("@FlagA").Value = False Else Dts.Variables("@FlagA").Value = True
End If
If (Dts.Variables("TableID").Value.ToString = "2") Then Dts.Variables("@FlagB").Value = False Else Dts.Variables("@FlagB").Value = True
End If
So in the 1st iteration, (if TableA comes) @FlagA=False and B&C will be True. So the Disable property for DataFlowTask will be set false and for others it will be set to True. Thus, only DataFlowTaskA will be executed.
And this action should be repeated for each input table. this is the logic.
However only for the 1st iteration(say TableA is selected) it behaves as above. i.e. DataFlowTaskA is executed and DataFlowTaskB & C are skipped. But in the 2nd iteration(say TableB is selected) , it again executes DataFlowTaskA and doesnt exeute B & C (where it should have executed B & skipped A&C).
I do set daelay validation to true for all these but it still it doesnt working as expected. Even I checked the values for all the flags for each iteration and they seem to get the correct values. But somehow Diable propery in the expression not behaving as it should.
Am i missing anything. Do i need to set any other property to make this work.
I just one question : How can I disable the package validation when I open it ?
When I collect the packages from the production environment, I open them on my pc and I wait 5 minutes by package while the validation are complete... It's so long because the connection point to the production and they must point to the test environment (My pc isn't connected to the network )
To make this change, I must wait 5 long minutes by package and I'm eager...
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.
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.
I am developing tools for automatic creation of data warehouse tables, cubes and SSIS packages. Generating the SSIS Data Flows works very well using the SSIS components for OLE DB Source, Derived Column, Lookup and OLE DB Destination.
However for some of the advanced functionality I need to use Script Component. I have managed to add it in the Data Flow with all inputs and outputs, but how do I populate it with my code? I've seen there is a component property called "SourceCode" and one called "BinaryCode". The "SourceCode" contains the code, but also some extra metadata.
Questions:
Do you know if there is any programmatic support to generate the Source Code property with the metadata necessary?
Do you know how to compile the Source Code and generate the property BinaryCode?
Has anyone done this? I can't find anything in the documentation that describes this. The closest I get is to the InnerObject property of the TaskHost class. There is an example of programming a bulk insert task. But I can't find anything on programmatically setting the column mappings (source to dest) of a simple data flow task. Any help is appreciated!
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
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.
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; }
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.
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.
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.
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.
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); } } }
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?
I'm trying to write a monitoring application for SSIS packages deployed on my machine. I know I can look at running packages via the DtsRuntime.Application object's GetRunningPackages method. Does anyone know if there is any way one can view tasks in that package that are currently being executed?
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?
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"
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.
i searched and all i found is questions, not answers.
maybe it's a silly question, but i really can't find any documention / posts about this.
i have a scheduled job in sqlagent that executes a SSIS package that runs every minute. As a result, my application log in eventviewer gets filled very quickly.
i tried using "/REPORTING E" option with no luck.
i tried enabling logging on the package, and then select only the OnErro Event, no luck.
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?
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.
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.
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.
