Fixing Non-printing Character That Breaks SSIS Package
Apr 3, 2008
Hi folks,
I used a non-printing character (hex 97) in one of my derived column transformations which made my data flow task comopnents disappear, and throw the error during execution:
"Warning: The DataFlow task has no components. Add components or remove the task."
In order to fix the package, I thought I could open the .dtsx file in code/raw form, find the misbehaving character, remove it, and I'd be good to go.
But I can't figure out how to read the .dtsx code to find the character (despite my best efforts with a hex editor).
Anyone have any ideas? Fixing this package could save me days of rework...
I have e-mails saved in a SQL database with all HTML formatting information. To remove all HTML formatting I craeted a function on the RS report:
Public Shared Function removeHtmlChar(ss) try Dim l as integer Dim i as integer Dim ch as string Dim x as integer Dim t as string t = "" l=len(ss) for i=1 to l ch=mid(ss,i,1) x=i if(ch="<") then x=i do while(mid(ss,x,1)<>">") x=x+1 loop else t=t & ch end if i=x next t = t.Replace("nbsp;"," ") removeHtmlChar = t catch removeHtmlChar = "" end try End Function
When I look at the report on the screen the report is showed correct. If I print or export the report it add a lot of line breaks in the e-mail text. Why does it not print it as it looks at the screen?
we have some SSIS packages using a dataflow sourcing data from DB2 using an oledb connection. These were working when we were using the oledb for DB2 drivers as supplied in HIS2004, but since upgrading to HIS2006 some of these dataflows are failing with the error shown below.
Also a DTS package running the same query against the same database(s) is working fine.
Can anyone shed any light on this? Do we need a patch?
The error text we're seeing is:
[OLE DB Source [1380]] Error: An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft DB2 OLE DB Provider" Hresult: 0x80040E14 Description: "".
[DTS.Pipeline] Error: The PrimeOutput method on component "OLE DB Source" (1380) returned error code 0xC0202009. 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.
[DTS.Pipeline] Error: Thread "SourceThread0" has exited with error code 0xC0047038.
During SQL Server SP2 timeframe a pending security update from the .NET Framework team was announced. That fix, when released would break any SSIS package using scripts. In response to this SQL Server Integration Services provided a fix into SP2 (as well as corresponding fixes for RTM & SP1) that would mitigate this issue, including log messages that would point to the KB below. The .NET Framework fix was released on Tuesday 7/10/2007 and thus we expect to see it surfacing on SQL Server Integration Services applications using scripts (both in data flow and control flow), most likely on machines running €˜pure€™ RTM and/or 64bit installations.
The related KB article describing the impact to SSIS packages is available here:
http://support.microsoft.com/kb/931846
Here are the details of the .NET Framework 2.0 fixes:
http://support.microsoft.com/kb/928365 (for Windows XP/2003/2000) http://support.microsoft.com/kb/929916 (for Windows Vista)
Is there a way to print the source code from the local package. I know you can go in the source and cut and paste it into a word document. I was wondering if there was a way to do it when you are in designing the package so that it would print all the source codes of SQL statments/queries.
Hey, I've a few jobs which call SSIS packages. If I run the SSIS package, it runs fine but if I try to run the job which calls this package, it fails. Can someone help me troubleshoot this issue? None of my jobs that call an SSIS package work. All of them fail.
So I have been asked by our sustainability person to create report from our printing data that actually shows the number of pieces of paper used. This is easy enough for single-sided printing, but when printing in duplex the software does not take into account that 3 printing pages actually equates to 2 pieces of paper. I know this sounds simple, but say I have a print job record that looks like this:
Submitted printed total_pages duplex 8/10/2015 8/10/2015 42 1Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â
This is a print jobs that if done correctly is actually 21 pages( duplex printing). If the job is say total_pages =5 I cant just divide by 2 because its actually using 3 pieces of paper ( yes they are wanting this data don't ask why). How can I adjust some sql to accurately depict 5 pages, front and back, as 3 pieces of paper?
I have deployed a report that is configured for landscape printing. It does print in landscape, however, only the first seven columns appear on the first page and the other five columns appear on the next page. Is there a method, like in print preview setup in MS Excel, where we can scale down the print (like to 80%) so all columns appear on each page?
SQL 2012. VS2013..I have created a SSIS package that generated a pdf report and a script task that prints the pdf reportScript task    Public Sub Main()    '        Dim pdfFileName As String = Dts.Variables("User::CUR_ADDACSReport").Value.ToString        Dim PrintPDF As New ProcessStartInfo        PrintPDF.UseShellExecute = True        PrintPDF.Verb = "print"        PrintPDF.WindowStyle = ProcessWindowStyle.Hidden        PrintPDF.FileName = pdfFileName        Process.Start(PrintPDF)        '        Dts.TaskResult = ScriptResults.Success    End Sub
When I run the SSIS package from VS2013 it executes and the report is printed, however when I deploy it to the SQL server as an agent job, the package executes, but the report is not pronted.
I have several nested sub-total groups that are all coded to "page break at end". I don't know how to tell RS to suppress the page break if one sub-total immediately follows another. The initial HTML output seems to be smart enough to do this suppression on its own, but when I export to PDF, all the page breaks appear. This causes several pages at the end of the report with one line per page. Perhaps, if I could explicitly suppress these page breaks, the PDF would render properly. How can I do this?
And there is a task (Execute SSIS package) in First package that calls the execution of second package.
I m continuously receiving an error "Failed to decrypt protected XML node "PackagePassword" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available."
As we are running first package by job, job runs successfully logging above error
The protection level of second package is set to "EncryptSensitiveWithUserKey"
Any SQL guru's out there know a better way of writing this SP, its very slow.....CREATE PROCEDURE SP_LIST_ACTIVITY_CLASS_BY_ENROLLMENT( @int_activity_class_Id INT,) asDECLARE @ENROLL_COUNT INTDECLARE @WAITLIST_COUNT INTDECLARE @CONFIRM INTDECLARE @PENDING INTDECLARE @WITHDRAWN INTDECLARE @APPROVED INTDECLARE @DELETED INT SET @ENROLL_COUNT = (SELECT ISNULL(COUNT(DISTINCT ENROLLMENT_ID),0) FROM ENROLLMENT WHERE ACTIVITY_CLASS_ID = @int_activity_class_Id AND ENROLLMENT_STATUS_ID NOT IN (4,5,6)) --TAKES 5 SECONDSSET @WAITLIST_COUNT = (SELECT ISNULL(COUNT(DISTINCT ENROLLMENT_ID),0) FROM ENROLLMENT WHERE ACTIVITY_CLASS_ID = @int_activity_class_Id AND ENROLLMENT_STATUS_ID = 5) --TAKES 5 SECONDSSET @CONFIRM = (SELECT ISNULL(COUNT(DISTINCT ENROLLMENT_ID),0) FROM ENROLLMENT WHERE ACTIVITY_CLASS_ID = @int_activity_class_Id AND ENROLLMENT_STATUS_ID = 3) --TAKES 5 SECONDSSET @PENDING = (SELECT ISNULL(COUNT(DISTINCT ENROLLMENT_ID),0) FROM ENROLLMENT WHERE ACTIVITY_CLASS_ID = @int_activity_class_Id AND ENROLLMENT_STATUS_ID = 2) --TAKES 5 SECONDSSET @WITHDRAWN = (SELECT ISNULL(COUNT(DISTINCT ENROLLMENT_ID),0) FROM ENROLLMENT WHERE ACTIVITY_CLASS_ID = @int_activity_class_Id AND ENROLLMENT_STATUS_ID = 4) --TAKES 5 SECONDSSET @APPROVED = (SELECT ISNULL(COUNT(DISTINCT ENROLLMENT_ID),0) FROM ENROLLMENT WHERE ACTIVITY_CLASS_ID = @int_activity_class_Id AND ENROLLMENT_STATUS_ID = 1) --TAKES 5 SECONDSSET @DELETED = (SELECT ISNULL(COUNT(DISTINCT ENROLLMENT_ID),0) FROM ENROLLMENT WHERE ACTIVITY_CLASS_ID = @int_activity_class_Id AND ENROLLMENT_STATUS_ID = 6) --TAKES 5 SECONDS Select A.ACTIVITY_NAME, A.DESCR, C.CUSTOMER_ID, C.CUSTOMER_NAME, cast(A.PROVIDER_CODE as varchar) + '-' + cast(FY.FISCAL_YY as varchar) + '-' + cast(AC.CEU_ACTIVITY_CODE as varchar) + '-' + cast(isnull(ax.activity_seq_number,'XXX') as varchar) as ACTIVITY_CODE, MIN(S.SCHEDULE_DATE) AS SCHEDULE_DATE, SS.STATUS, A.ACTIVITY_ID, AX.ACTIVITY_SIZE, AX.ECOMMERCE_IND, @ENROLL_COUNT AS ENROLLMENT, @WAITLIST_COUNT AS WAITLIST, @CONFIRM AS CONFIRMED, @PENDING AS PENDING, @WITHDRAWN AS WITHDRAWN, @APPROVED AS APPROVED, @DELETED as DELETED, AX.WAITLIST_INDFrom ACTIVITY_CLASS AS AX JOIN ACTIVITY AS A ON (AX.ACTIVITY_ID = A.ACTIVITY_ID) JOIN CUSTOMER AS C ON (A.CUSTOMER_ID = C.CUSTOMER_ID) JOIN FISCAL_YEAR AS FY ON (AX.FISCAL_YEAR_ID = FY.FISCAL_YEAR_ID) JOIN ACTIVITY_CODE AS AC ON (AX.ACTIVITY_CODE_ID = AC.ACTIVITY_CODE_ID) JOIN SCHEDULE AS S ON (AX.ACTIVITY_CLASS_ID = S.ACTIVITY_CLASS_ID) JOIN ACTIVITY_STATUS AS SS ON (AX.ACTIVITY_STATUS_ID = SS.ACTIVITY_STATUS_ID)Where AX.ACTIVITY_CLASS_ID= @int_activity_class_Id GROUP BY A.ACTIVITY_NAME, A.DESCR,C.CUSTOMER_NAME,C.CUSTOMER_ID, A.PROVIDER_CODE, FY.FISCAL_YY, AC.CEU_ACTIVITY_CODE, ax.activity_seq_number, SS.STATUS, A.ACTIVITY_ID, AX.ACTIVITY_SIZE, AX.WAITLIST_IND,AX.ECOMMERCE_IND --TAKES 1 SECONDS--TOTAL 36 SECONDS
I have got dbo , in the database security - logins. But I do not find it in the System - Security - Logins. When I try to re-create it throws an error. The server principle - dbo already exists.
I have got all the schemas in the database under dbo.So I cannot drop the dbo from the database.
Hello everybody... I have a SQL 2000 Script that is not working on SQL 2005.. Or 2000 for that matter.. I wanted to know if somebody can help me fix this script.
/****** Object: Database NetManage_SQL ******/ IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'NetManage_SQL') DROP DATABASE [NetManage_SQL] GO
Insert into [settings](Company, LicenseKey, comments) values('DEMO COMPANY','|95|49|47|26|253|195|170|232|71|19|151|77|188|231|23|64|87|62|215|53|169|186|27|65|218|111|185|218|238|127|2|115|187|245','Product License Key') Insert into [subnet](Subnet_Name, Subnet_Mask, Parent_Subnet) values('Network Enterprise','000000000000','0') Insert into [Device_Type](Device_Type_ID, Device_Type) values(0,'Not Assigned') Insert into [Device_Type](Device_Type_ID, Device_Type) values(1,'PC') Insert into [Device_Type](Device_Type_ID, Device_Type) values(2,'Printer') Insert into [Device_Type](Device_Type_ID, Device_Type) values(3,'Router') Insert into [Device_Type](Device_Type_ID, Device_Type) values(4,'Switch') Insert into [Device_Type](Device_Type_ID, Device_Type) values(5,'Hub') Insert into [Device_Type](Device_Type_ID, Device_Type) values(6,'Web Server') Insert into [Device_Type](Device_Type_ID, Device_Type) values(7,'FTP Server') Insert into [Device_Type](Device_Type_ID, Device_Type) values(8,'Mail Server') Insert into [Device_Type](Device_Type_ID, Device_Type) values(9,'DNS Server') Insert into [Device_Type](Device_Type_ID, Device_Type) values(10,'DHCP Server') Insert into [Device_Type](Device_Type_ID, Device_Type) values(11,'Other') Insert into [Device_Type](Device_Type_ID, Device_Type) values(12,'Virtual Server') Insert into [Device_Type](Device_Type_ID, Device_Type) values(13,'Other Server') Insert into [Device_Type](Device_Type_ID, Device_Type) values(14,'VoIP Phone') Insert into [Device_Type](Device_Type_ID, Device_Type) values(15,'SQL Server')
Insert into [IPAddress_Type](IPAddress_Type_ID, Can_Edit, IPAddress_Type) values(0,0, 'Not Assigned') Insert into [IPAddress_Type](IPAddress_Type_ID, Can_Edit, IPAddress_Type) values(1,0, 'Static') Insert into [IPAddress_Type](IPAddress_Type_ID, Can_Edit, IPAddress_Type) values(2,0, 'DHCP') Insert into [IPAddress_Type](IPAddress_Type_ID, Can_Edit, IPAddress_Type) values(3,0, 'Reserved')
Insert into [Users](UserName, DisplayStyle, [Password],Full_Name, EmailAddress, CanDelete, Priv) values('Administrator',0,'admin','Administrator', 'admin@company.com',0,1)
I keep getting error:
Msg 128, Level 15, State 1, Line 6
The name "Contact_Name" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
I have a SQL2000 db that uses NT authentication for the users. All users have access to the public group on the db's and are assigned security inside the application. When I restore the db's to another server (can't do master) the logins seem out of synch. Users who had access to db's no longer did. I realize each user has their own sid. 2 questions - 1 if I dropped all the users and used DTS to import the logins would that be better. 2nd - I thought (couldn't find in BOL there was a sp_fixlogin% sp that could be run that might help me with this. Thanks
I have a SSIS job, one of the last steps it performs is to execute a SQL 2000 DTS package. This has to be done as a SQL 2000 DTS package as it is performing rebuilds of SQL 2000 Analysis Services dimensions and cubes. We've found that when the DTS fails the SSIS job is happily completing showing as a success, we would prefer to know it went wrong.
As far as I'm aware SSIS merely starts the DTS off and doesn't care about it's result. I've taken a look in to turning on the logging for the execute DTS package and thought that the ExecuteDTS80PackageTaskTaskResult would give me the answer I need...but is merely written to the log not available as an event-handler. It also looks like it is not safe to put a SQL task in as the next item to go look at the SQL 2000 system tables to look at the log for the DTS package as the SSIS documentation warns that the DTS package can continue to run after the execute DTS package task has ended.
Ideally I want any error raised within the DTS package to cascade up to be an error in the SSIS job, I can then handle it appropriately. I cannot find a way to do this. Is there a way?
If not, can anyone suggest how in the remainder of the SSIS tasks I can be sure that the DTS has completed before I start any other tasks that will check for the SQL 2000 log of its execution?
I have developed an SSIS package for ETL purpose. I am invoking the SSIS package through .Net console application by referencing the ManagedDTS Assembly. I am able to execute the package in Sql Server 2005 Developer Edition and it runs fine till completion.
But when i try to execute the packahe in Sql Server 2005 Standard edition, by invoking the package through .Net console application the status of the package is failure.
Can any one help me how to over come this problem.
I am in the process of moving from a 32-bit SQL Server 2005 Enterprise (9.0.3054) to a 64-bit SQL Server 2005 Enterprise (9.0.3054 with 4 CPUs and 8GB of memory on Win 2003 SP2) and the process has been very frustrating to say the least. I am having a problem with packages that I created on my 64-bit SQL Server. I am importing a few tables from the 32-SQL Server into the 64-bit SQL Server using the Task --> Import to create the package.
Sometimes when I am creating a package I get the following error in a message box:
SQL Server Import and Export Wizard
The SSIS Runtime object could not be created. Verify that DTS.dll is available and registered. The wizard cannot continue and it will terminate.
Additional information: Attempted to read or write protected memory. This is often an indication that other memory is corrupt. (System.Windows.Forms)
Other times when I run a package that has run successfully before I get the following error:
Faulting application dtexecui.exe, version 9.0.3042.0, stamp 45cd726d, faulting module unknown, version 0.0.0.0, stamp 00000000, debug? 0, fault address 0x025d23f0.
The package appears to hang when running. By this I mean that the Package Execution Progress shows progress up to a point then it just stops. (The package takes about 17 seconds to run normally) CPU usage is at 1% and the package cannot be stopped.
I have deleted and re-created the package several times and I have also re-installed the service pack on the SQL Server (9.0.3054) but that did not help.
I would like to standardize SSIS development so that developers all start with the same basic template. I have set it up so it is an available template ( http://support.microsoft.com/kb/908018 ) but I would like it to be the default when a new project or package is created. Is this an option?
I am new to database design and a lot of things never made any sense to me regarding relationships and such. I have been working on a very large design that started out well enough, but as tables were added a lot of organization fell by the wayside. Now that I am getting closer to the end, I am finding a lot of places where there should be Foreign keys, maybe some triggers, etc (I have the same data item in 5 different places, when it is deleted in one place it must go from all). Assuming that the datatypes and sizes are identical for the duplicated bits of data, can I go about making FK-PK relationships and such now that there is a lot of stuff in the database, or do I have to start from scratch and rebuild the whole thing.
The other question is much more simple:
How do I make multiple rows "unique". I have a primary key, and an identity column, but I can't add a secong primary key, and Enterprise Manager only lets me make 'int' datatypes identity columns. I have tried the "add constraints" but it asks for an expression and I have no idea what the syntax might be.
hours wasted what else is new with Micro --- crap try to import an excel into a table, longest field (via vba macro report) is 278 receiving DB field is 4000 get "Truncation error, I must stop I am a piece of s*** program " the only thing you can find on the web is make sure you have SQL server 2005 SP2 install it same thing and g** only knows what other problems I have just created by installing another piece of Microsoft magic generated in India or China by the best technologists making at least $5 and hour
so MVPs when is this rediculous situation going to be fixed ?? oh that's right your answer will be "go to VISTA" which won't fix my problem but will probably help your stock situation
MS owes me (conservatively) 100K ... my current plan is to install Linux get up on MySQL and NEVER deal with SHoddy half built pieces of garbage again
so where is the fix ???
when will we see it (short of buying another bloated piece of ... oh i mean Vista)
an addendum
i am trying to import 25,000 rows with approx 20 columns from Excel
i added a first column with an ID .... if I import whole table it dies on row 1852 for truncation problems
if I empty that column all goes in make other exel get rid of all rows except ID and bad row (contains web addresses eg http://www. blah blah)
import that excel it dies on row 2395 in other words the first offending cell was no problem if it was the 2cnd row instead of the 15th row
try to tell me this is not a random bug .... long live MYSQL
I would like to fetch the data flow component name while package is executing. Since system variable named [System::SourceName] only fetches name of the control flow tasks? Is there a way to capture them?
The master package has a configuration file, specifying the connect strings The master package passes these connect-strings to the child packages in a variable Both master package and child packages have connection managers, setup to use localhost. This is done deliberately to be able to test the packages on individual development pc€™s. We do not want to change anything inside the packages when deploying to test, and from test to production. All differences will be in the config files (which are pretty fixed, they very seldom change). That way we can be sure that we can deploy to production without any changes at all.
The package is run from the file system, through a job-schedule.
We experience the following when running on a not default sql-server instance (called dkms5253uedw)
Case 1: The master package starts by executing three sql-scripts (drop foreign key€™s, truncate tables, create foreign key€™s). This works fine.
The master package then executes the first child package. We then in the sysdtslog get:
Error - €œcannot connect to database xxx€? Info - €œpackage is preparing to get connection string from parent €¦€?
The child package then executes OK, does all it€™s work, and finish. Because there has been an error, the master package then stops with an error.
Case 2: When we run exactly the same, but with the connection strings in the config file pointing to the default instance (dkms5253), the everything works fine.
Case 3: When we run exactly the same, again against the dkms5253uedw instance, but now with the exact same databases defined in the default instance, it also works perfect.
Case 4: When we then stop the sql-server on the default instance, the package faults again, this time with
Error - €œtimeout when connect to database xxx€? Info - €œpackage is preparing to get connection string from parent €¦€?
And the continues as in the first case
From all this we conclude, that the child package tries to connect to the database before it knows the connection string it gets passed in the variable from the master package. It therefore tries to connect to the default instance, and this only works if the default instance is running and has the same databases defined. As far as we can see, the child package does no work against the default instance (no logging etc.).
We have tried delayed validation in the packages and in the connection managers, but with the same results (error).
So we are desperately hoping that someone can help us solve this problem.
I am using Conditinal split in my package. I need to remove certain rows which are matching my criteria. The criteria requires using wild card characters like, first_name = '%john%'.
I would like to know how to import in the custom delimited text file by using SSIS. For example, instead by using tab or comma delimited, I use this character : '¶' The reason is the delimited format that SSIS provided is too common such as colon, semi colon, tab, comma and pipeline. I have the data that the user also key in the pipeline there. So I am thinking to separate the field by using this special character, but cannot see if there is anyway to import in by using SSIS.
I am interested in Passing value from a child Package variable to the Parent package that calls it in ssis.
I am able to call the Child package using the execute package task and use Configurations to pass values from the parent variable to the child, but I am not able to pass the value from the child to the parent.
I have a variable called datasetId in both the parent and child. it gets computed in the child and needs to be passed to the parent...
Deployed Report having SSIS package as source do not work when Indirect Package configuration is used in ETL package. It seems ETL package when called/executed from Report manager does not recognize environment variable to pick up the dtsconfig file.
The Report works when Direct package configuration is used to same dtsconfig file.
What could be the reason? Any solution for this? This will cause our build/deployment to QA and Prod very difficult.
I now have two SSIS package, "TESTING" and "LOADING". The "TESTING" package have an execute package task that call the "LOADING" package. When I want to execute the TESTING package, how can I setup the connection string so that I can edit the password of the database connected by the "LOADING" package?
I've got a huge inefficiency in my code that I'm trying to fix. I’m coding in VB.NET using ASP.NET and an MSSQL 2000 server.
I’m working in a temporary table that has an identical layout as another, non-temporary table in my database. Once I get the temp table how I want it, I need to insert everything from that table into my main table. Before I can do that, however, I need to delete all the records in the main table with certain fields that match a record’s fields in the temporary table.
Right now, I have a method that builds one delete statement per record in the temporary table and then runs those statements on the main table. Since I’m dealing with the order of 50,000 records (at least) here, building and sending those statements to the server takes forever.
Is there a way I can accomplish the same thing without building and sending such a huge SQL call to the server? If so, how would I go about doing that?
Thanks in advance for whatever help you can give, -Starwiz
