.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

In SSIS, can I write the value of a variable to a SQL table?

Posted By:      Posted Date: April 14, 2011    Points: 0   Category :

This seems like it should be easy to do, but I can't figure it out.

In my Control Flow I have an Execute SQL task that populates an ADO Recordset variable. That works fine, the variable holds a list of server names.

Then I have that connected to a Foreach Loop Container, which loops through the server names, connects to each server, reads some info, and writes that to a table. That also works, except for those servers that it can't connect to.

So here's what I'd like to do: before I connect to the server, write the value of the variable (which contains the server name) to a table in SQL, along with getdate() so I know when it started trying to connect.

How do I just write the value of a variable to a table? If I just use an OLE DB Destination, there are no input columns.

My best thought was to use a Script Component and somehow get that value, and then somehow make it an output column, which I then connect to the OLE DB Destination as an input column??

If I do this in a C# Script Component:



it works just fine, so I know my variable is there an available.

Any suggestio

View Complete Post

More Related Resource Links

How to load a new table with the value of a variable from SSIS package?

Hi, i have two variables in a SSIS package Var1 and Var2. Both of these variables have values. is there any way i can put the values of these two variables in a new table? e.g In New table col1 having value of Var1 and col2 having value of Var2. Thanks

How to send record(which is a weblink) from a table to the value of the variable in SSIS package and

Hi Folks, I have table called Table1 with columns, col1 and col2 with col1 having weblinks for the report and col2 the name of the report. Now, i have a package with a variables var1 and var2 which should get the col1 and col2 values respectively from table1 and send it through an email. if the weblink gets updated in the table, package should send the updated link. i know the reverse way of it but trying to do somethig like this. Appreciate any help from you guys. Thanks

How to declare a table variable in SSIS and then insert rows into it

Hello everyone,   I'll try to explain my problem as clearly as I can: 1)I have an Execute SQL Task that's inside a Foreach Loop Container. 2)Inside the Execute SQL Task, I have an int OUTPUT column. 3)With each iteration, I need to insert the data from the INT output colum into a table variable. 4)After the Foreach Loop finishes the iterations, I need to use the table variable to create a report based on the data inside.   My question is the following: How can I declare a table variable so that I can do all of the above?   Thank you, CostinP

SSIS Create table using variable

Im working on my case study about inserting all the records of a flat file source to a OLEDB Destination. The problem is that I want to create a table using a sql query stored in a variable. How will I do that? Thanks

Error while trying to assign a value to a Read Write variable in SSIS package script component



       I am trying to develop a SSIS package which will read the records from the flat file and insert them into a destination table. I have some validations written in script component. I have declared two Read Write variables with package level scope. when i try to assign a value to the variable in the script component and run the package, the package throws me an error "The collection of variables locked for read and write access is not available outside of PostExecute".


What should be done to over come the problem please help me on this regard 




SSIS Package Transaction locks entire table

Hello, I have SSIS package that using transactions and working as expected. But this locks all the tables involved in the data flow that are get inserted/loaded. If I query those tables during package execution the query waits till SSIS releases the lock. But I can still query those tables using NOLOCK hint. Is this possible SSIS will lock only the rows that it insert/update/delete etc? So that other user can still use that table or another instance of same SSIS Package can load data into those tables? My Package: In the package level I have TransactionOption = Required. The Sequence (Data Flow is inside this sequence) TransactionOption = Supported. I had some issue in my package that I posted here: http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/dfb5a8b9-d43c-452b-bb21-d6aea192fc97 and also resolved. Now I descovered this issue and trying to resolve. Thanks, Prabhat

SSIS Deployment Reads local Environment Variable and not Server

Hi All, I have an SSIS package that uses an Environment variable to hold the connection string for the local SSISConfiguration table.  This works properly on my local machine.  However, when I import the package to Integration Services(through SSMS) it reads the environment variable from the local machine and not the server it is deployed to.  If I log on to the server remotely and do the same process it uses the the correct environment varible... (SQL Server 2005) Is there a way to deploy the package to the server and get it to use the correct Env variable without having to log-in to the server and import it?  Let me know if anyone is looking for additional information.   Thanks!

Using EXEC with a table variable?

I want to pass the results of a stored proc into a table variable in SQL SEVER 2000. Something like this declare @a table ( employeeid int )   exec @a=dbo.MetricsProcessor_GetTopEmployees @parameter1, @parameter2   ---------------------------------------------------------------------------------------------------------------------------------- I tried insert @a execute dbo.MetricsProcessor_GetTopEmployees @parameter1, @parameter2    but got this error: Msg 197, Level 15, State 1, Line 6 EXECUTE cannot be used as a source when inserting into a table variable.   How can i do this.   Thanks.

Using a Variable in SSIS - Error - "Command text was not set for the command object.".

Hi All, i am using a OLE DB Source in my dataflow component and want to select rows from the source based on the Name I enter during execution time. I have created two variables, enterName - String packageLevel (will store the name I enter) myVar - String packageLevel. (to store the query) I am assigning this query to the myVar variable, "Select * from db.Users where (UsrName =  " + @[User::enterName] + " )" Now in the OLE Db source, I have selected as Sql Command from Variable, and I am getting the variable, enterName,. I select that and when I click on OK am getting this error.   Error at Data Flow Task [OLE DB Source [1]]: An OLE DB error has occurred. Error code: 0x80040E0C.An OLE DB record is available.  Source: "Microsoft SQL Native Client"  Hresult: 0x80040E0C  Description: "Command text was not set for the command object.". Can Someone guide me whr am going wrong? myVar variable, i have set the ExecuteAsExpression  Property to true too. Please let me know where am going wrong? Thanks in advance.

Comparing Null Values(from variable) in SSIS

Hi All I need to create a script that checks to see if a variable value is NULL, if it is then it executes the next SQL Task and if not then the package has finished. I have this in a SQL 2000 DTS package script: Function Main() If isnull(DTSGlobalVariables("NextLoadDate")) Then Main = DTSStepScriptResult_DontExecuteTask Else Main = DTSStepScriptResult_ExecuteTask End if End Function As you can see, all I want to do is check to see if a variable is null then don't execute the next task. But i cannot get it to work in a SQL 2005 SSIS package, so far I have created a script and come up with the following: Public Sub Main() If Dts.Variables("NextLoadDate").Value.ToString <> "" Then Dts.TaskResult = Dts.Results.Success Else Dts.TaskResult = Dts.Results.Failure End If End Sub Any Ideas? Thanks BigGopher

Stored Procedure utilizing table variable

I'm creating a stored procedure that will take the results of a temp table and check for existence using the IN keyword.  The code looks like this: ALTER PROCEDURE [dbo].[studentBranchLogin] -- Add the parameters for the stored procedure here @userName varchar(50), @userPass varchar(20) AS BEGIN DECLARE @dActive bit; DECLARE @TT TABLE (dept int, active bit); -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; INSERT INTO @TT(dept, active) SELECT deptid, active FROM srcuDeptId WHERE active = TRUE -- Insert statements for procedure here SELECT srcuid FROM srcuLogins WHERE logon = @userName AND password = @userPass AND srcuid IN @TT END I get a Msg 170, Level 15 state 1 Line 24: Incorrect syntax near '@TT'. So while I'm sure the @TT temp table isn't being read, I find very few resources for answer what the proper syntax should be.   Any assistance is greatly appreciated.

Passing Table Variable to Stored Procedure

My stored procedure expects a uniqueidentifier as a parameter.  However, this unique identifier is stored in a table variable, which looks like this:DECLARE @TableGiftGuid table ( GiftGuid uniqueidentifier )When I try to execute a stored procedure by passing the GiftGuid like so:EXEC my_procedure (SELECT GiftGuid FROM @TableGiftGuid) I get an error. What I ended up doing is declaring another variable, storing the GiftGuid into it and then passing the variable to the stored procedure like this: DECLARE @TableGiftGuid table ( GiftGuid uniqueidentifier ) DECLARE @GiftGuid uniqueidentifieDECLARE @TableGiftGuid table ( GiftGuid uniqueidentifier ) DECLARE @GiftGuid uniqueidentifier SET @GiftGuid = (SELECT GiftGuid FROM @TableGiftGuid) EXEC my_procedure @GiftGuidThat works, but is there a more elegant way of doing this?

can create table statement be used in and ssis expresssion?

I need to modify the preparation sql task built by the import wizard. I wanted to create an expression  using the create table sql but insert the variable to append the date and another descriptor  to make it dynamic. The source is Excel The expression would look something like this "Create Table" + @user::descriptor +  @user::todaysdate+" ( name varchar(50) ..........." SSIS cannot evaluate the sql although it runs fine in ssms The column mappings will always be the same but the table name needs to be dynamic

update table with SSIS

  Hi, sorru for my english   I have two tables: intervenant: idInt (primary key) name login (foreign key) (accepting the null) compte: login (primary key) idInt (foreign key) I'd like to know how to do an UPDATE in the table "intervenant" once I filled the login field of the "compte" table.     thanks

Oracle clustered table to MS SQL Server 05 SSIS

Hi Guys, Can the SSIS deal with clustered Oracle tables? Moreoever, what are clustered tables compared to partitioned tables? I cant find much about it. (only sytax how to create them). Thanks, metalray

Adding Oracle table into SSIS project --Oracle error occurred, but error message could not be retrie

Hi all I am new to design SSIS packages,to day I stated creating package SSIS package and adding the Tables,while adding tables,one of the table giving an below error . I verified the table structure it has one "CLOB" datatype. can some body help how to add this table( whole idea of the package is I have Dump those tables with structure and Data to SQL Server 2008 Database)  =================================== Oracle error occurred, but error message could not be retrieved from Oracle. Data type is not supported. (Microsoft Visual Studio) ------------------------------ Program Location: at System.Data.OleDb.OleDbCommand.ProcessResults(OleDbHResult hr) at System.Data.OleDb.OleDbCommand.PrepareCommandText(Int32 expectedExecutionCount) at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior) at System.Data.OleDb.OleDbCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior) at Microsoft.DataWarehouse.Design.DataSourceConnection.FillDataSet(DataSet dataSet, String schemaName, String tableName, String tableType) at Microsoft.AnalysisServices.Design.DataSourceDesigner.AddRemoveObjectsFromDSV() Thanks in Advance.   SNIVAS

How to deploy and configure ssis packages using sql server table in testing server and production en

Hi We have three environments (development, testing and production ) I have to configure and deploy the ssis packages using sqlserver table in different environments(development, testing and production).   Three environments database structures  are same But database names are different like (DEVSERVER,SITSERVER,UATSERVER) We have 14 packages and one master package. In master package we called all 14 packages question1 First time i will deploy all (master and 14 packages). If i modify one package we need to deploy all the packages or single package deployment is enough    Please help me thanks in advance          
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend