Can anyone recomend an EXTREEMLY user friendly web based data mapping / conversion tool? I am wanting to transform csv, xml, database sources into csv, xml, db sources. There are a ton of Windows based applications (ie: www.altova.com / mapforce). I am looking for something I can expose via the web...
users has ability to upload a csv file and then define how the data would be mapped to an output source (say another csv or xml). In addition having functions like concantination, sum, if-exists, etc...
Does SSIS have a user friendly interface or has someone written some interactive tools on top of SSIS.
I am developing one automation tool for data migration from one table to other table, here i am looking for one function or SP for which i will pass source column and destination column as input parameter and want output parameter to return true when source column data is compatible to copy to destination column if not then it should return false.
For example if source column is varchar and destination column is integer, the script should check all the data in source column in good enough to move to integer column or not and return the output flag. I want a script to work this for all types of data types.
I have an Excel source > Data Conversion task > OLE DB Destination.
In the Data Conversion task I rename all the outputs to match the column names in my destination table.
However, when I go to map the columns in the OLE DB Destination mapping tab, it's a mess. Some fields are prefaced by "Data Conversion" as in "Data Conversion.column1", others are prefaced by "Excel Source" and others have no prefix at all.
What's confusing is, since all the columns are going through the Data Conversion task, how come I don't have ALL fields prefaced by "Data Conversion" ?
That is, only SOME of the fields have a "Data Conversion" prefix, and some don't. The ones that aren't prefaced by "Data Conversion" have no corresponding "Excel Source" so I'm assuming that the ones without the prefix are from the Data Conversion task.
Hi, I am less of a technical but more of a analyst professional and right now investgating on various tools / options for the new conversion project I will be leading in insurance client. One of the tools that client want to use is SSIS but the source and target database is not on SQL server but plans are to build a staging SQL server database for transformation. Does SSID supports this kind of ETL process where both source and target system are non SQL servers.
I'm trying to find out the best O/R mapping tool for .NET. A perfect tool would begin with the database model and would be capable of generating 2 layers: the Domain layer (normal classes) and the Persistence layer (methods for storing and retrieving the domain layer objects from the database.
This tool would also be capable of generating the corresponding Visual Studio solution and projects.
I've already tried NetTiers, but it generates so many layers that the code is very dificult to manage and understand.
So any of you know a tool similar to the described above?
I have a small problem in parameter mapping for Execute SQL Task. I am using a delete statement with 2 conditions. Followed by another Execute SQL Task which contains commit statement.
delete from tname where c1 = ? and c2 =?
where c1 is number(4) datatype and c2 is of varchar2(20) datatype in oracle.
The connection manager i am using is ORacle OLE DB provider. I am passing 2 global variables i.e g_v1 of Int32 and g_v2 of String Type.
In the parameter mapping of the Executing SQL task, i am mapping these 2 variables for c1 and c2 and changed the datatypes inside parameter mapping as Numeric for c1 and Varchar for c2.
I also set the property as ByPassPrepare = True.
When i am executing the package i getting INVALID NUMBER ERROR. i believe the SSIS is unable to perform the implict datatype converison.
For the next run, i changed the g_v1 varible datatype to Double and also i changed the parameter mapping for c1 as Doble datatype. This time it is working fine. I can see the Green signal for the 2 SQL Tasks.
But when i connected to Oracle check the count in the table, the data is not getting deleted.
Also, I set the property RetainSameConnection = TRUE for oracle connection manager. I am not able to trace this logical error.
The same is working fine in my local machine. But i am facing the problem when i deployed the same on the client machine.
Is there any problem with parameter mapping? What should be equialent Datatype for Oracle NUMBER datatype that should be used inside the SSIS package while declaring the global variable and inside the parameter mapping.
Hi all, about a year ago we started development of a tool to migrate DTS packages to SSIS and profile the effort. We're pleased to announce that it launched yesterday. It also applies a series of rules to your DTS packages as it converts like turning on logging or checkpoints.
You can see more information and download a trial here:
Does anyone know of a vendor who provides a packaged application that allows a user to pass T-SQL commands from a browser, to our IIS web server, to our SQL Server database?
I am trying to find a pure web based backup restore tool that can be used on MS SQL 2000.
I have found a few that claim to be web based, but then it tells you to log into your MS SQL server and make some adjustments.
I know that microsoft Enterprise manager comes with a backup / restore tool. but 1) i dont want to have to install a complete copy of 2005 server, and 2) i dont hav ethe physical space to do it right now anyway. all i need to do is move 2 databases from one account to another.
I am extracting data from SQL Server 2005 to flat file destination. I am using SQL Command to specify the data selection query. One of my query uses Replicate function to derive a column value. When I execute this package it fails with the error "Data conversion failed. The data conversion for column "value" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page".
The reason for the problem is that, it is taking the InputColumnWidth of the flat file destination as 8000 and I specified the OutputColumnWidth as 4.
If I change the OutputColumnWidth to 8000, it is working without any error but resulting in the column width of 8000.
I tried using DerivedColumn Transformation's Type cast and DataConversion Transformation but still I am getting the same error in the respective Transformation components.
I can€™t figure out how to map xml data stored in a table to a variable in integration service.
For example: I would like to use a €śfor each loop container€? to iterate through a row set selected from database. Each row has three columns, an integer, a string and an xml data. In the variable mappings, I can map the integer column and the string column to a variable with type of int and a variable with type of string. But I am having trouble to map the xml data column to any variable. I tried using either a string variable or object. It always reports error like €śvariable mapping number X to variable XXX can€™t apply€?. Any help?
Hello,Our company often receives data from outside sources to add to our application. This data is usually provided to us in Excel, CSV, XML, etc. The files that we receive usually have different columns from the columns in our database, so we have to map these columns to our table structure to import.I'm looking for an application that will easily allow me to load up the data file (whatever type it may be), expose the columns in the data file, allow me to map these columns in our SQL server, then import the data. I know that this can be done as DTS, however I'm looking for alternatives. Does anyone have any recommendations? Thanks in advance.
Hi Can anyone point me to a document somewhere that shows a mapping ofSQL Server 2000 datatypes to C datatypes? I am writing some extendedstored procedures which need to be able to process pretty much anydata type, so I want to make sure I am taking them from SRVPROC andstoring them in the correct C data type.Thanks,Bruce
Hi, Is there a way to accomplish one- many or many -one or many - many column mappings in the SSIS data flow task or using any other tasks. We were able to do this in DTS Transform data task. Also is it possible to edit the mapping like: dest column1 = Right(dest column1, 3)
I am just starting out with SSIS and trying to get the feel of data cleansing using it. But on my very first project for data cleansing I've got into this weird error.
My data flow is very simple, it has a OLE DB source, Fuzzy Lookup and OLE DB destination. I've built three tables for this purpose, one is source, one is reference (it will be used to match for the real entries in fuzzy lookup) and the last is the destination table.
In all the three tables I've a field of City which I'd like to Fuzzy lookup in the reference table and if it crosses certain confidence level, I'd like to insert to the destination table. City in all the tables has the same datatype, defined in the same way, it is varchar(50).
But when in the fuzzy lookup I try to map the Source tables City field to reference tables City field, it gives me this error:
The following columns cannot be mapped:
[City, CityRef]
One or more columns do not have supported data types, or their data types do not match.
Although as I have mentioned before, both have same data types and are defined in the same manner (i.e. I've just selected the datatypes for those columns and all the other settings are left to default). I just cannot understand why this is happening, plz help me with this. FYI I've also tried to give the City Column different datatypes in all the tables like varchar(max), text, Only to be greeted with the same error message.
Hi I need to map the unstructured report to my Sql server data source,Is there any method to achieve this in ASP.Net or is there any good tool available in the market which support ASP.Net2.0 with SQL server 2005.My requirement is to load/parse the existing data into my application which in various formats for each cleintsPlease help me on this
Hi, I am trying to use an integer as input parameter for my task I get suck on the parameter data type.
The input parameter is define as @Control_ID variable as Int32 in SSIS. When I got into the parameter mapping of Execute SQL Task, I don't find the Int32 data type. I used to try Short, Numeric, Decimal and so on, but all of those data type didn't work. and it returns the following error message:
SSIS package "DCLoading.dtsx" starting. Error: 0xC002F210 at Update Control_ID, Execute SQL Task: Executing the query "use DCAStaging
update DCA_HFStaging set [dbo].[Control_ID] = P0 where [Control_ID] is null " failed with the following error: "The multi-part identifier "dbo.Control_ID" could not be bound.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. Task failed: Update Control_ID Warning: 0x80019002 at DCLoading: The Execution method succeeded, but the number of errors raised (1) 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 "DCLoading.dtsx" finished: Failure.
I would like to transfer selected data from an ODBC-based table to a OLEDB-based table. However, there isn't a data flow source on the Data Flow Design screen to accomodate such an action. Please help!
I Have a problem when copying data from one server to another in Management studio, I need to create and exact copy of the original because of primary key relationships,
Currently when I export the data the data will run through an insert type statement, which means that all PKs are reissued, rather than being duplicated from the original, How can I be sure that the data will be copied exactly how it is on one server to the other.
I need a recommendation on a data modeling tool that can be used with a data warehouse. My warehouse is running SQL 2012.
Here is my challenge: Most of the tables in the warehouse do not have primary keys and none of the tables have foreign keys on them. However, there are indexes and unique keys/indexes on the tables. I am looking for a tool that I can create virtual relationships on how the data is related, so it is visually easier for the ETL developers to write the code.
I have looked at both ER/Studio 11 and ERwin 9.6. Neither of them do it exactly the way I want it too. However, ER/Studio is pretty close.
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.
For the Data Driven Subscription in SSRS we are using the following stored procedure
In Step 3 - Create a data-driven subscription
create procedure spRSGetReportSettings
(
@ReportID as integer
) as
begin
set nocount on
declare @t as table(y int not null primary key)
declare
@cols as nvarchar(max),
@y as int,
@sql as nvarchar(max)
set @cols=stuff(
(select N',' + quotename(y) as [text()]
from (select ParameterName as y from Reportsettings where reportid=1) as Y
order by y
For XML Path('')),1,1,N'');
set @sql=N'select * from
(select reportid,parametername, parametervalue from ReportSettings where reportid= ' + Cast(@ReportID as varchar(5)) +' ) as D
pivot(min(parametervalue) for parametername in(' + @cols +N')) as p'
exec sp_executesql @sql
end
Basically the idea is to maintain a single report parameter setting table for multiple reports.
Structure of the table is as given below
ReportID, ParameterName, ParameterValue.
Using Pivot we can generate the ParameterName/ParameterValue combinations for each report. This stored procedure is working fine in query editors(Management Studio)
But, in SSRS it is giving any results.
In Step 4 - Create a data-driven subscription,
Get the value from the database drop down, I am not getting any database columns.
Not seeing the Review Data Type Mapping Screen in SQL Server Import and Export Wizard?
Is there only a certain version where that screen shows up?
I am trying to import data from an MS Access application to SQL Server and all of the connections are good, but some of the data isn't and if I let it migrate using this tool it crashes on the bad data and there is no data that migrates. The Review Data Type Mapping screen will allow me to bypass the records in error and load the rest. however, I can;t do that if I cannot see the screen.
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
Does anyone have an opinion on specific “data comparison tools�?
We are looking for something to use in our test or dev environments that will be able to compare snaps shots of the data in a database before verse after a test event.
We have been able to record and compare data in specific tables but are learning that other tables were also being changed that we didn’t track. We want to be able to see all changes to a database.
