Logical Error: using expressions & package variables

Hi,

I have encountered some strange output when testing an SSIS package I've been working on.

Please see this thread for background information: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1590716&SiteID=1. The previous thread was resolved and i have used the tips listed here to create a package with Data Flow Tasks that use expressions to create 'dynamic' queries.

The Data Flow tasks use two package-scope variables to determine the date range that they're operating on - this allows me to script the from / to dates (usually 'last week' range) or, in event of error, 'back date' the job easily without having to update each individual components.

However, something weird is happening - when I have two data flow tasks (DFT) in the same Sequence Container (tasks that are not 'connected' or configured to operate in a flow) only one DFT is picking up the package variable & returning the rows. The other DFT is not returning any rows & seems to be using the default value of the variables.

I have tested this by simply enabling DFT A and disabling DFT B - debugging shows only one DFT executes correctly. The same point is illustrated when I disable DFT A and enable DFT B. However, if I configure DFT A to run after DFT B then both pick up the correct variable values and return the correct row counts!!

I want Data Flow Tasks that pull data from the same remote source to be the same sequence containers so when an error occurs I can get container level transaction support.

Note:

I have thoroughly checked the 'evaluated' expressions in IDE and in Management Studio - this is an SSIS specific quirk (or me!).

Questions:

What's happening here, have I made a conceptual error of how this should work? On the same variable topic: is there a way to set values for package level variables at execute time .e.g via command line execution / dtsexecui?

Let me know if you need any extra explaination / diagrams etc to understand this, the other (closed) thread I have listed above should provide more context for my problem

Thanks for your support!!

You can set any property on any object in the package from the command line, using the same syntax as you see with configurations. See the /SET syntax, e.g.

/SET \package\DataFlowTask.Variables[User::MyVariable].Value;newValue

I'll think about the other bit....

|||

Hi Darren,

Thanks for your response & useful command line syntax. Slight update on my posting, this happens, not only with Data Flow Tasks in a single Sequence Container but accross seperate Sequence Containers.

For example I have 5 sequence containers with 2-3 data flow tasks in each, unless all the sequence containers 'flow'/'connect' to each other (on success) then the variable value never seems to get read properly. I see no reason why the containers should be linked to execute correctly.

Admiteddly, the impact of this is minimal: if one sequence container fails then the whole package has failed & it should backs out & notify an admin - it's still a weird occurrence tho!

Question:

I've seen 'container level' transaction management - my package is 'read only' in the sense that it reads from remote endpoints and writes to a local db, it never alters the remote DB's. Container level transactions aren't an option for me as the package seems to want transaction control over the remote data sources (i guess important if you've done remote db insert and updates). I can't get network access to the remote machine's DTC service.

Is there a global 'on package error' event that I can hook into my package where i can run some sql to truncate all local data that was created created during a failed/partial run?

Thanks for your help

Logging using OnError Event

Hi

We are generating log file in our SSIS package by enabling the built-in feature of SSIS tool. We are generating log for the "OnError" event. This also recorded the error/failed task messages in the text file "log.txt". That error information is too complex with more unwanted information like below

-OnError,,,pkgExtract,,,8/30/2006 11:50:04 AM,8/30/2006 11:50:04 AM,-1071636471,0x,An OLE DB error has occurred. Error code: 0x80040E21.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".

OnError,,,pkgExtract,,,8/30/2006 11:50:04 AM,8/30/2006 11:50:04 AM,-1071607780,0x,There was an error with input column "create_user_id" (116) on input "OLE DB Destination Input" (103). The column status returned was: "The value violated the integrity constraints for the column.".

OnError,,,pkgExtract,,,8/30/2006 11:50:04 AM,8/30/2006 11:50:04 AM,-1071607767,0x,The "input "OLE DB Destination Input" (103)" failed because error code 0xC020907D occurred, and the error row disposition on "input "OLE DB Destination Input" (103)" specifies failure on error. An error occurred on the specified object of the specified component.

This is infact not in a better readable format. We also don't want to do our error logging in database.

Is there any way of defining our error log and create error error log with customization of our messages . Can we do it using OnError event handler.

Please help us with some good solution to avoid giving this confused error log messages.

Thanks

Kumaran

Sure, you can do that. Read this: http://blogs.conchango.com/jamiethomson/archive/2005/06/11/1593.aspx

-Jamie

Logging of SSIS package doesnt work when executed as a job

I am having the same problems as those in another post. SSIS package works fine when executed in BIDS and through execute package utility but it doesnt work when executed as a step in a job.

The other problem is that the logging also doesnt work when i try executing it as a job. So I have no clue about what to do without knowing what error it is. When I run the job it simply says the step has failed.

I have tried most of the solutions posted in other websites most of them to do with using proxies with credentials but havent hit a solution. I would love to get any input on what to do.

Thanks

Look at my post

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=328057&SiteID=1

I have the same problem, but have also do some research and as I think the problem lays in something more complicated than running SSIS package as a job - it is only a symptom.

I have also found some roundtrip (not the best...) but it works on my development machine.

If you will have some new thoughts how to solve it please write!

Przemo

|||

Have Sql Agent run a command processor batch job that calls DtExec. If your package runs fine in DtExec it seems it would run fine when DtExec is called from a batch file?

Ken

|||

Look again at my post. There is a solution for the problem which helped me.

Now I use "Windows Integrated Security" in my sql connection string, so there is no problem with encrypting passwords. When I use SQL Login it must be used together with password encrypting SSIS, so I can provide the password at command line in SQL Job.

Przemo

|||

Hi,

Thanks for the reply , I have tried all what you replied it still dint work.

I am running a 32 bit SQl on a 64 bit machine and also have imports from an Excel source, if that can make things different. I know that there can be two problems why it doesnt work.

1) I developed the package in BIDS with my login and I am trying to deploy it in SQl server. The sql server agent is using proxies of my credentials to run. Is ther a different way it should be done?

2) Since i am running on a 64 bit machine, I set Run64BitRuntime to false, thinking that the providers (especially jet is 32 bit). I dont have program files(x86) on my computer. Should that be on the Sql server? If that is the case please let me know how to point to a different dtexec(32 bit).

Please let me know the exact settings that I need to do. One last question will the problem vanish if I use a 64 bit SQl server?

Thanks

|||I finally figured it out. It was a combination of the above two plus some authentication problems. The logging problem was due to pointing at a file outside of sql server.