Adding Static Column Of Data From Properties File To DB Import
Sep 4, 2007
Hey all-
I put togehter a package that opens a flat file, parses the data based on the semi-colon delimeter, and imports the rows into a database table. Thats the fun easy part.
What I cant figure out is how to add a variable that will hold a constant ID value that will be persisted with the same value to all rows inserted to the DB. Making the problem harder, I would like that this value be defined in a properties file or database table of some sort so that I can do a lookup based on the file name / location to find out what value should be used.
Any suggestions? I hope my explanation makes at least some sense - but basically I want to do a look up in a configuration of some sort, pull out a single value, and add it to a data import.
let the fun begin!!
View 4 Replies
ADVERTISEMENT
Sep 10, 2007
Hi,
I have a excel file which i want to import the data to sql server... The sql server Data type for that particular column is
varchar and it has a contraint too like the data should be in this fashion 00000-0000 or 00000...
but when i try to import the data from the excel to sql server... 08545 just becomes 8545 (cause excel is treating it as a float) and so my insert fails...
what can i do to rectify the problem...
regards
Karen
View 8 Replies
View Related
Mar 2, 2004
Hello dbforums,
I are using a BULK INSERT to insert the data from a ascii file to a sql table. The table has a ProductInstanceId column that exists in the tables but does not exist in the ascii DICast data. I am setting the ProductInstanceId to a Guid that will be used for Metrics. I would like to create the Guid in C++ and then set it somehow during the BULK INSERT DICastRaw1hr and DICastRaw6hr. I am calling the BULK INSERT from C++/ADO. I do not see how you can set a static data in the BULK INSERT for a column that exists in the table but does not the source data ... seems there should be a way to do this with the format file?
The other way to do this is with a TRIGGER. I have the TRIGGER below. Prior to the calling the BULK INSERT using ADO I will use ADO to ALTER the TRIGGER with the new Guid. When the BULK INSERT runs the ProductInstanceId will be populated with the new Guid.
ALTER TRIGGER DICastRaw1hrInsertGuid
ON Alphanumericdata.dbo.DICastRaw1hr
FOR INSERT AS UPDATE dbo.DICastRaw1hr SET ProductInstanceId = '4f9a44eb-092b-445b-a224-cc7cdd207092'
WHERE modelrundatetime = (select max(modelrundatetime) from Alphanumericdata.dbo.DICastraw1hr(NOLOCK))
More Questions:
- The Trigger is slow. The Bulk Insert without the Trigger runs in about 10 sec ... with the Trigger in about 40 sec. I tried to use the sql code below in the TRigger but it was only doing the UPDATE on the last row. The TRIGGER must run after the BULK INSERT is complete. Now I am using the select (bad). Any comments ...
ALTER TRIGGER DICastRaw1hrInsertDate
ON Alphanumericdata.dbo.DICastRaw1hr
FOR INSERT
AS
DECLARE @ID as integer
SELECT @ID = i.recordid from inserted i
UPDATE dbo.DICastRaw1hr SET ProductInstanceId = '4f9a44eb-092b-445b-a224-cc7cdd207092'
WHERE recordid = @ID
- I understand that I could set the Guid in the Default Value part of the table definition using the NEWID() function. I need the Guid to be the same for all the rows that are inserted during the BULK INSERT (all have the same modelrundatetime) ... how would I do this?
Thanks,
Chris
View 6 Replies
View Related
Mar 5, 2008
Hi,
I have a data; descriptions (types of elements in my system) on rows, and results (numeric values) of different branches for each description on columns. I have one entry (one row) for each individual day (so i have year, month and day columns) I prepared a report for this data (using enable drilldown; year --> month --> day --> description and its numeric values on the row) But I have to add new descriptions using my own descriptions, like formulas... My descriptions and my new descriptions made of with old ones, should be listed one under the other
For ex;
new_description = (descr1 + descr2) / descr7
Can I do this on report design step? If so, how?
View 4 Replies
View Related
May 13, 2015
I am creating a report in SSRS which has the following criteria:
- Row 1 (parent) is 'Product'
- Row 2 (child) is 'Feed'
- Columns are date. I have 5 dates showing at any one time across the top. The date field is set up as a parameter so depending on the date the user selects, the report will show that date on the end column and then the 4 days prior to that in the other columns.
- Data is the number of records.
I have a sub total on the Product and the report is collapsed on Product as default.
What i'm stuck on is trying to insert a column at the very end that will show the variance between the last two dates. So the difference between the date the user selected (@date parameter) and the day before that.
View 6 Replies
View Related
Feb 1, 2007
What is the best way to deal with a flat file source when you need to add a new column? This happens constantly in our Data Warehouse, another field gets added to one of the files to be imported, as users want more data items. When I originally set the file up in Connection Managers, I used Suggest File Types, and then many adjustments made to data types and lengths on the Advanced Tab because Suggest File Types goofs a lot even if you say to use 1000 rows. I have been using the Advanced Tab revisions to minimize the Derived Column entries. The file is importing nightly. Now I have new fields added to this file, and when I open the Connection Manager for the file, it does not recognize the new columns in the file unless I click Reset Fields. If I click Reset Fields, it wipes out all the Advanced Tab revisions! If I don't click Reset Fields, it doesn't seem to recognize that the new fields are in the file?
Is it a waste of time to make Advanced Tab type and length changes? Is it a better strategy to just use Suggest Types, and not change anything, and take whatever you get and set up more Derived Column entries? How did the designers intend for file changes to be handled?
Or is there an easy way to add new fields to this import that I am overlooking? I am finding it MUCH more laborious to set up or to modify a file load in SSIS than in DTS. In DTS, I just Edit the transformation, and add the field to the Source and Destination lists, and I'm good to go. My boss isn't understanding why a "better" version is taking so much more work!
thanks,
Holly
View 11 Replies
View Related
Jan 5, 2006
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 8 Replies
View Related
Apr 18, 2007
We are importing Flat file data from our Mainframe system. We have a lot of money amounts coming in, but the mainframe does not store the decimals in the flat file. So for example a row in the file might look like this:
+0000007894-0000000563
Where the first value is $78.94 and the second value is -$5.63
Is there anyway to have the Flat file connection manager put in the decimal place for me, or do i have to create derived columns for each column and divide it by 100? There like 50-100 columns per file, so i'm looking for a better, quicker way.
Thanks in advance.
John
View 12 Replies
View Related
May 30, 2008
Hello,
I have a OLE DB on a Data Flow that execute a query and returns 5 columns i want to add a new column with a static value that a have on a Variable.
I use Derived Column in order to create the new column adding the variable value in the expression field, Is this the best way??
Thanks
View 4 Replies
View Related
Oct 23, 2007
Hi All,
I have a particular issue that has been causing me some problems for a while. I have an SSIS package that imports an excel file into my database, and then performs various data manipulation that I won't go into. The problem I am having is at the import end. The excel source file I am working on is provided to me by my client. It is a fixed format and doesn't change, it contains a header row and there are 32 headings. The trouble I am having is that quite often, the last column is empty, i.e. it contains no data. The header is still there, but theres no data underneath. When I try to import this file using my SSIS package it fails, and complains about needing to remove the metadata for this final column from the External Columns list (VS_NEEDSNEWMETADATA). When I try to preview this file in the properties of the Excel Data Source, the last column does not exist. It's as if it's determining that as there is no data in that final column, that it's unnecessary and not part of the data set, even though it has a header.
Now I've done a bit of research, and found cases that a sort of like mine, I know that the excel file has the first 8 records sampled to determine the data format. This problem suggested to use the IMEX=1 extension in the connection string, which didn't help. I also discovered that when using flat files, if you have odd numbers of columns in your comma seperated list there can be problems. But neither of these issues seem to match the issue I'm facing.
Has ANYONE had a similar problem to me, and can anyone offer any kind of assistance regarding what I need to do to import an excel file that may or may not have data in the final column?
Thanks in advance,
Paul
View 1 Replies
View Related
Jul 30, 2007
I have a a flat file that consists of 2 Columns of data that need to overwrite an existing Table that has 3 Columns of data. The Import fails because the 3rd column on the table is a Date stamp column with the Data Type of "smalldatetime" and does not allow Null data. If I were to delete this 3rd column from the table the import works great but I lose the DateTime column. How can I use the Import Wizard to import the first 2 columns from a text file and update the 3rd column with the date and time? The wizard does not seem to let me update a column unless the data for this column comes from the flat file. Please assist, thanx.
View 6 Replies
View Related
Mar 13, 2008
Is it possible to add new data to an existing report. I already updated the SQL query, but the new data does not appear within the report. How can I modify the rows, columns and data fields???
Thanks in advance!
View 5 Replies
View Related
Dec 24, 2005
the text file format use the length of character to define the field
for example,
0001130130HAUT BAGES AVEROUS 03
9 chars <0001130130> is a field
1 char <H> is a field
20 chars <AUT BAGES AVEROUS 03> is a field
one record by one record store in db
no space, no symbol, no line break between each record
----------------------------------------------------------
I try bcp method, but some error happens. Please give me suggestions. thx
I run the following code in query analyzer.
BULK INSERT Chain.dbo.POLL59
FROM 'D:POLL59.DWN'
WITH (FORMATFILE = 'D:cp.fmt')
?????
it shows the error
Server: Msg 4839, Level 16, State 1, Line 1
Cannot perform bulk insert. Invalid collation name for source column 4 in format file 'D:cp.fmt'.
***
For your information
data file
000000011301220051222000192000000000011301320051222000030000000000019067420051222000000001<there are many space >
bcp.fmt file <I use tab to separate and use ascii>
8.0
4
1 SQLCHAR 0 4 "" 1 PLUEVT ""
2 SQLCHAR 0 9 "" 2 PLUSKU ""
3 SQLCHAR 0 8 "" 3 PLUFRD ""
4 SQLCHAR 0 9 "" 4 PLUPRC ""
!!!!
I try to edit collation name in Chinese_Taiwan_Stroke_CI_AS or others, but the error also happens.
View 6 Replies
View Related
Jul 10, 2007
Hi
I have a package which contains a foreach container. Can anyone help me in setting the properties for the enumerators programmatically??? I am trying to set the properties for the enumerator "ForEach File Enumerator"
The properties which i need to set are
1. Folder
2. File Type
3. Traverse Subfolder
4. Retrieve File Name
Thanks in Advance
Suganya
View 1 Replies
View Related
Feb 11, 2002
We are using SQL server 7.0 as RDBMS for BaaN ERP. We have
created the database file in E: drive. Now E: drive is going to be
exhausted, We are trying to add a new file at different location. When
we add the file into database it creates a file with default extension .ndf
but previous file has the extension .mdf. should we change the extension
of new file to .mdf or we should continue with default extension .ndf.
is it necessary to add a secondary file group or we add a file to the
existing primary group.
thanx in advance
madhu p menon
View 1 Replies
View Related
Feb 22, 2006
I'm using Delphi 2006 to create a DLL which will be integrated into SQL 2005. It's been a long road and I've made a lot of headway, however I am currently having the following problem creating the stored procedure:
My dll name is 'Crystal_Reports_Test_01'
In the DLL, my class is named 'Class01'.
In the DLL, my procedure is named 'TestMe'
I've managed to integrate the DLL into SQL using the following statement:
CREATE ASSEMBLY TEST_ERIC_01
AUTHORIZATION dbo
FROM 'c:mssqlassembliescrystalreports.dll'
WITH PERMISSION_SET = UNSAFE
I am attempting to create the stored procedure which points to the 'TestMe' method inside of the DLL. FYI: 'CrystalReports' is the namespace above my class that I had to add in order to get it to locate the class. The following code is used to create the stored procedure:
create procedure dbo.Crystal_Reports_Test_01(
@Parm1 nvarchar(255)
)
as external name TEST_ERIC_01.[CrystalReports.Class01].TestMe
But I get the following error:
Msg 6573, Level 16, State 1, Procedure Crystal_Reports_Test_01, Line 1Method, property or field 'TestMe' of class 'CrystalReports.Class01' in assembly 'CrystalReports' is not static.
I'm not sure what this means exactly. I think it means the method (the procedure) is not using Static method binding but should be. I have no idea what this really means or how to accomplish this in the DLL - or if I'm even going about this in the right way.
Any help would be appreciated ! I'll post the Delphi code (DLL) below.
Thanks,
Eric Gooden
library CrystalReports;uses System.Reflection, System.Runtime.InteropServices;...................type Class01 = class public procedure TestMe([MarshalAs(UnmanagedType.LPWStr)] var sVarString: wideString); export; end;procedure Class01.TestMe([MarshalAs(UnmanagedType.LPWStr)] var sVarString: wideString); export;begin sVarString:= 'Lets change the value and see if the stored proc. gets the change.';end;end.
View 4 Replies
View Related
Apr 28, 2007
Hi All
I have a matrigx report that groups by months in the columns. The reason for using a matrix style report is due to not knowing which months are going to exist in the database for the current year.
I do however need to have a static column appended to the matrix, using the same row groupings... I did think of placing a table next to the matrix with that column although im worried the row groupings and alignment may be off of each record.
Is it possible to have a static column inside a matrix that is not grouped by any of the columns just the rows.
What would be the best way to achieve this requirement. I also need to provide the options to hide the months columns and display only the static one and vice-versa...
Any help would be appreciated..
Regards,
Neil
View 4 Replies
View Related
Jan 9, 2007
Hello,
I am working with a matrix report item and would like to display something like that :
DataGroup1
DataGroup2
StaticText11 StaticText12 Data1
StaticText21 StaticText22 Data2
StaticText31 StaticText32 Data3
But I cannot find a simple way to have 2 columns of static text on the left. The only way I've found is inserting a table report item in the matrix cells, but it cannot be exported in Excel.
The only samples I found do'nt have multiple colums in the static rowgroup.
Is it possible ?
Kind regards,
Xavier Miller.
View 14 Replies
View Related
Nov 2, 2015
I have a SQL VIEW with col1, col2, col3. I need to add a new column to the view col4 coming from a TABLE in SQL Server.
View 4 Replies
View Related
Apr 24, 2008
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?
Any thoughts on how to?
View 9 Replies
View Related
Jul 13, 2007
I'm moving data between identical tables and have to use a flat file as an intermediary. I thought: "No problem, SSIS can do a quick export to a file, then move the file to another server, then use SSIS to import the data to the new server."
Seems simple, right?
I'm hitting all sorts of surprising data conversion errors. I used the export wizard to create the export package. This works fine. However using the same flat file definition, the import package fails -- even when I have no destination. That is I have just one data flow task that contains only one control: the Flat File source. When I run the package the flat file definition fails with data type conversion and truncation errors. One of the obvious errors is for boolean types. The SQL field is a bit, SSIS defined the column as DT_BOOL, the output of the data are literal text values "TRUE" and "FALSE". So SSIS converts a sql datatype of bit to "TRUE" and "FALSE" on export, but can't make the reverse conversion on import?
Does anyone else find this surprising? I would expect that what SSIS exports, it can import given all the same table and flat file definitions. Is SSIS the wrong tool to do such simple bulk copies? I'd like to avoid using BCP because this process will need to run automatically within SQL Agent so we can leverage all the error tracking and system monitoring.
View 12 Replies
View Related
Sep 10, 2007
I have one column in SQL Server 2005 of data type VARCHAR(4000).
I have imported sql Server 2005 database data into one mdb file.After importing a data into the mdb file, above column
data type converted into the memo type in the Access database.
now when I am trying to import a data from this MS Access File(db1.mdb) into the another SQL Server 2005 database, got the error of Unicode Converting a memo data type conversion in Export/Import data wizard.
Could you please let me know what is the reason?
I know that memo data type does not supported into the SQl Server 2005.
I am with SQL Server 2005 Standard Edition with SP2.
Please help me to understans this issue correctly?
View 4 Replies
View Related
Jul 2, 2015
I have a flat with few columns
FirstName, lastName, Address
f1,l1,a1
f2,l2,a2
I build my SSIS package based on the above file.But now i receive files with different columns order let say
lastName,FirstNamr,Address
l1,f1,a1
L2,f2,a2
or
Address,FirstName,LastName
a1,f1,l1
a2,f2,l2
every time i receive multiple files in different order and i have to remap all my mappings. These are just a few columns and i have like 20 columns and the order can potentially change any time. so every time i have build new packages remap them etc.
through normal c# code it pretty easy. I tried to add script here but the script also needs a source and mapping so there is also a mapping issue. Is there a better way to do this.
View 10 Replies
View Related
Jul 27, 2015
how to add the Secondary Data file to the Database that is a part of the Always on Availability on SQL Server 2012.
View 3 Replies
View Related
Jan 13, 2005
Hi,
I backup my database through SQL Server 2000 SQL Query Analyzer :
"BACKUP DATABASE Mysite to disk = 'd:BatabaseBackupMysite.bak' ",
and got a bak file to import it to hoster server. Now I need import (backup)
database from that hoster server back to my local server. I got a bak file call
"MysiteExport.bak" file and transfer it to my computer d:BatabaseBackupMysiteExport.bak' . Then use SQL Server import wizard to import "MysiteExport.bak" and fail to do it.
How can I import/restore database from this "MysiteExport.bak" which have already
under my computer. If use SQL Query Analyzer, what is the correct statement.
Please help.
Lin
View 6 Replies
View Related
Nov 3, 2003
I have no previous experience in MS SQL and i have this little problem : i import data into a table from a txt file (log file after process) and i want to put the time/date string into a column with data type timedate, but i cant make it, there is an error message (cant store char data into timedate field...)
Any help??
thanx in advance :P
View 3 Replies
View Related
Mar 15, 2004
I have a .csv-file which is imported into my database using DTS.
When look at the DTS package in design-mode i can see that it creates a table and then copies the data from the file into the created table.
Nothing wrong there.
But when the contents of the file changes (e.g. column added or removed) the create table script raises an error. The script is not dynamic, it is created when the package is created and only changes when the package itself is re-created.
Is there a way to make the create table script be dynamic so that the table is dropped re-created according to the format of the .csv-file?
Thanks in advance
View 3 Replies
View Related
Mar 3, 2008
hello there!
I'm newbie here and I would appreciate a little help..
I have a .ttx file like this
"xxxxxxxxx" "dc8" "184:30" "168:00" "00:00" "00:00" "00:00" "00:00" "00:00" "00:00" "00:00" "00:00" "00:00" "00:00" "00:00" 0 0 0 1
I want to put every value between double quotes in an exact column of a table (lets say it IMPORT table). The problem is that values in quotes refer to hours. So I'd like to
get a result 184,5 hours for "184:30".
Thanks in advance!
View 1 Replies
View Related
Jun 21, 2006
Hi,
I'm having dificulties in loading data into a table coming from an excel file because one of the columns is a text based with an average of 1024 characters... How can i import that column? The excel source always shows me the column as a DT_WSTR of 255 characters...
Best Regards,
Luis Simões
View 7 Replies
View Related
May 30, 2006
I am using the Import wizard to import a SQL2000 database to SQL2005 and noticed 2 problems:
1. all tables and views were selected; the tables were imported correctly but the views were created as tables, ignoring the "Create view" syntax. The SQL generated contains "Create table" syntax instead of "Create View".
2. when a table contained a column with an "identity" property, the data was successfully imported, but the values for the "identity" column were not preserved, instead they were resquenced from 1 with an increment of 1 (the default values for an identity). When I opened the "Edit" (under "Mapping"), "enable identity insert" was not checked.
A further note: I created all tables in the SQL2005 database before running the Import.
View 4 Replies
View Related
Mar 3, 2008
hii all,
i've to import bulk data from excel file to sql server 2000 , i'm using asp.net 1.1 with C# and i've to make a front end(windows application) for this.
help me out if any1 knows that...
Thanks & Regards
anant vijay
View 2 Replies
View Related
Jan 30, 2002
Hi,
I am having my present database system in DB2. Recently, I have transferred to SQL Server 6.5 and have created a database there.
I want to import all my data from dbase to SQL Server 6.5.
I have created a CSV file from the dbase database.
How can I import this data into a table in SQL Server 6.5 ??
I can't find any DTS component in SQL 6.5 like we have in SQL 7.0 and 2000.
Thanks.
View 1 Replies
View Related
May 16, 2003
Hi, does anyone know how to access the *.evt file (SysEvent.Evt) from SQL in order to import a data from a file into a server table?
When you run EventViewer there is an option to save the log file as *.csv file and then use it. I want to eliminate this step (or make it automatic) and get the data into SQL right away.
Any ideas?
Thx
Dim
View 13 Replies
View Related
