I created an Excel Source and used a query to get the data,i.e
SELECT F1,F2,F3,F4,F5,F6,F7 FROM [Fut Days$A20:G1480]
The query works fine, the preview returns the rows, but SSIS will not generate output Columns nor will it let me manually add them? Am I missing something?
I am not sure which type to use for my Script Transformation Editor output fields. I'm getting errors based on the Data Type I'm specifying for my fields.
Print Screens:
http://www.webfound.net/script_task.jpg
TITLE: Package Validation Error
------------------------------
Package Validation Error
------------------------------
ADDITIONAL INFORMATION:
Error at Import Maintenance (mnt) File [Split HeaderRows into Columns [5176]]: Error 30512: Option Strict On disallows implicit conversions from 'Double' to 'UInteger'.
Line 21 Column 37 through 71
Error 30512: Option Strict On disallows implicit conversions from 'Double' to 'Long'.
Line 22 Column 35 through 69
Error 30512: Option Strict On disallows implicit conversions from 'Double' to 'Long'.
Line 23 Column 37 through 71
Error 30512: Option Strict On disallows implicit conversions from 'Double' to 'Long'.
Line 25 Column 27 through 61
Error at Import Maintenance (mnt) File [Split HeaderRows into Columns [5176]]: Error 30512: Option Strict On disallows implicit conversions from 'Double' to 'UInteger'.
Line 21 Column 37 through 71
Error 30512: Option Strict On disallows implicit conversions from 'Double' to 'Long'.
Line 22 Column 35 through 69
Error 30512: Option Strict On disallows implicit conversions from 'Double' to 'Long'.
Line 23 Column 37 through 71
Error 30512: Option Strict On disallows implicit conversions from 'Double' to 'Long'.
Line 25 Column 27 through 61
Error at Import Maintenance (mnt) File [DTS.Pipeline]: "component "Split HeaderRows into Columns" (5176)" failed validation and returned validation status "VS_ISBROKEN".
Error at Import Maintenance (mnt) File [DTS.Pipeline]: One or more component failed validation.
Error at Import Maintenance (mnt) File: There were errors during task validation.
(Microsoft.DataTransformationServices.VsIntegration)
------------------------------
BUTTONS:
OK
------------------------------
I'm not sure if this is needed but here's the script I coded in my script task also:
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Public Class ScriptMain
Inherits UserComponent
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Dim strWholeRow As String = Row.OutputHeaderRows
Row.BatchDate = CStr(strWholeRow.Substring(0, 8))
Row.NotUsed = CStr(strWholeRow.Substring(9, 32))
Row.TransactionCode = CStr(strWholeRow.Substring(33, 34))
Row.GrossBatchTotalAmount = CDbl(strWholeRow.Substring(35, 44))
Row.NetBatchTotalAmount = CDbl(strWholeRow.Substring(45, 54))
Row.BatchTransactionCount = CDbl(strWholeRow.Substring(55, 59))
Row.PNETID = CStr(strWholeRow.Substring(60, 63))
Row.PartnerCode = CDbl(strWholeRow.Substring(64, 67))
Row.Filler = strWholeRow.Substring(68, 100)
End Sub
End Class
IS that possible to get teh output of a execute sql task to excel destination.I have query which will comapre the data difference between two databses. It will comapre all tables in both databses and list out the difference in data by each table. I need to run this query using SSIS and need to get the output to a excel sheet...I have used the data flow task to run this query but my query is giving some error when used with data flow task. So i have used excecute sql task and need to write teh out put to a excel sheet.
View 11 Replies View RelatedUsing below script to export the select statement result to .xls
declare @sql varchar(8000)
select @sql = 'bcp "select * from Databases..Table" queryout c:bcpTom.xls -c -t, -T -S' + @@servername
exec master..xp_cmdshell @sql
But result is not exporting in seperate tabs, all 4 column details are exporting in single cell.
how to export the data in columns to separate tabs in excel.
I am using execute sql task to run a stored procedure in oracle database which returns a resultset. This works. Now I need to send the ouput to a destination table in a sql database. Should I use for each loop to pick the resultset and insert it into the destination one by one (which I dont think is a great idea) or is there a better way to accomplish this task (in data flow task) ?
When I use dataflow task instead of execute sql task, the main issue is I am not able to see the output columns when I execute an oracle stored procedure, but when I see the preview I can see the resultset . But I can see the output columns for a sql server stored procedure.
I have a SQL Task that calls a stored procedure and returns an output parameter. The task fails with error "Value does not fall within the expected range." The Stored Procedure is defined as follows: Create Procedure [dbo].[TestOutputParms] @InParm INT , @OutParm INT OUTPUT as Set @OutParm = @InParm + 5 The task uses an OLEDB connection and has a source type of Direct Input. The SQL Statement is Exec TestOutputParms 7, ? output The parameter mapping is: Variable Name Direction Data Type Parameter Name User::OutParm Output LONG @OutParm
View 7 Replies View Related I am using a Excel Source to get the data from an excel file to sql server 2005 table. A couple columns are coming in a double precision float, but some values have characters in them, but those values are coming out as null, even though I changed the datatype from float to unicode string. Any inputs on resolving this will be much appreciated.
Thanks,
Manisha
Can someone please explain the difference between Output and External columns? I can't fathom why "Output" columns aren't good enough. In other words, what is there a need or value in having two types of "output" columns?
TIA,
Barkingdong
Hello All,
I have a problem and i wish i can get the answers or advices to solve it.
i have like 20 excel files and in each file there is 1 sheet (Planning) . What i need to do is to loop on the on the 20 files (actually this is the easy part and i already done it) the hard part is while looping i need to open each excel file and loop on the 256 columns in it and extract the data from it to a SQL server Database.
Any help will be alot appreciated.
I am trying to find a way how to query following data from a table that looks like this:
ColA ColB ColC
1 2 3
Output should look like this:
ColA
1
2
3
Hi,
I am using a sql command to produce outputs having 3 columns
Before running the sql I am spooling the output in .xls format
But when I open the .xls file (i.e. the spooled file) data of all the 3 columns appear in a single column with blank spaces between them
Is there a way by which I can ensure that each output column of the SQL appears in a seperate column in the excel Sheet
Thanks
Sanket
I need to get the output of a SSIS package in an Excel.
I have made a Stored Procedure for getting the output, since the output has variable number of columns hence i have made a table through the stored procedure which is dynamically created when the procedure is executed.
Now i have a table in SQL with the output data.
Any pointers, how to put it into an excel??
In my report I have used the "jump to url" for the report output but when I export this to excel I get a column with the correct data and also the url.
Is there anyway of exporting the data without the url?
I want my users to be able to view the report and click on the column the want to URL jump to but also be able to export just the raw data without the URL?
I think I have a basic question. Can someone tell me
the command I can use to have a stored procedure run
and generate output? This is for SQL 6.5.
I would like to schedule the sp_help_revdatabase
stored procedure to run periodically and output it
to a file. I think this is possible, but I haven't
been able to come up with the syntax...
Thanks for any suggestions!
Toni
Hello,
What I'm trying to do is get an XML query from a database and use the XML to render an excel document. Can this be done using SSIS? Can I actually read a column from a database and store it in a variable and then create an excel spreadsheet with that variable in SSIS? Would this be done in the control flow using the XML task editor?
Thanks,
Phil
I have a stored procedure that returns XML and have defined my own "Root" element w/ "ROOT('urlset')" in the stored procedure. When I put this into my Control Flow, I have a subsequent task that takes this XML stream and writes it to a file. The issue I am running into is that SSIS is adding it's own "Root" element before my output. Is there any way of avoiding this?
View 4 Replies View RelatedHi,
I am trying to do the following without success;
I have a UDF which returns a string of XML data (of the data type XML)
I want to use this UDF within an Execute SQL Task to set a variable to the output of the UDF. I then want to use the variable to pass the XML string to a XML Task to apply an XSLT to the XML.
I have set the following properties in the Execute SQL Task:
ResultSet: XML
SQLSourceType: Direct Input
SQLStatement: SELECT [dbo].[fn_GENERATE_XML](123) as rawxml
ResultName: 0
Variable Name:user::xml (where the variable is of type string)
My problem is something to do with the UDF call as if I substitute the UDF for a simple SELECT statement with a FOR XML Clause it works fine.
Any ideas where I am going wrong?
Thanks
I've got a question that I can't seem to find an answer for, I was hoping someone here might be able to point me in the right direction. I've set up a stored procedure that will email someone if any entries are added to a table . However, the output is garbled looking (see below)
Client Number SSN Client Name Old SD
New SD
------------- ----------- ---------------------------------------- --------
--------
800901 899-34-3482 John Smith 04/20/20
05/01/20
400909 144-23-0029 John Smith 04/09/20
04/11/20
447788 445-89-9967 kjl;j;j 04/05/20
04/12/20
300099 234-90-7815 John Johnson 04/08/20
04/15/20
What's happened is the client name field is too wide, so the New SD field kicks down to the next line. I'd like to clean this up. Is there a way I can either increase the length of the row before it moves to the next line, or can I re-size the client name field to match the size of the data. In other words, cli_name_vc is declared as a varchar(40). If the longest name that comes up in the query is 18 characters long, can I re-size the output so that it does not take up 40 characters?
Any help is greatly appreciated.
thanks,
-scott
I have a table like:
ID Disc
----------------
1 BUSH
2 JOHN
1 GOLE
2 MIKE
I would like output depending on ID to put Disc into two columns. Like:
ID Disc1 Disc2
----------------------------
1 BUSH NULL
2 NULL JOHN
1 GOLE NULL
2 NULL MIKE
Any help will be appreciated. Thanks
ZYT
I am trying to add two columns together and find all the entries that equal zero I have:
select table1.nbr, table2.itemvalue as CC
from table1, table2
where table1.nbr=table2.nbr and exists (select (OpnAc1+OpnAc2) as Open from table2
I want to take the sum of OpnAc1 and OpnAc2 and find all that equal 0, (meaning no open 1 or open 2)
Using MySQL 5, MySQLQuery (latest). Complete Newbie,
I have the following query:
SELECT i.IndivId, i.Surname, i.First_Names, i.Parents,
(SELECT i.Surname FROM individuals i, families f
where (i.IndivId = f.father_ID) and (i.indivId = f.Family_ID))
as "father"
FROM individuals i
where i.Parents>0
order by i.Parents;
It returns nulls for the subselect. There are 2 tables, individuals and Parents. I am trying to use a father id. in the PArents table to cause the individual id. (being the father of the individual(s)) to be named in the father column. I have a feeling I'm going wrong here.
I think the query above is pretty self explanatory!
Many thanks.
I am using bcp to get the query output in excel file. But I am not able to see the column headings. Is there a way to get the column heading also in the output file.
the command I am using is this.
declare @x varchar(300)
set @x = 'bcp "select * from Northwind..orders" queryout c: est.csv -S local -U sa -P passwd -c -C RAW -t "," -r '
exec master..xp_cmdshell @x
go
OK, another problem but the same member, i am feeling REALLY thick now!
I have a job set up that extracts data from a table and puts to an Excel sheet, this is then emailed to certain recipients.
why is the data appending in the Excel file rather than overwriting? How can i prevent this?
Has anyone had experience formatting an excel file (i.e. run a macro) after it is (created &) outputted from a DTS package?
Also an easier question:
What is the best (easiest) way to create a unique filename in Excel with a datetimestamp in the file name (i.e. MyFile-20040608.xls)
Thanks!
Is there a way of running an OS command via xp_cmdshell and sending the results to a spreadsheet within a DTS package ?.
View 2 Replies View RelatedI would like to create a SQL job that runs a basic query monthly and saves the results as a .xls file and emails the results to a user. I've been unsuccessful with sp_send_dbmail in that the output file is always a single column. Any suggestions?
Thanks, Dave
I use bcp command to output to excel, it works. But I want to format the excel, some column width are too small,user need adjust the column width, otherwise it shows ######.
How can I set columns width when I use bcp output to excel.
Also, can bcp command output to multiple excel sheets and add report title in each excel sheet?
I have this script which outputs a combination of financial data. I have recently joined a table that includes a narrative and when I run the query, it runs perfectly. However, when I copy and paste the output into Excel, not all the data is showing. After the process of elimination, it is because Excel doesn't like the narrative column and not all data is copied across which is very annoying. If I remove the narrative column, then all the data copies over correctly.
I am using Excel 2010 which is quite up-to-date.
Where does output from <xsl:message> stylesheet elements go? It's not in the Progress or Output window, and there doesn't seem to be a property that controls the destination for messages.
View 4 Replies View RelatedIf ive got different pieces in a dataflow, how could I for example, see the output of a datasource before it gets to destination ? If im executing a task, what can i get access to from the immedate window to see the outputs at various stages ?
View 1 Replies View RelatedI am learning SSIS, and am trying to save the XML output from a SQL task executing a stored procedure. I need to either save it to a .xml file, then FTP the .xml file to an external server, or do the reverse (FTP, then save file.) I can't get past the SQL task so that it works with the File System Task, or the FTP task.
Here is the stored procedure
Create Procedure [dbo].[HRGCSpace]
(@HRGSpace nvarchar(max) = ''Output)
as
Begin
Set NOCOUNT On
set @HRGSpace = (select
CustomerNumber 'CustomerNumber/@id',StartDate 'StartDate/@id', EndDate 'EndDate/@id',Action 'Action/@id',
WorkZoneNumber 'WorkZoneNumber/@id',BuildingName 'BuildingName/@id',BuildingFloorPlan 'BuildingFloorPlan/@id',
FloorName 'FloorName/@id',FloorFloorPlan 'FloorFloorPlan/@id',SpaceName 'SpaceName/@id',VacantUnit 'VacantUnit/@id',
SpaceFloorPlan 'SpaceFloorPlan/@id',Address1 'Street/@id',Address2 'Street2/@id',City 'City/@id',
StateProv 'StateProv/@id',ZIPPost 'ZipPosCode/@id',MoveOutDate 'MoveOutDate/@id'
from HRGCvCorrigoUnits
order by BuildingName, SpaceName
For XML Path ('Space'), Root('HRGSpaces'))
End
--------------------------------------
Here is the execute from the SQL Task
DECLARE @HRGSpace nvarchar(max) EXEC HRGCSpace @HRGSpace Output
If I define the SSIS variable User::HRGSpaces as string, Direction = Output, Parameter Name = 0, Parameter Size = 0
, then use in the SQLTask
General ResultSet = None, Bypass Prepare = True, Connection Type = OLE DB, IsQuery Stored Procedure = False,
Parameter Mapping for User::HRGSpaces,Direction = Output, Parameter Name = 0, Parameter Size = 0
the Progress is
"Progress: Executing query "DECLARE @HRGSpace nvarchar(max) EXEC HRGCSpace @HR". - 100 percent complete
Task Execute SQL Task - select the XML Output failed
Error: The wrapper was unable to set the value of the variable specified in the ExecutionValueVariable property.
If I change it so that ResultSet = XML, the Progress is
" [Execute SQL Task] Error: There is an invalid number of result bindings returned for the ResultSetType: "ResultSetType_XML".
If I add ResultName = 0, and VariableName= HRGSpaces, I'm back to
"Progress: Executing query "DECLARE @HRGSpace nvarchar(max) EXEC HRGCSpace @HR". - 100 percent complete
Task Execute SQL Task - select the XML Output failed
Error: The wrapper was unable to set the value of the variable specified in the ExecutionValueVariable property."
Leaving the ResultSet = XML, and changing the SSIS variable as Object, the Progress is
" [Execute SQL Task] Error: There is an invalid number of result bindings returned for the ResultSetType: "ResultSetType_XML".
Changing the ResultSet back to None, but leaving the variable as Object, the SQL task will successfully execute, but the File System Task with IsSourcePathVariable = True and SourceVariable = User::HRGSpaces when executed errors out with
"[File System Task] Error: Variable "HRGSpaces" is not a string. Error: There were errors during task validation. Validation is completed. Warning: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (3) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
So, how to I resolve the Object vs String definition. Any help in getting beyond this is appreciated!
Hi Everyone,
I am trying to do a query where I need to use as little C# as possible to build my gridview. Basically I have a column called statusID. There are about 15 options for this column but I only want to count certain ones. I want to count when statusID = 3 and output that into a column called "fullUnitsUsed" but when the value is 4 or > 13 I want it to count and put the result into a column called "halfUnitsUsed". I also want it to count based on the month. To accomplish this I have used CASE and GROUP BY. This has worked to some extent. Currently if I COUNT for one month I get the correct number of fullUnitsUsed and halfUnitsUsed used for January. Unfortunately the query returns 2 records for the month. The first one has a value for fullUnitsUsed and halfUnitsUsed is NULL, the second record has fullUnitsUsed as NULL and halfUnitsUsed has the correct value. I was hoping to output one record where both fullUnitsUsed and halfUnitsUsed have data. My other problem is that if I test for the entire year (which is what this query is supposed to do) there are 5 records returned for each month, 3 of the records have fullUnitsUsed and halfUnitsUsed both as NULL and for the other 2, one has fullUnitsUsed with data and the other has halfUnitsUsed with data and the other column in both records is NULL. The values for fullUnitsUsed and halfUnitsUsed are counted for the entire year as well, which I only want it to count based on each month. Below is my query, any suggestions about how to approach this will be greatly appreciated. If any clarification is needed please let me know. Again if I could get this to work completely with SQL and not need to use any more C# than I have to it would be preferable.
SELECT People.lastName + ', ' + People.firstName AS fullName, Property.Name, NYSDDSORegion.Description, OpenDays.[month], OpenDays.maxOpenDays,CASE Attend.statusID
WHEN 3 THEN COUNT(Attend.statusID)
END AS fullUnitsUsed,CASE Attend.statusID
WHEN 4 THEN COUNT(Attend.statusID)
WHEN 14 THEN COUNT(Attend.statusID)
WHEN 15 THEN COUNT(Attend.statusID)
WHEN 16 THEN COUNT(Attend.statusID)
WHEN 17 THEN COUNT(Attend.statusID)
WHEN 18 THEN COUNT(Attend.statusID)
WHEN 19 THEN COUNT(Attend.statusID)
WHEN 20 THEN COUNT(Attend.statusID)
END AS halfUnitsUsed
FROM Attend INNER JOIN People ON Attend.personID = People.personID
INNER JOIN Property ON Attend.propertyID = Property.propertyID
INNER JOIN NYSDDSORegion ON Property.RegionID = NYSDDSORegion.RegionID
CROSS JOIN OpenDays
WHERE (Attend.attendDate BETWEEN '1/1/2007' AND '12/31/2007')
GROUP BY Property.Name, People.lastName, NYSDDSORegion.Description, People.firstName, OpenDays.monthID, OpenDays.[month], OpenDays.maxOpenDays, Attend.statusID
ORDER BY Property.Name, fullName, NYSDDSORegion.Description
I'll show my schema first, then I'll explain what I'm doing:
--------------------------------------------------
Events
--------------------------------------------------
ID | E_Title
--------------------------------------------------
--------------------------------------------------
EventOptionGroups
--------------------------------------------------
ID | EOG_EventID | EOG_OptionGroupID
--------------------------------------------------
--------------------------------------------------
OptionGroups
--------------------------------------------------
ID | OG_Title
--------------------------------------------------
--------------------------------------------------
Options
--------------------------------------------------
ID | O_OptionGroupID | O_Description
--------------------------------------------------
--------------------------------------------------
EventRegistration
--------------------------------------------------
ID | ER_EventID | ER_Name
--------------------------------------------------
--------------------------------------------------
RegistrantOptions
--------------------------------------------------
ID | RO_EventRegistrationID | RO_OptionGroupID | RO_Selection
--------------------------------------------------
There are several events. Each event has several different sessions (stored in EventOptionGroups), and each session has a certain number of options (stored in Options).
A user can sign up for an event, and their information is stored in EventRegistration. They can choose an option for each session in the event. For each option they choose, a new row is added to RegistrantOptions.
For each row in EventRegistration, I want to output the user's information, and then the option they chose for each session in the event. Like this:
----------------------------------------------------------------------
E_Title | ER_Name | OG_Title1 | OG_Title2 | OG_Title3
----------------------------------------------------------------------
Event | Bob | O_Description1 | O_Description2 | O_Description3
So in that example, that event had 3 sessions.
Right now, I can only output E_Title and ER_Name, I don't know how to output the session information