Can I Use And Excel Doc Saved In An SQL Server Table In An Image Type As A Source For An SSIS Script?
Aug 10, 2007
Hi Folks,
My situation is that Excel files are to be downloaded into a SQL Server 2005 table (perhaps as type image or nvarchar), which serves as a document repository. From there, they should be converted to XML. Use of an NT file directory is strongly discouraged. I would like to have SSIS read the Excel from one field in a table and then write the XML into another field in the same (or perhaps another) table. Is this possible? If not, is the a strait-forward way to do this?
Also, I€™m hoping to invoke the SSIS script from a SQL Server INSERT trigger so the conversion is done during the INSERT.
Hi all, I am not sure this is the right place to post this, but I could find a better place. I uploaded a document in the SQL Server (image datatype), but I was not able to save file Type since there is no field for that, and cannot change the database. Now I download the saved file. Is there any way to extract file Type from the saved file (like from file header?) and open it with an appropriate program such as doc file with MS Word? Thanks, J
I am creating an SSIS package witha a Dataflow task, which reads from an Excel source and then uses script component to dumpt the data to multiple tables in Sql Server database
I need to some how make my Excel source dynamic, that is my excel template which i would be using to map the excel columns to script component's input columns would be dynamic..
In other words, I should be able to define the Excel Source, Column Mapping Information, Precedence constraint to the Script component dynamically
I have a package that reads a table for a file path of a excel file. This gets passed to a variable and then this file is imported into a staging table for further transformation work. The issue i have is that file 1 may contain data in Column A which is 50 characters long in which case i have to import the excel as a DT_WSTR, do a data conversion to a DT_STR and load to the staging table. However file 2 may contain data in Column which is over 255 characters in which case it would import as a DT_NTEXT which i then transform to a DT_TEXT and then to a DT_STR. I used a fixed file path in the Excel Connection to start with which was for File 1 so the datatype for column 1 is a DT_WSTR. I then changed the excel connection to a filepath variable, put the path of file 2 in my table and called it from my package. It failed as the data exceeding 255 characters in column 1 needed to be a DT_NTEXT. I can change it and it works but if i then run the package using file 1 (less than 255 characters) it fails again as it wants it to be a DT_WSTR.
Is there anyway around this? Am i missing something as i would have thought that by setting it to DT_NTEXT this would cover data under 255 characters as well.
I have a report that is scheduled to run a once a week. This works fine. But now I would like this report to be saved as an Excel file automatically when it runs. how / where do I do this?
Hello everyone. I have an interesting problem. We have a SQL database with a field that is an image type. In it are records where the image field are text files. What I would like to do is look at these files and stream them line by line and do some processes for each line read. So let's say a client machine uploaded a file called myfile.txt into this database. I would like the asp.net (vb) application tkae this file and read each line and do some processing. I looked at memorystream and streamreader but just can not figure this out. Can you please help? Thank you Kameron
Is there a way to change an image data type? I want to make a change to some deployed SQL 2008 SSIS deployed packages. I have a TSQL SELECT that searches the packages for a string. But I would like to be able to change a string. I have googled it but cannot find anything.
Hi, I've a column col1 of image data type in table1. I would like to copy the data from col1 to another image column col2 in table2. Before moving the value, checking has to be done to specify which col1 data from table1 is needed and the destination has to be checked too.
Example: insert into col2 (select col1 from table1 where table1_id =5) where table2_id =6
Hence bcp wouldn't work. Can anyone suggest me a way to do it. I tried using writetext but then, i've to get data from col1 in a variable, which is not possible. Any suggestions would be very helpful.
When i click upload image button when my database table has no any row, the selected image is saved(one row saved in table). If i continue and select a different image, i get no error sa if the image has been saved but when i view the images i have been saving, its strange even if i saved 10 records they all contain the first image that i saved. In short only the first image is saved the rest of the rows are just duplicates of the first row. so it basically becomes a table of ten rows but with same data rows(same image). Code is below. Protected Sub btnupload_Click(ByVal sender As Object, ByVal e As System.EventArgs) Dim intLength As Integer Dim arrContent As Byte() If FileUpload.PostedFile Is Nothing Then Lblstatus.Text = "No file specified." Exit Sub Else Dim fileName As String = FileUpload.PostedFile.FileName Dim ext As String = fileName.Substring(fileName.LastIndexOf(".")) ext = ext.ToLower Dim imgType = FileUpload.PostedFile.ContentType If ext = ".jpg" Then ElseIf ext = ".bmp" Then ElseIf ext = ".gif" Then ElseIf ext = "jpg" Then ElseIf ext = "bmp" Then ElseIf ext = "gif" Then Else Lblstatus.Text = "Only gif, bmp, or jpg format files supported." Exit Sub End If intLength = Convert.ToInt32(FileUpload.PostedFile.InputStream.Length) ReDim arrContent(intLength) FileUpload.PostedFile.InputStream.Read(arrContent, 0, intLength) If Doc2SQLServer(txtTitle.Text.Trim, arrContent, intLength, imgType) = True Then Lblstatus.Text = "Image uploaded successfully." Else Lblstatus.Text = "An error occured while uploading Image... Please try again." End If End If End Sub Protected Function Doc2SQLServer(ByVal title As String, ByVal Content As Byte(), ByVal Length As Integer, ByVal strType As String) As Boolean Try Dim cnn As Data.SqlClient.SqlConnection Dim cmd As Data.SqlClient.SqlCommand Dim param As Data.SqlClient.SqlParameter Dim strSQL As String strSQL = "Insert Into Images(imgData,imgTitle,imgType,imgLength,incident_id) Values(@content,@title,@type,@length,@incident_id)" Dim connString As String = "Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|safetydata.mdf;Integrated Security=True;User Instance=True" cnn = New Data.SqlClient.SqlConnection(connString) cmd = New Data.SqlClient.SqlCommand(strSQL, cnn) param = New Data.SqlClient.SqlParameter("@content", Data.SqlDbType.Image) param.Value = Content 'cmd.Parameters.AddWithValue(param) cmd.Parameters.AddWithValue("@content", Content)
param = New Data.SqlClient.SqlParameter("@title", Data.SqlDbType.VarChar) param.Value = title cmd.Parameters.Add(param) param = New Data.SqlClient.SqlParameter("@type", Data.SqlDbType.VarChar) param.Value = strType cmd.Parameters.Add(param) param = New Data.SqlClient.SqlParameter("@length", Data.SqlDbType.BigInt) param.Value = Length cmd.Parameters.Add(param) cmd.Parameters.AddWithValue("@incident_id", id.Text) cnn.Open() cmd.ExecuteNonQuery() cnn.Close() Return True Catch ex As Exception Return False End Try End Function
Dear Friends, I need to import data from several excel files. How can I configure excel source object to dinamically import each file? The name of the file will be in a parameter of ssis package and this name change frequently, and ach time the filename change I dont want to change the configuration on the excel source? What you sugest? Shoul I use a script component as source?! Regards!
I have saved an SSIS pacakage on my sql server. I am able to see and run the package through Integrations services. My question is, is there a way to edit the package through Management studio?
Is it possible to open a package that is saved to a server??
The reason being is I develop on desktop but some of the development I cannot do due to access restrictions.... So I want to save the package to the server then do the final creation there...
Dear Friends, I currently have my excel source dynamic for the connectionstring. Using a global variable with the filename and using the expressions of this control to dynamically have the connecttionstring. My problem is that i need tto read always the first workseet and the name changes frequently and generates an erro in SSIS. how can I set the excel source to goes only for the first sheet independently of the worksheet name?! Regards!!
Running SQL 2008. Trying to copy data from one table into another table using SSIS Import/Export Wizard. Now, when I do a straight "Copy data from one or more tables or views", no problems. But when I use the "Write a query to specify the data to transfer", it will not let me get anywhere.
My source table has a field that is setup as "time". It has data, and no problems with the field. I even replicated my destination table structure exactly. But when I try to use the Import & Export wizard, for that one field I get an error stating the source field is unknown and it is labeled as "-1" instead of "time".
I found a couple of of workarounds. One is to cast the source field "time" as "datetime", and then end up with a "datetime2" field in the destination table. Works, but not what I want to store in that field. Second workaround is to use TSQL and use a "INSERT INTO...SELECT...FROM..WHERE.." statement. This works, and gives me the desired results with all data types being same in source and destination, but is a slight pain in the rear end.
I just want the Import & Export wizard to work. It should work. Why doesn't it know what "time" is? I even checked the MSSQLToSSIS10.XML mapping file the wizard is using. This is what it has for "time":
An Excel Source Data Flow object (which used to work fine) sudenly started display the following error box:
TITLE: Microsoft Visual Studio ------------------------------ Error at Create BusStop Table [DTS.Pipeline]: The index is not valid. ADDITIONAL INFORMATION:Exception from HRESULT: 0xC0048004 (Microsoft.SqlServer.DTSPipelineWrap)
What could the cause be? What is the meaning of: HRESULT: 0xC0048004 ? How could this info be used?
I have a problem with reading data from an Excel file in SSIS. I'm trying to read a column that mostly consists of decimal values, but there are couple places where column entry is 2 numbers separated by a slash (e.g. "100/6.0"). SSIS tries to be smart and identifies the column data type as decimal and when it reads the cell with the slash in it, it reads as NULL. I tried to make my excel source reader component to read that cell as a string, but it gives me an error. If anybody has come across something like this, I would highly appreciate some help
Please can anybody help me in transferring existing SSIS Packages saved in a shared folder location from development server 2ED to Live server TWD1. Both has SQL server 2005 running and has visual studio 2005 Currently about 25 SSIS packages are executed from the development server transferring data on Live server TWD1...these ETL process is called from development server but executed on live server. Now the problem is when i call these packages from the shared folder from live server it crashes.....i need to changes something to shift the whole package to the live server..and execute on live server itself instead of recreating the whole 25 process from scratch.....also i use optimize for many tables ..and run in a single trancastion....so how can i see the mappings of source and destination tables.
Please let me know the process how i can achieve this. Thanks George
hi i have question can sql server know when the row in table Saved add new field call "date_row_save" date+time inside the my table in the sql server i need to know whan the row last Saved evry row put date time TNX
I have excel column with numeric and special character values , when I take that into SQL table using SSIS, the special character values enter as null value. the example column values are given bellow
1 2 2/1Â 1/2 1/2 means 1 or 2 ,
how can I read this values exactly into SQL table?
We have found that using the SSIS "Import and Export Wizard" using the "Microsoft Excel" data source that there appears to be a maximum column length of 255 characters for any row.
Even when defining the destination table columns as nvarchar(4000), the wizard fails with the errors shown below.
We have found no workaround except manually changing the imput data. There doesn't appear to be any "Advanced" options for the Excel importer as there are for the flat-text importer. So, no question here, just posting the bug so that *next* time someone searches the web for an answer, this post comes up
MessagesError 0xc020901c: Data Flow Task: There was an error with output column "English String" (18) on output "Excel Source Output" (9). The column status returned was: "Text was truncated or one or more characters had no match in the target code page.". (SQL Server Import and Export Wizard) Error 0xc020902a: Data Flow Task: The "output column "English String" (18)" failed because truncation occurred, and the truncation row disposition on "output column "English String" (18)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component. (SQL Server Import and Export Wizard) Error 0xc0047038: Data Flow Task: The PrimeOutput method on component "Source - Sheet1$" (1) returned error code 0xC020902A. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. (SQL Server Import and Export Wizard) Error 0xc0047021: Data Flow Task: Thread "SourceThread0" has exited with error code 0xC0047038. (SQL Server Import and Export Wizard) Error 0xc0047039: Data Flow Task: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown. (SQL Server Import and Export Wizard) Error 0xc0047021: Data Flow Task: Thread "WorkThread0" has exited with error code 0xC0047039. (SQL Server Import and Export Wizard)
edit: After searching further this is documented under "Excel Source" in BOL which provides a registry-based workaround. I guess the issue is that the wizard considers truncation to be a 'fail' case and there's no easy way to override this behaviour, specify the column types nor determine which line is in error)
Truncated text. When the driver determines that an Excel column contains text data, the driver selects the data type (string or memo) based on the longest value that it samples. If the driver does not discover any values longer than 255 characters in the rows that it samples, it treats the column as a 255-character string column instead of a memo column. Therefore, values longer than 255 characters may be truncated. To import data from a memo column without truncation, you must make sure that the memo column in at least one of the sampled rows contains a value longer than 255 characters, or you must increase the number of rows sampled by the driver to include such a row. You can increase the number of rows sampled by increasing the value of TypeGuessRows under the HKEY_LOCAL_MACHINESOFTWAREMicrosoftJet4.0EnginesExcel registry key. )
When expoting data from excel to sql server table, using SSIS package, after exporting is done, how would i check source rows are equal to destination rows. If not to throw an error message.
How can we handle transactions in SSIS 1. when some error/something happens during export and the # of rows are not exported fully to destination, how to rollback the transaction in SSIS.
When expoting data from excel to sql server table, using SSIS package, after exporting is done, how would i check source rows are equal to destination rows. If not to throw an error message.
This is a pretty simple question, but I'm going nuts trying to find the answer. After creating an SSI package, I told it to save to the SQL server... Now where do I go to pull that package up again and make changes and/or execute the package?
I'm trying to import some XLS files that I receive from some suppliers. The problem is that every time they send some columns with text values but formatted as number. When I read those columns with SSIS Excel Source, they come all with null values.
I don't want to change the columns data types every time, so I would like to know if there's a way to bypass the column types that are already there.
I tried to use both the Jet driver and the Office 12 driver. I've already used the IMEX=1 on ExtendedProperties too with no success. Is there a way to force reading the columns as text, even if they have data types assigned to them?
I am trying to import spreadsheets from web pages on a regular basis. I do not have any control over their format but they have all been saved as web pages from Excel.
There are two parts to this: a) Downloading the files b) Importing the downloads
Right now I am focusing on b) Importing the downloads.
I have tried 1) Using an Excel connection in DTS 2) Using OPENROWSET to access the spreadsheets more directly from TSQL 3) Using a linked server to the file.
All of these seem to use the OLEDB 4.0 provider and they all work if I convert the *.htm file to a *.xls file using Excel first.
With one curious exception none of them work from the *.htm files. DTS is the exception, if I first open the *.htm files in Excel the transformation task succeeds.
I don't really want to use Excel at all on the server. It's strange that DTS does work when the *.htm file is open.
I am trying to get some insight into the various OLEDB options to use hoping that will help me avoid having to use Excel. Perhaps I would just be better off using SQL Server 2005 and SSIS?
I'm using SSIS 2005 Enterprise edition, I'm creating a package that reads an excel (xls) file using the "excel source" component, and it dumps the data into an OLEDB destination (a sql server). When I drag the excel source component and create the excel connection to my file the component automatically reads the columns and their datatypes.
The problem is that I have a column which has numeric data and the package uploads as NULL every number that starts with a zero. (note: in excel this column is formatted as "text", despite it has only numbers, because it's the only way excel maintains the left sided zeros).
So I checked the data types by right clicking the excel source component -> show advanced editor and my surprise is that this column's data type is detected as double-precision float, and it doesn't let me change it. URL... but it only works when the first row of data has a number beginning with zero on this column. How to get the data imported correctly?
I've had little success gooling/searching for this (so far).
Given a simple spreadsheet:
StoreNumber StoreName
1 UPDStoreName_1
2 UPDStoreName_2
3 UPDStoreName_3
4 NEWStoreName_4
I want to have an SSIS package that will update a table: mystores (storenumber int, storename nvarchar(255))
StoreNumber StoreName
1 StoreName_1
2 StoreName_2
3 StoreName_3
5 StoreName_5
.. what I need to do is insert the new, update the existing and leave the remaining unchanged. i.e. :
StoreNumber StoreName
1 UPDStoreName_1
2 UPDStoreName_2
3 UPDStoreName_3
4 NEWStoreName_4
5 StoreName_5
(the UPD and NEW are added to simplify the example).
Now the default action of an excel source into an ole db destination is an insert into the table - so PK constraints causes failures.
Now, given that the table is referred to by other table, and is in a 24x7 website, how do I change the SSIS package such that, on a row-by-row basis, anUpSert (update or insert) is performed?
The only idea I have so far is:
create temp table
insert excel data into temp table
iterate through the table, using if exists ... update else insert logic <-- this to be done in a SP
