Hi everybody, i'm a newbie to SSIS and I'm having a problem dynamically creating a new excel spreadsheet in SSIS.
What I need to do is be able to dynamically create a brand new Excel spreadsheet after a data flow task completes.
Any help would be most appreciated. Thank you.
Is there anyway to dynamically create Excel Worksheets based on a group identity results from a query?
Thanks!
SkySeek.
I am using SSIS to export data from a table to an Excel spreadsheet. This all seems to work put just fine. The user would like a data in column B1 to say when the spreadsheet was created. Is there a way within SSIS to do this. I was looking at using a .NET script but it accesses the spreadsheet as a table so I do not know how to insert data above the headings in row 1. I believe the OleDB provider using column 1 as it column names for the table. Maybe I am just going about the whole think wrong?
Thanks
I am new to SSIS.
I am interested in using SSIS to import an excel spreadsheet into a SQL server database. My biggest concern is how to handle/manage errors that might occur when the import process occurs. Can anyone give me any guidance on this?
I could write some C# code to do the import and to create a custom .txt file listing errors that occur on import. Using C# code to do the import seems like I would just be reinvinting the wheel so to speak.
Hi
Some one Please guide me... How to create a Excel File Dynamically in DTS.
Once i run my DTS Package the result should be moved to a new Excel sheet( This should be created dynamically).
I tries in this way but it says....Syntax error at the DTS GlobalVariables.....
Function Main()
Dim appExcel
Dim newBook
Dim oSheet
dim oPackage
dim oConn
Set appExcel = CreateObject("Excel.Application")
Set newBook = appExcel.Workbooks.Add
Set oSheet = newBook.Worksheets(1)
oSheet.Range("A1").Value = "au_lname"
oSheet.Range("B1").Value = "au_fname"
oSheet.Range("C1").Value = "phone"
oSheet.Range("D1").Value = "address"
oSheet.Range("E1").Value = "city"
DTSGlobalVariables("fileName").Value = "C:\" & Month(Now) & "-" &
Day(Now) & "-" & Year(Now) & "-" & Hour(Time) & "-" &Minute(Time) & "-" &
Second(Time) & ".xls"
With newBook
.SaveAs DTSGlobalVariables("fileName").Value
.save
End With
appExcel.quit
set oPackage = DTSGlobalVariables.parent
set oConn = oPackage.connections(2)
oConn.datasource = DTSGlobalVariables("fileName").Value
set oPackage = nothing
set oConn = nothing
Main = DTSTaskExecResult_Success
End Function
I'm trying to use Excel in SSIS to import the data from spreadsheet to a staging table. The package runs well from the web server using SSMS. But when I deploy and try to execute the package, I'm getting the below error. I've a question, whether I've to install the AccessDatabaseEngine driver in SQL database server or the web server where I'm executing the SSIS?
Error: The requested OLE DB provider Microsoft.Jet.OLEDB.4.0 is not registered. If the 64-bit driver is not installed, run the package in 32-bit mode.
I am attempting to run an SSIS package that, among other things, imports a spreadsheet from excel into a database table. The package runs without any issues within Visual Studio. I have tried executing the package through both, the MSDB run package and through dtexec (trying to kick of the package through a stored procedure) and I get 2 different behaviors.
Using dtexec (the method I really need to use): The package will run successfully...up to the point when the spreadsheet is imported at which time it fails with Description: The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009. Here is the code:
exec xp_cmdshell 'dtexec /sq PopulateTRTLStationandtRTLUnitMapping /ser SERVERNAME
Running it through the MSDB Run Package UI...It will also make it up to the point where the Excel spreadsheet is imported but errors with: The Product level is insufficient for the component "Lookup Station and Account Type: (1894) ...and 1 line with that same error for every single task in that dataflow. Here is the code it runs.
/DTS "MSDBPopulateTRTLStationandtRTLUnitMapping" /SERVER "SERVERNAME" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING V
The machine is running 32 bit OS Windows Server 2003 SP1 and Db SQL Server 2005 32 bit. I found one forum posting that suggested turning the Delay Validation property to True...but that did not fix the issue. I did create the package with my username with a ProtectionLevel of EncryptSensitiveWithUserKey. I don't think it is related to the account however because all of the tasks (serveral work tables are created) up to the Excel import will execute.
I really need to get this working as soon as possible so am open to any solutions someone can present.
is there a way to query an excel spreadsheet directly from sql without using ssis or excel macros?...and without saving the spreadsheet to a table first?
View 5 Replies View RelatedHi All,
I have an exiting excel workbook say master.xls. Now I need to dynamically create and append a new worksheet to the above master.xls every month end using the Reporting services.
Could you please guide me how dynamically creating the worksheets task can be achieved using the reporting services?
Your any guidance or help in this matter will be highly appreciated.
Thanks in advance
Regards
Raman Kohli
Trying to figure out the best method of reading in a number of flat files, all with different number of columns and data types and outputting them to a database.
Here's the problem: They are EBCDIC encoded and some of the columns are packed decimal. I've set up one package that takes the flat file, unpacks the decimal (Using UnpackDecimal component) and then sending the rest through a second component to go from EBCDIC -> ASCII.
What I need is a way to do this for every flat file based on the schema for that flat file. One current solution is to write a script/app to create the .dtsx XML file and then execute that for each flat file. It appears like this may be possible, but I haven't gotten far enough to know for sure. So my questions are this:
1) Is there an easier way to do this (ie somehow feed the schema to the package and use it to dynamically set up the column makers and determine which columns get fed to the unpack decimal component.
2) If there isn't a better way, will dynamically creating the .dtsx XML file based on the necessary input/output columns for each flat file work? If so, what is a good source of information on this (information about how the .dtsx XML file is set up, what needs to be changed/what doesn't, etc).
Thanks,
Travis
I have one share folder ,every month end-user will copy & paste excel file into particular share folder. Ok .
Now i have to create new SSIS package as schedule should run every month to find the file and then load automatically into Sql server tables and then move those excel file to another share folder if file successfully loaded only.
The excel file name will be changing every month. but the format wont change. If any body knows this process or steps.
Please share with me .
Thanks in Advance.
In my SSIS package I have a loop container that I am running the same code against 4 servers.
I have the package export the SQL data to an Excel spreadsheet that has multiple tabs.
Is there a way I can change the tab on the fly or do I need to create a Connection for the same spreadsheet 4 times
Each Connection pointing to a different tab?
I tried to set up a expression for the Excel Connection Manager to use the InitialCatalog for the tab and change it
based on the script in the loop however this causes the following error:
An OLE DB error has occurred. Error code: 0x80040E21. An OLE DB record is available. Source: "Microsoft Native Client" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".
Cannot create an OLE DB accessor. Verify that the column metadata is valid.
Thanks in advance
Hi All..
I have an issue where I'm trying to export data from Sql Server tables (or from a result set in a SP or view) into Excel Spreadsheets. Normally I would use a simple data flow to do this. However, I need to do this on-the-fly because the schema of the Sql data is not static. The table could be a different one or the result set would have column schema that is not always the same.
The constant in all of this is that the spreadsheet columns and the table (or result set) column schema is identical. It's just that the column count and column names are not defined at design time, but would need to be defined at runtime.
Going from Excel to Sql Server is simple as I used a Script Task and the SQLBulkCopy class to dynamically transfer the data. However, BOL says that it's only one way (Data to Sql Server). Basically I need the to go the opposite direction now.
I have all of the information (SQL Table server, database, schema, and name and the Excel file path and name) already set up in variables and running through a ForEach container and I can dynamically change the variable information. I just need to figure out how to dynamically map the columns, create the spreadsheet file, and load the data into the spreadsheet. I'm sure this has been tossed around before. If someone could point me in the right direction I would most grateful.
Thanks.
Lee.
I need to take my 'table stats'every week and put them into an excel spreadsheet so that I can track the changes of my table sizes over time (basically I am watching to see how many rows are in each table). What I was planning on doing was to create a view of my table stats that I could then use DTS to transfer on a weekly basis into my excel spreadsheet. I have only used DTS a couple of times, and I have not ever tried it with excel. Now the problem: Everytime my DTS job runs it appends the data to the end of the origional columns that were created. Since my database has over 5000 tables these columns grow quite quickly. What I would like to do is set it up so that everytime the job runs it puts the new data into new columns in the same worksheet of my excel file. Is this possible? Any suggestions?
View 1 Replies View RelatedI'm getting an issue on a MS SQL DTS package that is doing a simple export from a MS SQL table to and Excel spreadsheet. I have three of these running but one is failing. I’m using DTSRun to run all three of these DTS packages. The only recent change was to the DTS package to fix the first step to delete the data in the spreadsheet tab named “Results”. The process works correctly in development (on different servers). The same active directory ID is being used on all three DTS packages and all three do the same i.e. export data to an excel spreadsheet in the same file location but with different names. I’ve Google’d this but only came across access issues which does not make since since it is writing the other two spreadsheets just fine. Curious.
Error I See:
Running DTS package with passed variables
...
DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSDynamicPropertiesTask_1
DTSRun OnFinish: DTSStep_DTSDynamicPropertiesTask_1
DTSRun OnStart: Drop table Results Step
DTSRun OnError: Drop table Results Step, Error = -2147217911 (80040E09)
Error string: Cannot modify the design of table 'Results'. It is in a read-only database.
Error source: Microsoft JET Database Engine
Help file:
Help context: 5003027
Error Detail Records:
Error: -2147217911 (80040E09); Provider Error: -538642193 (DFE4F8EF)
Error string: Cannot modify the design of table 'Results'. It is in a read-only database.
Error source: Microsoft JET Database Engine
Help file:
Help context: 5003027
Any ideas would be great.
Thanks.
Jim
Can somebody please tell me what I am doing wrong or need to do to resolve my issue. I having problems with one of the columns in an excel spreadsheet that I am trying to upload into the system. One of the columns contain values of both text and numbers such as 'ABC123', 'ABC124', '123456' etc. When I try uploading the sheet into SQL Server 2000 using DTS, the system removes all characters from the one column that I am having problems with. So entry 'ABC123' for example would be truncated to '123'. I tried formatting the column that I am having trouble with in excel to 'General' format as well as tried to transform the column to type varchar in SQL Server while using the DTS wizard but still had no luck. The problem is that SQL server thinks that the column is a float type column from the source and therefore truncates any characters.
View 2 Replies View RelatedBeing new to SSIS I wish to loop through a series of excel spreadsheets and within each workbook loop through each sheet. I am aware of the For Each container but how can the each sheet in the workbook be referenced?
Steve
Hello,
I have some XML that I'm passing through a variable into the XML task in SSIS. I also have an xsd file that I'm using and I want to validate (I think) that XML and have the XML task output an excel document. I've got the xsd file all set up in the "second operand" part of the XML task and the XML I'm passing in as a variable and that's in the "input" part of the XML task. My question is are there any tricks to make an excel document with these two things? Is there something I need to do in the xsd file to tell it that I want excel? Below is my XML and xsd:
XML:
<person>
<person_id>117</person_id>
<last_name>Von Neumann</last_name>
<first_name>Marina</first_name>
<jobs>
<job>
<job_key>1</job_key>
<from_month>06</from_month>
<from_year>1987</from_year>
<company_id>30358</company_id>
</job>
<job>
<job_key>2</job_key>
<from_month>06</from_month>
<from_year>1985</from_year>
<company_id>30358</company_id>
</job>
<job>
<job_key>3</job_key>
<from_month>06</from_month>
<from_year>1979</from_year>
<company_id>30358</company_id>
</job>
<job>
<job_key>4</job_key>
<from_month>09</from_month>
<from_year>1992</from_year>
<company_id>8766</company_id>
</job>
<job>
<job_key>5</job_key>
<from_month>06</from_month>
<from_year>1962</from_year>
<company_id>8822</company_id>
</job>
<job>
<job_key>6</job_key>
<from_month />
<from_year />
<company_id>8822</company_id>
</job>
</jobs>
</person>
XSD:
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"><xsd:element nillable="true" name="person"><xsd:complexType><xsd:sequence minOccurs="0"><xsd:element minOccurs="0" nillable="true" type="xsd:integer" name="person_id" form="unqualified"></xsd:element><xsd:element minOccurs="0" nillable="true" type="xsd:string" name="last_name" form="unqualified"></xsd:element><xsd:element minOccurs="0" nillable="true" type="xsd:string" name="first_name" form="unqualified"></xsd:element><xsd:element minOccurs="0" nillable="true" name="jobs" form="unqualified"><xsd:complexType><xsd:sequence minOccurs="0"><xsd:element minOccurs="0" maxOccurs="unbounded" nillable="true" name="job" form="unqualified"><xsd:complexType><xsd:sequence minOccurs="0"><xsd:element minOccurs="0" nillable="true" type="xsd:integer" name="job_key" form="unqualified"></xsd:element><xsd:element minOccurs="0" nillable="true" type="xsd:string" name="from_month" form="unqualified"></xsd:element><xsd:element minOccurs="0" nillable="true" type="xsd:string"
name="from_year" form="unqualified"></xsd:element><xsd:element minOccurs="0" nillable="true" type="xsd:integer" name="company_id" form="unqualified"></xsd:element></xsd:sequence></xsd:complexType></xsd:element></xsd:sequence></xsd:complexType></xsd:element></xsd:sequence></xsd:complexType></xsd:element></xsd:schema>
Thanks,
Phil
Hi there,
I am able to import an excel spreadsheet into a table in sql server 2005 using SqlBulkCopy. The only thing that bothers me here is how to check duplicate entries and throw an error to the user regarding the duplicate entries. In the table in sql, there is no primary keys. There are five columns and the way I will have to find the duplicates is to match all those 5 columns. Since the excel spreadsheet can have 40 to 500 entries, how can I check those dupes.
Hi everyone,
I've got an Excel Spreadsheet with 5 columns of data which I need to get into an SQL Table. There's 13,000 rows in this Spreadsheet so manually doing it is out of the question.
How can I go about doing this?
Any help will be appreciated!
Thanks,
Andrew
Hey guys,
I'm exporting via dts to an excel spreadsheet. However my database has more than 65000 records. DTS croacks and shoots out that there are too many records in the spreadsheet.
What do I do? Any way to go around that? These are my daily hit logs that are recorded and they tend to get big.
Ilya Zherebetskiy
I am new to SQL and can do queries OK on SQLTalk. I need to know if there is a script to retrieve data and then export to an Excel spreadsheet for internal company use. Is there such a beast and is this the right place to look???
View 1 Replies View RelatedHi,
I'm trying to import an excel file into SQL sever(using an insert statement), i'm creating a DTS package (in enterprise manager) and have VB Script. When i parse it, i get no errors, but when i run the package it says that it ran successfully but nothing happens, it doesnt insert into the table, even though i tested the insert statement. Can anyone help me?? Here's the code:
'************************************************* *********************
' Visual Basic ActiveX Script
'************************************************* ***********************
Function Main()
on error resume next
Set objxl = CreateObject("Excel.Application")
objxl.Visible = False
Dim xlFile
xlFile = "C:Datafile.xls"
Set objWkb = objxl.Workbooks.Open(xlFile)
'' Connecting to SQL Server
set cn = server.CreateObject("ADODB.Connection")
Dim serverName
serverName = "myserver2"
strCS = "Provider=SQLOLEDB; Data Source=myserver2;Initial
Catalog=mycat; Integrated Security=SSPI"
cn.ConnectionString = strCS
On Error Resume Next
cn.Open
Set objsht = objWkb.Worksheets.Open("Sheet1")
Dim client_name, rb, date_rvd, LOB
Dim sql
Dim row, sequence
row = 2
client_name = Trim(objsht.Cells(row, 2).Value)
Do While IsNull(client_name) = False And client_name <> ""
'client_name = Trim(objsht.Cells(row, 2))
rb = Trim(objsht.Cells(row, 4).value)
date_rvd = Trim(objsht.Cells(row, 6).value)
LOB = "WCS"
sql = "INSERT INTO TEMP_TEST (CLIENT_NAME, RB, DATE_REVIEWED, LOB) VALUES (" & _
" '" & client_name & "' ,'" & rb & "', " & date_rvd & ", '" & LOB & "');"
row = row + 1
MsgBox (sql)
client_name = Trim(objsht.Cells(row, 2).value)
cn.Execute (sql)
Loop
if err.count = 0 then
Main = DTSTaskExecResult_Success
else
Main = DTSTaskExecResult_Failure
end if
End Function
Dear all,
i'm using DTS to create a procedure. what i wanna do is to pump data to the excel, however i need to re-use the same excel file everytime, ie:i need to delete all fields in the spreadsheet except the colume name and then pump all data in again. i know how to pump the data in, however i dun know how to simply clear the existing data in the spreadsheet, can anyone help me, big thx ~!
Kam2
I am trying to set up a linked server in SQL Server 2005 to link to an excel spreadsheet.
-I am selecting Jet 4.0 as the provider
-Product name is Excel
-Data Source is the path on our network to the excel file: N:Devon 5403 4.0 Engineering4.01 ProcessLinelistIFCLDT Field.xls
-Provider String is Excel 8.0
-Security | Login not defined is set to Be made using the login's current security context.
The Excel file is an Excel 2003 spreadsheet. The worksheet is titled Pages
I have a query window open in SQL Server Management Studio and the following is my select statement:
SELECT * FROM DEVON_LINE_LIST...Pages$
I get the following error message:
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "DEVON_LINE_LIST" returned message "Cannot start your application. The workgroup information file is missing or opened exclusively by another user.".
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "DEVON_LINE_LIST" reported an error. Authentication failed.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "DEVON_LINE_LIST".
I get similar error messages no matter which security settings I pick.
Any thought as to what I can try to get this to work?
I am looking for a way to import data from a CSV or Excel spread sheet and add the data directly into an Extended field instead of a regular field in the table. for example: let's say I have a comma delimited field with the following info:
NDC_M_FORMULARY,CUSTOM_EXTSIG,Custom EXT SIG
NDC_M_FORMULARY,DRUG_CODE,Alternate key, user defined
NDC_M_FORMULARY,CHARGE_CODE,From the Charge code table
The first column is the table name
Second Column is the Column name in the table
The third column contains the description that I would like to store in the Value in the Extended Property Name "MS_Description"
BTW,I did find the following T-SQL which returns the Extended description for a specific Extended Property
Here it is:
SELECT
[Table Name] = i_s.TABLE_NAME,
[Column Name] = i_s.COLUMN_NAME,
[Description] = s.value
FROM
INFORMATION_SCHEMA.COLUMNS i_s
LEFT OUTER JOIN
[code]...
Hi All,
I am new to SQL program. I did little management for SQL 2000 before.
I need to export from a table or view to excel spreadsheet for company's marketing resourece. Is there any easy simple way to do it?
Thanks all for your help.
Perry Yang
Is there a limit to the number of fields that can be set in an OleDB Update Statement?
This works with 6 fields:
cmd.CommandText = "Update [Sheet2$A2:AP2] Set F1 = '1', F2 = '35062', F3 = '6', F4 = '620000.0000', F5 = '200000.0000', F6 = '700000.0000'"
This Fails with 7 fields:
cmd.CommandText = "Update [Sheet2$A2:AP2] Set F1 = '1', F2 = '35062', F3 = '6', F4 = '620000.0000', F5 = '200000.0000', F6 = '700000.0000', F7 = '123'"
The range should be plenty big with A2:AP2. In the end I'm trying to push 42 fields.
The complete segment is:
Dim ExcelConnection As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & ExcelFileName & ";" & _
"Extended Properties=""Excel 8.0;HDR=NO"""
Dim conn As New System.Data.OleDb.OleDbConnection(ExcelConnection)
Dim cmd As New System.Data.OleDb.OleDbCommand()
conn.Open()
cmd.Connection = conn
cmd.CommandText = "Update [Sheet2$A2:AP2] Set F1 = '1', F2 = '35062', F3 = '6', F4 = '620000.0000', F5 = '200000.0000', F6 = '700000.0000', F7 = '1234'"
cmd.ExecuteNonQuery()
Hi all,
I have two tables in SQL Server 2005 and excel sheet ( Office 2003).
The colums of excel sheet are: name ,ssn, flagbit ( Note: Excel sheet contains data already)
Columns of table_one and table_two are: name ,ssn
I want to compare the ssn field from table_one and ssn in excelsheet_one, if it matches , then flagbit in excelsheet_one should say"T1".
If i compare ssn field from table_two and ssn in excelsheet_one, if it matches, the flagbit in excelsheet_one should say "T2".
Ex:
Table_one (input) ---- excelsheet_one ( Output)
ssn name ---- ssn name flagbit
11 NYC ---- 11 NYC T1
Both the tables refers same excelsheet_one and have to update the same flag bit column in excelsheet.
Basically, i want to compare table and excel sheet, then if it matches, then update excel sheet.
Does anybody how to do this.
Any help will be greatly appreciated.
Thanks
I have this situation that I need to read a spreadsheet with user names into a sql table where user name is just one of the columns. I tried using oledb connection to read the spreadsheet and sqlbulkcopy to import into sql table. There was no error, but the data wasn't imported into sql. Does anyone have any suggestion what I did wrong or what is the right way of doing this? Thanks a lot. Mia
View 2 Replies View RelatedI am using the import wizard in SQL Server 2008 R to import data from an Excel spreadsheet into a table I have created.
The spreadsheet contains 3 columns that SQL recognises as DOUBLE and they contain a 1 or 0. What data type do the corresponding fields in SQL table need to be? I have tried BIT, INT and FLOAT but keep getting an error (can't view details of the error because I get chucked out every time the error pops up). I know the problem is with the DOUBLE data because when I 'ignore' those columns the import works fine.
Is it possible to export the results of a query using SQL Server querymanager to an excel spreadsheet file.Thanks in advance.
View 2 Replies View RelatedDeaa group,I am using SQLServer 2000 in an XP Sp2. I would like to do thefollowing:I have a program running on a database server that generates some datawhich are loaded to the database. This program is used in a webapplication, invoked by some java program and JSP scripts. (I amfrontend illiterated.)The question is, is it possible to write a stored procedure to generateoutput in excel spreadsheet? So that user could call this procedureand get spreadsheet output on the client side.Any pointer to a solution would be immensely apprecaited.thanks,charia
View 2 Replies View Related