I have text file having three column
like
300;Dev Fot;30097
400;Kit Mol;79684
now i have to insert this in DB with SSIS but at the same time I
want another column insert in DB table with ID
like
1;300;Dev Fot;30097
2;400;Kit Mol;79684
my table structure is
Table_name (ID not null,EmpID not null;desc Not null;FinID Null)
I have a tab delimited file I need to transfer to a table using SSIS. Columns can have NULL value and there might be extra tabs in each row also. How can I do this? Maybe fuzzy lookup?
I have worked with straight-forward simplistic SSIS up to now, so I apologize if this is a simple question. I have a SQL table destination that accepts daily data from an AS400 table. We now have a need to datestamp the incoming data so I added a new column to the destination table where I want today's date. Is there a data flow transformation that I need to use to default the destination column to today's date? Time is not a consideration and not needed, just the date.
Hi, I am declaring a table with 2 column Id, and Name. Id is a integer column but Name is a Varchar. I am declaring Name as VARCHAR(100) you can say that varchar(100) is more than enougf for a name column. But lets suppose ifn futuer user will enter data only till 100 characters but lets suppose i put or declare it as NAME VARCHAR(4000), That means a column requires only 100 memory space but i am giving 4000 to that column So in case of SQL any drwbacks are there for this approach. Meaning my 3900 space will not be used so will it a wrong thing or we can declare it as ,,, it will put no effect??? or what are the drawback??
I'm trying to make a report with the following layout:
Car Sales 2004 2005 Var % Total 10 20 50% Green 7 14 50% Red 3 6 50%
I'm using a matrix and the data is coming from a cube. One dimension called 'Years' is used to fill the matrix columns and the data corresponding to the car sales is filled by a measure called 'Sales'. I've built part of the example but I can't add the final column ( the VAR% column). If I try to add a static column in the end it apears only one year. The behavior that I would like to have is simillar to the situation when we add the Subtotal column. Although, instead the sum() made by subtotal I would like to calculate the variation percentage between years. Is there any way to add a final column into a matrix avoiding the problem that I'm having or maybe change the behavior of Subtotal column?
select max(PTR_DATE) from MPR_portfolio_transactions group by PTR_SYMBOL
and this is working fine, but if I add an extra column with another field like:
select max(PTR_DATE) , PTR_SHAREBALANCE from MPR_portfolio_transactions group by PTR_SYMBOL
Then I get an error message like:
Column 'MPR_portfolio_transactions.PTR_SHAREBALANCE' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Is it possible to show or hide columns in a report on the fly, i.e. without having to run the report again?
I have a report in with a parameter to let the user choose between the data to show. However the customer doesn't like it that he has to rerun the report. Any help would be appreciated!
Hi all, please help. I m trying to create an "empty" table from existing table for the audit trigger purpose. For now, i am trying to create an empty audit table for every table in a database named "pubs", and it's seem won't work. Please advise.. Thanks in advance.
SELECT @TABLE_NAME= MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE= 'BASE TABLE' AND TABLE_NAME NOT LIKE 'audit%' AND TABLE_NAME!= 'sysdiagrams' AND TABLE_NAME!= 'Audit' AND TABLE_NAME = 'sales'
WHILE @TABLE_NAME IS NOT NULL BEGIN
SELECT @TABLE_NAME= MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.Tables WHERE TABLE_NAME> @TABLE_NAME AND TABLE_NAME = 'sales'
SELECT @TABLE_NAME= MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.Tables WHERE TABLE_NAME> @TABLE_NAME AND TABLE_TYPE= 'BASE TABLE' AND TABLE_NAME!= 'sysdiagrams' AND TABLE_NAME!= 'Audit' AND TABLE_NAME NOT LIKE 'audit%'
Here's a tricky SQL question that has definitely driven me to the end ofmy rope. I'm using Oracle 9i and I need to perform some simplemultiplication on a field and then display it with a percent sign usingthe COLUMN command. Here's the code thus far:COLUMN price format 9,999.99 HEADING 'Charged%'SELECT pricecharged * .231 as priceFROM VT_examdetailThe output from this reads:Charged%---------23.1034.6534.65....The kicker here is that I need to add a percent sign to the right of theoutput, so that it reads:Charged%---------23.10%34.65%34.65%....I thought I could do this by just adding "|| ('%')" into the SELECTstatement, but when I do this the decimal position defined in the COLUMNcommand is lost. Does anyone know another way around this?Thanks,Alex
I am getting extra column while exporting SSRS report into excel but those columns not in my report. How to avoid these columns please find the attached screen shot with red circle.
Before I start just wanna say thanks for all the top advice so far - much appreciated. My current quandry is this:-
I have a table called STOCK with columns ITEM_ID, ITEM & PRICE. Most of the stock is my own produce but i do buy-in some items. What i want to do is add some extra columns to STOCK for the supplier details on those items i buy in. The problem is that a lot of the fields in the supplier details columns will be empty. Would this be a problem or would it be better to have a seperate table for supplier details?
Happy 2008!!!! I am inserting data into a tab delimted text file using SSIS package. After data insetion some extra tabs get added between columns in some rows in the text file. Can we programmatically delete the extra tabs from the text file, if so how to use/implement the code inside the SSIS package? Any pointer/suggestions are welcome.
Hi, I've got a table with trialsubscriptions. When someone orders a trialsubscription he has to fill in a form. With the values in the form I like to fill a database with some extra fields from the trialsubscription table. I get those extra fields with a datareader. But when I insert I can't use the same datareader (its an insert), I can't make two datareaders because I have to close the first one etc. Does someone had the same problem and has some example code (or make some :-)) Some keywords are also possible! Thanks! Roel
Hi all, please help. I m trying to create an "empty" table from existing table for the audit trigger purpose. For now, i am trying to create an empty audit table for every table in a database named "pubs", and it's seem won't work. Please advise.. Thanks in advance.
SELECT @TABLE_NAME= MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE= 'BASE TABLE' AND TABLE_NAME NOT LIKE 'audit%' AND TABLE_NAME!= 'sysdiagrams' AND TABLE_NAME!= 'Audit' AND TABLE_NAME = 'sales'
WHILE @TABLE_NAME IS NOT NULL BEGIN
SELECT @TABLE_NAME= MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.Tables WHERE TABLE_NAME> @TABLE_NAME AND TABLE_NAME = 'sales'
SELECT @TABLE_NAME= MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.Tables WHERE TABLE_NAME> @TABLE_NAME AND TABLE_TYPE= 'BASE TABLE' AND TABLE_NAME!= 'sysdiagrams' AND TABLE_NAME!= 'Audit' AND TABLE_NAME NOT LIKE 'audit%'
I have two tables 1) tblCustomer (ID, Name, City) 2) tblemp (ID, NAME, Dept.ID, tblcustomer.ID) Both the tables have ID as PKA emp can be either assigned a) All customers b)single customer c) NO customer Pls note:- there will never be 2 or 3 customer linked to emp (my actuall requirement tables are different but to explain i am using the above tables) I know how to assign single customer......but had problem how to link all customers and "no customer" Please tell me if the following solution is right?1) I will manually insert a record in tblCustomer with id 0 as " all customers" and will not allow the user to delete it 2) store null in FK if it is "no customer" Also please tell me is it ok to store one more value in tblCustomer as -1 and take it as "No customer" I have a DDL in the Employee page which should be displaying the names of allthe customers with 2 extra values "ALL CUSTOMERS" and "SELECT" Select is the default value in DDL which says "NO CUSTOMER" selected yet Thank youSara
I am trying to insert an NTEXT value from one table T1 to another table T2 within a database. Table structures are as below;
Create table T1 (T1ID INT NOT NUll, SourceColumn Ntext null)
Create table T2 (T2ID INT NOT NUll, TargetColumn NVARCHAR(MAX) null)
Every time when long text value is getting inserted into T2.TargetColumn , it is appending with an unwanted character '?' either at the beginning or at the ending of the text string. Same problem happens even when I am trying to update T2.TargetColumn = T1.TextColumn. Because of this, the same column never matches to the source column and gets updated every time even there is no change...
I am also converting NTEXT column to NVARCHAR(MAX) and replacing CHAR(10) to '' .
I am using SQL Server 2012. How can I avoid inserting '?' in T2.TargetColumn . Is there any setting which I need to set in the target table?
Is it possible to insert the identity cloumn in a table via SSIS. I've an ID (Identity) column is a table and I am importing data in the table using Excel sheet. I want to insert the value of ID column as Identity(1,1).
I'm importing data from Navision 3.70A Database (not MS SQL Server) with SSIS and data reader via odbc.
Works perfect until I try to import a table which has a column including cells with | (pipe symbol) and .. (dots) between numbers.
8420|8421|8430|8431
8900..8944
the error message from data reader: [get sachkonto [5165]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "component "get sachkonto" (5165)" failed because error code 0x80004003 occurred, and the error row disposition on "output column "Zusammenzählung" (8265)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.
No error message exist before this message.
Is there an option to allow or a workaround? Maybe a replace function in the sql command can help but I have no sql manual for the native database of navision. Can somebody help with an example of a query?
I can read this table with Excel via ODBC without problems ...
My Requirement IS : 1<sup>st</sup>run: if the record does not exist in the table insert the record (file_name, last_modified_file_date) and create a copy in the archive folder with file_name_currentdate.csv
Daily run: retrieve the last_modified_file_date from the input file and check if the retrieved date is greater than the last_modified_file_date in the table:
If true: create a copy of the input file in the archive folder and update the last_modified_file_date in the table with the retrieved date
If false don’t do nothing because the file has been archived in one of the previous runs.I have already retrieving the modified date and File Nae iserting into Filename Table: (That table has 2 columns which are FileName and FileDate) so In script task everytime the variable getting Modified date(retrieve the last_modified_file_date from the input file). How I can Compre the existing table record and variable. I have already imported the all Filenames and Modified into table like below.
How do i import a Varying Column Width Flat file into a Table using SSIS?
I have a flat file that has 4 columns with varying width Like I should read the file as Col 1 - (1 to 10 Characters) Col 2 - (12 to 21 Characters) Col 3 - (22 to 35 Characters) Col 4 - (36 to 38 Characters) At the end of the record is a "LF"
I think "Fixed Width" Columns allow me to define a standard column length for all the columns.. Right?
I have a simple enough task to complete that I can€™t seem to find the answer to.
The task is this €“
Select table x from the database and write it to a flat file complete with that tables column headings.
Now I€™ve managed to set up an ole db datasource and selected the table and I€™ve also linked it to the flat file output. So now I can generate a flat file from the database. However no column headings appear in the flat file.
I can€™t seem to find anywhere (like a checkbox) that will also output the column headings to the flat file.
Now I can add in Headings manually in the properties of the Flat File Destination object but the columns that appear in the flat file don€™t appear to be in the order that I requested them in the SQL.
So the question is how do I automatically have the column headings appear for flat file output (ideally without me having to manually add them in).
If it can€™t be done and I have to use a vb.net script instead then would anyone have an example script of how to do it?
Thanks in advance for anyone who manages to answer this.
TABLE_NAME DESC CODE tab1 table1 A tab1 table1 B tab1 table1 C tab2 table2 D tab2 table2 E tab2 table2 G...
First column values are table names which are already exists in target database. Next two columns[Desc],[Code]Â data gets populate from CSV file to table.
In this scenario, how to load tab1 data into the same table in destination and so on.
Which way will be more standard to accomplish this task? If its a script task using C#, looking for clear script to identify a value changes in the first column.
For example,I have a table "authors" with a column "author_name",and it has three value "Anne Ringer,Ann Dull,Johnson White".Here I want to create a new table by using a select sentence,its columns come from the values of the column "author_name".
can you tell me how can I complete this with the SQL?
Hi all, I am not over familiar with SQL, I am a VB programmer, simply I need to achieve the following within Enterprise Manager.
I have 2 tables, different designs, different number of rows, I simply need to check whether the contents of a column in the first table is in a column in the second table, just simply a table/column to table/column data check for the same data content.
Easy Peasy for you guys, any help would be appreciated.
I want to use time series algorithm to mine data from my case table and nested table. Case table is Date table, while nested table is the fact table. E.g, I want to predict the monthly sales amount for different region (I have region table related to the fact table), how can I achieve this?
Thanks a lot and I hope it is clear for your help and I am looking forward to hearing from you shortly.
I am confused on key column of case table and key time column of nested table by using Time Series algorithm.
In my case, the case table structure is as below:
Territory key text (the ID is actually dimrisk_key, in this case, I use the name column binding to combine the Territory column of case table Dimrisks),
While the nested table structure is as below:
Cal_month key time (in this case, actually the ID is dimdate_key, again, I used name column bining property to bind the Cal_month to the ID)
So my question is, as the key column of case table has been set to be Territory, as a result, does the model training still cover all the cases (rows) based on the ID of the table?
Also, in the nested table, as the key time column has been set to Cal_month rather than Dimdate_key of the nested table, as a result, would the single series based on the cal_month?
Hope it is clear for your advices and help.
And I am looking forward to hearing from you shortly.
Hi guys, If I have a temporary table called #CTE With the columns [Account] [Name] [RowID Table Level] [RowID Data Level] and I need to change the column type for the columns: [RowID Table Level] [RowID Data Level] to integer, and set the column [RowID Table Level] as Identity (index) starting from 1, incrementing 1 each time. What will be the right syntax using SQL SERVER 2000?
I am trying to solve the question in the link below: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2093921&SiteID=1
Thanks in advance, Aldo.
I have tried the code below, but getting syntax error...
ALTER TABLE #CTE ALTER COLUMN [RowID Table Level] INT IDENTITY(1,1), [RowID Data Level] INT;
I have also tried:
ALTER TABLE #CTE MODIFY [RowID Table Level] INT IDENTITY(1,1), [RowID Data Level] INT;
I have an SSIS package that imports data from an Excel file, replaces any value in Excel that reads "NULL" to "", then writes the data to a couple of databases.
What I have discovered today, is I have two columns of dates, an admit date and discharge date column, and what I need to do is anywhere I have a null value in the discharge date column, I have to replace it with the value in the admit date column.Â
I have searched around online and tried a few things using the Replace funtion in Derived columns but no dice so far.Â
how to declare multiple derived columns in SSIS Derived Column Task in one attempt.as i have around 150 columns coming from Flat file. I had created the required Expression in Excel and now i want add those in derived column task but its allowing only 1 expression at a time.
