How do I combine several SSIS packages into one package?
Situation:
Every day company x takes snap shots of 15 tables (.cvs files) and then zips the .csv filse into one zip archive and posts them on an FTP site. I want to load that data into my database and so I have developed several packages to accomplish that.
I first developed a package that unzips the zip archive and loads the 15 .csv files into a local folder. I then developed a package for each .csv file which extracts them from the folder and the loads them into my database (15 separate tables). All the packages work well separately, but now I need to combine them together.
How do I get the packages to automatically execute together once a day? I am assuming that I first need to get combine them together¦
I've run into a problem with SSIS packages wherein tasks that write or copy files, or create or delete directories, quit execution without any hint of an error nor a failure message, when called from an ASP.NET 2.0 application running on any other machine than the one where the package was created from. By all indications it appeared to be an identity/permissions problem.
Our application involves a separate web server and database server. Both have SQL Server 2005 installed, but the application server originally only had Integration services. The packages are file system-deployed on the application server, and are called using Microsoft.SqlServer.Dts.Runtime methods. For all packages that involve file system tasks, the above problem occurs.
When the above packages are run using the command prompt (either DTEXEC or DTEXECUI) the packages execute just fine. This is expected since we are using an administrative account. However when a ShellExecute of the same command is called from ASP.NET, the same problem occurs.
I've tried giving administrative permissions to the ASPNET worker process user to no avail.
I have likewise attempted to use the SQL Server Agent job approach but that approach might not be acceptable for our clients since it means installing SQL Server 2005 Database services on the application server.
I have read the relevant threads in this forum, namely http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1044739&SiteID=1 and http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=927084&SiteID=1 but failed to find any solution appropriate for our set up.
We manage some SSIS servers, which has only SSIS and SSIS tools installed on them and not the sql server DB.
SSIS packages and configuration files are deployed on a NAS. We run the SSIS packages through DTEXEC by logging in to the server.
We want to allow developers to run their packages on their own on the server, but at the same time we dont want to give them physical access on the server i.e we do not want to add them into RDP users list on server properties. We want them to allow running their packages remotely on the server.
One way We could think of is by using powershell remoting and we are working on that. But is there any other way or any tool already present for the same.
I can use the IN with the WHERE clause as example:SELECT * FROM NORTHWIND WHERE LASTNAME IN ('FULLER','KING')I want to use the IN and LIKE at the same time:SELECT * FROM NORTHWIND WHERE LASTNAME LIKE ('A%','B%')I know this is a simplistic example, but the members for the IN will bemany, 5 to 10.I'm trying to avoid:SELECT * FROM NORTHWIND WHERE LASTNAME LIKE 'A%' OR LASTNAME LIKE 'B%'OR LASTNAME LIKE 'FU%' OR LASTNAME LIKE 'JON%' <...>and so forth.Any Ideas?TIARob
I have a table with multiple rows with the same ID. a) How do I combine all columns into one row with the same ID? b) Is this better to do the combine in the store procedure/trigger or a sub that involked by some sort of datarepeater, datagrid controls? Since I am trying to brush up my sql. I appreciate any examples on top of conceptual solution.
Combine 3 Databases, Not tables. Let me spell this out -- I have 3 databases (they are in isolation when in use, its a field app) that need to be merged into 1 "masterDB" database. I've discovered I can use something like this to get to each DB in a query... 1 USE [database1]2 SELECT table1.Name, table1.Location, table1.Date, table2.Blog3 FROM table2 INNER JOIN4 table1 ON table2.ID = table1.ID5 ORDER BY table1.Date and then just repeat for database2 and database3. Ok, fine, rah rah. My question is how do I "merge" all of these into 1. No data on each db will be identical, at all, ever so that is not a concern. I just need all the data from db1, 2 and 3 into masterDB. Ideas? Direction?
Hello, I have these variables on my page: userid = "10101" RequestHost = "example.com" What would be the best way performace wise to first check if the userid 10101 exists in my sql server db. If it does exist I would then need to check if "example.com" exists for the userid in the userdomains table. If these both exist I would then like to query some additional data. I was hoping its possible to combine this into one query somehow. I dont think this is the best solution: sqlcommand.CommandText = "SELECT UserId From Users Where UserID = '10101'" Conn.Open() dr = sqlcommand.ExecuteReader if dr.hasrows then sqlcommand2.CommandText = "SELECT UserDomain From UserDomains Where UserID = 'example.com'" dr2 = sqlcommand2.ExecuteReader if dr2.hasrows then sqlcommand3.CommandText = 'Select Additional Data dr3 = sqlcommand3.ExecuteReader 'read values conn.close else conn.close 'do something end if else conn.close 'do something end if Thanks Very Much!
I have an stored procedure that returns 3 columns. Month, Date, and Total Number of Calls. Here is the stored Proc: SELECT DATEPART(mm, CALLSTARTTIME) , DATEPART(dd, CALLSTARTTIME), COUNT(*) FROM CALL_LOG_MASTER WHERE (COMMERCIALS = '1') AND (PINCODE IS NOT NULL) GROUP BY DATEPART(mm, CALLSTARTTIME), DATEPART(dd, CALLSTARTTIME) ORDER BY DATEPART(mm, CALLSTARTTIME), DATEPART(dd, CALLSTARTTIME) It returns a table: MONTH DATE TOTAL NUMBER OF CALLS======= ===== ===========1 1 10 1 2 15 My question is: is it possible to combine the Month and Date column into one column. e.g. Date Total Number of Calls==== ==============1/1 101/2 15 Please Help, Thanks in advance :)
I am looking for the correct T-sql statement. I want to put parameters in a SP from a select statement. And make the SP exec for each records that the select statement returns. The following is the SP that I want to pass the parameters.
-- Create the CDONTS NewMail object. EXEC @hr = sp_OACreate 'CDONTS.NewMail', @object OUT IF @hr <> 0 GOTO ObjectError
-- Add the optional properties if they are specified IF @Body IS NOT NULL BEGIN EXEC @hr = sp_OASetProperty @object, 'Body', @Body IF @hr <> 0 GOTO ObjectError END
IF @Cc IS NOT NULL BEGIN EXEC @hr = sp_OASetProperty @object, 'Cc', @Cc IF @hr <> 0 GOTO ObjectError END
IF @Bcc IS NOT NULL BEGIN EXEC @hr = sp_OASetProperty @object, 'Bcc', @Bcc IF @hr <> 0 GOTO ObjectError END
IF @HTMLFormat <> 0 BEGIN EXEC @hr = sp_OASetProperty @object, 'MailFormat', 0 IF @hr <> 0 GOTO ObjectError END
-- Loop through the ; delimited files to attach CREATE TABLE #FileExists (FileExists int, FileIsDir int, ParentDirExists int)
WHILE isnull(len(@Attachments),0) > 0 BEGIN SELECT @StrEnd = CASE charindex(';', @Attachments) WHEN 0 THEN len(@Attachments) ELSE charindex(';', @Attachments) - 1 END SELECT @Attachment = substring(@Attachments, 1, @StrEnd) SELECT @Attachments = substring(@Attachments, @StrEnd+2, len(@Attachments))
-- Ensure we can find the file we want to send. DELETE #FileExists INSERT #FileExists EXEC master..xp_fileexist @Attachment
IF NOT EXISTS (SELECT * FROM #FileExists WHERE FileExists = 1) BEGIN RAISERROR ('File %s does not exist. Message not sent.', 16, 1, @Attachment) RETURN 1 END
I have two tables in MS SQL 2000 that I need to combine into one. they will share 3 columns and the rest will stay the same. the existing tables are very large and I REALLY don't want to plug in all the data by hand...Any nifty tricks??? I found software but dont want to spend $$ on it.
Here is my dilemma, i'm trying to combine the results of two different tables. Both tables are very similar but the data returned must be sorted before they are combined because I'm only returning the top xx records based on a hits column.
I have tried the following union query which combines the two RS's then sorts the data: SELECT Top 2 ID, Song, Hits FROM Table1 UNION SELECT Top 2 ID, Song, Hits from Table2 Which would return the first two records from each then sort them like this: 2 - tb1SONG 2 - 1459 1 - tb2SONG 1 - 412 1 - tb1SONG 1 - 356 2 - tb2SONG 2 - 85
I would like to sort based on the hits column then combine the RS producing this: 3 - tb2SONG 3 - 2035 2 - tb1SONG 2 - 1459 4 - tb1SONG 4 - 965 5 - tb2SONG 5 - 745
Any ideas or solutions will be greatly appreciated. Thanks
SELECT bms_id,email_address,COUNT(*) INTO #temp FROM emp_db WHERE email_address IS NOT NULL GROUP BY bms_id,email_address ORDER BY bms_id DESC,COUNT(*) DESC
SELECT bms_id COUNT(*) FROM #TEMP GROUP BY bms_id ORDER BY COUNT(*) DESC
How can i put these two statements into a single sql statement.
I need to combine to sql queries. Separately they work fine, but I need the "total qty" from second query put into the first query
Query 1
SELECT dbo.Job.CompanyJobId, dbo.Job.Name, dbo.Region.CompanyRegionID, dbo.Job.Active, dbo.Job.ChangeDate FROM dbo.Job LEFT OUTER JOIN dbo.Division ON dbo.Job.DivisionGuid = dbo.Division.DivisionGuid LEFT OUTER JOIN dbo.Region ON dbo.Job.RegionGuid = dbo.Region.RegionGuid LEFT OUTER JOIN dbo.JobType ON dbo.Job.JobTypeGuid = dbo.JobType.JobTypeGuid WHERE dbo.job.CompanyJobId = 3505048 ORDER BY dbo.Job.CompanyJobId
Query 2
SELECT case dbo.SourceType.CompanySourceTypeId when 'PR' then SUM(dbo.ProductionEvent.Quantity) end AS Ttl_Qty FROM dbo.Batch INNER JOIN dbo.Event ON dbo.Batch.BatchGuid = dbo.Event.BatchGuid INNER JOIN dbo.Job ON dbo.Event.JobGuid = dbo.Job.JobGuid INNER JOIN dbo.ProductionEvent ON dbo.Event.EventGuid = dbo.ProductionEvent.EventGuid INNER JOIN dbo.Item ON dbo.Event.ItemGuid = dbo.Item.ItemGuid INNER JOIN dbo.Source ON dbo.ProductionEvent.SourceGuid = dbo.Source.SourceGuid INNER JOIN dbo.SourceType ON dbo.Source.SourceTypeGuid = dbo.SourceType.SourceTypeGuid LEFT OUTER JOIN dbo.Product ON dbo.ProductionEvent.ProductGuid = dbo.Product.ProductGuid left outer join dbo.JobNoteEvent on Event.EventGuid = dbo.JobNoteEvent.EventGuid WHERE dbo.Job.CompanyJobId = 3505048 and dbo.SourceType.CompanySourceTypeId = 'PR' GROUP BY dbo.SourceType.CompanySourceTypeId
I have tried this but it doe not work:
SELECT dbo.Job.CompanyJobId, dbo.Job.Name, dbo.Region.CompanyRegionID, dbo.Job.Active, dbo.Job.ChangeDate, Ttl_Qty FROM dbo.Job LEFT OUTER JOIN dbo.Division ON dbo.Job.DivisionGuid = dbo.Division.DivisionGuid LEFT OUTER JOIN dbo.Region ON dbo.Job.RegionGuid = dbo.Region.RegionGuid LEFT OUTER JOIN dbo.JobType ON dbo.Job.JobTypeGuid = dbo.JobType.JobTypeGuid WHERE dbo.job.CompanyJobId = 3505048 and where Ttl_Qty =
(SELECT case dbo.SourceType.CompanySourceTypeId when 'PR' then SUM(dbo.ProductionEvent.Quantity) end AS Ttl_Qty FROM dbo.Batch INNER JOIN dbo.Event ON dbo.Batch.BatchGuid = dbo.Event.BatchGuid INNER JOIN dbo.Job ON dbo.Event.JobGuid = dbo.Job.JobGuid INNER JOIN dbo.ProductionEvent ON dbo.Event.EventGuid = dbo.ProductionEvent.EventGuid INNER JOIN dbo.Item ON dbo.Event.ItemGuid = dbo.Item.ItemGuid INNER JOIN dbo.Source ON dbo.ProductionEvent.SourceGuid = dbo.Source.SourceGuid INNER JOIN dbo.SourceType ON dbo.Source.SourceTypeGuid = dbo.SourceType.SourceTypeGuid LEFT OUTER JOIN dbo.Product ON dbo.ProductionEvent.ProductGuid = dbo.Product.ProductGuid left outer join dbo.JobNoteEvent on Event.EventGuid = dbo.JobNoteEvent.EventGuid WHERE dbo.Job.CompanyJobId = 3505048 and dbo.SourceType.CompanySourceTypeId = 'PR' GROUP BY dbo.SourceType.CompanySourceTypeId)
I have a query that looks at the stock levels in one warehouse and returns the quantity, have been asked to create a new column that shows the total of the same stock that is available in our two other warehouses.
Have tried this: SELECT ItemCode, WhsCode, InStock FROM TABLE1 INNER JOIN TABLE2 ON TABLE1.WhsCode = TABLE1.WhsCode WHERE WhsCode = '31' or WhsCode = '61' GROUP BY InStock, WhsCode,ItemCode
This returns the results in one column rather than in a seperate column for 31 & 61, I then need to add the two columns together so a total stock is shown.
I realise this may be a basic query but I'm batting my head against a wall at the moment.
I have two statements getting data from two different tables.
1: select dataDate from table1,- gives β20080325β
2: select count (recordNumber) from table2,-- gives β4566β
Each table has one row but more than one column. The columns are not the same in each table.
Question. I need to combine two statements to give the outcome in one row as '200803254566'. '2008032' from first statement and '54566'from second statement. How do you combine them? ?
I wonder if anyone can help. All I am trying to do is combine three SQL SELECT queries into one. They are:
SELECT COUNT(ISNULL(Result, 0)) AS Win FROM Games WHERE (CurrentSeason = 'True') AND Result = 'Win'AND ([MatchType] = @MatchType)
SELECT COUNT(ISNULL(Result, 0)) AS Lose FROM Games WHERE (CurrentSeason = 'True') AND Result = 'Lose'AND ([MatchType] = @MatchType)
SELECT COUNT(ISNULL(Result, 0)) AS Draw FROM Games WHERE (CurrentSeason = 'True') AND Result = 'Draw'AND ([MatchType] = @MatchType)
As you can see they are all doing pretty much the same thing. I have experimented by using GROUP and HAVING but end up with no results. Sorry if this is obvious but I am new to SQL!
I have a table that has two dates in it, a date opened and a dateclosed. I would like to create one query to give me the number ofrecords that have been opened each month plus, and this is the hardpart the number of those records that have been closed each month. Ican get the result with two seperate queries but have been unable toget it combined into one query with three values for each month, i.e.,the month, the number opened and the number of those that were openedin the month that have been subsequently closed.Here's my two queries. If anyone can help I'd appreciate.SELECT COUNT(*) AS [Number Closed], LEFT(DATENAME(m, DateOpened),3) + '' + CAST(YEAR(DateOpened) AS Char(5)) AS [Month Opened]FROM tableWHERE (DateClosed IS NOT NULL)GROUP BY CONVERT(CHAR(7), DateOpened, 120), LEFT(DATENAME(m,DateOpened), 3)+ ' ' + CAST(YEAR(DateOpened) AS Char(5))ORDER BY CONVERT(CHAR(7), DateOpened, 120)SELECT COUNT(*) AS [Number Opened], LEFT(DATENAME(m, DateOpened),3) + '' + CAST(YEAR(DateOpened) AS Char(5)) AS [Month Opened]FROM tableGROUP BY CONVERT(CHAR(7), DateOpened, 120), LEFT(DATENAME(m,DateOpened), 3)+ ' ' + CAST(YEAR(DateOpened) AS Char(5))ORDER BY CONVERT(CHAR(7), DateOpened, 120)TIABill
I have the following 3 SQL statements that need to be combined, ifpossible. The output of one feeds the input of the next. I need to viewall of the defined output fields (the output needs to be used in aCrystal Report).The SQL Follows:/* Input is ISBN (vendor_part_number) */QUERY_1 - returns 1 recordselect p.product_id, v.name, m.description, p.author, p.title,p.revision_number, p.copyright_edition, p.vendor_part_number,p.conforming_flag,m.code, mp.unit_price_product, mm.quota_pricefrom T_PRODUCT p, T_VENDOR v, T_PRODUCT_VENDOR pv,T_MULTILIST_PRODUCT mp, T_MULTILIST m,T_MULTILIST_MEMBERSHIP mm where/* p.vendor_part_number == input */p.vendor_part_number = '0153364475' and p.medium_type ='TEXTBOOK' andp.product_id = pv.product_id and pv.type = 'CONTRACT' andpv.vendor_id = v.id andp.product_id = mp.product_id andm.code = mp.multilist_code and m.proclamation_year =mp.proclamation_yearand m.proclamation_seq_id = mp.proclamation_seq_id andm.code = mm.multilist_code and m.proclamation_year =mm.proclamation_yearand m.proclamation_seq_id = mm.proclamation_seq_id/* The above should return a single record */QUERY_2 - returns 2 recordsselect p.product_id, p.consumable, p.title, p.copyright_edition,p.vendor_part_number, p.product_type,p.item_type, p.hardware_requiredfrom T_PRODUCT p, T_PRODUCT_RELATION pr where (pr.relationship_type ='AID'or pr.relationship_type = 'KIT') andp.product_id = pr.child_product_id and pr.parent_product_id =90321/* 90321 = result from above: pr.parent_product_id = p.product_id*/QUERY_3 - returns 18 recordsselect p.product_id, p.consumable, p.title, p.copyright_edition,p.vendor_part_number, p.product_type,p.item_type, p.hardware_requiredfrom T_PRODUCT p, T_PRODUCT_RELATION pr where (pr.relationship_type ='AID'or pr.relationship_type = 'KIT') andp.product_id = pr.child_product_id and pr.parent_product_id in(90322, 90323)/* 90322, 90323 = result from QUERY_2: pr.parent_product_id =p.product_id */Only 21 records are returned from these combined queries. I need accessto all of them even though there are 3 different resultsets, 2 of whichcontain the same fields. Is there a way to simplify this into a storedprocedure or a view that can take 1 input parameter? It needs to beused in a Crystal Report, which is limited in its handling of thesetypes of complex queries.
Hello,I am working with a database that is in use in several locations.The structure of the databases are identical, but the data isdifferent.I am tasked with combining the data into one large database.(It is SQL 2000)I have .bak files for each location.Is there any way to restore a backup into a database, combining thedata?If not, what is the best way to accomplish this?Thanks,Rodjk #613
I'm sure this has been brought up many times, but I will ask anyway.Let's say I have 2 tables related:Owner:---------o_ido_nameDog:---------d_idd_nameo_id - for Owner table.If the data is laid out aso_id o_name1 Johnd_id d_name o_id1 Skippy 12 Fido 1How can I make a query that will produce the following results:o_id o_name owned dog names1 John Skippy, FidoI think it has something to do with unions but I can't seem to get it. I'musing SQL Server Compact Edition.
Hi!I have a table looking like(username) (account number) (start date) (end date) (product)wich I can have up to 4 lines for the same client.I wist to transfert those lines into a new table looking like(username) (account number) (start date 1) (end date 1) (product 1)(start date 2) (end date 2) ... (product 4)How (in SQL) I could do it?
Hi guys..is there any query to do this action:i want to combine view record into a single record.exm.table 1Name A BJack 10 22jack 12 21jack ... ....jack 1 11ben 12 2ben 3 2ben ... ...into:View 1Name combinejack 10,22 and 12,21and1,11 and .....ben 12,2 and 3,2 and......thx before..
Hi,I have 2 queries that I need to join. I have the following tables:attendancelog :headeridreportmonthattlogstuds:headeridsidclass:sidclassstatusyearcodelogdatecdatemidThe result must return all the classes that appear in query2 but notin query1.I am not sure how to join the 2 queries.Help will be appreciated :-)ThanksQUERY1select sid fromattlogstuds studsinner joinattendancelog attlogon studs.headerid=attlog.headeridwhere reportmonth=10query2-- students learning excl. studs left before 1th oct.select class.SID from classleft outer JOIN ( select * from class where yearcode=26 and status=6and ( logdate <'20041001' or CDate< '20041001' )) c6 ON c6.sid = class.sid and c6.mid=class.midand c6.cdate >= class.cdatewhere class.yearcode=26 and class.status in (3,5) andclass.cdate<'20041101' and c6.sid is null
I have a problem that need some help from expert.Is there any way I could combine many rows into a row in Access using Visual Basic. I want to change the below table from TABLE A to TABLE B
TABLE A
SampleCode Test Name
Result ID Name Sex
9300105 Peripheral Blood Film ....
a few poikilocytes are present. S7585512E DHANDAPANI MAHESH M
9300105 Peripheral Blood Film ....
No blast cells seen. S7585512E DHANDAPANI MAHESH M
9300105 Peripheral Blood Film ....
microcytes, elongated cells and S7585512E DHANDAPANI MAHESH M
9300105 Peripheral Blood Film ....
hypochromic but normocytic: . Some S7585512E DHANDAPANI MAHESH M
9300105 Peripheral Blood Film ....
Majority of rbcs appear slightly S7585512E DHANDAPANI MAHESH M
Output:
TABLE B
SampleCode Test Name
Result ID Name Sex
9300105 Peripheral Blood Film ....
a few poikilocytes are present, No blast cells seen.microcytes, elongated cells and hypochromic but normocytic. Some Majority of rbcs appear slightly S7585512E DHANDAPANI MAHESH M
I have a Excel file as data source. On sheet 1 there are some header information. On sheet 2 are a quiet simple data table. Both sheets will be read into memory with SSIS.
Now i want to combine some header information with the data table.
This should be result: 34¬, 56¬, 12¬, Unit A, 3/20/2008, Group1 43¬, 43¬, 12¬, Unit A, 3/20/2008, Group1 32¬, 42¬, 13¬, Unit A, 3/20/2008, Group1 ...
