Dynamic Importing Of Files And Data
Aug 4, 2000
I am new to SQL7 and I am trying to run a package that will import fixed text files in a folder and transform the data to a table. I am using a good example found in sywink.com for dynamically importing files in a directory, but I am getting an error message when there is more than one file in the folder. Also, I am able to display all the files during the loop process, but the file initially set for the source connection is the only one that transfers data. I have the close(Transform) connection checkbox option checked, and have tried other methods of closing the connection before the new source name is called, none have helped. Does anyone have any solutions to this type of problem? Or know of other methods used for my situation?
Thanks
James
View 1 Replies
ADVERTISEMENT
Jan 29, 2008
I need to write a genric CSV importer. one set of CSVs define the formats of all teh other CSVs. The format of the second set of CSVs is not know at the time of creating this project/SSIS package.
Imagine having two sets of CSV files.
Each file in the first set of CSVs define the format of files in the second set of CSVs
The first set always have the same format. Each row of the CSV file defines a field with a Name and Type.
The first two columns of each row of in the first set of CSVs have a FormatName and index.
So a simple file may have:
Format1,1,Name,string
Format1,2,Surname,string
Format1,3,DOB,Date
Format1,4,Email,string
The second set of CSVs contains records that have to comply with the format define in the first set.
So in this aprticular case, a CSV file in the second set may have records such as:
Format1,John,Doe,2007-01-01,john@doe.com
Format1,Jane,Doe,2007-02-02,jane@doe.com
The problem I have is creating a generic SSIS package for this.
The first task, loading the first set of CSV file is fairly simple. The CSV format is known.
But the second task is a bit trickier.
Assuming I have SQL tables to load the data.
One 'Fields' record for each row in the CSVs from the fiirst set.
One 'Rows' record for each row in the CSVs from the second set,
One 'Values' record for each value in each row in the CSVs from the second set,
Something like:
TABLE Fields (
FieldID int IDENTITY,
FormatName varchar(100),
Position int,
ColumnName varchar(100),
ColumnType varchar(20) )
TABLE Rows (
RowID int IDENTITY )
TABLE Values (
ValueID IDENTITY,
RowID int,
FieldID int,
ColumnValue varchar(100) )
What would be the best/easiest SSIS approach:
- Dynamically create a SSIS package based on the content of each CSV fiel of the first set and execute that package for each file in the second set, selecting the correct package to execute (all records within a CSV file belong to the same format).
- Write a single SSIS package that iterates through the rows of the second CSVs, does a lookup for each value of each row to find the field name and make an insert into the DB
- Other SSIS method?
- Don't use SSIS to parse the CSV and call a custom C# task?
- Don't use SSIS at all ?
Thanks
View 1 Replies
View Related
Aug 25, 2007
Hi Craig/Kamal,
I got your email address from your web cast. I really enjoyed the web cast and found it to be
very informative.
Our company is planning to use SSIS (VS 2005 / SQL Server 2005). I have a quick question
regarding the product. I have looked for the information on the web, but was not able to find
relevant information.
We are getting Source data from two of our client in the form of Excel Sheet. These Excel sheets
Are generated using reporting services. On examining the excel sheet, I found out that the name
Of the columns contain data itself, so the names are not static such as Jan 2007 Sales, Feb 2007 Sales etc etc.
And even the number of columns are not static. It depends upon the range of date selected by the user.
I wanted to know, if there is a way to import Excel sheet using Integration Services by defining the position
Of column, instead of column name and I am not sure if there is a way for me to import excel with dynamic
Number of columns.
Your help in this respect is highly appreciated!
Thanks,
Hi Anthony, I am glad the Web cast was helpful.
Kamal and I have both moved on to other teams in MSFT and I am a little rusty in that area, though in general dynamic numbers of columns in any format is always tricky. I am just assuming its not feasible for you to try and get the source for SSIS a little closer to home, e.g. rather than using Excel output from Reporting Services, use the same/some form of the query/data source that RS is using.
I suggest you post a question on the SSIS forum on MSDN and you should get some good answers.
http://forums.microsoft.com/msdn/showforum.aspx?forumid=80&siteid=1
http://forums.microsoft.com/msdn/showforum.aspx?forumid=80&siteid=1
Thanks
Craig Guyer
SQL Server Reporting Services
View 12 Replies
View Related
Feb 15, 2011
I have a scenario, need to create SQL server Tables dynamically.
I Have multiple xml data file on a particular location, and want to load those XML data into sql server tables, but he metadata of each xml data files are not same.
Hence the approach is that,
1. Pick first file from that location
2. Create a table according to that xml data file metada
3. Â load data on newly created table. Â
4. Pickup the next xml data files.
5. loop through, till the XML data files are exists on that location.
View 4 Replies
View Related
Apr 18, 2012
I have a stored procedures that will import 28 CSV files with different columns layout for each one, into their own table in SQL Server 2008 and a master stored procedure which will execute the other 28 store procedures.
All the CSV files are stored on the network drive in one folder.The naming of these files will vary from week to week and I don't want to manually change the names in the stored procedures. Each table name has a unique Identifier F1 to F28 that will be a constant before the rest of the name for example ...
F1_country23
F2_region12
Can I use the unique Identifier to add an wildcard or variable to the stored proc so it will pick any file in the folder for example with "F1" or "F2" meaning format layout 1 or 2 and use the correct stored procedure like below?
WHEN 'F1' THEN 'usp_F1_ImportScript'
WHEN 'F2' THEN 'usp_F2_ImportScript'
Example SQL scripts so far ...
Code:
Create PROC [dbo].[usp_F1_ImportScript]
-- Check if template table already exists and if it does then delete the table
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CSVTest]') AND type in (N'U'))
DROP TABLE dbo.CSVTest
-- Create a new template table
[Code] ....
the SP which will run the 28 SP
Code:
CREATE PROCEDURE [dbo].[usp_FilePicker] (@FileName VARCHAR(255))
AS
--check if @FileName is null
IF @FileName IS NULL
BEGIN
SELECT 'No Files Match' AS ERROR
[Code] .....
I cannot use SSIS because other users from different countries (50+ so far) may need to add/delete columns in the raw text/cvs files (for their own test environment) meaning there may be changes needed to made to the SQL scripts and with documentation any user without experience with MSSQL would be able to make simple changes.
View 1 Replies
View Related
May 23, 2008
Hello,
I have a requirement to create a reusable process that will be able to read a file and insert it into a table. The complication comes from the fact that the process will not just need to handle one file format, but files that will come in up to 20 different formats. (Some will be fixed width, some will be comma delimited, column names will be different.)
Since these formats may potentially change, such as a new column being added, the process needs to be dynamic enough to handle this without changing the package itself. While this task is somewhat daunting, I was hoping that SSIS might be able to handle it.
One thought that I had was to somehow have dynamic file connection, with the information about the file coming from a table. (I.e. The table would store values to specify the column name for a file type, the delimiter used, etc.) However, I don't know if this is something that SSIS is equipped to handle.
Any suggestions would be much appreciated.
Thanks,
Attila
View 3 Replies
View Related
Feb 16, 2007
With DTS it was easy to import a DBF file, but its a huge pain with SSIS. Is there any way on the horizon to import DBF files as easily as DTS did?
View 1 Replies
View Related
Jul 23, 2005
Hello,I need to import a bunch of .csv files. The problem I am having is the"non data" information in the files creating bogus rows and columndefinitions. Here is an example of the csv file.CBOT - End-of-Day Futures Bulk Download 2001.2 Year U.S. Treasury Notes FuturesDateSymbolMonth CodeYear CodeOpen20010103ZTH2001102.0937520010104ZTH2001102.0312520010105ZTH2001102.28125In this case, there are bogues rows created with the text at thebeginning of the file, and also the column names get placed into a rowas well. My question is; how do you import the file and strip out the"non-data" data? so that only the actual data gets inserted into the db?Thanks,TGru*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 4 Replies
View Related
Jul 20, 2005
What is the best way to import a structured .prt export file from anaccounting package into SQl Server??Tabs, Fixed col length???
View 1 Replies
View Related
Sep 14, 2006
I have the original copies of the .MDF and .LBF of another database that has a bunch of data in that I'm trying to important into MSSQL Express for a project I'm working on. I no longer have a running version of this database just the .MDF and .LBF files and I have no clue what version of MSSQL they were originally created with. If anyone knows a way to import this database please help.
View 1 Replies
View Related
May 2, 2007
I am trying to import a CSV file into an SQL Server table with the OleDbDataReader and SqlBulkCopy objects, like this: using (OleDbConnection dconn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\mystuff\;Extended Properties="text;HDR=No;FMT=Delimited""))
{
using (OleDbCommand dcmd = new OleDbCommand("select * from mytable.csv", dconn))
{
try
{
dconn.Open();
using (OleDbDataReader dreader = dcmd.ExecuteReader())
{
try
{
using (SqlConnection dconn2 = new SqlConnection(@"data source=MyDBServer;initial catalog=MyDB;user id=mydbid;password=mydbpwd"))
{
using (SqlBulkCopy bc = new SqlBulkCopy(dconn2))
{
try
{
dconn2.Open();
bc.DestinationTableName = "dbo.mytable";
bc.WriteToServer(dreader);
}
finally
{
dconn2.Close();
}
}
}
}
finally
{
dreader.Close();
}
}
}
finally
{
dconn.Close();
}
}
}
A couple of the columns for the destination table use a bit datatype. The CSV files uses the strings "1" and "0" to represent these.When I run this code, it throws this exception:Unhandled Exception: System.InvalidOperationException: The given value of type String from the data source cannot be converted to type bit of the specified target column. ---> System.FormatException: Failed to convert parameter value from a String to a Boolean. ---> System.FormatException: String was not recognized asa valid Boolean. at System.Boolean.Parse(String value) at System.String.System.IConvertible.ToBoolean(IFormatProvider provider) at System.Convert.ChangeType(Object value, Type conversionType, IFormatProvider provider) at System.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType destinationType) --- End of inner exception stack trace --- at System.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType destinationType) at System.Data.SqlClient.SqlBulkCopy.ConvertValue(Object value, _SqlMetaDatametadata) --- End of inner exception stack trace --- at System.Data.SqlClient.SqlBulkCopy.ConvertValue(Object value, _SqlMetaDatametadata) at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternal() at System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServer(Int32 columnCount) at System.Data.SqlClient.SqlBulkCopy.WriteToServer(IDataReader reader) at MyClass.Main()It appears not to accept "1" and "0" as valid strings to convert to booleans. The System.Convert.ToBoolean method appears to work the same way. Is there any way to change this behavior? I discovered if you change the "1" to "true" and "0" to "false" in the CSV file it will accept them.
View 3 Replies
View Related
Sep 14, 2007
Hi,
I got one more problem with importing csv files using .net. The problem is that the csv file contains double-quotation marks (""). For example, the record looks like:
...,Bearing Double "D" Flange,...
And the result is: ... | Bearing Double | null (all following columns are null)
The code is as following:
string strCsvConn = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=;Extended Properties='text;HDR=Yes;FMT=Delimited(,)';";
using (OleDbConnection cn = new OleDbConnection(strCsvConn))
{
string strSQL = "SELECT * FROM " + strFileName;
OleDbCommand cmd = new OleDbCommand(strSQL, cn);
cn.Open();
using (OleDbDataReader dr = cmd.ExecuteReader())
{while (dr.Read())
{
string str = Convert.ToString(dr[8]);
}
// Bulk Copy to SQL Server
//using (SqlBulkCopy bulkCopy = new SqlBulkCopy(strSqlConn))
//{
// bulkCopy.DestinationTableName = strSqlTable;
// bulkCopy.WriteToServer(dr);
//}
}
}
Any idea is highly appreciated.
shz
View 2 Replies
View Related
Apr 10, 2001
Hi,
I want to import multiple text files into a single table. I know I have to use BCP or DTS. But, I want import all files at once, instead of one at a time. And the file names are in sequence, viz. file1, file2, file3 etc.
Can anybody tell me, How I can achieve this.
Thanks
View 4 Replies
View Related
Jul 7, 2000
Has anyone written a SQL script to import ASCII comma or space delimited?
I am trying to load a PROGRESS exported file (Unix) into SQL SERVER 6.5 (NT).
Much appreciated,
Nickd
View 1 Replies
View Related
Sep 19, 2001
I have to import 18000 text files into a sql database.
Each file contains 10 fields and around 5000 records.
I am currently doing this with DTS.
What I am wondering is this: Is DTS the most efficient i.e. quickest way to import all this data. Bearing in mind there is about 90 million records to import in all.
I would appreciate the benefit of somebody elses experience when dealing with this type of thing.
Cheers,
Brookesy
View 2 Replies
View Related
May 10, 2004
Hello,
I've been trying to import a TXT file into an SQL database and I'm having trouble making it work correctly. It is a ASCII text file with over 100,000 records. The fields vary by the number of characters. This can be 2 characters up to 40 (STATE would be 2 characters, CITY is 32 characters, etc.)
I can import the file with DTS. I go in and select exactly where I want the field breaks to be. Then it imports everything as Characters with column headers of Col001, Col002, Col003, etc. My problem is that I don't want everything as Characters or Col001 etc. I want different column names and columns of data to be INT, NUMERIC(x,x), etc. instead of characters every time. If I change these values to anything than the default in DTS it won't import the data correctly.
Also, I have an SQL script that I wrote for a table where I can create the field lengths, data type etc. the way I want it to look, FWIW. This seems to be going nowhere fast.
What am I doing wrong? Should I be using something else than DTS?
Any suggestions are greatly appreciated.
Thanks,
JB
View 5 Replies
View Related
Apr 25, 2008
Hi i've 6 files with the same name part but all have a different ext. The 17 always changes to the current week number
xbouns.A17
xbouns.B17
xbouns.C17
I want to import these files into a database table. So I've create a foreach loop and select the foreach file enumerater but am not sure if this is the right way to go about it some help woth be great thanks.
View 7 Replies
View Related
Aug 5, 2005
I have over three hundred text files that I need to import to SQLServer.Each is in the exact same format.I want to import tham as seperate tables.Is there any way to do it in one process?Regards,Ciarán
View 5 Replies
View Related
Apr 28, 2008
Hi i've 6 files with the same name part but all have a different ext. The 17 always changes to the current week number
xbouns.A17
xbouns.B17
xbouns.C17
I'd like to do this within a SSIS package some help in getting this to work would be great.
Thanks
View 12 Replies
View Related
Jan 24, 2008
Hello Everyone.
I am a bit new to SQL Server but not to DBA or programming per se. I am having difficulties getting either an Excel or Text flat file to import properly.
I guess it would be best to ask, using either SSIS or BULK INSERT, what options need to be entered for a typical excel flat file?
View 2 Replies
View Related
Feb 28, 2006
I have a package with a corresponding configuration file.
If I open Sql server management studio and i connect to SSIS and right click to import a package, and then select to store the package in SQL Server (not on the file system)....
What happens with the configuration file?
Does the import take the values from the configuration file and place them in the package which then is stored in SQL Server?
Or do I need to put the configuration file someplace on the SQL Server where the package is imported so it can access it when it runs?
I'm a bit confused about what goes on there?
For example, I tried using the build command and then running the manifest file to import using the wizard and when it does that it copies the configuration files to a default location within the c:program filesmicrosoft sql server directory.
Thx.
R-
View 1 Replies
View Related
Jul 16, 2015
I am new to SSIS and C#. In SQL Server 2008 I am importing data from a .csv file. Now I have the columns dynamic. They can be around 22 columns (some times more or less). I created a staging table with 25 columns and import data into it. In essence each flat file that I import has different number of columns. They are all properly formatted only. My task is to import all the rows from a .csv flat file including the headers. I want to put this in a job so I can import multiple files into the table daily.
So inside a for each loop I have a data flow task within which I have a script component. I came up(research online) with the C# code below but I get error:Index was outside the bounds of the array.I tried to find the cause using MessageBox and I found it is reading the first line and the index is going outside the bounds of the array after the first line.
My File1Conn is the flat file connection instead I want to read it directly from a variable User::FileName
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.Windows.Forms;
using System.IO;
[code]....
View 8 Replies
View Related
Jun 2, 2008
I have a folder on an SFTP server in our internal network which gets flat files periodically (almost every minute) from another server. The file structure is like this:
Flat File Format•Line 1—List of field names comma separated •Line 2—List of field type comma separated •Line 3—Data comma separated •Line 4—Data comma separated and so on...
All the files will be like this without the .csv extention. There are a lot of fields and creating a column for each field manually will be a pain. Heres what I am trying to do:
1. Create a process that runs periodically on the server and imports data from files into the SQL server table.
2. Use the information in line 1 and 2 to create the schema of the table and then ignore those lines in every import (the data should be appended to the existing data).
What are my options?
Thank you all,Bullpit
View 3 Replies
View Related
Sep 20, 2004
Hi,
Can anyone help? Need to upload a text file to a sql database but keep getting errors.
I'm creating a page that will allow users to to bulk import and update to a MsSql database. The users provide a text file every so often with new/update information. So i want to use a DTS package to transform the infomation, and create a table in the database, then check against existing/non existing records, if the record exist, update it, if not insert it. I'm using Visual Studio.Net, ASP.Net and coding in VB.Net.
Anyone know where i can find documentation/code regarding the above?
I will be greatful for any help.
View 5 Replies
View Related
Jun 6, 2005
I have a load (180,000+) of text files whose contents need to go into a SQL server database.Whats the best way of doing this? Using a c# console program and if so, using FileStream or StreamReader? Or using a feature of SQL server itself. The text files themselves are less that 1k and are literally less than 200 characters.The problem is, I've tried a WinForm and although I can detect what files are there, as soon as I attempt to open one for reading, everything stops working and won't insert anything to the database.
View 1 Replies
View Related
Oct 27, 2006
I have a client who is sending me 800+ excel files each month with sales data. Each of the files is identical in structure, but has sales data for different stores. I receive all these files at the same time.
Is there a method with Data Transformation Services where I can have it work off of all the files in a given directory. I can set up DTS to work off of specific Excel files with no problem, but what I would like to do is set up a DTS so it could pull from each of the 800+ files.
Is this possible, or do I need to look at a solution outside of SQL to consolidate the Excel files first?
The Excel file would have columns similar to the following: store_id, zip_code, sales, transactions.
View 4 Replies
View Related
Dec 30, 2003
I want to write a DTS that will import a file every day. The problem is that the files is not named the same thing every day. There is a naming convention (SOMMDDYY.TRN) that it will follow. I want to import this file (which is a fixed width file) each day to a table (The table will be empty each day).
After it is imported, I want to look at the NAME of the file, and pull out the date portion of it. So, if the file is called SO122603.TRN, i want to pull out 122603, and then update my table with that date for every record. So when I am done, I will have a table that represents the file I imported, with one added column. This added column would be a Date/Time that has the date that was in the filename. How do I do this???
View 11 Replies
View Related
Jul 23, 2005
HI!I am importing .txt files. How can i check the errors? I have created alog file, but the problem is that i lose some characters.I import for example:CodeABCFZHJHNfrom a text file, but sometimes Code can be 4 caracters longI import this 3 characters long now. When i add the same structuredtext file with some rows lenght 4, it skips the last character, but iget nothing in the log file.please helpxgirl
View 4 Replies
View Related
Oct 31, 2006
Hi allCould someone help me with the following problem? Hours of googlingyesterday couldn't get me the answer. I'm using SQL 2000 and DTS andtrying to import a huge fixed width text file.File is >1m rows and >200 columns and is defined by a proprietory (i.e. notbcp produced) format specification of the formName Start LengthFld1 0 20Fld2 19 5Fld3 24 53and so on.Tbe only way I've found to define the columns so that DTS can import thefile properly is to go through the wizard and click on the starts of eachcolumn. I don't want to use bcp if possible (I did enough of that onSQL7) - but surely there's a way to get DTS to read from a format file so Idon't have to click 200 times (with all the ensuing errors I could make).Any help greatly appreciated.CheersRob
View 2 Replies
View Related
Feb 14, 2008
HiI have multiple excel files of the same format in a directory.They are called book1.xls, book2.xls, book3.xls and so on.What is the easiest way to import the tab named sheet1 from each ofthe excel files to a databse using SQL server 2000 enterprise edition?Regards,Ciarán
View 2 Replies
View Related
Aug 1, 2006
I'm trying to input a few thousand flat files into a few thousand tables in a sql databaseim using integration services with a for each loop to read all the files in a directorythe problem is i can only insert the data from all the files into one tabledoes anyone know a way to do multiple tables? maybe using some sort of variable?
View 1 Replies
View Related
Jul 19, 2007
My company is running Livestats.XSP v8. We have been trying to import log files for a website for a few days now but Livestats keeps getting stuck randomly throughout the import process. What i mean by this is that i will import all of the log files by date which have been imported, then its stop and displays "Not Imported....."
We really need to get this remedied as soon as possible and it seems that support for Livestats is slim. Does anyone any idea that could help me? Thanks in advance.
View 1 Replies
View Related
Aug 2, 2006
I'm trying to input a few thousand flat files into a few thousand tables in a sql database, using SQL Server Business Intelligence Development Studio.
im using a for each loop to read all the files in a directory
the problem is i can only insert the data from all the files into one table
does anyone know a way to do multiple tables? maybe using some sort of variable?
View 1 Replies
View Related
