Hi there,
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?
thanks,
Hi,
I have sql server 2005 express and toolkits installed. However, I only see Report Server projects in my Visual Studio 2005 when I choose a Business Intelligence project.
Will someone please help me download the right file to start using SSIS designer?
Thanks,
Jae
Not quite sure if this is the right place for this comment...
While using SSIS Designer came across a DUE where the package was setup to:
SEQ1 - Truncate staging tables
SEQ2 - Load staging tables (sequence container had been setup to hold multiple data flows)
For some reason one data flow was run before the truncate table sequence.
Looking in the designer it appeared that the dataflow was a member of SEQ2 but looking at the Package Explorer it was outside the sequence container.
It would be good to have some visible indicator to show that a task is a member/not a member of a container
John.
When I drop a new component onto the design surface, it appears with dotted lines around it, as it is selected.
But, the F2 key (the hotkey for rename) does not work.
I have to click on some other component, and then back on the new component, and then the F2 key works.
I remember reporting this bug back in the beta cycle, but it is still present even in the release -- I actually think Ã?'m using the SP1 version of 2005, but see version info below to be sure:
Remote terminal services broke my copy buffer again, as is so often does, so pasting in the version info failed -- falling back to manual typing -- SSIS 9.00.2047.00.
Where can I find instructions on how to use it?
View 1 Replies View RelatedHow to Install SQL Server SSIS Designer? Is it part of SQL Server Buisness Intelligence?
View 5 Replies View RelatedHello,
This morning I got an odd problem, I just added a new package to my dtproj and I cannot add a variable. Am I missing anything. ?
here is the image
I am executing a single package that references 180 other packages , after executing the first 90-100 packages , SSIS designer completely freezes and then i have to kill the session using Task Manager . Is this a limitation of SSIS or is it a system constraint ?
If you have any suggestions or workarounds for de same plz do reply
Thanks in Advance
Shailesh
Hi,
When I installed SP1 on my SQL Server 2005, the performance of the package designer dropped enormously. I have to wait like 30 seconds after I drag-and-drop a datasource from the toolbox to the design area. Also it takes a very long time to run the very simple package. Not to mention opening some complicated package that was done before applying SP1. The package scheduled in SQL Agent went from 3-4min to 5-6min.
Can anyone address this issue? Maybe there's something I did wrong?
While installing SP1 I ecountered strange behaviour (some messages about file locking, rebooting and stuff), but in the end it said that it was successful
Best Regards
Wojtek
How do I remove a custom component from SSIS Designer pernamently? I can easily remove it from the tool box, but I want to get rid of it completely.
I tried searching in MSDN for an answer to this question but I could not find it.
Thanks
Our Visual Studio installation suddenly starting producing the error below upon trying to open any .dtsx package; although by "suddenly" I'm sure it was due to some Windows update or other program install, update, or uninstall. I've spent two days uninstalling, reinstalling, and repairing everything that is seemingly related to VS, but no luck. I've searched the forums and could not find this specific error mentioned, although I have tried some of the suggestions to fix possibly related 'class not registered' errors, but again no luck.
===================================
There was an error displaying the layout. (Microsoft Visual Studio)
------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.762&EvtSrc=Microsoft.DataTransformationServices.Design.SR&EvtID=DDSErrorDeserializeDiagram&LinkId=20476
===================================
Class not registered (Exception from HRESULT: 0x80040154 (REGDB_E_CLASSNOTREG)) (msddsp)
------------------------------
Program Location:
at MSDDS.IDdsDiagram.CreateShape(String ProgID, Boolean Visible, Int32 X, Int32 Y)
at Microsoft.DataWarehouse.Controls.Interop.AxMSDDS.CreateShape(String progID, Boolean visible, Int32 x, Int32 y)
at Microsoft.DataWarehouse.Design.ComponentDiagram.OnCreateShape(Object logicalObject, Int32 x, Int32 y, Boolean visible)
at Microsoft.DataTransformationServices.Design.DtsComponentDiagram.OnCreateShape(Object component, Int32 x, Int32 y, Boolean visible)
at Microsoft.DataWarehouse.Design.ComponentDiagram.CreateShape(Object logicalObject, Int32 x, Int32 y)
at Microsoft.DataTransformationServices.Design.DtrControlFlowDiagram.CreateExecutableShape(DtsContainer dtsContainer, Int32 x, Int32 y, Int32 width, Int32 height)
at Microsoft.DataTransformationServices.Design.DtrControlFlowDiagram.RefreshSequenceExecutables(IDTSSequence sequence)
at Microsoft.DataTransformationServices.Design.DtrControlFlowDiagram.AfterDeserialize()
Any help would be greatly appreciated.
TIA,
Scott
Has anybody seen this issue already?
If you try to "enable memory restriction" from the Lookup component GUI you need to input both 32 and 64 bit size of maximum memory. However when clicking "OK" on the editor you get a message like :
TITLE: Microsoft Visual Studio
------------------------------
Error at Update Execution Logs [Lookup folder path 1 [4429]]: Failed to set property "MaxMemoryUsage64" on "component "Lookup folder path 1" (4429)".
------------------------------
ADDITIONAL INFORMATION:
Exception from HRESULT: 0xC0204006 (Microsoft.SqlServer.DTSPipelineWrap)
------------------------------
BUTTONS:
OK
------------------------------
You can only set the properties directly for mthe Properties window (F4).
I think this is a post SP1 bug in Visual studio designer...
Unlike SQL Server 2000 DTS, SSIS Flat File Connection Manager Editor does not provide available list of Text qualifier,
i tried
-- ""
-- double quote {"}
-- "
--{"}
but none of them worked....!
My file sample looks like this
"Col1","Col2"
"1234","3456"
"3456","1234"
what qualifier should I use then?
Many Thanks,
Hi there,
This is what I am trying to do :
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.
Thanks for your help in advance.
Hi,
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.
View 2 Replies View RelatedBefore 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.
Your help is appreciated,
-Lawrence
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.
Let me know if you need more info.
Thanks!
John
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.
Greetings my SQL friends,
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.
What am I doing wrong?!
Your help would be much appreciated.
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.
anyone please help.
thanks
Hi all of you,
I don't think that such thing is possible but either way, I ask, just in case...
We're doing lots of packages which must have the same variables (in terms of datatype, name, etc..)
TIA for that,
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?
Thanks.
I have defined an active X task (converting from SQL 2000)
Function Main()
DTSGLOBALVARIABLES("TEST").Value= InputBox("TEST : ","Message Box")
Main = DTSTaskExecResult_Success
End Function
The problem is that this variable is not recognized by the subsequent tasks although I have defined a variable "TEST" in the variable lists.
Is there something else to do to get it recognized ?
Thanks for helping,
Pierre
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]
1) execute SQL task to collect the port
(DECLARE @portNumber NVARCHAR(10)
EXEC xp_instance_regread
@rootkey = 'HKEY_LOCAL_MACHINE',
@key =
'SoftwareMicrosoftMicrosoft SQL ServerMSSQLServerSuperSocketNetLibTcpIpAll',
@value_name = 'TcpPort',
@value = @portNumber OUTPUT
)
Which is then put into a variable @Portnumber
2) I then need to insert this into a certain server with a where clause including another variable
so something like
"INSERT INTO DBO.InstanceConfig VALUES ('"+@[User::Portnumber]+"') where Serverinstance = ('"+@[User::Server2]+"')
but it doesnt work like that.. is there an easier way
How do i access global variables in SSIS scripts ?
the Dts.variables("VarName").Value
should work but doesn't ?
do i need to reference the variable another way ?
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.
Thanks,
Clive
Can any one please help me. how to pass the database connection through variables to SSIS.
And also calling SSIS from visual studio.net
Regards
Ravi
Hi
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.
Thanks,
Vipul
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.
With oStep
Console.Write("Name: " + .Name + vbTab)
Console.Write("Proxy Account: " + .ProxyName + vbTab)
Console.Write("Type: " + .SubSystem.ToString + vbTab)
Console.WriteLine("Urn to point to job: " + .Urn.ToString)
iProp = .Properties.GetEnumerator
While iProp.MoveNext
Console.WriteLine("Property Name/Value: " + Convert.ToString(iProp.Current))
End While
End With
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?!?!?!
-Thanks Thames