No Error thrown by SSIS on missing configuration files

Hello All,

I have a package in which I have enabled "Package Configuration".

When I run the package i am sure that it reads the configuration file and executes the package correctly.

However if I remove the configuration file, the package still executes correctly with the settings which were used at the time of development.

I have event handlers for OnError and OnWarning and both these are NOT invoked.

IMO, this is incorrect behavior because if a package has been configured for "package configuration, then we should atleast have a warning generated that SSIS did not find the configuration and it would execute the package with hard coded values (from the time of development).

Is there any work around for this? how can I make SSIS warn me if the config file is missing for a package which was configured for package configuration?

A missing configuration should trigger a warning and then the package uses the values provides at design time. I always get a the warnings.|||

What event handlers have you written?

I have written event handlers at package level for OnError and OnWarning

In both the event handlers I log the message into the windows event log.

The only messages I see in the windows event log is the "package started" and "package completed".

regards,

Abhishek.

|||I was talking about SSIS logining. I choose OnError and OnWarning.|||

The first thing I would look at is what circumstances are causing the configuration files to be missing at run time, as it seems like this is the root cause of your problem. The behavior you're describing appears to be "by design" to me - this is what I would expect, although I would also expect a warning to be logged if the configuration file were missing.

One possible workaround would be to have a Script task or custom component explicitly check for the existence of the file(s) before the main package logic executes, and only continue if the config files exist.

Another possible workaround would be to ensure that the design time values for the settings that should be set by the package configuration are obviously invalid. (For example, string that is not a valid path, or the like, depending on what you're setting in your configurations.) This would cause the package to fail outright at run time if the configuration could not be set, as opposed to executing with incorrect values and failing silently.

With that said, neither one of these workarounds seems particularly clean or ideal to me. I'd look at the root cause (why are the files missing) before doing anything else.

|||

> The first thing I would look at is what circumstances are causing the configuration files to be missing at run time

The circumstance is that we are testing our operations management strategy. We are deliberately causing things to go wrong in our test environment and we see if MOM (Microsoft Operations Manager) informs the admin correctly of what went wrong.

regards,

Abhishek.

no available input columns

Hi ,

Im trying to build a package that will copy data from excel to SQL

in a program

unfortunately , when I open the package xml file and I drill into the

oledb destination I see that I have no available input columns

what could be the problem ?

thanks ahead

Eran

p.s

the script:

Dim p As Package = New Package()

Dim e As Executable = p.Executables.Add("DTS.Pipeline.1")

Dim thMainPipe As TaskHost = CType(e, TaskHost)

thMainPipe.Properties("Name").SetValue(thMainPipe, "Data Flow")

Dim dataFlowTask As MainPipe = CType(thMainPipe.InnerObject, MainPipe)

' Create excel connection MANAGER

Dim excelCon As ConnectionManager = p.Connections.Add("EXCEL")

excelCon.Name = "ExcelSourceConn"

excelCon.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=e:\try\try\try.XLS;Extended Properties=""Excel 8.0;HDR=YES"""

' Create sqldev connection Manager

Dim sqlCon As ConnectionManager = p.Connections.Add("OLEDB")

sqlCon.Name = "sqldevConn"

sqlCon.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;User ID=sa;Initial Catalog=InsFocus_Admin_Eran;Data Source=SQLDEV\SQLDEV"

''create source component

Dim excelSource As IDTSComponentMetaData90 = dataFlowTask.ComponentMetaDataCollection.New()

excelSource.Name = "ExcelSource"

excelSource.ComponentClassID = "DTSAdapter.ExcelSource.1"

Dim excelInstance As CManagedComponentWrapper = excelSource.Instantiate()

excelInstance.ProvideComponentProperties()

excelSource.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.ToConnectionManager90(p.Connections(0))

excelInstance.SetComponentProperty("AccessMode", 0)

excelInstance.SetComponentProperty("OpenRowset", "business_codes$")

excelCon.AcquireConnection(Nothing)

'excelInstance.ReinitializeMetaData()

excelInstance.ReleaseConnections()

Dim sqldev As IDTSComponentMetaData90 = dataFlowTask.ComponentMetaDataCollection.New()

sqldev.Name = "sqldev"

sqldev.ComponentClassID = "DTSAdapter.OLEDBDestination.1"

Dim sqldevInstance As CManagedComponentWrapper = sqldev.Instantiate()

sqldevInstance.ProvideComponentProperties()

sqldev.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.ToConnectionManager90(p.Connections(1))

sqldevInstance.SetComponentProperty("AccessMode", 0)

sqldevInstance.SetComponentProperty("OpenRowset", "business_codes")

sqldevInstance.AcquireConnections(Nothing)

sqldevInstance.ReinitializeMetaData()

sqldevInstance.ReleaseConnections()

Dim path As IDTSPath90 = dataFlowTask.PathCollection.New()

path.AttachPathAndPropagateNotifications(excelSource.OutputCollection(0), sqldev.InputCollection(0))

MsgBox(excelSource.OutputCollection.Count)

'For Each input As IDTSInput90 In sqldev.InputCollection

' Dim vInput As IDTSVirtualInput90 = input.GetVirtualInput

' For Each vColumn As IDTSVirtualInputColumn90 In vInput.VirtualInputColumnCollection

' ' Call the SetUsageType method of the design time instance of the component.

' sqldevInstance.SetUsageType(input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY)

' Next

'Next

Dim app As Application = New Application()

app.SaveToXml("c:\myXMLPackage.dtsx", p, Nothing)

Hi,

I found your post and was wondering if you ever got a response to this? I'm having the same problem

Thanks

Cat

|||

When you open the package in the designer do you see any columns published on the source adapter?

Also to clarify: is the problem that the virtual column collection is empty in your program or the input column collection is empty when you look at your destination adapter afterwards?

The commented piece of code is supposed to add columns to the input column collection.

Thanks.

no available input columns

Hi ,

Im trying to build a package that will copy data from excel to SQL

in a program

unfortunately , when I open the package xml file and I drill into the

oledb destination I see that I have no available input columns

what could be the problem ?

thanks ahead

Eran

p.s

the script:

Dim p As Package = New Package()

Dim e As Executable = p.Executables.Add("DTS.Pipeline.1")

Dim thMainPipe As TaskHost = CType(e, TaskHost)

thMainPipe.Properties("Name").SetValue(thMainPipe, "Data Flow")

Dim dataFlowTask As MainPipe = CType(thMainPipe.InnerObject, MainPipe)

' Create excel connection MANAGER

Dim excelCon As ConnectionManager = p.Connections.Add("EXCEL")

excelCon.Name = "ExcelSourceConn"

excelCon.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=e:\try\try\try.XLS;Extended Properties=""Excel 8.0;HDR=YES"""

' Create sqldev connection Manager

Dim sqlCon As ConnectionManager = p.Connections.Add("OLEDB")

sqlCon.Name = "sqldevConn"

sqlCon.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;User ID=sa;Initial Catalog=InsFocus_Admin_Eran;Data Source=SQLDEV\SQLDEV"

''create source component

Dim excelSource As IDTSComponentMetaData90 = dataFlowTask.ComponentMetaDataCollection.New()

excelSource.Name = "ExcelSource"

excelSource.ComponentClassID = "DTSAdapter.ExcelSource.1"

Dim excelInstance As CManagedComponentWrapper = excelSource.Instantiate()

excelInstance.ProvideComponentProperties()

excelSource.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.ToConnectionManager90(p.Connections(0))

excelInstance.SetComponentProperty("AccessMode", 0)

excelInstance.SetComponentProperty("OpenRowset", "business_codes$")

excelCon.AcquireConnection(Nothing)

'excelInstance.ReinitializeMetaData()

excelInstance.ReleaseConnections()

Dim sqldev As IDTSComponentMetaData90 = dataFlowTask.ComponentMetaDataCollection.New()

sqldev.Name = "sqldev"

sqldev.ComponentClassID = "DTSAdapter.OLEDBDestination.1"

Dim sqldevInstance As CManagedComponentWrapper = sqldev.Instantiate()

sqldevInstance.ProvideComponentProperties()

sqldev.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.ToConnectionManager90(p.Connections(1))

sqldevInstance.SetComponentProperty("AccessMode", 0)

sqldevInstance.SetComponentProperty("OpenRowset", "business_codes")

sqldevInstance.AcquireConnections(Nothing)

sqldevInstance.ReinitializeMetaData()

sqldevInstance.ReleaseConnections()

Dim path As IDTSPath90 = dataFlowTask.PathCollection.New()

path.AttachPathAndPropagateNotifications(excelSource.OutputCollection(0), sqldev.InputCollection(0))

MsgBox(excelSource.OutputCollection.Count)

'For Each input As IDTSInput90 In sqldev.InputCollection

' Dim vInput As IDTSVirtualInput90 = input.GetVirtualInput

' For Each vColumn As IDTSVirtualInputColumn90 In vInput.VirtualInputColumnCollection

' ' Call the SetUsageType method of the design time instance of the component.

' sqldevInstance.SetUsageType(input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY)

' Next

'Next

Dim app As Application = New Application()

app.SaveToXml("c:\myXMLPackage.dtsx", p, Nothing)

Hi,

I found your post and was wondering if you ever got a response to this? I'm having the same problem

Thanks

Cat

|||

When you open the package in the designer do you see any columns published on the source adapter?

Also to clarify: is the problem that the virtual column collection is empty in your program or the input column collection is empty when you look at your destination adapter afterwards?

The commented piece of code is supposed to add columns to the input column collection.

Thanks.

Newby DTS Question

Hi,

I am trying to set up a DTS Package to transfer data from a remote
MySQL server to a local MS SQL database. The source tables will remain
static as will the destination however I only want to copy down data
that does not already exist in the local database.

I have two different methods of which to identify the new rows, on some
tables it is by a unique ID thus in TSQL I would say WHERE ID x.
Other tables are by date where I would want to do something like WHERE
CreateDate >= GetDate()-1

How would I go about performing such clauses using DTS?

Thanks

Mikemike_kilby@.hotmail.com wrote:

Quote:

Originally Posted by

Hi,
>
I am trying to set up a DTS Package to transfer data from a remote
MySQL server to a local MS SQL database. The source tables will remain
static as will the destination however I only want to copy down data
that does not already exist in the local database.
>
I have two different methods of which to identify the new rows, on some
tables it is by a unique ID thus in TSQL I would say WHERE ID x.
Other tables are by date where I would want to do something like WHERE
CreateDate >= GetDate()-1
>
How would I go about performing such clauses using DTS?

Hi Mike,

Assuming that I am understanding you correctly... in your data pump you
should select "SQL Query" on the "Source" tab. Then fill in the query
using something like:

SELECT my_columns
FROM dbo.My_Table
WHERE id ?

The "?" tells DTS that you will supply it with this parameter. Now
click on the Parameters button and create a global variable to hold the
maximum ID value in your destination table. Now you just need to make
sure that the global variable gets filled *before* your data pump. You
can use the Dynamic Properties Task for this, with an "On Success"
workflow constraint between that and your data pump.

You may need to play with the dates a bit to make sure that you have
the right global variable data type/formats, etc.

HTH,
-Tom.|||Thanks for your help Tom,worked a treat.

Thomas R. Hummel wrote:

Quote:

Originally Posted by

mike_kilby@.hotmail.com wrote:

Quote:

Originally Posted by

Hi,

I am trying to set up a DTS Package to transfer data from a remote
MySQL server to a local MS SQL database. The source tables will remain
static as will the destination however I only want to copy down data
that does not already exist in the local database.

I have two different methods of which to identify the new rows, on some
tables it is by a unique ID thus in TSQL I would say WHERE ID x.
Other tables are by date where I would want to do something like WHERE
CreateDate >= GetDate()-1

How would I go about performing such clauses using DTS?

>
Hi Mike,
>
Assuming that I am understanding you correctly... in your data pump you
should select "SQL Query" on the "Source" tab. Then fill in the query
using something like:
>
SELECT my_columns
FROM dbo.My_Table
WHERE id ?
>
The "?" tells DTS that you will supply it with this parameter. Now
click on the Parameters button and create a global variable to hold the
maximum ID value in your destination table. Now you just need to make
sure that the global variable gets filled *before* your data pump. You
can use the Dynamic Properties Task for this, with an "On Success"
workflow constraint between that and your data pump.
>
You may need to play with the dates a bit to make sure that you have
the right global variable data type/formats, etc.
>
HTH,
-Tom.