Hi, all experts here,
Do we always have to use SCD component for the loading of data into data warehouse to handle changes of rows?
I am looking forward to hearing from you and thank you very much in advance for your help.
With best regards,
Should we have foreign key relationship between two slowly changing dimensions?
Most of the cases we talked about are about how to build an SLC, any example of trying to have two SLCs with FK relationship in a snow flake schema?
I am new to SSIS and I am investigating using the Slowly Changing Dimension transform.
The data source that I receive is a daily snapshot of the external source system table. I need to store the history of the entity attributes (Type 2 SCD) and I am using the Start / End Date mechanism.
When an entity (identified by the business key) is no longer received in the source snapshot, I would like the data flow to update the End Date of the current row to show that the entity has now expired.
Does anyone have any suggestions for a good way to achieve this ?
NB: Changing the source system extract to include and flag expired entities is not an option for me.
Many thanks
Graham
Hi, All experts here,
Thank you for your kind attention.
I have questions about Slowly Changing Dimensions. I am quite confused about when should we use type 1 ( changing), type2 (historical), or type3( fixed) for the dimensions in each table? Is there any good suggestions on that?
Thank you in advance and I am looking forward to hearing from you.
Yours sincerely,
Hi,
I would like to know if there is an easy way to load Hierarchical Dimensions with Type 2 using SSIS ( or in general).
Here is my example:
There is a Hierarchy Product Group <- Product Class <- Product. (In words, Product rolls up to Product Class and Product Class Rolls up to Product Group). Say Product Group is Type 1 and the other two are Type 2. Every night, I receive a feed for each of these tables only if the record is new or a change to an existing record.
Now, when a Product Class Record is changed to assign it to a different group then I receive the feed only for Product Class (but not for Product). To load this record, I expire the old record and create a new entry with a new Surrogate Key. Then how do I automatically cascade this change to Product and make a Type 2 change to use the new Surrogate Key from Product Class?
Any ideas would be greatly appreciated.
Thanks,
Srini
Hello,
We are having issues with SCDs that appear to be volume related. Small dimensions work fine. Pasted below is the last few lines of our package run without any contention (We get different errors at different time with contention referring to buffer or virtual memory). It seems to be calling out the SCD main task but just gives a number and not much of a description.
2) For the errors referring to a buffer ( I can paste them also but did not want to put too much at once); we have the "BufferTempStoragePath" pointing to a drive with lots of space. We have tinkered with the DefaultBufferMax Rows and Size but that seems to just control how big the "batches" are (the row count size changes you see).
Any configuration type things we could try to avoid these errors? Any more detail that can be gleaned from the messages below or the number? This is kind of a show stopper as we are late in the cycle and need larger (at this point only about 600,000 rows) volumes to go through the SCD tasks. The performance seems to be good enough (about 20 minutes). We have tens of millions working fine in fact packages not using SCDs.
Thanks. Geoff
Information: 0x4004300C at Data Flow Task, DTS.Pipeline: Execute phase is beginning.
Error: 0xC0047022 at Data Flow Task, DTS.Pipeline: The ProcessInput method on component "Slowly Changing Dimension" (45421) failed with error code 0x8007000E. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0x8007000E.
Error: 0xC0047039 at Data Flow Task, DTS.Pipeline: Thread "WorkThread1" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread "WorkThread1" has exited with error code 0xC0047039.
Error: 0xC02020C4 at Data Flow Task, Control File Extended view as source [959]: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.
Error: 0xC0047038 at Data Flow Task, DTS.Pipeline: The PrimeOutput method on component "Control File Extended view as source" (959) returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread "SourceThread0" has exited with error code 0xC0047038.
Information: 0x40043008 at Data Flow Task, DTS.Pipeline: Post Execute phase is beginning.
Information: 0x40043009 at Data Flow Task, DTS.Pipeline: Cleanup phase is beginning.
Information: 0x4004300B at Data Flow Task, DTS.Pipeline: "component "Insert New Rows into Product Dim" (51111)" wrote 0 rows.
Task failed: Data Flow Task
Warning: 0x80019002 at ProductDimensionIncr: The Execution method succeeded, but the number of errors raised (7) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "ProductDimensionIncr.dtsx" finished: Failure.
Hi,
I use lookups to map surrogate of level 1 dimensions to my fact tables in SSIS.
But how to handle a level 2 dimension with a ValidFrom and a ValidUntil date field?
I do not use an IsCurrent column, because this could problem with late arriving facts.
- In dts I used an SQL statement like this:
update SA
SET SA.DimProdRef = Dim.RecordID
FROM SAWarenEingang SA, DimProd Dim
where SA.ProduktNumber = Dim.ProduktNumber
and SA.ArtikelkontoBewegungsdatum between Dim.ValidFrom and Dim.ValidUntil
Now in SSIS I want to handle the whole thing in the data flow without using a staging table:
- Using Lookups: I would have to pass the date column for each inside the fact table into the lookup. That does not work.
- Using Execute SQL in the data flow: would be very slow, because the statement will be executed for any line in the dataflow
Any ideas?
Best regards,
Stefoon
Hello,
Is it possible to use existing data flow components (Merge Join, aggregation,...) in a custom data flow component?
Thanks,
Yoann
How do I tell the destination of the dataflow to use the stage table that has not been created yet?
View 9 Replies View RelatedHello Everybody,
Can anybody please let me know the procedure for loading data into Dimensions & Facts? And what is the sequence of loading?
Thanks in Advance
Rajesh
Hi,
I have SSIS which will have OLF DB Source, and then have Derived Column component to managering all data from OLF DB Source. I used to have default columns such as Create Date, Update Date set as fixed date. Now we decided to put this default column values into a table to manage. I then have problem to choose which component I should use in order to have this columns selected from default table.
For example: if Create Date is null, I have to select default value from the default table; otherwise, use Create Date value and so on.
Thanks,
Megan
Is there a way to change the data source for a SCD Component without having to go back to reinsert the matches for Source and Destination columns. Note the underlying data table hasn't changed, just the server the table resides on. Whenever I change the data source I am noticing that I have to painfully go back and match columns one by one.
Thanks
David
I have a fairly simple data flow task that loads data from one table (OLE DB Source) into another table (OLE DB Destination). The data type for one of the pairs of columns is nVarChar(120) and it contains version information that looks like a decimal. When I run the export, the destination has a trailing zero added after the decimal point as if it were a numeric column which invalidates our comparisons (string 1.0 is not the same as string 1.00). There is no cast or convert done to this column, it is a straight copy. Any ideas what could be causing this or how to fix?
View 6 Replies View RelatedI have a int value that I pulled from a table in a database and it is stored in a variable. I would like to increment this value in a script component and then insert it into a field in my ole db destination. Is there any example out there of using a script component to do something similar to this.
Do I want to select source, destination or transformation?
Can I/how do I access my variable from within the script component.
Any asistance or examples you can point me towards would be greatly appreciated.
There is a table with a column that contains Xml documents. For each record from my Data Flow Source, I want to pass in the Xml document and the node to interrogate, and return the value contained in the node. Like the Crm component, this is probably one I will have to write from scratch in C#, but I would like to avoid having to create the custom component if it already exists in the public arena.
Does anyone know of any Xml Ssis Data Flow Components that are downloadable for free?
Dear Colleagues,
I'm trying to develop a custom Data Flow Transformization component in SSIS.
I compiled it without errors, installed it in the GAC and in the Pipeline Components-folder however I always get the following message when I'm trying to drag the component onto the designer surface:
The component could not be added to the Data Flow task.
Please verify that this component is properly installed.
------------------------------
ADDITIONAL INFORMATION:
The data flow object "RisikoKennzahlenKomponenten.MarktwertTransformation,
RisikoKennzahlenKomponenten, Version=1.0.0.0, Culture=neutral,
PublicKeyToken=cfa8722b8086ac2d" is not installed correctly on this computer.
(Microsoft.DataTransformationServices.Design)
Program Location:
at
Microsoft.DataTransformationServices.Design.DtsBasePackageDesigner.GetPipelineInfo(String creationName, IServiceProvider serviceProvider)
at
Microsoft.DataTransformationServices.Design.DesignUtils.GetNewPipelineComponentObjectName(IDTSComponentMetaDataCollection90
parentCollection, String clsid, IDTSComponentMetaData90 componentMetadata,
PipelineComponentInfo& pipelineComponentInfo)
at
Microsoft.DataTransformationServices.Design.PipelineTaskDesigner.AddNewComponent(String clsid, Boolean throwOnError)
This happens with EVERY custom component on my computer. The same components work fine on other machines.
Does anyone have an idea?
Regards
Arne Janning
Is there a default destination component used when a new data flow is created? The reason I ask is simply curiosity. I have an xml file with 2 pieces of data: item A and item B. A should simply get copied out of the file. B should undergo a quick transform. I set up an XML source such that two columns are mapped correctly to the XML source data of A and B. I set up my data transform task as well. So, if I leave those two components on the .dtsx page with no other components, then will there be a default data flow destination already created? ...OR, do you always have to have a destination component?
Thanks for the input. I am just curious.
I have a package variable that I set via an ExecuteSQL task. I want to reference it in a data flow script component. In the Script component I enter the variable into the ReadOnlyVariables collection, then in the script I reference it as Me.Variables.var. (E.G. counter = Me.Variables.var)
I'm getting errors when the data flow starts:
Error: 0xC0047062 at Provider, Set Surrogate Key [4261]: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> Microsoft.SqlServer.Dts.Pipeline.ReadWriteVariablesNotAvailableException: The collection of variables locked for read and write access is not available outside of PostExecute.
I have no problem referencing other variables that I have in DerivedColumn transformations. I've tried putting the variable in the ReadWriteVariables collection but I get the same error. I don't understand why this is so difficult. Please help.
Hi,
I've read the various posts and articles regarding this matter, but I seem to have problems getting to work:
In my control flow, I start by declaring a variable named "LastJobLedgerEntryID", to identify the records I need to add to the stage. From there I would like to use this variable in the source component in my dataflow, i.e.:
"SELECT [Entry No_],[Job No_],[Posting Date],[Document No_],[Type],[No_],[Description],[Quantity],[Direct Unit Cost],[Unit Cost],[Unit Price],[Chargeable],[Job Posting Group],[Global Dimension 1 Code],[Global Dimension 2 Code],[Work Type Code] FROM mytable WHERE [Entry No_] > " + @[User::LastJobLedgerEntryID]
But this fails? I should note that the variable LastJobLedgerEntryID is stored as a int32, and with the default value of 0
Could someone please help me with this?
Thanks in advance!
I'm wondering if it is possible to create a flat file source on the fly while bypassing the following step:
On the Connection Managers page, add or create the Flat File connection manager, using a descriptive name such as MyFlatFileSrcConnectionManager. Then close the Script Transformation Editor.
I want to create the connection totally in script, yet i'm having a hard time proving this out...does anybody have any
experience with this?
Ryan
Hello-
I have an SSIS package which I've been using for nearly a year now. Basically the package is responsible for looping through a directory, and importing pipe delimited files into a database.
The issue I'm having is with the UnDoubleError data flow component. I've been using it to remove the qualifiers from the data being imported, in this case it happens to be double-quotes. ex: { " " } I have found that the component will insert a single double-quote { " } when it finds a null string, or two consecutive qualifiers for that matter. If there are two qualifiers with a space seperating them, then it will insert an empty string, or rather a string with one space in it, and the qualifiers will be removed.
The dilemma is since the log files have some columns which are technically NULL, by that I mean columns where there exists only two qualifiers ex: { "" } the UnDoubleError component then inserts a single double-quote. I want to retain the NULL value if it exists, though still use the component to remove the qualifiers when there actually is data there.
Any suggestions?
Thanks for the help...
I am getting the following exception when attempting to read from a DataReaderDestination:
System.Exception was unhandled
Message="Could not obtain a DataReader object from the specified data flow component."
Source="Microsoft.SqlServer.Dts.DtsClient"
StackTrace:
at Microsoft.SqlServer.Dts.DtsClient.DtsCommand.internalPrepare(Boolean fReaderRequired)
at Microsoft.SqlServer.Dts.DtsClient.DtsCommand.ExecuteReaderInThread()
at Microsoft.SqlServer.Dts.DtsClient.DtsCommand.ExecuteReader(CommandBehavior behavior)
at CA3DataImportTool.ViewSSISOutput.btnRun_Click(Object sender, EventArgs e) in C:Documents and Settings
heinMy DocumentsVisual Studio 2005ProjectsCA3DataImportToolCA3DataImportToolViewSSISOutput.cs:line 35
at System.Windows.Forms.Control.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ButtonBase.WndProc(Message& m)
at System.Windows.Forms.Button.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.Run(Form mainForm)
at CA3DataImportTool.Program.Main() in C:Documents and Settings
heinMy DocumentsVisual Studio 2005ProjectsCA3DataImportToolCA3DataImportToolProgram.cs:line 18
at System.AppDomain.nExecuteAssembly(Assembly assembly, String[] args)
at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()
If I use the example from SQL Server BOL (http://msdn2.microsoft.com/en-us/library/ms135917.aspx), and make a new package for the sample in the same project, the sample works. The only thing that I can see that is significantly different between my code and the sample is that my DataReaderDestination has a lot more data in it, but here's the relevant code:
string dtexecArgs;
string dataReaderName;
DtsConnection dtsConnection;
DtsCommand dtsCommand; //IDbCommand
IDataReader dtsDataReader;
DataTable dtsTable;
dtexecArgs = @"/FILE ""C:Documents and Settings
heinMy DocumentsVisual Studio 2005ProjectsCA3DataImportToolML3000_IntegrationProjectPackage.dtsx"" ";
dataReaderName = "DataReaderDest";
dtsConnection = new DtsConnection();
dtsConnection.ConnectionString = dtexecArgs;
dtsConnection.Open();
dtsCommand = new DtsCommand(dtsConnection);
dtsCommand.CommandText = dataReaderName;
dtsDataReader = dtsCommand.ExecuteReader(CommandBehavior.Default); // EXCEPTION HERE
Please help!
Richard Hein
Hi
After having read the forums I understand that it is not possible to use parameters in the "Ole Db Command", when connecting to Oracle.
What I have to do, is to delete a record in an Oracle table from within the "Data Flow", based on parameters.
It does not seem to be possible to create the SQL statement in a variable (with parameters generated) and use the variable in the "Ole Db Command"
Instead I probably need to use the "Script Component"?
So what I need is step by step guidance for how to connect to and interact with an Oracle database from a Script Component, what to import and where, what to declare and where, and generally a way to get this to work.
Any help appreciated.
Olafur
Does anyone know how to hook up to data flow pipeline events via custom solution (C#)? I am trying to write code to log start and end times of components(lookup,merge joins etc) in a data flow task. I tried with a class inheriting from the EventsProvider class but it didn't work as this is only for container tasks. Any ideas will be greatly appreciated.
Hi,
I am creating a SSIS Programmatically. I facing problem in setting name to data flow compenent.
here is the sample code
IDTSComponentMetaData90 oledbItemMasterSource;
oledbItemMasterSource = dataFlow.ComponentMetaDataCollection.New();
oledbItemMasterSource.ComponentClassID = "DTSAdapter.OLEDBSource";
oledbItemMasterSource.Name = "InventTable";
oledbItemMasterSource.Description = "Get Item Master";
After creating this package i found that 'InventTable" is not set to OLE Db Source component. where as i can set name to OLE DB Flat File destination component.
Kindly suggest me a way fix this issue.
Thanks-
aravind
Dear all,
I am developing tools for automatic creation of data warehouse tables, cubes and SSIS packages. Generating the SSIS Data Flows works very well using the SSIS components for OLE DB Source, Derived Column, Lookup and OLE DB Destination.
However for some of the advanced functionality I need to use Script Component. I have managed to add it in the Data Flow with all inputs and outputs, but how do I populate it with my code? I've seen there is a component property called "SourceCode" and one called "BinaryCode". The "SourceCode" contains the code, but also some extra metadata.
Questions:
Do you know if there is any programmatic support to generate the Source Code property with the metadata necessary?
Do you know how to compile the Source Code and generate the property BinaryCode?
Example from my code below:
// Create script component
IDTSComponentMetaData90 script = dataFlowTask.ComponentMetaDataCollection.New();
script.ComponentClassID = app.PipelineComponentInfos["Script Component"].CreationName;
CManagedComponentWrapper scriptWrapper = script.Instantiate();
script.InputCollection.New();
script.OutputCollection.New();
scriptWrapper.ProvideComponentProperties();
script.Name = "Logics";
// Create path
IDTSPath90 scriptPath = dataFlowTask.PathCollection.New();
scriptPath.AttachPathAndPropagateNotifications(lastComponent.OutputCollection[0], script.InputCollection[0]);
// Populate input and output columns
IDTSInput90 scriptInput = script.InputCollection[0];
IDTSVirtualInput90 scriptVInput = scriptInput.GetVirtualInput();
foreach (IDTSOutputColumn90 col in oledbSrc.OutputCollection[0].OutputColumnCollection)
{
scriptWrapper.SetUsageType(scriptInput.ID, scriptVInput, col.LineageID, DTSUsageType.UT_READONLY);
IDTSOutputColumn90 tmp = script.OutputCollection[0].OutputColumnCollection.New();
tmp.Name = col.Name;
tmp.SetDataTypeProperties(col.DataType, col.Length, col.Precision, col.Scale, col.CodePage);
}
// Make script asynchronous
script.OutputCollection[0].SynchronousInputID = 0;
Thanks for any assistance and Best Regards,
Johan Åhlén,
Business Intelligence consultant at IFS
I am trying to cleanly shutdown a dataflow task, which contains a script component, when RunningPackage.Stop() is called from the SSIS runtime.
I've been going in ever decreasing circles with no success - it looks like the cleanest way to find out whether RunningPackage.Stop() has been called is to call FireQueryCancel(). But I can't find any reference to anything useful in a dataflow task script component that gives me something that implements IDTSComponentEvents. The nearest thing seems to be Me.ComponentMetaData which gives a reference to IDTSComponentMetaData90, but this only has methods for calling FireError, FireInformation, FireProgress, FireWarning, and FireCustomEvent. But no FireQueryCancel.
Is there a way in a script component that I can find out the state of QueryCancel?
Any help would be apprecieated.
At our business we are getting a lot of PDF documents that are being hand keyed into a database. Has anyone heard ior know of a SSIS Data Flow Source component that I coud use to read thos documents into a data stream (?) and process?
View 5 Replies View RelatedI have found not very good behavior of Union All Data Flow Component.
I have Script component and about 30 Union All Data Flow Components. I have string field named 'MyField'. Legth of the field is 15 characters. The field appears first in Script component (defined as 'MyField' Output collumn) and present in all Union All Data Flow Components. I need to change field length from 15 to 10. In Script Transformator Editor of Script component I changed 'MyField' Output collumn Length property value set it to 10. But length of the field remains 20 in all Union All Data Flow Component downstream and no errors no warnings appears. To fix this i recreated (deleted and added again) 'MyField' in all Union All 30 Data Flow Components.
If extend length from, say, 15 to 20 in the first of Union All Data Flow Components error sign appears
"Error 1 Validation error. Data Flow Task: Union All [22]: The metadata for "input column "MyField" (144)" does not match the metadata for the associated output column."
and only recreation of the field in all Union All Data Flow Components can help to fix it.
May be someone knows other solution to fix such behavior?
Hi,
I'm pretty new in SSIS and i have some problems with error log. I want to get detailled error description in a script component of a dataflow. for the moment I use thooses lines
Row.ErrorDesc = ComponentMetaData.GetErrorDescription(Row.ErrorCode)
and for unique constraints on a sql table I have this error : The data value violates integrity constraints.
For the same error, if i use an event handler on error, i have more row and the first of them is more explicit (Variable System::ErrorDescription)
An OLE DB error has occurred. Error code: 0x80040E2F.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E2F Description: "The statement has been terminated.".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E2F Description: "Cannot insert duplicate key row in object 'dbo.dimDepot' with unique index 'IX_dimDepot'.".
Is that possible to have a so detailled error text in a script componnent of a data flow? If yes, How?
Or if i use error event how can authorize the dataflow go ahead even if there is error.
thanks for you help
krest
Hi,
I'm having my first go at developing a destination adapter which will send data to an update Web Service.
I've got some rather big gaps in my understanding. I've been following the various samples I've found on the net and have validated my mapping and picked up all the available column names and datatypes which are appearing in the Input and Output Properties tab of the Advanced Editor but I only have a tab for "Input Columns" and not "Column Mappings".
Which method defines the availble columns for the user to map?
Let me know if I haven't given enough information.
cheers
Hello Helpers,
I need to know how to use my private function - created as a scalar-valued-function in SQL Server 2005 - in script component (here a transformation is used) in a data flow task to transform a two-digit-month into a tree-sign-month:
Example: '01' should be transformed into 'Jan'
Many thanks for alle your commitment and help!
Ulrike
Hi there.
"This is a simple problem, so do not be discouraged by the amount of text ahead. I wrote so much because I thought this needed to be explained as well as possible, even though it's simple."
I have two (almost) same sets of tables on two servers, call them Source and Destination.
What needs to be done is that the data in the tables on Source has to be transferred to the same (corresponding) tables on Destination. The unfortunate thing is that in some of the tables on Source some columns are Unicode and in the corresponding tables on Destination the same columns are non-Unicode. Since the data kept in the tables is always ASCII there is no need to have a distinction.
Remark: No, I can't delete the Destination tables and recreate them with the Unicode settings.
Now, I know there is a task that can convert between encodings. But 1. there are MANY tables, 2. there are MANY columns in them and 3. I do not know which columns are Unicode and which are not. I would have to examine all the tables which would take A LOT OF time.
A question arises: Is it possible to write a script component that would take a row, examine the columns for SSIS data types (not VB.NET types) and change the piece of data's type in the colum from Unicode to non-Unicode? I am not sure I've stated this properly but hopefully you get the idea.
If you think this is impossible, please give me an alternative solution (if able), excluding - of course - the conversion task.
Thank you very much.
Darek