Tag Info

New answers tagged

0

I have done something like this and it works. ([Drv DOB] == "0") || ([Drv DOB] == "101") ? NULL(DT_DBTIMESTAMP) : (DT_DBTIMESTAMP)(SUBSTRING([Drv DOB],1,2) + "-" + SUBSTRING([Drv DOB],3,2) + "-" + SUBSTRING([Drv DOB],5,2))


6

Data Flow Here's the general approach I'd take to solving your problem. I started with your source data and added some other conditions - a NULL as well as a 14 and 15 year to ensure my logic later is correct. SELECT D.DrvDOB FROM ( VALUES ('470324') , ('470324') , ('470209') , ('140209') , ('150209') , ('101') , ('0') , ...


0

It looks like you have your statement correct but it appears you have the order of substring wrong (currently you have yy-mm-dd). Try flipping it around to be mm-dd-yy (DT_DATE)(SUBSTRING([Drv DOB],3,2) + "-" + SUBSTRING([Drv DOB],5,2) + "-" + SUBSTRING([Drv DOB],1,2))


0

If you have mixed data, where some are double quoted and others are single, another option would be to load the complete data into a column and have a computed column in the same table that is running a function to strip out the unwanted characters from the source column. something like this: create table [xxxx] ( originalcol varchar(50) null, [$$derived] ...


0

I know it's silly to answer my own question. But I think I've fixed it. Thought I'd share the answer in case anyone else comes across this exact same issue. All it took was the addition of IMEX=1 to the Excel connection string. I stumbled across this post which explains more. ...


0

You could use a script task to set the variable property: Dts.Variables["uVarDateStamp"].Value = DateTime.Date;


3

Currently, your code will not send any email if any fields used to create the subject or body are NULL. I'd add a case to deal with that such as: IF (@EmailBody IS NOT NULL) BEGIN SET @Subject = @Subject + CASE WHEN @EmailBody LIKE '%Failed%' THEN 'Failed' WHEN @EmailBody LIKE '%Succeeded%' THEN 'Succeeded' WHEN @EmailBody LIKE ...


0

When troubleshooting the error I've searched the internet for possible solutions. The main solution to this error is the 32bit / 64bit excecution of SSIS. Because I can run other packages without any problems all posible solutions I found regarding configuration of packages, SSIS and Visual Studio didn't apply. Maybe I did something wrong when I generated ...


0

Its quite simple the reason for not running in 2012 is that when you upgraded the 2008 solution to 2012 solution. It gets converted to Project deployment model. So in 2012, right click the project name and choose Convert to Package deployment model. Then go to your package control, on white space right click and enable package configurations. Test it ...


1

Set the text delimiter in the flat file reader properties to the double quote.


0

If you want to store the configuration file and/or the file you need to "pick up" via the SSIS package on a UNC path, SQL Server is going to need permissions to that direct UNC path. This permission is based on your service account being used for SQL Server Agent. If you are in a domain environment, then it is best to have your service run as a domain ...


1

SSIS Excel Data Source: Error with output column “Comments” (5691) on output “Excel Source Output” (5596). The column status returned was: “Text was truncated or one or more characters had no match in the target code page.” Reason for this error: 1. The error is due to the lengthy data in the excel. 2. SSIS has an inbuilt logic to scan the data in the ...


5

You can create 3-4 different data flow tasks within your control flow (inside the Foreach loop container). Place a sql script before the DF containers. You can have the script actually do something or not. The idea is to create a conditional from this task that leads to the 3-4 data flow tasks. On the connector for each DF object, add a constraint that ...


0

I decided to experiment with creating the user with a random password, and everything worked. CREATE LOGIN [##MS_SSISServerCleanupJobLogin##] WITH PASSWORD=N'blahblahblah', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF GO USE SSISDB GO ALTER USER [##MS_SSISServerCleanupJobUser##] with LOGIN = ...


0

If you don't have the option of fixing the spreadsheet I would load the data into a SQL table and update it in SQL with the below code. Load test data CREATE TABLE FillTest (MyDate date, MyType char(3), MyAccount char(3), Id INT NOT NULL IDENTITY(1,1)) GO INSERT INTO FillTest VALUES ('1/1/2014','AAA','111'), (NULL, NULL, '112'), (NULL, ...


1

Two options come to mind. If you go with the local copy approach you can manually use the AutoFill feature to fill in the blank values. For an automated approach, the one approach I would think of in T-SQL is to use a cursor to loop through all the rows, capture the non-NULL Dates and Types into variables and when you encounter a NULL value, UPDATE the ...



Top 50 recent answers are included