Help Required Using Variables And Comparing Them In SSIS
Sep 25, 2007
Hi i am New to ssis and Scripting in ssis what i was trying to do was to compare few variables which i populate using execute sql tasks and determine which process needs to kick off based on Task success or Failure
are the variables i am populating Mapping them to result set in execute sql task.
This is the criteria for script task to Fail or success
I am not able to populate the varibales with the Values from Execute Sql and also i am encountering errors in the script task.
I am Declaring ex: myvar11 as variables and then using it , please help me as i am new and guide me for the right approach. and can this logic be implemented in ssis for example like && operator used in script.
End IF
HiThe scenario:The price of products are determined by size.I have a Prices table that contains 3 columnsWidth Length and Price.User inputs their own width and length values as inWidth and inLength.It is unlikely that these values will exactly match existing lengths and widths in the price table.I need to take these User Input values and round them up to the nearest values found in the Prices table to pull the correct price.What is the most efficient way of achieving this?Thanks for your time.C# novice!
I have a statement that works in compatibility mode 8 of SQL Server2000 while it fails in 7:declare @P3 intexec sp_prepexec @P3 output, N'@P1 float', N'select custname fromcustomer where custnr = @P1', 12600034custnr is a varchar(15). As long as the float p1 is less than1,000,000 the query returns correct results, but as soon as p1 isequal or greater than 1,000,000 the resultset is empty. I couldfigure, that above 1 million, sp_exec converts the float intoexponential format and compares this format to the string containingfixed format values. As said above, in mode 8 the statement returnscorrect results above 1,000,000.Please don't ask me, why we use float variables - I don't have anyinfluence on the statement - we isolated the statement with theprofiler.We are also currently dependent on the compatibility mode 7.I would be very grateful if you have any ideas, help or other issuesfor me.Thank you,Peter
I'd like to know how people out there are comparing ("diffing") their DTSX files.
Using XML file compare doesn't seem to work, because the package XML appears to be arbitrarily ordered and reordered by the designer in Visual Studio.
ApexSQL is supposedly planning to have a tool available in Q3, but even their product page does not seem too hopeful: "Compare and Document SSIS Packages (may be released with ApexSQL Doc and ApexSQL Diff in Q3)"
I remember seeing a CodePlex project that was hoping to address this and other issues, but it was in its infancy, and I can't seem to locate it today.
I'm thinking of starting to write my own package compare tool that will work with the SSIS .NET API, but this seems like it's going to be a ridiculous amount of work and an exercise in pain, and it seems unlikely that I will make the time to target anything more than the specific portions of the package model that I really need.
So if anyone out there has words of wisdom to share, I'd love to hear it. And if (dare I get my hopes up?) have a URL to share, I'll buy you a beer at TechEd in Orlando week after next.
I'm trying to determine proper end date for measuring the effectiveness of my client's email campaigns. For each email address that received the campaign, I would like to determine a measurement end date. This date should be the lesser of 7 days from the Sent date or the date of the last email offer. This way, I prevent double-counting the revenue associated with each email campaign. Here's an example of what I'd like to see for each unique Sent Date and Email Address:
Sent Date Email Address Measurement End Date <<<< This is what I'm trying to derive with a script
2/22/2007 test@test.com 2/29/2007 <<<< 7 days from Sent Date
2/18/2007 test@test.com 2/22/2007 <<<< 4 days from Sent date due to the 2/22/2007 email
1/20/2007 test@test.com 1/27/2007 <<<< 7 days from Sent Date
etc.
If the input data stream is sorted by Email Address and descending Sent Date, then I need to be able to compare the Sent Date on the current record to the Sent Date on the next record (until the Email Address changes and then the process starts over).
I'm open to any solutions as this has really stumped me for awhile.
I am trying to compare two flat files and extract new entry into new file.But in my case there is no key column in both flat files. is any way to find the new entry by checksum with out Key matching?.
Refering to my previous post I've a package which easily run when transaction mode is Supported but when i put it to Required and All DFs are supported inside the sequence container it won't run.
Well technically it goes and pass step 1, so it goes to green but regardless of what i put in the 2nd DF it won't run. It just hangs in there. When i check the component service and check DTC, the transaction would be running but never commited.
I have done most of the things about DTC and configuration with the MS articles as this package was failing before but now it just sits at this point.
I have created a few packages and i want to execute this in a sequence so I created a wrapper/parent package and added all the other packages as child Package using the Execute Package Task. These packages are file system based packages. I am executing the wrapper/parent package from a web page which will execute all the child packages. All is well and works fine when I choose the TransactionOption as "Supported" in my wrapper/parent package but when I choose the TransactionOption as "Required" in my wrapper/parent package I get the following error
Error Occurred: The package is failed due to following: The SSIS Runtime has failed to enlist the OLE DB connection in a distributed transaction with error 0x8004D024 "The transaction manager has disabled its support for remote/network transactions.".
What I am doing is connecting to 3 DB in the same server and doing some data manipulation. The MSDTC is running in the Target SQL Server and also the DTC Server in my Local is started and running. What else could be the problem.
I'm after running into something in SSIS 2012 that I fail to grasp.
I have a package that provides a service to other packages. In order to provide that service it needs 4 parameters provided by the caller. So naturally I'm thinking I make those 4 parameters 'required'.
The caller uses Execute package task and provides the 4 parameters on the parameter mapping tab.
Yet the packages fails with the error message that one or more required parameters weren't provided.
I running SSIS package job without sql agent , it is working fine.when i am running through sql agent not running.
created Proxy account job failed and give above error. Server is cluster and taking data from desktop. server is in one domain and desktop in another domain.
1) Create a global variable. 2) assign a value to the variable using an SQL Task. (For this all I want to do is assign a maximum value from a column in my table)
3) use the variable in a Data flow task (using SQL to get my source data)
I know this must be very simple but I am new to SSIS and I am struggling to achieve this task.
I'm trying to use a variable in a OLEDB Command, but i can't do it. What i want to do is: Step 1: Count the number of rows of a table (using count rows transofrmation) and save it to a variable 'var1'. Step2: Insert some rows on this table. Step 3: Count again the number of rows and save it on another variable 'var2'. Step 4: Ina OLEDB Command , get the inserted records in step 2 to manipulate them on other tables...
Something like that: select top (@var2 - @var1) * from table order by desc
¿Can i use local or global variables from an OLEDB Command transformation object?
I have a process which connects to a SQL server remotely, runs some code, creates a temporary table with an output. I want to be able to select the results of this table into XML (not a problem), put the results into a variable in SSIS, and put the variable results into a SQL table a different SQL Instance. Doing it this way removes the footprint of needing a normal table on the source SQL Server.
Before in DTS, I used to using ActiveX scripts to setup simple Global Variables that would later be used by the package, let's say a give state or a date. I'm completely lost how to do the same in SSIS, can someone give me a hand? I'm not sure what component to use and how to read this variable.
Hello, I am wondering what is the difference between assigning variables in the following two ways.1. Dim myVar11 As Variables Dts.VariableDispenser.LockOneForWrite("LoopCount", myVar11) myVar11("LoopCount").Value = 32. Dts.Variables("LoopCount").Value = 3I received this error when processing SSIS Package.
Error: A deadlock was detected while trying to lock variables "User::LoopCount" for read/write access. A lock cannot be acquired after 16 attempts. The locks timed out.
I have a SSIS package that was migrated from DTS 2000. It had a variable that I passed to the DTS package when executing. Now when I try executing the SSIS package with the variable, I get the following error: The package path referenced an object that cannot be found: "package.variables[user::SnapShotDate].value". This occurs when an attempt is made to resolve a package path to an object that cannot be found.
From everything that I can find, I am passing the variable correctly. If I look at the package within BIDS, my variable is listed under the variable window & it is scoped for the package. Any ideas on what I might be missing here? This is my first SSIS package that contains a variable.
I have a simple task that I would like to manage through a SSIS package but do not know how to accomplish it. I need to perform the following tasks:
1. update a sql server table (ecwcust) and set the ftpstatus column to 'P' when the column's value is 'E'. update ecwcust set ftpstatus = 'P' where ftpstatus = 'E' 2. declare a variable and set the variable to the number of rows that have a ftpstatus equal to 'P' declare @newcustomercount int select @newcustomercount = count(*) from ecwcust where ftpstatus = 'P' 3. if @newcustomercount > 0, copy all of the rows from ecwcust where ftpstatus = 'P' to an excel spreadsheet. 4. send an email to an email recipient with the excel spreadsheet as an attachment. 5. delete the excel spreadsheet.
I do not know how to create the variable @newcustomercount so that I can determine whether or not to create the excel spreadsheet and copy the records to it. I have read all the online help and stuff that MS has but I can't understand the instructions. Please point me to some instructions that are understandable.
I have a script which grabs data from a source server. The problem is I have over 50 source servers, so I have to make a script for each one.. EXACTLY the same code but different linked server address.
So I was wondering if i can put the server name into a variable so I only need to make one script. Is that possible?
I have a very simple package which consists of a SQL Task component and a Script Task.
The SQL Task retrieves a single value from 1 row table in one of my database and assigns that value to a variable. The Script task simply outputs the value of the variable in to a message box. I know, it's all simple stuff (I am trying to master SSIS )
The problem I have is that when I change the value of the column in my table, the value of the variable is not changing when I re-run my package. It seems to be the same value every time.
in my package, I have serveral data flow sources using a same date value. the date value is different everytime the package is executed. (e.g. the date is two weeks before the execution date) I want to create something in my package which can dynamically calculate the datetime and pass it to all the data flow sources.
I thought variable is a good choice. but i don't know how to make the value dynamic.
I need to pass a value to a package level variable in an SSIS package from a SQL Agent job. If I use dtexecui the syntax for passing the variable in the Property Path is
Package.Variables[User::MyVar].Properties[Value]
and the package runs fine.
When I use this syntax, or any similar format I can think of, in SQL Agent, my job fails. In SQL Agent you put this in the Property Path on the Set Values tab of the Step that runs the package. If I remove passing in the variable my package runs fine.
BOL says use the format Package<container name>.<property name> but I can find no examples. Can anyone provide a working sample to make this work?
I have this procedure to remove certain characters from file names.
The SQL Task has this: exec dbo.spCleanseFileName @strFileName = ?, @strFileNameCleansed = ?
The stored procedure: CREATE PROCEDURE [dbo].[spCleanseFileName](@strFileName varchar(40),@strFileNameCleansed varchar(40) output)
I have it in an SSIS package and my problem is that, after that SQL Task completes, the value for the ),@strFileNameCleansed variable is blank. I HAVE confirmed that the procedure DOES set the correct value inside the SP.
I have an SSIS package which uses variables and foreach loop containers so connect to multiple instances to retrieve config data. I am adding an extra step to include the port of each instance.
I used a select from a central table to get the connection strings, which is put into [User::Server2]
I'm trying to understand how to use SSIS variables within SQL commands and which Data Flow toolbox objects I can use for this. My simple package has a OLE DB Source object that returns the rows in a table. For the next operation, the output from the OLE DB Source is input into a Lookup data flow transformation where there is a select query that returns another value from a different table...
select bla from myTable where dbname = db_name()
The output is then passed on to an OLE DB Destination.
The above lookup query only works because, it so happens, that I could get a unique and correct result back filtered by current database name. However, due to other required changes, I now have introduce more 'where clauses'. I have all the necessary clause information in SSIS variables (iterated via a For Each loop). I would like do something like...
select bla from myTable where dbname = ? and bla_type = ?
However, there isn't a facility for Parameters in the Lookup object's 'use results of a SQL query'. I can see that I can use parameters in the OLE DB Source object by changing the 'data access mode' to SQL Query. However, it doesn't seem valid to join two OLE DB Source objects together in the data flow? I can't see any other suitable objects in the Data Flow toolbox. Ideally, I would be able to use Parameters in the Lookup object or some other object that would do the same job.
I am not able to access SSIS variables which are defined at Data Flow Task in a custom component. This custom component is developed by me in C#. How can i access these variables?
Please let me know if theres a way to access SSIS variables.
This is a fun one. I have a job that runs a SSIS package. It has some variables that I set a runtime. How can I programmatically change the values for these variables using SMO? I can get to the step in the job using VB but I can't find a way to programmatically change the value of the parameters of the job. The code loops through every property of the SSIS step. There's a command string but that's an ugly beast to code against. Any help is appreciated.
I'm not sure if this is the right forum, but i'm having an issue where SSIS Variables window will not come up. The most i get is a gray bar that looks like it is supposed to hold the variables.
I've tried getting it to show by: 1) Menu --> SSIS --> Variables 2) Right Click --> Variables 3) Menu --> View --> Other Windows --> Variables
The same thing seems to happen for "log events" in Menu --> View --> Other Windows --> Log Events.
Does anyone know how to fix this? Is anyone else having this problem?!?!?!
