Hi,
My stored packages folder under my Integration service is empty. Its missing msdb and the file system folder
However, when I execute select * from [sysdtspackages90] it returns rows.
So, whats the story? Why can't I see my packages in the GUI?
Please help, I have achived almost nothing this week.
SQL Server 2005, 64bit, 2-node cluster.
Thanks,
I am able to connect to Integration Services in MSSMS. However, when I try to expand and click on MSDB under Stored Packages, I get a Login Timeout expired error. I get this error both locally, and remotely. Error message suggested "does not allow remote connections", but I checked Surface Area Configuration, and remote connection is already enabled. Moreover, I get the same error even when connecting locally.
any ideas?
Hi,
newbie to sql2005. I am trying to write a VB.NET program to list all ssis packages we have stored in a file folder (not in MSDB) where both SqlServer and ssis servers are located. These packages currently running on daily basis by automated jobs with no problem.
I have found the following link on the web that includes VB.Net program. If you scroll down under Example (SSIS Package Store) , you'll see a VB.NET program that I am trying to get it to work for me but keep getting error message like "Cannot find folder......"
http://msdn2.microsoft.com/en-us/library/ms403343.aspx
My goal is to:
1. List all ssis packages stored in the file systems (.dstx)
2. List DB source and distination providers(tables in/out) in a ssis package
3. List properties for DB providers
4. List FlatFile & Excel file provider properties in a ssis package.
My assumption, but not sure, is that I may need to register this file folder (where packages are located) to the SqlServer if that is not done already. Any command or sys table in Sql2005 to figure this out?
I would appreciate any help on this in advance.
Thank You.
I have an interesting problem. I am unable to see any database triggers in the Database Triggers folder under programmability in SQL 2005. I have been able to create and alter triggers in the database that function exactly as their supposed to, yet they never show up as visible in the Triggers folder. I would love for one of the luminaries on this forum to share some wisdom with me and let me know what setting is the culprit. It is driving me batty.
Thank you all in advance.
Hi,
We patched our servers last week and now, when connecting to our server remotely via Enterprise Manager (with DBO permissions), the management folder shows as empty. My guess is its permission related but, I'm not sure where to begin to correct the issue since we aren’t sure how it happened. Any help will be greatly appreciated.
~des
Is it possible to create a folder within the MSDB folder in Integration Services and deploy packages in it?
Hi,
My Problem goes Like this....
I have a folder which contains all the flat files which are used by all the packages(ex--flat file connection managers) in my project.
If we want to change the name of the folder,have to change in every package( in all connection managers) manually.It looks hardcoding and timetaking.
Is there any way to change in one place(xml,file,variable) so that it should be affected in all the packages.
one more doubt is..
If we configure the flat file connection manager in package configurations,configuration file (ex-xml)will be created (we can make changes in that file regarding that connecion mgr only.)
But i want one configuration file (ex--xml) so that i should configure the details of all the connection managers used in all packages.
I have created for each container to call all the packages in a folder like below, also created a variable.
Then I add execute package task inside of foreach container and selected file system in a location and in connection called currently creating package name finally in connection properties i added variable in expression which i created and mapped into for each loop container. I referred below linkÂ
[URL] ....
All the packages are running but its not ending once all the packages executed its re run and continue the running process, how to stop once all the packages execute.Â
Please can anybody help me in transferring existing SSIS Packages saved in a shared folder location from development server 2ED to Live server TWD1.
Both has SQL server 2005 running and has visual studio 2005
Currently about 25 SSIS packages are executed from the development server transferring data on Live server TWD1...these ETL process is called from development server but executed on live server.
Now the problem is when i call these packages from the shared folder from live server it crashes.....i need to changes something to shift the whole package to the live server..and execute on live server itself instead of recreating the whole 25 process from scratch.....also i use optimize for many tables ..and run in a single trancastion....so how can i see the mappings of source and destination tables.
Please let me know the process how i can achieve this.
Thanks
George
Hi folks,
I've been learning how to build an ETL package in MS SQL 2005 using the MSDN turorials. I wanted to deploy the package and followed the procedures (Created a deployment bundle) and then ran the package install wizard. I am now stuck as I cannot view the installed packages thru MS SQL Server Management Studio and obviously cannot use them. I should be able to see a "Storage Packages" folder in the "Object Explorer" window but it isn't available. Any idea why I cannot see this folder ? I know my packages are installed as I can see them in the system table called "dbo.sysdtspackages90". The " Integration Services" service is also started. Not sure what else I can check.
Thanks,
Steph
Is there a way I can add all my stored procedures into a folder of my chosen name Inside the Programmability->Stored Procedures tree structure.
Thanks
Hi, I have created a store procedure and want to save them. this is wha ti did.
I clicked on the Northwind--> Programm--> Stored Procedure-- > right clicked --> new stored procedure
after writing my stored procedure I saved it. But when I am saving I want to save it under the Stored Procedure folder
of the NorthWind database. I guess this is what it should do. When I use to use the sql 2000, i could always send my stored procedure here , i guess it was by default. But even if i save my sql stored procedure at any other location than at the run time, how the applicaiton will find my store procedure.
Well so thinking of that I have to save my stored procedure under the database for which table it is created however, i can not save it there. I tried to figure out the directly but I couldnt.
So any suggestion????
Hi, I am wondering how can we return each file in a folder ? I am trying to get each file in a folder to perform other job. Below is the description ...
while folder is not empty
foreach file
run the job
end for
end while
Greetings! This is my first ever post here, so please be gentle.
I have a stored procedure that will be accepting many parameters (around 10). Any number of them can be empty (empty with a length of zero, but probably not Null per se).
How do I do a Select... Where... where if the parameter is empty it ignores it in the 'Where' but if the parameter had anything in it, it becomes part of the filter?
Thanks!
In the code below, the statement 'Print @Sites' prints nothing, eventhough the cursor contains 4 records, and 'Print @Site' prints theappropriate values. Can anyone see my mistake? I am attempting toprint a delimited string of the values referred to by @Sites.Thanks.Dan FishermanDECLARE SiteCursor CURSORGLOBALSCROLLSTATICFOR SELECT OfficeName FROM ClientOffices WHERE ClientID=12 ORDER BYOfficeNameOPEN SiteCursorDECLARE @Sites varchar(1000)DECLARE @Site varchar(100)FETCH NEXT FROM SiteCursor INTO @SiteWHILE @@FETCH_STATUS=0BEGINprint @SiteSET @Sites = @Sites + ', ' + @SiteFETCH NEXT FROM SiteCursor INTO @SiteENDPRINT @SitesCLOSE SiteCursorDEALLOCATE SiteCursorGO
View 4 Replies View RelatedI dont alot about sql server 2005(Express edition). For debugging purposes i want to copy the whole app_data folder(.mdf & .log files) on the production server to another folder on the same machine(or sometimes to a network folder). So when i copy and try to paste this App_data folder to a new location, i get this error message
"cannot copy ASPNETDB: it is being used by another person or program. close any programs that might be using the file and try again."
After reading the above message, i close visual web developer, stop the website in IIS and stop the SQLExpress service on the server and try again but still get the same message.
So how can i make sure that all the programs accessing these database files are closed such that i'm able able to copy them to a different location.
I know a WMI event watcher can be used to watch for a new file being added to a folder. However, I need to check for new folders being added to an existing folder. I haven't been able to find a post on doing this. Is there a way in WQL to check for a new folder being added instead of a new file? I've used SQL for years, but am new to SSIS.
View 2 Replies View Related
Ok,
I have a network folder called A
I have a SharePoint (2007) Document Library site called B. Web Client is enabled on the server and B is mapped as a Drive (let's call it Y for this discussion)
I want to move documents in A to B. Easy enough, right? Not so....
I first started by creating a batch file that issues a COPY \A \Y /Y at the command prompt. Viola! Worked Great!
I then moved that command to a SQL Agent job as a CMDExec statement (exact same statement) and attempted to run it.....CRASH! It found the files in A but then said "The system cannot find the path specified"
Ok, so I tried it in SSIS. CRASH! Checked the error log. Same thing...
So I then checked the account under which the SQl Agent was running (special domain account for all our SQL Servers). Thinking it might mater I changed it to run under my name (I'm in Domain admin). I also ensured I had permissions to the SPS 2007 library as well. (I did).
Ran again! CRASH! Same error....
So, I created a batch file , placed thec ommand in the batch file and ran that from the command prompt! Viola! Worked Great.
So, I was thinking of how ingenious I was as I pasted my C:RootCopy.bat into my SQL Agent job. With a big grin on my face I right clicked and picked "Start Job at step".......CRASH! Same error.
Does anyone have any ideas on this ???????????????
Thanks,
Stephen
hi, im new to this site so i don't know if i'm posting in the correct forum. anyway, this is my code:---Dim dbMac As DBLibrary = Nothing
dbMac = New DBLibrary(General.GetMACConnectionString)dbMac.OpenConnection("SPR_STAFFMAIN_GETEMPLOYEERECORDS")dbMac.CreateParameter("@USERENTITYID", GetUserEntityID(), Data.SqlDbType.Int)drpEmpNumbers.DataSource = dbMac.GetDataView("StaffMaintenance")gridStaffMaintenance.DataSource = dbMac.GetDataView("StaffMaintenance")gridStaffMaintenance.DataBind()---DBLibrary is a class that opens a connection to the SQL server. i'm getting an empty grid even though the stored procedure returns a row when i test it in the analyzer. is there to debug or test this code? thanks!
Hi
I have a stored procedure some thing like this..
When I pass empty strings to both the parameters ..it is
returning all the rows from the table.
IF I pass both empty strings to @LLASTNAME_I Char(21),
@DEPARTMENTCODE_I char(7),it should not select any rows from the table.. can any one suggest me as to how to accomplish this..
CREATE procedure Ceb_Phone_Book
@LLASTNAME_I Char(21),
@DEPARTMENTCODE_I char(7)
AS
Select
EM010132.DEPARTMENTCODE_I,
DEPARTMENTNAME_I,
LLASTNAME_I,
FFIRSTNAME_I,
EM010132.WORKPHONE_I,
EM010132.MSTRING_I_5
FROM
EM010132 INNER JOIN HR2DEP01
ON HR2DEP01.DEPARTMENTCODE_I = EM010132.DEPARTMENTCODE_I
WHERE
INACTIVE = 0 AND
LLASTNAME_I LIKE LTRIM(RTRIM(@LLASTNAME_I)) + '%' AND
EM010132.DEPARTMENTCODE_I LIKE LTRIM(RTRIM(@DEPARTMENTCODE_I)) + '%'
ORDER BY
LLASTNAME_I,FFIRSTNAME_I
Thanks
VENU
I have a set of folders with the following structure:
MainFolder
1999
SpreadsheetA.xls
SpreadsheetB.xls
SpreadsheetC.xls
2000
SpreadsheetA.xls
SpreadsheetB.xls
SpreadsheetC.xls
etc.
Is there a way that I can use the foreach loop container to loop the sub folders? My plan was to get the folder name and path into a variable, use this to build the connection string for each file in this folder, carry out the upload for that folder, then move onto the next folder and do the same thing, but I cannot see a way to do this.
Thanks,
Sam
I've built a simple VS2005 ASP.Net web app that uses Crystal Reports for generating assorted reports. More specifically, when a report is called it executes the correct SQL Server Stored Procedure and returns a Data Table populated with with appropriate data. In my testing, everything seemed to be working fine.But I just did a test where I pressed the "Submit" button on two different client browsers at almost the same time. Without fail, one browser returns the report as it should but the other one returns an empty report; all of the Crystal Reports template info is there but the report is just empty of data. Considering that each browser is running in its own session, I'm confused about why this is happening.One thing: I did login as the same user in both cases. Might this be causing the problem?Robert W.Vancouver, BC
View 7 Replies View RelatedIf a sqldatasource is programed to send textbox1.text to a stored procedure, and the .text property is left empty, and there is no default value set for the parameter, what exactly is the stored procedure receiving?I would like to run a IF BEGIN statement on the value of the parameter in the stored procedure but the following does not work:IF @Parameter IS NULL BEGINor IF @Parameter = '' BEGINThe only way I've gotten it to work is if I set the default value of the parameter being sent to a specific alphanumeric value. Then do something like:IF @Parameter = '99' BEGIN<Code Here>END
View 4 Replies View Relatedhi everyone,
I have a db based on the Tracking_Schema.sql / Tracking_Logic.sql (find in &windir%/Microsoft.NET/Framework/v3.0/Windows Workflow Foundation/SQL/EN), so after executing both of them I get several stored procedures, especially dbo.GetWorkflows. And I have a solution in VS05 which when executed is filling this stored procedure with Instance-Id´s. My question is: how is the working command (like exec, truncate,..) to empty my st.procedure, not to drop/delete it?
Thanks in advance, best regards
bg
If I deploy an SSIS packages in SQL Server (not in file system).
Where are they stored? Are they stored in a DB? if yes, which one?
Also, is it a best practise to deploy the SSIS package in SQL (I guess it should be because I can have a central place for deploying all package and have one place to load all the packages from).
regards,
Abhishek.
I am having problem with moving a file from one folder to another folder. Here is the detailed scenario:
I want to move a input.csv file from shared input folder to shared archive folder. i am using the below code to do this.
declare @inpath varchar(100)
SET @inpath = 'move "\abcdefINPUTinput*.csv" "\abcdefARCHIVEarchive.csv"'
EXEC @filestatus = master..xp_cmdshell @inpath
but the problem was it was cutting the input.csv file from INPUT folder but not pasting it in the ARCHIVE folder.
I really appreciate if anyone can help me to solve this or anyone can tell some workarounds.
Thanks,
I have a DTS package that I wanted to call from a Coldfusion page. Going through some searches, I found this page which uses three seperate stored procedures to run a DTS packages.
All seems fine and dandy until nothing changes after running the package with coldfusion. I start dumping the cfstoredproc variable, and it turns out that the DTS fails every time it gets to the Excel file. Looking at the logs, the reason is that the JET Database Engine failed creating the file. This doesn't make sense, as the DTS runs fine from within Enterprise Manager.
Is there something in the connection between the stored procedure and the DTS that would cause a problem like this? Might calling the DTS from a stored procedure change the full path that the file looks at?
EDIT:
Here's the exact error I'm given from the DTS logs:
Code:
Step Error Source: Microsoft JET Database Engine
Step Error Description:Failure creating file.
Step Error code: 80004005
Step Error Help File:
Step Error Help Context ID:5003436
Hi, hoping someone may be able to shed some light on this.
I have a stored procedure that conditionally executes different DTS Packages. Everything seems to work, there's no errors at all, but the DTS Packages are never executed. Wondering if anyone has any ideas.
Here's the stored procedure....
CREATE PROCEDURE exportLists
AS
DECLARE @script VARCHAR(8000)
DECLARE @id INT
DECLARE @max INT
select regionid,count(distinct homephone) as phone
into #phone
from tbl_template
Where exportid is null
group by regionid
select officeid,listpath
into #office
from tbl_office
WHERE startdate <= GETDATE()
AND (enddate >= GETDATE() OR enddate IS NULL)
and listpath is not null
and listpath <> ''
Select case when phone > 2500 then '"exportLists" /A MDBFile="' + listpath + '" /A OfficeID ="' + convert(varchar(10),officeid) + '" '
Else '"resetTemplate" /A OfficeID="' + convert(varchar(10),officeid) + '" '
end as cmd
,Identity(int,1,1) as rowno
,phone
into #temp
from #office as O
inner Join #phone as p
on o.officeid=p.regionid
Set @max=@@rowcount
set @id = 1
While @id <=@max
Begin
Select @script='"Dtsrun /S "' + @@SERVERNAME + '" /E /N ' + cmd
+ case when phone > 2500
then '/A ExportID="' + convert(varchar(10),(Select coalesce(Max(listid),0) + 1 from tbl_sf3Lists)) + '" '
else ' ' end
, @id=@id+1
from #temp
Where rowno = @id
End
Return
GO
This question is for a SQL Server 2000 Installation.
I have created Windows Groups for three types of users Admins, Technical, and Standard users. Some of the users in all of the groups attach using Enterprise Manager.
The data base access is right on, also you cannot see logins under the individual databases on the server which is perfect; however, from the Server level, the Security folder is still visible. When using the Technical or Standard, you cannot change anything; however, I would prefer that the Security Folder not be visible. Also the same is true with these two groups for DTS Packages and Stored Procedures. I do not want them to see DTS packages, or Stored Procedures. What is the process for securing these two areas using Windows Authentication?
Hi,
I am writing one program in vb.Net where I need to bind a treeview with all the SSIS packages stored in SQL server MSDB database.
Is it possible to get all the SSIS packages by using T-SQL query or Object model ?
Thanks
Hi All
View 4 Replies View RelatedHi,
I am looking to create a job on our SQL Server 7.0 dB to:
1. Manipulate and move data from table 'a' to table 'b' (via stored proc)
2. Export table 'b' to a comma delimeted, double quote text identifier file (via dts export wizard stored as a DTS )
3. Cleanup table 'b' and end the job.
I am creating a DTS package to execute step 1, then 2 and lastly 3.
How do I execute another DTS package (#2) from within this DTS package?
Also, is there a way I can make the file name for #2 a dynamically generated name (i.e. include date and time in the file name)?
Thank You
Jamie Reis
I have 100 packages all of them stored in "Integration services catalog". Is there a way to execute packages using script task in ssis 2012.
View 5 Replies View Related