Use Of SSIS In Reading And XSL File. Should I Use SSIS Or Just Straight C#
Aug 1, 2007
I need to build an asp.net/C# application to read values from an Excel spreadsheet. Once the values are read from the spreadsheet, the C# code will do some elementary statistics on the values read. Then the values read and their computations will be written to a sql server database.
My manager suggested that SSIS might be a good candidate technology for doing this type of work. Does that sound correct? My only hesitation with using SSIS is that I want to keep the application as simple as possible, so that the code can be more portable. Maybe might argument is not a good one, but maybe someone can help me out here.
I am trying to create and later read a data file from a package deployed in SSISDB, but it is not reading it while it is successfully creating the file. The same package when run from the file system package, runs successfully. Generating ispac and deploying in SSISDB is running for infinite time. Is it a permission issue?
I have a requirement where I should be reading the file (I will use File system Task for this) and the header content. I should be able to read the header, validate and when the validation succeeds , I should start process the rest of the file.
To elaborate this further, take a sample Example. Assume that the header will have the date information. In a given folder I should read the file, will check the date (header being the first line of the file). If it matches the current date, I will start processing and on completion I will archive my files.
I have a simple SSIS package -> It reads a local text file which has 10 rows of data ( id, name, telephone # ) and puts it into a table.
It uses the "SSIS Flat File source" to read and a "SQL Command" to insert into the table. I can see that it reads line by line and puts each line into one row in my table.
Now, my production data is over 5 GiG of mainframe data and it seems their data is arranged in some hierarchical form.. so the position or arrangement of data in that file is important.
I pulled the data using my package and as far as I can see , my SSIS package pulled one line at a time ( from the flat file) and pushed it into my table. For each row, I also created an identity column in my table to be able to identify the positional arrangement of the hierarchical data and then use relational mappings to suit our business needs.
In all of this, my assumption is -
"SSIS reads one line at a time, inserts to my table and goes down to the next line .
It does NOT read a snapshot of rows from the flat file so as to write them into the table using internal ordering methods based on that particular snapshot "
I have an excel file which i am reading through SSIS through MICROSOFT Office 12.0 Access Database Engine OLE DB Provider. My problem is that if the Excel file has data in a column as 123.45 which is displayed as 123. SSIS is able to pick 123 only and not 123.45. however if I change the data type to Numeric in the excel, SSIS is able to read data as 123.45.Any solution other than changing data type in Excel?
Hi. I need to import excel file in database. i first need to do an unpivot task. the column names are dates and SSIS seems to be unable to pick up the column name as it is replaced by F2 F3 F4etc Can you advise of a solution. thanks ken
I'm trying this code in a script task inside a foreach loop that gets a file in a foreach file enumerator and maps it to a variable FileName. In the script task I'm setting the ReadOnlyVariables to @[User::FileName] : Imports System.IO Imports System Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Runtime Public Class ScriptMain Public Sub Main() Dim fname As String = CType(Dts.Variables("FileName").Value, String) File.Move(fname, fname + ".processed") Dts.TaskResult = Dts.Results.Success End Sub End Class But now getting this error: Error: Failed to lock variable "\xxxxDataf1.csv" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.".
As you can see the variable does appear to be resolving.
I have to read a database column in my VB script which basically contains XML data. My requirement is to read that column and then retrieve couple of nodes within that dataset and then based on those values ....i have to execute a procedure....
Can anyone please let me know how to traverse a XML document in VB Script
I have a question regarding ASP.NET 2.0 reading available packages (calling "GetDtsServerPackageInfos" on the "Application"-Object which returns "Microsoft.SqlServer.Dts.Runtime.PackageInfos") from a remote SSIS server (not running them). It seems to me that I am experiencing either a security/delegation issue or that I am trying to do something that is not possible...
The setup is the following.
I have two machines, A and B. A runs IIS 6/ASP.NET 2.0 and B runs SQL Server and SSIS. They belong to the same domain. IIS runs the application pool with the webapplication in question under the "Network Service" account and A is trusted for delegation to B. At the moment its trusted when using Kerberos and for MSSQLSrvc and rpcss; but I tried "all" and many more as well.
I configured IE to use Windows Authentication and set the URL to belog to the Intranet (to enable kerberos authentication).
My ASP.NET webapplication now tries to read (in the sense explained above) the available packages on the SSIS machine (B). The packages are stored on the filesystem, not in mdsb. But I feel that this does not matter, since ASP.NET is accessing them through "the SSIS service" which can access packages in msdb or/and on the filesystem?
What happens now is that
a) everything works, when I fire up IE on A and access the webapplication. The user I have on A is a local admin. b) I get "E_ACCESSDENIED" when I start IE as regular User on A c) I get "E_ACCESSDENIED" when I access the webapplication from any other machine than A (IE6 or IE7)
My main question is: is it possible to do what I want to do? The other question is, why does everything work when I access the webapplication from A, but not from any other machine? The second thing makes me think it could be a delegation issue (double hop and SSIS don't seem to go togethe too well). But still, I could not find any resource telling me that this is impossible And frankly, I do not fully understand delegation and/or Kerberos.
I would greatly appreciate if anybody had a pointer or something for me. Regards scherand
Btw: Thanks to Caddre for his (her?) first help and pointing me to this forum.
Dear Friends, I store several configurations in the main database of my SSIS packages. I need to get the servername from a xml or txt file in order to get those configurations stored in my database. How you think is the better way to do that? Using a FlatFileSource to read the file and a script to save the value into a SSIS variable? Using the package configuration I cant do that... maybe I dont know, but I can save the SSIS variale in the configuration file, but what I need is to do the inverse, read the configuration file and save the value in the SSIS variable. How the best way you suggest?! Regards!! Thanks.
I have created an ssis package, am using Script task to read all the cell values and comments.
In server MS Excel is not installed, so we use regsvr32 to register excel.dll ( also tried with Microsoft.Office.Tools.Excel.dll ), while doing the registration we got error as
"Registration not done, enrty point not found"
Sice the registration is not done we where unable to create the excel object in our script task.
Can anybody give as any clue, all helps are welcome.
I used the below code to read excel files in SSIS 2008R2 script component and it is working fine but when i copied it in Script Task of SSIS 2012, the code doesnt work. I have define one variable
Var_ExcelFileName and stored location of excel file.
/* Microsoft SQL Server Integration Services Script Component * Write scripts using Microsoft Visual C# 2008. * ScriptMain is the entry point class of the script.*/
using System; using System.Data; using Microsoft.SqlServer.Dts.Pipeline.Wrapper; using Microsoft.SqlServer.Dts.Runtime.Wrapper;
[Code] ....
I am getting errors in the below lines:
using Microsoft.SqlServer.Dts.Pipeline.Wrapper; using Microsoft.SqlServer.Dts.Runtime.Wrapper;
I have to transform 500 columns from an excel sheet to Sql Server. In Excel 2k3 , I can read a max of 256 columns only.If I use Excel 2k7, then SSIS 2k5 excel source does not support excel 2k7. If I use ole db source then again it can read a max of 256 columns.how can we read 500 columns in excel sheet (Around 10000 rows) efficiently using SSIS 2k5.
I need to read in general web pages (not a web service) from a typical web site using SSIS and make it available for other SSIS transformations (Script Component). I tried using the XMLSource data source but this appears to require well formed XML, and will not accept HTML which is what I am likely to be getting from the web pages. I tried a HTTP Connection Manager with a DataReader Source, but seems to only accomodate web services.
Can this be done? If someone has an example (tutorial) of how to accomplish this I would greatly appreciate a copy. James
Hello Experts, I am createing one task (user control) in SSIS. I have property grid in my GUI and 2 buttons (OK & Cancle). PropertyGrid has Properties like SourceConnection, OutputConnection etc....right now I am able to populate Connections in list box next to Source and Output Property.
Now my question to you guys is depending on Source Connection it should read that text file associated with connection manager. After validation it should pick header (first line of text file bases on record type) and write it into new file when task is executed. I have following code for your reference. Please let me know I am going in right direction or not.. What should go here ? ->Under Class A
In a foreachloop, I am inserting records into a flat file which is working fine. But the thing is that as the file grows, it takes longer for it to locate the EOF(End of File) of the flat file so as to insert the records.
I have around 70-100 lines written to the file at each loop and there are more than 20k records to be looped. wihich means that at the end I should be having 1400k - 20000k line in the text file.
One solution would be to insert the records at the start of the file itself so that it does not has to lookup the EOF each time before writting.
Another would be to generate separate files and then merge it.
Any idea how can this can be done?
Beside this I have to zip the file and then SFTP to a given address.
Historically I've always written a VB script to copy a file from a sharepoint library. I don't like this method because I have to input a username & password in the script and maintain a config file.
Yesterday I was playing around with using a file system task. The sharepoint file has a UNC path so why not? I created a simple test package with a single file system task that copies the sharepoint file (addressed via UNC) to another network location. Package runs fine locally.
I try running on our utility server but am getting a "The file name [SHAREPOINT UNC PATH] specified in the connection was not valid" error. Package is running with a proxy on the server and the proxy account has the same permissions to the sharepoint site (so far as I can tell) as me.
Can we keep a database connection information in .ds (datasource) files. Is it possible to use these instead of the standard approach of putting the connection string in the configuration file.
Furthermore, when they run the SSIS package from the command line (using DTEXEC), is it possible to pass the .ds file as a parameter?
I am using an XML source component to integrate it into an SQL server data base.My problem is that when I try to open my data flow containing the XML source component, it take much time to open it, so I don't know why beacause I have a very good machine with 2GO of RAM. is it a problem of SSIS with XML files?
I want to rename a file that is a variable to a fixed file name in a specific folder in SQL Server System Integration Services using the File System Task Editor. Example: File1.txt to Users.txt or File2 to Users.txt. The source file is a variable the destination file is not. Here is an example of the File System Task EditorHOW DO I SPECIFY A FIXED OR VARIABLE FOLDER NAME WITH A SPECIFIC FILE NAME?????????File System Task EditorProperties ValueDestination ConnectionIsDestinationPathVariable TrueDestinationVariable User::ArchivePathOverWriteDestination TrueGeneralName Rename a fileDescription File System TaskOperationOperation Rename fileSource ConnectionIsSourcePathVariable TrueSourceVariable User::ImportFilePath Thanks
We are deploying our SSIS packages into different folders. For example: Test1 est2 production
Test1 points to the Test1 database, etc.
So, I configured my SSIS package to use a database connection called dbMAIN.
I then setup the SSIS File Configuration (XML) so that "dbMain" points to Test1. This xml file, called Global.dtsConfig, sits in C: est1, the same place as my ssis.dtsx file.
I want to copy my dtsx file and my dtsconfig file into est2 and production. The problem is that the the location of dtsconfig is HARDCODED in the ssis package!
How do I dynamically change it?
I tried to hardcode the location to ".Global.dtsconfig", but that did not work! Please advise!
have a URL that I get from a partner by calling another URL and feeding it some query parameters. That call returns to me a URL which is what I need to then go get the file I'm downloading. I have no problem getting that URL into a variable in my SSIS package. URL....there are several key-value pairs after that question mark which denote the login information, the report ID that it's picking up, etc.
If I manually take that URL and dump it into IE and hit enter, I get the standard "Open/Save/Cancel" prompt. If I open it, I see the XML that should be in my file in the browser. If I save it, I get the standard save dialoge box so that I can save the xml file.How do I do this in SSIS? I can create a file connection for where I want this XML file to be saved, but I don't see an option in the XML task to simply save the file from a URL.
I have a very simple SSIS package that executes a .bat file. Here's the actual file it executes:
@Echo Off c: F: cd ReportingServicesScripts rs -i NoteBlankSnapshots.rss -S http://10.90.160.13/ReportServerTest EXIT
The .bat file executes a Reporting Services .rss script using the rs.exe utility. When I run the command from the http://10.90.160.13/ReportServerTest EXIT
The .bat file executes a Reporting Services .rss script using the rs.exe utility. When I run the command from the command prompt, it runs just fine. When I execute the .bat file manually, it runs and completes. When I execute the actual package, it also completes. But when I schedule the package as a job, it just hangs...it never finishes.
The owner of the job is an administrator in SQL Server. I have the SQL Server Agent configured to interact with the desktop - although my .bat file requires no input from the user.
I've created other jobs that just execute plain old SQL using the same owneer and these jobs complete just fine.
What am I missing?
Any help would be appreciated!
Thanks!! __________________ Anthony Robinson
"If I'm curt with you, it's because time is a factor here. I think fast, I talk fast, and I need you guys to act fast if you want to get out of this. So, pretty please - with sugar on top..."
I can use MS Access to import an XML file and it builds the columns and loads the data. I am trying to use SQL Server 2005 to do the same thing and there doesn't seen to be a way to easily accomplish the same task that works so well in Access.
Has anyone tried this? If so and you were successful, what were the steps to set up the package?
