Can No Longer Open My SSIS Packages
Apr 18, 2006
I am in the process of migrating DTS 2000 packages via DTS Migration Wizard.
I ran all of the packages through the wizard. Then, I successfully completed changing the remaining issues in two of my packages to SSIS. Everything was fine. The second package I completed is called from a SQL Agent Job. I managed to get the job step converted to use SSIS package. It ran successfully.
Now, I am trying to open other packages to work on them and I get "Microsoft Visual Studio is unable to load this document: Value does not fall within the expected range." and a message window that says "Object reference not set to an instance of an object." This actually happens with the two that I migrated, also. I can no longer access them.
What happened?!?!?!? Does anyone have any experience with this?
View 1 Replies
ADVERTISEMENT
Nov 15, 2007
Is there a way ( using an included SSIS task rather than coding a script task) to detect whether a package has run longer than a specified period of time?
So I can send an email to operators notifying them that a job is taking longer than usual.
Thanks
View 12 Replies
View Related
Jan 9, 2007
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.
Anybody got any idea on how to go about this?
View 33 Replies
View Related
Mar 1, 2005
Hi we have a table with about 400000 records in it. It starting to take longer and longer to add a new record. I was thinking of creating another identical table and archiving off most of the records every month (we are now adding about about 4000 records a day) . Is this the best thing to do?
I don't know a lot about sql server so any help or suggestions would be great
View 4 Replies
View Related
Apr 22, 2015
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.
View 4 Replies
View Related
Nov 10, 2006
Hi,
I installed the dts designer component for 2005 so I could migrate over some 2000 packages and was able to successfully open and run dts packages under 2005.
Then all of a sudden I get this error msg and can't open them, migrate them, export them, etc:
"here were errors loading the package "MyPackageName":
[DNETLIB][ConnectionOpen (Invalid Instance()).] Invalid connection.
(Microsoft.SqlServer.DtsObjectExporerUI)"
That is not a typo, it actually says "here were errors..." instead of "There were errors..."
I've tried the obvious, repair the dts designer component and rebooting, no luck.
Anybody seen this?
Hope this is the right group to post to...
Thx
View 5 Replies
View Related
Jun 6, 2006
Hi,
I created DTS packages in SQL 2k and I want to access them (modify) in SQL 2k5.
But I could not open those in SQL 2k5. Is there any way to open them in SQL 2k5
Thanks,
Geetha
View 1 Replies
View Related
Feb 7, 2006
Does anyone know why this happens? When I run one of the packages in my project (by hitting the play button in the designer), all of the other packages in that project open before it starts running?
Thank you.
View 6 Replies
View Related
Nov 29, 2006
(Applies to SQLServer 2005 SP1)
We have found that using the SSIS "Import and Export Wizard" using the "Microsoft Excel" data source that there appears to be a maximum column length of 255 characters for any row.
Even when defining the destination table columns as nvarchar(4000), the wizard fails with the errors shown below.
We have found no workaround except manually changing the imput data. There doesn't appear to be any "Advanced" options for the Excel importer as there are for the flat-text importer. So, no question here, just posting the bug so that *next* time someone searches the web for an answer, this post comes up
MessagesError 0xc020901c: Data Flow Task: There was an error with output column "English String" (18) on output "Excel Source Output" (9). The column status returned was: "Text was truncated or one or more characters had no match in the target code page.". (SQL Server Import and Export Wizard) Error 0xc020902a: Data Flow Task: The "output column "English String" (18)" failed because truncation occurred, and the truncation row disposition on "output column "English String" (18)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component. (SQL Server Import and Export Wizard) Error 0xc0047038: Data Flow Task: The PrimeOutput method on component "Source - Sheet1$" (1) returned error code 0xC020902A. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. (SQL Server Import and Export Wizard) Error 0xc0047021: Data Flow Task: Thread "SourceThread0" has exited with error code 0xC0047038. (SQL Server Import and Export Wizard) Error 0xc0047039: Data Flow Task: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown. (SQL Server Import and Export Wizard) Error 0xc0047021: Data Flow Task: Thread "WorkThread0" has exited with error code 0xC0047039. (SQL Server Import and Export Wizard)
edit: After searching further this is documented under "Excel Source"
in BOL which provides a registry-based workaround. I guess the issue
is that the wizard considers truncation to be a 'fail' case and
there's no easy way to override this behaviour, specify the column
types nor determine which line is in error)
Truncated text. When the driver determines that an Excel column contains
text data, the driver selects the data type (string or memo) based on the
longest value that it samples. If the driver does not discover any values longer
than 255 characters in the rows that it samples, it treats the column as a
255-character string column instead of a memo column. Therefore, values longer
than 255 characters may be truncated. To import data from a memo column without
truncation, you must make sure that the memo column in at least one of the
sampled rows contains a value longer than 255 characters, or you must increase
the number of rows sampled by the driver to include such a row. You can increase
the number of rows sampled by increasing the value of TypeGuessRows under
the HKEY_LOCAL_MACHINESOFTWAREMicrosoftJet4.0EnginesExcel registry
key.
)
View 21 Replies
View Related
Feb 4, 2008
The following code is taking longer and longer to run. I am not talking about the gradualy increase in size. this job has been taking 30-40 mins normaly and in the last few days it has gone 1hr to 2 hr to 3 hr... ANy ideas why this is happening? I can not see and other jobs running at this time.
declare @filename varchar(255)
set @filename =
(select top 1 physical_device_name
from ****.msdb.dbo.backupset bs, ****.msdb.dbo.backupmediafamily bf
where bs.media_set_id=bf.media_set_id
and database_name = 'Live_PRD'
and backup_start_date>getdate()-1
and type = 'D'
order by backup_start_date desc)
restore database REPORTS_REP
from disk=@filename
with
move 'LIVE_PRD_Data' to 'T:SOUTHREPORTS_REP_Data.mdf',
move 'LIVE_PRD_Log' to 'U:SOUTHREPORTS_REP_Log.ldf',
move 'LIVE_PRD_Log2' to 'U:SOUTHREPORTS_REP_Log2.ldf',
replace, stats=2, recovery
View 5 Replies
View Related
Feb 22, 2008
Why does it take longer and longer for the same code to run very simply I have 8,0000,000 records I want to delete from a table . I have tried a few options
Option 1 a while loop which deletes 10,000 rows per loop starting from the earliest until it hits the cut of number I have set. THIS TOOK 5 HOURS
Option 2 created an SP which found the oldest 100,000 records then deleted them. If I run this SP manually it takes 30 €“ 60 secs. Which I thought was much better than above. So I put this SP in a while loop to run 80 odd times thinking the time it would take would be 80 mins a huge improvement.
But every time this SP is called it takes longer and longer (36,30,32,39,37,37,123,163,155,182€¦and so on(In seconds)).
All the sp is doing is as follows(8860000 is just to insure I don€™t delete to much). this sp is then called from in a while loop.
set @recnumber = (select top 1 recnumber from
(select top 100000 recnumber from TabletodeleteFROM where recnumber < 8860000
order by recnumber asc ) TabletodeleteFROM
order recnumber desc)
delete TabletodeleteFROM where recnumber < @recnumber
please help why is it taking longer and longer
View 8 Replies
View Related
Apr 24, 2006
Hi,
Is there a way to reuse existing Configuration Files in SSIS Packages?
I have two packages with the same connection managers, properties and variables.
I want to reuse the Config file of the 1st package in the second one. While building the packages it gives error like
“Config file already exists cannot recreate”.
View 1 Replies
View Related
Jun 8, 2007
Hi All,
I have created SSIS (.dtsx) files and have stored in different servers.
Now my query is I want to move all dtsx files from filesystem to Sqlserver2005 database how should i do it.
Please help
Regards
Hassan
View 2 Replies
View Related
Oct 16, 2007
I need to create the ssis package in business intelligence developement studio i am need to sqlserver 2005.When i opened the BID studio i am not able to see the integration services packages type..
Please help the steps to design the package.
I have experience of using the 2000 in dts designer mode.
Thnks for your help in advance.
View 1 Replies
View Related
Mar 14, 2008
Hi,
iam New to Packages. So Please give me the guidelines to Learn Packages.
Thanks for Advance
View 3 Replies
View Related
Mar 12, 2007
Hi,
I upgraded to Microsoft SQL Server 2005 Service Pack 2 and now when I run the master SSIS package( that has several packages in it), all the packages run twice.
After removing SP2, they work fine. Any ideas how to make this work with SP2?
View 2 Replies
View Related
Aug 24, 2006
I am writing a vb application that is supposed to let the users set the connection string for the datasources in the package. After new connection strings are entered the application is supposed to run 8 packages in a certain order, but I haven't been able to set a new connection string successfully. Is there a way to programmatically modify the connection string of a package's datasource? (the packages are moving data from a D3 database to sql server 2005)
Here is what I have tried so far:
A.
Dim pkgLocation As String
Dim app As Application = New Application()
pkgLocation = "c:Package1.dtsx"
Dim pkg As Package = app.LoadPackage(pkgLocation, Nothing)
Dim myConns As Connections = pkg.Connections
MessageBox.Show(myConns(0).ID.ToString)
Dim myConnMgr As ConnectionManager = myConns(0)
Dim connProperties As DtsProperties = myConnMgr.Properties
connProperties(0).SetValue(myConnMgr, "notsupposed=towork;")
MessageBox.Show(myConnMgr.ConnectionString)
B.
ConMgr = p.Connections.Add("OLEDB")
ConMgr.ConnectionString = "Provider=SQLNCLI.1;Data Source=192.168.0.233; User ID=sa; Initial Catalog=Rmt_New"
ConMgr.Name = "SqlDatabase"
ConMgr.Description = "Sql Connection"
Any help would appreciated.
View 1 Replies
View Related
Feb 28, 2006
I am connecting to a DB2 mainframe to pull data into SQL 2005. Very simple import. SSIS package works fine on 32 bit. However, once deployed to the 64 bit machine, I get "invalid product license" on the Acquire Connection method.
I've worked with IBM support. I have the correct version of the DB2 Connect client installed. The license is there and in the right place. I can connect to the mainframe from the 64 bit server using the DB2 client tools. I just can't seem to execute the package from Integration Services or run a job in SQL Server that executes the package.
According to BOL, the package should automatically detect the 64 client I installed. It and the 32 bit client I developed with share the same name/id.
Am I missing something? Any hints?
View 3 Replies
View Related
Feb 7, 2007
Where are SSIS packages stored and how can I see them in SQL Server Management studio?
View 6 Replies
View Related
Sep 15, 2006
I read in Kirk Haselden's book "Microsoft SQL Server 2005 Integration Services" that if SQL Serfver 2005 and 2000 are installed on the same machine as seperate instances then you can view the SQL Server 2000 DTS packages in 2005 Management Studio under the Management tree, Legacy, Data Transformation Services node.
But in my case, I am not able to see DTS packages in Management Studio. Is there a property or a setting that we need to configure for that?
Thanks.
View 2 Replies
View Related
Jan 17, 2007
I'm still new to SSIS packages and I'm NOT a developer. I am in the process of doing preliminary/prepatory work for migrating our SQL 2000 platforms to SQL 2005.
I am having a REAL headache with migrating/moving DTS packages from SQL 2000 to SQL 2005. Here are things that I know :
1. I know that some packages cannot be migrated due to ActiveX issues and other issues. Fine.
2. I know that I can install DTS backwards compatibility components on the server in order to be able to edit the DTS packages using a SQL 2000 DTS GUI. Fine.
3. I know that I can use the Migration wizard to migrate packages (and that some of them can't be migrated this way). Fine.
Here's what I don't know/or am conjecturing:
1. In a clustered environment, I have to edit the <%Install Path%>/90/DTS/Bin/MsDtsSrvr.ini.xml file to set the <ServerName> property to the Virtual Server name. Correct? Why can't M$ do this for me?
2. Do I HAVE to export the SSIS package to a .DTSX file in order to be able to edit it with Visual Studio? Is there ANY way around this?
3. If I am running in a clustered environment and I use the File System for storing packages, then the pacakges must be stored on a shared volume, right?
4. I did not find SQL Server Integration services on the B- (Passive) node. Do I have to install it separately onto the B server (much like having to install the Client Tools)?
If anyone has some guidance or tips on running SSIS in this brave, new, wonderful world, I would sure appreciate it.
And yes, I am going to go out right now and order a new book on SSIS.
Regards,
hmscott
View 3 Replies
View Related
May 11, 2007
Hello,
When I try to save modifies in packages with many components the system show me a information dialog telling me that there was a System.OutOfMemoryException
Anyone knows how to solve this problem without divide the package in 2 or more packages?
View 6 Replies
View Related
Apr 17, 2008
HI Gurus,
What permissions one sholud have to cretae and execute SSIS packages.
Thanks,
ServerTeam
View 1 Replies
View Related
Jun 3, 2008
Hi All,
I've been assigned a task by one of the programmer in my team to create packages from some of the databases(One from SQL2005&Other one from SQLEXPRESS)
I've created and saved the packages using the export wizard.I saved the Packages as Integration Services Packages(On file System).
Now he wants me to execute the packages using SSIS But in different time,like maybe after 5min.Other package runs.
I really have no clue how to do that,I've added Both packages in SSIS,But i really dont know how to run them in different time.
If anyone could help please do so!
View 3 Replies
View Related
Jun 23, 2008
Hi Gurus,
How can i know SSIS packages history as this package is executed with issues or without issues
Thanks,
ServerTeam
View 2 Replies
View Related
Jun 24, 2008
Hi Gurus,
How can i know SSIS packages history as this package is executed with issues or without issues
Thanks,
ServerTeam
View 1 Replies
View Related
Feb 16, 2006
I am in a situation where we are redesigning our Datawarehouse. Currently we have our Datawarehouse in SQL 2000 and we are rebuilding from scratch in SQL 2005. This means that even though we will get the same tables but we are planning to rename each & every attribute to make it more meaningful.
The question is like this:
We can migrate the current DTS packages to SSIS but since all the ODBC connections , field names(attributes) will change is it worth it to leverages the DTS packages ?
Also we convert the julian date to gregorian date in our DTS packages but since SSIS has a feature to convert julian date it would be redundant to migrate the packages and my feeling is to create new packages in SSIS and start on a clean slate.
Is there anybody who was in such situation ?
Please advise.
Adarsh Mathur
View 2 Replies
View Related
May 30, 2006
I thought so but I'm looking for a couple of packages and I am not be able to find any there at all.
View 3 Replies
View Related
Nov 7, 2007
Hi,
I am asking something very basic and theoretical, here.
Are there any design tools available in the market for designing SSIS packages?
By "design tools" I mean tools which enable us to "plan" or "design" the architecture of a SSIS Solution that will be implemented later, using SSIS, of course.
We have several design tools available for designing a Web Application solution, for example. Similarly do we have something for SSIS?
Are there any design approaches, best practices and/or design techniques published for designing a SSIS solution?
Please note that I am not talking about the SSIS Designer or the BIDS. I am talking about a tool/approach for designing the SSIS solution which can be delivered as a project artifact before the actual coding phase starts?
Well, I have tried to express myself as best as I could.
If someone can help me with this, it will be really great!
Thanks in advance.
Regards,
B@ns
View 10 Replies
View Related
Aug 22, 2007
On my local desktop, I can create and run ssis packages, when I try to do the same thing on server I get he following error right clicking on running packages or stored packages.
failed to retrieve data for this request. Library not Registered. (exception from HRESULT: 0x8002801D
there are 2 instances of SQL on this server. Both are named with one being use by SQLExpress and the other by SQL2005 Std
View 16 Replies
View Related
Jun 13, 2006
hi everyone,
Up to moment I've been using Dtsbackup2000 for do backup for our DTS but now, which let's allow do the same with dtsx???
TIA
View 4 Replies
View Related
Jan 31, 2008
Hi
I am fairly new to SSIS and I have a number of questions concerning deploying SSIS packages and Configuration Files
The SSIS packages will be deployed to three environments (DEV, TEST,PROD - this is a clustered environment) and will be executed by SQL Server Agent Jobs. There will be a need for different configuration values for each environment (file import directory, database server connectivity) , the configurations will be reused by mulitple packages.
I have decided to deploy the packages and configuration files to a file directory in the format
<<DRIVE LETTER>>SSIS PackagesPackages
<<DRIVE LETTER>>SSIS PackagesConfiguration Files
The questions are:
1) I storing the packages/config files to a file directory the best approach, or should I be deploying the packages to SQL or file ( C:Program FilesMicrosoft SQL Server 90DTSPackages)
2) Is it best to have different configuration files for each environment (dDBConn_DEV.dtsconfig, dDBConn_TEST) or have the same config file and then change the values during deployment (via scripts).
3) What is the best way to deploy the packages and config files to the different environments (rather do it via scripts than the deployment utility)
4) Where is the best place to store the config files (I have one VS 2005 project per package, the confi files are used by multiple packages), TFS is our source control software
5) Does any one know of a good website to look at for best practice when deploying packages and config files
Regards
Tim
View 5 Replies
View Related
Feb 20, 2006
I am currently building an ASP.Net web app and implementing SQL Server 2005 for a project. I was relying somewhat on kicking off SSIS packages from the web app, but I am not really sure how to do this.
If anyone could help me out, I would really appreciate it.
View 1 Replies
View Related
