Hi,
I need to write a reporting app that process daily IIS logs that are averaging about 299MB a day!
I am considering using SSIS to put the log data into a sql server 2005 database.
Has anyone done this? Or does anyone know if it is possible? And without using the MS LogParser?
Thanks in advance and any help greatly appreciated!
Regards,
kitquo2.
The problem is that I have (for example) following data
AAAA AA ALH
B BBB MIL
CCCCCC CAC Q
D D P
in space delimited file and i want to populate a database using SSIS and it is not working. Any suggestions will be appreciated.
P.S. the only way to recognize colums is through spaces b/w the data.
Hi,
In my case I have to log the errors raised by any task in a package to either windows event log, text file or SQL server. Also I need to send an email notifications to a group of people telling them about the error.
Now can I use SSIS package logging for logging the errors into the required destinations. I mean right clicking on the package and selecting Logging, then adding the required log providers and enabling the events for logging into those. I think I have to upfront select the log providers to log the error, I will not have the liberty to log the error to the destination, the name of which is passed as a variable to the package. This is okay with me though.
Now what will a custom log provider help me to do in this case. Also can I somehow configure my package to call the send mail task everytime an error is raised.
Also, one more option can be developing a package that only does the error handling. It will take in the paramters or the error codes and descriptions, the destination to write to and a flag to send mail or not for that particular type of error.
What do you think? Kindly advise.
Thanks in advance for your help and time.
Regards,
$wapnil
We'll be using 2014 enterprise to populate some excel spreadsheets. We may not have the option of using ssrs so here is the question.
The excel spreadsheets have some pretty fancy heading structures, multiple tabs and in a few cases graphs that i'm sure are dependent on data sitting somewhere in the spreadsheet. The heading have variable info in them (eg FYxx where xx is a parameterized fiscal year, month names etc). Sometimes those headings have sub headings (with variable info) and so on.
Generally speaking how (if at all) do folks deal with this kind of excel challenge when limited to using ssis? I don't know if templates are going to be a good idea. I don't know if i'll be looking at a fair amount of c# etc code behind the scenes. I can be more specific depending on the feedback.
This is part of a conversion from 2 of the larger BI and statistical tools out there to the MS sql stack. Obviously with some shortcuts that we may wish we didn't take. Â
Hello Experts,
I have an SSIS package which runs evertyime flat file is uploaded. I am also logging the results in sql table(sysdts90)..But I am cluesless abt how to show these results to the user on a web page ....as this package is automated..is there a way for me to show the user for this upload this package was run and these r the results
Regards
Following the blog of jamie, I was trying to aply it to my project... but my requirments are a little different than him...
http://blogs.conchango.com/jamiethomson/archive/2005/06/11/SSIS_3A00_-Custom-Logging-Using-Event-Handlers.aspx?CommentPosted=true#commentmessage
I have inside the ControlFlow several sequence containers and inside each container I have several dataflows. I want to get the time of each dataflow execution and the rows inserted in each.
So I added a row count transform for each dataflow and added a global variable to save the rows count value. In each dataflow I need to initialize this global variable to 0. And my problem is that using event handlers in OnPostExecute it repeats several times for each dataflow... how can I save info only once for each dataflow using eventhandlers?
How can make eventhandler execute only once to save the related information for each dataflow?
Understood?
regards
Code Snippet
"INSERT INTO SSISLog(TaskStartTime, EventType, PackageName, TaskName, PackageDuration, ContainerDuration, InsertCount, UpdateCount, DeleteCount, Host)
VALUES( '" + (DT_STR, 4, 1252) DATEPART("yyyy", @[System::EventHandlerStartTime]) + "-"
+(DT_STR, 4, 1252) DATEPART("mm", @[System::EventHandlerStartTime]) + "-"
+(DT_STR, 4, 1252) DATEPART("dd", @[System::EventHandlerStartTime]) + " "
+(DT_STR, 4, 1252) DATEPART("hh", @[System::EventHandlerStartTime]) + ":"
+(DT_STR, 4, 1252) DATEPART("mi", @[System::EventHandlerStartTime]) + ":"
+(DT_STR, 4, 1252) DATEPART("ss", @[System::EventHandlerStartTime]) +"',
'OnPostExecute',
'"+@[System::PackageName]+"',
'"+@[System::TaskName]+"',
"+ (DT_STR, 6, 1252)DATEDIFF( "ss", @[System::StartTime] , GETDATE() ) + ",
"+ (DT_STR, 6, 1252)DATEDIFF( "ss", @[System::ContainerStartTime] , GETDATE() ) + ",
" + (DT_STR, 4, 1252) @[User::SSIS_Rows] + ",
2,
3,
'"+ @[System::SourceDescription] +"')"
Dear Friends,
I created my BI Blog and I need your feedback to my post SSIS-Populating Dimension. Do you think is a good approach? In my project works perfectly!
http://pedrocgd.blogspot.com/
Thanks!
How to populate an excel cell from a sql table using ssis.
I need to populate a specified cell lets say H7, in an excel sheet,
from a sql table in a ssis package.
Suppose we have a column in sql table called total, which contains some value,
we need to take this value and populate the cell H7 in an excel sheet.
Dear all,
I was wondering how to open .LOG files created for a SSIS execution package.
Thanks in advance,
Hi Everyone,
I am very new to Integration Services and need some help on how to capture any errors generated in the SSIS package when executed. Any idea how I can accomplish this?
Thank You
-Sam
HI,
What I have set out to do is to try and truncate the transaction logs on my server instances after a nightly backup. I went ahead and used the shrink database option in the maintenance plan wizard. Unfortunately I have found this will only truncate the logs for databases using the simple database recovery model, and I wish to truncate the whole lot to save space...
I went ahead and put together an SSIS package that does the following:
1. First thing I do is build a fresh copy of a database listing in a temp table using the following SQL statement
USE Master;
Go
SELECT name, DATABASEPROPERTYEX(name, 'Recovery') AS RecoveryModel, DATABASEPROPERTYEX(name, 'Status') AS DBStatus INTO TempTables.dbo.Recovery FROM sysdatabases ORDER BY name
GO
2. Next I read this dataset back into memory using a SQL statement as follows:
USE TempTables
GO
Select name from dbo.recovery Where name <> 'master' and name <> 'model' and name <> 'msdb' and name <> 'tempdb'
I map the results to a results set called User::TableName (which is of an object data type)
3. Next the package goes to a ForEach Loop container and I use the Foreach ADO Enumerator Enumerator to read the data I just selected into the look. I select the User::TableName variable and use the enumeration mode 'Rows in first table'. I have used a variable mapping of Variable: User::TableName with an Index 0.
4. I then use the following SQL statement (which sits in the For Each Loop) to try and alter the recovery model:
ALTER DATABASE @TableName
SET RECOVERY SIMPLE;
GO
This is where the package falls over with the following error message
SSIS package "mnt_TransLog.dtsx" starting.
Error: 0x0 at Simple Mode: Incorrect syntax near '@TableName'.
Error: 0xC002F210 at Simple Mode, Execute SQL Task: Executing the query "ALTER DATABASE @TableName
SET RECOVERY SIMPLE;
" failed with the following error: "'RECOVERY' is not a recognized SET option.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Simple Mode
Warning: 0x80019002 at For Each Table Set to Simple: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (2) 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.
Warning: 0x80019002 at mnt_TransLog: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (2) 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.
SSIS package "mnt_TransLog.dtsx" finished: Failure.
I think the problem here is either I am not passing the variable across correctly (i.e. not declaring it or something..!) or I am passing the wrong type of variable across.
Once the recovery model is changed I can truncate the transaction logs successfully but I can€™t seem to until this is done! Any help in finding a solution to this problem or a better way of approaching this problem would be appreciated!
Thanks
Marek Kluczynski
Management Information Officer
Investors in People UK
7-10 Chandos Street
London
W1G 9DQ
Tel: 020 7467 1956
The problem is that I have (for example) following data
AA AA LH
BB BB ML
CC CC QA
DD DD PS
in space delimited file and i want to populate a database using SSIS and it is not working. Any suggestions will be appreciated.
Hello,
I am having a hard time setting the executable path for an Execute Process Task in SSIS. I have a variable that is initialized at package statup which holds the path to an executable in Windows. When I set the property "Executable" Path in an expression, I get a warning that the path for the executable is not set. One workaround was to try and initialize the variable with a bogus path with the hopes that the "correct" value will be written on run-time. NO LUCK. I still get the error and I cannot run the package until I put a static path.
Does anyone have a clue as to what is going on??
Mike
I have two Databases with the same name that reside in different servers. The two servers are linked servers. Each database consists of 5 Tables. The tables have identical names in each database.
The database in ServerA has 5 tables with data, the database in ServerB also has 5 tables with the same schema as ServerA, however the 5 identical tables in ServerB contain no data.
I need to populate the 5 tables in ServerB with the Data from the 5 tables in ServerA.
What SQL code or script can i use to populate the 5 tables in ServerB
Below are the names of the 5 tables:
TABLE1: [ServerB].[ProdDB].dbo.[Orders]
TABLE2: [ServerB].[ProdDB].dbo.[Sales]
TABLE3: [ServerB].[ProdDB].dbo.[Employee]
TABLE4: [ServerB].[ProdDB].dbo.[Customer]
TABLE5: [ServerB].[ProdDB].dbo.[Region]
Does any one have a script that could help me with this task. Thanks
I am using SQL Server 2005
I've deployed an ssis package to the database server through the manifest file over the network..
The package runs great when testing through vs.net on my client.
I've added the package to a job step in the sql server job agent.
When I test the new job, the package step fails, but there is no error or log information in the job history.
Wwhere can I get error information? And/or How can make SSIS error information more verbose in the job history?
Thanks for any help or information.
Hi i'm pretty new to this, how do i connect to my database a put all the values from one column into the arraylist. Any help and a easy example would be nice Thanks in advance Richard
View 4 Replies View RelatedHi, I want to populate the array with a single column values from database(sqlserver 2000)Ex. name ageaaaa 23bbbb 43cccc 18 Now i want to populate the array with name field values. Please anyone guide me how to do this.Thanks in advanceBala
I am writing a small program (Visual Studio Express 2008) to help me automate populating a database graphically. The program I am writing will keep track of sales figures state by state on a map of the US. This will be accomplished by using a map of the United States overlaid with a hexagonal grid that represents individual sales regions (hexagons). Each hexagon represents a specific sales region, and there can be many sales regions within a state. This small program will populate the database with data specific to each hexagon that is selected by clicking on it with a mouse. A complete set of row entries should be sent to the database every time the mouse is clicked in a hexagon on my map. This program will capture such information as the center x,y pixel coordinates of each hexagon, row & column location of each hexagon, and the state where the hexagon is located. All of this information (HexX, HexY, RowX, RowY, State) has already been figured out or calculated, except that I need help on how to capture, display, and send this information as a new row entry into my database. I need to populate the database hexagon by hexagon, since which state they are in is determined visually.
Here is what I need to populate my database with:
Hex_No - each unique hexagon needs to have its own unique number 1,2,3,€¦.n. This should be as easy as assigning a counter that starts at 1 and increments until all hexagons have been processed
Hex_X €“ the pixel x value of the center (x,y) of the specific hexagon, which is known by the program
Hex_Y €“ pixel y value of the center (x,y) of the specific hexagon, which is known by the program
Hex_Col €“ column location of this specific hexagon, which is known by the program
Hex_Row€“ row location of this specific hexagon, which is known by the program
State €“ the current state on the map. This will be selected by me (combobox) prior to clicking on each hexagon within a given state
Each of the above is represented by a textbox on a form, except for the State field, which is represented by a combobox. When one clicks on the down arrow of the combobox, one can see that the names of the different states have already been entered and linked to the SQL database. The other textboxes are also linked to a different column in the same database; however they are currently empty due to the fact that this is the very data that needs to be captured by this program. This is what I need for this program to do for every mouse click , once I have moved on to a given hexagon within a given state:
Determine next available Hexagon Number, and assign it to current hexagon, display in textbox, send this as part of row data entry under Hexagon_No column
Capture and display the current hexagon€™s center x, and y (Hex_X, Hex_Y), display this in textbox, send this as part of row data entry under Hex_X, and Hex_Y columns
Capture and display the current hexagon€™s row and column information (Hex_Col, Hex_Row), display this in textbox, send this as part of row data entry under Hex_Col, and Hex_Row columns
Capture and display the current State that is selected manually from the list of database values, send this as part of row data entry under State column
Here is an example of what this program might look like in action:
http://farm4.static.flickr.com/3172/2285817199_d75f230f8d_o.jpg
I am not looking for anyone to write my program, but I would appreciate some ideas on how I can accomplish this. I have figured everything out except the part about displaying the new information on the textbox/combobox form, and sending it to the database as the next row entry. That€™s the part I need help with.
Thanks.
Hi
I have few print statements in a stored procedure that gets called from the SSIS package. How do I make sure that these are captured in the SSIS log meaning how do I get them to be displayed in the Package Explorer window when running from the Business Intelligence Studio IDE.
hi
i have a database with a table with several columns:
ID - primery key
customer
company
now lets say i want to edit one row in the database and i have the primery key ID for that row. so i click on that row (which is displayed in a gridview) when i click I want the text that is in the customer cell to appear in a textbox1 and the same with compeny text I want it to apear in another textbox2.
so what code can i write in the GridView1_SelectedIndexChanged eventhandler to populate the textboxes?
thank you
Im a newbie with a sql table in a MSDE database that contains fields ofCustName, BrokerName, Type, Status.I am trying to write a trigger that does this:1. When a new record in a table is added, I would like the selectedfields to be copied to another table (call it Master).2. If that Master table already contains a record where the CustName =CustName, then simply ignore. If CustName does not match anything, thenadd the record to the Master TableIn other words, I am trying to create a table (or even a new database)that has unique records in it - making a master customer list.Any hints on how to get started on this?
View 6 Replies View RelatedHi
I'd like to be a little more efficient in my approach when using databases. Although my site is very simple with a low hit rate expected, I would still like to learn the best methods in designing server load conscious code.
Basically, I have a page where I'd like to show events broken down into days of the week (Monday - Sunday). All the events contain the same data and only difference being the day. Right now, the only way I know how to make this work is to create a unique sqlDataSource (select * where Day = Monday) then assign it to a ‘Monday’ grid view. I then repeat this same process for each day of the week.
As the data is always the same, is there a way to make only call to the database returning all the data, then close the connection and then separate the data there after. To me this seems to be a much more efficient way to manage the data???
Thanks for any help/supportRichard
OS: Windows 2012 Enterprise
SQL Server: 2012 Enterprise
I was wondering if there is any way all SQL Server error log entries could be automatically written to Windows Event Log.Â
I am using SQL server to create a rather complicated client database for a nonprofit organization. I have access to an ancient version of the database in Access format, but would rather create a new database from scratch instead of "up-sizing" the old database. Although the old database is mostly useless, it contains a goldmine of names and addresses that I could use to populate the new database that I'm creating. My question is this: Is there any relatively easy way to cut and paste from external data sources into a new SQL database? For example, I would love to just select twenty rows of "first names" from the old database and then paste that into my new table. Can anyone suggest any quick and easy tricks for populating a new database with place-holder content? Thanks!
View 2 Replies View RelatedI am trying to load in to a new application's database to see if my load procedures will be able to handle the volume and how long I should plan on when I perform our cut over for production. Unfortunately, trying to copy 5.4M rows from one staging table into a production processing table consistently leaves the database in a suspect status, usually stating to perform some type of disk consistency check utility. It happens time after time, is there anything obvious that I could look at to see why this is happening?
View 3 Replies View RelatedHi,
I accidentally populate tables into System databases/master database. What should I do? Should I delete all the tables I populate in mster database?
Thank you very much!
Hi Gurus,i am having problems with restoring a ms sql database.i have restored the database using veritas to a different location('g:datafiles') in no recover mode.when i view the database through the Enterprise Manager, it shows thedatabase as silver icon (loading).i go to sql analyzer, and put in the restore commandrestore log myDatabasefrom 'mylog'with recoverythis produces an errorRESTORE FILELISTONLYFROM jobsServer: Msg 3206, Level 16, State 1, Line 1No entry in sysdevices for backup device 'mylog'. Update sysdevicesand rerunstatement.i look in master..sysdevices - no entry for mylog, but then no entriesfor the log files for any of the other perfectly working databaseseither.i do have a copy of the log files ('c:logfiles') in another locationi would like the following help if possible:* a way to update sysdevices with the log file i wish to apply to myrestored database so it will let my restore go through properly.* a way to specify to use apply the logs in 'c:logfiles' withouthaving to give the restore statement a logical name for the log files(which naturally won't be in sysdevices!)pls supply transact sqlmy thanksEdwina63(if wish to email please remove h from edwinah@)p.s sp_add_log_file_recover_suspect_db will not work in a partiallyrestored database
View 6 Replies View Related I have a set of disks allocated for a high performance SQL implementation that will entail lots of large queries. My question is do I allocate more IOPS to the logs or to the database?
For example, if I have a 10 disk RAID 1/0 and a 4 disk RAID 1/0 available, which do I allocate to the logs and which do I allocate to the database? Which will require the most IOPS?
Thanks!
Hi,
I've got a sitatuation where one of our sql databases appears to be frequently "starting". The log entry looks like:
Starting up database 'Database'.
And seems to occur at irregular intervals and does not seem to be inline with any other db activity....i.e. transaction log backups, insertions or reads.
This DB is fairly busy receving inserts from our PBX CTI software almost constantly.
Note this is the only DB on the server displaying this behaviour (we've got two named instances running with several databases in each).
Any help / pointers appreciated.
Rgds,
SLE
I have a Development database and I want to roll it back to Monday morning. I backed up the database and used the command:
RESTORE DATABASE ITTEST
FROM ITTEST20040203
WITH NORECOVERY
GO
RESTORE LOG MyNwind
FROM ITTEST20040203
WITH RECOVERY, STOPAT = 'FEBRUARY 2, 2004 09:00 AM'
GO
The transaction logs have never been truncated.
But it does not seem to have worked. It this the best way to do a roll back the database or have I missed out something.
thanks.
Is there an easy way to monitor (audit) who logs onto a database ??Thanks for any and all help that is provided.Art
View 2 Replies View RelatedI have a Db that is 1.7 gigs. The table data takes approximately 200megs. The transaction logs were truncated. Where else can this large size be coming from and how can I confirm?
DB is generally small. ~25 tables, 100 SPs, 10 views, etc.
Note:
I have 4 queues using SQL Notifications, but when selecting from them results in no data.
Thanks
Scott
Hello.
Not an SQL admin. We have an SQL 2005 server that has about 5 DB's on it. One database is maintained primarily by a third party. Often when they need to do upgrades they login remotely to the desktop of our SQL server. Is there a way to apply permissions to specific databases like you would for NTFS? That way they can only backup their database and not do anything to any other databases? Thanks.