Integration Services :: Importing A File With Dynamic Columns
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
ADVERTISEMENT
Aug 31, 2015
I will be receiving a CSV daily where columns within the file will change. The column order and number of columns can change daily. I need a way to read in the header from the csv and create a flat file connection that reflects the columns listed in the header.
Is there an easy way to do this using a script task? I have already read the header into a table but I have been unable to create the dynamic file connection.
View 4 Replies
View Related
Sep 11, 2015
We have a requirement to produce adhoc Excel reports with a standardized header page with a disclaimer attached. We want to be able to feed in a SQL Statement, or a table with the resultset from a SQL Statement and have SSIS populate an existing blank Excel workbook, which the disclaimer attached. The use of xp_cmdshell is not an option.I've spent a lot of time looking for solutions on the web and it seems though its not possible - although many articles are 3-5 years old. Before I throw in the towel, I just wanted to get feedback from this group if it still is not possible in the latest versions of SQLServer and SSIS, or to ask if there are any other 3rd party solutions that can do this today.
View 5 Replies
View Related
Sep 24, 2015
'm getting the following error when trying to import an Excel file into SQL..I'm using SQL Server 2014 Express
- Validating (Error)
Messages
Error 0xc00470b6: Data Flow Task 1: The LocaleID 0 is not installed on this system. (SQL Server Import and Export Wizard)
Error 0xc004706b: Data Flow Task 1: "Source - Sheet1$" failed validation and returned validation status "VS_ISBROKEN". (SQL Server Import and Export Wizard)
Error 0xc004700c: Data Flow Task 1: One or more component failed validation. (SQL Server Import and Export Wizard)
Error 0xc0024107: Data Flow Task 1: There were errors during task validation. (SQL Server Import and Export Wizard).
View 4 Replies
View Related
Nov 2, 2015
We have a 2014 SQL Server. I have a SSIS package written in VS 2008 where I am simply importing an .xlsx into an existing table via a mapped drive. I have it working on my development machine using the 2007 Access 32 bit driver from [URL]..... Our DBA is trying to schedule the package to execute on a schedule job on the 2014 server and we received an error. He installed the 32 bit driver and still getting the error. I set the package to run in 32 bit and we are still getting the error.
Date 10/30/2015 2:51:18 PM
Log Job History (BD_ISS_Websites_New1)
Step ID 1
Server ETSSQL2014DEV
Job Name BD_ISS_Websites_New1
Step Name ISSWebsite
Duration 00:00:01
[code]....
View 3 Replies
View Related
Jul 24, 2015
Need to know how I can get the dynamic filename created in the FlatFile destination for insert into a package audit table?
Scenario: Have created a package that successfully outputs Dynamiclly named flat files { Format: C:Test’Comms_File_’ + ‘User::FileNumber’+’_’+Date +’.txt’
E.g.: Comms_File_1_20150724.txt, Comms_File_2_20150724.txt etc} using Foreach Loop Container :
* Enumerator Set to: “Foreach ADO Enumerator” with the ADO object source variable selected to identify how many total loop iterations there are i.e. Let’s say 4 thus 4 files to be created
*Variable Mappings : added the User::FileNumber – indicates which file number current loop iteration is i.e. 1,2,3,4
For the DataFlow task have a OLDBSource and a FlatFile Destination where Flat File ConnectionString is set up as:
@[User::Output_Path] + "Comms_File"+ @[User:: FileNumber] +"_" + replace((DT_WSTR, 10) (DT_DBDATE) GETDATE(),"-","")+ ".txt"
All this successfully creates these 4 files:
Comms_File_1_20150724.txt, Comms_File_2_20150724.txt, Comms_File_3_20150724.txt, Comms_File_4_20150724.txt
Now the QUESTION is how do I get these filenames as I need to insert them into a DB Audittable. The audit table looks like this:
CREATE TABLE dbo.MMMAudit
(
AuditID INT IDENTITY(1, 1) NOT NULL,
PackageName VARCHAR(100) NULL,
FileName VARCHAR(100) NULL,
LoadTime DATETIME NULL,
NumberofRecords INT NULL
)
To save the Filename & how many records in each file in our Audit Table, am using an Execute SQL Task and configuring it as this:
Execute SQL Task
Parameter mapping - Mapped the User Variable (RecordsInserted) and System Variable( PackageName) to Insert statement as shown below
SQLStatement: INSERT INTO [dbo].[MMMAudit] (
PackageName,NumerofRecords,LoadTime)
(?,?.GETDATE)
Again this all works terrific & populates the dbo.MMMAudit table as shown below BUT I also need to insert the respsctive file name – How do I do that?
AuditID PackageName FileName NumberOfRecords
1 MMM NULL 12
2 MMM NULL 23
3 MMM NULL 14
4 MMM NULL 1
View 2 Replies
View Related
Aug 3, 2015
I'm importing comma-delimited text files into a SQL table. The data imports in a seemingly random order. One time I import and the lines appear one way and the next time I import they import another way.
Is there a way to force the text files to import in the same order the data is found in the file?
View 10 Replies
View Related
Jun 11, 2015
I have a requirement to load bulk of csv files to sql table. some times, some columns could not come in csv file(some times 100 columns and some times 80 cloumns). That time the package is getting failed. How to create a table dynamically based on csv file structure.
View 8 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
May 28, 2009
I have a requirement wherein I have to setup the flat file connection manager to accept columns on fly. Meaning I want to retrieve list of columns/column count from the database when the package is run and set the connection manager with those many columns.
View 6 Replies
View Related
Oct 21, 2015
I have one requirements below..
Table input
Eno ename Eloc
Edept
1 Sid
Pune 101,201,301,401,501,601
Output:
Eno ename Eloc
Edept
1 Sid
Pune 101
1 Sid
Pune 201
1 Sid
Pune 301
1 Sid
Pune 401
1 Sid
Pune 501
1 Sid
Pune 601
View 5 Replies
View Related
Apr 2, 2014
I have a situation where I want to load the Excel file dynamically, and the excel file have different columns or even worksheet name. How I could approach this? I believe there's no way to modify the meta data (specifically the mapping) in the data flow.
View 6 Replies
View Related
Jun 16, 2015
Here is a requirement. Need to update the columns in the tables with the latest values available in CSV.
The file is based on department so the list of tables which is under this department all the corresponding tables needs to updated.
The CSV file which is dynamic in nature the number of columns changes it has header of the columns that needs to be updated.
The destination tables are listed under department table for each department. So I have to update the columns in the tables with the values in csv.
View 4 Replies
View Related
Nov 6, 2015
I'm loading data from a sql server table into a flat file. The flat file connection manager has the following settings
GENERAL:
Format:Delimited
Text Qualifier:"
Header row delimiter: {CR}{LF}
Header rows to skip : 0
Columns:
Row Delimiter: {CR}{LF}
Column delimiter: comma(,)
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
Nov 7, 2015
I am going to set up a new SSIS package that will import data into 5 different tables on a SQL Server database. The source of the data is on another SQL Server and I will use to select the data. If one of the tables fail to import I do not want the SSIS package to import any of the data.What is the best way to create this package? Is it best to create one SSIS package, with five data flow tasks that are linked to each other. Within each data flow task, is a Source and Destination to transfer the data to each table.
View 3 Replies
View Related
Dec 18, 2013
When I import a pecentage from Excel to Access it either showas as 100% or 0% in Access. How do I bring in a percentage such as 92.5%?
View 3 Replies
View Related
May 25, 2015
If I have an XML without an XSD what is the best way to create and import data in SQL Server? I know I can use xsd.exe to create an XSD from my XML.
But if I want my structure to be somewhat different in SQL server how would I go about creating a reliable and repeatable import system for my data so i can easily manage the data updates?
View 3 Replies
View Related
Jun 3, 2015
I have several regular reports that are produced by different offices that I need to import the data from. The challenge lies in the fact that the reports are not simply columns of data. Some cells are labels, others contain data, and some contain both. Also, the formatting of the reports isn't strictly uniform from office to office. Is it possible to read this kind of sheet? Excel data sources seem to just define everything in a column as data, and that doesn't work for me. Is there an alternative, or perhaps a more manual way of defining what cells contain data?
View 10 Replies
View Related
Aug 15, 2007
I'm importing a fixed width text file into SQL using DTS. The txt file is all data with no headers. I need to specify specific column names, because my next step is going to to be moving this data into multiple established SQL Tables.
Can I specify column names in DTS? It seems to always force me to name the columns Col001, Col001, etc.
Thanks
View 2 Replies
View Related
Aug 4, 2015
how to import data from word document into sql server table in SSIS.
View 18 Replies
View Related
Apr 27, 2006
I need to report on data from several databases on several servers. They are all SQL Server 2005 databases. I am trying to created an Integration Services task to consolidate and transform this data for easy reporting. The problem I am having is one database in particular. It has tables like this:
tblLookupData_Customer1
tblLookupParseData_Customer1
tblLookupData_Customer2
tblLookupParseData_Customer2
tblLookupData_Customer3
tblLookupParseData_Customer3
I want to use only the tables of the form "tblLookupParseData*" for this list. I can do this in Stored Procedures by dynmacally building up the sql query. I cannot find out how to do this in Integration Services. When I make Datasource Views, they seem to expect me to pick from a list of known tables. This list of tables grows as Customers are added to the system.
NOTE: The way the tables are structured was NOT my idea. I hate storing "data" in the structure of the database. Many people also do this when they create "period" tables such as "CustomerData_05_2005". It speeds up writing the data, and querying a specific table, but it is a nightmare for reporting. I cannot change this as it is not in my responsibility.
View 1 Replies
View Related
Jul 31, 2015
I have a reqirement where in i need to create a Database dynamically in SSIS, Database name is given in table.
View 5 Replies
View Related
Aug 6, 2015
I have the variable @Monthname defined as shown below:
(MONTH(getdate()) == 1 ? "January" : MONTH(getdate()) == 2 ? "February" : MONTH(getdate()) == 3 ? "March" :
MONTH(getdate()) == 4 ? "April" : MONTH(getdate()) == 5 ? "May" : MONTH(getdate()) == 6 ? "June" :
MONTH(getdate()) == 7 ? "July" : MONTH(getdate()) == 8 ? "August" : MONTH(getdate()) == 9 ? "September" :
MONTH(getdate()) == 10 ? "October" : MONTH(getdate()) == 11 ? "November" : MONTH(getdate()) == 12? "December":"")
By itself, it is working fine. I am attempting to have the @SubjectLine variable display the below:
CA - TFL for the month of August 2015
I am using the expression below in the expression builder:
"CA - TFL for the month of " + @[User::MonthName] + (DT_WSTR,4)YEAR(GETDATE())
When evaluating the expression, I get the below (month is missing):
CA - TFL for the month of 2015
View 2 Replies
View Related
Dec 31, 2013
I can set almost all properties of FTP task and FTP connection manager using expressions option. In that option, I don't see that I can set FTP PASSWORD using a variable. How do I set the password dynamically?
View 2 Replies
View Related
Nov 3, 2015
In my package I am using lookup to get new and similar record. I want to filter the rows for Lookup Reference Data Set by using Variable Value.
I have created variable @[User::CustId] with Int32 datatype, having default value 2 when I am trying to evaluate below query I am getting error
"select CustId,PartNm,LocId,LocTyp from loc where CustId= "+ @[User::CustId]
Error. The Data types "DT_WSTR" and "DT_I4" are incompatible for binary operator "+".
The operand types could not be implicitly cast into compatible types for the operation. To perform this operation , one or both operands need to be explicitly cast with the operator.
View 2 Replies
View Related
Nov 2, 2015
I have some source files is there today it will have 4 columns..Tomorrow it will have 10 columns...my package is dynamically load the data to destination table..How we have do it in Using script task...
View 4 Replies
View Related
Jun 1, 2015
I have a requirement to take xml file, in case the number of column changes, it should not fail the package, rather it should load the data in destination table. Destination table could be altered separately depending on xml schema by the DB team in production.
View 3 Replies
View Related
May 17, 2005
Hi all!
If I have some mdx I'm using in reporting services like this:
select
{ [TimeByMinute].[All TimeByMinute].[2005].[May].[1] : [TimeByMinute].[All TimeByMinute].[2005].[May].[6] } on columns,
{A_list_of_measures } on rows
from ACD_Calls
The column names are unique to the day of month- which means when I use a table to display this in reporting services, the field names change dynamically when the date parameters change which means the table stops working.
I'll post this in reporting services too but I thought maybe I could alias the column names in mdx shielding the reporting services table from changes in dates.
What do you think? Would a matrix be more flexible in this case?
Richard
View 7 Replies
View Related
Apr 22, 2015
I am using sql 2012 SSRS.
My requirement : I have Database which has got 100 tables.I need to create SSRS report which display top 10 records from the table within that database.
User will select The table name as parameter in the report. the number of column in each table will vary .
I am using the below statement in stored proc, and calling same from ssrs report
exec(
'select top 10 * from ['+@datbasename+'] .[app].'+@tablename+'')
View 6 Replies
View Related
Jul 30, 2015
I am in between of creating a dynamic SSSIS package which will run for multiple zones having different source connection.My source is in Oracle.I am having 3 DFT with the 3 different source tables.I want to create a package with above DFT dynamically so that my single package can run for the entire zone with dynamically source connection change.I have created a Master table which stores the zone source connection string and zone name. I have 2 different connection.so if in future any new zones come so only newly zone details need to be add in master table without opening the package.
View 3 Replies
View Related
Sep 4, 2015
I want to export the data into multiple sheets with same template, all the worksheets have to split dynamically with specific Sheet Name and template also copied to all other sheets
For Example:
Sheet Name: Guru
Name Age
Guru 24
Sheet Name: Johnson
Name Age
Johnson 32
it goes on......
View 5 Replies
View Related