SQLServerCentral / SQL Server 2005 / Business Intelligence

Need help to set global variable value

Sagar-636902 — Thu, 19 Nov 2009 12:32:34 GMT

Hi All, I am new in SSIS. i was trying to migrate dts to ssis. in dts there is a script task which set the global variable with that fround in query. i was able to merge the dts tasks along with activex script. only thing i am stuck on was could not set the value in global variable. script is like below.. I have set the package variable for File name and file size as FileName, FileSize. Only the reason i need those are use in another script task so that i can insert the file record and size in database table. Any idea that would be so great Thanks.sagar Function Main() On Error Resume Next Set fso = CreateObject("Scripting.FileSystemObject") sFolder ="C:\Documents and Settings\Sagar\Desktop\dbfl\App\TEST\Input\abc" sInCompleteFolder ="C:\Documents and Settings\sagar\Desktop\dbfl\App\TEST\Incomplete" sAppFileName = "abc.txt" set fsoFolder = fso.GetFolder(sFolder) Dim sSourceFile Dim sDestinationFile If FSO.FileExists(sFolder & sAppFileName) Then FSO.DeleteFile sFolder & sAppFileName END if For Each fsoFile in fsoFolder.Files ' Get filenme sSourceFile = sFolder & fsoFile.Name sFileName = fsoFile.Name 'Get filesize sFileSize = fsoFile.Size 'msgbox "SFileName " &sFileName 'msgbox "SFileSize " &sFileSize sDestinatiionFile = sFolder & sAppFileName 'Renaming file If FSO.FileExists(sSourceFile) Then FSO.MoveFile sSourceFile,sDestinatiionFile End If GlobalVariables("FileName").Value = sFileName GlobalVariables("FileSize").Value = sFileSize 'If the file loading fails, If FSO.FileExists(sFolder & sAppFileName) Then FSO.MoveFile sDestinatiionFile, sInCompleteFolder&sFileName END IF Next ' Clean Up Set fso= Nothing Main = DTSTaskExecResult_SuccessEnd Function

SSIS job success but not quite

johnsonchase7 — Fri, 20 Nov 2009 12:37:01 GMT

I have a package that loads data into a table from a flat file and it runs fine, when scheduled as a job it also runs fine. The problem here is that every time it is ran as a job, the job executes successfully but the data does not load. Anybody ever experienced this? Any help on how to fix this? Could it be a permission issue? Pretty clueless at this point !

could not delete file after renamed using filesystem.

Sagar-636902 — Fri, 20 Nov 2009 16:45:38 GMT

Hi All, What i was trying to do is loading file from txt file to database using ssis once i load done i need to delete and rename another file to txt file and load that again it is working from the folder i had setup for looping within folder using for each containeer. every thing works renaming and deleting but after renaming another loop stopped. reason i found when it renamed that txt file in that folder it is only read only. could not figuer out to change that setting or any logic. i am stuck... any help would be great for me.ThanksSagar

Exporting Data to CSV file

sqlbuddy123 — Thu, 19 Nov 2009 09:42:18 GMT

Hi, When I am trying to export data from a SQL Server View onto a .csv file using the data flow task, the data in the output .csv file is always out of sync with the header i.e I am getting data ouside the last colum i.e data in 20th column is getting in the 25th column like that. Could anyone please help me how to get in exact sync with the header ? Thank You,Best Regards,SQLBuddy.

Customizing Min/Max measures

mtassin — Fri, 20 Nov 2009 07:59:42 GMT

I've got a cube :)Cube has application performance data. Basically as screen load times.Application load times can be zero when the underlying systems determine that the locally cached data is up to date enough.Two different groups.Use Experience group, wants MIN/MAX/Average/and count of uses for all data.Development Group: Wants MIN/MAX/Average/and count of uses for data where the loadtime is non-zero.I managed to solve the Average and count values by adding a calculated field onto my measures table that is either a 1 or a 0 depending on if the individual loadtime is 0 or not. Then I just sum that column and divide the aggregated total execution time by the summed result for the average execution time for non-zero execution times.But I'm at a loss how I can modify the MIN/MAX functions built into the cube designer to have it take the Minimum execution time that is not Zero.Anybody have an idea?

MDX query or a new fact table

Debbie Edwards — Fri, 20 Nov 2009 04:44:19 GMT

I have Schools and each school has ‘Number of session codes’ For each term (1 through to 6) Each term has the Max date against it. Currently the MDX isSELECT NON EMPTY { [Measures].[PCT ATT - FACT Sessional Attendance By School], [Measures].[TOT Al INV PUPILS], [Measures].[TOTAL RCVD], [Measures].[PCT AUTH UNAUTH - FACT Sessional Attendance By School], [Measures].[PCT UNAUTH - FACT Sessional Attendance By School], [Measures].[PCT AUTH - FACT Sessional Attendance By School], [Measures].[CURRENT ATT SUB], [Measures].[CURRENT MAIN ATT] } ON COLUMNS, NON EMPTY { ([Dim Date Time SA].[AC YEAR].[AC YEAR].ALLMEMBERS * [Sessional School].[SCH DFEE].[SCH DFEE].ALLMEMBERS * [Dim Date Time SA].[Date].[Date].ALLMEMBERS * [Sessional School].[SCH NAME].[SCH NAME].ALLMEMBERS * [Sessional School].[PHASE DESC].[PHASE DESC].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( { [Dim Date Time SA].[AC YEAR].&[AC 09-10] } ) ON COLUMNS FROM [CAYA Data Warehouse Dev SA]) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGSAnd it looks like for example (2 rows)[AC YEAR], ‘AC 09-10’[SCH DFEE],’1234’[DATE], ‘2009/10/23’[SCH_NAME], ‘Test School’[PHASE DESC], ‘Secondary’[CURRENT MAIN ATT],4[CURRENT ATT SUB], 5[TOTAL RCVD], 2[TOT Al INV PUPILS],15[PCT ATT], 91.43[PCT AUTH],5.71[PCT UNAUTH], 2.86[PCT AUTH UNAUTH]8.57[AC YEAR], ‘AC 09-10’[SCH DFEE],’1234’[DATE], ‘2009/10/23’[SCH_NAME], ‘Test School’[PHASE DESC], ‘Secondary’[CURRENT MAIN ATT],4[CURRENT ATT SUB], 5[TOTAL RCVD], 2[TOT Al INV PUPILS],13[PCT ATT], 90[PCT AUTH],10[PCT UNAUTH], 0[PCT AUTH UNAUTH]10As you can see here there are 2 rows for the same school. This is because currently there are 2 terms of data. How to I do this so that I just get the max date and then….The attendance for that max dateThe sub attendance for that max dateThe sum of total invalid pupilsThe total received for that specific max dateFor percentage it would be Attended / Possible sessions for everything.I think that in reality I may have to create a new fact table for just the year which would then be a lot easier to manage and then the other fact table would have the data by term.Does any one have any ideas about what the better option would be?Thanks in advanceDebbie

Adventure Works Cube in SQL 2005

Bad dog — Mon, 16 Jan 2006 00:42:00 GMT

Hi there

Excuse my ignorance, but How do I get the samble cubes installed and visible in SQL 2005. I selected the options to install all sample database when I installed but in SSMS there are no cubes.

I also tried to build my own cube by adding a cube database and trying to right click and say "New Cube" but that option is not available.

Thanks

SSAS Cube calculation

matt_weimer — Thu, 19 Nov 2009 10:35:56 GMT

Hi,I have built a data cube in SSAS 2008 that displays billing and payment information. There are two key date fields. One is the original invoice date. The other is for subsequent invoice dates. So, for example, we bill $100 to 10 different patients in January 2009. Therefore, my cube illustrates billed = $100 for 01/09 (original invoice date). Let's say we receive payments of $50 in Jan 09, $25 in Feb 09 and $25 in Mar 09 that all tie to invoices that were originally posted in Jan 09. In short, I have $100 billed in Jan 09 (original invoice date or month) and payments of $50 in Jan, $25 in Feb and $25 in Mar (subsequent invoice dates or months). I'm trying to compute payment percentages based on the billed amount from the orginal month. But SSAS only counts the billed amount of $100 for January. I need it to count $100 for Feb and March as well so I can get percentages.Basically, I want to divide 50/100, 25/100 and 25/100 and put them into periods 1, 2 and 3 so we will know that out of the 100 dollars billed in January, we got paid half in the same month, 25% in month 2 and 25% in month 3. I want the amount billed to be the same for every original invoice month and the payments should differ based on what was received each month (subsequent invoices).Can anyone please help me with the calculation?Thanks,Matt

create a ssas cube

Mh-397891 — Thu, 19 Nov 2009 12:10:58 GMT

I am new to SSAS, I am trying to create my first cube, I have the data in a single table. I need to create the fact and dimension tables based on that table. All the aggrgations have already been done in that table that's provided to me. I need to get the following output when browsing the cube, also attached the sample data below:Date filters: MOnth,yearResellerName Regionname Performance RegionAverage ProductivityMembership 400 670 ServiceComplaints 0 2CustSatisfaction 0 4CREATE TABLE #Temp(month_year VARCHAR(100),Resellername VARCHAR(100),Regionname VARCHAR(100),Performance numeric(12, 4),RegionAverage numeric(12, 4),Area VARCHAR(100),Title VARCHAR(100))INSERT INTO #Temp ( month_year, Resellername, Regionname, Performance, RegionAverage, Area, Title) SELECT '2009-01-01 00:00:00.000', 'Reseller1','Region1',400,670,'Productivity','Membership'UNION ALLSELECT '2009-10-01 00:00:00.000', 'Reseller2','Region2',0,2,'Service','Complaints'UNION ALL SELECT '2009-10-01 00:00:00.000', 'Reseller2','Region2',0,4,'Service','CustSatisfaction'SELECT * FROM #Temp--DROP TABLE #Temp

Execute Process Task (SQLCMD)

forumdog — Wed, 18 Nov 2009 09:00:27 GMT

Hello,I have a execute process task that runs SQLCMD that in turn processes a backup/copy/restore script. Rather then using an input file (-i filename) I would like to somehow embed the script and pass the contents to sqlcmd through SSIS. Rather then having a seperate script file on the server. Does anyone have any ideas on how I can make this work?Here is the arguments I'm using:-S servername -U sa -P password -i C:\Scripts\InputFile.sql -o C:\Scripts\LogFiles\LogFile.txtThanks!

SSIS Excel Foreach Loop Container - Different Worksheet Names

ricardodeano — Thu, 24 Sep 2009 15:12:07 GMT

Hello all - newbie here so be gentle!Ok, here is the situation. I have a number of files with weekly sales information:Sales1.xlsSales2.xlsSales3.xlsEach of these have identical data sets and I have succeeded in creating a foreach loop container..lovely stuff!However, each of these weekly reports have slightly different sheet names:Sales1.xls >>> Wk1Sales2.xls >>> Wk2Sales3.xls >>> Wk3Now this poses a problem with the forloop each container.I attempted to created a variable based on the worksheet name and change the excel file so that it looks at the table with varible and selected that worksheet variable.However, when I attempt to run this, it runs through the first file, executes that data flow but then fails when it encounters the second file.Has anyone got any ideas how I can sort this and/or a step by step procedure to complete this action?Apologies for being thick and any help gratefully received.

Corrupt file error after Analysis Services backup and restore when browsing....help!

Ronnie Walker-485142 — Thu, 19 Nov 2009 07:51:51 GMT

Hi there - got an issue with the backup and restore of a cube from one machine to another. Issue is as follows:Cube sits and is processed on SERVER_A without any issues and can be browsed quite happily. The cube is approx 9.5GB in size after processing.The cube is then backed up using encryption and copied over to another machine (SERVER_B) which has the same level of Analysis Services installed (9.00.3054.00) and then restored. The cube restores successfully however when browsing it using cube browseryou receive the following message:[b]"The query could not be processed: File system error. The following file is corrupted: Physical file \\?\D:\<path to file>"[/b]In order to get the cube onto the new machine and working again it has to be re-processed on the new server.Anyone seen this issue before and know what causes it - its driving me crazy and does not affect ALL cubes backed up and restored in this way as others work fine.Thanks for help in advance.

Report Filter: Summary of Detail level

dave-dj — Mon, 16 Nov 2009 05:06:44 GMT

Hi can anyone tell me how I would go about setting a report prompt that would then display a report with either summary report information (e.g. totals) or detailed report (as as a seperate line - sales number, sale value) based on a report prompt that can be either Summary or Detailed.(I can setup the prompt fine - but I'm not quite sure how to use the visibility functionality to suit my needs)

SSIS Obtain more information through a set of IDs

kramaswamy — Mon, 16 Nov 2009 12:19:00 GMT

Here's the situation I have -I have a raw file which contains ID numbers. Those ID numbers correspond with a unique ID field of a table in my database.What I want to do is to read in those ID numbers from the raw file, and then obtain other information related to them. This other information is located in specific tables.I've done some looking around, and the only option I can find which seems to work is to use the Lookup transform, and pass the ID numbers through it to get the information related to the IDs.Is this the only option? It seems like it's a bit excessive, since it will require me to load all of the information for all records into memory, when I have already got the IDs for which I need the information.If I were writing this out in a T-SQL stored procedure, the way that I would be doing it would be to obtain the raw file data into a temp table, and then write a query along the lines of "SELECT FROM TABLE JOIN RAW_FILE ON TABLEID = RAW_FILEID". Can I do something similar in SSIS?

Report Server Authentication Problem

Paul-1065223 — Thu, 19 Nov 2009 00:00:43 GMT

HiI have just installed SQLServer 2008 R2 CTP and everything works fine except the reportserver, no matter who you are logged in as it says you have insufficient permissions even if it is the admin level service account. OS is Server 2008 64bit. I know it's a CTP so there could be issues but would just thought i would check that no one else has had this problem and can help.If you log onto the browser and open IE as administrator you do get in but i can't then see anyway of adding other users to permissions groups!I have used RS 2005 extensively but i am new to 2008 and realise there is no longer any dependancy on IIS.CheersPaul

Need an advise on designing BI infrastructure

esnkumar — Fri, 06 Nov 2009 02:16:33 GMT

Our corporate currently has a medium sized (approx 500 GB) data warehouse in SQL SERVER 2005 and all SQL Server’s components database server, SSIS and SSAS are deployed to the single physical server. Considering the current performance and future growth management, we are in the process of re-architecting this BI infrastructure using SQL SERVER technologies. Could you please share your ideas/experiences/expertise on selecting the following design choices for designing a BI infrastructure for an optimal performance: 1. System Configuration : Monolithic (run all SQL Servers components in single physical server) OR Distributed (run all SQL Servers components to different physical servers)2. Data Storage: SAN or DASThanks in Advance.

SSAS MDX Get Previous Year Month data date range sales

briansalentine — Tue, 17 Nov 2009 22:53:48 GMT

I've created a cube in SSAS. The user enters a begin and end date range, lets say 10/7/2009 and 10/17/2009. I want to display the sum of sales for that date range, as well as the sum for the previous year during that range only (10/7/2008 thru 10/17/2008).Or they could enter a date range of 1/1/2009 to 11/12/2009, or really anything.1)Getting the current date range is easy, but what MDX do I use to calculate the previous year's data for that date range only? My MDX and other MDX examples I see only get the entire previous year's data, but don't seem to know how to limit it to just the date range I show above. 2)And as a bonus I got an even bigger challenge...instead of reporting date to date, I need to also report Day to Day...so if 10/7/2009 is the first Wednesday on October of 2009, I need to find the first Wednesday in October of 2008 and report through the end of the date range. So for example the date range of 2008 would start on 10/1/2008 because that is the first Wed, and end on 10/11/2008.All this is easily done is T-SQL, but I don't know if MDX can do it.

SSIS problem

mbasabebe — Tue, 17 Nov 2009 10:45:31 GMT

Hola,He creado un proyecto .dtsx en el que como los datos de una tabla(que tiene como datos un ntext y 2 int ), cambio el tipo de ntext a nstring a traves de un "data conversion" y despues el string trato te dividirlo en varias columnas a traves de un "derivied column". Para ello utilizo esta sintaxis :SUBSTRING([Data Conversion].Path,FINDSTRING([Data Conversion].Path,"municipalityId=",1),3)Pero me da error en el derivied column al ejecutar, en el k me pone:-[Derived Column [87]] Error: An error occurred while evaluating the function.-[Derived Column [87]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "component "Derived Column" (87)" failed because error code 0xC0049067 occurred, and the error row disposition on "output column "IdMunip" (97)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.-[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Derived Column" (87) failed with error code 0xC0209029 while processing input "Derived Column Input" (88). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.Alguien puede ayudarme??Un saludo y gracias

Accessing SSAS Cubes using .NET Login

vallisaravanan — Tue, 03 Nov 2009 22:07:06 GMT

Hi all, We have built cube using Analysis services and we are trying to restrict access based on Geographical location. We have implemented several roles to do that and it works fine while tested using BIDS in Visual Studio and SSMS. Actually we are using a third party web based tool for accessing the cube and we have developed a custom built .NET application to control the Log-ins. Now we would like to map these UserIds that the users provide during logins to different roles in SSAS and give access to appropriate data. Now we have given NT authority service, the rights to access the cube and the users access it through that account.Instead of this how to make them access the cube through the .NET application's credentials?Thanks in advance,Valli

SSIS - Dynamically Generate Excel File

david.tyler — Tue, 08 Jul 2008 15:35:39 GMT

Hi all, I am trying to find out if it's possible to generate an excel sheet using an SSIS Script Task. I have results from 4 tables that need to be included in a sheet and I need to write a custom header for each section. The issue I am having is, there is no COM tab in the script designed references page to add the Excel libraries. I would like to avoid creating the file through T-SQL. Has anyone else experienced this issue?

How to Sum Reportitems!textbox.values

pitso.maceke — Mon, 16 Nov 2009 02:50:58 GMT

Hi All,Can someone assist me as how can I sum reportitems values.my expression is below, but it display a binary code like 1.001.001.00=reportitems!YTDKRA_Closed_WITH_SLA.Value+reportitems!YTDKRA_Closed_ofAssignned.Value+ reportitems!YTDKRA_Perc_Queries_Red.Value+reportitems!YTDKRA_Perc_Queries_Amber.Value+ reportitems!YTDKRA_Per_Internal_Quality.Value+reportitems!YTDKRA_Per_External_Quality.Value

RepertServer database chunkdata 85Gb

5QL53rv3r — Tue, 17 Nov 2009 09:35:51 GMT

Hi,My Report Server slows down in the afternoon and the following query is running on the database and CPU and physical IO keep increasing to the point only an IIS reset will fix the issue.ReportServer.dbo.WriteChunkPortion;1There are no errors in the SQL error log.The message in the report services log is - w3wp!dbcleanup!8!11/16/2009-09:56:18:: e ERROR: Sql Error in CleanOrphanedSnapshots: System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Microsoft.ReportingServices.Library.InstrumentedSqlCommand.ExecuteNonQuery() at Microsoft.ReportingServices.Library.DatabaseSessionStorage.CleanOrphanedSnapshots(Int32& chunksCleaned)w3wp!library!8!11/16/2009-09:56:18:: i INFO: Cleaned 0 batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0 chunks, 0 running jobs, 0 persisted streamsI suspect the root of the problem is the fact that the chunkdata table in the reportserver database is 85Gb.I have came across a suggestion that it is OK to truncate this table, but it was talking about the reportservertemp database rather than reportserver database.any ideas?

Regarding SSIS

mithun.gite — Tue, 17 Nov 2009 23:42:01 GMT

Hi Friends,We are facing once issue due to code page 65001 , as 65001 is not supported in 2008, we want to use ssis to insert the data from flat file to table, which is working fine when we tested for the one table.Now the problem is there we have 84 tables in one dataset, for which we want to insert the data in tables.See our Sp will pick the first table , it will make the call to SSIS and then SSIS will take the file and insert into the table, this is what we want to achive, currently the sitaution is like we have to create 84 packages , each one for each table, but anybody have any idea how we can write only one package which can pick up the file and insert the data in to table and also map the columns dynamically....Seems not possible but , please share if someone has any idea or option or any way which we should give it a try..Any reply will be appriciated..Thanks-Mithun

SSIS package with source file on server

simin811 — Fri, 23 Oct 2009 06:48:26 GMT

Hi All,I have to send data from a flat file to a sql server table using SSIS package. The problem is that the flat files are in an unshared folder on the server and I don't want the clients to have access to that folder. If I run the package from my VB .Net solution, it will only be able to access the text files if they are on the client or in a shared folder on server. So I want to run SSIS package from the sql server, so it can access the folder on itself. I think that the only way is using sp_start_job, because I don't have permissions to run cmp_shell.If I'm right, please tell me how to do that and how pass parameters like the source file name to the package using stored procedure.Thanks a lot,Simin

Table vs Matrix

Jason Marshall — Tue, 17 Nov 2009 10:26:22 GMT

Good morning-When drilling through to another report from a matrix, we can pass some parameters (i.e., for a sales report (x-axis = Product and y-axis = month with intersection giving sales for product by month, we can pass Product and Month to the detail report to get the detail behind the sales total). Is this at all possible with a table? I have a request to enable drill through to detail from a report laid out in a table, but need to find a way to pass several parameters. For example, if the user clicks on any of the totals under the Count columns in the attached example, they want to see the detail making up those counts. Is this possible, or would the report need to be recreated as a matrix? SSRS 2005.Thanks in advance,Jason

Tips and tricks for executing queries in SSRS reports

Louise Boulton — Mon, 02 Nov 2009 04:10:42 GMT

Hi,I need to write some t-sql to use for reports in SSRS 2005. The reports will be called from a front end application. In the front end users will select options (start date, end date, customer address in this or that country, all the usual kind of stuff) and a user's selections will be passed at runtime as report parameter values.So here's my question: does anyone have any tips or tricks for limiting the number of separate rdl files I have to create and upload to the report server? I can see for example that if I write a parameterised stored procedure which executes one query if the param value is x and another if the value is y, then call this procedure in a report, the report designer shows me only the first possible set of column headings to use in the report. But I don't want to create a separate report for every possible set of user selections.Thx in advance,Louise

A little advice please on Business Intelligence

simon.hull — Thu, 12 Nov 2009 10:59:29 GMT

Hello all.Need a little advice if thats ok on a few points.First a little about myself if thats ok.I'm at a crossroads in life with my career and i am close to committing to going down the BI road. I have been a Crystal Reports writer since 1997 and have used all versions from then until version 2008 now. I consider myself an expert Crystal Reports Developer. Along the way i have also seen and used all the version of SQL Server from 6.5 to 2005, I am yet to cross swords with SQL 2008, not even seen it or read up on it yet. I know my way around SQL 2005 but am no expert. I was an acting DBA on SQL 6.5 for several years way back, but never took that futher. I have always had a home network and SQL server (sad i know) to train and develop on, that remains.Now, i would like a little advice on whether i should go ahead and get the Microsoft press book for SQL 2005 BI (70-445 exam) or bypass that and go for SQL 2008 BI (70-448 exam) instead. Is there any real benefit doing 2005 first ? I do intend to take whatever exam at prometric when im done. On amazon there is only 1 reveiw for the 2008 book and none for the 2005 book. So has anyone got any comments about these books? Money is tight at the moment as i have moved from the UK and am now in Norway, i need to get the right books as I am taking this opportunity to bring myself up-to-date in some areas. I have got my hands on some training vids of BI for 2005 but there is one particular area that was lacking and thats how (and the best practices) to get from your day to day database to the datawarehouse version that you interrogate with cubes, and reporting services etc. I presume its all done within SSIS, however i am not sure on this.I would also love to hear some comments from those of you that have Business Objects (SAP) BI experience as well as the SQL version about. Is there a book on SAP's BI that you would recommend ?Any help will be appreciated.Regards,Simon

My Topics

eddieaus25 — Tue, 17 Nov 2009 04:46:00 GMT

[url=http://www.atoncer.com/coins/main/medieval-byzantine.htm]medieval coins[/url][url=http://www.atoncer.com/coins/main/roman.htm]roman coins[/url][url=http://www.atoncer.com/coins/main/ancient.htm]ancient coins[/url][url=http://www.atoncer.com/coins/main/biblical.htm]biblical coins[/url][url=http://www.atoncer.com/coins/main/celtic.htm]celtic coins[/url]

How to move a table plus data between server/databases

barri_j_martin — Tue, 03 Nov 2009 10:15:11 GMT

HiI've been slogging away for a few days now and have hit a brick wall.Using SSIS 2005, I need a way to move a table from one database to another, including data. The problem is, there are 400 potential tables, and I don't know which one it will be until runtime. Basically the tables are datasets, and they vary in table definition and data volumes. The code is required to move certain of these dataset tables to a new copy of the database on a different server, for demos, testing, etc.But the tables required will vary from copy to copy, and so I'm looking for a way to parse the table at run-time.I've tried SQL Server Objects Transfer, and it leaves out the defaults,etc.Dont want to create data flow for each possible table as more are added all the time.Trying to find a way to do it in a script task maybe, even using script to create a new package / task programmatically.any ideas?Thanks guys n gals.Barri

Script component question

a2zwd — Wed, 21 Oct 2009 13:51:07 GMT

Hi Pals, How can I import data from a csv file and load to database table.I want to use Script component(Source). I will pass the table name(destination) from a variable.Means, I dont know the output columns.So, how can I insert data to output buffer in the script component.Is there any good solution for this?thanks in advance

Problem with DB2 Source in SSIS

anilpinnamaneni — Mon, 26 Oct 2009 09:03:11 GMT

Hi,I am running in to a strange issueI am extracting the data from the DB2.I am pulling three columns from the source table.Their data types is varchar,varchar and dbdatetime.The source is having some thing around 12,000 rows.After pulling the 9000 change records i am getting a wired problem like column a and column b which are varchars are getting empty values and date time value is getting some weird value 0000-00-00 00:00:00 and due to this my package is failing.The package is running fine in local BIDS.The only problem is when i am running this package in the server i am getting errori am using Provider=IBMDADB2Please help me out

What is the best Task to do this??

johnsonchase7 — Thu, 12 Nov 2009 14:50:59 GMT

Hey forumites, I have an ssis task that I need directions with. I have an FTP task that I have set up to receive some files. A new file is added everyday to this ftp site and the old files are never deleted. I need to somehow get the new file when i run the package everyday (dynamically) without mistakenly getting the old files. How can I do this dynamically? I also need to export the new file to a database everyday. Here is what I am doing so far.For the FTP file transfer - I have set up the task and configured the manager. How do I dynamically let it choose the most recent file to transferf rom the ftp site?For the dataflowr - I will attach a forloop container to loop through the csv file and in the container a data flow task for the transfer.Any additions, subtractions, recommendations or better way to do this? Any input would be nice. Thanks

Installing SSIS 2008 on existing SQL Server 2005 installation

seandeyoung-1145978 — Mon, 16 Nov 2009 12:13:25 GMT

I would like to install SQL Server SSIS 2008 on my SQL Server 2005 installation/instance and was wondering if the two can work in conjunction or if there is any complications to this configuration? I will depoying SSIS packages on the 2008 installation to backup databases on from the 2005 databases.Thanks

SSIS - how to watch the values in SSIS variables in debug mode

adam spencer — Mon, 16 Nov 2009 11:47:52 GMT

I am using an Execute SQL task to run an SP that return a state abbreviate but I can't find out if it did return a value and if so, what, how to watch the values in SSIS variables in debug mode

SSRS - using stored procedure not returning all columns

Brad Allison — Mon, 16 Nov 2009 11:31:17 GMT

I have the following stored procedure that is using a declared temporary table, however the data being returned into the report seems to be the select statement inside this procedure for the temp table. I am not getting * from @report, but I am getting * from dbo.tblGlueRoomStock. Does anybody know why this would be?[code="sql"]CREATE PROC dbo.GlueRoomInventoryAS-----------------------------------------------EXEC dbo.usp_GlueRoomSET NOCOUNT ON DECLARE @report TABLE ( uid int primary key clustered, caseCode char(10) not null, scheduledDate dateTime not null, shift char(1) not null, line char(10) not null, CartonCopy nvarchar(20) not null, CubeSize int not null, CasesRan int null, CartonsPerCase int not null, GluedPerSkid int not null, CartonsPerSkid int not null, TotalGlued int not null, TotalFlat int not null, StartDate datetime not null, EndDate datetime not null, EightHrUsage decimal(8,2) not null, scheduledCases int not null, totalScheduledCases int not null, currentInventory int not null, ShiftInventoryWillRunOut BIT NULL ) DECLARE @uid int , @caseCode char(10) , @CaseCodeTracker char(10) , @scheduledDate dateTime , @shift char(1) , @line char(10) , @cartonCopy nvarchar(20) , @cubeSize int , @CasesRan int , @CartonsPerCase int , @GluedPerSkid int , @CartonsPerSkid int , @TotalGlued int , @TotalFlat int , @StartDate datetime , @EndDate datetime , @EightHrUsage decimal(8,2) , @scheduledCases int , @totalScheduledCases int , @currentInventory int , @ShiftInventoryWillRunOut BIT , @DoneWithThisCaseCode BITDECLARE runningTotalsCursor CURSOR FOR SELECT uid , caseCode , ScheduledDate , Shift , line , CartonCopy , cubesize , CasesRan , CartonsPerCase , GluedPerSkid , CartonsPerSkid , TotalGlued , TotalFlat , StartDate , EndDate , EightHrUsage , CasesScheduled , coalesce(CurrentGluedPieces,0) as CurrentGluedPieces FROM dbo.tblGlueRoomStock ORDER BY caseCode , scheduledDate , shift , lineOPEN runningTotalsCursorSET @totalScheduledCases = 0 --------------------------------------------------------------- -- Perform the first fetch.--------------------------------------------------------------- FETCH NEXT FROM RunningTotalsCursor INTO @uid , @caseCode , @scheduledDate , @shift , @line , @cartonCopy , @cubeSize , @CasesRan , @CartonsPerCase , @GluedPerSkid , @CartonsPerSkid , @TotalGlued , @TotalFlat , @StartDate , @EndDate , @EightHrUsage , @scheduledCases , @currentInventory--------------------------------------------------------------- -- @CaseCodeTracker and @caseCode are now the same.--------------------------------------------------------------- SET @CaseCodeTracker = @caseCodeSET @ShiftInventoryWillRunOut = 0SET @DoneWithThisCaseCode = 0--------------------------------------------------------------- -- Check @@FETCH_STATUS to see if there are any more rows to fetch.--------------------------------------------------------------- WHILE @@FETCH_STATUS = 0BEGIN ---------------------------------------------------------------------- -- First loop @CaseCodeTracker and @caseCode are equal. -- Check it every loop. -- When it changes reset the count of @totalScheduledCases to zero. ---------------------------------------------------------------------- IF @CaseCodeTracker <> @caseCode BEGIN SET @totalScheduledCases = 0 SET @CaseCodeTracker = @caseCode SET @DoneWithThisCaseCode = 0 END ---------------------------------------------------------------------- -- Increment @totalScheduledCases for the given casecode. ---------------------------------------------------------------------- SET @totalScheduledCases = @totalScheduledCases + @scheduledCases ---------------------------------------------------------------------- -- Set a flag to indicate the first shift where the inventory will be -- depleted. ---------------------------------------------------------------------- SET @ShiftInventoryWillRunOut = CASE WHEN ( @DoneWithThisCaseCode = 0 AND @currentInventory <= @totalScheduledCases ) THEN 1 END IF @ShiftInventoryWillRunOut = 1 BEGIN SET @DoneWithThisCaseCode = 1 END INSERT INTO @report(uid, caseCode, CartonCopy, scheduledDate, shift, line, scheduledCases, cubeSize, casesRan, CartonsPerCase, GluedPerSkid, CartonsPerSkid, TotalGlued, TotalFlat, StartDate, EndDate, EightHrUsage, totalScheduledCases, currentInventory, ShiftInventoryWillRunOut) VALUES (@uid, @caseCode, @cartoncopy, @scheduledDate, @shift, @line, @scheduledCases, @cubeSize, @casesRan, @CartonsPerCase, @GluedPerSkid, @CartonsPerSkid, @TotalGlued, @TotalFlat, @StartDate, @EndDate, @EightHrUsage, @totalScheduledCases, @currentInventory, @ShiftInventoryWillRunOut) ------------------------------------------------------------ -- This is executed as long as the previous fetch succeeds. ------------------------------------------------------------ FETCH NEXT FROM RunningTotalsCursor INTO @uid , @caseCode , @scheduledDate , @shift , @line , @cartonCopy , @cubeSize , @casesRan , @CartonsPerCase , @GluedPerSkid , @CartonsPerSkid , @TotalGlued , @TotalFlat , @StartDate , @EndDate , @EightHrUsage , @scheduledCases , @currentInventoryENDCLOSE runningTotalsCursor DEALLOCATE runningTotalsCursor SELECT *FROM @reportORDER BY caseCode , scheduledDate , shift , lineSET NOCOUNT OFF [/code]

SSIS how to delete or empty out destination xls files for futre runs

adam spencer — Fri, 13 Nov 2009 08:47:38 GMT

SSIS how to delete or empty out destination xls files for futre runsfirst time I create the Excel desination i choose create new, but after the first run , i will create new again.I want to set up a step before it runs to moveRename old Xls files, then it runs extracts data from sps in sql server and dumps then into newly created xls file. how to do this.thanks in advance

Something Strange happend in SSIS....

only4mithunc — Wed, 11 Nov 2009 06:41:30 GMT

Hi friends,For my application I need to extract data to flat file, the data is the result of a stored procedure execution. When I used OLEDB as the source with SQLCommand as data access mode , after mapping the parameter the columns are not showing. This happened when I used an existing SP.The same when I tried by creating one new sp its shows the columns...What could be the reason... ? I'm sure that I have given the parameters in the same order and same as in the SP.Regards,MC

T-SQL lookup of dimension incidentally fails

giovi — Sat, 14 Nov 2009 17:21:10 GMT

Within a small project I'm performing lookups of dimension surrogates using T-SQL lookups SK=surrogate, BK= business keySK_Organization BK_Organization Rowstartdate Rowenddate-1----------------NULL-------------01-01-2001----NULL1-----------------5----------------01-01-2004----NULL2-----------------6----------------01-01-2004----NULL(the -1 represents the unknown value , when a BK value doesn't exist, for instance 102, the t-sql lookup simply uses coalesce(@result,-1) in order to assign the unknown. )Next to this I have a staging table which contains the business key.Within the staging I assign the surrogate at SK_Organizationkey using a stored procedure, which calls the lookup function and is filled with the business key.When loading large datasets, some rows, without a pattern, are associated a -1 value at the SK while having a valid business key.When taking the business key of the invalid row, and executing it in the lookup function, without the procedure, a valid value occurs rather than the invalid -1.So...function is correct.I could give you all the programmation , but as having testing this about 10 times I can tell you there's something going on in the configuration, and I hope someone over can help me.Or..I should use some hint in the T-SQL..? I've already put it serializableThink of "large datasets", "update statement", "T-SQL lookups", "assign invalid values incidentally"(I wished the text editor would be somewhat more modern over here so we could use tables)

Fonts change when exporting from SSRS2005 to PDF

Gunjan — Thu, 12 Nov 2009 14:54:00 GMT

I am faced with a very strange issue when attempting to export a SSRS2005 report to PDF format. If I export the report to a PDF from the preview in VS2005 the (Calibri) font appears correctly in the exported file. When I deploy the report to the report server and open the report the Calibri font appears as it should but when I choose to export the report to PDF the font in the exported PDF file appears as MS San Serif. I have also installed the Calibri font on reporting server.Any help in resolving this issue would be greatly appreciated.Thanks.

PDF to RDL converter.

Gunjan — Thu, 12 Nov 2009 14:57:21 GMT

I have an pdf document which contains almost 10 pages. I have to generate the SSRS report of the same PDF. Is there a way to convert the PDF document to RDL report? There is no database interactions associated.Thanks.