I am using SSIS package for pulling the data(last 2 months data).
Since the data size is huge, i have to split the data into hourly basis and pull the data.
how i can make this dynamic? Right now i am changing the hours manually after package execution.
Is it possible to dynamically grow a data file for TempDB?
I have some TSQL that I want to execute (this is raw tsql, not something I'd typically share) when I get an alert that TempDB is growing.
SET NOCOUNT ON;
DECLARE @TempDB TABLE (FileID tinyint, name varchar(50), size bigint)
DECLARE @TotalSize decimal(19,4), @TotalFiles int, @CurrentTempDBSize bigint, @SQL varchar(1000), @DBName varchar(50)
SET @SQL = ''
INSERT INTO @TempDB
[code]....
ENDThe code runs fine, but doesn't actually increase the size of the file.
I am tasked with truncating and reloading tables from one server to another. Company policy prevents cross-server queries, but allows SSIS packages with cross-server connections. I am doing this for about 25 tables. I have the table names in a single table & I have created an FEL to execute tasks against each table one-by-one. It works fine to truncate all the tables. I run into issues, though, with the DataFlowTask. I'm able to tell it which server & table to dynamically connect from and to, but it doesn't know how to map the metadata. They're the exact same columns and field names in both source & destination.
View 9 Replies View RelatedI have created some dynamic sql to check a temporary table that is created on the fly for any columns that do contain data. If they do the column name is added to a dynamic sql, if not they are excluded. This looks like:
If (select sum(Case when [Sat] is null then 0 else 1 end) from #TABLE) >= 1 begin set @OIL_BULK = @OIL_BULK + '[Sat]' +',' END However, I am currently running this on over 230 columns and large tables 1.3 mil rows and it is quite slow. How I can dynamically create a sql script that only selects the columns in the table where there is data in a speedier manner. Unfortunately it has to be on the fly because the temporary table is created on the fly.
I am trying to get hourly data on a table which was working fine on mysql but not on sql. how to use this in sql server managmenmt studio, i receive 'time' is not a recognized function name.and curdate() is not recognise function.
select [pa_number], [pa_surname]
,[pa_forename],
sum(time(datetime) >= '07:00:00' and time(datetime) < '08:00:00') as '7.00-8.00 AM',
sum(time(datetime) >= '08:00:00' and time(datetime) < '09:00:00') as '8.00-9.00 AM',
sum(time(datetime) >= '09:00:00' and time(datetime) < '10:00:00') as '9.00-10.00 AM ',
sum(time(datetime) >= '10:00:00' and time(datetime) < '11:00:00') as '10.00-11.00 AM',
[code]....
Need to scheduled to copy table data from one database to another database daily @7:00 AM by creating the table with date and time stamp.
Example :
Test1DB ---> Table1
Test2DB -----> Table1_281120140700 in Day1 and Table2_291120140700 in Day2.....and so.
SELECT * INTO Test2db.dbo.new_table_name FROM Test1db.dbo.old_table_name in this format ??
script and scheduled to run daily @ 7:00AM?
I am having 2 tables one is staging temp and another is main import table.
In my staging table there are 3 column Col001,Id,Loaddate
in Col001 column data are present with '¯' delemeter.
I am having function which is used to load data from staging to import table using one function.
this function create a insert statement.
My Existing function
-- Description: To Split a Delimited field by a Delimiter
ALTER FUNCTION [dbo].[ufn_SplitFieldByDelimiter]
(
@fieldname varchar(max)
,@delimiter varchar(max)
,@delimiter_count int
[Code] ....
I am unable to get correct statement with above function.
Hi guys!
How can I extract data from a SQL server 2000 to TEXT Files. On which the data length that I have is different from the new format.
sample: database1: employee_ID = 10
database2: ID_employee = 8
May I know what query can I execute to provide an 8 length for the incoming database?
Thanks! Hope u'll help me!
Guys can you help me to extract my database data to text files using different format:
sample
ClientId9ClientNo12
How can I extract ClientNo with a length of 12 to ClientID with a length of 9 to text files.
I am using the below script and I am getting data for 15 minutes interval. I would like to aggregate this data to hourly so instead of reading for 2014-01-01 00:15:00.000 and 2014-01-01 00:30:00.000 I want all the data aggregated for 2014-01-01 00:00:00.000 and then for 2 o’clock. how should I tweak this query to sum the interval values and display it?
SELECT r.MeterId, r.ReadingDate, r.Reading
FROM MeterReading r, MeterDetail d, Building b
where r.MeterId = d.MeterId
and d.BuildingId = b.BuildingId
and b.BuildingName like '%182%'
and r.ReadingDate between '2014-01-01'and '2014-01-10'
order by r.MeterId
Current Output
MeterIdReadingDateReading
3969 1/01/2014 0:000
3969 1/01/2014 0:150
3969 1/01/2014 0:300
3969 1/01/2014 0:450
3969 1/01/2014 1:000
3969 1/01/2014 1:151
3969 1/01/2014 1:300
3969 1/01/2014 1:450
3969 1/01/2014 2:000
3969 1/01/2014 2:150
3969 1/01/2014 2:300
3969 1/01/2014 2:450
3969 1/01/2014 3:000
I have SharePoint 2010, which I have uploaded a PowerPivot model onto.
Currently it doesn't seem like I could setup the Data Refresh service to refresh my model more frequent than once a day. The Data Refresh configuration page looks like this:
Which doesn't show an option for anything more frequent than daily.
I have also tried to refresh the model's database directly on the Tabular SSAS instance (which SharePoint is using to store PowerPivot models) via SSIS or XMLA, but I get an error saying the tabular model is in "ReadOnly" mode, which I could potentially bypass (by detaching and re-attaching the model), but thats starting to sound abit too hacky.
Is there any way I could refresh my SharePoint uploaded PowerPivot model more than once daily?
After performing a join operation on two tables i get the below resultset
pid, fname, typename, pname, pcost
1, cad, bars, product-1, 100
2, har, witte, product-2, 120
3, nes, bars, product-3, 119
Now i need to create files with the obtained resultset like
Column 'fname' is the folder name and 'typename' should be the file in the particular folder.
For example the first record should be inserted into file name 'bars.txt' in the folder 'cad' and third record should be created in file name 'bars.txt' in the folder 'nes'.
I am having 100 of flat files need to load in respective staging table.I want to create table on run time as per filename input.suppose if input filename is ABC then table name should be Staging_ABC if file name is XYZ then it should be Staging_XYZ.Table structure is below need to create at run time
CREATE TABLE Staging_'Filename'(
[COL001] [varchar](4000) NULL,
[Id] [int] IDENTITY(1,1) NOT NULL,
[LoadDate] [datetime] NOT NULL default getdate()
)
I am getting error when I am trying to create table on runtime
Declare @FileName varchar(100)
Declare @File varchar(100)
set @FileName='brkrte_121227102828'
SET @File = SUBSTRING(@FileName,1,CHARINDEX('_',@FileName)-1)
--=select @File
[Code] ....
Error massage:-
Msg 203, Level 16, State 2, Line 16
The name 'CREATE TABLE DataStaging.dbo.Staging_brkrte ( [COL001] VARCHAR (4000) NOT NULL, [Id] Int Identity(1,1), [LoadDate] datetime default getdate() )' is not a valid identifier.
How to resolve above error....
I'm looking to dynamically remove records from tables dynamically using the information schema within SQL Server. Looking to remove records from all the tables within a schema. I have gotten as far as generating the script dynamically then using a while exist clause to execute the delete statements.
DECLARE@TargetSchema varchar(100),
--@LibNameData varchar(100),
@fnameIndex varchar(100),
--@startOFR_SCR_FILENAME_DATE varchar(25),
[code]...
Would like to execute the statements generated by the results from the information schema.
Hi,
I am new to the whole concept of ASP.NET and codebehind (i am using c# and i am also new to that) i currently trying to get my codebehind to connect to a SQL Server express 2005 database on page load however i am getting this error thrown back at me when i run the aspx page on my localhost,
Cannot open database "db_revival" requested by the login. The login failed.Login failed for user '12042HOPEASPNET'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Cannot open database "db_revival" requested by the login. The login failed.Login failed for user '12042HOPEASPNET'.Source Error:
Line 18: {
Line 19: SqlConnection conn = new SqlConnection(@"Data Source=12042HOPESQLEXPRESS;Initial Catalog=db_revival;Integrated Security=True");
Line 20: conn.Open();
Line 21:
Line 22: }
Source File: g:Level HRevivalDefault.aspx.cs Line: 20
i believe it has something to do with the conn.Open() funtion or the connection string it's self the acutal full code is here,
using System;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Data.Sql;public partial class _Default : System.Web.UI.Page
{protected void Page_Load(object sender, EventArgs e)
{SqlConnection conn = new SqlConnection(@"Data Source=12042HOPESQLEXPRESS;Initial Catalog=db_revival;Integrated Security=True");
conn.Open();
}
secondly once i get the connection working i want t be able to check a user email and password against those in a table to allow the log in, through my experience with PHP i would of done this by putting what the user entered into a variable and then checking the variables against an sql query, how do i go about this with aspx and C# my textfield is called uname and my passsword is call pword. Any help of example code would be greatly appreciated.
Many Thanks
S_ainley87
I have a table having code
code
12345678
21345678
45789612
12345678
21345678
21345678
12347586
I want result on the basis of first 2 digit the repeatation of code is count as 1.
code1 code2 count
12 34 2
21 34 1
45 78 1
So these are a couple different interpretations I've made of a statement that needs to return students name's with the OSOS field in the attendcode code column of the attendance table on todays date with an attendance.aperiod column equal to 4.
This first one returns no results at all when I should get about 7 rows or so.....
Select Student.name,
attendance.studentid,
attendance.attenddate,
attendance.aperiod,
attendance.attendcode
from Attendance, Student
where AttendDate >= DateAdd(Day, DateDiff(Day, 0, GetDate()), 0)
And AttendDate < DateAdd(Day, DateDiff(Day, 0, GetDate()), 0) + 1
And aperiod = '4'
And AttendCode = 'OSOS'
AND student.name = Attendance.studentid
This one is returning all of the names in the database 6 times each.
Select Student.name,
attendance.studentid,
attendance.attenddate,
attendance.aperiod,
attendance.attendcode
from Attendance, Student
where AttendDate >= DateAdd(Day, DateDiff(Day, 0, GetDate()), 0)
And AttendDate < DateAdd(Day, DateDiff(Day, 0, GetDate()), 0) + 1
And aperiod = '4'
And AttendCode = 'OSOS' order by name
I think something is messed up with my WHERE control but I cant figure it out... Any help is greatly appreciated!
Hi,
Could you please help me to find easiest way to schedule daily auto data extraction time in SQL Server Express 2005 Edition (I need to put time to load data every day).
Thanks
Amir
Hi all,
I am really new to SSIS, so may be this is a really simple question, but I couldnt find an answer yet.
I need to build a package that
1) counts the rows from a view
2) if rowcount >0 extracts the data into a file
I tryed to do this using a Row Count Transformation in the data flow, but after putting the count in a variable I am not able to perform the "conditional" phase two.
I mean that I want to check the value of the variable, but cannot figure out how to conditionally execute the flat file extraction.
Using Row Count, I have to build 2 data flow tasks.
Is there a way to do this in a single data flow?
May be using an Execute SQL Task instead of row count?
Any suggestions will ge greately appreciated
IgorB
I have a database that has a table with a lot of delimited data. I need to extract some of this data, preferably into a sql view, that I can name the fields. The data looks like this:
|7700|6|0|work center|24|8|XLT
What I need is:
field name 1= 7700 field name 2= 6 field name 3= 0 field name 4= work center field name 5=24, ETC
I'm not sure how to count the delimiter | and get the data between | & |. Or maybe that isn't the way to do this.
Here is a sample of what I did in Crystal Reports but I need a view to make this usable.
Split ({usrc1.application_data}, "|") [22] This gives me the data between delimiters 22 & 23.
I would like to fetch the data flow component name while package is executing. Since system variable named [System::SourceName] only fetches name of the control flow tasks? Is there a way to capture them?
View 5 Replies View Related
Hi all,
We are using a package to extract data from a source build on some image using SOLID ODBC 5.2 driver.
When we tried to extract data through MS SSIS(SQL Server Integration Services) package using SOLID 5.2 ODBC driver connection, it throws an error as mentioned below:
ERROR DESCRIPTION:
Error: 0xC02090F5 at Data Flow Task, DataReader Source [1]: The component "DataReader Source" (1) was unable to process the data.
Error: 0xC0047038 at Data Flow Task, DTS.Pipeline: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.
The PrimeOutput method on component "DataReader Source" (1) returned error code 0xC02090F5. 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. There may be error messages posted before this with more information about the failure.
Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: SSIS Error Code DTS_E_THREADFAILED. Thread "SourceThread0" has exited with error code 0xC0047038. There may be error messages posted before this with more information on why the thread has exited.
Also, we tried the same in a Excel sheet using the SOLID 5.2 ODBC driver it is working fine.
Does the SOLID ODBC driver support .Net framework or do we need to install any other components??
Thanks and Regards,
Nirmal
Currently we have a DTS that extracts data from a cube to a SQL Server table and then from SQL Server to a flat file.
This is taking a considerable time is there a faster way of doing this?
Thanks.
Mark.
I want to build a data import process with SSIS, sourcing Hyperion Financial Management. Accoring to my knowldge there were a Star Integration Server (Star Analytics acquired by IBM in Feb 2013) doing the extraction job and which could be used in SSIS.
As this product is not available now, how to do this.
I have a function that returns a table from a comma-delimited string.
I want to take this a step further and create a function that will return a set of tablenames in a table based on a 'group' parameter which is a simple integer...1->9, etc.Obviously, what I am doing is not working out.
CREATE FUNCTION dbo.fnReturnTablesForGroup
(
@whichgroup int
)
RETURNS @RETTAB TABLE (
TABLENAME VARCHAR(50)
[code]....
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've tried downloading SQL Server Express several times and installing. Every time it fails during extraction. I have a Vista machine with .NET Framework 3.0 already installed. I'm using MS Expression Web, MS Access, and have also downloaded and installed MS Visual Web Developer 2008 Express Edition. Why can't I even get SQL Server Express to extract from the downloaded file?
I'm trying to use the MSDN subscriber downloads VPC image for SQL Server 2008 CTP4. There are 4 files listed as 1 of 4, 2 of 4, ... 4 of 4.
When executing the WinRAR self-extracting archive, it runs normally for a while and starts to create a .VHD file but just when it seems to be almost complete, it stops and asks for "c:mypathen_sql_server_2008_ctp_4_vhd_part_1_of_5_.rar".
Since the files were just posted back on 10/9, I thought I would wait and eventually, someone would notice the problem and post corrected files but now it's been over a week and I don't see new files or any mention of any problems.
Is anyone else able to successfully download and extract this image?
LaRoux
Does any kind person have a simple example Package for undertaking 'Text Extraction' on one or two columns of text data in a SQL Server table (the data is not in Unicode)?
I've not really used SSIS for a while, and I'm now building some in 2012 and trying to utilise some of the features in the 2012 SSIS catalogue; however I've hit a bit of a stumbling block.
What I'm trying to do is have a master/child package relationship, with several child packages and where the child packages themselves are dynamically called (i.e. the master package may call a different child package based upon some value or state of data already processed.)
When I try and create an expression for the PackageNameFromProject property... well, that property doesn't appear to set dynamically. I know how to do this for old style packages by creating expressions for the package name etc; but that way I can't use the package parameters I have from my master package.
I am trying to dynamically create the connection to a database within an SSIS package.
the requirement is to allow the user to pass through the database as a variable and that variable will dynamically create the connection string in the connection manager.
Is this possible, if so how?
I have SSRS report that has around 80+ columns. I have requirement where in dynamically hideshow columns in report based on user selection. I could able to do it by setting expression for "Visiblity" property and having report parameter thro' which columns to display can be choosen.
My problem is 2 points
1. fox example if columns 2 and 4 to be hidden, then there is an empty column between 1 and 3 and 5 columns. How to avoid this
2. When i export to PDF / Excel these spaces prevail.