Dynamic Data Import
Oct 15, 2007
I've finally made some great headway on my import package and am close to the end. I am trying to do the following:
1. Automatically FTP down CSV files.
2. Loop through the directory and get the latest CSV file (filename has date in it)
3. Do a data conversion and insert - Flat file source to Ole DB destination
In my development I was able to get all this to work, but only when I explicitly set the flat file source. When I set an expression for the connection string, I get a "external metadata column collection is out of synch" error. Is there any way to preserve the column mappings when using a dynamic flat file connection?
Thanks.
View 3 Replies
ADVERTISEMENT
Apr 3, 2008
I set up a external data connection to a reporting services report via excel but because my report is paginated it will only retrieve data for the first page. Is there a way to make the report render on 1 page only?
or is there an easier way to do what i'm trying to do?
View 1 Replies
View Related
Sep 14, 2005
hello All,
Am not sure if this is the right forum to post this. But here goes
I have a phone book, which users could enter numbers via a web interface and a SP.
However i was wondering how to generate a bulk insert script, that would
Import from an uploaded excell workbook to MS SQL
How could this be done
NOTE: Am doing an upload via an ASP page, then hoping that either the ASP page, via a SP or an SQL command Statement do a bulk copy for the user who uploaded it to my SQL table
difficult ???
Thanks Anyway
Afrika
View 5 Replies
View Related
Sep 26, 2006
How would I import a dynamic file name into a known table?I have the file name in a variable. So what object in SSIS do I use to do this. Thanks
View 2 Replies
View Related
Oct 26, 2005
Hi
I'm struglling with this, can anyone assist. I'd go as far as to say, there is $$ up for the solution that works.
Here's the routine..
1) Upload file to server - works, no problem.
2) Extract cell ref A1-ws1 & A1-ws2 from the newly uploaded file (myXLSFileName - WHERE ProjectID = Session("strProjectID").
3) Update tMyTable.myfield1 with A1-ws1 & tMyTable.myfield2 with A1-ws2 WHERE ProjectID = Session("ProjectID")
All efforts greatly received!.
Thanks
View 2 Replies
View Related
Feb 25, 2008
A view named "Viw_Labour_Cost_By_Service_Order_No" has been created and can be run successfully on the server.
I want to import the data which draws from the view to a table using SQL Server Import and Export Wizard.
However, when I run the wizard on the server, it gives me the following error message and stop on the step Setting Source Connection
Operation stopped...
- Initializing Data Flow Task (Success)
- Initializing Connections (Success)
- Setting SQL Command (Success)
- Setting Source Connection (Error)
Messages
Error 0xc020801c: Source - Viw_Labour_Cost_By_Service_Order_No [1]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "SourceConnectionOLEDB" failed with error code 0xC0014019. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
(SQL Server Import and Export Wizard)
Exception from HRESULT: 0xC020801C (Microsoft.SqlServer.DTSPipelineWrap)
- Setting Destination Connection (Stopped)
- Validating (Stopped)
- Prepare for Execute (Stopped)
- Pre-execute (Stopped)
- Executing (Stopped)
- Copying to [NAV_CSG].[dbo].[Report_Labour_Cost_By_Service_Order_No] (Stopped)
- Post-execute (Stopped)
Does anyone encounter this problem before and know what is happening?
Thanks for kindly reply.
Best regards,
Calvin Lam
View 6 Replies
View Related
Oct 16, 2006
I am attempting to import data from Microsoft Access databases to SQL Server 2000 using the DTS Import/Export Wizard. I have a few errors.
Error at Destination for Row number 1. Errors encountered so far in this task: 1.
Insert error column 152 ('ViewMentalTime', DBTYPE_DBTIMESTAMP), status 6: Data overflow.
Insert error column 150 ('VRptTime', DBTYPE_DBTIMESTAMP), status 6: Data overflow.
Insert error column 147 ('ViewAppTime', DBTYPE_DBTIMESTAMP), status 6: Data overflow.
Insert error column 144 ('VPreTime', DBTYPE_DBTIMESTAMP), status 6: Data overflow.
Insert error column 15 ('Time', DBTYPE_DBTIMESTAMP), status 6: Data overflow.
Invalid character value for cast specification.
Invalid character value for cast specification.
Invalid character value for cast specification.
Invalid character value for cast specification.
Invalid character value for cast specification.
Could you please look into this and guide me
Thanks in advance
venkatesh
imtesh@gmail.com
View 4 Replies
View Related
Jul 3, 2006
I have a requirement to create many SSIS packages and no datatransform is required so the BCP task looks a good contender providing it can do both import & export
is it possible to parse the values in bold as package variables into the BCP task. If so how?
BULK INSERT ipcs_wvg.dbo.extract
FROM 'D:IPCSextract.csv'
WITH (FORMATFILE = 'D:ipcsqueryextract.xml');
Thanks in advance
Dave
I have a global database called ETL Configuration for all my SSIS packages that uses a single table. So I can create three global variables
USE [ETLConfiguration]
CREATE TABLE [dbo].[SSIS Configurations](
[ConfigurationFilter] [nvarchar](255) COLLATE Latin1_General_CI_AS NOT NULL,
[ConfiguredValue] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[PackagePath] [nvarchar](255) COLLATE Latin1_General_CI_AS NOT NULL,
[ConfiguredValueType] [nvarchar](20) COLLATE Latin1_General_CI_AS NOT NULL
) ON [PRIMARY]
Here is the data I would put in here
ConfigurationFilter = 'MySSISPackageName'
ConfiguredValue = 'D:IPCSextract.csv'
PackagePath = 'Package.Variables[User::gsFileName].Properties[Value]'
ConfiguredValueType = 'String'
ConfigurationFilter = 'MySSISPackageName'
ConfiguredValue = 'D:ipcsqueryextract.xml'
PackagePath = 'Package.Variables[User::gsFormatFile].Properties[Value]'
ConfiguredValueType = 'String'
ConfigurationFilter = 'MySSISPackageName'
ConfiguredValue = 'ipcs_wvg.dbo.extract'
PackagePath = 'Package.Variables[User::gsTableName].Properties[Value]'
ConfiguredValueType = 'String'
--Database connection info
ConfigurationFilter = 'MySSISPackageName'
ConfiguredValue = '.mssql2005'
PackagePath = 'Package.Connections[MyDatabaseName].Properties[ServerName]'
ConfiguredValueType = 'String'
ConfigurationFilter = 'MySSISPackageName'
ConfiguredValue = 'MyDatabaseName'
PackagePath = 'Package.Connections[MyDatabaseName].Properties[InitialCatalog]'
ConfiguredValueType = 'String'
I have looked at lots of options to automaticly create SSIS packages and have a hunch that that simple can be better: All these solutions look way to complex to what I want to achieve--
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=17484&SiteID=1
http://lakshmik.blogspot.com/2005/05/how-to-programmatically-create-ssis.html
Create DataFlow Package Sample
http://msdn2.microsoft.com/en-us/library/ms161541.aspx
SMOTableList Sample
http://msdn2.microsoft.com/en-us/library/ms161564.aspx
View 4 Replies
View Related
Jan 7, 2004
Hello:
I am not sure how to implement the following, but I believe it entails using DTS, and hopefully it is fine that I post it here b/c ultimately I will need this backend data for my frontend .aspx pages:
On a weekly basis, I need to IMPORT some data located on a remote Oracle DB into SQL Server 2k. Since there is so much data to transfer, I would only like to transfer the data that is new to the table since the last IMPORT, i.e. a week ago and leave behin the OLD data.
Is DTS the correct way to go or do I have more control via DTS with STORED PROCEDURES? Does anyone have any good references for me?
On a similar note, once this Oracle data is IMPORTED into a certain table, I would like to EXPORT some of these NEWLY acquired rows matching certain criteria into another table for auditing purposes. For this scenario, should I implement a TRIGGER UPDATE event here on the first table?
Any advice will be greatly appreciated!
View 3 Replies
View Related
Aug 19, 2015
I have some data in Excel - sheet1 which would be static.
Now I need to import data from SQL Server (using a query) and Union above static data with this SQL data and later I will have to create PowerPivot table in Sheet2.
Which is suitable option for me to import data from SQL to excel as I see "From SQL Server" option under "Data" and "Power Query" tab as well.
How to merge above SQL data with existing static data?
(SQL Server 2012)
View 6 Replies
View Related
Oct 25, 2007
Hello,
i want to import data from an excel sheet into a database. While reading from the excel sheet OleDb automatically guesses the Datatype of each column. My Problem is the first A Column which contains ~240 Lines. 210 Lines are Numbers, the latter 30 do contain strings. When i use this code:
Code BlockDim sConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & conf_path_current & file_to_import & ";Extended Properties=""Excel 8.0;HDR=NO"""
Dim oConn As New OleDb.OleDbConnection(sConn)
Dim cmd1 As New System.Data.OleDb.OleDbCommand("Select * From [Table$]", oConn)
Dim rdr As OleDb.OleDbDataReader = cmd1.ExecuteReader
Do While rdr.Read()
Console.WriteLine(rdr.Item(0)) 'or rdr(0).ToString
Next
it will continue to read the stuff till the String-Lines are coming.
when using Item(0), it just crashes for trying to convert a DBNull to a String, when using rdr(0).ToString() it just gives me no value.
So my question is how to tell OleDB that i want that column to be completly read as String/Varchar?
Thanks for Reading
- Pierre from Berlin
[seems i got redirected into the wrong forum, please move into the correct one]
View 1 Replies
View Related
Sep 20, 2005
What is the better table design for a data collection application.1. Vertical model (pk, attributeName, AttributeValue)2. Custom columns (pk, custom1, custom2, custom3...custom50)Since the data elements collected may change year over year, whichmodel better takes of this column dynamicness
View 7 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
Jun 7, 2006
I need to periodically import a (HUGE) table of data from an external data source (not SQL Server) into SQL Server, with the following scenarios:
Some of the records in the external data source may not exist in SQL.Some of the records in the external data source may have a different value at different imports, but this records are identified univocally by the same primary key in the external datasource and in SQL Server.Some of the records in the external data source may be the same in SQL.
Due to the massive volume of the import, I would like to import only the records which are different from what I have in SQL Server (cases 1 and 2 above). In fact case 2 is the most critical.
I thought of making a query with a left outer join between the data in the external data source table (SOURCE) and the data in the SQL Server table (DESTIN). The join is done on the respective primary keys (composed keys of up to 10 columns) and one of the WHERE conditions will be that the value in SOURCE is different from the value in DESTIN.
The result of this query would be exactly what I need to import.
How to do this in SSIS??? I couldn't figure out how to join tables in different data sources yet.
In fact I cannot write a stored procedure to do that, since one of the sources is in a datasources not SQL Server.
I have seen the Lookup transformation in this article http://www.sqlis.com/default.aspx?311 but this is not exacltly what I want to do.
Another possibility is to use the merge join, but due to the sorting I believe its performances would be terrible!
Thanks in advance for your suggestions!
View 9 Replies
View Related
Oct 4, 2007
Hi all,
It looks like these options are only available in the SQL Server Management Studio? I installed SQL Server Management Express Studio and I can't even find the DTSWizard.exe on my machine.
Can you please help how I can import data from excel or where can I download the SQL Server Management Studio?
Your prompt response is greatly appreciated.
Thanks!!
Tram
View 8 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 29, 2015
I am trying to import data from an excel Sheet to SQL Database using OPENROWSET. After import I found that all the cells containing data of more than 2000 length got truncated to  255 characters only. I tried finding the solution and found that We need to have the data with length more than 255 in first 8 rows of Excel sheet. It worked for me also. But In real scenario the data that I cant do the manual work on excel. I tried out with Dot Net utility and SSIS package also but the truncation is still the issue.
INSERT into tmp_Test
SELECT
*
FROM
OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel
12.0;Database=D:Book1.xlsx', [Sheet1$])
View 9 Replies
View Related
Aug 10, 2000
I have data for online catalogue in SQL 7.0. The web grogrammer asked me to add a unique key for reference. I used int datatype with identity seed of 1 and increment of 1. This works fine BUT when I try to import new data I get an error because the csv file has no column and therefore no value for the unique field which will not allow null by definition.
How can I maintain a unique field to act as primary key in my data when
I want to add (and delete) data that doesn't have this field.
I tried adding the uniqueidentifier field but this gives error message.
The only work round is to delete the unigue field altogether and then add the new data and afterwards create a new unique field. At 600000 + lines of data, this is time and memory consuming
Any help appreciated,Thanks, Keith
View 1 Replies
View Related
Jan 6, 2007
Hello
Looks like I'am the first one to pop this forum!
But I'am a forward guy so lets get to the problem.
A problem with my replication system as occured.
I have a working SQL server that can do replication trough internet, everyting works
The problem is when I try to import large amount of data (10000 rows) to my database on the SQL server
the subscriber on my client don't get the rows. That will say imported data is not being replicated.
only rows that i have manually inserted will be replicated.
I used the import wizard that came with SQL server.
Is there a solution to this problem?
View 5 Replies
View Related
Mar 4, 2008
Hello all, I am using the Import Wizard to pull in data from an Excel spreadsheet. One column in particular SQL Server sees as a float data type but it contains varchar data. So I change this in the wizard but some of these values are missing when I select * from Sheet1$ in SQL Server 2005. Any ideas why this would happen? I have formatted the particular column as text in Excel.
View 6 Replies
View Related
Jan 6, 2007
Hello
A problem with my replication system as occured.
I have a working SQL server that can do replication trough internet, everyting works
The problem is when I try to import large amount of data (10000 rows) to my database on the SQL server
the subscriber on my client don't get the rows. That will say imported data is not being replicated.
only rows that i have manually inserted will be replicated.
I used the import wizard that came with SQL server.
Is there a solution to this problem?
View 7 Replies
View Related
Feb 24, 2004
I have a situation where I have a lot of dynamic heirarchical data. I work for a manufacturing company, and as such, items we produce have Bills-of Materials (BOM). Some of our BOMs have items on them that have BOMs as well. This nesting can go as deep as 20 levels. Some components on each of the BOMs have a flag set on them.
I need to know the best way to drill through all of these BOMs to get a list of all the items that are flagged.
I tried to sketch out what I'm talking about in the attached image.
View 14 Replies
View Related
Jan 29, 2008
Hi,
I'm trying to group data by date range, but each row of data could have a different date range based on a variable.
I want to say "look at the date range the paste five orders were placed" for each row individually. As an example, think of the rows as keywords in a Search Marketing program. Keyword X had 5 orders placed in the last week, Keyword Y had 5 orders placed in the last 2 weeks. I want each keyword to display its average impressions over the course of its respective date range.
Is this possible?
Thanks in advance!
View 10 Replies
View Related
Jun 6, 2005
Sorry if this is a pretty basic question.. but my team here is working on SQL Reporting app for the first time.
View 19 Replies
View Related
Dec 28, 2007
I need to import data from a SQL Server (2000) to a SQL Server (2005 Express).I need some data only, and the database structure is different between the 2 databases.How should I do that? What's the best practice?
View 3 Replies
View Related
Jul 28, 2000
Hi!
There are various ways to import data in the sql server enterprise, but i want to import data through code, if it is possible, please help me..
View 1 Replies
View Related
Aug 10, 2000
I'm trying to import data into an SQL Server (7.0) and I'm wondering which Source (Microsoft Data Link, Microsoft ODBC Driver for Oracle, Microsoft ODBC Driver for SQL Server, etc.) -- I THINK we would use the SQL Server driver but I'm not sure... to use and where to go from there? So far, I get seem to get things to work in my favor. I appreciate any help :)
View 2 Replies
View Related
Aug 9, 2000
Working with SQL 7.
I wanted to upsize an Access 97 database (serves as backend of db app so contains tables only) to SQL 7. I used Import Data from Data Transformation Services from the Tools menu. The import procedure worked well except it stashed the tables in the Master db. That's probably because I didn't create a new db to import them into.
How to recover from this is my question. It is a new install of SQL 7.
Thanks
Juan
View 1 Replies
View Related
Oct 28, 1999
Hi!I am using SQL Server 7.0.My query goes as follows
There are two tables Table1 and Table2 both of which have a column companykey
which have PK and FK relationship
I have imported data from a *.CSV file into Table2
Table2 has many to one relationship with Table1 in the sense for each company in Table1 there can be more than 1 employees in Table2
Now I want to insert only the value of column CompanyKey from Table2 to Table1 and also some other columns values like employeekey from Table2 to regiskey column in Table1.
Now how do I do this???
I need this very urgently...
View 1 Replies
View Related
Mar 17, 2004
Hi,
I do a monthly import in MSSQL 2K. The file contains 7.000.000 rows. These rows are loaded in to a special (preload) database. After the import several modifications are performed before it's uploaded to production.
The importfile is a comma-seperated file, fields are enclosed by double-quotes (seems to be an issue for BCP?)
If I'm right, there are 3 options to import:
1) data transformation
2) Bulk insert
3) BCP import
Could someone give me a more detailed explanation what the differences are between those methods? What's the fastest? disadvantages?
Thanks in advance!
View 5 Replies
View Related
Aug 24, 1999
How can i import data from Visual Fox Pro Tables into SQL Server 7.0 using Transact SQL. Thanks.
João Pedro Carvalho
View 1 Replies
View Related
Jan 7, 2007
A problem with my replication system as occured.
I have a working SQL server that can do replication trough internet, everyting works :D
The problem is when I try to import large amount of data (10000 rows) to my database on the SQL server
the subscriber on my client don't get the new inserted rows. That will say imported data is not being replicated. :o
only rows that i have manually inserted will be replicated.
I used the import wizard that came with SQL server.
Is there a solution to this problem?
View 3 Replies
View Related
Oct 6, 2005
We have a database in sql server. Now in the front end, we have two fields called “latefee and latefeeDateReceived”. When next time the people get another late fee. They just replace the old one and enter the new one. But now we want to keep all the history. Keep whatever they get the late fee. I am thing maybe need to create another table called “latefee” to contain these value. Is is good idea? As later they may want to get a report for all the late fee in one company. The thing is, I need to retrieve the current latefee and latefeeDateReceived and import to “latefee” table. But I need to create primary key for the latefee table. How can I import? Only one thing is I can import the data first, then add primary key. If I import later, how can I import. (Explain, the data retrieved have fields called “CID, Latefee, latefeeDataReceived”. But the table “latefee” has four fields called, lateID, CID, Latefee, latefeeDateReceived. When I import, it is always failed. Anybody can give me an idea? Many thanks.
View 2 Replies
View Related
