hi,
I have an aggregate transformation in a dataflow task.
It has only 1 output value.
I'm trying to assign this value to a user variable, but I can't figure out how to do that.
i can hack something silly together - like write the value to the db, and then get it out, but I there has to be an easier way..
Thanks a lot.!
Hi Everyone,
In the data flow task, i have done a group by and now i have a single row.... I want to assign the value in this row to a package variable.... Without using the script component .......Any suggestions ??
Regards,
Manu
Hi:
In the derived column transformation editor, I have a Derived column name called FileGroupID. I would like to pass in a value for this column from a variable that I have set earlier in the scope. Can someone let me know, how to write the expression that does that and where do I specifiy that expression. I am thinking its the expression field in the derived column transformation editor. My main question is how to actually write the expression, what is the syntax to pull the variable value? Thanks.
MA2005
All,
Is it possible to passing variable at row level within a data flow? If so, what transformation should use?
Thanks
I'm currently setting variables at the package level with an ExecuteSQL task. This works fine. However, I'm now starting to think about restartability midway through a package. It would be nice to have the variable(s) needed in a data flow set within the data flow so that I only have to restart that task.
Is there a way to do that using an SQL statement as the source of the value in a data flow?
OR, when using checkpoints will it save variable settings so that they are available when the package is restarted? This would make my issue a moot point.
We are designing an ETL solution for a BI project using SSIS.
We need to load a dimension table from a source DB into the DW; inside the DW there are two tables for each source dimension table: a current dimension table and a storical dimension table.
The source table includes technical columns that indicate if each record was processed correctly by ETL procedures.
Each row in the source table can be a new record, or an exisisting one. If it's a new record, a corresponding new record should be inserted into the current dimension table of the DW; if it's an exisisting one, a new record should be inserted in the storical dimension table and the existing record in the current dimension table should be updated.
Furthermore for each record we need to update the source table with an error code. If the record was processed with succes the code is zero, otherwise it contains an error code.
I try to use a single data flow task, using the 'ole db command trasformation' task for managing update and 'ole db destination' task for managing insert.
The problem is that some insert and update should be executed inside a single transaction, for each record; for example if the source contains a new record I should insert the record in the current dimension table and update the source record with error code zero if every think goes fine.
There is some way to group task in the data flow pane in a single transaction ?
There is a better way to do that ?
Cosimo
Hopefully this is an easy question:
Inside of a for each loop (looping through an ADO record set of objects to import) I have a data flow task (along with many other processes).... if the dataflow task suceeds I log success in a table. If it errors I want it to fail the dataflow task (which will fire off my Event Handler for that data flow and log the failure, email etc) BUT I want it to continue the loop - I can't seem to figure out how to get the data flow object not to fail the whole loop. If any other objects inside the foreach, other than the data flow, fail I would like the whole loop to fail. Also if possible (but not a requirement) I would like it to have a threshold where if the data flow fails X variable times it will fail the package.
I am having difficulty how to not fail the loop when the import data fails..... just looking for a simple "on error next" type logic for that specific object in the foreach but not the rest. Thanks in advance for the help/advice.
Hi,
I have tables like the one below for my Stage and dimension tables:
Stage Table
accountid
name
address
Dimension Table
accountkey ---- surrogate key (DW key)
accountid ---- business key (transaction's primary key)
name
address
I used slowly changing dimension to detect the changes for the records inside my Dimension table. But I had a problem when a new record exists in the stage table. The accountkey is set as the primary key and it gets its value from a different table which stores the last account key that was created. I cannot load all the changes unless i have a business key. Is there a way that i can get the "last key" from a different table in the data flow area and then supply it together with the other fields in the new output branch of the slowly changing dimension?
cherriesh
thanks!
Hi
Is there any task which helps up to update data inside the data flow task or any method to do the same.....
how to pass the column that has a numeric(12,0) data type to user variable in SSIS? what kind of variable data type should I choose?
if i select int64, it keep giving me an error:
Error: 0xC001F009 at Row by Row process: The type of the value being assigned to variable "User:bject_Key" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
there is no numeric data type in variable..
if you click the drop down box in variable data type, you can only see the below data type:
Int32
int64
Object
Sbyte
single
string
uint32
uint64
boolean
byte
char
Hi,
How do I retrieve the connections (connection managers) collections from Custom Data Flow destination? ComponentMetadata.RuntimeConnectionCollection is empty. I would like to be able to access all the connections defined in the package from the custom data flow task.
I came across code in which it was possible to access the Connections collection using the IDtsConnectionService for custom task (destination). The custom task has access to serviceProvider, whcih can be used to get access to the IDtsConnectionService interface but not the custom data flow task.
Any help appreciated.
Thanks
Naveen
Hi,
I would like to know is there any way we can assign/change the value of variable within data flow without using script component?
Thx.
Hi all,
By using for each loop container and script task, i am able to pick the file name from a specified folder to a user defined variable. Now i am trying to pass this variable to excel source (using data flow), but i am getting this error : -
===================================
Error at Data Flow Task [Excel Source [1]]: A destination table name has not been provided.
(Microsoft Visual Studio)
===================================
Exception from HRESULT: 0xC0202042 (Microsoft.SqlServer.DTSPipelineWrap)
------------------------------
Program Location:
at Microsoft.SqlServer.Dts.Pipeline.Wrapper.CManagedComponentWrapperClass.ReinitializeMetaData()
at Microsoft.DataTransformationServices.DataFlowUI.DataFlowComponentUI.ReinitializeMetadata()
at Microsoft.DataTransformationServices.DataFlowUI.DataFlowAdapterUI.connectionPage_SaveConnectionAttributes(Object sender, ConnectionAttributesEventArgs args)
Please can you suggest me how should i pass the vaiable to the data flow and how the Excel sheet will be selected there.
Hi all,
By using for each loop container and script task, i am able to pick the file name from a specified folder to a user defined variable. Now i am trying to pass this variable to excel source (using data flow), but i am getting this error : -
===================================
Error at Data Flow Task [Excel Source [1]]: A destination table name has not been provided.
(Microsoft Visual Studio)
===================================
Exception from HRESULT: 0xC0202042 (Microsoft.SqlServer.DTSPipelineWrap)
------------------------------
Program Location:
at Microsoft.SqlServer.Dts.Pipeline.Wrapper.CManagedComponentWrapperClass.ReinitializeMetaData()
at Microsoft.DataTransformationServices.DataFlowUI.DataFlowComponentUI.ReinitializeMetadata()
at Microsoft.DataTransformationServices.DataFlowUI.DataFlowAdapterUI.connectionPage_SaveConnectionAttributes(Object sender, ConnectionAttributesEventArgs args)
Please can you suggest me how should i pass the vaiable to the data flow and how the Excel sheet will be selected there.
Hello There:
I am running a data flow within a ForEach loop wherein I am computing a value called QuotaGap. When it is 0 I do not want any further execution of the loop. I am using a Conditional Transform within this dataflow that writes a record to a table only when the QuotaGap is NOT 0. However, I am unable to terminate the execution of the loop as I am still within the dataflow.
Now, the computation of the gap requires a value from another variable called NetPurchases. I tried using an ExecuteSQL task in the control flow but could not figure out how to pass the value of the variable NetPurchases into the select statement to compute the gap. For example, the select statement would read:
select (QuotaUpperLimit - ?) As QuotaGap from <<tablename>>
I tried setting the parameter as an input as well as an output and it did not work.
Then I tried passing the entire SQL as a string within a variable. This does not work either because in order to compute the math QuotaUpperLimit - NetPurchases, both variables need to be integers but then you cannot concatenate integres together, which is what we need to do to create the SQL.
The other reason I am going through these hoops I guess is that I have not figured out a way to set the value of a variable within a data flow. I compute the value for QuotaGap within the dataflow in a ForEach loop but I have no way to pass this result to a variable called QuotaGap without using an ExecuteSQL task or another ForEach Loop.
I have spent hours on this simple issue and so have given up and looking to the good friends in this forum for help.
If what I have stated is not clear please let me know and I will try to clarify things a bit.
Thanks!
My package sets a variable in an ExecSQL task. This variable is then used as a parameter in a DataFlow task that follows. Normally everything works just fine. However, sometimes if the package fails inbetween the step that sets the variable and the DataFlow, the default value of the variable is stored in the checkpoint file. (It is not failing in the step that sets the variable) When the package is restarted I can see that the variable is not set to the data value in the database, but rather it has its default (design time) value.
Anyone else see this bug?
Hi,
I'm having trouble with a Script Component in a data flow task. I have code that does a SqlCommand.ExecuteReader() call that throws an 'Object reference not set to an instance of an object' error. Thing is, the SqlCommand.ExecuteReader() call is already inside a Try..Catch block. Essentially I have two questions regarding this error:
a) Why doesn't my Catch block catch the exception?
b) I've made sure that my SqlCommand object and the SqlConnection property that it uses are properly instantiated, and the query is correct. Any ideas on why it is throwing that exception?
Hope someone could help.
I am able to use a custom script task to receive a MSMQ package and save the package contents to a flat file.
I can also use the bulk load task to push the flat file contents into a SQL table.
However, I would like to save the package contents to a variable (done, it works), and then pass that string variable to a data flow task for SQL upload. In other words, I don't see any reason to persist the msmq package contents to disk.
My question is: Which data flow source can I use that will accept a string variable? The string variable will then need to be processed with bulk load or an execute sql task.
Btw, the content of the string variable is a csv style string:
"01001","11/21/2006",15
"01001","11/21/2006",1
"01001","11/21/2006",25
"01001","11/21/2006",3
Thanks,
Trey
Hello All,
I have a user variable , say max_id, which retrieves the max id from a primary key column of a table.
now in Data Flow Task, I have a Derived Column where I am creating a new column which will have the values max_id + 1 for the first record, then max_id + 2 for the second record of the derived column and so on..
and then I am mapping this derived column with that primary key column of the table, so that the new id's are different than the old ones which are already in the table, so there wont be any primary key violation.
I cannot make that primary key column as an Identity column.
my question is how do I increment this max_id user variable everytime ?
Regards,
Kapadia Shalin P.
Hi all,
I am creating a customer data flow component for SSIS for use in a package. I've got some custom properties that I am exposing using the supplied advanced editor (no custom property editor here).
Some of my properties are enumerated types, and I have deciphered how to get those properties to show as dropdown lists of their respective enumerations. (For those of you who may be looking as hard as I did as to how to accomplish this, see the end of this post.)
I also have a few properties which request SSIS package variable names - such as an file name variable. However, I can't figure out how to tell the advanced editor that the property is looking for an SSIS variable, so that it can show a dropdown list of package variables, much like virtually any other Microsoft supplied Data Flow component can.
Is there a Type Converter I could specify for those custom properties? Is there another way to instruct SSIS that my custom property is expecting a variable? Or do I need to code a custom UI for editing my Data Flow Task?
To create a dropdown list of values for a custom property that represents an enum, do the following:
1. Create your enum definition, such as "public enum ThisIsMyEnum { one, two }"
2. Create a new class that inherits from TypeConverter, such as "public class MyEnumConverter : TypeConverter"
3. Override "CanConvertFrom", and return true if "sourceType == typeof(string)"
4. Override "CanConvertTo", and return true if "destinationType == typeof(string)"
5. Override "ConvertFrom", and return the enum value (such as "one" or "two" in my example) that corresponds to the string passed in the parameter "value"
6. Override "ConvertTo", and return a string that corresponds to the enum value passed in the parameter "value"
7. Override "GetStandardValuesSupported" and return true
8. Override "GetStandarValuesExclusive" and return true to indicate that ONLY the enum values should be accepted
9. Override "GetStandardValues", and return a new StandardValuesCollection constructed with Enum.GetValues() of your enum, such as "return new StandardValuesCollection(Enum.GetValues(typeof(ThisIsMyEnum)));"
10. Just above your "public enum" declaration, add a "TypeConverter" attribute to link your type converter to your enum, such as "[TypeConverter(typeof(MyEnumConverter))]"
11. In "ProvideComponentProperties", after you've created your custom property like this: "IDTSCustomProperty90 propEnum = ComponentMetaData.CustomPropertyCollection.New()", add another line to specify the TypeConverter property of the property to the full assembly name of your type converter, like so: "propEnum.TypeConverter = typeof(MyEnumConverter).AssemblyQualifiedName;"
In my Script Component properties I have included "ClientReportGroupId" as a ReadWrite variable. This variable is declared as a Package Variable.
View 23 Replies View RelatedI have a Data Flow Script Component(Destination Type) and in the properties I have a read/write variable called User::giRowCount
User::giRowCount is populated by a Row Count Component previously in the Data Flow.
After reading http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=97494&SiteID=1 it is very clear that you can actually only use variables in the PostExecute of a Data Flow Script Component or you will get an error
"Microsoft.SqlServer.Dts.Pipeline.ReadWriteVariablesNotAvailableException: The collection of variables locked for read and write access is not available outside of PostExecute."
What I need to do is actually create a file in the PreExecute and write the number of records = User::giRowCount as second line as part of the header, I also need to parse a read/write variable such as gsFilename to save me hardcoding the path
(Me.Variables.gsFilename.ToString),(Me.Variables.giRowCount.ToString)
-they must go in the PreExecute sub --workarounds please-here is the complete script component that creates a file with header, data and trailer --Is there any workaround
Thanks in advance Dave
Imports System
Imports System.Data
Imports System.Math
Imports System.IO
Imports System.Text
Imports System.Configuration
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Public Class ScriptMain
Inherits UserComponent
'Dim fs As FileStream
Dim fileName As String = "F:FilePickUpMyfilename.csv"
'Dim fileName = (Me.Variables.gsFilename.ToString)
Dim myFile As FileInfo = New FileInfo(fileName)
Dim sw As StreamWriter = myFile.CreateText
Dim sbRecord As StringBuilder = New StringBuilder
Public Overrides Sub PreExecute()
sbRecord.Append("RECORD_START").Append(vbNewLine)
End Sub
Public Overrides Sub ParsedInput_ProcessInputRow(ByVal Row As ParsedInputBuffer)
sbRecord.Append(Row.ProjectID.ToString)
sbRecord.Append(Row.TransactionRefNum.ToString)
sbRecord.Append(Row.BillToCustomerNum.ToString)
sbRecord.Append(Row.BillToAccountNum.ToString)
sbRecord.Append(Row.BillToLineNum.ToString)
sbRecord.Append(Row.BillToReassignmentNum.ToString)
sbRecord.Append(Row.ChargeCode.ToString)
sbRecord.Append(Row.NotificationMethod.ToString)
sbRecord.Append(Row.AdjustmentAmount.ToString)
sbRecord.Append(Row.AdjustmentDate.ToString)
sbRecord.Append(Row.ReparationGivenFlag)
sbRecord.Append(Row.BillingSystemProcessingErrorCode.ToString).Append(vbNewLine)
End Sub
Public Overrides Sub PostExecute()
sbRecord.Append("RECORD_COUNT").Append((vbTab))
sbRecord.Append(Me.Variables.giRowCount.ToString).Append(vbNewLine)
sbRecord.Append("RECORD_END").Append(vbNewLine)
'Now write to file before next record extract
sw.Write(sbRecord.ToString)
'Clear contents of String Builder
sbRecord.Remove(0, sbRecord.Length)
'Close file
sw.Close()
End Sub
End Class
Has anyone got a workaround
thanks in advance
Dave
Hi
View 7 Replies View RelatedI use SQL Server 2005 and in a Stored Procedure I want to execute a sql statement and assign the result to a variable. How can I do that?The name of the column I want to retreive the value from is "UserID"Here's my SP so far:
ALTER PROCEDURE [dbo].[spUnregisterUser]
@UserCode int
AS
BEGIN
SET NOCOUNT ON;
declare @uid uniqueidentifier
--get userid
SELECT @uid=UserID FROM tblUserData WHERE UserCode=@UserCode
-- Delete user
UPDATE tblUserData SET IsDeleted='True' WHERE UserCode=@UserCode
END
How to make it workable code..
declare @i int=1
declare @numweek int=2
declare @a int=35
declare @b int=29
declare @Wkstr1 date,@Wkstr2 date
[Code] .....
How do I assign XmlDocument results to a variable (so that I can pass it to a sp)?
I know that the following code works....
select * from tblUsers where userId = 1225 for XML raw
It returns "<row UserId="1225" LastName="Evans" FirstName="Stephanie" MiddleInitial=...."), which is what I want. But when I try to assign it, I get an error "Incorrect syntax near 'XML'."
declare @strXml nvarchar(1000)
set @strXml = (select * from tblUsers where userId = 1225 for XML raw)
Ideas?
Hi,
HOW can I assign the value of @@IDENTITY to the any variable in SQL SERVER .
Thanks,
Greetings once again,
I am trying to achieve a seemingly simple task of assigning datetime value to a user variable at the point my package starts running. How can I do this without using a SQL Script Task? Should I be using a script task for this or is there a simpler way to achieving the same thing?
Thanks in advance.
I have a column with int data type.. i am trying to assign this column to a variable ( int) in For Each Loop..but it keeps giving me an erro
The type of the value being assigned to variable "User:ubcontractor_Key" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
Am i getting this error becasue the column has NULL value?. how can I resove this probelm?
hi friends..... i want to assign value to the nvarchar variable on sql server2005 procedure ...tell me how ? thanks and regards.samuel chandradoss . J
View 4 Replies View RelatedI'm using a stored procedure to create a table in sql 2000. One of the columns is an identity column. I need to set the seed to a max(number) from a column in another table, this column is not an identity column and can't be changed into one. I've been trying to set the seed by passing a variable. I continue to get errors so either I've got the syntax wrong or it's not possible to set the seed via a variable. Any words of wisdom would be appreciated.
View 5 Replies View RelatedI have this big huge SQL statement. I am trying to see whether there is some quick magic way to assign it to the variable @SQL
Declare @SQL NVARCHAR(MAX)
Set @SQL = <????>
SELECT ClaimSurgical.FormNbr as ClaimIdentifier,ClaimSurgical.MemberTID, ClaimSurgical.Membernbr as MemberNumber, ClaimSurgical.AdmitDate AS ServiceDateYYYYMMDD,
CAST(CAST(ClaimSurgical.AdmitDate AS VARCHAR) AS DATETIME) ServiceDate,ClaimSurgical.ProviderNbr as ClaimPRVNO, ClaimSurgical.VendorNbr as ClaimTaxID,
ClaimSurgical.AssignedProvider1 as PCPPRVNO,ClaimSurgical.CAPVendor as PCPTaxID, EventCode_1.EventCodeTypeID,
[code]....
Hi this is probably a very stupid question, but I still need to know.
How do I set the result of a 'SELECT' statement to a variable? I know I can use CURSOR, but I am certain the SELECT statement will return either 1 record or NULL. Can I use something else apart from CURSOR?
Hi all of you,
That's an easy one. I've got a Send Mail task which might send a message in plain text along with a SSIS variable.
Something like that:
'La tabla "' + SUBSTRING( @[System:ackageName], 3,20) + "' se ha cargado correctamente'
TIA for that,