I'm developing a Destination Component. I need to create the input columns in my ReinitializeMetadata() method. This is a small code sample of the method:
This piece of code works fine, but now i want to use a UI Type DataFlowDestinationUI with a Destination mapping page added by base.AddDestinationMappingsPageToForm( form, "" );.
When I open the mapping page - ReinitializeMetadata() is called BEFORE opening this page - this error is shown:
Code Snippet
===================================
Exception from HRESULT: 0xC0047041 (Microsoft Visual Studio)
------------------------------
Program Location:
at Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSVirtualInputColumn90.get_UsageType()
at Microsoft.DataTransformationServices.DataFlowUI.DataFlowDestinationUI.mappingPage_MapColumns(Object sender, MapColumnsEventArgs args)
I think it's because the mapping page calls GetVirtualInput() itself. Do you know a solution for this problem?
I'm trying to do something simple that has turned out to be a frustrating problem. I have a field in my report that needs to be populated by user input. Based on this user input, I will use the field in a calculation. The simple solution to me, was to create a custom assembly that has a function that uses Console.Write("Input Data") and Console.readline() to assign the input to a string, and then return that string to the report. This works fine in a test console application, but when I copy and paste the code to my custom assembly, it returns #Error to the report. I've debugged, and found that when I hit F10 on Console.Write("test"); it skips right over it, and nothing happens. It also skips over Console.readline(); with nothing happening. This makes me unable to take the user input and set the textbox equal to that value.
Am I doing this completely wrong? Is there something I'm missing, or just not understanding correcly? It seems like reporting services has to have this option. I'd really appreciate some advice. Thanks!
Hi.I have a database I need to supply something (I'm assuming a t-sql script..maybe something else is better) to update customer tables with.The operations include mostly changing varchar lengths, though a couple ofcolumns were renamed.I'd like to maybe figure out how to get Enterprise Manager or Query Analyzerto generate the scripts.I can't just send alter table scripts because I'm involving all sorts ofconstraints that have to be disabled/or dropped, the alter made, then havethem enabled/ or re-created.Basically I'm hoping to get the tools to do the rather large amount of workfor me. I'm targetting sql server 2000.Can someone make a knowledgeable suggestion?RegardsJeff Kish
Hey, I changed the database name in the initial cataloge in the web.config conncetion string so that it now connects to other databas that contains same tables as the old one,but now i am getting that error at the update stmt ! thank u in advance Hiba
I've been trying to create an async transform component and one of the things I'd like to do is add a new output and/or columns to existing outputs for each input column that is selected. I thought that the SetUsageType method would be a good place to do this as it will allow me to delete output's and columns based on the user's selections but, despite the fact that my code doesn't actually throw or cause errors, when I return from the SetUsageType method I'm informed that error 0xC0047041 (DTS_E_OBJECTNOTINHASHTABLE) has occurred . I pared the method down to an absolute minimum and found that a call to GetVirtualInput is enough to push it over the edge even if it does nothing! What is wrong here? Thanks Charlie
Sample: public override IDTSInputColumn90 SetUsageType(int inputID, IDTSVirtualInput90 virtualInput, int lineageID, DTSUsageType usageType) {
Hi, I was wondering how I can complete a column (which doesnt have an input one) with data. For example:
I have a sql query which bring data of 3 columns
ID | FISRT NAME | LAST NAME 1 MIKE MORGAN 2 SARA JOHANES
So, I will insert that data in a FLAT FILE CONNECTION MANAGER, which I configured with 3 columns and I did the corresponding mapping in the FLAT FILE DESTINTATION.
Now, If I add one more column in the FLAT FILE CONNECTION MANAGER, I will not have it mapped to a input one, obviously. So, what I need is to add one more column to the flat file destination and complete it with zeros values in it.
Probably I can solve this part by introducing a DERIVED COLUMN and there I can configure the zeros that I want to add to the column. But I'm not sure if I can do that without having a input column. So, the question will be, how can I add one column to a flat file which doesnt have a input and introduce any value that I want to it? Hope I was clear Thanks for your help.
I have a stored proc I am updating in an OLEDB Command from the results of a Transform Script Component. The Stored Proc has over 65 input parameters, most of them have a NULL passed in. I dont want to create output columns in the Transform Script Component for all of them to map them from the "Available Input Columns" to "Available Destination Columns".
I want to create 3 or 4 generic Output columns for their data type - say IntegerOutput (datatype Int), DateTimeOut (datatype datetime) and so on. The I want to map these generic columns in the OLEDB Command as Available Input Columns" to multiple "Available Destination Columns" - wherever the datatype matches the input column.
But the OLEDB Command Column Mappings let me map One to One only. This will create a huge and unnecessary workload for me to develop and maintain - when I tell you I have 3 such stored procedures, all of whose interfaces are exactly same and for which I can create similar Output columns in the Transform Script Component.
I have an OLE DB Command which updates a table. However, the command needs to use the value of an input column more than once.
For example I want to update TableA only if either ColumnA or ColumnB have actually changed: - update tableA set columnA = ?, column B = ? where columnC = ? AND (columnA != ? OR columnB != ?)
I can't map the Input column to more than one parameter so I've been forced to create a copy of columnA and ColumnB as input columns so I can map to the extra paramters that the Command shape expects.
I also attempted to modify the command syntax so it set up variables for the 3 values required and then set the values to parameters - but I get a very unhelpful syntax error message: - declare @ValueA varchar(50), @ValueB varchar(50), @ValueC varchar(50) select @ValueA = ?, @ValueB = ?, @ValueC = ?, update tableA set columnA = @ValueA , column B = @ValueB where columnC = @ValueC AND (columnA != @ValueA OR columnB != @ValueB)
Is it possible to assign to a column a value passed as a parameter?
When I run the proc I get the following error :
Msg 245, Level 16, State 1, Procedure Transfer, Line 17
Conversion failed when converting the varchar value '@ID' to data type int.
----#################################################### USE [tbldata] GO /****** Object: StoredProcedure [dbo].[Transfer] Script Date: 05/19/2014 11:26:38 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[Transfer](@ID int)
I have a script component and under input columns, I checked one of the fields I want to use inside the script. It's under the Input 0 in the hierarchical tree inside the inputs and outputs.
I'm bothered by an issue of updating a column in input dataset from a update query. It looks like SSIS has a very poor function on this.
Example, I have an input dataset of name, salary, dept_no, and I have another table called departments which has fields of dept_no, Dept_name, basic_salary
now I want to update salary column in input dataset with basic_salary if it's salary is smaller than the basic_salary.
update #tmp set salary = basic_salary where #tmp.salary <departments.basic_salary and #tmp.dept_no = departments.dept_no
how could I impletement this in SSIS package?
I tried with lookup, modify scripts by enabling memory restriction. It doesn't say any error when I save the package, but I never get pass debug.
I am loading files with same format from two different locations in to one database. First, my SSIS connects to location 1.
Lets Say Location1 : C:LoadFilesImport Location2 : D:LoadFilesImport
Location one has three different set files starting with
First Set: AP_1, AP_2,AP_3, Second Set: VD_1, VD_2, VD_3, Third Set: BK_1,BK_2,BK_3,
This SSIS set to run every 3 hours, if it finds files of any set load them. While loading it has to insert into a derivedcolumn called CustID. If the file name Starts with AP_ , custiD values should be as 101
AP_1 goes to Table1 AP_2 goes to Table2 AP_3 goes to Table3
If the file name Starts with VD_ , custiD values should be as 201 If the file name Starts with BK_ , custiD values should be as 301
after processing all these files in first location, the SSIS looks for files in second location and does the same?
--How to achieve CustID depending upon file name ?
I want to run a loop for all the input columns in the script component. My requirement is, I have nearly 50 columns in the input columns list. For each row and for each column I need to do some operation. How Can I run a loop for each column. Please note in the script component I need to get the column names in the middle for some operations. Please see below.
Process Each Input Row
For each column in Input column list .... .... If column.Name Starts with "Test" then set NULL to the column value .... .... End Loop
I'm trying to read in a flat file (which, admittedly, has one very wide column), and it keeps breaking because of truncation when it tries to read in the file.
I have a quick question regarding SQL Server Enterprise Manager. I'm looking at setting up a job to automatically create DDL for a user database. This will be done along with our normal nightly backup routine. I'm very familiar with using EM to create SQL scripts, but is there anyway to schedule this task? I've considered DTS and some type of scheduled package, but can't seem to find anything similar. I'm thinking I may need a custom task. Could someone please shed some light on the subject? If not from within EM, how about any third party tools? FYI - I already own the Embarcadero suite and am trying it out wwith that.
A 3rd part app is requiring that I create a credential, whick in turn requires an SMK be set. When I try to create the credential, I get an error message indicating a decryption error. When I run the alter command to regenerate the key (without force) it throws an error indicating the key cannot be decrypted. According to a KB article I found, this may indicate that a key has never been generated.
My question is, I have a number of production databases in this instance, including SQL Reporting Services. Except for the SRS DB's, all other user db's are simple db's that don't use encryption. If I run the Force command to generate the key, am I going to break anything? I'm really concerned about report servioces.
I need a tool to generate sql code of database including all data like "insert into table values()". Same as sql file in IBuySpy portal. How can I generate a file like this? I tried with enterprise manager but it doesn't generate insert statements and default values of some fileds lost. Can someone help me?
I have a data driven subscription with the information about the file name/file extension/ path etc coming in from a database. The problem is that the subscription status after running tells me that things are done and there is no error but the file is not being generated in the specified directory and for that reason the file is not generated at all anywhere on the hard disk. can anybody please help.
The information in the database for the report is as follows
Hi all,I am following the procedure where I generate XMLs of tables and put itin a DataSet and read that dataset back into another database to bewritten into the tables.Although I am VERY close to completion a small problem that I am facingis that the XMLs being generated have as the table name "table" and notthe actual names.Is this a known issue or am I amiss? and, does anyone have a solutionfor this?Thank you.*** Sent via Developersdex http://www.developersdex.com ***
Hi guys,Apologies Simon for not making it clearer last time. What i am doing isreading a table from the SQL Server 2000 database and using:SQLDataSet.WriteXml(strFileName, XmlWriteMode.WriteSchema);to write to the DataSet. But I realised that when I do that the XML isgenerated correctly, the only error being that<xs:element name="Table"> comes in as the tablename instead of theactual table name.Any suggestions will be appreciated. Thank you.*** Sent via Developersdex http://www.developersdex.com ***
HelloFor my client, I need to generate reports from the information storedin the database. The client has fixed format forms (on paper e.g. USCustoms forms etc).Will I need to redesign the forms in the application and then show theinformation?Another approach is to scan the forms as image and print theinformation on top of that image, so when it is printed , theinformation will be displayed at the right places.Is there any other way? How is the reporting done if the forms arepre-defined and the information is stored in a databaseThanks for your input
I am working with SQL Server 2005 Reporting Service from few days, though I am not expert, for some reason I have to run on field without having sound knowledge of RDL, but need your help, gys. I am using SQL Server Business Intelligence Development Studio to design report.
Here is the procedure of my work to populate a RDL report
I used a stored procedure for the DataSet of my RDL Then I drag and drop necessary field to my report layout. Put required parameters to preview tab and then run report.
This is quite simple, I didn't face any problem with this process, even though the process may not correct, but working perfect. My problem is when the stored procedure returns multiple result set. The data tab only shows the first result set though the SP returns multiple result set, I have run the SP in the Query Analyzer. I don't want change my stored procedure.
And please give me some suggestions about the best procedure to develop RDL report in real life.
Can any one tell me how can I create auto number (similar feature to MS Access) i.e. autmatic increament by 1 in MS SQL 2005 (without using any script)
Does anyone know an efficient method for generating a sequence number in the following form?
Starting with 2 columns 1 2 ---- A X A Y B X B Y B Z C X
I want to then generate a third column as follows: 1 2 3 ------- A X 1 A Y 2 B X 1 B Y 2 B Z 3 C X 1
The purpose being so that I can easily identify the previous row within a Column1 group. So given column1=A and column2=Y I know that the previous row is Column3 - 1 where column1 = A. Therefore I will be able to join to the previous result of any row within any group quickly for future calculations.
I am using a relation(table) which has a artificial key. I want to use this key as the primary key hence is unique. What datatype is associated with this attribute in MS SQL 2000. How can I generate unique id everytime I add a new record to this table ?
I want to make up a system that I can create barcodes for my students and scan books for checkout. I am using Windows xp and .net winforms to finish the task. But I have no source for doing that. I am also wondering if my HTC sense could be used for scanning.
