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.
(sorry, I don't know how to make the copied tables 'look right' but spaces should --> tabs) Branches Table B_ID Branch 0 OLD 1 ADM 2 CL1 3 CL2 4 NEB 5 PTO 6 HOL 9 MKT 10 CL3 12 IOW 15 IND 16 PAP 18 SOU 19 NOR Value Table (Pk = Vid, FK = Bid) VID BID Emp_id Value 1 4 1 100 2 4 1 200 3 10 1 300 4 3 2 400 5 4 2 500 6 4 2 600
Desired Result View (Pivot on On Emp_id sum the value column) Emp_ID OLD ADM CL1 CL2 NEB PTO HOL MKT CL3 IOW IND PAP SOU NOR 1 0 0 0 0 300 0 0 0 0 0 300 0 0 0 2 0 0 0 400 1100 0 0 0 0 0 0 0 0 0
My current solution is to use a pivot query similar to the below (note: ..etc. used to simplify the discussion), It gets me close with the exception of presenting the null values as null rather than 0, I can make that work! SELECT Emp_id,[0] AS OLD, [1] AS AD, [2] AS CL1, [3] AS CL2, [4] AS NEB ....etc) FROM (SELECT Emp_id, value, B_id FROM Branches) P PIVOT (sum(value) FOR B_idId IN ([0], [1], [2], [3], [4].."etc.")) AS pvt "Take the data from the Branches table, sum up the values for a employee then present as a "pivoted" sql view with each branch in a column and nulls converted to 0"
Finally here is the problem/question.
I don't want to 'hard' code the branches (as the solution above does), they will change over time as branches are added and I want the query to reflect these changes. The problem, I don't know how to proceed from here, I have looked at using a SSAS solution, but that seems like too much for such a simple problem and in any case, I don't know how to integrate the SSAS cube into the current Ms Access 2000 solution (moving from Access backend data store to SQL server 2005).
Note: all the examples I have found in the BOL and forums, hard code the columns
I am frankly overwhelmed be the number of options available in SQL Server 2005, and still am trying to 'get my mind' around some of the concepts. I'm sure I don't know many of the options available, much more how to best implement them. The BOL, while good, have not really provided the enlightment I seek.
If I have to go to a CLR solution, could one provide some simple guidance, Thanks.
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.
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?!?!?!
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
I'm trying this code in a script task inside a foreach loop that gets a file in a foreach file enumerator and maps it to a variable FileName. In the script task I'm setting the ReadOnlyVariables to @[User::FileName] : Imports System.IO Imports System Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Runtime Public Class ScriptMain Public Sub Main() Dim fname As String = CType(Dts.Variables("FileName").Value, String) File.Move(fname, fname + ".processed") Dts.TaskResult = Dts.Results.Success End Sub End Class But now getting this error: Error: Failed to lock variable "\xxxxDataf1.csv" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.".
As you can see the variable does appear to be resolving.
I have finally been able to get access to SQL Server 2012, so I am just now getting to play with some of the features. My new company has multiple ways of calling an SSIS package, and some (if not all) use Package Parameters. I just did a little research on these, and I in general get how they work. Here is why I ask...
I liked how my last company had dynamic variables setup. They had all of the required variables stored on a single database table on a single server. This kept all of the connections strings, file paths, file names, etc. that a package would use. My computer, the UAT server, and the Prod server all had the same file in the same location with 1 character difference to note which environment to run in. This way I knew I would always use only the Dev server when I ran a package during development. I would also only touch the test folders with my test data. Once we moved it to UAT or Prod, that single character difference would point to a different set of variables for the same PackageID, and we didn't have to worry about forgetting to change hard coded variable values. The only 2 hard coded variable values were the PackageID and the variable holder database. I have heard of similar ways of doing this via an XML file, but I have not had the (ahem) pleasure of working with that yet.
Jump to my current job and why so far I have not been impressed with the Package Parameters. We have 3 different jobs that all call the same SSIS package. We feed in a different FacilityID, and using values stored in tables we know to look for different file names, etc. To me this seems to be a similar set up to how my last company had set the system. The big difference is my old company used a Script Task to verify which environment we were running in (I can see setting this for different FacilityID's, but that would be cumbersome), and my new company uses multiple tables across multiple databases (I know, this is not the best set up in the world, and we are fixing it soon) to decide what we will use for some of the variable values.
What are your thoughts on storing variables outside a package? Do you have any blog posts or articles you can link to show current best practices?do the Package Parameters only work via the Integrated Services Catalog, or can they be accessed for the File System packages too?
I have created two User variables at Package level (OldRunID{Int32} and NewRunID{Int32}). I am using two simple ExecuteSQL Tasks to get a Single Row Resultset return (each) and loading them (on Result Set Page). Using the Constraint and Expression Flow controls, I have found that they load to True (-1) instead of the 25 and 50, respectively, that I would expect. I then wrote a Script task to evaluate their values and find that I get zero for Dts.Variables.Count. - Why am I not loading the variables correctly and why can I not access them in the Script task?
