Logging In The Data Flow Pipeline
Hi,
Does anybody have any experience implementing logging within a data flow task? ie. logging timestamps for SCD component start and finish times.
Thx
tony
View Complete Forum Thread with Replies
Related Forum Messages:
Logging Information About Items Of The Data Flow Task?
Hi,In SSIS logging, do you know if we can log details down to the itemswithin the Data Flow Task?I mean for example having in the sysdtslog90 table, details regardingthe start time and the end start of tasks like 'Derived Columns','Lookup', 'Audit', 'Aggregate', ... ?Thanks very much,Chris
View Replies !
SSIS Data Flow Execution Monitoring And Logging
Has anyone come up/determined a generic way to capture and log indicative information within a data flow in SSIS - e.g., a number of rows selected from the source, transformed, rejected, loaded, various timestamps around these events, etc.? I am trying to avoid having to build a custom solution for each of the packages that I will have (of which there will be dozens). Ideally, I'd like to have some sort of a generic component (such as a custom transformation) that will hide the implementation details and provide a generic interface to the package. It is not too difficult to achieve something similar on the control flow level, but once you get into data flows things get complicated. Any ideas will be greatly appreciated.
View Replies !
Logging Data Flow Steps In SSIS Package
Hello. I am using the "SSIS Log Provider for SQL Server" to log events to a table for "OnError" and "OnPostExecute" events of a package. This works as expected and provides a nice clean output on the execution steps of the package. I am curious as to why I do not see any detail for any/all tasks that fall under the "Data Flow" section of the package though. For instance, on my "Control Flow" tab, I added a "Data Flow" task that simply loads a few tables from a target to destination server. However, there is nothing shown in the logging output. Just that a Data Flow task was initiated. And when I'm configuring this logging under "SSIS-->Logging" in the checkbox area on the left, you cannot "drill into" data flow steps. Is there a reason why there is no detailed logging for Data Flow tasks? Would getting to that require me to create a custom log provider? Thanks for the help. Greg
View Replies !
Logging Data Flow Component Events In SSIS
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.
View Replies !
Error: The Task With The Name &&"Data Flow Task&&" And The Creation Name &&"DTS.Pipeline.1&&" Is Not Registered For Use On This Computer
Hi, I am trying to create a simple BI Application for SSIS. In Visual Studio 2005 I just get a Data Flow Task from the toolbar and add it to the project. When I double click it I get the following error: The task with the name "Data Flow Task" and the creation name "DTS.Pipeline.1" is not registered for use on this computer. Then when I try to delete it it gives this other error: Cannot remove the specified item because it was not found in the specified Collection. I am creating this application in an administrator account in this computer, so I doubt the problem is related to permissions. I am running SQL Server 2005 and Visual Studio 2005 in WinXP Tablet PC Edition. Any suggestions why this is happening and how to fix it?
View Replies !
Logging The Control Flow Hierarchy
Hello, I am using the event handling mechanism to do my custom logging. This works fine. Using the OnPreExecute and OnPostExecute my log tables fill up with the start- and enddates of all the containers and tasks in the complete package hierarchy. However, what I am missing (e.g. in a system variable) is the source ID of the parent container that started the task or container. In other words, in my logging reports, I would like to build up a tree starting with the topmost package, like the progress indication in the IDE. The built-in Logging features do not log this information either as far as I can tell. Does anyone know how to do this? Kind regards, Jeroen
View Replies !
Pipeline Error-excel Source-data Reader Does Not Read In Meta Data
Hi all, i got this error: [DTS.Pipeline] Error: "component "Excel Source" (1)" failed validation and returned validation status "VS_NEEDSNEWMETADATA". and also this: [Excel Source [1]] Warning: The external metadata column collection is out of synchronization with the data source columns. The column "Fiscal Week" needs to be updated in the external metadata column collection. The column "Fiscal Year" needs to be updated in the external metadata column collection. The column "1st level" needs to be added to the external metadata column collection. The column "2nd level" needs to be added to the external metadata column collection. The column "3rd level" needs to be added to the external metadata column collection. The "external metadata column "1st Level" (16745)" needs to be removed from the external metadata column collection. The "external metadata column "3rd Level" (16609)" needs to be removed from the external metadata column collection. The "external metadata column "2nd Level" (16272)" needs to be removed from the external metadata column collection. I tried going data flow->excel connection->advanced editor for excel source-> input and output properties and tried to refresh the columns affected. It seems that somehow the 3 columns are not read in from the source file? ans alslo fiscal year, fiscal week is not set up up properly in my data destination? anyone faced such errors before? Thanks
View Replies !
&&<Data&&>Microsoft.SqlServer.Dts.Pipeline.BlobColumn&&</Data&&>
I have a custom (dataset) destination component from ms samples and it has an input holds DT_NTEXT value. Whenever I try to retrieve data from this it returns "Microsoft.SqlServer.Dts.Pipeline.BlobColumn" as value. I try this but didn't work: String sValue = System.Text.Encoding.Default.GetString(Convert.FromBase64String(this.dataSet.Tables[0].Rows["Data"].ToString())); It throws an execption "invalid character in.." Please help how I can convert this? Thanks in advance
View Replies !
How To Pass Parameter Froon Control Flow To Data Flow
Hi, All, I need to pass a parameter from control flow to data flow. The data flow will use this parameter to get data from a Oracle source. I have an Execute SQL task in control flow to assign value to the Parameter, next step is a data flow which will need take a parameter in the SQL statement to query the Oracle source, The SQL Looks like this: select * from ccst_acctsys_account where to_char(LAST_MODIFIED_DATE, 'YYYYMMDD') >? THe problem is the OLE DB source Edit doesn€™t have anything for mapping parameter. Thanks in Advance
View Replies !
Transformation Object To Filter Data In Pipeline
Hi, I have some data coming through pipeline and I wanna add some component at some point to pass on only selected rows based on conditions to the objects onwards. My opinion is I should use conditional split object, but Please suggest me something if you know better. Thanks, Fahad
View Replies !
SSIS Variables Between Data Flow And Control Flow... How To????
Hi everyone, Primary platform is 64 bit cluster. How to move information allocated in SSIS variables from Data Flow to Control Flow layers?? We've got a SSIS package which load a value into a variable inside a Data Flow. Going back to Control Flow how could we retrive that value again???? Thanks in advance and regards,
View Replies !
HELP: How Do I Pass Variables From Control Flow To Data Flow
I have an Execute SQL Task that returns a Full Rowset from a SQL Server table and assigns it to a variable objRecs. I connect that to a foreach container with an ADO enumerator using objRecs variable and Rows in first table mode. I defined variables and mapped them to the columns. I tested this by placing a Script task inside the foreach container and displaying the variables in a messagebox. Now, for each row, I want to write a record to an MS Access table and then update a column back in the original SQL Server table where I retreived data in the Execute SQL task (i have the primary key). If I drop a Data Flow Task inside my foreach container, how do I pass the variables as input to an OLE DB Destination on the Data Flow? Also, how would I update the original source table where source.id = objRects.id? Thank you for your assistance. I have spent the day trying to figure this out (and thought it would be simple), but I am just not getting SSIS. Sorry if this has been covered. Thanks, Steve
View Replies !
Is There A Way To Set A Variable In A Data Flow From A SQL Statement (like In Control Flow)
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.
View Replies !
Please Advise: Big Control Flow Or Big Data Flow
Hi all! I recently started working with SSIS and one of the things that is puzzling me the most is what's the best way to go: A small control flow, with large data flow tasks A control flow with more, but smaller, data flow tasksAny help will be greatly appreciated. Thanks, Ricardo
View Replies !
How Do I Write Multiple Pipeline Buffer To Multiple Targets Based On A Calculated Value In The Pipeline Buffer
The scenario is as follows: I have a source with many rows. Each row has a column called max_qty_value. I need to perform a calculation using another column called qty. This calculation is something similar to dividing qty/(ceiling) max_qty_value. Once I have that number I need to write an additional duplicate row for each value from the prior calculation performed. For example, 15/4 = 4. I need to write 4 rows to the same target table as in line information for a purchase order. The multicast transform appears to only support fixed and/or predetermined outputs. How do I design this logic in SSIS to write out dynamic number of rows to a target table. Any ideas would be greatly appreciated. thanks John
View Replies !
Lookup Task Data Flow Transformation Causes Data Flow Task To Hang?
Hi, I'm trying to implement an incremental data pull (Oracle to SQL) based on Andy's blog: http://sqlblog.com/blogs/andy_leonard/archive/2007/07/09/ssis-design-pattern-incremental-loads.aspx My development machine is decent: 1.86 GHz, Intel core 2 CPU, 3 GB of RAM. However it seems the data flow task gets hung whenever I test the package against the ~6 million row source, as can be seen from these screenshots. I have no memory limitations on the lookup transformation. After the rows have been cached nothing happens. Memory for the dtsdebug process hovers around 1.8 GB and it uses 1-6 percent of CPU resources continuously. I am not using fast load to insert new records into my sql target table. (I am right clicking Sequence Container 3 and executing this container NOT the entire package in the screenshots) http://i248.photobucket.com/albums/gg168/boston_sql92/1.jpg http://i248.photobucket.com/albums/gg168/boston_sql92/2.jpg http://i248.photobucket.com/albums/gg168/boston_sql92/3.jpg http://i248.photobucket.com/albums/gg168/boston_sql92/4.jpg http://i248.photobucket.com/albums/gg168/boston_sql92/5.jpg http://i248.photobucket.com/albums/gg168/boston_sql92/6.jpg The same package works fine against a similar test table with 150k rows. http://i248.photobucket.com/albums/gg168/boston_sql92/7.jpg http://i248.photobucket.com/albums/gg168/boston_sql92/8.jpg The weird thing is it only takes 24 minutes for a full refresh of the entire source table from Oracle to the SQL target table. Any hints,advice would be appreciated.
View Replies !
Need To Save A Value From Data Flow A To Use In Data Flow B
Good morning, all, I am working on importing an Excel workbook, saved as multiple CSV flat files, that has both group level data and related detail row on the same sheet. I have been able to import the group data into a table. As part of the Data Flow task, I want to be able to save the key value for the group, which I will use when I insert the detail rows. My Data Flow has the following components: The flat file with the data, which goes to a derived column transformation to strip out extraneous dashes, which leads to the OLEDB Destination component. I want to save the value as a package level variable, so that I can reference it in another dataflow. Is this possible, and if so, at what point do I save the value? Thanks, Kathryn
View Replies !
OLE DB Source Task In The Second Control Flow Data Task Fails After The Same Task Succeeds In The First Control Flow Data Task
I am trying to run a SSIS package as an agent job. The package has several control flow data tasks. Each of the control flow data tasks uses a OLEDB source task to connect to the same SQL 2005 database(defined by a single connection manager) using SQL server authentication and basically pulls some data. The first control flow data task succeeds, but the second one fails and so he package errors out. My question is why would the second OLEDB task be failing when it uses the same connection manager as the frist one? Here are some facts 1) the package runs fine in BIDS from my development wkstation. 2) the package uses an XML configuration file which specifies the sql server password and connectionstring 3) the package runs on a 64 bit sql server box with SP1. 4) the job is running under sql agent service account. 5) I created the package as well as the agent job. 6) the ole db source is a sql 2005 server. 7) the error message generated by the second task(oledb source) is "The AcquireConnection method call to the connection manager "Source" failed with error code 0xC0202009." I have read about some issues running SQL authenticated ssis packages running under job agent but i wonder if this is related. thankyou, js40
View Replies !
Data Flow Task Error To Extract Data From Sql Server To Excel
Hi All, I want to export data from SQL Server2005 to an Excel spreadsheet thru "Data Flow Task". I am using OLE DB for SQL Server for the source connection and a Connection To Excel as my destination source. The Excel spreadsheet (2003) exists and has the first row with column names. I don't have any warnings before trying to execute. The SQL datable fileds are i) ID - Int ii) RefID iii) txtRemarks - nvarchar(MAX) iv) ddlWaterLevel - nvarchar(50) While executing the tasks, I got the error Error: 0xC0202025 at Data Flow Task, Excel Destination [427]: Cannot create an OLE DB accessor. Verify that the column metadata is valid. Error: 0xC004701A at Data Flow Task, DTS.Pipeline: component "Excel Destination" (427) failed the pre-execute phase and returned error code 0xC0202025. After analysing I found in the DataFlow --> Excel destination --> Advanced Editor for Excel Destination, the default data type for txtRemarks shows as "Unicode string [DT_WSTR]". But this is supposed to be "Unicode text stream [DT_NTEXT]". Even if I change the data type in the design time, It doesn't accept. Please do help me out. thanks Sanra
View Replies !
Adding A New Data Column (not Derived) Midway Thru A Data Flow
I need to know what a table's max row Identity is part way thru a data flow. I can't get it at the beginning of the data flow. I need to either (1) add it to the data buffer part way thru or (2) set it into a package variable and then reference the var in a script component. I've not found a way to add a database column to the data buffer without doing a lookup for each row (too slow and not appropriate here) or some goofy oledb source and then merge join into the data buffer on a contrived join. I've read questions about referencing package vars in scripts but I can't get that to work. DTS.Variables("varname").Value isn't recognised when I code it up. Anyone have an idea or solution for either one of these? If you're gonna explain the script code, please include the entire snipet including the INCLUDEs, etc.
View Replies !
Error Writing Data To Same Destination In Single Data Flow
I am getting the following error running a data flow that splits the input data into multiple streams and writes the results of each stream to the same destination table: "This operation conflicts with another pending operation on this transaction. The operation failed." The flow starts with a single source table with one row per student and multiple scores for that student. It does a few lookups and then splits the stream (using Multicast) in several layers, ultimately generating 25 destinations (one for each score to be recorded), all going to the same table (like a fact table). This all is running under a transaction at the package level, which is distributed to a separate machine. Apparently, I cannot have all of these streams inserting data into the same table at one time. I don't understand why not. In an OLTP system, many transactions are inserting records into the same table at once. Why can't I do that within the same transaction? I suppose I can use a UnionAll to join them back together before writing to a single destination, but that seems like an unnecessary waste and clutters the flow. Can anyone offer a different solution or a reason why this fails in the first place? Thanks in advance.
View Replies !
Logging Package Name For Any Event In Sysdtslog90 Logging Table
Hi, I decided to use the SQL Server log provider to store logging data of all my Integration Services packages. I also created some reports about this data for operating purposes. I have a problem occurs the name of the executing package is not always written to the log,but the name of the single task which failed. But that is not very useful information for operating, because I do not see any chance to get the name of the package by the information which is logged in the sysdtslog90 table in the database which I defined for SSIS Logging. How do I configure the package to always log the package information into the table, too? Best regards, Stefoon
View Replies !
Handle Tasks In &&"Control Flow&&" Tab From &&"Data Flow&&" Tab
Dear All! My package has a Data Flow Task. In Data Flow Task, I use a Script Component and a OLE BD Destination to transform data from txt file to database. Within Data Flow Task, I want to call File System Task to move file to a folder or any Task of "Control Flow" Tab. So, Does SSIS support this task? Please show me if any Thanks
View Replies !
Data Flow: Converting Data In Multiple Columns
Hi, I'm just wondering what's the best approach in Data Flow to convert the following input file format: Date, Code1, Value1, Code2, Value2 1-Jan-2006, abc1, 20.00, xyz3, 35.00 2-Jan-2006, abc1, 30.00, xyz5, 6.30 into the following output format (to be loaded into a SQL DB): Date, Code, Value 1-Jan-2006, abc1, 20.00 1-Jan-2006, xyz3, 35.00 2-Jan-2006, abc1, 30.00 2-Jan-2006, xyz5, 6.30 I'm quite new to SSIS, so, I would appreciate detailed steps if possible. Thanks.
View Replies !
Data Reader Source In Data Flow Problem
hi all, i have a package in ssis that needs to deliver data from outside servers with odbc connection. i have desined the package with dataflow object that includes inside a datareader source. the data reader source connect via ado.net odbc connection to the ouside servers and makes a query like: select * from x where y=? and then i pass the data to my sql server. my question is like the following: how do i config the datasource reader or the dataflow so it will recognize an input value to my above query? i.e for example: select * from x where y=5 (5 is a global variable that i have inside the package). i did not see anywhere where can i do it. please help, tomer
View Replies !
Exporting Data From A Merge Join From One Data Flow To Another
Hi, Does anyone know if it is possible to point data that underwent the "merge join" transformation (in one data flow) to the following data flow? I don't want to recreate all that merging, sorting and calling the same sources again in the following data flow if the data that I am using exists in the previous data flow. The merged data is simply too big to export to an excel file, so does anyone have any ideas? Thanks!
View Replies !
Set The Data Source Of Data Flow From External Application (C#)
I am new to SSIS programming, so bear with me if my question seems naive to you gurus. I have a situation that needs to set the data source for a data flow from external .NET application ('external' means that the application will run on different process than the SSIS). I am trying to set the data source on which the data flow works from my C# application in a DataSet format. Ideal solution is not to save the DataSet to any file on harddisk (I know that will work, but has the overhead of writing, reading and managing the temp file). What I want to achive is that the business logic of picking data for SSIS Data Flow to process is controlled inside my C# application, the Data Flow just does what it does best - Transformation. Have any of you successfully done this before?. Thanks!
View Replies !
Data Unexpectedly Changing In Data Flow Task
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 Replies !
One Data Flow Task And Multiple Data Flows
I have a data flow task which has around 5 data flows (like the 2nd diagram shown here). These 5 simple flows with just a row count transformation in between. Now, I want to fail the entire task immediately even if one of the data flows failed. Right now if one flow fails the remaining flows fails after a long time, not immediately. How can I make it fails immediately. The other I would like to do is Can I place these 5 data flows in a transaction, so that if one data flow fails, others data flows also roll backs? ( I assume its not possible) Thanks
View Replies !
SSIS Data Migration Logging
1) We are doing data migration from SQL Server 2000 OLTP design to SQL Server 2005 OLAP design. 2) We have used SSIS packages and data flow tasks in which we mentioned connection strings for source and target containers. 3) We have a master execute package which contains series of execute packages in relational order. 4) We want to log Insert Count which is not a system variable i.e. basically @@ROWCOUNT i.e. one per data flow/migration or source cum target tables combination. How do we achieve this? Can I make use of inherent table INSERTED? But there is update also involved & I need to log update count against package/table name. 5) We also want to mail/log details of error for OnError and warning for OnWarning i.e. what we get in debug mode when we execute packages in BIDS. Please post links if the steps/code is more to post.
View Replies !
Scrubbing Data In A Data Flow Task
I can only assume that I either did something very stupid, the examples are completely wrong, or I don't have the slightest clue what it is that I'm doing. I'm trying to do one of those incredibly complicated things in SSIS that was a brain-dead, point click operation in DTS. I want to strip characters out of an input stream from a flat file source before loading them into a table. I've defined my data flow task. I have my flat file source properly defined. I have my SQL Server destination properly defined. For the life of me, I can't figure out how to scrub the data that I want. My input is a csv file that has 22 columns of data. Column 22 contains data and if data was not present, there is a - inserted. So, I want to strip any - from the input before it goes into the column. So, I grabbed a derived column task and drug it out on the surface of the designer. I then set the derived column name to Column 22, the derived column to 'Replace Column 22', and tried the following in the expression section: REPLACE(Column 22, "-", "" ) REPLACE( (DT_WSTR, 50) Column 22, "-", "" ) Both of these threw some really nasty error messages when I clicked the OK button. The really irritating part is that I exactly followed the examples in BOL.REPLACE(Product, "Bike","")REPLACE((DT_WSTR,8)DaysToManufacture,"6","5") I know I'm not dreaming this, but it absolutely refuses to accept my expression.
View Replies !
Using An ODBC Data Source In A Data Flow
Okay, this should be really simple but I don't get it. How do I use an ODBC data source in an SSIS data flow task? When I look at Data Flow Sources I see the following options: Pointer DataReader Source Excel Source Flat File Source OLE DB Source Raw File Source XML Source Which one do I use if I need to get the data from a connection manager that is ODBC based? The IBM OLEDB driver for the AS400 doesn't work correctly so I HAVE to use an ODBC driver to connect to an AS400 data source. Thanks in advance for any info.
View Replies !
Pass Data Between Two Data Flow Tasks
Hi Guys, I have yet another question. How can i pass data b/w 2 data flow tasks? I'm trying to get some data from one sql server and then I want to pass this whole bunch of data to another data flow task which is going to get some data from second sql server. I would probably combine them using union all and then save that as a transaction in a third sql server? Information regarding how to pass the data with some detailed discussion would be fine with me. thanks Gemma
View Replies !
Data Flow
Is there a way to extract data from a source and insert these data directly into destination without using an intermediate step like storing data in a text or staging table? I am trying to use the data flow task: Source -> Destination. The Data Access Mode for both the source and destination is from a variable name. Thanks in advance. Andrew
View Replies !
Help With Data Flow
I would like to use Integration Services to update data in my datawarehouse. I have a table called "AgentStats" that stores archived data from the past 3 years. I would like to import the current year's data from the production server into the same table in my datawarehouse and have my ETL update only the current year's day on a daily basis. The current year's data is constantly updated in the datasource, so I achive the data at the end of the year. Any ideas how I can accomplish this? Thank You -Sam
View Replies !
Data Flow
I am designing a data flow archieture.I need the suggestions. What are the advantages of storing data of the flat files to sqlserver.
View Replies !
Tie Together Custom Logging And SSIS Logging?
I recently read the project real ETL design best practices whitepaper. I too, want to do custom logging as I do today, and also use SSIS logging. The paper recommended using the variable system::PackageExecutionId to tie the 2 logging methods together.
View Replies !
Help With SSIS Data Flow
To keep this simple, I have a control flow that loops through files in a folder. Within that loop I have a data flow. In my data flow I read in the file as a whole to get some counts of header rows, data rows, and total file size and store that in a db using various components. The last component in my data flow so far is a script component which I use to validate something so that I have set up 2 output paths, 1 if the validation passes and 1 if it errors out. For the error, I store the info in the db. For the row that passes, what I need to do is now go back to the origional file and instead of loading it as a whole, I need to now load it separating the data into columns. I cannot figure out how to do this. I was going to use another file source to reload but it will not let me connect the output of the script component to it. Does anyone have any ideas on how I can now go back to the orig file on disk and reload the data? or is there another suggestion? Thanks a bunch in advance!!!
View Replies !
Data Flow Based On A DSV
Hi, I've got this feeling that something is missing in Integration Services. You can add a DSV in any Integration Services project, but you can't use it inside a data flow. Can someone please tell me why this is? Let me explain why i want this: In Dynamics AX you've can make perspectives. These perspectives are a combined set of tables. From these perspectives you can automatically deploy these as Report Models. The Reporting Services generates a DSV for you, based not only on physical tables but also on Business logic. So it generates the linking between the tables (based upon application logic), derrived enumerated tables, referred tables, etc.. So these generated DSV's are great to get a quick startup. So repeating my question. Why can't i select a DSV as source? What is the function of a DSV inside Integration Services? Hoping to receive an answer for this question. Kind regards, Johan Blad
View Replies !
Custom Data Flow
To all the SSIS gurus, I have a situation where I have multiple source tables, I need to populate the destination tables that have the same schema as the source ones. I dont want to do the repetitive task of creating a Dataflow for each source-destination load. I want to create one custom dataflow component and loop through all my source tables and provide destination tables dynamically. Is there a way to do that? Any custom data flow component out there?? Your ideas are greatly appreciated. Regards,
View Replies !
Which Data Flow Task To Use?
I have a table which has been loaded from various source feeds. The SourceId relates to the source name and the SourceCompanyId is the sources primary key for the company. I am basically trying to create one row with all the SourceCompanyIds in my column headers. What data flow tasks would be necessary in SSIS? The structure of the final table is: CREATE TABLE [dbo].[Company]( [CompanyId] [int] IDENTITY(1,1) NOT NULL, [CompanyName] [varchar](75), [CIK] [varchar](10), [Ticker] [varchar](10), [Source1CompanyId] [int] NULL, [Source2CompanyId] [int] NULL, [Source3CompanyId] [int] NULL, [Source4CompanyId] [int] NULL, [Source5CompanyId] [int] NULL, [Source6CompanyId] [int] NULL, CONSTRAINT [PK_Company] PRIMARY KEY CLUSTERED ( [CompanyId] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] ================================= The table in which contains all the company data CREATE TABLE [dbo].[SourceCompany]( [SourceId] [int] NOT NULL, [SourceCompanyId] [varchar](10) , [SourceCompanyName] [varchar](75), [CIK] [varchar](10), [Ticker] [varchar](10), CONSTRAINT [PK_SourceCompany] PRIMARY KEY CLUSTERED ( [SourceId] ASC, [SourceCompanyId] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY]
View Replies !
Variables In Data Flow OLE DB
I'm storing a sql command (could be an SP call or simple select) that I would like to pass into the "OLE DB Source" container in my data flow task. If I choose Data Access Mode: SQL Command from variable, I get an error saying there is a data source column with no name. I've fought SSIS pretty good up to this point. I realize you need to "initialize" variables & expressions in the control flow ahead of time if you want to access them in the data flow. But I was kind of hoping that all I would need to do in this case would be pass a SQL command into a variable and access that somehow in the data flow without jumping through any hoops. Is this possible? This is a great forum btw, thanks, Phil
View Replies !
Data Flow Doesn't Run
I have a simple data flow inside of a ForEach container. The ForEach container uses an ADO recordset. Suddenly it seem it isn't running. The first two SQL tasks go green, and then the for each container itself turns green, but nothing happens to the data flow task inside of the loop, but I get the message saying it is done. I have a simple Sybase ODBC source, which when I preview the data in the source object itself it previews fine. When I try to preview the data in the destination it is blank. I have linked up the source and destination objects, and I haven't disabled anything. The mappings in the source and destination look ok--I"m moving the data here as is, nothing fancy. Not much to go on I know, but how do I troubleshoot this? The execution results seem all o.k.-it runs the query against Sybase and then just says it was finished. But no data was moved. Thanks for the help, Kayda
View Replies !
Data Flow Components
Does anyone know a good place to download (or even purchase) custom data flow components? Some ideas I've had (and I'm sure I'm not the first): Transformations: Filter Sometimes I just want to filter out certain rows. Unicode Converter Convert all unicode columns in place. Sequence Column Adds a column with a sequence of numbers. Or perhaps even random numbers. GUID Column Adds a column with a GUID. Destinations: Garbage Would act as a visual indicator in the designer that these rows are being discarded. You could add a data viewer right before it too. Log File Good for redirected error rows, writes them to various log files. Email Along the same lines as Log File, but sends off all the rows in a single email. Great for day to day things. Nothing too critical.
View Replies !
Retrieve Value Within A Data Flow
hi, I am doing a data flow to insert data into a dimension table(A) and master surrogate key table(B). Master surrogate key table(B) will be inserted prior to dimension table(A). My dimension table(A)'s key is depend on the last key in master surrogate key table(B). The data flow started from a flat file and checking if the key found in dimension table. If not found, insert a record into B. Then generate the new record to A with max id from B. My question is how to retrieve the max id in master surrogate key table(B) in a data flow and use it as an input to my dimension table?
View Replies !
|
TRACKER
