I am in need of creating a package that is "self sufficient", per say. First part i need is for it to start off by checking to see if the database i am going to use is even on the sql instance it is deployed on. After such validation, it either builds the database or kicks off the rest of the logic. In either situation, the logic is run. Problem i am having is choosing between a Script task or a SQL Task. So far i am finding i can do the SQLConnection/Command combo, in a script task, but am limited as far as consistently, or succesfully, getting a Success to progress on to the logic. I can get the failure to progres on to the create table, but from there it does not continue on to the logic series of statements, it ends at my last step and finishes the package instead of going on to the logic steps. I have done verification and validation of Tables using SQL Tasks but never to verify a database exists. As well, how would i conditionally split out of a SQl task if the database does not exist vs it does exist?"I am the reason, Curiosity killed the Cat!" Please be patient, there are times where i do not respond for weeks at a time.

Do that with an Execute SQL Task. Run db_id('dbname') and map to an out variable, so it is not null then the db exists and attest the value in a Precedence Constraint to branch the package flow execution into one avenue or another.Arthur My Blog

I assume the out variable would be the Result Set setup or a Parameter mapping? In either point in case, how do i map the resulting value to the Output (parameter or resultset)? This is my first time doing this kind of conditional valuation, otherstuff i simply filter data out and then filter rest after the good is out. This although requires a little more intelligence on the part the package, so we can deploy it and it builds what it needs prior to reporting what it is there to do."I am the reason, Curiosity killed the Cat!" Please be patient, there are times where i do not respond for weeks at a time.

Quick verification, i think i figured out what you are talking about. if the body of the SQL Tasks is: use master go declare @dbexists int if db_id('mpdb') is null begin set @dbexists = -1 end else begin set @dbexists = 1 end and with the Precedence Constraint Editor: I select 'Expression' from "Evaluation Operation" In the Expression box i put '@dbexists = -1' or '@dbexists = 1', pending on which path it takes Is that the correct implementation?"I am the reason, Curiosity killed the Cat!" Please be patient, there are times where i do not respond for weeks at a time.

Hi, Under "General" Properties of an "Execute SQL Task Editor", change "ResultSet" to "Single row" Then under "Result Set" Properties Page, Column "Result Name" is where you enter your SQL result column name and "Variable Name" is the variable that you would like the value of the column to map to. Hope this helps. ~ J.

So i have to map it to a package, or task, variable name? I really dont need it after i validate whether the database exists or not. as this first step is performed with every iteration of the package (weekly)."I am the reason, Curiosity killed the Cat!" Please be patient, there are times where i do not respond for weeks at a time.

So i have to map it to a package, or task, variable name? I really dont need it after i validate whether the database exists or not. as this first step is performed with every iteration of the package (weekly). "I am the reason, Curiosity killed the Cat!" Please be patient, there are times where i do not respond for weeks at a time. You need to use the variable. This variable will drive to the Precedence Constraint (to branch the package execution) Arthur My Blog

So could you give me an example of the expression value that i need to put in, cause right now it is constantly failing the validation check. I made sure the database was setup on the instance i am testing against so it progress to the logic of the package before testing to see if the create database flow will work. Right now it is constantly going to the create database flow of the package every time."I am the reason, Curiosity killed the Cat!" Please be patient, there are times where i do not respond for weeks at a time.

Declaring variables within the SQL statement does not make those variables visible to SSIS. You need to place a "SELECT @dbexists" as the last line of your batch in order to return a rowset to SSIS from SQL Server. You can then set the return type to SingleRow and do the variable mapping as described above. Talk to me now on

Thank you, this is a new feature that i am being lead into by all of you. So i am unsure of the proper way to utilize it. I will continue to play with it, with your guidance of course ;) Let you know how it works for me, by tomorrow or end of the weekend."I am the reason, Curiosity killed the Cat!" Please be patient, there are times where i do not respond for weeks at a time.

Should the connection be to the 'master' database or should i have it connecting to a different database? Obviously i dont want it to connect to the database i am checking to see if it exists but i am still going down the 'does not exist' path everytime, whether it does or does not. Currect script declare @dbexists int; if db_id('mpdb') is null begin set @dbexists = -1 end else begin set @dbexists = 1 end select @dbexists with the expression being '@dbexists == -1' and '@dbexists == 1' being checked on the Precedence Constraint for the two possible paths."I am the reason, Curiosity killed the Cat!" Please be patient, there are times where i do not respond for weeks at a time.

Should the connection be to the 'master' database or should i have it connecting to a different database? Obviously i dont want it to connect to the database i am checking to see if it exists but i am still going down the 'does not exist' path everytime, whether it does or does not. Currect script declare @dbexists int; if db_id('mpdb') is null begin set @dbexists = -1 end else begin set @dbexists = 1 end select @dbexists with the expression being '@dbexists == -1' and '@dbexists == 1' being checked on the Precedence Constraint for the two possible paths. Also setup a result set for the Script Task: Result Name- @dbexists | Variable Name - User::dbe , if this makes a difference. Also have NOT setup any parameter values for the task. "I am the reason, Curiosity killed the Cat!" Please be patient, there are times where i do not respond for weeks at a time.

Yes, connecting to "master" is a smart thing to do. I believe you're still using variables not quite correctly. You have two variables at play here: "@dbexists" and "@dbe". @dbexists is a SQL Server variable that exists only during execution of the Execute SQL Task. It does NOT exist in the SSIS world. SSIS can't look at it, can't make precedence constraints on it - nothing. In order to allow SSIS to use a value you retrieve/calculate within the SQL of an Execute SQL Task, you have to make the statement "return" a rowset (which you've done), and then tell SSIS that it is returning a rowset (SingleRow) and that you want the value in the first column (column zero) placed in the SSIS variable @dbe (that's what you should be doing on the Variable Mapping page). From here on, you can only use @dbe. @dbexists doesn't exist anymore. Your precedence constraint can't refer to it. If SSIS isn't reporting an error on that precedence constraint, it's probably because you have a third variable - @dbexists - defined within SSIS. This is not the same variable as the Execute SQL Task statement's @dbexists. Delete it from SSIS. Talk to me now on

Roger....Roger... And what about the definition of the Logical AND and OR part of the constraints...How does that effect the outcome? edited: When i put 'LogicalAND' to True....it just sits at the SQL Task. If i set it to False, it actually does the split decision part of the package. It works when i set it to False (or Logical Or). "I am the reason, Curiosity killed the Cat!" Please be patient, there are times where i do not respond for weeks at a time.